模式匹配like'%XXX%'优化
在MySQL里,like'XXX%可以用到索引,但like '%XXX%'却不行,比如,以下这个案例:
查看测试表行数:
两次like匹配对比:
优化思路:
这个测试表中,id是主键,叶子节点上保存了数据,从索引中就可以去到select的的id的列,不必读取数据行(只有select字段正好就是索引,那么就用到了覆盖索引),通过覆盖索引,减少I/O,提高性能。
优化之前的执行计划:
优化之后的执行计划:
网站栏目:模式匹配like'%XXX%'优化
本文来源:http://ybzwz.com/article/ghjjcg.html
查看测试表行数:
点击(此处)折叠或打开
我们提供的服务有:网站建设、网站设计、微信公众号开发、网站优化、网站认证、铜仁ssl等。为上1000+企事业单位解决了网站和推广的问题。提供周到的售前咨询和贴心的售后服务,是有科学管理、有技术的铜仁网站制作公司
-
mysql> select count(*) from test03;
-
+----------+
-
| count(*) |
-
+----------+
-
| 117584 |
- +----------+
点击(此处)折叠或打开
-
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 |
| 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 |
- +----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+
网站栏目:模式匹配like'%XXX%'优化
本文来源:http://ybzwz.com/article/ghjjcg.html