一、識別慢 SQL
1. 啟用慢查詢日志
-- 查看當前慢查詢配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';-- 開啟慢查詢日志（臨時生效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- 設置慢查詢閾值（秒）
SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow.log';-- 記錄未使用索引的查詢
SET GLOBAL log_queries_not_using_indexes = 'ON';-- 永久生效需修改 my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1查看慢查詢日志內容:使用系統命令查看日志文件（Linux）
假設你的日志文件在 /var/lib/mysql/slow.log，可以直接用 tail、cat 或 less 查看：
tail -f /var/lib/mysql/slow.log # 實時查看新增的日志
cat /var/lib/mysql/slow.log # 查看全部內容
less /var/lib/mysql/slow.log # 分頁查看示例日志格式如下：
# Time: 2025-06-20T17:30:45.123456Z
# User@Host: root[root] @ localhost []
# Query_time: 3.123456 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1000000
SET timestamp=1750486245;
SELECT * FROM users WHERE username = 'test';- Query_time: 查詢耗時（秒），超過?
long_query_time?才會被記錄。 - Rows_examined: 掃描行數，越大越可能需要優化。
- SQL語句: 實際執行的 SQL。
2. 使用性能分析工具
-- 查看當前運行中的慢查詢
SHOW FULL PROCESSLIST;-- 使用 EXPLAIN 分析執行計劃
EXPLAIN SELECT * FROM orders WHERE customer_id = 1000;-- 使用 EXPLAIN ANALYZE（MySQL 8.0+）
EXPLAIN ANALYZE SELECT * FROM products WHERE price > 100;-- 性能分析（臨時啟用）
SET profiling = 1;
SELECT * FROM large_table;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;3.使用?EXPLAIN?分析 SQL 執行計劃的詳細指南
基礎用法（直接在 SQL 前加?EXPLAIN）
EXPLAIN SELECT *
FROM orders
WHERE user_id = 100 AND status = 'completed'
ORDER BY created_at DESC
LIMIT 10;輸出結果示例及關鍵列解析：
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | orders | NULL | ref | idx_user_id | idx_user | 4 | const | 500 | 10.00 | Using where; Using filesort |
核心列深度解析：
-
type（訪問類型）?-?最重要指標
-
性能排序：
system?>?const?>?eq_ref?>?ref?>?range?>?index?>?ALL -
優化目標：至少達到?
range?級別，避免?ALL（全表掃描） -
示例診斷：
ref?表示使用了非唯一索引? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
-
-
key（實際使用索引）
-
檢查是否使用預期索引：
idx_user（實際使用） vs?idx_user_status（可能更好的索引） -
若為?
NULL?表示未使用索引 → 需優化
-
-
rows（掃描行數）
-
預估掃描 500 行 → 結合?
filtered?列，實際返回約 500 × 10% = 50 行 -
目標：減少該值
-
-
Extra（額外信息）?-?問題高發區
-
Using filesort：手動文件排序（需優化） -
Using temporary：創建臨時表（需優化） -
Using index：覆蓋索引（好現象） -
Using where：存儲引擎返回后再次過濾
-
| 類型 (type) | 性能排序 | 含義描述 | 典型場景 | 掃描方式 | 掃描行數 | 是否用索引 | 優化建議 |
|---|---|---|---|---|---|---|---|
| system | ★★★★★ 最佳 | 系統表/僅有一行記錄 | ? MyISAM/Inemory引擎的單行系統表 ? 衍生表只有一行數據 | 直接訪問系統記錄 | 1 | ? | 無需優化 |
| const | ★★★★☆ 極優 | 主鍵/唯一索引的單行訪問 | ??WHERE id = 1?(主鍵查詢)?? WHERE unique_col = 'value'?(唯一索引) | 常量掃描 直接定位單行 | 1 | ? | 確保主鍵/唯一索引有效 |
| eq_ref | ★★★★☆ 極優 | 主鍵關聯查詢 (多表JOIN時) | ??JOIN ... ON t1.primary_key = t2.primary_key? 驅動表每行匹配被驅動表主鍵 | 唯一索引掃描 每行只匹配一次 | 1/N (N=驅動表行數) | ? | 確保JOIN字段是主鍵或唯一索引 |
| ref | ★★★☆☆ 良好 | 非唯一索引的等值查詢 | ??WHERE index_col = 'value'? 普通索引關聯查詢 ? 最左前綴匹配查詢 | 索引范圍掃描 可能返回多行 | N (匹配行數) | ? | 提升索引選擇性 添加組合索引 |
| range | ★★☆☆☆ 中等 | 索引范圍掃描 | ??WHERE id > 100?? BETWEEN 100 AND 200?? IN (1,2,3)?? LIKE 'prefix%' | 索引部分掃描 指定范圍內遍歷 | M (范圍行數) | ? | 控制掃描行數<1萬 避免大范圍掃描 |
| index | ★☆☆☆☆ 較差 | 全索引掃描 | ??SELECT indexed_col FROM table?(覆蓋索引)?? ORDER BY indexed_col?(無WHERE條件)? 索引全掃描 | 遍歷整個索引樹 不讀數據文件 | 全索引 (索引條目數) | ? | 確認是否需回表 檢查排序必要性 |
| ALL | ?? 最差 | 全表掃描 | ? 無索引字段查詢：WHERE non_index_col=...? 前導通配符： LIKE '%value%'? 未優化的JOIN條件 | 逐行掃描數據文件 性能災難 | 全表 (數據行數) | ? | 緊急優化！ ? 添加索引 ? 重寫SQL ? 限制結果集 |
?通過?EXPLAIN?分析后，若出現?ALL?或?index?且掃描行數>1000,需優化
-
優先關注?type?避免全表掃描
-
重點檢查?Extra?消除警告項
-
通過?rows?評估執行成本
-
結合?key_len?判斷索引利用率
-
每次優化后必須重新執行 EXPLAIN 驗證效果
二、慢 SQL 的常見原因
1. 索引問題
-- 缺失索引
SELECT * FROM users WHERE last_name = 'Smith'; -- 無索引-- 索引失效
SELECT * FROM orders WHERE YEAR(order_date) = 2023; -- 函數導致索引失效2. 全表掃描
-- 未使用索引導致全表掃描
SELECT * FROM products WHERE category LIKE '%electronics%';3. 復雜 JOIN
-- 多表 JOIN 未優化
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products p ON o.product_id = p.id
WHERE c.country = 'USA' AND p.price > 100;4. 大表分頁
-- 低效的分頁查詢
SELECT * FROM logs ORDER BY id LIMIT 1000000, 20;5.低效子查詢
-- 低效的子查詢
SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE country = 'China'
);?三、慢 SQL 優化方案
1. 索引優化策略
-- 添加必要索引
CREATE INDEX idx_last_name ON users(last_name);
CREATE INDEX idx_order_date ON orders(order_date);-- 使用覆蓋索引
SELECT customer_id, order_date FROM orders; -- 建立 (customer_id, order_date) 索引-- 索引合并優化
SELECT * FROM products
WHERE category_id = 5 OR price > 100; -- 分別建立兩個索引-- 強制索引使用
SELECT * FROM orders FORCE INDEX (idx_order_date)
WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';2. SQL 重寫
-- 避免 SELECT *
SELECT id, name, email FROM users;-- 分頁優化
SELECT * FROM logs WHERE id > 1000000 ORDER BY id LIMIT 20;-- JOIN 優化
SELECT o.id, c.name, p.product_name
FROM orders o
FORCE INDEX (idx_customer)
JOIN customers c ON o.customer_id = c.id
JOIN products p ON o.product_id = p.id
WHERE c.country = 'USA';-- 子查詢轉 JOIN
SELECT o.*
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'China';-- 避免函數操作
SELECT * FROM orders
WHERE order_date >= '2023-01-01'
AND order_date < '2023-02-01';3. 表結構優化
數據庫分庫分表:垂直拆分/水平拆分
4. 架構級優化
-
讀寫分離：寫操作到主庫，讀操作到從庫
-
緩存層：使用 Redis 緩存熱點數據
-
搜索引擎：復雜查詢使用 Elasticsearch
)
 附源碼)
