【MySQL数据库高级】索引单表优化案例

建表：

---

案例：

1.查询category_id 为1，且comments大于1的情况下views最多的article_id

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

comments>1使用后出现了索引失效，导致后面的order by 数据库又进行了filesort排序，性能太低，需要优化

建立索引：

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

删除索引：

DROP INDEX id_category_id_comments_views ON article;

优化：
建立索引：

CREATE INDEX id_category_id_views ON article (category_id,views);

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