MySql 中like的模糊查询如何优化

问题说明

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

当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>     | ALL  | <null>        | <null> | <null>  | <null> | 19820 | 11.11    | Using where |
+----+-------------+-------+------------+------+---------------+--------+---------+--------+-------+----------+-------------+

当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>     | ALL  | <null>        | <null> | <null>  | <null> | 19820 | 11.11    | Using where |
+----+-------------+-------+------------+------+---------------+--------+---------+--------+-------+----------+-------------+

当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 |
+----+-------------+-------+------------+-------+---------------+----------+---------+--------+------+----------+-----------------------+

解决方案

那么，like %abc真的无法优化了吗？

我们之所以会使用%abc来查询说明表中的name可能包含以abc结尾的字符串，如果以abc%说明有以abc开头的字符串。

假设我们要向表中的name写入123abc，我们可以将这一列反转过来，即cba321插入到一个冗余列v_name中，并为这一列建立索引：

ALTER TABLE `test` ADD COLUMN `v_name` VARCHAR(50) NOT NULL DEFAULT ''; //为test表新增v_name列
ALTER TABLE `test` ADD INDEX `idx_v_name`(`v_name`); //为v_name列添加索引
INSERT INTO `test`(`id`,`name`,`v_name`)VALUES(1,'123abc','cba321'); //这里不但要写name，也要写v_name

接下来在查询的时候，我们就可以使用v_name列进行模糊查询了

SELECT * FROM `test` WHERE `v_name` LIKE 'cba%'; //相当于反向查询匹配出了name=123abc的行

当然这样看起来有点麻烦，表中如果已经有了很多数据，还需要利用update语句反转name到v_name中，如果数据量大了（几百万或上千万条记录）更新一下v_name耗时也比较长，同时也会增大表空间。

幸运的是在MySQL5.7.6之后，新增了虚拟列功能（如果不是>=5.7.6，只能用上面的土方法）。为一个列建立一个虚拟列，并为虚拟列建立索引，在查询时where中like条件改为虚拟列，就可以使用索引了。

ALTER TABLE `test` ADD COLUMN `v_name` VARCHAR(50) GENERATED ALWAYS AS (REVERSE(`name`)) VIRTUAL; //创建虚拟列
ALTER TABLE `test` ADD INDEX `idx_name_virt`(`v_name`); //为虚拟列v_name列添加索引

我们再进行查询，就会走索引了

EXPLAIN SELECT * FROM `test` WHERE `v_name` LIKE 'cba%';

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

当然如果你要查询like 'abc%'和like ‘%abc’，你只需要使用一个union

EXPLAIN SELECT * FROM `test` WHERE `v_name` LIKE 'cba%' //第一部分查询的是虚拟列
UNION SELECT * FROM `test` WHERE `name` LIKE 'abc%'; //第二部分查询的是原name列

+--------+--------------+------------+------------+-------+---------------+---------------+---------+--------+--------+----------+-----------------------+
| id     | select_type  | table      | partitions | type  | possible_keys | key           | key_len | ref    | rows   | filtered | Extra                 |
+--------+--------------+------------+------------+-------+---------------+---------------+---------+--------+--------+----------+-----------------------+
| 1      | PRIMARY      | test       | <null>     | range | idx_name_virt | idx_name_virt | 153     | <null> | 200    | 100.0    | Using where           |
| 2      | UNION        | test       | <null>     | range | idx_name      | idx_name      | 153     | <null> | 200    | 100.0    | Using index condition |
| <null> | UNION RESULT | <union1,2> | <null>     | ALL   | <null>        | <null>        | <null>  | <null> | <null> | <null>   | Using temporary       |