mysql 索引类型案例_MySQL高级（三）——索引单表优化案例

MySQL高级(三)——索引单表优化案例

#创建表格

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'),

(1,1,3,3,'3','3');

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

SELECT id,author_id FROM article WHERE category_id=1 AND comments>1 ORDER BY views DESC LIMIT 1;

#性能分析

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，虽然这条语句能够查询出来结果，但是很消耗行性能，需要优化。

(1)查看索引

show index from article;

(2)开始优化

方式一：新建索引+删除索引

创建索引

create index idx_article_ccv on article(`category_id`,`comments`,`views`);

删除索引

drop index idx_article_ccv on article

这里我们已经建立了索引，为什么没有用？

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

方式二(正确)

create index idx_article_cv on article(category_id,views);