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

(資料圖)

SQL優(yōu)化背景

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

那我們?cè)趺粗滥菞lSQL慢？開啟慢查詢?nèi)罩?slow_query_log)

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

mysql>set global slow_query_log="ON";

設(shè)置慢查詢?nèi)罩敬娣诺奈恢?

mysql>set global slow_query_log_file="c:/log/slow.log";

查詢速度大于1秒就寫日志：

mysql>set global long_query_time=1;

當(dāng)然了，這并不是標(biāo)準(zhǔn)化流程，如果是實(shí)時(shí)業(yè)務(wù)，500ms的查詢也許也算慢查詢，所以一般需要根據(jù)業(yè)務(wù)來(lái)設(shè)置慢查詢時(shí)間的閾值。

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

SELECT SQL_NO_CACHE

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

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

毫無(wú)疑問，當(dāng)我們?cè)诔园拥臅r(shí)候，其實(shí)是在吃餡兒，如果沒有餡兒，包子就不是包子，而是饅頭。那么問題來(lái)了，我怎么保證一口就能吃到餡兒呢？這里的餡兒，可以理解為數(shù)據(jù)，海量數(shù)據(jù)的包子，可能直徑幾公里，那么我怎么能快速得到我想要的數(shù)據(jù)(餡兒)？有生活經(jīng)驗(yàn)的吃貨一定會(huì)告訴你，找油皮兒。

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

SQL索引的數(shù)據(jù)結(jié)構(gòu)B+tree

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

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

SELECT SQL_NO_CACHE id from article where id = 4

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

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

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

當(dāng)B+樹的數(shù)據(jù)項(xiàng)是復(fù)合的數(shù)據(jù)結(jié)構(gòu)，即所謂的聯(lián)合索引，比如(name,age,sex)的時(shí)候，B+樹是按照從左到右的順序來(lái)建立搜索樹的，比如當(dāng)(小明,20,男)這樣的數(shù)據(jù)來(lái)檢索的時(shí)候，B+樹會(huì)優(yōu)先比較name來(lái)確定下一步的所搜方向，如果name相同再依次比較age和sex，最后得到檢索的數(shù)據(jù)；但當(dāng)(20,男)這樣的沒有name的數(shù)據(jù)來(lái)的時(shí)候，B+樹就不知道下一步該查哪個(gè)節(jié)點(diǎn)，因?yàn)榻⑺阉鳂涞臅r(shí)候name就是第一個(gè)比較因子，必須要先根據(jù)name來(lái)搜索才能知道下一步去哪里查詢。比如當(dāng)(小明,F)這樣的數(shù)據(jù)來(lái)檢索時(shí)，B+樹可以用name來(lái)指定搜索方向，但下一個(gè)字段age的缺失，所以只能把名字等于小明的數(shù)據(jù)都找到，然后再匹配性別是男的數(shù)據(jù)了， 這個(gè)是非常重要的性質(zhì)，即索引的最左匹配特性，關(guān)于最左原則可以參照這篇文章：mysql聯(lián)合索引的最左前綴原則以及b+tree 。

最基本的索引建立原則無(wú)外乎以下幾點(diǎn)：

1.最左前綴匹配原則，非常重要的原則，mysql會(huì)一直向右匹配直到遇到范圍查詢(>、<、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的順序可以任意調(diào)整。 2.=和in可以亂序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優(yōu)化器會(huì)幫你優(yōu)化成索引可以識(shí)別的形式。 3.盡量選擇區(qū)分度高的列作為索引，區(qū)分度的公式是count(distinct col)/count(*)，表示字段不重復(fù)的比例，比例越大我們掃描的記錄數(shù)越少，唯一鍵的區(qū)分度是1，而一些狀態(tài)、性別字段可能在大數(shù)據(jù)面前區(qū)分度就是0，那可能有人會(huì)問，這個(gè)比例有什么經(jīng)驗(yàn)值嗎？使用場(chǎng)景不同，這個(gè)值也很難確定，一般需要join的字段我們都要求是0.1以上，即平均1條掃描10條記錄。 4.索引列不能參與計(jì)算，保持列“干凈”，比如from_unixtime(create_time) = ’2020-01-01’就不能使用到索引，原因很簡(jiǎn)單，b+樹中存的都是數(shù)據(jù)表中的字段值，但進(jìn)行檢索時(shí)，需要把所有元素都應(yīng)用函數(shù)才能比較，顯然成本太大。所以語(yǔ)句應(yīng)該寫成create_time = unix_timestamp(’2020-01-01’)。 5.盡量的擴(kuò)展索引，不要新建索引。比如表中已經(jīng)有a的索引，現(xiàn)在要加(a,b)的索引，那么只需要修改原來(lái)的索引即可。

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

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

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

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

一般情況下，主鍵(聚簇索引)通常建議使用自增id，因?yàn)榫鄞厮饕臄?shù)據(jù)的物理存放順序與索引順序是一致的，即：只要索引是相鄰的，那么對(duì)應(yīng)的數(shù)據(jù)一定也是相鄰地存放在磁盤上的。如果主鍵不是自增id，那么可以想 象，它會(huì)干些什么，不斷地調(diào)整數(shù)據(jù)的物理地址、分頁(yè)，當(dāng)然也有其他一些措施來(lái)減少這些操作，但卻無(wú)法徹底避免。但，如果是自增的，那就簡(jiǎn)單了，它只需要一 頁(yè)一頁(yè)地寫，索引結(jié)構(gòu)相對(duì)緊湊，磁盤碎片少，效率也高。

非索引優(yōu)化

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

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

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

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

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

2.重復(fù)第一步直到 N = 10000 + 10

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

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

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

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

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

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

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

給user字段設(shè)置索引，子查詢只用到了索引列，沒有取實(shí)際的數(shù)據(jù)，只取主鍵，我們知道，聚簇索引是把數(shù)據(jù)和索引放在一起的，所以把原來(lái)的基于 user 的搜索轉(zhuǎn)化為基于主鍵（id）的搜索，主查詢因?yàn)橐呀?jīng)獲得了準(zhǔn)確的索引值，所以查詢過(guò)程也相對(duì)較快。

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

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關(guān)鍵字，因?yàn)閕n子查詢總是以外層查詢的table作為驅(qū)動(dòng)表，所以如果想用in子查詢的話，一定要將外層查詢的結(jié)果集降下來(lái)，降低io次數(shù)，降低nested loop循環(huán)次數(shù)，即：永遠(yuǎn)用小結(jié)果集驅(qū)動(dòng)大的結(jié)果集。

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;

是的，有時(shí)候，我們往往忽略了一個(gè)關(guān)鍵問題，就是需求，當(dāng)出現(xiàn)了上面這種SQL的時(shí)候，我們腦子里想的不應(yīng)該是優(yōu)化，因?yàn)榫退銉?yōu)化了，也是飲鴆止渴，由于SQL用例回歸時(shí)落掉一些極端情況，可能會(huì)造成比原來(lái)還嚴(yán)重的后果。

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

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

關(guān)鍵詞： 聚簇索引 數(shù)據(jù)結(jié)構(gòu)