一、單表索引優(yōu)化

• 建表：

建一張表，SQL如下：

create table `tb_article`(
  `id` int not null primary key auto_increment comment '主鍵',
  `author_id` int not null comment '作者id',
  `category_id` int not null comment '文章類別id',
  `views` int not null comment '閱讀量',
  `comments` int not null comment '評論量',
  `title` varchar(200) not null comment '文章標(biāo)題',
  `content` text not null comment '文章內(nèi)容'
) comment '文章表';
 insert into tb_article(author_id,category_id, views, comments, title, content) values(1,1,1,1,1,1);
 insert into tb_article(author_id,category_id, views, comments, title, content) values(2,2,2,2,2,2);
 insert into tb_article(author_id,category_id, views, comments, title, content) values(1,1,3,3,3,3,);

• 需求：

查詢出 類別id為1 且 評論量大于1的情況下，閱讀量最多的那篇文章的作者id。

SQL寫法如下：

select id, author_id  
from tb_article 
where category_id = 1 and comments > 1 
order by views desc 
limit 1;

用explain分析一下，發(fā)現(xiàn)這條SQL問題很大：

首先沒有使用索引，type是all，然后用了文件內(nèi)排序，using filesort。這兩個都是嚴(yán)重影響性能的，那么接下來就建索引。

之前說過，where后面的字段，以及order by后面的字段，最好都要用索引，所以建立如下索引：create index idx_ccv on tb_article(category_id, comments, views);

即用這三個字段建立了一個復(fù)合索引。接下來再看上面那條查詢SQL的執(zhí)行計劃。

見鬼了，怎么還有using filesort呢？我排序字段不是建了索引了嗎？假如，把comment 大于1改成等于1，看看什么情況：

發(fā)現(xiàn)using filesort神奇地消失了。這是因為，comment大于1是一個范圍，而comment等于1是常量，范圍后面的索引是會失效的，即使用comment大于1的時候，order by后面根本沒用到索引，因為失效了。

那說明我們建的這個索引不太合適，干掉它：drop index idx_ccv on tb_article;

既然comment大于1會導(dǎo)致后面的索引失效，那如果繞開它，只對category_id和views建索引會怎樣呢？即create index idx_cv on tb_article(category_id, views);

可以看到，用到了索引，也沒有文件內(nèi)排序了。

• 結(jié)論：如果范圍查詢的字段跟其他字段一起建立了復(fù)合索引，那么范圍查詢字段后面字段的索引會失效。解決辦法可以繞過該字段。

二、兩表索引優(yōu)化

上面是單表，這里來看看連接查詢的情況。

• 建表：

create table `tb_novel`(
  `id` int not null primary key auto_increment comment '主鍵',
  `title` varchar(100) not null comment '小說名'
) comment '小說';

create table `tb_character`(
  `id` int not null primary key auto_increment comment '主鍵',
  `name` varchar(100) not null comment '人物名',
  `novel_id` int not null comment '歸屬于的小說id'
) comment '人物';

create table `tb_kongfu`(
  `id` int not null primary key auto_increment comment '主鍵',
  `kongfu_name` varchar(100) not null comment '功夫的名字',
  `novel_id` int not null comment '小說的id'
)comment '功夫';

insert into tb_novel(title)values('天龍八部');
insert into tb_novel(title)values('射雕英雄傳');
insert into tb_novel(title)values('神雕俠侶');
insert into tb_novel(title)values('倚天屠龍記');

insert into tb_character(name, novel_id) values('喬峰',1);
insert into tb_character(name, novel_id) values('掃地僧',1);
insert into tb_character(name, novel_id) values('洪七公',2);
insert into tb_character(name, novel_id) values('郭靖',2);
insert into tb_character(name, novel_id) values('金輪法王',3);
insert into tb_character(name, novel_id) values('小龍女',3);
insert into tb_character(name, novel_id) values('趙敏',4);
insert into tb_character(name, novel_id) values('滅絕老尼',4);

