#查询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|
+----+-------------+-------+------+---------------+------+---------+------+------+-------+------+-------+------+-------+------+