like模糊匹配查询慢解决之道——MySQL全文索引

需求

需要模糊匹配查询一个单词

select * from t_phrase where LOCATE(‘昌’,phrase) = 0;

select * from t_chinese_phrase where instr(phrase,‘昌’) > 0;

select * from t_chinese_phrase where phrase like ‘%昌%’

explain一下看看执行计划

由explain的结果可知，虽然我们给phrase建了索引，但是查询的时候，索引是失效的。

原因： mysql的索引是B+树结构，InnoDB在模糊查询数据时使用 “%xx” 会导致索引失效（此处就不展开讲了）

从查询时长上来看，花费时间：90ms

目前数据量：93230（9.3W）已经需要90ms，这个时间不太能接受，假如数据量增加，这个时间会不断增长。

解决方案：

数据量不大的情况下，使用mysql的全文索引；
数据量比较大或者mysql的全文索引不达预期的情况下，可以考虑使用ES

下面主要是MySQL的全文索引相关.

全文索引介绍

1、发展历史

• 旧版的MySQL的全文索引只能用在MyISAM存储引擎的char、varchar和text的字段上。

• MySQL5.6.24上InnoDB引擎也加入了全文索引。

2、全文索引

• 全文检索（Full-Text Search） 是将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。它可以根据需要获得全文中有关章、节、段、词等信息，也可以进行各种统计和分析

3、创建全文索引

若需对大量数据设置全文索引，建议先添加数据再创建索引。

1、创建表时创建全文索引

create table 表名(
字段名1,
字段名2,
字段名3,
字段名4,
FULLTEXT full_index_name (字段名)
)ENGINE=InnoDB;

2、为已有表添加全文索引

create fulltext index 索引名称 on 表名(字段名)；

eg:

create table t_word
(
    id        int unsigned auto_increment comment '自增id' primary key,
    uid       char(32)     not null comment '32位唯一id',
    word      varchar(256) null comment '英文单词',
    translate varchar(256) null
);

create fulltext index full_idx_translate
    on t_word (translate);

create fulltext index full_idx_word
    on t_word (word);

INSERT INTO t_word (id, uid, word, translate) VALUES (1, '9d592499c65648b0a9519206688ef3f9', 'lion', '狮子');
INSERT INTO t_word (id, uid, word, translate) VALUES (2, 'ce26ac4239514bc6af481bcb1d9b67df', 'panda', '熊猫');
INSERT INTO t_word (id, uid, word, translate) VALUES (3, 'a7d6042853c44904b68275daafb44702', 'tiger', '老虎');
INSERT INTO t_word (id, uid, word, translate) VALUES (4, 'f13bd0a8ecea44fc9ade1625eeb4cc3c', 'goat', '山羊');
INSERT INTO t_word (id, uid, word, translate) VALUES (5, '27d5cbfc93a046388d712085e567474f', 'sheep', '绵羊');
INSERT INTO t_word (id, uid, word, translate) VALUES (6, 'ed35df138cf348aa937781be8ee21cbf', 'lamb', '羊羔');
INSERT INTO t_word (id, uid, word, translate) VALUES