模式匹配like'%XXX%'优化

发布时间：2020-08-05 07:49:11 作者：StevenBeijing
来源：ITPUB博客阅读：212

在MySQL里，like'XXX%可以用到索引，但like '%XXX%'却不行，比如，以下这个案例：
查看测试表行数：

点击(此处)折叠或打开

mysql> select count(*) from test03;
+----------+
| count(*) |
+----------+
| 117584 |
+----------+

两次like匹配对比：

点击(此处)折叠或打开

mysql> explain select count(*) from test03 where username like '1%';
+----+-------------+--------+-------+-----------------+-----------------+---------+------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+-----------------+-----------------+---------+------+-------+--------------------------+
| 1 | SIMPLE | test03 | range | idx_test03_name | idx_test03_name | 302 | NULL | 58250 | Using where; Using index |
+----+-------------+--------+-------+-----------------+-----------------+---------+------+-------+--------------------------+
1 row in set (0.03 sec)
mysql> explain select count(*) from test03 where username like '%1%';
+----+-------------+--------+-------+---------------+-----------------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+-----------------+---------+------+--------+--------------------------+
| 1 | SIMPLE | test03| index | NULL | idx_test03_name | 302 | NULL | 116500 | Using where; Using index |
+----+-------------+--------+-------+---------------+-----------------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)

优化思路：
这个测试表中，id是主键，叶子节点上保存了数据，从索引中就可以去到select的的id的列，不必读取数据行（只有select字段正好就是索引，那么就用到了覆盖索引），通过覆盖索引，减少I/O，提高性能。
优化之前的执行计划：

点击(此处)折叠或打开

mysql> explain select count(*) from test03 where username like '%1%';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test03 | ALL | NULL | NULL | NULL | NULL | 7164 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+

优化之后的执行计划：

点击(此处)折叠或打开

mysql> explain select count(*) from test03 a join (select id from test03 where username like '%1%') b on a.id=b.id;
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+
| 1 | PRIMAR | <derived2> | ALL | NULL | NULL | NULL | NULL | 7164 | NULL |
| 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 8 | b.id | 1 | Using index |
| 2 | DERIVED | test03 | ALL | NULL | NULL | NULL | NULL | 7164 | Using where |
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+

推荐阅读：

免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。

模式 %xxx%

上一篇：Python中的LEGB规则

下一篇：scala相关介绍

相关阅读

您好，登录后才能下订单哦！

密码登录

忘记密码？

登录注册

获取短信验证码

其他方式登录

点击登录注册即表示同意《亿速云用户服务条款》

行业资讯-文章归档问答-问答归档