索引分析
单表
创建表并插入数据
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的情况下view最多的author_id)
select id,author_id from article where category_id=1 and comments>1 order by views
desc limit 1;
sql分析
结果:type为All(变量全表)即最坏的情况,Extra里还出现了using filesor(文件排序)即使用一个外部的索引排序情况也是最坏所以必须优化。
新建索引
建立索引
create index idx_article_ccv on article(category_id,comments,views);
查看索引
show index from article;
sql分析
结果:Extra依旧出现了using filesor。
删除索引重建
drop index idx_article_ccv on article;
create index idx_article_cv on article(category_id,views);
show index article;
sql分析
原因:按照BTree索引的工作原理,先排序category_id,遇到相同的category_id
则再排序comments,如果遇到相同的comments则再排序views.
当comments字段在联合索引里处于中间位置时,
因comments>1条件是一个范围值(range),
mysql 无法利用索引再对后面的views部分进行索引,即range类型查询字段后面的索引无效。
双表
创建表并插入数据
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 book(card)values(FLOOR(1+(RAND()*20)));
insert into book(card)values(FLOOR(1+(RAND()*20)));
......
查询语句
select * from class left join book on class.card=book.card;
sql分析
结果:type有ALL添加索引优化。
右表添加索引
alter table book add index y(card);
sql分析
结果:type变为了ref。
左表添加索引
drop index y on book;
alter table class add index y(card);
sql分析
结果:可以看到type变为了ref,rows也变了优化比较明显.。
结论:左连接的时候left join 条件用于确定如何从右表搜索行,左表一定都有这是由左连接特性决定的,所以应该在右边建立索引;反之右连接应该在左表建立索引。
三表
创建表并插入数据
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)
);
create table if not exists phone(
phoneid int(10) unsigned not null auto_increment,
card int(10) unsigned not null,
primary key (phoneid)
)
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 phone(card)values(FLOOR(1+(RAND()*20)));
insert into phone(card)values(FLOOR(1+(RAND()*20)));
......
查询语句
select * from class left join book on class.card
=book.card left join phone on book.card=phone.card;
sql分析
结果:type全为ALL,需要优化。
建立索引
alter table phone add index z(card);
alter table book add index y(card)
sql分析
结果:后2行的type变为ref且总rows优化很好,效果不错。
结论:索引最好应设置在需要经常查询的字段中。