mysql 单表索引优化案例

#查询category_id为1且comments大于1 的情况下，views最多的article_id（article 表只有主键id索引）

EXPLAN  SELECT id ,author_id FROM article WHERE category_id=1 AND comments>1 ORDER BY views DESC LIMIT 1;

+----+-------------+-------+------+---------------+------+---------+------+------+-------+------+-------+------+-------+------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                      |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+------+-------+------+-------+
|  1 | SIMPLE      | article | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+------+-------+------+-------+------+

#结论：type为ALL，Extra存在Using filesort，情况较坏，需要优化

1、加索引

CREATE INDEX idx_article_ccv ON article(category_id,comments,views);

+----+-------------+-------+------+---------------+------+---------+------+------+-------+------+-------+------+-------+------+
| id | select_type | table | type       | possible_keys  | key                  | key_len | ref  | rows       | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+------+-------+------+-------+
|  1 | SIMPLE      | article | range  | idx_article_ccv |idx_article_ccv | 8| NULL |  6 | Using where; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+------+-------+------+-------+------+

#依然存在Using filesort

#原因：因为按照BTree索引的工作原理，

#先排序category_id，

#如果category_id相同，再排序comments，如果comments相同，则排序views。

#当comments字段在联合索引里处于中间位置时，

#因为comments>1条件是一个范围值，

#mysql无法利用索引再对后边的views部分进行检索，即range类型查询字段后面的索引失效。

2、删除1中索引，增加新的索引

DROP INDEX idx_article_ccv ON article;

CREATE INDEX idx_article_cv ON article(category_id,views);

+----+-------------+-------+------+---------------+------+---------+------+------+-------+------+-------+------+-------+------+
| id | select_type | table | type | possible_keys  | key                | key_len | ref    | rows       | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+------+-------+------+-------+
|  1 | SIMPLE      | article | ref|  idx_article_cv  | idx_article_cv | 4           |const  |  2          | Using where|
+----+-------------+-------+------+---------------+------+---------+------+------+-------+------+-------+------+-------+------+