一、數據庫故障的關鍵點
引起數據庫故障的因素有操作系統層面、存儲層面，還有斷電斷網的基礎環境層面（以下稱為外部因素），以及應用程序操作數據庫和人為操作數據庫這兩個層面（以下稱內部因素）。這些故障中外部因素發生的概率較小，可能幾年都未發生過一起。許多 DBA 從入職到離職都可能沒有遇到過外部因素導致的故障，但是內部因素導致的故障可能每天都在產生。內部因素中占據主導的是應用程序導致的故障，可以說數據庫故障的主要元兇就是應用程序的 SQL 寫的不夠好。
SQL 是由開發人員編寫的，但是責任不完全是開發人員的。
- SQL 的成因：SQL 是為了實現特定的需求而編寫的，那么需求的合理性是第一位的。一般來說，在合理的需求下即使有問題的 SQL 也是可以挽救的。但是如果需求不合理，那么就為 SQL 問題埋下了隱患。
- SQL 的設計：這里的設計主要是數據庫對象的設計。即使是合理的需求，椰果在數據庫設計層面沒有把控的緩解或者保證，那么很多優化就會大打折扣。
- SQL 的實現：這部分是開發人員所涉及的工作。但是這已經是流程的末端，這個時候改善的手段依然有，但是屬于挽救措施。
在筆者多年的工作中，數據庫故障主要來源于三個方面：不合理的需求、不合理的設計和不合理的實現。而這些如果從管理和流程上明確規定由經驗豐富的 DBA 介入，那么對數據庫故障的源頭是有很大的控制作用的。
上述摘自薛曉剛老師的 《DBA 實戰手記》 3.2 節。
二、慢 SQL 的常見成因
在分析 SQL 語句時，SQL 運行緩慢絕對是最主要的問題，沒有之一。慢 SQL 是數據庫性能瓶頸的主要表現，其核心成因可歸納為以下幾類：
- 索引問題：缺失索引導致全表掃描、索引失效（如函數操作索引列、隱式類型轉換）、索引設計不合理（單值索引 vs 復合索引選擇錯誤）。
- 查詢寫法缺陷：SELECT * 全字段查詢、復雜子查詢嵌套、無過濾條件的大范圍掃描、低效 JOIN 操作。
- 數據量與結構：表數據量過大未分區、字段類型設計不合理（如用 VARCHAR 存儲數字）、大字段（TEXT/BLOB）頻繁查詢。
- 執行計劃異常：優化器誤判（統計信息過時）、JOIN 順序錯誤、臨時表與文件排序濫用。
下面通過實例表和純 SQL 生成的數據，結合執行計劃工具詳解優化方法。
三、實驗表結構及數據
1. 表結構（電商場景）
-- 用戶表
CREATE TABLE users (user_id INT AUTO_INCREMENT PRIMARY KEY,username VARCHAR(50) NOT NULL,email VARCHAR(100) UNIQUE,age INT,register_time DATETIME,INDEX idx_age (age),INDEX idx_register_time (register_time)
) ENGINE=InnoDB;-- 商品表
CREATE TABLE products (product_id INT AUTO_INCREMENT PRIMARY KEY,product_name VARCHAR(100) NOT NULL,price DECIMAL(10,2),category_id INT,stock INT,INDEX idx_category (category_id),INDEX idx_name_price (product_name, price)
) ENGINE=InnoDB;-- 訂單表
CREATE TABLE orders (order_id INT AUTO_INCREMENT PRIMARY KEY,user_id INT NOT NULL,product_id INT NOT NULL,order_time DATETIME,amount DECIMAL(10,2),status TINYINT, -- 1:待支付 2:已支付 3:已取消INDEX idx_user_time (user_id, order_time),INDEX idx_product_id (product_id)
) ENGINE=InnoDB;
2. 生成測試數據（存儲過程）
生成用戶數據（10 萬條）
DELIMITER //
CREATE PROCEDURE prod_generate_users()
BEGINDECLARE i INT DEFAULT 1;DECLARE batch_size INT DEFAULT 1000; -- 每批處理1000條記錄DECLARE total INT DEFAULT 100000; -- 總記錄數WHILE i <= total DOSTART TRANSACTION;-- 插入當前批次的記錄WHILE i <= total AND i <= (batch_size * FLOOR((i-1)/batch_size) + batch_size) DOINSERT INTO users (username, email, age, register_time)VALUES (CONCAT('user_', i),CONCAT('user_', i, '@example.com'),FLOOR(RAND() * 40) + 18,DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY));SET i = i + 1;END WHILE;COMMIT;END WHILE;
END //
DELIMITER ;-- 執行存儲過程
CALL prod_generate_users();
生成商品數據 1 萬條）
DELIMITER //
CREATE PROCEDURE prod_generate_products()
BEGINDECLARE i INT DEFAULT 1;DECLARE batch_size INT DEFAULT 1000; -- 每批處理1000條記錄DECLARE total INT DEFAULT 10000; -- 總記錄數WHILE i <= total DOSTART TRANSACTION;-- 插入當前批次的記錄WHILE i <= total AND i <= (batch_size * FLOOR((i-1)/batch_size) + batch_size) DOINSERT INTO products (product_name, price, category_id, stock)VALUES (CONCAT('product_', i),ROUND(RAND() * 999 + 1, 2), -- 1-1000元FLOOR(RAND() * 20) + 1, -- 1-20類分類FLOOR(RAND() * 1000) + 10 -- 10-1009庫存);SET i = i + 1;END WHILE;COMMIT;END WHILE;
END //
DELIMITER ;CALL prod_generate_products();
生成訂單數據（100 萬條）
DELIMITER //
CREATE PROCEDURE prod_generate_orders()
BEGINDECLARE i INT DEFAULT 1;DECLARE batch_size INT DEFAULT 500; -- 每批處理500條記錄（訂單數據量大，批次更小）DECLARE total INT DEFAULT 1000000; -- 總記錄數DECLARE max_user INT;DECLARE max_product INT;DECLARE rand_product_id INT;DECLARE product_price DECIMAL(10,2);-- 獲取用戶和商品的最大IDSELECT MAX(user_id) INTO max_user FROM users;SELECT MAX(product_id) INTO max_product FROM products;WHILE i <= total DOSTART TRANSACTION;-- 插入當前批次的記錄WHILE i <= total AND i <= (batch_size * FLOOR((i-1)/batch_size) + batch_size) DO-- 優化：預先計算隨機商品ID和價格，避免子查詢SET rand_product_id = FLOOR(RAND() * max_product) + 1;SELECT price INTO product_price FROM products WHERE product_id = rand_product_id LIMIT 1;INSERT INTO orders (user_id, product_id, order_time, amount, status)VALUES (FLOOR(RAND() * max_user) + 1, -- 隨機用戶rand_product_id, -- 隨機商品DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 365) DAY), -- 近1年訂單product_price * (FLOOR(RAND() * 5) + 1), -- 1-5件數量FLOOR(RAND() * 3) + 1 -- 隨機狀態);SET i = i + 1;END WHILE;COMMIT;END WHILE;
END //
DELIMITER ;CALL prod_generate_orders();
查看數據
select count(1) from users
union all
select count(1) from products
union all
select count(1) from orders;+----------+
| count(1) |
+----------+
| 100000 |
| 10000 |
| 1000000 |
+----------+
四、執行計劃詳解：從分析到優化完整指南
三類工具的選擇指南
| 工具 | 核心價值 | 適用場景 |
|---|---|---|
EXPLAIN | 快速預判執行計劃（預估） | 日常開發、索引設計驗證、排查明顯低效操作 |
optimizer_trace | 深入優化器決策過程（分析“為什么這么做”） | 復雜查詢的索引選擇問題、JOIN 順序優化 |
EXPLAIN ANALYZE | 量化實際執行性能（精確耗時、行數） | 性能瓶頸量化、優化效果對比、分頁/排序分析 |
通過這三類工具的組合使用，可從“預判”到“分析”再到“量化”，全面掌握 MySQL 查詢的執行邏輯，精準定位并解決性能問題。
1. 基礎分析工具：EXPLAIN——預判查詢執行邏輯
EXPLAIN是 MySQL 中最常用的執行計劃分析工具，無需實際執行查詢，即可返回優化器對查詢的執行方案（如索引選擇、掃描方式等），幫助提前發現性能隱患。
核心語法與使用場景
-- 對任意SELECT查詢執行分析
EXPLAIN SELECT 列名 FROM 表名 WHERE 條件;
-- 支持復雜查詢（JOIN、子查詢等）
EXPLAIN SELECT u.username, o.order_id FROM users u JOIN orders o ON u.user_id = o.user_id WHERE u.age > 30;
+----+-------------+-------+------------+------+----------------------+----------------------+---------+------------------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------------+----------------------+---------+------------------+-------+----------+-------------+
| 1 | SIMPLE | u | NULL | ALL | PRIMARY,idx_age | NULL | NULL | NULL | 99864 | 50.00 | Using where |
| 1 | SIMPLE | o | NULL | ref | idx_user_time_amount | idx_user_time_amount | 4 | testdb.u.user_id | 9 | 100.00 | Using index |
+----+-------------+-------+------------+------+----------------------+----------------------+---------+------------------+-------+----------+-------------+
2 rows in set, 1 warning (0.14 sec)
適用場景：
- 快速判斷查詢是否使用索引、是否存在全表掃描；
- 分析 JOIN 語句的表連接順序和連接方式；
- 定位
Using filesort（文件排序）、Using temporary（臨時表）等低效操作。
字段深度解讀
在 MySQL 的 EXPLAIN 執行計劃中，除了 type、key、rows、Extra 這幾個核心字段外，其他字段也承載著查詢執行邏輯的關鍵信息。以下是 EXPLAIN 所有字段的完整含義：
| 字段名 | 核心含義 | 補充說明 |
|---|---|---|
id | 查詢中每個操作的唯一標識（多表/子查詢時用于區分執行順序）。 | - 若 id 相同：表示操作在同一層級，按表的順序（從左到右）執行（如 JOIN 時的驅動表和被驅動表）。- 若 id 不同：id 越大優先級越高，先執行（如子查詢會嵌套在主查詢內部，id 更大）。 |
select_type | 查詢的類型（區分簡單查詢、子查詢、聯合查詢等）。 | 常見值： - SIMPLE：簡單查詢（無子查詢、JOIN 等復雜結構）。- PRIMARY：主查詢（包含子查詢時，最外層的查詢）。- SUBQUERY：子查詢（SELECT 中的子查詢，不依賴外部結果）。- DERIVED：衍生表（FROM 中的子查詢，會生成臨時表）。- UNION：UNION 語句中第二個及以后的查詢。- UNION RESULT：UNION 結果集的合并操作。 |
table | 當前操作涉及的表名（或臨時表別名，如 derived2 表示衍生表）。 | 若為 NULL：可能是 UNION RESULT（合并結果集時無具體表），或子查詢的中間結果。 |
partitions | 查詢匹配的分區（僅對分區表有效）。 | 非分區表顯示 NULL；分區表會顯示匹配的分區名稱（如 p2023 表示命中 p2023 分區）。 |
type | 訪問類型（索引使用效率等級，最關鍵的性能指標）。 | 詳見前文“type 字段優先級與解讀”，從優到差反映索引利用效率（如 const > ref > ALL）。 |
possible_keys | 優化器認為可能使用的索引（候選索引列表）。 | 該字段僅表示“可能有效”的索引，不代表實際使用；若為 NULL，說明沒有可用索引。 |
key | 實際使用的索引（NULL 表示未使用任何索引）。 | 若 possible_keys 有值但 key 為 NULL，可能是索引選擇性差（如字段值重復率高）或優化器判斷全表掃描更快。 |
key_len | 實際使用的索引長度（字節數）。 | 用于判斷復合索引的使用情況： - 若 key_len 等于復合索引總長度，說明整個索引被使用；- 若較短，說明僅使用了復合索引的前綴部分（需檢查是否因類型不匹配導致索引截斷，如字符串未指定長度）。 |
ref | 表示哪些字段或常量被用來匹配索引列。 | - 若為常量（如 const）：表示用固定值匹配索引（如 WHERE id=1）。- 若為表名.字段（如 u.user_id）：表示用其他表的字段關聯當前表的索引（如 JOIN 時的關聯條件）。 |
rows | 優化器預估的掃描行數（基于表統計信息估算）。 | 數值越小越好，反映查詢的“工作量”；若遠大于實際數據量，可能是統計信息過時，需執行 ANALYZE TABLE 表名 更新。 |
filtered | 經過過濾條件后，剩余記錄占預估掃描行數的比例（百分比）。 | 如 filtered=50 表示掃描的 rows 中，有 50% 滿足過濾條件；值越高，說明過濾效率越好（減少后續處理的數據量）。 |
Extra | 額外的執行細節（補充說明索引使用、排序、臨時表等特殊行為）。 | 包含大量關鍵信息，如 Using filesort（文件排序）、Using temporary（臨時表）等，是優化的核心線索（詳見前文）。 |
type字段優先級與解讀（從優到差）
type值 | 含義 | 性能影響 |
|---|---|---|
system | 表中只有 1 行數據（如系統表），無需掃描 | 理想狀態，僅特殊場景出現。 |
const | 通過主鍵/唯一索引匹配 1 行數據（如WHERE id=1） | 高效，索引精確匹配，推薦。 |
eq_ref | 多表 JOIN 時，被驅動表通過主鍵/唯一索引匹配，每行只返回 1 條數據 | 高效，適合關聯查詢（如orders.user_id關聯users.user_id主鍵）。 |
ref | 非唯一索引匹配，可能返回多行（如WHERE age=30，age為普通索引） | 較好，索引部分匹配，需關注返回行數。 |
range | 索引范圍掃描（如BETWEEN、IN、>等） | 中等，比全表掃描高效，適合范圍查詢（需確保索引覆蓋條件）。 |
index | 掃描整個索引樹（未命中索引過濾條件，僅用索引排序/覆蓋） | 低效，相當于“索引全表掃描”（如SELECT id FROM users，id為主鍵但無過濾）。 |
ALL | 全表掃描（未使用任何索引） | 極差，大表中會導致查詢超時，必須優化。 |
Extra字段關鍵值解讀
Extra值 | 含義 | 優化方向 |
|---|---|---|
Using where | 使用WHERE條件過濾，但未使用索引（全表掃描后過濾） | 為過濾字段創建索引。 |
Using index | 索引覆蓋掃描（查詢字段均在索引中，無需回表查數據） | 理想狀態，說明索引設計合理（如SELECT user_id FROM orders使用user_id索引）。 |
Using where; Using index | 既用索引過濾，又用索引覆蓋 | 最優狀態，索引同時滿足過濾和查詢需求。 |
Using filesort | 無法通過索引排序，需在內存/磁盤中排序（大結果集極慢） | 優化排序字段，創建“過濾+排序”復合索引（如WHERE status=1 ORDER BY time需(status, time)索引）。 |
Using temporary | 需要創建臨時表存儲中間結果（如GROUP BY非索引字段） | 避免在大表上使用GROUP BY非索引字段，或創建包含分組字段的復合索引。 |
Using join buffer | 多表 JOIN 未使用索引，通過連接緩沖區匹配 | 為 JOIN 條件字段創建索引（如ON u.user_id = o.user_id，需o.user_id索引）。 |
案例：從EXPLAIN結果到優化
需求：查詢年齡 30-40 歲的用戶用戶名和郵箱。
原始查詢：
EXPLAIN SELECT username, email FROM users WHERE age BETWEEN 30 AND 40;
執行計劃結果（問題版）：
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | users | NULL | ALL | idx_age | NULL | NULL | NULL | 99776 | 47.03 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
問題分析：
type=ALL：全表掃描，未使用索引；possible_keys=idx_age但key=NULL：索引存在但未被使用（可能因統計信息過時或索引選擇性差）。
優化步驟：
- 確認索引是否有效：
SHOW INDEX FROM users WHERE Key_name='idx_age';（若不存在則創建）； - 更新表統計信息：
ANALYZE TABLE users;（讓優化器獲取準確數據分布）； - 優化后預期結果：
type=range，key=idx_age，Extra=Using where; Using index（若username和email不在索引中，至少實現range掃描）。
2. 深入優化工具：optimizer_trace——揭秘優化器決策過程
EXPLAIN只能展示執行計劃的“結果”，而optimizer_trace可以展示優化器生成計劃的“過程”（如索引選擇的權衡、成本計算、JOIN 順序決策等），適合分析復雜查詢的深層性能問題。
核心作用與適用場景
- 分析“明明有索引卻不用”的原因（優化器認為全表掃描成本更低？）；
- 對比不同索引的成本差異，指導索引設計；
- 解讀 JOIN 語句中表連接順序的決策邏輯（為什么 A 表驅動 B 表而不是相反？）。
使用步驟與注意事項
-- 默認是關閉的
show global variables like 'optimizer_trace';
+-----------------+--------------------------+
| Variable_name | Value |
+-----------------+--------------------------+
| optimizer_trace | enabled=off,one_line=off |
+-----------------+--------------------------+-- 開啟跟蹤（修改當前會話）
SET optimizer_trace = "enabled=on";-- 僅影響當前會話（看global全局還是off的）
show session variables like 'optimizer_trace';
+-----------------+-------------------------+
| Variable_name | Value |
+-----------------+-------------------------+
| optimizer_trace | enabled=on,one_line=off |
+-----------------+-------------------------+-- 執行目標查詢
SELECT * FROM orders WHERE user_id = 100 AND order_time > '2024-01-01';-- 查看跟蹤結果
SELECT * FROM information_schema.optimizer_trace\G
*************************** 1. row ***************************QUERY: SELECT * FROM orders WHERE user_id = 100 AND order_time > '2024-01-01'TRACE: {"steps": [{"join_preparation": {"select#": 1,"steps": [{"expanded_query": "/* select#1 */ select `orders`.`order_id` AS `order_id`,`orders`.`user_id` AS `user_id`,`orders`.`product_id` AS `product_id`,`orders`.`order_time` AS `order_time`,`orders`.`amount` AS `amount`,`orders`.`status` AS `status` from `orders` where ((`orders`.`user_id` = 100) and (`orders`.`order_time` > '2024-01-01'))"}]}},{"join_optimization": {"select#": 1,"steps": [{"condition_processing": {"condition": "WHERE","original_condition": "((`orders`.`user_id` = 100) and (`orders`.`order_time` > '2024-01-01'))","steps": [{"transformation": "equality_propagation","resulting_condition": "((`orders`.`order_time` > '2024-01-01') and multiple equal(100, `orders`.`user_id`))"},{"transformation": "constant_propagation","resulting_condition": "((`orders`.`order_time` > '2024-01-01') and multiple equal(100, `orders`.`user_id`))"},{"transformation": "trivial_condition_removal","resulting_condition": "((`orders`.`order_time` > TIMESTAMP'2024-01-01 00:00:00') and multiple equal(100, `orders`.`user_id`))"}]}},{"substitute_generated_columns": {}},{"table_dependencies": [{"table": "`orders`","row_may_be_null": false,"map_bit": 0,"depends_on_map_bits": []}]},{"ref_optimizer_key_uses": [{"table": "`orders`","field": "user_id","equals": "100","null_rejecting": true}]},{"rows_estimation": [{"table": "`orders`","range_analysis": {"table_scan": {"rows": 925560,"cost": 93207.1},"potential_range_indexes": [{"index": "PRIMARY","usable": false,"cause": "not_applicable"},{"index": "idx_user_time","usable": true,"key_parts": ["user_id","order_time","order_id"]},{"index": "idx_product_id","usable": false,"cause": "not_applicable"}],"setup_range_conditions": [],"group_index_range": {"chosen": false,"cause": "not_group_by_or_distinct"},"skip_scan_range": {"potential_skip_scan_indexes": [{"index": "idx_user_time","usable": false,"cause": "query_references_nonkey_column"}]},"analyzing_range_alternatives": {"range_scan_alternatives": [{"index": "idx_user_time","ranges": ["user_id = 100 AND '2024-01-01 00:00:00' < order_time"],"index_dives_for_eq_ranges": true,"rowid_ordered": false,"using_mrr": false,"index_only": false,"in_memory": 1,"rows": 10,"cost": 3.76,"chosen": true}],"analyzing_roworder_intersect": {"usable": false,"cause": "too_few_roworder_scans"}},"chosen_range_access_summary": {"range_access_plan": {"type": "range_scan","index": "idx_user_time","rows": 10,"ranges": ["user_id = 100 AND '2024-01-01 00:00:00' < order_time"]},"rows_for_plan": 10,"cost_for_plan": 3.76,"chosen": true}}}]},{"considered_execution_plans": [{"plan_prefix": [],"table": "`orders`","best_access_path": {"considered_access_paths": [{"access_type": "ref","index": "idx_user_time","chosen": false,"cause": "range_uses_more_keyparts"},{"rows_to_scan": 10,"access_type": "range","range_details": {"used_index": "idx_user_time"},"resulting_rows": 10,"cost": 4.76,"chosen": true}]},"condition_filtering_pct": 100,"rows_for_plan": 10,"cost_for_plan": 4.76,"chosen": true}]},{"attaching_conditions_to_tables": {"original_condition": "((`orders`.`user_id` = 100) and (`orders`.`order_time` > TIMESTAMP'2024-01-01 00:00:00'))","attached_conditions_computation": [],"attached_conditions_summary": [{"table": "`orders`","attached": "((`orders`.`user_id` = 100) and (`orders`.`order_time` > TIMESTAMP'2024-01-01 00:00:00'))"}]}},{"finalizing_table_conditions": [{"table": "`orders`","original_table_condition": "((`orders`.`user_id` = 100) and (`orders`.`order_time` > TIMESTAMP'2024-01-01 00:00:00'))","final_table_condition ": "((`orders`.`user_id` = 100) and (`orders`.`order_time` > TIMESTAMP'2024-01-01 00:00:00'))"}]},{"refine_plan": [{"table": "`orders`","pushed_index_condition": "((`orders`.`user_id` = 100) and (`orders`.`order_time` > TIMESTAMP'2024-01-01 00:00:00'))","table_condition_attached": null}]}]}},{"join_execution": {"select#": 1,"steps": []}}]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0INSUFFICIENT_PRIVILEGES: 0-- 關閉跟蹤（避免性能消耗）
SET optimizer_trace = "enabled=off";
注意事項：
- 僅在分析復雜查詢時使用，開啟后會增加 CPU 和內存消耗；
- 結果中
MISSING_BYTES_BEYOND_MAX_MEM_SIZE>0表示內容被截斷，需調大optimizer_trace_max_mem_size（默認 1MB）； - 需
PROCESS權限才能查看information_schema.optimizer_trace。
關鍵結果解讀
從 JSON 結果中重點關注以下部分：
rows_estimation：優化器對各表行數的估算（若與實際偏差大，需更新統計信息）；potential_range_indexes：優化器考慮的所有候選索引（包括未被選中的）；analyzing_range_alternatives：各索引的成本對比（cost字段，值越小越優）；chosen_range_access_summary：最終選擇的索引及原因（如cost=3.76的索引被選中）。
示例解讀：
若potential_range_indexes中包含idx_user_time，但chosen_range_access_summary未選中，需查看cost是否高于全表掃描（可能因索引選擇性差，優化器認為全表掃描更快），此時需優化索引（如增加區分度更高的前綴字段）。
3. 精準量化工具：EXPLAIN ANALYZE（MySQL 8.0+）——實測執行性能
EXPLAIN ANALYZE是 MySQL 8.0 引入的增強功能，會實際執行查詢，并返回精確的執行時間、掃描行數等 metrics，適合量化性能瓶頸（如排序耗時、掃描行數與預期的偏差）。
核心優勢與使用場景
- 對比
EXPLAIN：EXPLAIN返回“預估”值，EXPLAIN ANALYZE返回“實際”值（如actual time、actual rows）； - 適合分析分頁查詢（
LIMIT offset, size）、排序、JOIN 等操作的真實耗時； - 量化索引優化效果（如優化前后的執行時間對比）。
警告：
- 對大表執行
EXPLAIN ANALYZE會消耗實際資源（如全表掃描 1000 萬行），生產環境謹慎使用； - 非
SELECT語句（如UPDATE、DELETE）禁用，避免誤操作數據。 - 會實際執行語句，因此非 DQL 語句謹慎執行！！！
案例：分析分頁查詢性能
需求：分析“查詢狀態為 2 的訂單，按時間排序后取第 10001-10020 條”的性能瓶頸。
查詢：
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE status = 2
ORDER BY order_time
LIMIT 10000, 20\G
未優化結果：
EXPLAIN: -> Limit/Offset: 20/10000 row(s) (cost=93205 rows=20) (actual time=744..744 rows=20 loops=1)-> Sort: orders.order_time, limit input to 10020 row(s) per chunk (cost=93205 rows=925560) (actual time=742..743 rows=10020 loops=1)-> Filter: (orders.`status` = 2) (cost=93205 rows=925560) (actual time=3.26..591 rows=333807 loops=1)-> Table scan on orders (cost=93205 rows=925560) (actual time=3.25..505 rows=1e+6 loops=1)
關鍵信息解讀：
總執行時間：744ms（根節點的actual time）
各階段實際耗時：
- 表掃描（
Table scan on orders）：(505ms - 3.25ms)*1 ≈ 502ms - 過濾（
Filter）：(591ms - 3.26ms)*1 ≈ 588ms（包含表掃描時間） - 排序（
Sort）：(743ms - 3.26ms)*1 ≈ 740ms（包含表掃描和過濾時間）
| 字段 | 含義 |
|---|---|
cost | 優化器預估的執行成本（數值越小越好，基于 CPU 消耗、IO 操作等計算） |
rows（預估） | 優化器預估的需要處理的行數（反映查詢的“工作量”，數值越小效率越高） |
actual time | 實際執行時間（格式為 開始時間..結束時間，單位為毫秒） |
actual rows | 實際處理的行數（反映真實數據量，與預估 rows 差異過大需關注） |
loops | 操作執行的循環次數（非join或帶子查詢的sql通常為 1） |
優化方案：創建覆蓋“過濾+排序”的復合索引：
CREATE INDEX idx_status_time ON orders(status, order_time);
優化后結果：
EXPLAIN: -> Limit/Offset: 20/10000 row(s) (cost=48225 rows=20) (actual time=48.4..48.4 rows=20 loops=1)-> Index lookup on orders using idx_status_time (status=2) (cost=48225 rows=462780) (actual time=25.3..47.9 rows=10020 loops=1)
優化效果：
- 總時間從 744ms 降至 48ms（提升 93%）；
- 消除全表掃描和文件排序，直接通過索引定位數據（
Index lookup）。
五、案例 - 索引問題與表結構
1. 索引失效 - 函數操作索引列
案例 SQL：
EXPLAIN SELECT COUNT(1) FROM users WHERE YEAR(register_time) = 2025;
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+-------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+-------+----------+--------------------------+
| 1 | SIMPLE | users | NULL | index | NULL | idx_register_time | 6 | NULL | 99776 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+-------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)EXPLAIN SELECT COUNT(1) FROM users WHERE register_time >= '2025-01-01' AND register_time < DATE_ADD('2025-01-01', INTERVAL 1 YEAR);
+----+-------------+-------+------------+-------+-------------------+-------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+-------------------+-------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | users | NULL | range | idx_register_time | idx_register_time | 6 | NULL | 5300 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+-------------------+-------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
執行計劃問題：對索引列register_time使用了函數，導致索引失效。
優化手段：改為范圍查詢，不要對索引列使用函數。
2. 索引失效 - 隱式類型轉換
案例 SQL：
EXPLAIN select count(1) from products where product_name=12345;
+----+-------------+----------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | products | NULL | index | idx_name_price | idx_name_price | 408 | NULL | 9642 | 10.00 | Using where; Using index |
+----+-------------+----------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
1 row in set, 3 warnings (0.00 sec)EXPLAIN select count(1) from products where product_name='12345';
+----+-------------+----------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | products | NULL | ref | idx_name_price | idx_name_price | 402 | const | 1 | 100.00 | Using index |
+----+-------------+----------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
執行計劃問題：product_name是 varchar 類型，但查詢條件使用了數字類型，發生隱式類型轉換導致索引失效。參考：select count(1) from products where CAST(product_name AS SIGNED)=12345;
優化手段：查詢條件類型與索引列字段一致。
可能有小伙伴會問，明明查出來是 0 行，怎么執行計劃 rows=1 呢，實際在掃索引時 mysql 也已經知道了沒有匹配的結果，理論返回 0 行就行，但 mysql 代碼里寫死了這種情況返回 1，他們也沒有解釋那就這樣吧。
3. 索引設計不合理 - 單值索引 vs 復合索引選擇錯誤
案例 SQL：
-- 刪掉原本的 idx_category 方便驗證該案例
alter table products drop index idx_category,add index idx_price_category(price,category_id);EXPLAIN SELECT * FROM products WHERE category_id = 5 AND price > 900;
+----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | products | NULL | range | idx_price_category | idx_price_category | 6 | NULL | 1003 | 10.00 | Using index condition |
+----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)-- 優化索引順序
alter table products drop index idx_price_category,add index idx_category_price(category_id,price);EXPLAIN SELECT * FROM products WHERE category_id = 5 AND price > 900;
+----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | products | NULL | range | idx_category_price | idx_category_price | 11 | NULL | 45 | 100.00 | Using index condition |
+----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)-- 改回去
alter table products drop index idx_category_price,add index idx_category(category_id);
執行計劃問題：雖然有復合索引idx_price_category(price, category_id)，但范圍查詢price > 900導致索引截斷。
優化手段：調整索引順序為(category_id, price)
六、案例 - 執行計劃異常與查詢寫法缺陷
1. 優化器誤判（統計信息過時，以 products 表為例）
案例 SQL：
-- 查詢特定分類的商品，products表有idx_category索引
EXPLAIN SELECT * FROM products WHERE category_id = 15;ANALYZE TABLE products; -- 更新統計信息后，執行計劃會選擇idx_category索引
執行計劃問題：優化器誤判（因統計信息過時），認為全表掃描比走idx_category快（實際category_id=15的數據量很小）。
統計信息過時的原因:
- 數據量劇變：批量插入 / 刪除 / 更新超過表總量的 10%。
- 分布劇變：字段值的重復度、基數發生顯著變化（如從低重復到高重復）。
- 配置限制：關閉自動更新、采樣精度不足。
- 結構變更：新增索引、修改字段類型后未同步更新統計信息。
統計信息過時會直接導致優化器誤判執行計劃（如全表掃描 vs 索引掃描、JOIN 順序錯誤），因此需在上述場景中定期執行 ANALYZE TABLE 或開啟自動更新（非極致寫入場景）。
優化手段：更新表統計信息，幫助優化器正確判斷。
2. SELECT * 全字段查詢
案例 SQL：
-- 查詢用戶的訂單記錄，使用SELECT * 導致讀取不必要字段
SELECT * FROM users u
JOIN orders o ON u.user_id = o.user_id
WHERE u.user_id = 100;SELECT u.username, o.order_id, o.order_time, o.amount
FROM users u
JOIN orders o ON u.user_id = o.user_id
WHERE u.user_id = 100;
問題：users表的email、register_time和orders表的status等非必要字段被讀取，增加 IO 和內存開銷（尤其orders表數據量達 100 萬條）。多余字段占用大量 IO 帶寬，拖慢查詢。
優化手段：只查詢業務需要的字段
3. 復雜子查詢嵌套
案例 SQL：
-- 統計2024 年每個用戶的訂單總金額（包含所有用戶，即使無訂單也顯示 0）
EXPLAIN ANALYZE SELECTu.user_id,u.username,-- 子查詢：統計該用戶2024年的訂單總金額（無訂單則返回NULL，用IFNULL轉為0）IFNULL((SELECT SUM(o.amount)FROM orders oWHERE o.user_id = u.user_idAND o.order_time >= '2024-01-01'AND o.order_time < '2025-01-01'), 0) AS total_2024_amount
FROM users u \G;
*************************** 1. row ***************************
EXPLAIN: -> Table scan on u (cost=10098 rows=99776) (actual time=0.147..44.7 rows=100000 loops=1)
-> Select #2 (subquery in projection; dependent)-> Aggregate: sum(o.amount) (cost=3.26 rows=1) (actual time=0.0155..0.0155 rows=1 loops=100000)-> Index lookup on o using idx_user_time (user_id=u.user_id), with index condition: ((o.order_time >= TIMESTAMP'2024-01-01 00:00:00') and (o.order_time < TIMESTAMP'2025-01-01 00:00:00')) (cost=2.36 rows=9.02) (actual time=0.0133..0.0143 rows=4.74 loops=100000)1 row in set, 1 warning (1.87 sec)-- 覆蓋索引：包含所有查詢需要的字段
alter table orders drop index idx_user_time,add index idx_user_time_amount(user_id, order_time, amount);EXPLAIN ANALYZE SELECTu.user_id,u.username,COALESCE(s.total_amount, 0) AS total_2024_amount
FROM users u
LEFT JOIN (SELECTuser_id,SUM(amount) AS total_amountFROM ordersWHERE order_time >= '2024-01-01'AND order_time < '2025-01-01'GROUP BY user_id -- 預聚合訂單數據
) s ON u.user_id = s.user_id \G;
*************************** 1. row ***************************
EXPLAIN: -> Nested loop left join (cost=1.01e+9 rows=10e+9) (actual time=706..892 rows=100000 loops=1)-> Table scan on u (cost=10098 rows=99776) (actual time=0.175..39.2 rows=100000 loops=1)-> Index lookup on s using <auto_key0> (user_id=u.user_id) (cost=113560..113562 rows=10) (actual time=0.00809..0.00831 rows=0.988 loops=100000)-> Materialize (cost=113559..113559 rows=100725) (actual time=706..706 rows=98795 loops=1)-> Group aggregate: sum(orders.amount) (cost=103487 rows=100725) (actual time=1.24..571 rows=98795 loops=1)-> Filter: ((orders.order_time >= TIMESTAMP'2024-01-01 00:00:00') and (orders.order_time < TIMESTAMP'2025-01-01 00:00:00')) (cost=93205 rows=102819) (actual time=1.23..487 rows=473886 loops=1)-> Covering index scan on orders using idx_user_time_amount (cost=93205 rows=925560) (actual time=1.23..340 rows=1e+6 loops=1)1 row in set (0.92 sec)
執行計劃問題：10 萬次子查詢重復執行 sum()，累積開銷大。
優化手段：
- 用覆蓋索引避免回表 IO：子查詢和主查詢均通過 idx_user_time_amount 獲取所需字段（user_id、order_time、amount），無需回表。
- 減少中間結果集大小（如預聚合）：將 SUM(amount)的計算放在子查詢中，避免主查詢處理大量中間結果。
七、案例 - 分頁查詢
傳統分頁問題（LIMIT 大偏移量）
案例 SQL：
-- 查詢第100001-100020條訂單（偏移量10萬）
EXPLAIN ANALYZE
SELECT * FROM orders
ORDER BY order_id -- 按主鍵排序（默認也可能按此排序）
LIMIT 100000, 20 \G;
*************************** 1. row ***************************
EXPLAIN: -> Limit/Offset: 20/100000 row(s) (cost=1074 rows=20) (actual time=50.3..50.3 rows=20 loops=1)-> Index scan on orders using PRIMARY (cost=1074 rows=100020) (actual time=2.84..46.1 rows=100020 loops=1)1 row in set (0.05 sec)
核心問題：
LIMIT 100000, 20需要掃描前 100020 行數據，然后丟棄前 100000 行，僅返回 20 行，99.98%的掃描是無效的。- 即使有排序，大偏移量仍會導致全表掃描+排序，IO 和 CPU 開銷極高。
分頁優化核心思路
| 優化方案 | 核心思路 | 適用場景 | 性能提升幅度 |
|---|---|---|---|
| 主鍵偏移量分頁 | 用WHERE id > 偏移值替代LIMIT 偏移量 | 按連續主鍵排序，有上一頁 ID | 50-100 倍 |
| 條件+邊界值分頁 | 用WHERE 條件 AND 字段 > 上一頁值 | 按非主鍵排序，有過濾條件 | 30-50 倍 |
| 延遲關聯+小范圍 LIMIT | 先查 ID 再回表，減少大偏移量數據量 | 必須用大偏移量，無邊界值 | 2-3 倍 |
優化方案 1：主鍵偏移量分頁（利用連續主鍵）
通過已知的最后一條記錄的主鍵（如第 100000 行的order_id）作為條件，直接定位到起始位置，避免掃描偏移量內的所有行。
優化后 SQL：
-- 假設第100000行的order_id為100000（可從上次查詢獲取）
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE order_id > 100000 -- 直接定位到偏移量位置
ORDER BY order_id
LIMIT 20 \G; -- 僅取20行
*************************** 1. row ***************************
EXPLAIN: -> Limit: 20 row(s) (cost=99909 rows=20) (actual time=2.08..2.09 rows=20 loops=1)-> Filter: (orders.order_id > 100000) (cost=99909 rows=498729) (actual time=2.08..2.08 rows=20 loops=1)-> Index range scan on orders using PRIMARY over (100000 < order_id) (cost=99909 rows=498729) (actual time=2.07..2.08 rows=20 loops=1)1 row in set (0.01 sec)
寫法優勢：
- 無需掃描偏移量內數據：通過
WHERE order_id > 100000直接定位到起始點，掃描行數從 100020 降至 20 行。 - 利用現有主鍵索引：
PRIMARY KEY (order_id)天然存在，無需額外索引，通過范圍查詢（range）快速定位。
適用場景：
- 分頁按連續自增主鍵排序（如
order_id）。 - 前端分頁可記錄上一頁最后一條記錄的
order_id（如“下一頁”按鈕傳遞該值）。
優化方案 2：基于條件過濾的分段分頁（非主鍵排序）
當分頁需要按非主鍵字段排序（如order_time），且有固定過濾條件（如status=2，已支付訂單），傳統LIMIT大偏移量同樣低效。
傳統寫法問題：需掃描前 1020 條符合status=2的記錄，丟棄前 10000 條，無效掃描多。
-- 按訂單時間排序，查詢第1001-1020條已支付訂單（偏移量1000）
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE status = 2
ORDER BY order_time
LIMIT 10000, 20 \G;
*************************** 1. row ***************************
EXPLAIN: -> Limit/Offset: 20/10000 row(s) (cost=52012 rows=20) (actual time=23.9..23.9 rows=20 loops=1)-> Index lookup on orders using idx_status_time (status=2) (cost=52012 rows=498729) (actual time=11.6..23.6 rows=10020 loops=1)1 row in set (0.02 sec)
優化寫法（利用上一頁邊界值）：
-- 假設上一頁最后一條記錄的order_time為'2025-05-01 10:00:00'，order_id為50000
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE status = 2AND order_time >= '2025-05-01 10:00:00' -- 用上一頁時間作為起點AND NOT (order_time = '2025-05-01 10:00:00' AND order_id <= 50000) -- 排除同時間的前序記錄
ORDER BY order_time, order_id -- 時間+ID聯合排序，避免重復/遺漏
LIMIT 20 \G;
*************************** 1. row ***************************
EXPLAIN: -> Limit: 20 row(s) (cost=58539 rows=20) (actual time=12.3..12.3 rows=20 loops=1)-> Index range scan on orders using idx_status_time over (status = 2 AND '2025-05-01 10:00:00' <= order_time), with index condition: ((orders.`status` = 2) and (orders.order_time >= TIMESTAMP'2025-05-01 10:00:00') and ((orders.order_time <> TIMESTAMP'2025-05-01 10:00:00') or (orders.order_id > 50000))) (cost=58539 rows=130086) (actual time=12.3..12.3 rows=20 loops=1)1 row in set (0.02 sec)
寫法優勢：
- 通過條件過濾替代偏移量：利用上一頁最后一條記錄的
order_time和order_id作為邊界，直接定位到下一頁起始位置，避免掃描前 10000 條記錄。 - 聯合排序去重：
ORDER BY order_time, order_id確保排序唯一，避免同時間訂單重復或遺漏。 - 復用現有索引：
idx_user_time (user_id, order_time)雖以user_id開頭，但order_time作為第二列可輔助范圍查詢（配合status=2過濾）。
適用場景：
- 按非主鍵字段排序（如
order_time）。 - 有固定過濾條件（如
status=2），可通過條件+邊界值快速定位。 - 前端需記錄上一頁最后一條記錄的
order_time和order_id（如傳遞給“下一頁”接口）。
優化方案 3：延遲關聯+小范圍 LIMIT（無邊界值時）
當無法獲取上一頁邊界值（如“跳轉至第 500 頁”），且必須使用大偏移量，可通過“先查 ID，再回表”減少無效數據傳輸。
優化寫法：
-- 子查詢先獲取目標頁的order_id，再關聯回表
EXPLAIN ANALYZE
SELECT o.*
FROM orders o
JOIN (-- 子查詢僅查ID，數據量小，排序/偏移高效SELECT order_idFROM ordersWHERE status = 2ORDER BY order_timeLIMIT 10000, 20 -- 大偏移量僅處理ID，而非全字段
) tmp ON o.order_id = tmp.order_id \G;
*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join (cost=52567 rows=20) (actual time=7.07..7.15 rows=20 loops=1)-> Table scan on tmp (cost=50058..50060 rows=20) (actual time=7.04..7.04 rows=20 loops=1)-> Materialize (cost=50058..50058 rows=20) (actual time=7.04..7.04 rows=20 loops=1)-> Limit/Offset: 20/10000 row(s) (cost=50056 rows=20) (actual time=7.01..7.01 rows=20 loops=1)-> Covering index lookup on orders using idx_status_time (status=2) (cost=50056 rows=498729) (actual time=2.36..6.37 rows=10020 loops=1)-> Single-row index lookup on o using PRIMARY (order_id=tmp.order_id) (cost=0.25 rows=1) (actual time=0.00447..0.00453 rows=1 loops=20)1 row in set (0.01 sec)
寫法優勢：
- 減少排序/偏移的數據量：子查詢僅處理
order_id（4 字節），比全字段（*包含多個字段，約 50 字節）更輕量，排序和偏移效率更高。 - 回表數據量小：僅對 20 條
order_id回表查詢全字段，避免 10000 條無效記錄的全字段傳輸。
適用場景：
- 必須使用大偏移量（如“跳轉至第 N 頁”）。
- 表字段較多（
*包含大量數據），通過先查 ID 減少中間數據傳輸。
