舉凡后端面試，面試官不言數(shù)據(jù)庫則已，言則必稱SQL優(yōu)化，說起SQL優(yōu)化，網(wǎng)絡上各種“指南”和“圣經(jīng)”難以枚舉，不一而足，仿佛SQL優(yōu)化已然是婦孺皆知的理論常識，然后根據(jù)多數(shù)無知（Pluralistic ignorance）理論，人們印象里覺得多數(shù)人會怎么想怎么做，但這種印象往往是不準確的。那SQL優(yōu)化到底應該怎么做？本次讓我們褪去SQL華麗的軀殼，以最淺顯，最粗俗，最下里巴人的方式講解一下SQL優(yōu)化的前因后果，前世今生。

(資料圖)

SQL優(yōu)化背景

首先要明確一點，SQL優(yōu)化不是為了優(yōu)化而優(yōu)化，就像冬天要穿羽絨服，不是因為有羽絨服或者羽絨服本身而穿，是因為天兒太冷了！那SQL優(yōu)化的原因是什么？是因為SQL語句太慢了！從廣義上講，SQL語句包含增刪改查，但一般的業(yè)務場景下，SQL的讀寫比例應該是一比十左右，而且寫操作很少出現(xiàn)性能問題，即使出現(xiàn)，大多數(shù)也是慢查詢阻塞導致。生產(chǎn)環(huán)境中遇到最多的，也是最容易出問題的，還是一些復雜的查詢操作，所以查詢語句的優(yōu)化顯然是第一要務。

那我們怎么知道那條SQL慢？開啟慢查詢日志(slow_query_log)

將 slow_query_log 全局變量設置為“ON”狀態(tài)

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;

當然了，這并不是標準化流程，如果是實時業(yè)務，500ms的查詢也許也算慢查詢，所以一般需要根據(jù)業(yè)務來設置慢查詢時間的閾值。

當然了，本著“防微杜漸”的原則，在慢查詢出現(xiàn)之前，我們完全就可以將其扼殺在搖籃中，那就是寫出一條sql之后，使用查詢計劃(explain)，來實際檢查一下查詢性能，關于explain命令，在返回的表格中真正有決定意義的是rows字段，大部分rows值小的語句執(zhí)行并不需要優(yōu)化，所以基本上，優(yōu)化sql，實際上是在優(yōu)化rows，值得注意的是，在測試sql語句的效率時候，最好不要開啟查詢緩存，否則會影響你對這條sql查詢時間的正確判斷：

SELECT SQL_NO_CACHE

SQL優(yōu)化手段(索引)

除了避免諸如select *、like、order by rand()這種老生常談的低效sql寫法，更多的，我們依靠索引來優(yōu)化SQL，在使用索引之前，需要弄清楚到底索引為什么能幫我們提高查詢效率，也就是索引的原理，這個時候你的腦子里肯定浮現(xiàn)了圖書的目錄、火車站的車次表，是的，網(wǎng)上都是這么說的，事實上是，如果沒坐過火車，沒有使用過目錄，那這樣的生活索引樣例就并不直觀，作為下里巴人，我們一定吃過包子：

毫無疑問，當我們在吃包子的時候，其實是在吃餡兒，如果沒有餡兒，包子就不是包子，而是饅頭。那么問題來了，我怎么保證一口就能吃到餡兒呢？這里的餡兒，可以理解為數(shù)據(jù)，海量數(shù)據(jù)的包子，可能直徑幾公里，那么我怎么能快速得到我想要的數(shù)據(jù)(餡兒)？有生活經(jīng)驗的吃貨一定會告訴你，找油皮兒。

因為餡兒里面有油脂，更貼近包子皮兒的地方，或者包子皮兒簙的地方，都會被油脂浸透，也就形成了油皮兒，所以如果照著油皮兒下嘴，至少要比咬其他地方更容易吃到餡兒，那么，索引就是油皮兒，有索引的數(shù)據(jù)就是有油皮兒的大包子，沒有索引的數(shù)據(jù)就是沒有油皮兒的大包子，如此一來，索引的原理顯而易見，通過縮小數(shù)據(jù)范圍（油皮兒）來篩選出最終想要的結果（餡兒），同時把隨機的查詢（隨便咬）變成順序的查詢（先找油皮兒），也就是我們總是通過同一種查詢方式來鎖定數(shù)據(jù)。

