單表優(yōu)化

createtablebook
(
bidint(4)primarykey,
namevarchar(20)notnull,
authoridint(4)notnull,
publicidint(4)notnull,
typeidint(4)notnull
);

insertintobookvalues(1,'tjava',1,1,2);
insertintobookvalues(2,'tc',2,1,2);
insertintobookvalues(3,'wx',3,2,1);
insertintobookvalues(4,'math',4,2,3);
commit;

查詢authorid=1且typeid為2或3的bid
explainselectbidfrombookwheretypeidin(2,3)andauthorid=1orderbytypeiddesc;

(a,b,c)
(a,b)

優(yōu)化：加索引
altertablebookaddindexidx_bta(bid,typeid,authorid);

索引一旦進行升級優(yōu)化，需要將之前廢棄的索引刪掉，防止干擾。
dropindexidx_btaonbook;

根據(jù)SQL實際解析的順序，調(diào)整索引的順序：
altertablebookaddindexidx_tab(typeid,authorid,bid);--雖然可以回表查詢bid，但是將bid放到索引中可以提升使用usingindex;

再次優(yōu)化（之前是index級別）：思路。因為范圍查詢in有時會實現(xiàn)，因此交換索引的順序，將typeidin(2,3)放到最后。
dropindexidx_tabonbook;
altertablebookaddindexidx_atb(authorid,typeid,bid);
explainselectbidfrombookwhereauthorid=1andtypeidin(2,3)orderbytypeiddesc;

--小結(jié)：a.最佳做前綴，保持索引的定義和使用的順序一致性b.索引需要逐步優(yōu)化c.將含In的范圍查詢放到where條件的最后，防止失效。

本例中同時出現(xiàn)了Usingwhere（需要回原表）;Usingindex（不需要回原表）：原因，whereauthorid=1andtypeidin(2,3)中authorid在索引(authorid,typeid,bid)中，因此不需要回原表（直接在索引表中能查到）；而typeid雖然也在索引(authorid,typeid,bid)中，但是含in的范圍查詢已經(jīng)使該typeid索引失效，因此相當(dāng)于沒有typeid這個索引，所以需要回原表（usingwhere）；
例如以下沒有了In，則不會出現(xiàn)usingwhere
explainselectbidfrombookwhereauthorid=1andtypeid=3orderbytypeiddesc;

還可以通過key_len證明In可以使索引失效。

兩表優(yōu)化

createtableteacher2
(
tidint(4)primarykey,
cidint(4)notnull
);

insertintoteacher2values(1,2);
insertintoteacher2values(2,1);
insertintoteacher2values(3,3);

createtablecourse2
(
cidint(4),
cnamevarchar(20)
);

insertintocourse2values(1,'java');
insertintocourse2values(2,'python');
insertintocourse2values(3,'kotlin');
commit;

左連接：
explainselect*fromteacher2tleftouterjoincourse2c
ont.cid=c.cidwherec.cname='java';

索引往哪張表加？-小表驅(qū)動大表
-索引建立經(jīng)常使用的字段上（本題t.cid=c.cid可知，t.cid字段使用頻繁，因此給該字段加索引）[一般情況對于左外連接，給左表加索引；右外連接，給右表加索引]
小表：10
大表：300
where小表.x10=大表.y300;--循環(huán)了幾次？10

大表.y300=小表.x10--循環(huán)了300次

小表:10
大表:300

select...where小表.x10=大表.x300;
for(inti=0;i<小表.length10;i++)
{
for(intj=0;j<大表.length300;j++)
{
...
}
}

select...where大表.x300=小表.x10;
for(inti=0;i<大表.length300;i++)
{
for(intj=0;j<小表.length10;j++)
{
...
}
}

--以上2個FOR循環(huán)，最終都會循環(huán)3000次；但是對于雙層循環(huán)來說：一般建議將數(shù)據(jù)小的循環(huán)放外層；數(shù)據(jù)大的循環(huán)放內(nèi)存。

--當(dāng)編寫..ont.cid=c.cid時，將數(shù)據(jù)量小的表放左邊（假設(shè)此時t表數(shù)據(jù)量?。?/p>

altertableteacher2addindexindex_teacher2_cid(cid);
altertablecourse2addindexindex_course2_cname(cname);

