索引优化
单表优化过程
## 建表语句
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 varchar(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'),
(3,3,3,3,'3','3');
需求: 查询category_id=2且comments大于1情况下,views最多的author_id
mysql> select author_id from article where category_id=2 and comments>1 order by views desc limit 1;
+-----------+
| author_id |
+-----------+
| 2 |
+-----------+
1 row in set (0.00 sec)
看一下sql的执行计划
type=ALL全表扫码 效率不高,而且Extra出现了Using fileSort 也是最坏的情况 需要优化
添加索引
create index idx_article_ccv on article(category_id,comments,views);
重新执行查询计划
type变成了range,但是extra里使用using filesort仍然是无法接受的。
但是我们已经建立了索引了,为什么没起作用呢?
这是因为按照BTree索引的工作原理,先排序category_id,如果遇到相同的category_id则再排序comments。如果遇到相同的comments则再排序views,当comments字段在联合索引中处于中间位置时,因comments>1条件是一个范围,MySQL无法利用索引再对后面的views部分进行检索,即range类型查询字段后面的索引无效
删除索引,重新建立一个索引
drop index idx_article_ccv on article;
create index idx_article_cv on article(category_id,views);
重新查看执行计划
发现索引类型是ref了,且没有文件内排序了。
多表优化过程
建表并插入数据
create table if not exists `class`(
id int(10) unsigned not null auto_increment,
card int(10) unsigned not null,
primary key(id)
);
create table if not exists `book`(
bookid int(10) unsigned not null auto_increment,
card int(10) unsigned not null,
primary key(bookid)
);
insert into class(card) values(floor(1+(rand()*20)));
insert into class(card) values(floor(1+(rand()*20)));
insert into class(card) values(floor(1+(rand()*20)));
insert into class(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
下面进行explain分析并对该语句进行优化
explain select * from class left join book on class.card=book.card;
发现type全是All,需要优化
添加索引优化
alter table book add index Y(`card`);
再次explain分析
可以看到第二行的type变成了ref,rows也变化了,优化比较明显。
这是由于左连接特性决定的,left join条件用于确定如果从右表搜索行,左边一定都有。
结论:左连接,索引加到右边的表。同理,右连接,索引加到左边的表
总结
join语句的优化,应尽可能的减少join语句中的NestedLoop的循环总次数,永远用小结果集驱动大的结果集。
保证join语句中被驱动表上join条件字段已经被索引,当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下,不要吝惜joinbuffer的设置。(join会使用到joinbuffer,当连接数据比较多,超过joinbuffer的容量时,会分多次来处理,所带来的磁盘IO也是不小的消耗)