SQL索引的數(shù)據(jù)結構B+tree

知道了背景，了解了原理，現(xiàn)在我們需要某種容器（數(shù)據(jù)結構）來幫我們實現(xiàn)包子的油皮兒，這種容器可以協(xié)助我們每次查找數(shù)據(jù)時把咬包子次數(shù)控制在一個很小的數(shù)量級，最好是常數(shù)數(shù)量級。于是B+tree閃亮登場。

那么，假設數(shù)據(jù)庫中有1-7條數(shù)據(jù)，一次查詢，B+tree到底怎么幫我們快速檢索到數(shù)據(jù)呢？

SELECT SQL_NO_CACHE id from article where id = 4

如圖所示，如果要查找數(shù)據(jù)4，那么首先會把B+tree的根節(jié)點加載到內存，此時發(fā)生一次咬包子(IO讀操作)，在內存中用二分查找確定4在3和5之間，通過根節(jié)點所存儲的指針加載葉子節(jié)點(3,4)到內存中，發(fā)生第二次咬包子，結束查詢，總計兩次。如果不使用索引，我們需要咬四口包子才能把4咬出來。而在生產(chǎn)環(huán)境中，2階的B+樹可以表示上百萬的數(shù)據(jù)，如果上百萬的數(shù)據(jù)查找只需要兩次IO讀操作，性能提高將是巨大的，如果沒有索引，每個數(shù)據(jù)項都要發(fā)生一次IO讀取，那么總共需要百萬次的IO，顯然成本是巨大的。

同時，我們知道IO次數(shù)讀寫取決于B+樹的層級，也就是高度h，假設當前數(shù)據(jù)表的數(shù)據(jù)為N，每個存儲容器的數(shù)據(jù)項的數(shù)量是m，則有h=㏒(m+1)N，當數(shù)據(jù)量N一定的情況下，m越大，h越?。欢鴐 = 存儲容器的大小 / 數(shù)據(jù)項的大小，存儲容器的大小也就是一個數(shù)據(jù)頁的大小，是固定的，如果數(shù)據(jù)項占的空間越小，數(shù)據(jù)項的數(shù)量越多，樹的高度越低。這就是為什么每個數(shù)據(jù)項，即索引字段要盡量的小，比如int占4字節(jié)，要比bigint8字節(jié)少一半。這也是為什么B+樹要求把真實的數(shù)據(jù)放到葉子節(jié)點而不是非葉子節(jié)點，一旦放到非葉子節(jié)點，存儲容器的數(shù)據(jù)項會大幅度下降，導致樹的層數(shù)增高。當數(shù)據(jù)項等于1時將會退化成線性表，又變成了順序查找，所以這也是為啥索引用B+tree，而不用B-tree，根本原因就是葉子節(jié)點存儲數(shù)據(jù)高度就會減小，而高度減小才能幫我們更快的吃到餡兒。

說白了就是B-tree也能實現(xiàn)索引，也能讓我們更快的訪問數(shù)據(jù)，但是B-tree每個節(jié)點上都帶著一點兒餡兒，而這個餡兒占據(jù)了本來油皮的空間，所以為了擴容，只能增加B-tree的高度進行擴容，隨著餡兒越來越多，導致B-tree的高度也越來越高，高度越高，我們咬包子的次數(shù)也越來越頻繁，讀寫效率則越來越慢。

