MySQL模糊查询LIKE优化

chosen1cwp 2023年08月04日 102次浏览

系统项目佣金模块中的结算数据查询功能,业务部门反馈查询很慢,提了优化需求。

EXPLAIN一顿分析,最终确定查询慢的原因在于某个数据表进行LIKE模糊查询了所导致的。

offfice

id name fid fids
1 NULL NULL
2 1 1,
3 2 1,2,
4 3 1,2,3,

MySQL手册中关于全文索引

MySQL 5.6开始支持全文索引,可以在变长的字符串类型上创建全文索引,来加速模糊匹配业务场景的DML操作。

  • 从MySQL 5.7开始内置了ngram全文检索插件,用来支持中文分词,并且对MyISAM和InnoDB引擎有效。
  • 由于全文索引的缓存和批量处理的特性,Insert&Update操作是在事务提交时处理,只能看到提交后的数据。
  • 全文索引使用函数MATCH() …… AGAINST()来进行检索,MATCH()中列个数及顺序必须和索引定义保持一致。
  • 只能用于InnoDB和MyISAM的表,不支持分区表,不支持%通配符搜索。
  • MATCH()列表与表的全文索引定义列完全匹配。
  • MySQL优化器Hint对于全文索引会被限制。

Table添加支持中文分词的全文索引

ALTER TABLE offfice ADD FULLTEXT INDEX index_fids (fids) WITH PARSER ngram;

改写查询语句

SELECT * FROM offfice WHERE MATCH(fids) against('2');

Explain 分析优化后的查询SQL

1 SIMPLE office fulltext index_fids index_fids 0 const 1 100 Using where; Ft_hints: sorted

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE office (NULL) fulltext index_fids index_fids 0 const 1 100.00 Using where; Ft_hints: sorted

使用了全文索引的方式查询,type是fulltext,同时命中全文索引index_fids,从上面的分析可知,在MySQL中,对于完全模糊匹配%关键字%查询的SQL可以通过全文索引提高效率。