MySQL查询优化–索引优化数据库weixin36979214的博客-

废话就不多说了，先建一张表

CREATE TABLE IF NOT EXISTS `article`( `id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,#文章id `author_id` INT (10) UNSIGNED NOT NULL, #作者id `category_id` INT(10) UNSIGNED NOT NULL , #分类id `views` INT(10) UNSIGNED NOT NULL , #阅读数 `comments` INT(10) UNSIGNED NOT NULL, #评论数 `title` VARBINARY(255) NOT NULL, #标题 `content` TEXT NOT NULL #内容 ); 

插入三条数据

INSERT INTO `article`(`author_id`,`category_id` ,`views` ,`comments` ,`title` ,`content` ) VALUES(1,1,1,1,'1','1'),(2,2,2,2,'2','2'),(3,1,1,3,'3','3'); 

查看下全表数据，就三条：

接着我们有这样一个需求：

查询分类id为1，评论数大于1，阅读数最多的文章的id和作者id，sql语句很简单，如下所示

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

结果是出来了，是我们想要的结果，但是别急，我们来查看下这条sql语句的性能如何

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

惊不惊喜？意不意外？ type=ALL是所有类型中最差的：全表查询。Using filesort， 文件外部排序。这两个放一起基本上这条SQL语句就废了，不用一百万条数据，一万条基本上就就可以看出性能下降了。

我们看一下索引情况：

SHOW INDEX FROM article; 

除了主键的索引外没有其他索引，所以这条SQL语句性能差也是在情理之中。

根据索引优化的经验，我们知道where 后面的字段，order by、group by 后面的字段，用于确定范围的字段等一般都需要加索引，所以我们先来创建一个复合索引

CREATE INDEX idx_ccv ON article(category_id, comments, views); SHOW INDEX FROM article; 

没有任何问题，创建成功，我们再看下刚刚那条SQL语句的查询性能如何

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

优化了一点点，但这并不是我们想要的结果，而且，最让人讨厌的文件外部排序filesort依然还在。我们不是创建了views的索引了吗？而且观察key=idx_ccv表示我们创建的索引也用上了，为什么性能还是贼烂呢？

分析一下，我们创建的复合索引:idx_ccv(category_id，comments，views)，在我们执行查询的时候，mysql会先根据category_id的索引找到category_id = 1的记录，然后紧接着根据comments进行范围查找，这里最多也就是个range级别，所以我们的优化就在这里断了，后面views的索引根本用不上，这也是导致了文件外部排序的直接原因。

那怎么优化呢？我们能不能改变一下复合索引的顺序呢？把views放前面一点。

DROP INDEX idx_ccv ON article; CREATE INDEX idx_cvc ON article(category_id,views, comments); EXPLAIN SELECT id, author_id  FROM article  WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 0, 1; 

果然！！！优化到这个程度其实还是可以的，至少文件外部排序的问题已经解决了，而且查询类型已经上升为ref了。

那有没有更优的解决方案呢，比如eq-ref呢？害,这个还是等DBA去优化吧