生产系统中遇见了一个like %XXX%的查询,工作中遇见过很多,但是没有实际测试过对它的优化。整理资料,总结常见的优化方法。
1、环境介绍
MySQL 8.0.16
2、对象信息介绍
MySQL [pion]>
MySQL [pion]> select count(*) from p_channel;
+----------+
| count(*) |
+----------+
| 7559141 |
+----------+
1 row in set (0.78 sec)
3、常见方法总结
(1)POSITION('substr' IN `field`)
(2)LOCATE('substr',str,pos)
(3)INSTR(`str`,'substr')
(4)mysql5.7以上的Generated Column功能
(1)STORED
(2)VIRTUAL
(5)添加一个新列存储该字段的反向值,进行sql修改
(1)修改业务完成该功能
(2)触发器完成该功能(不建议)
4、实际案例
业务SQL:
SELECT SQL_NO_CACHE `id` AS `value`, `channel_name` AS `label`
FROM `p_channel`
WHERE `channel_name` LIKE '%qm-qtt4800383%'
ORDER BY `id` DESC
LIMIT 100
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+
| 1 | SIMPLE | p_channel | NULL | index | NULL | PRIMARY | 4 | NULL | 100 | 11.11 | Using where; Backward index scan |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+
执行时间:
4061ms/3937ms/3906ms/3853ms
参考SQL:
SELECT SQL_NO_CACHE `id` AS `value`, `channel_name` AS `label`
FROM `p_channel`
WHERE `channel_name` LIKE 'qm-qtt4800383%'
ORDER BY `id` DESC
LIMIT 100;
执行计划:
+----+-------------+-----------+------------+-------+---------------------+--------------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type | possible_