Usingjoinbuffer:extra中的一個選項，作用：Mysql引擎使用了連接緩存。

三張表優(yōu)化

ABC
a.小表驅(qū)動大表b.索引建立在經(jīng)常查詢的字段上

示例：
createtabletest03
(
a1int(4)notnull,
a2int(4)notnull,
a3int(4)notnull,
a4int(4)notnull
);
altertabletest03addindexidx_a1_a2_a3_4(a1,a2,a3,a4);

explainselecta1,a2,a3,a4fromtest03wherea1=1anda2=2anda3=3anda4=4;--推薦寫法，因為索引的使用順序（where后面的順序）和復(fù)合索引的順序一致

explainselecta1,a2,a3,a4fromtest03wherea4=1anda3=2anda2=3anda1=4;--雖然編寫的順序和索引順序不一致，但是sql在真正執(zhí)行前經(jīng)過了SQL優(yōu)化器的調(diào)整，結(jié)果與上條SQL是一致的。
--以上2個SQL，使用了全部的復(fù)合索引

explainselecta1,a2,a3,a4fromtest03wherea1=1anda2=2anda4=4orderbya3;
--以上SQL用到了a1a2兩個索引，該兩個字段不需要回表查詢usingindex;而a4因為跨列使用，造成了該索引失效，需要回表查詢因此是usingwhere；以上可以通過key_len進行驗證

explainselecta1,a2,a3,a4fromtest03wherea1=1anda4=4orderbya3;
--以上SQL出現(xiàn)了usingfilesort(文件內(nèi)排序，“多了一次額外的查找/排序”)：不要跨列使用(where和orderby拼起來，不要跨列使用)

explainselecta1,a2,a3,a4fromtest03wherea1=1anda4=4orderbya2,a3;--不會usingfilesort

--總結(jié)：i.如果(a,b,c,d)復(fù)合索引和使用的順序全部一致(且不跨列使用)，則復(fù)合索引全部使用。如果部分一致(且不跨列使用)，則使用部分索引。
selecta,cwherea=andb=andd=
ii.where和orderby拼起來，不要跨列使用

usingtemporary:需要額外再多使用一張表.一般出現(xiàn)在groupby語句中；已經(jīng)有表了，但不適用，必須再來一張表。
解析過程：
from..on..join..where..groupby....having...selectdinstinct..orderbylimit...
a.
explainselectfromtest03wherea2=2anda4=4groupbya2,a4;--沒有usingtemporary
b.
explainselectfromtest03wherea2=2anda4=4groupbya3;

避免索引失效的一些原則

（1）復(fù)合索引
a.復(fù)合索引，不要跨列或無序使用（最佳左前綴）
(a,b,c)
b.復(fù)合索引，盡量使用全索引匹配
(a,b,c)
（2）不要在索引上進行任何操作（計算、函數(shù)、類型轉(zhuǎn)換），否則索引失效
select..whereA.x=..;--假設(shè)A.x是索引
不要：select..whereA.x3=..;
explainselectfrombookwhereauthorid=1andtypeid=2;--用到了at2個索引
explainselectfrombookwhereauthorid=1andtypeid2=2;--用到了a1個索引
explainselectfrombookwhereauthorid2=1andtypeid2=2;----用到了0個索引
explainselectfrombookwhereauthorid*2=1andtypeid=2;----用到了0個索引,原因：對于復(fù)合索引，如果左邊失效，右側(cè)全部失效。(a,b,c)，例如如果b失效，則bc同時失效。

dropindexidx_atbonbook;
altertablebookaddindexidx_authroid(authorid);
altertablebookaddindexidx_typeid(typeid);
explainselectfrombookwhereauthorid2=1andtypeid=2;
（3）復(fù)合索引不能使用不等于（!=<>）或isnull(isnotnull)，否則自身以及右側(cè)所有全部失效。
復(fù)合索引中如果有>，則自身和右側(cè)索引全部失效。

explainselect*frombookwhereauthorid=1andtypeid=2;

--SQL優(yōu)化，是一種概率層面的優(yōu)化。至于是否實際使用了我們的優(yōu)化，需要通過explain進行推測。

explainselectfrombookwhereauthorid!=1andtypeid=2;
explainselectfrombookwhereauthorid!=1andtypeid!=2;

