MySQL 性能优化 - 单表查询优化

• 单表的数据量非常大的时候，在查询上面有时候性能也会很低，这个时候，需要进行单表的性能优化，例如新浪微博里面的博客、电商网站的商品等。

  下面针对单表进行案例说明，帮助我们很好的掌握单表数据查询的优化。

  首先，准备建表 SQL，以及数据，如下：

  #建表 SQL
  CREATE TABLE IF NOT EXISTS `test_article`(
  `id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `author_id` INT (10) UNSIGNED NOT NULL,
  `category_id` INT(10) UNSIGNED NOT NULL , 
  `views` INT(10) UNSIGNED NOT NULL , 
  `comments` INT(10) UNSIGNED NOT NULL,
  `title` VARBINARY(255) NOT NULL,
  `content` TEXT NOT NULL
  );
  
  #插入数据
  INSERT INTO `test_article`(`author_id`,`category_id` ,`views` ,`comments` ,`title` ,`content` )VALUES
  (1,1,1,1,'1','1'),
  (2,2,2,2,'2','2'),
  (3,3,3,3,'3','3');
  

• 案例一：查询 category_id 为 1 且 comments 大于 1的情况下，views 最多的article_id

  EXPLAIN SELECT id,author_id FROM test_article WHERE category_id = '1' and comments > 1 order by views desc limit 1;
  

  分析结果如下：
  通过上面的分析结果，可以看出，type类型是 ALL，并且 Extra 里面出现了 Using filesort ，此时性能极差，需要进行优化。

下面，我们来进行查询优化，只能先试着进行创建索引，逐步分析。（优化是一个比较漫长的过程，不可能一次性搞定的。）

==== 第一次优化 ====

#创建索引
create index idx_article_ccv on test_article(category_id,comments,views);

再次执行上面的查询 SQL，进行分析，查看结果如下：
通过上面的分析结果，可以看到，type由原来的 ALL 变为了 range ，性能明显上升，而且通过 key 来看，也使用到了我们新创建的索引，但是 Extra 里面，仍然存在 Using filesort ，这是不允许的。那么， 为什么创建了索引，还会存在这种问题呢？这就是之前说过的范围索引会导致后面的索引失效问题，也就是 comments > 1 这个条件，导致 order by views 这个没有用到索引，所以当排序的时候，MySQL 自己使用了文件内排序进行处理，也就是我们看到的 Using filesort。

很明显，第一次优化失败了，我们需要重新进行优化，删掉原来我们创建的索引，继续进行优化：

drop index idx_article_ccv on test_article;

==== 第二次优化 ====

通过第一次的索引尝试，我们发现，在 comments > 1 时，范围索引导致后面的排序失效，那么我们可不可以跳过这个条件，直接新建一个只有 category_id 和 views 的覆合索引呢？下面来进行尝试：

CREATE INDEX idx_article_cv on test_article(category_id,views);

再次执行 SQL：

EXPLAIN SELECT id,author_id FROM test_article WHERE category_id = '1' and comments > 1 order by views desc limit 1;

结果如下：
发现，type 变为了 ref，extra 里面的 Using filesort 也消失了，达到了我们的预期效果。

至此，我们的单表优化就完成了，可以看到，优化需要一步步的来进行分析和处理，尤其是在比较复杂的业务场景下，更是需要慢慢调整索引，来达到最佳优化效果。