索引单表优化
建表及插入数据:
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);