mysql-单表索引优化

单表索引，来源尚硅谷周阳老师，https://www.bilibili.com/video/av49181542?p=211

• 样例数据创建

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

• 测试步骤

show index from article;

当前索引.png

在这里，只有主键索引，

explain select id,author_id from article where category_id = 1 and comments >1 order by views desc limit 1;

image.png

使用了FullTable Scan,将遍历全表以找到匹配的行，并且extra使用了using filesort，效率低下，需要进行优化

• 方案一

create index idx_article_ccv on article(category_id,comments,views);
并执行分析：
explain select id,author_id from article where category_id = 1 and comments >1 order by views desc limit 1;

image.png

1. 效率有所提升，到range，但是filesort仍然存在，
2. 只使用了idx_article_ccv部分索引，即category_id部分，comments 部分失效并连带着后面部分。

注释：是如果遇到相同的 category_id 则再排序 comments, 如果遇到相同的 comments 则再排序 views。当 comments 字段在联合索引里处于中间位置时, 因comments > 1 条件是一个范围值(所谓range)
MySQL 无法利用索引再对后面的 views 部分进行检索, 即range 类型查询字段后面的索引无效。

现在需要越过comments 。

• 方案二

创建category_id，和views字段的索引

drop index idx_article_ccv on article;
//新的索引
create index idx_article_cv on article(category_id,views);
//索引分析
explain select id,author_id from article where category_id = 1 and comments >1 order by views desc limit 1;

image.png

索引类型提高到了ref，避免了索引失效，并去除了filesort。