索引优化--单表

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 总结

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

posted @ 2018-04-24 14:28  cyhbyw  阅读(248)  评论(0编辑  收藏  举报