15 張圖破局,慢 SQL

今天和大家聊一個常見的問題:慢 SQL。

通過本文你將瞭解到以下內容:

後續均以 MySQL 默認存儲引擎 InnoDB 爲例進行展開,話不多說,開搞!

  1. 慢 SQL 的危害

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

試想一個場景:

大白和小黑端午出去玩,機票太貴於是買了高鐵,火車站的人真是烏央烏央的。

馬上檢票了,大白和小黑準備去廁所清理下庫存,坑位不多,排隊的人還真不少。

小黑髮現其中有 3 個坑的乘客賊慢,其他 2 個坑位換了好幾波人,這 3 位坑主就是不出來。

等在外面的大夥,心裏很是不爽,長期佔用公共資源,後面的人沒法用。

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

這是實際生活中的例子,換到 MySQL 服務器也是一樣的,畢竟科技源自生活嘛。

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

2.SQL 語句執行過程

客戶端和 MySQL 服務端的交互過程簡介:

  1. 客戶端發送一條 SQL 語句給服務端,服務端的連接器先進行賬號 / 密碼、權限等環節驗證,有異常直接拒絕請求。

  2. 服務端查詢緩存,如果 SQL 語句命中了緩存,則返回緩存中的結果,否則繼續處理。

  3. 服務端對 SQL 語句進行詞法解析、語法解析、預處理來檢查 SQL 語句的合法性。

  4. 服務端通過優化器對之前生成的解析樹進行優化處理,生成最優的物理執行計劃。

  5. 將生成的物理執行計劃調用存儲引擎的相關接口,進行數據查詢和處理。

  6. 處理完成後將結果返回客戶端。

客戶端和 MySQL 服務端的交互過程簡圖:

俗話說 "條條大路通羅馬",優化器的作用就是找到這麼多路中最優的那一條。

存儲引擎更是決定 SQL 執行的核心組件,適當瞭解其中原理十分有益。

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

3.1 存儲引擎

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

存儲引擎的主要作用是進行數據的存取和檢索,也是真正執行 SQL 語句的組件。

InnoDB 的整體架構分爲兩個部分:內存架構和磁盤架構,如圖:

存儲引擎的內容非常多,並不是一篇文章能說清楚的,本文不過多展開,我們在此只需要瞭解內存架構和磁盤架構的大致組成即可。

InnoDB 引擎是面向行存儲的,數據都是存儲在磁盤的數據頁中,數據頁裏面按照固定的行格式存儲着每一行數據。

行格式主要分爲四種類型 Compact、Redundant、Dynamic 和 Compressed,默認爲 Compact 格式。

磁盤預讀機制和局部性原理

當計算機訪問一個數據時,不僅會加載當前數據所在的數據頁,還會將當前數據頁相鄰的數據頁一同加載到內存,磁盤預讀的長度一般爲頁的整倍數,從而有效降低磁盤 IO 的次數。

磁盤和內存的交互

MySQL 中磁盤的數據需要被交換到內存,才能完成一次 SQL 交互,大致如圖:

隨機磁盤 IO

MySQL 的數據是一行行存儲在磁盤上的,並且這些數據並非物理連續地存儲,這樣的話要查找數據就無法避免隨機在磁盤上讀取和寫入數據。

對於 MySQL 來說,當出現大量磁盤隨機 IO 時,大部分時間都被浪費到尋道上,磁盤呼嚕呼嚕轉,就是傳輸不了多少數據。

一次磁盤訪問由三個動作組成:

  • 尋道:磁頭移動定位到指定磁道

  • 旋轉:等待指定扇區從磁頭下旋轉經過

  • 數據傳輸:數據在磁盤與內存之間的實際傳輸

對於存儲引擎來說,如何有效降低隨機 IO 是個非常重要的問題。

3.2 索引

可以實現增刪改查的數據結構非常多,包括:哈希表、二叉搜索樹、AVL、紅黑樹、B 樹、B + 樹等,這些都是可以作爲索引的候選數據結構。

結合 MySQL 的實際情況:磁盤和內存交互、隨機磁盤 IO、排序和範圍查找、增刪改的複雜度等等,綜合考量之下 B + 樹脫穎而出。

B + 樹作爲多叉平衡樹,對於範圍查找和排序都可以很好地支持,並且更加矮胖,訪問數據時的平均磁盤 IO 次數取決於樹的高度,因此 B + 樹可以讓磁盤的查找次數更少。

在 InnoDB 中 B + 樹的高度一般都在 2~4 層,並且根節點常駐內存中,也就是說查找某值的行記錄時最多隻需要 1~3 次磁盤 I/O 操作。

