MySQL--索引单表优化案例

索引单表优化

建表及插入数据：

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`)
VALUE (1,1,1,1,'1','1'),
			(2,2,2,2,'2','2'),
			(1,1,3,3,'3','3');

案例一

查询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 DESC
LIMIT 1;

结论：很显然，type是ALL全表扫描，即最坏的情况。Extra里还出现了Using filesort，也是最坏的情况，必须优化。

开始优化：
使用了3列，所以在3个列上创建符合索引

CREATE INDEX idx_article_ccv on article(category_id,comments,views);

删除索引再次优化：(绕过范围值)

DROP INDEX idx_article_ccv ON article;
CREATE INDEX idx_article_cv ON article(category_id, views);