sql语句中like的性能优化

在MySQL中，使用like进行模糊查询，在一定情况下是无法使用索引的。

比如like '%abc' like '%abc%'

只有like 'abc%'才会走索引。

EXPLAIN SELECT * FROM `test` WHERE `name` LIKE 'abc%' ;

+----+-------------+-------+------------+-------+---------------+----------+---------+--------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref    | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+----------+---------+--------+------+----------+-----------------------+
| 1  | SIMPLE      | test  | <null>     | range | idx_name      | idx_name | 153     | <null> | 200  | 100.0    | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+--------+------+----------+-----------------------+

所以，在碰到无法使用索引的情况下，可以使用反向数据解决。

反向查询

增加一个reverse_colname的字段，表示需要查询的列的逆序，如果需要查询的列数据是abc，那么这个字段就存储cba。

历史数据初始化update table set reverse_colname = REVERSE(colname);

同时创建一个联合索引，把(原字段,order_state,反转字段) 放在一起作为联合索引。

查询的时候SELECT * FROM tablename WHERE reverse_colname LIKE 'cba%'; 相当于反向查询匹配出了name=123abc的行，这样就可以利用上索引。

如果要查询要查询like 'abc%'和like '%abc'或者like '%abc%'，直接使用一个union将原字段和反向字段都like一次

EXPLAIN SELECT * FROM `tablename` WHERE `reverse_colname` LIKE 'cba%'
UNION SELECT * FROM `tablename` WHERE `colname` LIKE 'abc%';

虚拟列

在MySQL5.7.6之后新增了虚拟列功能，为一个列建立一个虚拟列，并为虚拟列建立索引，在查询时where中like条件改为虚拟列，就可以使用索引了。

ALTER TABLE `tablename` ADD COLUMN `reverse_colname` VARCHAR(50) GENERATED ALWAYS AS (REVERSE(`colname`)) VIRTUAL; //创建虚拟列
ALTER TABLE `tablename` ADD INDEX `idx_name_reverse`(`reverse_colname`); //为虚拟列reverse_colname列添加索引

虚拟列可以指定为VIRTUAL或STORED，VIRTUAL不会将虚拟列存储到磁盘中，在使用时MySQL会现计算虚拟列的值，STORED会存储到磁盘中，相当于我们手动创建的冗余列。所以：如果你的磁盘足够大，可以使用STORED方式，这样在查询时速度会更快一些。