今天和大家聊一個(gè)常見的問題：慢SQL。

通過本文你將了解到以下內(nèi)容：

• 慢SQL的危害
• SQL語句的執(zhí)行過程
• 存儲引擎和索引的那些事兒
• 慢SQL解決之道

后續(xù)均以MySQL默認(rèn)存儲引擎InnoDB為例進(jìn)行展開，話不多說，開搞！

1.慢SQL的危害

慢SQL，就是跑得很慢的SQL語句，你可能會問慢SQL會有啥問題嗎？

試想一個(gè)場景：

大白和小黑端午出去玩，機(jī)票太貴于是買了高鐵，火車站的人真是烏央烏央的。

馬上檢票了，大白和小黑準(zhǔn)備去廁所清理下庫存，坑位不多，排隊(duì)的人還真不少。

小黑發(fā)現(xiàn)其中有3個(gè)坑的乘客賊慢，其他2個(gè)坑位換了好幾波人，這3位坑主就是不出來。

等在外面的大伙，心里很是不爽，長期占用公共資源，后面的人沒法用。

小黑苦笑道：這不就是廁所版的慢SQL嘛！

這是實(shí)際生活中的例子，換到MySQL服務(wù)器也是一樣的，畢竟科技源自生活嘛。

MySQL服務(wù)器的資源(CPU、IO、內(nèi)存等)是有限的，尤其在高并發(fā)場景下需要快速處理掉請求，否則一旦出現(xiàn)慢SQL就會阻塞掉很多正常的請求，造成大面積的失敗/超時(shí)等。

2.SQL語句執(zhí)行過程

客戶端和MySQL服務(wù)端的交互過程簡介：

1. 客戶端發(fā)送一條SQL語句給服務(wù)端，服務(wù)端的連接器先進(jìn)行賬號/密碼、權(quán)限等環(huán)節(jié)驗(yàn)證，有異常直接拒絕請求。
2. 服務(wù)端查詢緩存，如果SQL語句命中了緩存，則返回緩存中的結(jié)果，否則繼續(xù)處理。
3. 服務(wù)端對SQL語句進(jìn)行詞法解析、語法解析、預(yù)處理來檢查SQL語句的合法性。
4. 服務(wù)端通過優(yōu)化器對之前生成的解析樹進(jìn)行優(yōu)化處理，生成最優(yōu)的物理執(zhí)行計(jì)劃。
5. 將生成的物理執(zhí)行計(jì)劃調(diào)用存儲引擎的相關(guān)接口，進(jìn)行數(shù)據(jù)查詢和處理。
6. 處理完成后將結(jié)果返回客戶端。

客戶端和MySQL服務(wù)端的交互過程簡圖：

俗話說"條條大路通羅馬"，優(yōu)化器的作用就是找到這么多路中最優(yōu)的那一條。

存儲引擎更是決定SQL執(zhí)行的核心組件，適當(dāng)了解其中原理十分有益。

3. 存儲引擎和索引的那些事兒

3.1 存儲引擎

InnoDB存儲引擎(Storage Engine)是MySQL默認(rèn)之選，所以非常典型。

存儲引擎的主要作用是進(jìn)行數(shù)據(jù)的存取和檢索，也是真正執(zhí)行SQL語句的組件。

InnoDB的整體架構(gòu)分為兩個(gè)部分：內(nèi)存架構(gòu)和磁盤架構(gòu)，如圖：

存儲引擎的內(nèi)容非常多，并不是一篇文章能說清楚的，本文不過多展開，我們在此只需要了解內(nèi)存架構(gòu)和磁盤架構(gòu)的大致組成即可。

InnoDB 引擎是面向行存儲的，數(shù)據(jù)都是存儲在磁盤的數(shù)據(jù)頁中，數(shù)據(jù)頁里面按照固定的行格式存儲著每一行數(shù)據(jù)。

行格式主要分為四種類型Compact、Redundant、Dynamic和Compressed，默認(rèn)為Compact格式。

磁盤預(yù)讀機(jī)制和局部性原理

當(dāng)計(jì)算機(jī)訪問一個(gè)數(shù)據(jù)時(shí)，不僅會加載當(dāng)前數(shù)據(jù)所在的數(shù)據(jù)頁，還會將當(dāng)前數(shù)據(jù)頁相鄰的數(shù)據(jù)頁一同加載到內(nèi)存，磁盤預(yù)讀的長度一般為頁的整倍數(shù)，從而有效降低磁盤IO的次數(shù)。

