索引优化--单表

Mysql 系列文章主页 

===============

一张表查询的索引优化

1 环境准备

1.1 创建文章表（类似于此处的博客）：

drop TABLE if EXISTS article;
create table if not EXISTS `article` (
    id int PRIMARY KEY auto_increment,
    author_id int,
    category_id int,
    comments int,
    views int
);

1.2 插入两条数据：

INSERT INTO article(author_id, category_id, comments, views) values(1001, 1, 44, 55555);
INSERT INTO article(author_id, category_id, comments, views) values(1002, 1, 55, 66666);

1.3 需求：查询 category_id=1 且 comments>1 的情况下，views 最多的 author_id

2 实现

2.1 查询Sql：

SELECT id, author_id FROM article where category_id = 1 and comments > 1 ORDER BY views desc limit 1;

2.2 执行结果：

3 Explain分析

EXPLAIN SELECT id, author_id FROM article where category_id = 1 and comments > 1 ORDER BY views desc limit 1;

分析结果：

可以看到，type=all 且 Using filesort！危险！

4 索引优化

4.1 第一次建立索引

既然查询条件和OrderBy中使用到了 category_id, comments, views 三个字段，于是，尝试对这三个字段建立索引

create index idx_ccv on article(category_id, comments, views);

再次Explain：

Explain SELECT id, author_id FROM article where category_id = 1 and comments > 1 ORDER BY views desc limit 1;

结果如下：

可以看到，type=range 比之前的好，但是还是有 Using filesort！需要继续优化！

既然 type=range ，那就表明这是一个范围内查询，而范围后的索引会失效，于是考虑，不对这个范围建立索引。

4.2 第二次建立索引

这次不对 comments 字段建立索引

drop index idx_ccv on article;
create index idx_cv on article(category_id, views);

再次Explain：

Explain SELECT id, author_id FROM article where category_id = 1 and comments > 1 ORDER BY views desc limit 1;

结果如下：

可以看到，type=ref 这比之前的 all & range 都好，且没有 Using filesort！

于是，索引优化成功。

5 总结

范围之后索引失效，所以，建立索引时，如果存在范围查询，可不对对应字段建立索引。