體驗概率情況(<>=)：原因是服務(wù)層中有SQL優(yōu)化器，可能會影響我們的優(yōu)化。
dropindexidx_typeidonbook;
dropindexidx_authroidonbook;
altertablebookaddindexidx_book_at(authorid,typeid);
explainselectfrombookwhereauthorid=1andtypeid=2;--復(fù)合索引at全部使用
explainselectfrombookwhereauthorid>1andtypeid=2;--復(fù)合索引中如果有>，則自身和右側(cè)索引全部失效。
explainselectfrombookwhereauthorid=1andtypeid>2;--復(fù)合索引at全部使用
----明顯的概率問題---
explainselectfrombookwhereauthorid<1andtypeid=2;--復(fù)合索引at只用到了1個索引
explainselect*frombookwhereauthorid<4andtypeid=2;--復(fù)合索引全部失效

--我們學(xué)習(xí)索引優(yōu)化，是一個大部分情況適用的結(jié)論，但由于SQL優(yōu)化器等原因該結(jié)論不是100%正確。
--一般而言，范圍查詢（><in），之后的索引失效。

（4）補救。盡量使用索引覆蓋（usingindex）
（a,b,c）
selecta,b,cfromxx..wherea=..andb=..;

(5)like盡量以“常量”開頭，不要以'%'開頭，否則索引失效
select*fromxxwherenamelike'%x%';--name索引失效

explainselect*fromteacherwheretnamelike'%x%';--tname索引失效

explainselect*fromteacherwheretnamelike'x%';

explainselecttnamefromteacherwheretnamelike'%x%';--如果必須使用like'%x%'進行模糊查詢，可以使用索引覆蓋挽救一部分。

（6）盡量不要使用類型轉(zhuǎn)換（顯示、隱式），否則索引失效
explainselectfromteacherwheretname='abc';
explainselectfromteacherwheretname=123;//程序底層將123->'123'，即進行了類型轉(zhuǎn)換，因此索引失效

（7）盡量不要使用or，否則索引失效
explainselect*fromteacherwheretname=''ortcid>1;--將or左側(cè)的tname失效。

一些其他的優(yōu)化方法

（1）
exist和in
select..fromtablewhereexist(子查詢);
select..fromtablewhere字段in(子查詢);

如果主查詢的數(shù)據(jù)集大，則使用In,效率高。
如果子查詢的數(shù)據(jù)集大，則使用exist,效率高。

exist語法：將主查詢的結(jié)果，放到子查需結(jié)果中進行條件校驗（看子查詢是否有數(shù)據(jù)，如果有數(shù)據(jù)則校驗成功），
如果復(fù)合校驗，則保留數(shù)據(jù)；

selecttnamefromteacherwhereexists(select*fromteacher);
--等價于selecttnamefromteacher

selecttnamefromteacherwhereexists(select*fromteacherwheretid=9999);

in:
select..fromtablewheretidin(1,3,5);

（2）orderby優(yōu)化
usingfilesort有兩種算法：雙路排序、單路排序（根據(jù)IO的次數(shù)）
MySQL4.1之前默認(rèn)使用雙路排序；雙路：掃描2次磁盤（1：從磁盤讀取排序字段,對排序字段進行排序（在buffer中進行的排序）2：掃描其他字段）
--IO較消耗性能
MySQL4.1之后默認(rèn)使用單路排序：只讀取一次（全部字段），在buffer中進行排序。但種單路排序會有一定的隱患（不一定真的是“單路|1次IO”，有可能多次IO）。原因：如果數(shù)據(jù)量特別大，則無法將所有字段的數(shù)據(jù)一次性讀取完畢，因此會進行“分片讀取、多次讀取”。
注意：單路排序比雙路排序會占用更多的buffer。
單路排序在使用時，如果數(shù)據(jù)大，可以考慮調(diào)大buffer的容量大?。簊etmax_length_for_sort_data=1024單位byte

如果max_length_for_sort_data值太低，則mysql會自動從單路->雙路（太低：需要排序的列的總大小超過了max_length_for_sort_data定義的字節(jié)數(shù)）

提高orderby查詢的策略：
a.選擇使用單路、雙路；調(diào)整buffer的容量大??；
b.避免select*...
c.復(fù)合索引不要跨列使用，避免usingfilesort
d.保證全部的排序字段排序的一致性（都是升序或降序）