温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

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

发布时间:2020-08-05 07:49:11 来源:ITPUB博客 阅读:199 作者:StevenBeijing 栏目:MySQL数据库
 在MySQL里,like'XXX%可以用到索引,但like '%XXX%'却不行,比如,以下这个案例:
 查看测试表行数:

点击(此处)折叠或打开

  1. mysql> select count(*) from test03;
  2. +----------+
  3. | count(*) |
  4. +----------+
  5. | 117584   |
  6. +----------+
  两次like匹配对比:

点击(此处)折叠或打开

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

  8. mysql> explain select count(*) from test03 where username like '%1%';
  9. +----+-------------+--------+-------+---------------+-----------------+---------+------+--------+--------------------------+
  10. | id | select_type | table | type   | possible_keys | key             | key_len | ref  | rows   | Extra                    |
  11. +----+-------------+--------+-------+---------------+-----------------+---------+------+--------+--------------------------+
  12. | 1  | SIMPLE      | test03| index  | NULL          | idx_test03_name | 302     | NULL | 116500 | Using where; Using index |
  13. +----+-------------+--------+-------+---------------+-----------------+---------+------+--------+--------------------------+
  14. 1 row in set (0.00 sec)
优化思路:
 这个测试表中,id是主键,叶子节点上保存了数据,从索引中就可以去到select的的id的列,不必读取数据行(只有select字段正好就是索引,那么就用到了覆盖索引),通过覆盖索引,减少I/O,提高性能。
 优化之前的执行计划:

点击(此处)折叠或打开

  1. mysql> explain select count(*) from test03 where username like '%1%';
  2. +----+-------------+--------+------+---------------+------+---------+------+------+-------------+
  3. | id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
  4. +----+-------------+--------+------+---------------+------+---------+------+------+-------------+
  5. | 1  | SIMPLE      | test03 | ALL  | NULL          | NULL | NULL    | NULL | 7164 | Using where |
  6. +----+-------------+--------+------+---------------+------+---------+------+------+-------------+
  优化之后的执行计划:

点击(此处)折叠或打开

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

向AI问一下细节

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

AI