本文不涉及復(fù)雜的底層數(shù)據(jù)結(jié)構(gòu)，通過explain解釋SQL，并根據(jù)可能出現(xiàn)的情況，來做具體的優(yōu)化，使百萬級、千萬級數(shù)據(jù)表關(guān)聯(lián)查詢第一頁結(jié)果能在2秒內(nèi)完成(真實業(yè)務(wù)告警系統(tǒng)優(yōu)化結(jié)果)。希望讀者能夠理解SQL的執(zhí)行過程，并根據(jù)過程優(yōu)化，走上自己的"成金之路"
需要優(yōu)化的查詢：使用explain
出現(xiàn)了Using temporary；
有分頁時出現(xiàn)了Using filesort則表示使用不了索引，需要根據(jù)下面的技巧來調(diào)整語句
rows過多，或者幾乎是全表的記錄數(shù)；
key 是 (NULL)；
possible_keys 出現(xiàn)過多（待選）索引。
1.使用explain語法，對SQL進(jìn)行解釋，根據(jù)其結(jié)果進(jìn)行調(diào)優(yōu)：
MySQL 表關(guān)聯(lián)的算法是 Nest Loop Join，是通過驅(qū)動表的結(jié)果集作為循環(huán)基礎(chǔ)數(shù)據(jù)，然后一條一條地通過該結(jié)果集中的數(shù)據(jù)作為過濾條件到下一個表中查詢數(shù)據(jù)，然后合并結(jié)果：
a.EXPLAIN 結(jié)果中，第一行出現(xiàn)的表就是驅(qū)動表
b.對驅(qū)動表可以直接排序，對非驅(qū)動表（的字段排序）需要對循環(huán)查詢的合并結(jié)果（臨時表）進(jìn)行排序（Important!），即using temporary;
c. [驅(qū)動表] 的定義為：1）指定了聯(lián)接條件時，滿足查詢條件的記錄行數(shù)少的表為[驅(qū)動表]；2）未指定聯(lián)接條件時，行數(shù)少的表為[驅(qū)動表]（Important!）。
d.優(yōu)化的目標(biāo)是盡可能減少JOIN中Nested Loop的循環(huán)次數(shù)，以此保證：永遠(yuǎn)用小結(jié)果集驅(qū)動大結(jié)果集（Important!）！：A JOIN B，A為驅(qū)動，A中每一行和B進(jìn)行循環(huán)JOIN，看是否滿足條件，所以當(dāng)A為小結(jié)果集時，越快。
e.NestedLoopJoin實際上就是通過驅(qū)動表的結(jié)果集作為循環(huán)基礎(chǔ)數(shù)據(jù)，然后一條一條的通過該結(jié)果集中的數(shù)據(jù)作為過濾條件到下一個表中查詢數(shù)據(jù)，然后合并結(jié)果。如果還有第三個參與Join，則再通過前兩個表的Join結(jié)果集作為循環(huán)基礎(chǔ)數(shù)據(jù)，再一次通過循環(huán)查詢條件到第三個表中查詢數(shù)據(jù)，如此往復(fù)
2.兩表JOIN優(yōu)化：
a.當(dāng)無order by條件時，根據(jù)實際情況，使用left/right/inner join即可，根據(jù)explain優(yōu)化 ；
b.當(dāng)有order by條件時，如select * from a inner join b where 1=1 and other condition order by a.col；使用explain解釋語句；
1）如果第一行的驅(qū)動表為a，則效率會非常高，無需優(yōu)化；
2）否則，因為只能對驅(qū)動表字段直接排序的緣故，會出現(xiàn)using temporary，所以此時需要使用STRAIGHT_JOIN明確a為驅(qū)動表，來達(dá)到使用a.col上index的優(yōu)化目的；或者使用left join且Where條件中不含b的過濾條件，此時的結(jié)果集為a的全集，而STRAIGHT_JOIN為inner join且使用a作為驅(qū)動表
3.多表JOIN優(yōu)化：
a.無order by條件時，根據(jù)實際情況，使用left/right/inner join即可，根據(jù)explain優(yōu)化；
b.有order by a.col條件時，所有join必須為left join，且每個join字段都創(chuàng)建索引，同時where條件中只能有a表的條件，即將其它表的數(shù)據(jù)關(guān)聯(lián)到a中形成一張大表，再對a的全集進(jìn)行過濾；
如果不能全使用left join，則需靈活使用STRAIGHT_JOIN及其它技巧，以時間排序為例：
1)數(shù)據(jù)入庫按照平臺時間入庫，自然a的數(shù)據(jù)都按時間有序；
SELECT c.*, r.HYPERVISOR_HOST_NAME hostname, r.HOST_IP FROM trust_monitor c STRAIGHT_JOIN res_node r ON c.res_node_id = r.ID STRAIGHT_JOIN am_assets a ON r.ASSET_ID = a.ID AND a.status = 58 STRAIGHT_JOIN se_role s ON a.DEPT_FLAG = s.ROLE_ORG AND s.ROLE_ID IN (32,33,36,41) where c.STATUS = 58 and c.changed_type = 79 limit 1,10;
SELECT c.*, r.HYPERVISOR_HOST_NAME hostname, r.HOST_IP FROM trust_monitor c inner JOIN res_node r ON c.res_node_id = r.ID INNER JOIN am_assets a ON r.ASSET_ID = a.ID AND a.status = 58 INNER JOIN se_role s ON a.DEPT_FLAG = s.ROLE_ORG AND s.ROLE_ID IN (32,33,36,41) where c.STATUS = 58 and c.changed_type = 79 order by c.changed_time limit 1,10;
兩者結(jié)果一致
4.誤區(qū)：
a.視圖只是屏蔽或者高效集合多表數(shù)據(jù)的一種方法，視圖與表JOIN，不會起到任何效果
參考：