當B+樹的數(shù)據(jù)項是復合的數(shù)據(jù)結構，即所謂的聯(lián)合索引，比如(name,age,sex)的時候，B+樹是按照從左到右的順序來建立搜索樹的，比如當(小明,20,男)這樣的數(shù)據(jù)來檢索的時候，B+樹會優(yōu)先比較name來確定下一步的所搜方向，如果name相同再依次比較age和sex，最后得到檢索的數(shù)據(jù)；但當(20,男)這樣的沒有name的數(shù)據(jù)來的時候，B+樹就不知道下一步該查哪個節(jié)點，因為建立搜索樹的時候name就是第一個比較因子，必須要先根據(jù)name來搜索才能知道下一步去哪里查詢。比如當(小明,F)這樣的數(shù)據(jù)來檢索時，B+樹可以用name來指定搜索方向，但下一個字段age的缺失，所以只能把名字等于小明的數(shù)據(jù)都找到，然后再匹配性別是男的數(shù)據(jù)了， 這個是非常重要的性質，即索引的最左匹配特性，關于最左原則可以參照這篇文章：mysql聯(lián)合索引的最左前綴原則以及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的查詢優(yōu)化器會幫你優(yōu)化成索引可以識別的形式。 3.盡量選擇區(qū)分度高的列作為索引，區(qū)分度的公式是count(distinct col)/count(*)，表示字段不重復的比例，比例越大我們掃描的記錄數(shù)越少，唯一鍵的區(qū)分度是1，而一些狀態(tài)、性別字段可能在大數(shù)據(jù)面前區(qū)分度就是0，那可能有人會問，這個比例有什么經(jīng)驗值嗎？使用場景不同，這個值也很難確定，一般需要join的字段我們都要求是0.1以上，即平均1條掃描10條記錄。 4.索引列不能參與計算，保持列“干凈”，比如from_unixtime(create_time) = ’2020-01-01’就不能使用到索引，原因很簡單，b+樹中存的都是數(shù)據(jù)表中的字段值，但進行檢索時，需要把所有元素都應用函數(shù)才能比較，顯然成本太大。所以語句應該寫成create_time = unix_timestamp(’2020-01-01’)。 5.盡量的擴展索引，不要新建索引。比如表中已經(jīng)有a的索引，現(xiàn)在要加(a,b)的索引，那么只需要修改原來的索引即可。

索引類型(聚簇(一級)/非聚簇(二級))

聚簇索引：將數(shù)據(jù)存儲與索引放到了一塊，找到索引也就找到了數(shù)據(jù)。

非聚簇索引：將數(shù)據(jù)存儲于索引分開結構，索引結構的葉子節(jié)點指向了數(shù)據(jù)。

上文說了，由于數(shù)據(jù)本身會占據(jù)索引結構的存儲空間，因此一個表僅有一個聚簇索引，也就是我們通常意義上認為的主鍵(Primary Key)，如果表中沒有定義主鍵，InnoDB 會選擇一個唯一的非空索引代替。如果沒有這樣的索引，InnoDB 會隱式定義一個主鍵來作為聚簇索引。InnoDB 只聚集在同一個頁面中的記錄。包含相鄰鍵值的頁面可能相距甚遠。如果你已經(jīng)設置了主鍵為聚簇索引，必須先刪除主鍵，然后添加我們想要的聚簇索引，最后恢復設置主鍵即可。除了聚簇索引，其他的索引都是非聚簇索引，比如聯(lián)合索引，需要遵循“最左前綴”原則。

一般情況下，主鍵(聚簇索引)通常建議使用自增id，因為聚簇索引的數(shù)據(jù)的物理存放順序與索引順序是一致的，即：只要索引是相鄰的，那么對應的數(shù)據(jù)一定也是相鄰地存放在磁盤上的。如果主鍵不是自增id，那么可以想 象，它會干些什么，不斷地調整數(shù)據(jù)的物理地址、分頁，當然也有其他一些措施來減少這些操作，但卻無法徹底避免。但，如果是自增的，那就簡單了，它只需要一 頁一頁地寫，索引結構相對緊湊，磁盤碎片少，效率也高。

非索引優(yōu)化

是的，SQL優(yōu)化包含但并不限于索引優(yōu)化，索引可以幫助我們優(yōu)化效率，但索引也并非萬能，比如著名的SQL分頁偏移優(yōu)化問題：

select * from table_name limit 10000,10select * from table_name limit 0,10

limit 分頁算法帶來了極大的遍歷，但數(shù)據(jù)偏移量一大，limit 的性能就急劇下降。

