mysql 8.0的全文索引使用及探索

由于公司积累的数据达到上千万，经常需要模糊查询，一般索引无效，幸好mysql8.0开始支持中文的全局索引了。就毫不犹豫的从mysql5.7升级到了mysql8.0。以下是使用全局索引的探索过程。

1、由于数据表过大，所以原始数据表是采用横向分表（分区表）的的结构存储的。升级到mysql8.0后发现，是不能在分区表上建立全局索引的，只有单独建立一个数据表，把需要模糊查询的几个字段放到新的数据表里。

2、建立全文索引时，必须带后缀with parser ngram。建立数据表如下：

CREATE TABLE `cust_full_text` (
	`id` INT(10) NOT NULL COMMENT '原始表主键ID',
	`name` VARCHAR(128) NOT NULL COMMENT '名称' COLLATE 'utf8mb4_unicode_ci',
	`address` VARCHAR(255) NULL DEFAULT NULL COMMENT '地址' COLLATE 'utf8mb4_unicode_ci',
	`update_time` DATETIME NOT NULL COMMENT '数据更新时间',
	PRIMARY KEY (`id`) USING BTREE,
	FULLTEXT INDEX `idf_cust_full_text` (`name`, `address`) with parser ngram,
	FULLTEXT INDEX `idf_cust_full_text_name` (`name`) with parser ngram
)
COMMENT='全文索引表'
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

3、新建立一个表，若原始表更新时，此表也要一同更新，所以需要建立同步的触发器，代码如下：

/* 插入触发器 */
DELIMITER //
create trigger trigger_insert_cust
after insert on cust
FOR EACH ROW
begin
    insert into cust_full_text(id,name,address,update_time) values(new.id,new.name,new.address,new.update_time);
end;

/* 更新触发器 */
DELIMITER //
create trigger trigger_update_cust
after update on cust
FOR EACH ROW
begin
	update cust_full_text 
	set name = new.name,address = new.address,update_time = new.update_time
	where id = new.id;
end;

/* 删除触发器 */
DELIMITER //
create trigger trigger_delete_cust
after delete on cust
FOR EACH ROW
begin
	delete from cust_full_text where id = old.id;
end;

4、但是，郁闷的时，数据达到10万、20万，查询性能还是可以接受的，但是一旦达到100万以上，查询就慢的不可接受了。

select *
from cust_full_text cft 
where match(name) against('+科技' in boolean mode)
limit 10

5、果断放弃mysql的全局索引，转向Elasticsearch等企业级搜索引擎。（但好像Elasticsearch需要占用不较多的内存，尽量保证不新增服务器或云服务器扩容的前提下实现全文搜索）