MyISAM 是將數據和索引分開存儲的,InnoDB 存儲引擎的數據和索引沒有分開存儲,這也就是爲什麼有人說 Innodb 索引即數據,數據即索引,如圖:

說到 InnoDB 的數據和索引的存儲,就提到一個名詞:聚集索引

聚集索引

聚集索引將索引和數據完美地融合在一起,是每個 Innodb 表都會有的一個特殊索引,一般來說是藉助於表的主鍵來構建的 B + 樹。

假設我們有 student 表,將 id 作爲主鍵索引,那麼聚集索引的 B + 樹結構,如圖:

聚集索引按照如下規則創建:

假如我們要查找 id=10 的數據,大致過程如下:

非聚集索引

非聚集索引的葉子節點中存放的是二級索引值和主鍵鍵值,非葉子節點和葉子節點都沒有存儲整行數據值。

假設我們有 student 表,將 name 作爲二級索引,那麼非聚集索引的 B + 樹結構,如圖:

由於非聚集索引的葉子節點沒有存儲行數據,如果通過非聚集索引來查找非二級索引值,需要分爲兩步:

我們把通過非聚集索引找到主鍵值,再根據主鍵值從聚集索引找對於行數據的過程稱爲:回表查詢

換句話說:select * from student where name = 'Bob' 將產生回表查詢,因爲在 name 索引的葉子節點沒有其他值,只能從聚集索引獲得。

所以如果查找的字段在非聚集索引就可以完成,就可以避免一次回表過程,這種稱爲:覆蓋索引,所以 select * 並不是好習慣,需要什麼拿什麼就好。

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

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

  1. 慢 SQL 解決思路

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

我們從兩個方面來進行闡述:

4.1 索引設置原則

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

假如表中有 1000w 記錄,其中有 status 字段表示狀態,可能 90% 的數據 status=1,可以不將 status 作爲索引,因爲其對數據記錄區分度很低。

每個索引都需要佔用磁盤空間,修改表數據時會對索引進行更新,索引越多,更新越複雜。

因爲每添加一個索引,.ibd 文件中就需要多維護一個 B+Tree 索引樹,如果某一個 table 中存在 10 個索引,那麼就需要維護 10 棵 B+Tree,寫入效率會降低,並且會浪費磁盤空間。

如果某個字段經常用來做查詢條件,那麼該字段的查詢速度會影響整個表的查詢速度,屬於熱門字段,爲其建立索引非常必要。

對於需要經常使用 ORDER BY、GROUP BY、DISTINCT 和 UNION 等操作的字段建立索引,可以有效藉助 B + 樹的特性來加速執行。

主鍵可以用來創建聚集索引,外鍵也是唯一的且常用於表關聯的字段,也需要建索引來提高性能。

4.2 SQL 的優化

如果數據庫表的索引設置比較合理,SQL 語句書寫不當會造成索引失效,甚至造成全表掃描,迅速拉低性能。

索引失效

我們在寫 SQL 的時候在某些情況下會出現索引失效的情況:

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

select id from std where id+1=10;

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

select id from std name like '%jim';

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

並不是所有的 or 都會使索引失效,如果 or 連接的所有字段都設置了索引,是會走索引的,一旦有一個字段沒有索引,就會走全表掃描。

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

好的建議

對於數據庫來說,在絕大部分情況下,連接會比子查詢更快,使用連接的方式,MySQL 優化器一般可以生成更佳的執行計劃,更高效地處理查詢
而子查詢往往需要運行重複的查詢,子查詢生成的臨時表上也沒有索引, 因此效率會更低。

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

在 MySQL 中,執行 from 後的表關聯查詢是從左往右執行的,第一張表會涉及到全表掃描,所以將小表放在前面,先掃小表,掃描快效率較高,在掃描後面的大表,或許只掃描大表的前 100 行就符合返回條件並 return 了。

MySQL 採用從左往右的順序解析 where 子句,可以將過濾數據多的條件放在前面,最快速度縮小結果集。

4.3 慢 SQL 的分析

在分析慢 SQL 之前需要通過 MySQL 進行相關設置:

開啓:SET GLOBAL slow_query_log = 1;
開啓狀態:SHOW VARIABLES LIKE '%slow_query_log%';
設置閾值: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 語句的詳細執行過程,幫助我們定位問題,網上關於 explain 的用法和講解很多,本文不再展開。

  1. 小結

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

MySQL 的很多知識點都非常複雜,並非一兩篇文章能講清楚的,因此本文在很多地方顯得很單薄,好在網上資料非常多。

如果本文能在某些方面對讀者有所啓發,足矣。

本文由 Readfog 進行 AMP 轉碼,版權歸原作者所有。
來源https://mp.weixin.qq.com/s/fCk-uZyi5k1hKwrURZNe7A