目录
一、建表
建立表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类型查询字段后面的索引无效。