造成效率問題的根源是查詢邏輯：

1.從數(shù)據(jù)表中讀取第N條數(shù)據(jù)添加到數(shù)據(jù)集中

2.重復第一步直到 N = 10000 + 10

3.根據(jù) offset 拋棄前面 10000 條數(shù)

4.返回剩余的 10 條數(shù)據(jù)

一般情況下，可以通過增加篩選條件限制查詢范圍而優(yōu)化：

select * from table_name where (id >= 10000) limit 10

這種優(yōu)化手段簡單粗暴，但是需要有一些前提：首先必須要有聚簇索引列，而且數(shù)據(jù)在邏輯上必須是連續(xù)的，其次，你還必須知道特征值，也就是每頁的最后一條邏輯數(shù)據(jù)id，如果增加其他的范圍篩選條件就會很麻煩。

所以，單純的關鍵字優(yōu)化又需要索引的參與：

Select * From table_name Where id in (Select id From table_name where ( user = xxx ))

給user字段設置索引，子查詢只用到了索引列，沒有取實際的數(shù)據(jù)，只取主鍵，我們知道，聚簇索引是把數(shù)據(jù)和索引放在一起的，所以把原來的基于 user 的搜索轉化為基于主鍵（id）的搜索，主查詢因為已經(jīng)獲得了準確的索引值，所以查詢過程也相對較快。

但優(yōu)化并未結束，由于外層查詢沒有where條件（因為子查詢還未執(zhí)行），結果就是將分頁表的全部數(shù)據(jù)都掃描了出來load到了內存，然后進行nested loop，循環(huán)執(zhí)行子查詢，根據(jù)子查詢結果對外層查詢結果進行過濾。

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次數(shù)，降低nested loop循環(huán)次數(shù)，即：永遠用小結果集驅動大的結果集。

SQL優(yōu)化瓶頸(成也優(yōu)化，敗也優(yōu)化)

SQL優(yōu)化能解決所有問題嗎？并非如此：

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_colsfrom INFORMATION_SCHEMA.TABLES aLEFT JOIN(SELECTt.TABLE_SCHEMA,t.TABLE_NAME,t.CONSTRAINT_NAME,t.CONSTRAINT_TYPE,group_concat(c.COLUMN_NAME) key_colsFROMINFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t,INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS cWHEREt.TABLE_NAME = c.TABLE_NAMEAND t.CONSTRAINT_TYPE in ("PRIMARY KEY","FOREIGN KEY")AND t.CONSTRAINT_NAME=c.CONSTRAINT_NAMEand c.table_schema=t.table_schemagroup by TABLE_SCHEMA,TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE) bon (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;

是的，有時候，我們往往忽略了一個關鍵問題，就是需求，當出現(xiàn)了上面這種SQL的時候，我們腦子里想的不應該是優(yōu)化，因為就算優(yōu)化了，也是飲鴆止渴，由于SQL用例回歸時落掉一些極端情況，可能會造成比原來還嚴重的后果。

那我們應該怎么解決這種“非優(yōu)化之罪”的情況呢？答案從業(yè)務出發(fā)，對業(yè)務進行解耦，復雜SQL的出現(xiàn)，往往是因為業(yè)務頻繁變動導致之前設計的表結構無法支撐業(yè)務的原子性擴容，所以，從源頭出發(fā)，對表結構重新設計，或者干脆寫一個腳本將慢查詢結果集導入到一張新的結果表中，這樣往往更加簡單和節(jié)省時間。

結語：任何一款開源數(shù)據(jù)庫，國內外大廠在用，三流的草臺班子也在用，但是用起來的效果不盡相同，同樣地，一套太祖長拳，在尋常武師和丐幫幫主喬峰手底下施展出來的威力更是天差地別，其實這道理與武學一般，看似簡單的業(yè)務更能體現(xiàn)個人實力，貌似稀松平常的索引優(yōu)化才能檢測出一個人的SQL功底，能在平淡之中現(xiàn)神奇，才說得上是大宗匠的手段。

關鍵詞： 聚簇索引 數(shù)據(jù)結構