磁盤和內(nèi)存的交互

MySQL中磁盤的數(shù)據(jù)需要被交換到內(nèi)存，才能完成一次SQL交互，大致如圖：

• 扇區(qū)是硬盤的讀寫的基本單位，通常情況下每個(gè)扇區(qū)的大小是 512B
• 磁盤塊文件系統(tǒng)讀寫數(shù)據(jù)的最小單位，相鄰的扇區(qū)組合在一起形成一個(gè)塊，一般是4KB
• 頁是內(nèi)存的最小存儲單位，頁的大小通常為磁盤塊大小的 2^n 倍
• InnoDB頁面的默認(rèn)大小是16KB，是數(shù)倍個(gè)操作系統(tǒng)的頁

隨機(jī)磁盤IO

MySQL的數(shù)據(jù)是一行行存儲在磁盤上的，并且這些數(shù)據(jù)并非物理連續(xù)地存儲，這樣的話要查找數(shù)據(jù)就無法避免隨機(jī)在磁盤上讀取和寫入數(shù)據(jù)。

對于MySQL來說，當(dāng)出現(xiàn)大量磁盤隨機(jī)IO時(shí)，大部分時(shí)間都被浪費(fèi)到尋道上，磁盤呼嚕呼嚕轉(zhuǎn)，就是傳輸不了多少數(shù)據(jù)。

一次磁盤訪問由三個(gè)動作組成：

• 尋道：磁頭移動定位到指定磁道
• 旋轉(zhuǎn)：等待指定扇區(qū)從磁頭下旋轉(zhuǎn)經(jīng)過
• 數(shù)據(jù)傳輸：數(shù)據(jù)在磁盤與內(nèi)存之間的實(shí)際傳輸

對于存儲引擎來說，如何有效降低隨機(jī)IO是個(gè)非常重要的問題。

3.2 索引

可以實(shí)現(xiàn)增刪改查的數(shù)據(jù)結(jié)構(gòu)非常多，包括：哈希表、二叉搜索樹、AVL、紅黑樹、B樹、B 樹等，這些都是可以作為索引的候選數(shù)據(jù)結(jié)構(gòu)。

結(jié)合MySQL的實(shí)際情況：磁盤和內(nèi)存交互、隨機(jī)磁盤IO、排序和范圍查找、增刪改的復(fù)雜度等等，綜合考量之下B 樹脫穎而出。

B 樹作為多叉平衡樹，對于范圍查找和排序都可以很好地支持，并且更加矮胖，訪問數(shù)據(jù)時(shí)的平均磁盤IO次數(shù)取決于樹的高度，因此B 樹可以讓磁盤的查找次數(shù)更少。

在InnoDB中B 樹的高度一般都在2~4層，并且根節(jié)點(diǎn)常駐內(nèi)存中，也就是說查找某值的行記錄時(shí)最多只需要1~3次磁盤I/O操作。

MyISAM是將數(shù)據(jù)和索引分開存儲的，InnoDB存儲引擎的數(shù)據(jù)和索引沒有分開存儲，這也就是為什么有人說Innodb索引即數(shù)據(jù)，數(shù)據(jù)即索引，如圖：

說到InnoDB的數(shù)據(jù)和索引的存儲，就提到一個(gè)名詞：聚集索引。

聚集索引

聚集索引將索引和數(shù)據(jù)完美地融合在一起，是每個(gè)Innodb表都會有的一個(gè)特殊索引，一般來說是借助于表的主鍵來構(gòu)建的B 樹。

假設(shè)我們有student表，將id作為主鍵索引，那么聚集索引的B 樹結(jié)構(gòu)，如圖：

• 非葉子節(jié)點(diǎn)不存數(shù)據(jù)，只有主鍵和相關(guān)指針
• 葉子節(jié)點(diǎn)包含主鍵、行數(shù)據(jù)、指針
• 葉子節(jié)點(diǎn)之間由雙向指針串聯(lián)形成有序雙向鏈表，葉子節(jié)點(diǎn)內(nèi)部也是有序的

聚集索引按照如下規(guī)則創(chuàng)建：

• 有主鍵時(shí)InnoDB利用主鍵來生成
• 沒有主鍵，InnoDB會選擇一個(gè)非空的唯一索引來創(chuàng)建
• 無主鍵且非NULL唯一索引時(shí)，InnoDB會隱式創(chuàng)建一個(gè)自增的列來創(chuàng)建

