MySQL 高效 SQL 使用 技巧詳解
一、為什么需要優化 SQL？
- 性能瓶頸：慢查詢導致數據庫負載升高，響應時間延長。
- 資源浪費：低效 SQL 可能占用大量 CPU、內存和磁盤 I/O。
目標：通過優化 SQL 將查詢性能提升 10 倍以上，降低硬件成本。
二、核心優化原則
- 索引優先：為高頻查詢字段添加索引。
- 減少數據量：避免全表掃描，縮小查詢范圍。
- 簡化邏輯：用 JOIN 替代子查詢，避免復雜嵌套。
- 事務控制：縮短事務長度，減少鎖競爭。
三、高效 SQL 實戰技巧
1. 索引優化
（1）創建復合索引
-- 為 (user_id, create_time) 創建復合索引
CREATE INDEX idx_user_time ON orders(user_id, create_time);
適用場景：排序、范圍查詢（如 BETWEEN）。
（2）覆蓋索引
-- 查詢字段包含在索引中，避免回表
SELECT user_id, create_time FROM orders WHERE user_id = 100;
索引建議：
CREATE INDEX idx_user_time_covering ON orders(user_id, create_time) INCLUDE (status);
（3）避免索引失效
| 錯誤寫法 | 正確寫法 | 原因 |
|---|---|---|
WHERE price > 100 OR status = 'paid' | WHERE price > 100 AND status = 'paid' | OR 導致索引失效 |
WHERE name LIKE '%test' | WHERE name LIKE 'test%' | 前綴模糊查詢可利用索引 |
2. 查詢優化
（1）使用 EXPLAIN 分析執行計劃
EXPLAIN SELECT * FROM users WHERE age > 30;
關鍵指標：
type: 連接類型（理想值：ref、eq_ref）。key: 使用的索引（NULL表示全表掃描）。rows: 預估掃描行數（越小越好）。
（2）分頁優化
原查詢（慢）：
SELECT * FROM products LIMIT 100000, 10;
優化后（利用覆蓋索引）：
SELECT id, name FROM products
WHERE id > (SELECT id FROM products LIMIT 100000, 1)
LIMIT 10;
（3）批量操作替代循環
錯誤寫法：
for item in data:cursor.execute("INSERT INTO logs VALUES (%s)", item)
正確寫法：
INSERT INTO logs (col1, col2) VALUES
(1, 'a'), (2, 'b'), (3, 'c');
3. 事務與鎖優化
（1）減少鎖粒度
-- 行級鎖示例（InnoDB 默認）
UPDATE users SET balance = balance - 100 WHERE id = 100;
（2）縮短事務長度
反模式：
START TRANSACTION;
SELECT * FROM large_table; -- 耗時操作
UPDATE orders SET status = 'paid';
COMMIT;
優化后：
START TRANSACTION;
UPDATE orders SET status = 'paid';
COMMIT;
4. 存儲引擎選擇
| 引擎 | 特點 | 適用場景 |
|---|---|---|
| InnoDB | 支持事務、行級鎖、外鍵 | 高并發讀寫（如電商訂單） |
| MyISAM | 表級鎖、不支持事務 | 讀多寫少（如日志表） |
| Memory | 內存存儲、速度極快 | 臨時數據緩存 |
四、高級優化技巧
1. 子查詢轉 JOIN
原查詢：
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE city = 'Shanghai');
優化后：
SELECT o.*
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.city = 'Shanghai';
2. 避免 SELECT *
反模式：
SELECT * FROM employees;
正確寫法：
SELECT id, name, email FROM employees;
3. 使用 LIMIT 限制結果集
-- 快速獲取最新記錄
SELECT * FROM logs ORDER BY create_time DESC LIMIT 10;
五、實戰案例：優化電商訂單查詢
優化前 SQL
SELECT * FROM orders
WHERE user_id = 100 AND status = 'paid'
ORDER BY create_time DESC;
問題：
- 全表掃描（無索引）。
- 回表查詢所有字段。
優化步驟
-
添加復合索引：
CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time); -
僅查詢必要字段：
SELECT id, amount, create_time FROM orders WHERE user_id = 100 AND status = 'paid' ORDER BY create_time DESC;
優化后效果
- 查詢時間：從 2.1 秒降至 0.05 秒。
- 索引覆蓋度：100%（無需回表）。
六、注意事項
1. 索引維護成本
- 索引過多會降低寫入性能。
- 建議：為查詢頻率高的字段創建索引。
2. 慢查詢日志
-- 開啟慢查詢日志（超過 2 秒的查詢）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;
3. 測試環境驗證
# 使用 sysbench 壓測
sysbench --test=oltp --oltp-table-size=1000000 --mysql-user=root run
七、總結：高效 SQL 檢查表
| 優化項 | 操作 | 工具/命令 |
|---|---|---|
| 索引 | 使用 EXPLAIN 分析執行計劃 | EXPLAIN |
| 查詢 | 避免 SELECT *，使用覆蓋索引 | EXPLAIN |
| 事務 | 縮短事務長度，減少鎖競爭 | 監控 InnoDB_row_lock_waits |
| 存儲引擎 | 根據場景選擇合適引擎 | SHOW TABLE STATUS |
| 慢查詢 | 開啟慢查詢日志并分析 | pt-query-digest |
