記一次「從2秒提升到2毫秒」的慢查詢最佳化過程

前言

在資料庫最佳化領域，慢查詢是一個常見的問題，它不僅影響系統的響應速度，還可能導致使用者體驗下降。在平時系統開發中，產品也會要求系統查詢必須在幾秒響應之內，作為後端開發人員具備系統最佳化能力是十分重要的， 本文將以個人工作實際專案分析入手，詳細介紹如何透過最佳化SQL查詢和增加索引，將一個原本耗時2秒的查詢最佳化到僅需2毫秒，歡迎大家在評論區一起討論。

1. 問題背景

本文主要是在專案中，發現有其中一個分頁查詢十分慢，遇到網路阻塞的情況下，可能需要8秒，一般情況也是需要2、3s，這其實算是必要慢的了，如圖所示，透過瀏覽器檢視需要2s多。

2. 問題分析

所以本人就從程式程式碼分析入手，逐步分析到底是什麼原因導致的。經檢視，其實該列表查詢比較簡單，重點是在SQL語句中，改SQL語句如下：

SELECT
	device.*,
	user_recode.uid 
	user_recode.start_time
	user_recode.game_id 
	user_recode.coin 
FROM
	device device
	LEFT JOIN game_user_recode user_recode ON device.id = user_recode.device_id 
	AND user_recode.use_status = 1  
WHERE
	device.`status` NOT IN (- 100 ) 
		AND device.use_flag = 1
ORDER BY
	user_recode.start_time DESC,
	device.id ASC;

上述SQL主要是主要目的是獲取裝置資訊以及相關的使用者記錄，並根據使用者記錄的開始時間和裝置ID進行排序，主要是device與game_user_recode多表查詢，分析查詢其執行順序

1. FROM子句：

• 首先，資料庫會處理FROM子句，確定需要查詢的表。在這個查詢中，有兩個表：device（別名為device）和game_user_recode（別名為user_recode）。

2. JOIN條件：

• 接下來，資料庫會處理JOIN條件。在這個查詢中，使用了LEFT JOIN，並且JOIN條件包括device.id = user_recode.device_id和user_recode.use_status = 1。這意味著資料庫會嘗試將device表中的每一行與game_user_recode表中滿足JOIN條件的行相匹配。

3. WHERE子句：

• 然後，資料庫會處理WHERE子句中的條件。在這個查詢中，WHERE子句包括多個條件，如device.status NOT IN (-100)、device.use_flag = 0。這些條件會進一步過濾JOIN操作後的結果集。

4. SELECT子句：

• 在確定了最終的結果集之後，資料庫會處理SELECT子句，選擇需要顯示的列。在這個查詢中，選擇了device.*以及user_recode表中的多個列，並使用了別名來區分它們。

5. ORDER BY子句：

• 最後，資料庫會處理ORDER BY子句，對結果集進行排序。在這個查詢中，首先按user_recode.start_time降序排序，然後按device.id升序排序。

接下來透過explain執行計劃，檢視上述SQL為什麼那麼慢，如圖所示：

可以看到，user_recode表資料很多，需要掃描全表，查詢ref的級別是ALL，也沒有走索引，很顯然問題就是出在這裏，無索引，全部掃描，肯定耗時。 device device LEFT JOIN game_user_recode這裏如果沒有索引，將會在game_user_recode全表尋找device.use_status = 1的資料

3. 最佳化策略

透過上述分析在JOIN的時候導致查詢編碼，即發現user_recode.use_status = 1這個條件在全表掃描中消耗了大量時間。因此，決定在這個欄位上增加索引。

CREATE INDEX idx_use_status ON user_recode(use_status);

4. 索引最佳化效果

增加索引後，查詢的執行順序和邏輯保持不變，但資料庫引擎可以利用這個索引快速定位到所有use_status = 1的記錄，然後只對這些記錄進行連線操作，而不是整個表的500萬條記錄。這極大地減少了需要處理的資料量，從而顯著提高了查詢效率。再次進行explain查詢分析，不再是全部掃描，並且查詢ref級別是const。

5. 驗證最佳化效果

在增加索引後，重新在系統列表查詢，在瀏覽器中可以看到，發現執行時間從2秒大幅下降到2毫秒，最佳化效果顯著。

總結

本文透過一個具體的案例，詳細介紹了慢查詢最佳化的過程和方法，手把手講解如何分析慢SQL，最終透過增加適當的索引，可以顯著提高SQL查詢的執行效率，實現從2秒到2毫秒的蛻變。 資料庫最佳化是一個持續的過程，需要不斷地監控、分析和調整。 透過合理的索引設計和查詢最佳化，可以顯著提升系統的效能和響應速度，從而提供更好的使用者體驗。希望能為讀者在實際工作中提供參考和借鑑。