insert into tb_kongfu(kongfu_name, novel_id) values('北冥神功', 1);
insert into tb_kongfu(kongfu_name, novel_id) values('六脈神劍', 1);
insert into tb_kongfu(kongfu_name, novel_id) values('落英神劍掌', 2);
insert into tb_kongfu(kongfu_name, novel_id) values('北斗七星陣', 2);
insert into tb_kongfu(kongfu_name, novel_id) values('黯然銷魂掌', 3);
insert into tb_kongfu(kongfu_name, novel_id) values('龍翔般若功', 3);
insert into tb_kongfu(kongfu_name, novel_id) values('乾坤大挪移', 4);
insert into tb_kongfu(kongfu_name, novel_id) values('九陰白骨爪', 4);

• 需求：

查詢出屬于同一部小說的人物名和功夫的名字。

SQL寫法如下：

select c.name,f.kongfu_name from tb_character c left join tb_kongfu f on c.novel_id = f.novel_id;

差不多就這個意思，反正就是兩表連接，但是不用主鍵去關(guān)聯(lián)。

來看一下這條sql的執(zhí)行計劃：

可以看到，type都是all，因為我們并沒有建索引。我們是用novel_id連接的，那么，我是在tb_character表的novel_id上建索引還是在tb_kongfu表的novel_id上建索引呢？

首先在tb_character表的novel_id上建索引，create index idx_novel_id on tb_character(novel_id);，然后再查看執(zhí)行計劃。

可以看到，都是all，并沒有走索引。

那么我們把tb_character的索引刪除，drop index idx_novel_id on tb_character;，加在右表tb_kongfu上試試，create index idx_novel_id on tb_kongfu(novel_id);，再看執(zhí)行計劃：

可以看到，這次走索引了，首先左表是驅(qū)動表，左連接就是左邊都要查出來，所以左邊還是all，但是右邊是ref了。

我們不妨把tb_kongfu表的索引刪除，再把tb_character表的索引加上去，然后將left join改成right join，再看執(zhí)行計劃：

可以看到，也走了索引。

• 結(jié)論：左連接的時候索引應(yīng)該加在右表，右連接應(yīng)該加在左表。

三、三表索引優(yōu)化

三表和兩表其實沒什么差別，比如：

select * from A left join B on A.key = B.key left join C on A.key = C.key;

這里都是left join，且有三表，那么首先應(yīng)該在B表的key上加索引，A和B連接的結(jié)果看成是一個臨時表，再和C連接，因此C表的key也應(yīng)該加上索引。

四、exists和in

連接查詢的時候，永遠(yuǎn)要用小表驅(qū)動大表。比如下面的語句：

select * from A where id in (select id from B)

這條語句是先執(zhí)行select id from B的，然后用它去驅(qū)動A表的，當(dāng)B表的數(shù)據(jù)少于A表時，用in的效率是更高的。

再看這條語句：

select * from A where exists (select 1 from B where B.id = A.id)

這條語句呢是先執(zhí)行select * from A，然后用查出來的結(jié)果集去驅(qū)動B表的，當(dāng)A表的數(shù)據(jù)更少時，這樣寫的效率是比用in更高的。

五、優(yōu)化結(jié)論

• 連接查詢，永遠(yuǎn)要用小表驅(qū)動大表，即用數(shù)據(jù)少的表作為驅(qū)動表。比如A表數(shù)據(jù)很少，B表很多，要左連接的話，那么應(yīng)該是 A left join B。

• 優(yōu)先優(yōu)化嵌套循環(huán)(nested loop)的內(nèi)層循環(huán)。

• 保證join語句中被驅(qū)動表上的join條件字段加了索引。

• 無法保證被驅(qū)動表的join條件字段加索引且內(nèi)存充足的情況下，可以加大joinBuffer的設(shè)置。