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

 在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)
9. mysql> explain select count(*) from test03 where username like ‘%1%’;
10. +––––+–––––––––––––+––––––––+–––––––+–––––––––––––––+–––––––––––––––––+–––––––––+––––––+––––––––+––––––––––––––––––––––––––+
11. | id | select_type | table | type   | possible_keys | key             | key_len | ref  | rows   | Extra                    |
12. +––––+–––––––––––––+––––––––+–––––––+–––––––––––––––+–––––––––––––––––+–––––––––+––––––+––––––––+––––––––––––––––––––––––––+
13. | 1  | SIMPLE      | test03| index  | NULL          | idx_test03_name | 302     | NULL | 116500 | Using where; Using index |
14. +––––+–––––––––––––+––––––––+–––––––+–––––––––––––––+–––––––––––––––––+–––––––––+––––––+––––––––+––––––––––––––––––––––––––+
15. 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. +––––+–––––––––––––+––––––––––––+––––––––+–––––––––––––––+–––––––––+–––––––––+––––––+––––––+–––––––––––––+