MySQL高级：单表索引分析

目录

一、建表

二、单表索引分析

三、单表索引优化

四、总结

---

一、建表

建立表article

CREATE TABLE IF NOT EXISTS `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 `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');
 
SELECT * FROM ARTICLE;

表一览：

​

二、单表索引分析

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

EXPLAIN SELECT category_id FROM ARTICLE WHERE category_id = 1 and  comments > 1 ORDER BY views  DESC  LIMIT 1

SQL语句很好实现，不过经过分析看到type类型为ALL，且Extra为 Using filesoft

​

也就是说，走了全表扫描，且使用了外部的索引排序。

要优化这条SQL语句，首先我们需要为article表建立索引，不让它走全表扫描，为用到的这三个字段加上了复合索引。

create index idx_ccv on ARTICLE(category_id,comments,views);

索引已建好

​

再次分析查询语句，可以看到type类型为range，key显示我们已经用上了刚刚建立的索引。

回顾type类型的优劣：MySQL 高级：EXPLAIN 性能分析_Clawww的学习笔记-CSDN博客

​

但是 Using filesort的问题仍然存在。

​ type类型是range（只检索给定范围的行），是范围，我们可以推测可能是comments>1这个条件引起的，如果把comments>1改成comments =1，再执行分析可见type类型为ref了，并且ref为常量。

​

我们不可能要求改变业务查询逻辑，强行让查询comments>1变为查询comments=1，既然我们建立了复合索引，为什么没有生效呢？

这里引发的一个问题就是，MySQL存储引擎不能继续使用索引中范围条件（between，<，>，in）右边的列。

也就是说，category_id,comments,views 这三个字段建立的复合索引，走到comments>1这里时，索引就失效了，即使key列显示用到了inx_ccv。

当前的复合索引有个不适合用到索引的字段comments，它用到了范围查询，会导致索引失效的问题，所以我们需要进一步的优化索引。

三、单表索引优化

既然comments字段需要范围查询，那么我们绕过它来建立索引

DROP INDEX idx_ccv ON ARTICLE
CREATE INDEX idx_article_ccv ON ARTICLE(category_id,views);

此时再查询，可以看到type类型为ref了，并且ref用到了const，Extra没有了Using filesort

​

四、总结

第一次我们建立了三个字段的复合索引，解决了type由ALL变为range，但Extra里用到了Using filesort仍然是无法忍受，需要优化的。

为什么建立了索引却没有用到？

因为按照BTree索引的工作原理，先排序category_id，再排序comments，如果遇到相同的comments则再排序views，当comments字段在复合索引处于中间位置时，因为comments>1是一个范围值（range），MySQL无法利用索引再对后面的views进行检索，即range类型查询字段后面的索引无效。