舉凡後端面試，面試官不言資料庫則已，言則必稱SQL優化，說起SQL優化，網絡上各種“指南”和“聖經”難以枚舉，不一而足，仿佛SQL優化已然是婦孺皆知的理論常識，然後根據多數無知（Pluralistic ignorance）理論，人們印象裡覺得多數人會怎麼想怎麼做，但這種印象往往是不準确的。那SQL優化到底應該怎麼做？本次讓我們褪去SQL華麗的軀殼，以最淺顯，最粗俗，最下裡巴人的方式講解一下SQL優化的前因後果，前世今生。
SQL優化背景
首先要明确一點，SQL優化不是為了優化而優化，就像冬天要穿羽絨服，不是因為有羽絨服或者羽絨服本身而穿，是因為天兒太冷了！那SQL優化的原因是什麼？是因為SQL語句太慢了！從廣義上講，SQL語句包含增删改查，但一般的業務場景下，SQL的讀寫比例應該是一比十左右，而且寫操作很少出現性能問題，即使出現，大多數也是慢查詢阻塞導緻。生産環境中遇到最多的，也是最容易出問題的，還是一些複雜的查詢操作，是以查詢語句的優化顯然是第一要務。
那我們怎麼知道那條SQL慢？開啟慢查詢日志(slow_query_log)
将 slow_query_log 全局變量設定為“ON”狀态
mysql> set global slow_query_log='ON'; 設定慢查詢日志存放的位置
mysql> set global slow_query_log_file='c:/log/slow.log'; 查詢速度大于1秒就寫日志：
mysql> set global long_query_time=1; 當然了，這并不是标準化流程，如果是實時業務，500ms的查詢也許也算慢查詢，是以一般需要根據業務來設定慢查詢時間的門檻值。
當然了，本着“防微杜漸”的原則，在慢查詢出現之前，我們完全就可以将其扼殺在搖籃中，那就是寫出一條sql之後，使用查詢計劃(explain)，來實際檢查一下查詢性能，關于explain指令，在傳回的表格中真正有決定意義的是rows字段，大部分rows值小的語句執行并不需要優化，是以基本上，優化sql，實際上是在優化rows，值得注意的是，在測試sql語句的效率時候，最好不要開啟查詢緩存，否則會影響你對這條sql查詢時間的正确判斷：
SELECT SQL_NO_CACHE SQL優化手段(索引)
除了避免諸如select *、like、order by rand()這種老生常談的低效sql寫法，更多的，我們依靠索引來優化SQL，在使用索引之前，需要弄清楚到底索引為什麼能幫我們提高查詢效率，也就是索引的原理，這個時候你的腦子裡肯定浮現了圖書的目錄、火車站的車次表，是的，網上都是這麼說的，事實上是，如果沒坐過火車，沒有使用過目錄，那這樣的生活索引樣例就并不直覺，作為下裡巴人，我們一定吃過包子：
毫無疑問，當我們在吃包子的時候，其實是在吃餡兒，如果沒有餡兒，包子就不是包子，而是饅頭。那麼問題來了，我怎麼保證一口就能吃到餡兒呢？這裡的餡兒，可以了解為資料，海量資料的包子，可能直徑幾公裡，那麼我怎麼能快速得到我想要的資料(餡兒)？有生活經驗的吃貨一定會告訴你，找油皮兒。
因為餡兒裡面有油脂，更貼近包子皮兒的地方，或者包子皮兒簙的地方，都會被油脂浸透，也就形成了油皮兒，是以如果照着油皮兒下嘴，至少要比咬其他地方更容易吃到餡兒，那麼，索引就是油皮兒，有索引的資料就是有油皮兒的大包子，沒有索引的資料就是沒有油皮兒的大包子，如此一來，索引的原理顯而易見，通過縮小資料範圍（油皮兒）來篩選出最終想要的結果（餡兒），同時把随機的查詢（随便咬）變成順序的查詢（先找油皮兒），也就是我們總是通過同一種查詢方式來鎖定資料。
SQL索引的資料結構B+tree
知道了背景，了解了原理，現在我們需要某種容器（資料結構）來幫我們實作包子的油皮兒，這種容器可以協助我們每次查找資料時把咬包子次數控制在一個很小的數量級，最好是常數數量級。于是B+tree閃亮登場。
那麼，假設資料庫中有1-7條資料，一次查詢，B+tree到底怎麼幫我們快速檢索到資料呢？
SELECT SQL_NO_CACHE id from article where id = 4 
如圖所示，如果要查找資料4，那麼首先會把B+tree的根節點加載到記憶體，此時發生一次咬包子(IO讀操作)，在記憶體中用二分查找确定4在3和5之間，通過根節點所存儲的指針加載葉子節點(3,4)到記憶體中，發生第二次咬包子，結束查詢，總計兩次。如果不使用索引，我們需要咬四口包子才能把4咬出來。而在生産環境中，2階的B+樹可以表示上百萬的資料，如果上百萬的資料查找隻需要兩次IO讀操作，性能提高将是巨大的，如果沒有索引，每個資料項都要發生一次IO讀取，那麼總共需要百萬次的IO，顯然成本是巨大的。
同時，我們知道IO次數讀寫取決于B+樹的層級，也就是高度h，假設目前資料表的資料為N，每個存儲容器的資料項的數量是m，則有h=㏒(m+1)N，當資料量N一定的情況下，m越大，h越小；而m = 存儲容器的大小 / 資料項的大小，存儲容器的大小也就是一個資料頁的大小，是固定的，如果資料項占的空間越小，資料項的數量越多，樹的高度越低。這就是為什麼每個資料項，即索引字段要盡量的小，比如int占4位元組，要比bigint8位元組少一半。這也是為什麼B+樹要求把真實的資料放到葉子節點而不是非葉子節點，一旦放到非葉子節點，存儲容器的資料項會大幅度下降，導緻樹的層數增高。當資料項等于1時将會退化成線性表，又變成了順序查找，是以這也是為啥索引用B+tree，而不用B-tree，根本原因就是葉子節點存儲資料高度就會減小，而高度減小才能幫我們更快的吃到餡兒。
說白了就是B-tree也能實作索引，也能讓我們更快的通路資料，但是B-tree每個節點上都帶着一點兒餡兒，而這個餡兒占據了本來油皮的空間，是以為了擴容，隻能增加B-tree的高度進行擴容，随着餡兒越來越多，導緻B-tree的高度也越來越高，高度越高，我們咬包子的次數也越來越頻繁，讀寫效率則越來越慢。
當B+樹的資料項是複合的資料結構，即所謂的聯合索引，比如(name,age,sex)的時候，B+樹是按照從左到右的順序來建立搜尋樹的，比如當(小明,20,男)這樣的資料來檢索的時候，B+樹會優先比較name來确定下一步的所搜方向，如果name相同再依次比較age和sex，最後得到檢索的資料；但當(20,男)這樣的沒有name的資料來的時候，B+樹就不知道下一步該查哪個節點，因為建立搜尋樹的時候name就是第一個比較因子，必須要先根據name來搜尋才能知道下一步去哪裡查詢。比如當(小明,F)這樣的資料來檢索時，B+樹可以用name來指定搜尋方向，但下一個字段age的缺失，是以隻能把名字等于小明的資料都找到，然後再比對性别是男的資料了， 這個是非常重要的性質，即索引的最左比對特性，關于最左原則可以參照這篇文章：mysql聯合索引的最左字首原則以及b+tree 。
最基本的索引建立原則無外乎以下幾點：
1.最左字首比對原則，非常重要的原則，mysql會一直向右比對直到遇到範圍查詢(>、<、between、like)就停止比對，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整。
2.=和in可以亂序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優化器會幫你優化成索引可以識别的形式。
3.盡量選擇區分度高的列作為索引，區分度的公式是count(distinct col)/count(*)，表示字段不重複的比例，比例越大我們掃描的記錄數越少，唯一鍵的區分度是1，而一些狀态、性别字段可能在大資料面前區分度就是0，那可能有人會問，這個比例有什麼經驗值嗎？使用場景不同，這個值也很難确定，一般需要join的字段我們都要求是0.1以上，即平均1條掃描10條記錄。
4.索引列不能參與計算，保持列“幹淨”，比如from_unixtime(create_time) = ’2020-01-01’就不能使用到索引，原因很簡單，b+樹中存的都是資料表中的字段值，但進行檢索時，需要把所有元素都應用函數才能比較，顯然成本太大。是以語句應該寫成create_time = unix_timestamp(’2020-01-01’)。
5.盡量的擴充索引，不要建立索引。比如表中已經有a的索引，現在要加(a,b)的索引，那麼隻需要修改原來的索引即可。
索引類型(聚簇(一級)/非聚簇(二級))
聚簇索引：将資料存儲與索引放到了一塊，找到索引也就找到了資料。
非聚簇索引：将資料存儲于索引分開結構，索引結構的葉子節點指向了資料。
上文說了，由于資料本身會占據索引結構的存儲空間，是以一個表僅有一個聚簇索引，也就是我們通常意義上認為的主鍵(Primary Key)，如果表中沒有定義主鍵，InnoDB 會選擇一個唯一的非空索引代替。如果沒有這樣的索引，InnoDB 會隐式定義一個主鍵來作為聚簇索引。InnoDB 隻聚集在同一個頁面中的記錄。包含相鄰鍵值的頁面可能相距甚遠。如果你已經設定了主鍵為聚簇索引，必須先删除主鍵，然後添加我們想要的聚簇索引，最後恢複設定主鍵即可。除了聚簇索引，其他的索引都是非聚簇索引，比如聯合索引，需要遵循“最左字首”原則。
一般情況下，主鍵(聚簇索引)通常建議使用自增id，因為聚簇索引的資料的實體存放順序與索引順序是一緻的，即：隻要索引是相鄰的，那麼對應的資料一定也是相鄰地存放在磁盤上的。如果主鍵不是自增id，那麼可以想 象，它會幹些什麼，不斷地調整資料的實體位址、分頁，當然也有其他一些措施來減少這些操作，但卻無法徹底避免。但，如果是自增的，那就簡單了，它隻需要一 頁一頁地寫，索引結構相對緊湊，磁盤碎片少，效率也高。
非索引優化
是的，SQL優化包含但并不限于索引優化，索引可以幫助我們優化效率，但索引也并非萬能，比如著名的SQL分頁偏移優化問題：
select * from table_name limit 10000,10
select * from table_name limit 0,10 limit 分頁算法帶來了極大的周遊，但資料偏移量一大，limit 的性能就急劇下降。
造成效率問題的根源是查詢邏輯：
1.從資料表中讀取第N條資料添加到資料集中
2.重複第一步直到 N = 10000 + 10
3.根據 offset 抛棄前面 10000 條數
4.傳回剩餘的 10 條資料
一般情況下，可以通過增加篩選條件限制查詢範圍而優化：
select * from table_name where (id >= 10000) limit 10 這種優化手段簡單粗暴，但是需要有一些前提：首先必須要有聚簇索引列，而且資料在邏輯上必須是連續的，其次，你還必須知道特征值，也就是每頁的最後一條邏輯資料id，如果增加其他的範圍篩選條件就會很麻煩。
是以，單純的關鍵字優化又需要索引的參與：
Select * From table_name Where id in (Select id From table_name where ( user = xxx )) 給user字段設定索引，子查詢隻用到了索引列，沒有取實際的資料，隻取主鍵，我們知道，聚簇索引是把資料和索引放在一起的，是以把原來的基于 user 的搜尋轉化為基于主鍵（id）的搜尋，主查詢因為已經獲得了準确的索引值，是以查詢過程也相對較快。
但優化并未結束，由于外層查詢沒有where條件（因為子查詢還未執行），結果就是将分頁表的全部資料都掃描了出來load到了記憶體，然後進行nested loop，循環執行子查詢，根據子查詢結果對外層查詢結果進行過濾。
select * from table_name a inner join ( select id from table_name where (user = xxx) limit 10000,10) b on a.id = b.id 是以，如果外層沒有篩選範圍，慎用in關鍵字，因為in子查詢總是以外層查詢的table作為驅動表，是以如果想用in子查詢的話，一定要将外層查詢的結果集降下來，降低io次數，降低nested loop循環次數，即：永遠用小結果集驅動大的結果集。
SQL優化瓶頸(成也優化，敗也優化)
SQL優化能解決所有問題嗎？并非如此：
select TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,TABLE_ROWS,TABLE_COLLATION,ENGINE,group_concat(case CONSTRAINT_NAME when NULL then '' else CONSTRAINT_NAME end) CN,group_concat(case CONSTRAINT_TYPE when NULL then '' else CONSTRAINT_TYPE end) PF from (select a.TABLE_SCHEMA,a.TABLE_NAME,a.TABLE_TYPE,a.TABLE_ROWS,a.TABLE_COLLATION,a.ENGINE,b.CONSTRAINT_NAME,b.CONSTRAINT_TYPE,b.key_cols
from INFORMATION_SCHEMA.TABLES a
LEFT JOIN
(SELECT
t.TABLE_SCHEMA,
t.TABLE_NAME,
t.CONSTRAINT_NAME,
t.CONSTRAINT_TYPE,
group_concat(c.COLUMN_NAME) key_cols
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c
WHERE
t.TABLE_NAME = c.TABLE_NAME
AND t.CONSTRAINT_TYPE in ('PRIMARY KEY','FOREIGN KEY')
AND t.CONSTRAINT_NAME=c.CONSTRAINT_NAME
and c.table_schema=t.table_schema
group by TABLE_SCHEMA,TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE) b
on (a.TABLE_NAME = b.TABLE_NAME and a.table_schema=b.table_schema)
WHERE a.TABLE_TYPE='BASE TABLE' and a.TABLE_SCHEMA = database()) ccc GROUP BY TABLE_SCHEMA,TABLE_NAME,TABLE_COLLATION,ENGINE; 是的，有時候，我們往往忽略了一個關鍵問題，就是需求，當出現了上面這種SQL的時候，我們腦子裡想的不應該是優化，因為就算優化了，也是飲鸩止渴，由于SQL用例回歸時落掉一些極端情況，可能會造成比原來還嚴重的後果。
那我們應該怎麼解決這種“非優化之罪”的情況呢？答案從業務出發，對業務進行解耦，複雜SQL的出現，往往是因為業務頻繁變動導緻之前設計的表結構無法支撐業務的原子性擴容，是以，從源頭出發，對表結構重新設計，或者幹脆寫一個腳本将慢查詢結果集導入到一張新的結果表中，這樣往往更加簡單和節省時間。
結語：任何一款開源資料庫，國内外大廠在用，三流的草台班子也在用，但是用起來的效果不盡相同，同樣地，一套太祖長拳，在尋常武師和丐幫幫主喬峰手底下施展出來的威力更是天差地别，其實這道理與武學一般，看似簡單的業務更能展現個人實力，貌似稀松平常的索引優化才能檢測出一個人的SQL功底，能在平淡之中現神奇，才說得上是大宗匠的手段。