目錄

慢SQL日志分析與診斷

開啟慢查詢日志

慢查詢日志分析工具

慢SQL優化策略

1. 避免SELECT * 查詢

2. 創建高效索引

索引選擇原則

索引使用注意事項

3. 使用EXPLAIN分析執行計劃

4. 優化排序操作

5. 解決深分頁問題

6. 避免全表掃描

7. 優化JOIN操作

8. 合理使用子查詢

高級優化技巧

1. 使用覆蓋索引

2. 索引條件下推(ICP)

3. 使用批處理減少交互

4. 合理使用臨時表

名詞解釋

總結

---

慢SQL日志分析與診斷

開啟慢查詢日志

-- 開啟慢查詢日志功能
SET GLOBAL slow_query_log = 'ON';-- 設置慢SQL時間閾值（單位：秒），超過此時間的查詢會被記錄
SET GLOBAL long_query_time = 5;-- 查看慢查詢日志文件位置
SHOW GLOBAL VARIABLES LIKE 'slow_query_log_file';-- 使用mysqldumpslow工具分析慢查詢日志
-- 常用參數：-s排序方式（t按時間，c按次數），-t顯示前N條
-- 示例：mysqldumpslow -s t -t 10 /var/logs/mysql/slow.log

慢查詢日志分析工具

除了mysqldumpslow，還可以使用：

1. ??pt-query-digest??（Percona Toolkit的一部分）：更強大的日志分析工具

   pt-query-digest /var/logs/mysql/slow.log

2. ??MySQL Workbench??：圖形化界面分析工具

慢SQL優化策略

1. 避免SELECT * 查詢

-- 不推薦
SELECT * FROM users WHERE id = 1;-- 推薦：只查詢需要的列
SELECT id, username, email FROM users WHERE id = 1;

??原因??：

• 增加網絡I/O負擔
• 當表中有大字段（如TEXT/BLOB）時性能影響顯著
• 可能使覆蓋索引失效（后面會解釋）

2. 創建高效索引

索引選擇原則

• ??高區分度??：選擇區分度高的列建索引（如用戶ID比性別更適合）
• ??常用查詢條件??：為WHERE、JOIN、ORDER BY、GROUP BY中的列建索引
• ??短字段優先??：整型字段比字符串字段更適合做索引

-- 創建單列索引
CREATE INDEX idx_username ON users(username);-- 創建復合索引
CREATE INDEX idx_status_created ON orders(status, created_at);

索引使用注意事項

• ??最左匹配原則??：復合索引必須從左到右使用

  -- 對于INDEX(a, b, c)
  WHERE a = 1 AND b = 2 -- 使用索引
  WHERE b = 2 AND c = 3 -- 不使用索引（缺少a）
  WHERE a = 1 AND c = 3 -- 部分使用索引（只用a）

• ??避免索引失效??：

  • 不要在索引列上使用函數：WHERE YEAR(create_time) = 2023
  • 避免隱式類型轉換：WHERE user_id = '123'（user_id是整型）
  • 避免使用!=、NOT IN、IS NULL等操作符

3. 使用EXPLAIN分析執行計劃

EXPLAIN SELECT * FROM users WHERE username = 'john';

??關鍵指標解讀??：

• ??type??：訪問類型（從好到差：system > const > eq_ref > ref > range > index > ALL）
• ??possible_keys??：可能使用的索引
• ??key??：實際使用的索引
• ??rows??：預估需要檢查的行數
• ??Extra??：額外信息（如Using filesort、Using temporary表示性能問題）

4. 優化排序操作

-- 不推薦：大數據集文件排序
SELECT * FROM products ORDER BY price DESC LIMIT 100;-- 推薦：為排序字段添加索引
ALTER TABLE products ADD INDEX idx_price (price);
SELECT * FROM products ORDER BY price DESC LIMIT 100;

??原理??：B+樹索引本身是有序的，利用索引可以避免內存排序（Using filesort）

5. 解決深分頁問題

??問題??：LIMIT 10000, 20會先讀取10020條記錄，然后丟棄前10000條

??優化方案??：

1. ??使用覆蓋索引+延遲關聯??

   SELECT * FROM products JOIN (SELECT id FROM products WHERE category_id = 5 ORDER BY created_at DESC LIMIT 10000, 20
   ) AS tmp USING(id);

2. ??記錄上次查詢位置??（適用于有序數據）

   -- 第一頁
   SELECT * FROM products ORDER BY id DESC LIMIT 20;-- 后續頁（假設上一頁最后一條記錄的id是12345）
   SELECT * FROM products WHERE id < 12345 ORDER BY id DESC LIMIT 20;

6. 避免全表掃描

• 為查詢條件添加適當的索引
• 避免在WHERE子句中對字段進行運算或使用函數
• 使用合適的查詢條件，避免過于寬泛的條件

7. 優化JOIN操作

• 確保JOIN字段有索引
• 小表驅動大表（MySQL優化器通常會自動處理）
• 避免多表JOIN（超過3個表考慮反范式化設計）

8. 合理使用子查詢

-- 不推薦：相關子查詢
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000
);-- 推薦：改用JOIN
SELECT DISTINCT u.* FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 1000;

高級優化技巧

1. 使用覆蓋索引

??覆蓋索引??：查詢的所有字段都包含在索引中，無需回表

-- 假設有INDEX(username, email)
SELECT username, email FROM users WHERE username = 'john';

??優勢??：減少I/O操作，提高查詢速度

2. 索引條件下推(ICP)

MySQL 5.6+特性，將WHERE條件推到存儲引擎層過濾

-- 假設有INDEX(a, b)
SELECT * FROM table WHERE a = 1 AND b LIKE '%test%';

3. 使用批處理減少交互

-- 不推薦
INSERT INTO users(name) VALUES ('a');
INSERT INTO users(name) VALUES ('b');-- 推薦
INSERT INTO users(name) VALUES ('a'), ('b');

4. 合理使用臨時表

對于復雜查詢，可以考慮使用臨時表分步處理

CREATE TEMPORARY TABLE temp_orders
SELECT user_id, SUM(amount) AS total 
FROM orders 
GROUP BY user_id
HAVING total > 1000;SELECT u.* FROM users u
JOIN temp_orders t ON u.id = t.user_id;

名詞解釋

1. ??B+樹??：MySQL索引的數據結構，特點是：

   • 所有數據都存儲在葉子節點
   • 葉子節點通過指針連接，適合范圍查詢
   • 樹的高度低，查詢效率穩定

2. ??回表??：當使用非主鍵索引查詢時，先通過索引找到主鍵，再通過主鍵索引查找完整數據的過程

3. ??覆蓋索引??：查詢的列都包含在索引中，無需回表

4. ??最左匹配原則??：復合索引必須從左到右使用，不能跳過前面的列

5. ??深分頁??：當LIMIT offset很大時（如LIMIT 100000, 10），MySQL需要先讀取offset+limit條記錄，性能差

6. ??Using filesort??：表示MySQL需要進行額外的排序操作，通常是因為沒有使用索引排序

7. ??Using temporary??：表示MySQL需要創建臨時表來處理查詢，常見于GROUP BY、ORDER BY等操作

總結

慢SQL優化是一個系統工程，需要：

1. 通過慢查詢日志定位問題SQL
2. 使用EXPLAIN分析執行計劃
3. 針對性應用優化策略
4. 持續監控優化效果

記住：索引不是越多越好，每個索引都會增加寫操作的成本。好的數據庫設計加上合理的索引策略，才能獲得最佳性能。