MySQL--Like %XXX% 优化测试

最新推荐文章于 2024-06-03 15:13:08 发布

小鱼儿逛地球

最新推荐文章于 2024-06-03 15:13:08 发布

阅读量280

点赞数

分类专栏： SQL优化文章标签： mysql

本文链接：https://blog.csdn.net/m0_62754777/article/details/120746847

版权

生产系统中遇见了一个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_