在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 |
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+
