索引分析

索引分析

單表

創建表並插入數據

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優化很好，效果不錯。
結論：索引最好應設置在需要經常查詢的欄位中。