阿里妹導讀
本文深入探討了MySQL資料庫中慢查詢分析的關鍵概念和技術手段。
慢查詢分析
對於MySQL，最簡單的衡量查詢開銷的三個指標如下：
-
響應時間：服務時間和排隊時間之和。服務時間是指資料庫處理這個查詢真正花了多長時間。排隊時間是指伺服器因為等待某些資源而沒有真正執行查詢的時間——可能是等I/O操作完成，也可能是等待行鎖。
-
掃描的行數：一條查詢，如果效能很差，最常見的原因是訪問的資料太多。大部分效能低下的查詢都可以透過減少訪問的資料量的方式進行最佳化。有時候也可能是訪問了太多的列；（每次看到SELECT*的時候都需要用懷疑的眼光審視，是不是真的需要返回全部的列，很可能不是必需的。取出全部列，會讓最佳化器無法完成索引覆蓋掃描這類最佳化，還會為伺服器帶來額外的I/O、記憶體和CPU的消耗）
-
返回的行數：會給伺服器帶來額外的I/O、記憶體和CPU的消耗（使用limit限制返回行數）
分析示例
我們看一下示例資料庫Sakila中的一個查詢案例：
select * from film_actor where film_id = 1 -- film_id列有索引這個查詢將返回10行資料，從EXPLAIN的結果可以看到，MySQL在索引idx_fk_film_id上使用了ref訪問型別來執行查詢：
EXPLAIN的結果還顯示MySQL預估需要訪問10行資料。換句話說，查詢最佳化器認為這種訪問型別可以高效地完成查詢。
如果沒有合適的索引會怎樣呢？MySQL就不得不使用一種糟糕的訪問型別，下面來看看如果刪除對應的索引再來執行這個查詢會發生什麼情況：
訪問型別變成了一個全表掃描(ALL)，現在MySQL預估需要掃描5462條記錄來完成這個查詢。這裡的“Using where”表示MySQL將透過WHERE條件來篩選儲存引擎返回的記錄。
一般地，MySQL能夠使用如下三種方式應用WHERE條件，從好到壞依次為：
-
在索引中使用WHERE條件來過濾不匹配的記錄。這是在儲存引擎層完成的。
-
使用索引覆蓋掃描（在Extra列中出現了Using index）來返回記錄，直接從索引中過濾不需要的記錄並返回命中的結果。這是在MySQL伺服器層完成的，但無須再回表查詢記錄。
舉例說明一下：索引列a，b，c，查詢條件時 a = xx and c = xx
-
從資料表中返回資料，然後過濾不滿足條件的記錄（在Extra列中出現Using where）。這在MySQL伺服器層完成，MySQL需要先從資料表中讀出記錄然後過濾。
三星索引
“三星系統”(three-star system)評價體系，用以判斷一個索引是不是適合某個查詢語句：
-
索引將相關的記錄放到一起則獲得“一星”；
-
如果索引中的資料順序和查詢中的排列順序一致則獲得“二星”；
-
如果索引中的列包含了查詢中需要的全部列則獲得“三星”。
聚簇索引
聚簇索引並不是一種單獨的索引型別，而是一種資料儲存方式，術語“聚簇”表示資料行和相鄰的鍵值緊湊地儲存在一起。InnoDB的聚簇索引實際上儲存了B-tree索引和資料行（聚簇索引的每一個葉子節點都包含了主鍵值、事務ID、用於事務和MVCC的回滾指標，以及所有的剩餘列）。當表有聚簇索引時，它的資料行實際上存放在索引的葉子頁(leaf page)中。一個表只能有一個聚簇索引，葉子頁包含了一條記錄的全部資料。
InnoDB根據主鍵聚簇資料。如果你沒有定義主鍵，InnoDB會選擇一個唯一的非空索引代替。如果沒有這樣的索引，InnoDB會隱式定義一個主鍵來作為聚簇索引。聚集的資料有一些重要的優點：
-
可以把相互關聯的資料儲存在一起，資料訪問更快。聚簇索引將索引和資料儲存在同一個B-tree中，因此從聚簇索引中獲取資料通常比在非聚簇索引中查詢要快。
同時，聚簇索引也有一些缺點：
-
插入速度嚴重依賴於插入順序。按照主鍵的順序插入行是將資料載入到InnoDB表中最快的方式。但如果不是按照主鍵的順序載入資料，那麼在載入完成後最好使用OPTIMIZE TABLE命令重新組織一下表。
-
更新聚簇索引列的代價很高，因為它會強制InnoDB將每個被更新的行移動到新的位置。
-
基於聚簇索引的表在插入新行，或者主鍵被更新導致需要移動行的時候，可能面臨頁分裂(page split)的問題。當行的主鍵值要求必須將這一行插入某個已滿的頁中時，儲存引擎會將該頁分裂成兩個頁面來容納該行，這就是一次頁分裂操作。頁分裂會導致表佔用更多的磁碟空間。
覆蓋索引
如果一個索引包含（或者說覆蓋）所有需要查詢的欄位的值，我們就稱之為覆蓋索引。覆蓋索引是非常有用的工具，能夠極大地提高效能。試想一下，如果查詢只需要掃描索引而無須回表，會帶來多少好處：
-
索引條目通常遠小於資料行大小，所以如果只需要讀取索引，那麼MySQL就會極大地減少資料訪問量。
-
因為索引是按照列值的順序儲存的（至少在單頁內如此），所以對於I/O密集型的範圍查詢會比隨機從磁碟讀取每一行資料的I/O要少得多。
-
由於InnoDB的聚簇索引的特點，覆蓋索引對InnoDB表特別有用。InnoDB的二級索引在葉子節點中儲存了記錄的主鍵值，所以如果二級索引能夠覆蓋查詢，則可以避免對主鍵索引的二次查詢。（二級索引訪問需要兩次索引查詢，而不是一次，透過二級索引查詢行，儲存引擎需要找到二級索引的葉子節點，以獲得對應的主鍵值，然後根據這個值去聚簇索引中查詢對應的行。）
索引列順序（怎麼指定索引列順序）
不需要考慮排序和分組時，將選擇性最高的列放在前面通常是很好的。這時索引的作用只是最佳化查詢語句中的WHERE條件。在這種情況下，按這個原則設計的索引確實能夠最快地過濾出需要的行。以下面的查詢為例：
select * from payment where staff_id = 123and customer_id = 456是應該建立一個(staff_id、customer_id)索引還是應該顛倒一下順序？這時，可以透過執行某些查詢來確定在這個表中值的分佈情況，並確定哪列的選擇性更高。先用下面的查詢預測一下，看看各個WHERE條件的分支對應的資料基數有多大：
根據前面的經驗法則，應該將索引列customer_id放到前面，因為對應條件值的customer_id數量更小。
索引最左字首法則
指在MySQL中使用索引時，查詢條件需要從索引的最左邊開始，並且不能跳過索引中的列。如果查詢條件跳過了索引中的某列，那麼索引將失效，後續的列也不會被使用。
select * from payment where staff_id = 123 -- 索引是customer_id,staff_id，因為漏掉了customer_id，所以走不上索引排序是否命中索引
無論如何排序都是一個成本很高的操作，所以從效能角度考慮，應儘可能避免排序或者儘可能避免對大量資料進行排序。當不能使用索引生成排序結果的時候，MySQL需要自己進行排序，如果資料量小則在記憶體中進行，如果資料量大則需要使用磁碟，不過MySQL將這個過程統一稱為檔案排序(filesort)，即使完全是在記憶體中排序不需要任何磁碟檔案時也是如此。
只有當索引的順序和ORDER BY子句的順序完全一致，並且所有列的排序方向（倒序或正序）都一樣時，MySQL才能使用索引來對結果做排序。如果查詢需要聯接多張表，則只有當ORDER BY子句引用的欄位全部在第一個表中時，才能使用索引做排序。ORDER BY子句和查詢型查詢的限制是一樣的：需要滿足索引的最左字首的要求，否則，MySQL需要執行排序操作，而無法利用索引排序。
有一種特殊情況，如果前導列為常量的時候，ORDER BY子句中的列也可以不滿足索引的最左字首的要求。如果在WHERE子句或者JOIN子句中將這些列指定為了常量，就可以“填補”索引欄位的間隙了。
例如，Sakila示例資料庫的表rental在列(rental_date，inventory_id，customer_id)上建有名稱為rental_date的索引：
不需要檔案排序
MySQL可以使用rental_date索引為下面的查詢做排序，從EXPLAIN中可以看到沒有出現檔案排序(filesort)操作：
select * from rental where rental_date = '2005-05-25' order by inventory_id, customer_id -- rental_date是常量，後面的排序列則認為符合最左字首select * from rental where rental_date = '2005-05-25' order by inventory_id, customer_id,id -- rental_date是常量，後面的排序列則認為符合最左字首，雖然索引裡面沒有顯示指定id列，id是隱性的包含在索引中的，所以也無需檔案排序即使ORDER BY子句不滿足索引的最左字首的要求，也可以用於查詢排序，這正是因為索引的第一列被指定為了一個常數。
需要檔案排序
-
下面這個查詢也沒有問題，因為ORDER BY使用的兩列就是索引的最左字首(索引順序：rental_date，inventory_id，customer_id)：
select * from rental where rental_date > '2005-05-25' order by rental_date, inventory_id -- 符合最左字首下面是一些不能使用索引做排序的查詢：
-
下面這個查詢使用了兩種不同的排序方向，但是索引中的列都是按正序排序的(索引順序：rental_date，inventory_id，customer_id)：
select * from rental where rental_date = '2005-05-25' order by inventory_id asc, customer_id desc -- 滿足最左字首，但是排序方向不一樣-
在下面這個查詢的ORDER BY子句中，引用了一個不在索引中的列(索引順序：rental_date，inventory_id，customer_id)：
select * from rental where rental_date = '2005-05-25' order by inventory_id ,staff_id -- staff_id不在索引中-
下面這個查詢的WHERE和ORDER BY中的列無法組合成索引的最左字首(索引順序：rental_date，inventory_id，customer_id)：
select * from rental where rental_date = '2005-05-25' order by customer_id -- 不滿足最左字首，漏掉了inventory_id-
下面這個查詢在索引列的第一列上是範圍條件，所以MySQL無法使用索引的其餘列(索引順序：rental_date，inventory_id，customer_id)：
select * from rental where rental_date > '2005-05-25' order by inventory_id -- 第一列如果是範圍查詢，則認為不符合最左字首-
這個查詢在inventory_id列上有多個等於條件。對於排序來說，這也是一種範圍查詢(索引順序：rental_date，inventory_id，customer_id)：
select * from rental where rental_date = '2005-05-25'and inventory_id in (1,2) order by customer_id -- inventory_id 條件是範圍查詢，則認為不符合最左字首10分鐘構建能主動提問的智慧導購
為助力商家全天候自動化滿足顧客的購物需求，可透過百鍊構建一個 Multi-Agent 架構的大模型應用實現智慧導購助手。該系統能夠主動詢問顧客所需商品的具體引數，一旦收集齊備，便會自動從商品資料庫中檢索匹配的商品，並精準推薦給顧客。
點選閱讀原文檢視詳情。
