只给出结果,详细可以查看原文:https://cloud.tencent.com/developer/article/1751656
①通过全文索引,模糊匹配优化
对于SQL语句后面的条件 nickname like '%看风%'
默认情况下,CBO是不会选择走nickname索引的,该写SQL为全文索引匹配的方式:match(nickname) against('看风')。
mysql>explain select * from users01 where match(nickname) against('看风');
| 1 | SIMPLE | users01 | NULL | fulltext | idx_full_nickname | idx_full_nickname | 0 | const | 1 | 100.00 | Using where; Ft_hints: sorted |
使用了全文索引的方式查询,type=fulltext,同时命中全文索引 idx_full_nickname
,从上面的分析可知,在MySQL中,对于完全模糊匹配%%查询的SQL可以通过全文索引提高效率。
②通过生成虚拟列,模糊匹配优化
对于where条件后的 like '%xxx'
是无法利用索引扫描,可以利用MySQL 5.7的生成列模拟函数索引的方式解决,具体步骤如下:
- 利用内置reverse函数将like '%风云'反转为like '云风%',基于此函数添加虚拟生成列。
- 在虚拟生成列上创建索引。
- 将SQL改写成通过生成列like reverse('%风云')去过滤,走生成列上的索引。
添加虚拟生成列并创建索引。
mysql>alter table users01 add reverse_nickname varchar(200) generated always as (reverse(nickname));
mysql>alter table users01 add index idx_reverse_nickname(reverse_nickname);
#SQL执行计划
| 1 | SIMPLE | users01 | NULL | range | idx_reverse_nickname | idx_reverse_nickname | 803 | NULL | 1 | 100.00 | Using where |
可以看到对于 like '%xxx'
无法使用索引的场景,可以通过基于生成列的索引方式解决。