mysql调优——sql优化(3):利用覆盖索引优化查询

浏览数：18 / 时间：2015年06月12日

概念简介

　　什么是覆盖索引？

　　就是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。使用覆盖索引要求：select后面的列在索引中。

　　举一个例子，我们知道mysql中的模糊查询是不会走索引的：

　　query 1，模糊查询：

　　> explain select sql_no_cache * from 表A where 列a LIKE ‘%兽医%‘ limit 10;

+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | 表A     | ALL  | NULL          | NULL | NULL    | NULL | 748521 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+

　　query 2，模糊查询，覆盖索引：

　　> explain select sql_no_cache id from 表A where 列a LIKE ‘%兽医%‘ limit 10;

+----+-------------+---------+-------+---------------+-----------+---------+------+--------+--------------------------+
| id | select_type | table   | type  | possible_keys | key       | key_len | ref  | rows   | Extra                    |
+----+-------------+---------+-------+---------------+-----------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | 表A     | index | NULL          | doc_title | 767     | NULL | 748521 | Using where; Using index |
+----+-------------+---------+-------+---------------+-----------+---------+------+--------+--------------------------+

　　同样的查询条件，只因为查询的内容不一样，就利用到了索引，原因就是，检索只在索引中进行；

　　注意这里的说法：检索只在索引中进行，而不是说走索引检索数据；这一点在查询计划里也有体现，possible_keys的值是null，但是key的值是doc_title。

　　那么为什么会在数据里检索，只在索引里检索？

　　简单说，索引的数据足够详细了，就不用去数据里找了。详细的内容需要去了解，statement在mysql中的运行过程，这里暂时先不做赘述。可以通过profile先对sql的执行过程有一个简单了解。

待优化sql：SELECT * FROM 表A as t WHERE title LIKE ‘%兽医%‘ AND category=‘3‘ ORDER BY t.bd_rank LIMIT 50;

查询计划：

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | t     | index | category      | bd_rank | 2       | NULL |  193 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

表中的索引状况，为了排版好看起见，我把为空的列暂时先删除：

> show index from 表A;

+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------------+---------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Index_type | Comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------------+---------+
|   表A |          0 | PRIMARY   |            1 | id          | A         |      769962 |     NULL | NULL   | BTREE      |         |
|   表A |          1 | idx_title |            1 | title       | A         |      769962 |     NULL | NULL   | BTREE      |         |
|   表A |          1 | category  |            1 | category    | A         |         150 |     NULL | NULL   | BTREE      |         |
|   表A |          1 | bd_rank   |            1 | bd_rank     | A         |         481 |     NULL | NULL   | BTREE      |         |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------------+---------+

对于这张表来说，索引创建还是比较合理的；注意到以下几点：

　（1）走了bd_rank索引而没有走category索引，为什么？mysql为什么这样选？

　　　　这个受cardinality列的影响。

　　　　cardinality值表示该列唯一值得个数，这个值并不准确，比如（2）的解答。这个值越大，就越容易选择这个列的索引。

　　　　有没有人问为什么不选cardinality值最大的列？哈哈，自己想想。

　　　　具体请参考 http://www.mysqlperformanceblog.com/2008/09/03/analyze-myisam-vs-innodb/

　（2）能不能用category和bd_rank建一个联合索引，会不会有更好的效果？

　　　　我们来查查category值得分布，发现只有4种值，因此不用建联合索引。

　　那么以上就是我们需要优化的sql对象了，我们排除了创建联合索引，弄清楚了为何走bd_rank索引，只剩下模糊查询带来的问题了。

　　用覆盖索引对这条sql做修改，如下：

　　> select t.* from se_rank t join (select id from se_rank where doc_title LIKE ‘%兽医%‘ and category=3) s on t.id=s.id ORDER BY `t`.`bd_rank` LIMIT 50;

　　注意：把条件category=3放在了括号内，想想有什么好处？

　　改完之后，对于这个关键字来讲，查询效率提高了8倍；

　　看看具体的查询计划：

+----+-------------+------------+--------+---------------+----------+---------+------+--------+---------------------------------+
| id | select_type | table      | type   | possible_keys | key      | key_len | ref  | rows   | Extra                           |
+----+-------------+------------+--------+---------------+----------+---------+------+--------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL     | NULL    | NULL |      9 | Using temporary; Using filesort |
|  1 | PRIMARY     | t          | eq_ref | PRIMARY       | PRIMARY  | 4       | s.id |      1 |                                 |
|  2 | DERIVED     | se_rank    | ref    | category      | category | 2       |      | 193802 | Using where                     |
+----+-------------+------------+--------+---------------+----------+---------+------+--------+---------------------------------+