一、索引分析
1、单表
建表SQL
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,`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');
select *from article;
查询category_id为1且comments大于1的情况下,views最多的article_id
explain select id,author_id from article where category_id =1 and comments > 1 order by views desc limit 1;
结论:很显然,type是ALL,即最坏的情况,Extra里还出现了Using filesort。也是最坏的,优化是必须的。
开始优化:
(1)于是创建索引,最进本思路,where后面涉及字段全加上索引:
create index idx_article_ccv on article(category_id, comments,views);
show index from article;
重新查询一次
explain select id,author_id from article where category_id =1 and comments > 1 order by views desc limit 1;
为什么范围会导致索引失效?comments>1这个会导致索引失效
详情如下:
(2)解决办法:既然comments字段是一个敢为,因此加索引的时候不加上这个字段即可
[1]删除第一次建立的索引
drop index idx_article_ccv on article;
[2]第2次新建索引
#alter table `article` add index idx_article_cv(`category_id`,`views`);
create index idx_article_cv on article(category_id, views);
[3]第三次explain
explain select id,author_id from article where category_id =1 and comments > 1 order by views desc limit 1;
结论:可以看大type变为了ref,Extra中的using filesort也消失了,结果非常理想。
2、两表
建表SQL
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)); #执行20次
insert into book(card) values(floor(1+rand()*20)); #执行20次
(1)下面开始explain分析
explain select *from class left join book on class.card = book.card;
结论:type有ALL
(2)添加索引优化
尝试建索引,book表建索引Y,用在card字段
alter table book add index Y(card);
drop index Y on book;
alter table class add index Y(card);
删除book的索引Y,在class表建索引Y
3.三表
多加一个phone表
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 phone(card) values(1+rand()*20); # 执行20次
三表left join查询
explain select *from class left join book on class.card = book.card left join phone on class.card = phone.card;
根据sql,应该在book表和phone表的card字段加索引
create index Y on book(card);
alter table phone add index Z(card);