假如我們要查找id=10的數(shù)據(jù)，大致過程如下：

• 索引的根結(jié)點(diǎn)在內(nèi)存中，10>9 因此找到P3指針
• P3指向的數(shù)據(jù)并沒有在內(nèi)存中，因此產(chǎn)生1次磁盤IO讀取磁盤塊3到內(nèi)存
• 在內(nèi)存中對磁盤塊3進(jìn)行二分查找，找到ID=9的全部值

非聚集索引

非聚集索引的葉子節(jié)點(diǎn)中存放的是二級索引值和主鍵鍵值，非葉子節(jié)點(diǎn)和葉子節(jié)點(diǎn)都沒有存儲整行數(shù)據(jù)值。

假設(shè)我們有student表，將name作為二級索引，那么非聚集索引的B 樹結(jié)構(gòu)，如圖：

由于非聚集索引的葉子節(jié)點(diǎn)沒有存儲行數(shù)據(jù)，如果通過非聚集索引來查找非二級索引值，需要分為兩步：

• 第一：通過非聚集索引的葉子節(jié)點(diǎn)來確定數(shù)據(jù)行對應(yīng)的主鍵
• 第二：通過相應(yīng)的主鍵值在聚集索引中查詢到對應(yīng)的行記錄

我們把通過非聚集索引找到主鍵值，再根據(jù)主鍵值從聚集索引找對于行數(shù)據(jù)的過程稱為：回表查詢。

換句話說：select * from student where name = 'Bob' 將產(chǎn)生回表查詢，因?yàn)樵趎ame索引的葉子節(jié)點(diǎn)沒有其他值，只能從聚集索引獲得。

所以如果查找的字段在非聚集索引就可以完成，就可以避免一次回表過程，這種稱為：覆蓋索引，所以select * 并不是好習(xí)慣，需要什么拿什么就好。

假如我們要查找name=Tom的記錄的所有值，大致過程如下：

• 從非聚集索引開始，根節(jié)點(diǎn)在內(nèi)存中，按照name的字典序找到P3指針
• P3指針?biāo)赶虻拇疟P塊不在內(nèi)存中，產(chǎn)生1次磁盤IO加載到內(nèi)存
• 在內(nèi)存中對磁盤塊3的數(shù)據(jù)進(jìn)行搜索，獲得name=tom的記錄的主鍵值為4
• 根據(jù)主鍵值4從聚集索引的根節(jié)點(diǎn)中獲得P2指針
• P2指針?biāo)赶虻拇疟P塊不在內(nèi)存中，產(chǎn)生第2次磁盤IO加載到內(nèi)存
• 將上一步獲得的數(shù)據(jù)，在內(nèi)存中進(jìn)行二分查找獲得全部行數(shù)據(jù)

上述查詢就包含了一次回表過程，因此性能比主鍵查詢慢了一倍，因此盡量使用主鍵查詢，一次完事。

4. 慢SQL解決思路

出現(xiàn)慢SQL的原因很多，我們拋開單表數(shù)億記錄和無索引的特殊情況，來討論一些更有普遍意義的慢SQL原因和解決之道。

我們從兩個(gè)方面來進(jìn)行闡述：

• 數(shù)據(jù)庫表索引設(shè)置不合理
• SQL語句有問題，需要優(yōu)化

4.1 索引設(shè)置原則

程序員的角度和存儲引擎的角度是不一樣的，索引寫的好，SQL跑得快。

• 索引區(qū)分度低

假如表中有1000w記錄，其中有status字段表示狀態(tài)，可能90%的數(shù)據(jù)status=1，可以不將status作為索引，因?yàn)槠鋵?shù)據(jù)記錄區(qū)分度很低。

• 切忌過多創(chuàng)建索引

每個(gè)索引都需要占用磁盤空間，修改表數(shù)據(jù)時(shí)會對索引進(jìn)行更新，索引越多，更新越復(fù)雜。

因?yàn)槊刻砑右粋€(gè)索引，.ibd文件中就需要多維護(hù)一個(gè)B Tree索引樹，如果某一個(gè)table中存在10個(gè)索引，那么就需要維護(hù)10棵B Tree，寫入效率會降低，并且會浪費(fèi)磁盤空間。

• 常用查詢字段建索引

如果某個(gè)字段經(jīng)常用來做查詢條件，那么該字段的查詢速度會影響整個(gè)表的查詢速度，屬于熱門字段，為其建立索引非常必要。

