MYSQL中慢SQL原因與優(yōu)化方法詳解
更新時間：2025年07月17日 10:46:18 作者：FixPng
這篇文章主要為大家詳細介紹了MYSQL中慢SQL形成的原因以及相關(guān)優(yōu)化方法,文中的示例代碼講解詳細,感興趣的小伙伴可以跟隨小編一起學習一下
一、數(shù)據(jù)庫故障的關(guān)鍵點
引起數(shù)據(jù)庫故障的因素有操作系統(tǒng)層面、存儲層面，還有斷電斷網(wǎng)的基礎(chǔ)環(huán)境層面（以下稱為外部因素），以及應用程序操作數(shù)據(jù)庫和人為操作數(shù)據(jù)庫這兩個層面（以下稱內(nèi)部因素）。這些故障中外部因素發(fā)生的概率較小，可能幾年都未發(fā)生過一起。許多 DBA 從入職到離職都可能沒有遇到過外部因素導致的故障，但是內(nèi)部因素導致的故障可能每天都在產(chǎn)生。內(nèi)部因素中占據(jù)主導的是應用程序?qū)е碌墓收希梢哉f數(shù)據(jù)庫故障的主要元兇就是應用程序的 SQL 寫的不夠好。
SQL 是由開發(fā)人員編寫的，但是責任不完全是開發(fā)人員的。
- SQL 的成因：SQL 是為了實現(xiàn)特定的需求而編寫的，那么需求的合理性是第一位的。一般來說，在合理的需求下即使有問題的 SQL 也是可以挽救的。但是如果需求不合理，那么就為 SQL 問題埋下了隱患。
- SQL 的設(shè)計：這里的設(shè)計主要是數(shù)據(jù)庫對象的設(shè)計。即使是合理的需求，椰果在數(shù)據(jù)庫設(shè)計層面沒有把控的緩解或者保證，那么很多優(yōu)化就會大打折扣。
- SQL 的實現(xiàn)：這部分是開發(fā)人員所涉及的工作。但是這已經(jīng)是流程的末端，這個時候改善的手段依然有，但是屬于挽救措施。
在筆者多年的工作中，數(shù)據(jù)庫故障主要來源于三個方面：不合理的需求、不合理的設(shè)計和不合理的實現(xiàn)。而這些如果從管理和流程上明確規(guī)定由經(jīng)驗豐富的 DBA 介入，那么對數(shù)據(jù)庫故障的源頭是有很大的控制作用的。
上述摘自薛曉剛老師的 《DBA 實戰(zhàn)手記》 3.2 節(jié)。
二、慢 SQL 的常見成因
在分析 SQL 語句時，SQL 運行緩慢絕對是最主要的問題，沒有之一。慢 SQL 是數(shù)據(jù)庫性能瓶頸的主要表現(xiàn)，其核心成因可歸納為以下幾類：
- 索引問題：缺失索引導致全表掃描、索引失效（如函數(shù)操作索引列、隱式類型轉(zhuǎn)換）、索引設(shè)計不合理（單值索引 vs 復合索引選擇錯誤）。
- 查詢寫法缺陷：SELECT * 全字段查詢、復雜子查詢嵌套、無過濾條件的大范圍掃描、低效 JOIN 操作。
- 數(shù)據(jù)量與結(jié)構(gòu)：表數(shù)據(jù)量過大未分區(qū)、字段類型設(shè)計不合理（如用 VARCHAR 存儲數(shù)字）、大字段（TEXT/BLOB）頻繁查詢。
- 執(zhí)行計劃異常：優(yōu)化器誤判（統(tǒng)計信息過時）、JOIN 順序錯誤、臨時表與文件排序濫用。
下面通過實例表和純 SQL 生成的數(shù)據(jù)，結(jié)合執(zhí)行計劃工具詳解優(yōu)化方法。
三、實驗表結(jié)構(gòu)及數(shù)據(jù)
1. 表結(jié)構(gòu)（電商場景）
-- 用戶表 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. 生成測試數(shù)據(jù)（存儲過程）
生成用戶數(shù)據(jù)（10 萬條）
DELIMITER //
CREATE PROCEDURE prod_generate_users()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE batch_size INT DEFAULT 1000; -- 每批處理1000條記錄
DECLARE total INT DEFAULT 100000; -- 總記錄數(shù)
WHILE i <= total DO
START TRANSACTION;
-- 插入當前批次的記錄
WHILE i <= total AND i <= (batch_size * FLOOR((i-1)/batch_size) + batch_size) DO
INSERT 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 ;
-- 執(zhí)行存儲過程
CALL prod_generate_users();
生成商品數(shù)據(jù) 1 萬條）
DELIMITER //
CREATE PROCEDURE prod_generate_products()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE batch_size INT DEFAULT 1000; -- 每批處理1000條記錄
DECLARE total INT DEFAULT 10000; -- 總記錄數(shù)
WHILE i <= total DO
START TRANSACTION;
-- 插入當前批次的記錄
WHILE i <= total AND i <= (batch_size * FLOOR((i-1)/batch_size) + batch_size) DO
INSERT 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();
生成訂單數(shù)據(jù)（100 萬條）
DELIMITER //
CREATE PROCEDURE prod_generate_orders()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE batch_size INT DEFAULT 500; -- 每批處理500條記錄（訂單數(shù)據(jù)量大，批次更?。?
DECLARE total INT DEFAULT 1000000; -- 總記錄數(shù)
DECLARE max_user INT;
DECLARE max_product INT;
DECLARE rand_product_id INT;
DECLARE product_price DECIMAL(10,2);
-- 獲取用戶和商品的最大ID
SELECT MAX(user_id) INTO max_user FROM users;
SELECT MAX(product_id) INTO max_product FROM products;
WHILE i <= total DO
START TRANSACTION;
-- 插入當前批次的記錄
WHILE i <= total AND i <= (batch_size * FLOOR((i-1)/batch_size) + batch_size) DO
-- 優(yōu)化：預先計算隨機商品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件數(shù)量
FLOOR(RAND() * 3) + 1 -- 隨機狀態(tài)
);
SET i = i + 1;
END WHILE;
COMMIT;
END WHILE;
END //
DELIMITER ;
CALL prod_generate_orders();
查看數(shù)據(jù)
select count(1) from users union all select count(1) from products union all select count(1) from orders; +----------+ | count(1) | +----------+ | 100000 | | 10000 | | 1000000 | +----------+
四、執(zhí)行計劃詳解：從分析到優(yōu)化完整指南
三類工具的選擇指南
| 工具 | 核心價值 | 適用場景 |
|---|---|---|
| EXPLAIN | 快速預判執(zhí)行計劃（預估） | 日常開發(fā)、索引設(shè)計驗證、排查明顯低效操作 |
| optimizer_trace | 深入優(yōu)化器決策過程（分析“為什么這么做”） | 復雜查詢的索引選擇問題、JOIN 順序優(yōu)化 |
| EXPLAIN ANALYZE | 量化實際執(zhí)行性能（精確耗時、行數(shù)） | 性能瓶頸量化、優(yōu)化效果對比、分頁/排序分析 |
通過這三類工具的組合使用，可從“預判”到“分析”再到“量化”，全面掌握 MySQL 查詢的執(zhí)行邏輯，精準定位并解決性能問題。
1. 基礎(chǔ)分析工具：EXPLAIN——預判查詢執(zhí)行邏輯
EXPLAIN是 MySQL 中最常用的執(zhí)行計劃分析工具，無需實際執(zhí)行查詢，即可返回優(yōu)化器對查詢的執(zhí)行方案（如索引選擇、掃描方式等），幫助提前發(fā)現(xiàn)性能隱患。
核心語法與使用場景
-- 對任意SELECT查詢執(zhí)行分析 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 執(zhí)行計劃中，除了 type、key、rows、Extra 這幾個核心字段外，其他字段也承載著查詢執(zhí)行邏輯的關(guān)鍵信息。以下是 EXPLAIN 所有字段的完整含義：
| 字段名 | 核心含義 | 補充說明 |
|---|---|---|
| id | 查詢中每個操作的唯一標識（多表/子查詢時用于區(qū)分執(zhí)行順序）。 | - 若 id 相同：表示操作在同一層級，按表的順序（從左到右）執(zhí)行（如 JOIN 時的驅(qū)動表和被驅(qū)動表）。 - 若 id 不同：id 越大優(yōu)先級越高，先執(zhí)行（如子查詢會嵌套在主查詢內(nèi)部，id 更大）。 |
| select_type | 查詢的類型（區(qū)分簡單查詢、子查詢、聯(lián)合查詢等）。 | 常見值： - SIMPLE：簡單查詢（無子查詢、JOIN 等復雜結(jié)構(gòu)）。 - PRIMARY：主查詢（包含子查詢時，最外層的查詢）。 - SUBQUERY：子查詢（SELECT 中的子查詢，不依賴外部結(jié)果）。 - DERIVED：衍生表（FROM 中的子查詢，會生成臨時表）。 - UNION：UNION 語句中第二個及以后的查詢。 - UNION RESULT：UNION 結(jié)果集的合并操作。 |
| table | 當前操作涉及的表名（或臨時表別名，如 derived2 表示衍生表）。 | 若為 NULL：可能是 UNION RESULT（合并結(jié)果集時無具體表），或子查詢的中間結(jié)果。 |
| partitions | 查詢匹配的分區(qū)（僅對分區(qū)表有效）。 | 非分區(qū)表顯示 NULL；分區(qū)表會顯示匹配的分區(qū)名稱（如 p2023 表示命中 p2023 分區(qū)）。 |
| type | 訪問類型（索引使用效率等級，最關(guān)鍵的性能指標）。 | 詳見前文“type 字段優(yōu)先級與解讀”，從優(yōu)到差反映索引利用效率（如 const > ref > ALL）。 |
| possible_keys | 優(yōu)化器認為可能使用的索引（候選索引列表）。 | 該字段僅表示“可能有效”的索引，不代表實際使用；若為 NULL，說明沒有可用索引。 |
| key | 實際使用的索引（NULL 表示未使用任何索引）。 | 若 possible_keys 有值但 key 為 NULL，可能是索引選擇性差（如字段值重復率高）或優(yōu)化器判斷全表掃描更快。 |
| key_len | 實際使用的索引長度（字節(jié)數(shù)）。 | 用于判斷復合索引的使用情況： - 若 key_len 等于復合索引總長度，說明整個索引被使用； - 若較短，說明僅使用了復合索引的前綴部分（需檢查是否因類型不匹配導致索引截斷，如字符串未指定長度）。 |
| ref | 表示哪些字段或常量被用來匹配索引列。 | - 若為常量（如 const）：表示用固定值匹配索引（如 WHERE id=1）。 - 若為表名.字段（如 u.user_id）：表示用其他表的字段關(guān)聯(lián)當前表的索引（如 JOIN 時的關(guān)聯(lián)條件）。 |
| rows | 優(yōu)化器預估的掃描行數(shù)（基于表統(tǒng)計信息估算）。 | 數(shù)值越小越好，反映查詢的“工作量”；若遠大于實際數(shù)據(jù)量，可能是統(tǒng)計信息過時，需執(zhí)行 ANALYZE TABLE 表名 更新。 |
| filtered | 經(jīng)過過濾條件后，剩余記錄占預估掃描行數(shù)的比例（百分比）。 | 如 filtered=50 表示掃描的 rows 中，有 50% 滿足過濾條件；值越高，說明過濾效率越好（減少后續(xù)處理的數(shù)據(jù)量）。 |
| Extra | 額外的執(zhí)行細節(jié)（補充說明索引使用、排序、臨時表等特殊行為）。 | 包含大量關(guān)鍵信息，如 Using filesort（文件排序）、Using temporary（臨時表）等，是優(yōu)化的核心線索（詳見前文）。 |
type字段優(yōu)先級與解讀（從優(yōu)到差）
| type值 | 含義 | 性能影響 |
|---|---|---|
| system | 表中只有 1 行數(shù)據(jù)（如系統(tǒng)表），無需掃描 | 理想狀態(tài)，僅特殊場景出現(xiàn)。 |
| const | 通過主鍵/唯一索引匹配 1 行數(shù)據(jù)（如WHERE id=1） | 高效，索引精確匹配，推薦。 |
| eq_ref | 多表 JOIN 時，被驅(qū)動表通過主鍵/唯一索引匹配，每行只返回 1 條數(shù)據(jù) | 高效，適合關(guān)聯(lián)查詢（如orders.user_id關(guān)聯(lián)users.user_id主鍵）。 |
| ref | 非唯一索引匹配，可能返回多行（如WHERE age=30，age為普通索引） | 較好，索引部分匹配，需關(guān)注返回行數(shù)。 |
| range | 索引范圍掃描（如BETWEEN、IN、>等） | 中等，比全表掃描高效，適合范圍查詢（需確保索引覆蓋條件）。 |
| index | 掃描整個索引樹（未命中索引過濾條件，僅用索引排序/覆蓋） | 低效，相當于“索引全表掃描”（如SELECT id FROM users，id為主鍵但無過濾）。 |
| ALL | 全表掃描（未使用任何索引） | 極差，大表中會導致查詢超時，必須優(yōu)化。 |
Extra字段關(guān)鍵值解讀
| Extra值 | 含義 | 優(yōu)化方向 |
|---|---|---|
| Using where | 使用WHERE條件過濾，但未使用索引（全表掃描后過濾） | 為過濾字段創(chuàng)建索引。 |
| Using index | 索引覆蓋掃描（查詢字段均在索引中，無需回表查數(shù)據(jù)） | 理想狀態(tài)，說明索引設(shè)計合理（如SELECT user_id FROM orders使用user_id索引）。 |
| Using where; Using index | 既用索引過濾，又用索引覆蓋 | 最優(yōu)狀態(tài)，索引同時滿足過濾和查詢需求。 |
| Using filesort | 無法通過索引排序，需在內(nèi)存/磁盤中排序（大結(jié)果集極慢） | 優(yōu)化排序字段，創(chuàng)建“過濾+排序”復合索引（如WHERE status=1 ORDER BY time需(status, time)索引）。 |
| Using temporary | 需要創(chuàng)建臨時表存儲中間結(jié)果（如GROUP BY非索引字段） | 避免在大表上使用GROUP BY非索引字段，或創(chuàng)建包含分組字段的復合索引。 |
| Using join buffer | 多表 JOIN 未使用索引，通過連接緩沖區(qū)匹配 | 為 JOIN 條件字段創(chuàng)建索引（如ON u.user_id = o.user_id，需o.user_id索引）。 |
案例：從EXPLAIN結(jié)果到優(yōu)化
需求：查詢年齡 30-40 歲的用戶用戶名和郵箱。
原始查詢：
EXPLAIN SELECT username, email FROM users WHERE age BETWEEN 30 AND 40;
執(zhí)行計劃結(jié)果（問題版）：
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 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：索引存在但未被使用（可能因統(tǒng)計信息過時或索引選擇性差）。
優(yōu)化步驟：
- 確認索引是否有效：
SHOW INDEX FROM users WHERE Key_name='idx_age';（若不存在則創(chuàng)建）； - 更新表統(tǒng)計信息：
ANALYZE TABLE users;（讓優(yōu)化器獲取準確數(shù)據(jù)分布）； - 優(yōu)化后預期結(jié)果：
type=range，key=idx_age，Extra=Using where; Using index（若username和email不在索引中，至少實現(xiàn)range掃描）。
2. 深入優(yōu)化工具：optimizer_trace——揭秘優(yōu)化器決策過程
EXPLAIN只能展示執(zhí)行計劃的“結(jié)果”，而optimizer_trace可以展示優(yōu)化器生成計劃的“過程”（如索引選擇的權(quán)衡、成本計算、JOIN 順序決策等），適合分析復雜查詢的深層性能問題。
核心作用與適用場景
- 分析“明明有索引卻不用”的原因（優(yōu)化器認為全表掃描成本更低？）；
- 對比不同索引的成本差異，指導索引設(shè)計；
- 解讀 JOIN 語句中表連接順序的決策邏輯（為什么 A 表驅(qū)動 B 表而不是相反？）。
使用步驟與注意事項
-- 默認是關(guān)閉的
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 |
+-----------------+-------------------------+
-- 執(zhí)行目標查詢
SELECT * FROM orders WHERE user_id = 100 AND order_time > '2024-01-01';
-- 查看跟蹤結(jié)果
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: 0
INSUFFICIENT_PRIVILEGES: 0
-- 關(guān)閉跟蹤（避免性能消耗）
SET optimizer_trace = "enabled=off";
注意事項：
- 僅在分析復雜查詢時使用，開啟后會增加 CPU 和內(nèi)存消耗；
- 結(jié)果中
MISSING_BYTES_BEYOND_MAX_MEM_SIZE>0表示內(nèi)容被截斷，需調(diào)大optimizer_trace_max_mem_size（默認 1MB）； - 需
PROCESS權(quán)限才能查看information_schema.optimizer_trace。
關(guān)鍵結(jié)果解讀
從 JSON 結(jié)果中重點關(guān)注以下部分：
rows_estimation：優(yōu)化器對各表行數(shù)的估算（若與實際偏差大，需更新統(tǒng)計信息）；potential_range_indexes：優(yōu)化器考慮的所有候選索引（包括未被選中的）；analyzing_range_alternatives：各索引的成本對比（cost字段，值越小越優(yōu)）；chosen_range_access_summary：最終選擇的索引及原因（如cost=3.76的索引被選中）。
示例解讀：
若potential_range_indexes中包含idx_user_time，但chosen_range_access_summary未選中，需查看cost是否高于全表掃描（可能因索引選擇性差，優(yōu)化器認為全表掃描更快），此時需優(yōu)化索引（如增加區(qū)分度更高的前綴字段）。
3. 精準量化工具：EXPLAIN ANALYZE（MySQL 8.0+）——實測執(zhí)行性能
EXPLAIN ANALYZE是 MySQL 8.0 引入的增強功能，會實際執(zhí)行查詢，并返回精確的執(zhí)行時間、掃描行數(shù)等 metrics，適合量化性能瓶頸（如排序耗時、掃描行數(shù)與預期的偏差）。
核心優(yōu)勢與使用場景
- 對比
EXPLAIN：EXPLAIN返回“預估”值，EXPLAIN ANALYZE返回“實際”值（如actual time、actual rows）； - 適合分析分頁查詢（
LIMIT offset, size）、排序、JOIN 等操作的真實耗時； - 量化索引優(yōu)化效果（如優(yōu)化前后的執(zhí)行時間對比）。
警告：
- 對大表執(zhí)行
EXPLAIN ANALYZE會消耗實際資源（如全表掃描 1000 萬行），生產(chǎn)環(huán)境謹慎使用； - 非
SELECT語句（如UPDATE、DELETE）禁用，避免誤操作數(shù)據(jù)。 - 會實際執(zhí)行語句，因此非 DQL 語句謹慎執(zhí)行?。?！
案例：分析分頁查詢性能
需求：分析“查詢狀態(tài)為 2 的訂單，按時間排序后取第 10001-10020 條”的性能瓶頸。
查詢：
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 2 ORDER BY order_time LIMIT 10000, 20\G
未優(yōu)化結(jié)果：
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)
關(guān)鍵信息解讀：
總執(zhí)行時間：744ms（根節(jié)點的actual time）
各階段實際耗時：
- 表掃描（
Table scan on orders）：(505ms - 3.25ms)*1 ≈ 502ms - 過濾（
Filter）：(591ms - 3.26ms)*1 ≈ 588ms（包含表掃描時間） - 排序（
Sort）：(743ms - 3.26ms)*1 ≈ 740ms（包含表掃描和過濾時間）
| 字段 | 含義 |
|---|---|
| cost | 優(yōu)化器預估的執(zhí)行成本（數(shù)值越小越好，基于 CPU 消耗、IO 操作等計算） |
| rows（預估） | 優(yōu)化器預估的需要處理的行數(shù)（反映查詢的“工作量”，數(shù)值越小效率越高） |
| actual time | 實際執(zhí)行時間（格式為 開始時間..結(jié)束時間，單位為毫秒） |
| actual rows | 實際處理的行數(shù)（反映真實數(shù)據(jù)量，與預估 rows 差異過大需關(guān)注） |
| loops | 操作執(zhí)行的循環(huán)次數(shù)（非join或帶子查詢的sql通常為 1） |
優(yōu)化方案：創(chuàng)建覆蓋“過濾+排序”的復合索引：
CREATE INDEX idx_status_time ON orders(status, order_time);
優(yōu)化后結(jié)果：
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)
優(yōu)化效果：
- 總時間從 744ms 降至 48ms（提升 93%）；
- 消除全表掃描和文件排序，直接通過索引定位數(shù)據(jù)（
Index lookup）。
五、案例 - 索引問題與表結(jié)構(gòu)
1. 索引失效 - 函數(shù)操作索引列
案例 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)
執(zhí)行計劃問題：對索引列register_time使用了函數(shù)，導致索引失效。
優(yōu)化手段：改為范圍查詢，不要對索引列使用函數(shù)。
2. 索引失效 - 隱式類型轉(zhuǎn)換
案例 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)
執(zhí)行計劃問題：product_name是 varchar 類型，但查詢條件使用了數(shù)字類型，發(fā)生隱式類型轉(zhuǎn)換導致索引失效。參考：select count(1) from products where CAST(product_name AS SIGNED)=12345;
優(yōu)化手段：查詢條件類型與索引列字段一致。
可能有小伙伴會問，明明查出來是 0 行，怎么執(zhí)行計劃 rows=1 呢，實際在掃索引時 mysql 也已經(jīng)知道了沒有匹配的結(jié)果，理論返回 0 行就行，但 mysql 代碼里寫死了這種情況返回 1，他們也沒有解釋那就這樣吧。
3. 索引設(shè)計不合理 - 單值索引 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) -- 優(yōu)化索引順序 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);
執(zhí)行計劃問題：雖然有復合索引idx_price_category(price, category_id)，但范圍查詢price > 900導致索引截斷。
優(yōu)化手段：調(diào)整索引順序為(category_id, price)
六、案例 - 執(zhí)行計劃異常與查詢寫法缺陷
1. 優(yōu)化器誤判（統(tǒng)計信息過時，以 products 表為例）
案例 SQL：
-- 查詢特定分類的商品，products表有idx_category索引 EXPLAIN SELECT * FROM products WHERE category_id = 15; ANALYZE TABLE products; -- 更新統(tǒng)計信息后，執(zhí)行計劃會選擇idx_category索引
執(zhí)行計劃問題：優(yōu)化器誤判（因統(tǒng)計信息過時），認為全表掃描比走idx_category快（實際category_id=15的數(shù)據(jù)量很?。?。
統(tǒng)計信息過時的原因:
- 數(shù)據(jù)量劇變：批量插入 / 刪除 / 更新超過表總量的 10%。
- 分布劇變：字段值的重復度、基數(shù)發(fā)生顯著變化（如從低重復到高重復）。
- 配置限制：關(guān)閉自動更新、采樣精度不足。
- 結(jié)構(gòu)變更：新增索引、修改字段類型后未同步更新統(tǒng)計信息。
統(tǒng)計信息過時會直接導致優(yōu)化器誤判執(zhí)行計劃（如全表掃描 vs 索引掃描、JOIN 順序錯誤），因此需在上述場景中定期執(zhí)行 ANALYZE TABLE 或開啟自動更新（非極致寫入場景）。
優(yōu)化手段：更新表統(tǒng)計信息，幫助優(yōu)化器正確判斷。
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 和內(nèi)存開銷（尤其orders表數(shù)據(jù)量達 100 萬條）。多余字段占用大量 IO 帶寬，拖慢查詢。
優(yōu)化手段：只查詢業(yè)務需要的字段
3. 復雜子查詢嵌套
案例 SQL：
-- 統(tǒng)計2024 年每個用戶的訂單總金額（包含所有用戶，即使無訂單也顯示 0）
EXPLAIN ANALYZE SELECT
u.user_id,
u.username,
-- 子查詢：統(tǒng)計該用戶2024年的訂單總金額（無訂單則返回NULL，用IFNULL轉(zhuǎn)為0）
IFNULL((SELECT SUM(o.amount)
FROM orders o
WHERE o.user_id = u.user_id
AND 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 SELECT
u.user_id,
u.username,
COALESCE(s.total_amount, 0) AS total_2024_amount
FROM users u
LEFT JOIN (
SELECT
user_id,
SUM(amount) AS total_amount
FROM orders
WHERE order_time >= '2024-01-01'
AND order_time < '2025-01-01'
GROUP BY user_id -- 預聚合訂單數(shù)據(jù)
) 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)
執(zhí)行計劃問題：10 萬次子查詢重復執(zhí)行 sum()，累積開銷大。
優(yōu)化手段：
- 用覆蓋索引避免回表 IO：子查詢和主查詢均通過 idx_user_time_amount 獲取所需字段（user_id、order_time、amount），無需回表。
- 減少中間結(jié)果集大?。ㄈ珙A聚合）：將 SUM(amount)的計算放在子查詢中，避免主查詢處理大量中間結(jié)果。
七、案例 - 分頁查詢
傳統(tǒng)分頁問題（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 行數(shù)據(jù)，然后丟棄前 100000 行，僅返回 20 行，99.98%的掃描是無效的。- 即使有排序，大偏移量仍會導致全表掃描+排序，IO 和 CPU 開銷極高。
分頁優(yōu)化核心思路
| 優(yōu)化方案 | 核心思路 | 適用場景 | 性能提升幅度 |
|---|---|---|---|
| 主鍵偏移量分頁 | 用WHERE id > 偏移值替代LIMIT 偏移量 | 按連續(xù)主鍵排序，有上一頁 ID | 50-100 倍 |
| 條件+邊界值分頁 | 用WHERE 條件 AND 字段 > 上一頁值 | 按非主鍵排序，有過濾條件 | 30-50 倍 |
| 延遲關(guān)聯(lián)+小范圍 LIMIT | 先查 ID 再回表，減少大偏移量數(shù)據(jù)量 | 必須用大偏移量，無邊界值 | 2-3 倍 |
優(yōu)化方案 1：主鍵偏移量分頁（利用連續(xù)主鍵）
通過已知的最后一條記錄的主鍵（如第 100000 行的order_id）作為條件，直接定位到起始位置，避免掃描偏移量內(nèi)的所有行。
優(yōu)化后 SQL：
-- 假設(shè)第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)
寫法優(yōu)勢：
- 無需掃描偏移量內(nèi)數(shù)據(jù)：通過
WHERE order_id > 100000直接定位到起始點，掃描行數(shù)從 100020 降至 20 行。 - 利用現(xiàn)有主鍵索引：
PRIMARY KEY (order_id)天然存在，無需額外索引，通過范圍查詢（range）快速定位。
適用場景：
- 分頁按連續(xù)自增主鍵排序（如
order_id）。 - 前端分頁可記錄上一頁最后一條記錄的
order_id（如“下一頁”按鈕傳遞該值）。
優(yōu)化方案 2：基于條件過濾的分段分頁（非主鍵排序）
當分頁需要按非主鍵字段排序（如order_time），且有固定過濾條件（如status=2，已支付訂單），傳統(tǒng)LIMIT大偏移量同樣低效。
傳統(tǒng)寫法問題：需掃描前 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)
優(yōu)化寫法（利用上一頁邊界值）：
-- 假設(shè)上一頁最后一條記錄的order_time為'2025-05-01 10:00:00'，order_id為50000
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE status = 2
AND 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聯(lián)合排序，避免重復/遺漏
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)
寫法優(yōu)勢：
- 通過條件過濾替代偏移量：利用上一頁最后一條記錄的
order_time和order_id作為邊界，直接定位到下一頁起始位置，避免掃描前 10000 條記錄。 - 聯(lián)合排序去重：
ORDER BY order_time, order_id確保排序唯一，避免同時間訂單重復或遺漏。 - 復用現(xiàn)有索引：
idx_user_time (user_id, order_time)雖以user_id開頭，但order_time作為第二列可輔助范圍查詢（配合status=2過濾）。
適用場景：
- 按非主鍵字段排序（如
order_time）。 - 有固定過濾條件（如
status=2），可通過條件+邊界值快速定位。 - 前端需記錄上一頁最后一條記錄的
order_time和order_id（如傳遞給“下一頁”接口）。
優(yōu)化方案 3：延遲關(guān)聯(lián)+小范圍 LIMIT（無邊界值時）
當無法獲取上一頁邊界值（如“跳轉(zhuǎn)至第 500 頁”），且必須使用大偏移量，可通過“先查 ID，再回表”減少無效數(shù)據(jù)傳輸。
優(yōu)化寫法：
-- 子查詢先獲取目標頁的order_id，再關(guān)聯(lián)回表
EXPLAIN ANALYZE
SELECT o.*
FROM orders o
JOIN (
-- 子查詢僅查ID，數(shù)據(jù)量小，排序/偏移高效
SELECT order_id
FROM orders
WHERE status = 2
ORDER BY order_time
LIMIT 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)
寫法優(yōu)勢：
- 減少排序/偏移的數(shù)據(jù)量：子查詢僅處理
order_id（4 字節(jié)），比全字段（*包含多個字段，約 50 字節(jié)）更輕量，排序和偏移效率更高。 - 回表數(shù)據(jù)量小：僅對 20 條
order_id回表查詢?nèi)侄?，避?10000 條無效記錄的全字段傳輸。
適用場景：
- 必須使用大偏移量（如“跳轉(zhuǎn)至第 N 頁”）。
- 表字段較多（
*包含大量數(shù)據(jù)），通過先查 ID 減少中間數(shù)據(jù)傳輸。
以上就是MYSQL中慢SQL原因與優(yōu)化方法詳解的詳細內(nèi)容，更多關(guān)于MYSQL慢SQL的資料請關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
如何區(qū)分MySQL的innodb_flush_log_at_trx_commit和sync_binlog
這篇文章主要介紹了如何區(qū)分MySQL的innodb_flush_log_at_trx_commit和sync_binlog，幫助大家更好的理解和使用MySQL數(shù)據(jù)庫，感興趣的朋友可以了解下2021-02-02
這篇文章主要介紹了MySql 索引、鎖、事務知識點,總結(jié)分析了mysql數(shù)據(jù)庫中關(guān)于索引、鎖和事務的概念、原理、知識點及相關(guān)注意事項,需要的朋友可以參考下2019-10-10
這篇文章主要介紹了mysql函數(shù)，下面文章從MySQL常用的函數(shù)開始介紹、還有數(shù)值函數(shù)，利用舉例說明的形式展開內(nèi)容,需要的朋友可以參考一下2021-11-11
這篇文章主要為大家詳細介紹了CentOS7下二進制安裝mysql 5.7.23，具有一定的參考價值，感興趣的小伙伴們可以參考一下2019-06-06
這幾天在學習mysql數(shù)據(jù)庫的優(yōu)化并在自己的服務器上進行設(shè)置，喻名堂主要學習了MyISAM和InnoDB兩種引擎的優(yōu)化方法，需要了解跟多的朋友可以參考下2012-11-11
數(shù)據(jù)庫死鎖問題是我們老生常談的問題了，在我們實際開發(fā)過程中經(jīng)常會遇到，本文主要介紹了MySQL之死鎖問題及其解決方案，具有一定的參考價值，感興趣的可以了解一下2023-12-12
mysql中find_in_set()函數(shù)用法及自定義增強函數(shù)詳解
這篇文章主要給大家介紹了關(guān)于mysql中find_in_set()函數(shù)用法及自定義增強函數(shù)的相關(guān)資料,在MySQL 數(shù)據(jù)庫中進行復雜的查詢語句,例如對多個字段進行篩選和排序,文中通過代碼介紹的非常詳細,需要的朋友可以參考下2024-06-06
MySQL8.0登錄時出現(xiàn)Access?denied?for?user?‘root‘@‘localhost‘?
這篇文章主要給大家介紹了解決MySQL8.0登錄時出現(xiàn)Access?denied?for?user?‘root‘@‘localhost‘?(using?password:?YES)?拒絕訪問的問題,文中有詳細的解決方法,需要的朋友可以參考下2023-09-09
這篇文章主要介紹了MySQL優(yōu)化器的SQL重寫規(guī)則,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-07-07
MySql 5.7.14 服務沒有報告任何錯誤的解決方法(推薦)
這篇文章主要介紹了MySql 5.7.14 服務沒有報告任何錯誤解決方法的相關(guān)資料,非常不錯，具有參考借鑒價值，需要的朋友可以參考下2016-09-09