優化SQL語句是提升MySQL性能的核心手段之一，其本質是減少數據庫的數據掃描量、降低資源（CPU/IO/內存）消耗、避免低效執行計劃。優化需結合執行計劃分析、語法優化、業務邏輯適配等維度，以下是系統化的SQL優化方法，涵蓋查詢、更新、關聯查詢等常見場景：
一、先通過工具分析執行計劃
優化前必須明確SQL的執行邏輯，EXPLAIN是MySQL中分析執行計劃的核心工具，通過它可查看是否走索引、全表掃描、連接方式、數據掃描行數等關鍵信息。
-- 分析SELECT語句
EXPLAIN SELECT id, name FROM user WHERE age > 20 ORDER BY create_time;
-- 分析UPDATE/DELETE（MySQL 8.0+支持）
EXPLAIN UPDATE user SET status = 1 WHERE id = 100;關鍵字段解讀：
- type：訪問類型，從優到劣為
system > const > eq_ref > ref > range > index > ALL，ALL表示全表掃描，需重點優化。 - key：實際使用的索引，若為
NULL表示未走索引。 - rows：預估掃描的行數，數值越大性能越差。
- Extra：包含
Using filesort（文件排序，需優化）、Using temporary（臨時表，需優化）、Using index（覆蓋索引，最優）等關鍵提示。
二、基礎查詢優化
1. 避免全表掃描，強制使用索引
- 杜絕SELECT \：只查詢需要的字段，減少IO開銷和內存佔用，同時便於使用覆蓋索引*（索引包含查詢所需所有字段，無需回表）。
-- 低效：SELECT * 導致回表，且返回冗餘字段
SELECT * FROM order WHERE user_id = 123;
-- 高效：僅查詢需要的字段，且可使用覆蓋索引
SELECT id, order_no, amount FROM order WHERE user_id = 123;- 使用LIMIT限制結果集：避免一次性查詢大量數據，尤其大表需分頁查詢。
-- 低效：無LIMIT，可能返回數萬條數據
SELECT id, name FROM product WHERE status = 1;
-- 高效：分頁查詢，每次僅查100條
SELECT id, name FROM product WHERE status = 1 LIMIT 0, 100;- 避免索引失效：這是全表掃描的主要原因，常見場景及解決方案：
|
索引失效場景
|
示例
|
優化方案
|
|
WHERE條件用函數
|
|
改為 |
|
隱式類型轉換
|
|
改為 |
|
LIKE前綴模糊匹配
|
|
改為前綴匹配 |
|
複合索引不遵循最左前綴
|
索引 |
調整查詢條件為 |
2. 優化排序和分組
- 利用索引優化排序：複合索引的末尾字段包含排序字段，可避免
Using filesort。
-- 需求：按user_id篩選，按create_time排序
-- 建立複合索引：idx_user_create (user_id, create_time)
-- 高效：索引已排序，無需額外排序
SELECT id, order_no FROM order WHERE user_id = 123 ORDER BY create_time;- 分組查詢優化：
GROUP BY字段儘量走索引，避免臨時表；大表分組可先過濾再分組。
-- 低效：先分組再過濾，掃描全表
SELECT user_id, COUNT(*) FROM order GROUP BY user_id HAVING user_id > 100;
-- 高效：先過濾再分組，減少分組數據量
SELECT user_id, COUNT(*) FROM order WHERE user_id > 100 GROUP BY user_id;三、關聯查詢（JOIN）優化
大表關聯查詢是性能重災區，核心原則是小表驅動大表、減少關聯數據量、避免笛卡爾積。
1. 小表驅動大表
InnoDB默認使用嵌套循環連接（NLJ），小表驅動大表可減少外層循環次數，降低IO開銷。
-- 假設user是小表（1萬行），order是大表（1000萬行）
-- 高效：小表user驅動大表order
SELECT o.* FROM user u JOIN order o ON u.id = o.user_id WHERE u.age > 20;
-- 低效：大表order驅動小表user（循環次數多）
SELECT o.* FROM order o JOIN user u ON o.user_id = u.id WHERE u.age > 20;2. 替代子查詢
MySQL對某些子查詢（尤其是IN子查詢）優化較差，易生成臨時表，建議用JOIN替代。
-- 低效：子查詢生成臨時表，掃描大表order
SELECT * FROM order WHERE user_id IN (SELECT id FROM user WHERE age > 20);
-- 高效：JOIN直接關聯，利用索引
SELECT o.* FROM order o JOIN user u ON o.user_id = u.id WHERE u.age > 20;3. 限制關聯表的數量和數據量
- 避免超過3張大表關聯，複雜關聯可拆分為多個簡單查詢。
- 關聯前先通過
WHERE過濾掉無關數據，減少關聯的數據量。
-- 低效：關聯後再過濾，掃描大量數據
SELECT * FROM a JOIN b ON a.id = b.a_id JOIN c ON b.id = c.b_id WHERE a.status = 1;
-- 高效：先過濾小表a，再關聯
SELECT * FROM (SELECT * FROM a WHERE status = 1) a_temp JOIN b ON a_temp.id = b.a_id JOIN c ON b.id = c.b_id;四、更新/刪除語句優化
大表的更新、刪除操作需避免鎖競爭和全表掃描，核心是精準定位數據、分批執行。
1. 基於主鍵/索引更新/刪除
避免無索引的WHERE條件，否則會觸發全表掃描並加表級鎖（InnoDB行級鎖需依賴索引）。
-- 低效：status無索引，全表掃描，鎖表
UPDATE product SET price = 99 WHERE status = 0;
-- 高效：id是主鍵，精準定位，行級鎖
UPDATE product SET price = 99 WHERE id = 1001;
-- 批量更新：按索引分批執行，避免長時間鎖表
UPDATE product SET price = 99 WHERE id BETWEEN 1 AND 1000;
UPDATE product SET price = 99 WHERE id BETWEEN 1001 AND 2000;2. 避免大事務中的批量操作
大表的批量更新/刪除若放在大事務中，會導致鎖等待時間過長，甚至觸發死鎖。需將批量操作拆分為小事務，分批執行。
-- 錯誤：一次性更新10萬條，事務過大，鎖表
START TRANSACTION;
UPDATE order SET status = 2 WHERE create_time < '2025-01-01';
COMMIT;
-- 正確：分批更新，每次1000條，獨立事務
UPDATE order SET status = 2 WHERE create_time < '2025-01-01' LIMIT 1000;
UPDATE order SET status = 2 WHERE create_time < '2025-01-01' LIMIT 1000;
-- 循環執行直到更新完成五、批量操作優化
1. 批量插入優化
大表批量插入需減少IO次數，提升插入效率。
-- 低效：單條插入，多次IO
INSERT INTO product (name, price) VALUES ('商品1', 99);
INSERT INTO product (name, price) VALUES ('商品2', 199);
-- 高效：批量插入，一次IO
INSERT INTO product (name, price) VALUES ('商品1', 99), ('商品2', 199), ...;
-- 超大批量插入：拆分為每1000條一批，避免日誌刷盤壓力
INSERT INTO product (name, price) VALUES (...); -- 1000條
INSERT INTO product (name, price) VALUES (...); -- 下一批1000條- 額外優化：插入時關閉自動提交（
SET autocommit = 0），插入完成後再提交，減少事務開銷。
2. 批量查詢優化
避免使用IN查詢大量值（如IN (1,2,...,10000)），可改為JOIN或臨時表。
-- 低效：IN中值過多，解析耗時且索引失效
SELECT * FROM order WHERE user_id IN (1,2,...,10000);
-- 高效：創建臨時表，JOIN查詢
CREATE TEMPORARY TABLE temp_user (id INT PRIMARY KEY);
INSERT INTO temp_user VALUES (1),(2),...,(10000);
SELECT o.* FROM order o JOIN temp_user u ON o.user_id = u.id;六、其他高級優化技巧
1. 使用提示（Hint）強制優化器選擇執行計劃
若MySQL優化器選擇了低效的執行計劃（如未走索引），可通過FORCE INDEX強制使用指定索引。
-- 強制使用idx_user_id索引
SELECT * FROM order FORCE INDEX (idx_user_id) WHERE user_id = 123;注意：Hint僅作為臨時優化手段，優先通過優化索引和SQL讓優化器自動選擇最優計劃。
2. 利用緩存減少重複查詢
對頻繁執行的靜態查詢（如配置表查詢），可使用MySQL查詢緩存（MySQL 8.0已移除，需業務層緩存如Redis），避免重複計算。
3. 避免使用OR，改用UNION ALL
OR連接非索引字段會導致索引失效，可使用UNION ALL替代（需確保字段兼容）。
-- 低效：OR導致索引失效
SELECT * FROM user WHERE id = 100 OR age = 20;
-- 高效：UNION ALL分別走索引
SELECT * FROM user WHERE id = 100
UNION ALL
SELECT * FROM user WHERE age = 20 AND id != 100; -- 避免重複數據總結
SQL優化的核心思路是：
- 減少掃描：通過索引讓數據庫只掃描必要的數據行，避免全表掃描。
- 減少計算：避免不必要的排序、分組、臨時表，利用索引完成排序和分組。
- 減少IO：避免
SELECT *、批量操作合併IO請求、使用覆蓋索引減少回表。 - 適配業務：結合業務邏輯拆分複雜查詢、分批執行批量操作，避免過度優化。
優化後需通過EXPLAIN和慢查詢日誌驗證效果，確保執行計劃得到改善，同時需注意索引的維護成本（過多索引會降低寫性能），平衡讀和寫的性能需求。