• 常排序/分組/去重字段建索引

對于需要經(jīng)常使用ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段建立索引，可以有效借助B 樹的特性來加速執(zhí)行。

• 主鍵和外鍵建索引

主鍵可以用來創(chuàng)建聚集索引，外鍵也是唯一的且常用于表關(guān)聯(lián)的字段，也需要建索引來提高性能。

4.2 SQL的優(yōu)化

如果數(shù)據(jù)庫表的索引設(shè)置比較合理，SQL語句書寫不當(dāng)會造成索引失效，甚至造成全表掃描，迅速拉低性能。

索引失效

我們在寫SQL的時(shí)候在某些情況下會出現(xiàn)索引失效的情況：

• 對索引使用函數(shù)

select id from std upper(name) = 'JIM';

• 對索引進(jìn)行運(yùn)算

select id from std where id 1=10;

• 對索引使用<> 、not in 、not exist、!=

select id from std where name != 'jim';

• 對索引進(jìn)行前導(dǎo)模糊查詢

select id from std name like '%jim';

• 隱式轉(zhuǎn)換會導(dǎo)致不走索引

比如：字符串類型索引字段不加引號，select id from std name = 100;保持變量類型與字段類型一致

• 非索引字段的or連接

并不是所有的or都會使索引失效，如果or連接的所有字段都設(shè)置了索引，是會走索引的，一旦有一個(gè)字段沒有索引，就會走全表掃描。

• 聯(lián)合索引僅包含復(fù)合索引非前置列

聯(lián)合索引包含key1，key2，key3三列，但SQL語句沒有key1，根據(jù)聯(lián)合索引的最左匹配原則，不會走聯(lián)合索引。
select name from table where key2=1 and key3=2;

好的建議

• 使用連接代替子查詢

對于數(shù)據(jù)庫來說，在絕大部分情況下，連接會比子查詢更快，使用連接的方式，MySQL優(yōu)化器一般可以生成更佳的執(zhí)行計(jì)劃，更高效地處理查詢
而子查詢往往需要運(yùn)行重復(fù)的查詢，子查詢生成的臨時(shí)表上也沒有索引， 因此效率會更低。

• LIMIT偏移量過大的優(yōu)化

禁止分頁查詢偏移量過大，如limit 100000,10

• 使用覆蓋索引
  減少select * 借助覆蓋索引，減少回表查詢次數(shù)。

• 多表關(guān)聯(lián)查詢時(shí)，小表在前，大表在后

在MySQL中，執(zhí)行from后的表關(guān)聯(lián)查詢是從左往右執(zhí)行的，第一張表會涉及到全表掃描，所以將小表放在前面，先掃小表，掃描快效率較高，在掃描后面的大表，或許只掃描大表的前100行就符合返回條件并return了。

• 調(diào)整Where字句中的連接順序

MySQL采用從左往右的順序解析where子句，可以將過濾數(shù)據(jù)多的條件放在前面，最快速度縮小結(jié)果集。

• 使用小范圍事務(wù)，而非大范圍事務(wù)

• 遵循最左匹配原則

• 使用聯(lián)合索引，而非建立多個(gè)單獨(dú)索引

4.3 慢SQL的分析

在分析慢SQL之前需要通過MySQL進(jìn)行相關(guān)設(shè)置：

• 開啟慢SQL日志
• 設(shè)置慢SQL的執(zhí)行時(shí)間閾值

開啟：SET GLOBAL slow_query_log = 1;
開啟狀態(tài)：SHOW VARIABLES LIKE '%slow_query_log%';
設(shè)置閾值：SET GLOBAL long_query_time=3;
查看閾值：SHOW GLOBAL VARIABLES LIKE 'long_query_time%';?


explain分析SQL

explain命令只需要加在select之前即可，例如:

explain select * from std where id < 100;

該命令會展示sql語句的詳細(xì)執(zhí)行過程，幫助我們定位問題，網(wǎng)上關(guān)于explain的用法和講解很多，本文不再展開。

5. 小結(jié)

本文從慢SQL的危害、Innodb存儲引擎、聚集索引、非聚集索引、索引失效、SQL優(yōu)化、慢SQL分析等角度進(jìn)行了闡述。

MySQL的很多知識點(diǎn)都非常復(fù)雜，并非一兩篇文章能講清楚的，因此本文在很多地方顯得很單薄，好在網(wǎng)上資料非常多。

如果本文能在某些方面對讀者有所啟發(fā)，足矣。