用 EXPLAIN 和 Performance Schema 定位索引误选、锁等待与系统调优，构建全链路排查实战
作者： ShunWah
公众号： "shunwah星辰数智社"主理人。持有认证： OceanBase OBCA/OBCP、MySQL OCP、OpenGauss、崖山YCA、金仓KingBase KCA/KCP、KaiwuDB KWCA/KWCP、 亚信 AntDBCA、翰高 HDCA、GBase 8a/8c/8s、Galaxybase GBCA、Neo4j ScienceCertification、NebulaGraph NGCI/NGCP、东方通TongTech TCPE、TiDB PCTA等多项权威认证。
获奖经历： 崖山YashanDB YVP、浪潮KaiwuDB MVP、墨天轮 MVP、金仓社区KVA、TiDB社区MVA、NebulaGraph社区之星 ，担任 OceanBase 社区版主及布道师。曾在OceanBase&墨天轮征文大赛、OpenGauss、TiDB、YashanDB、Kingbase、KWDB、Navicat Premium × 金仓数据库征文等赛事中多次斩获一、二、三等奖，原创技术文章常年被墨天轮、CSDN、ITPUB 等平台首页推荐。
- CSDN_ID： shunwahma
- 墨天轮_ID：shunwah
- ITPUB_ID： shunwah
- IFClub_ID：shunwah
前言
在数据库管理的世界里，性能问题往往藏匿于细节之中。一条慢 SQL 可能拖垮整个应用，一个错误的索引选择可能让高并发系统瞬间雪崩。MySQL 8.0 作为目前广泛使用的版本，带来了窗口函数、CTE（公用表表达式）以及更智能的优化器，但同时也引入了新的参数陷阱和排查挑战。
本文不堆砌理论，将在一个全新的 CentOS 7 + Docker + MySQL 8.0.42-debian 环境上，从零开始构建一套“发现问题 -> 解读计划 -> 定位瓶颈 -> 优化解决”的实战能力。文章将分为四个部分：
- 工欲善其事：执行计划（EXPLAIN）的“新”解读
- 顺藤摸瓜：通过 Performance Schema 定位故障源头
- 釜底抽薪：参数调优与索引设计的落地实操
- 复盘案例：一个索引误选导致的线上故障模拟
一、工欲善其事：执行计划（EXPLAIN）的深度解读
在 MySQL 8.0 中，EXPLAIN 已经不再是那个只有几张表的“老古董”。EXPLAIN ANALYZE 和 FORMAT=TREE 的引入，让我们能以前所未有的精度观察优化器的每一步操作 。
1.1 准备“实验台”
首先，进入 Docker 环境。通过 Docker 启动了 MySQL 容器。
# 进入容器（假设容器名为 mysql8-lab）
[root@instance2 ~]# docker exec -it mysql-8 bash
root@960a0ba8dcb4:/#
登录 MySQL：
root@960a0ba8dcb4:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.42 MySQL Community Server - GPL
Copyright (c) 2000, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
创建实验数据库：
mysql> CREATE DATABASE IF NOT EXISTS perf_lab;
Query OK, 1 row affected (0.00 sec)
mysql> USE perf_lab;
Database changed
mysql>
创建两张表：用户表（user）和订单表（order）。注意：为了演示问题，我们故意暂时不建索引。
mysql> CREATE TABLE `user` (
-> `id` INT AUTO_INCREMENT PRIMARY KEY,
-> `name` VARCHAR(100),
-> `age` INT,
-> `email` VARCHAR(100),
-> `created_at` DATETIME
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE `order` (
-> `id` INT AUTO_INCREMENT PRIMARY KEY,
-> `user_id` INT,
-> `amount` DECIMAL(10,2),
-> `status` VARCHAR(20),
-> `order_date` DATETIME
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
附：生成 10 万用户 + 50 万订单的完整脚本
为了模拟真实场景，需要生成足够多的测试数据。以下是两个存储过程，分别用于填充 user 表和 order 表。
1. 插入 10 万条用户数据
使用存储过程 InsertUsers，每 1000 条提交一次事务。
mysql> DELIMITER $$
mysql> CREATE PROCEDURE InsertUsers(IN num_rows INT)
-> BEGIN
-> DECLARE i INT DEFAULT 1;
-> START TRANSACTION;
-> WHILE i <= num_rows DO
-> INSERT INTO `user` (`name`, `age`, `email`, `created_at`)
-> VALUES (
-> CONCAT('User', i),
-> FLOOR(RAND() * 50) + 18,
-> CONCAT('user', i, '@example.com'),
-> DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY)
-> );
-> IF i % 1000 = 0 THEN
-> COMMIT;
-> START TRANSACTION;
-> END IF;
-> SET i = i + 1;
-> END WHILE;
-> COMMIT;
-> END$$
Query OK, 0 rows affected (0.03 sec)
mysql> DELIMITER ;
mysql>
执行调用：
mysql> CALL InsertUsers(100000);
Query OK, 0 rows affected (2.16 sec)
2. 插入 50 万条订单数据
使用存储过程 InsertOrders，随机关联用户 ID。
mysql> DELIMITER $$
mysql> CREATE PROCEDURE InsertOrders(IN num_rows INT)
-> BEGIN
-> DECLARE i INT DEFAULT 1;
-> DECLARE max_user_id INT DEFAULT 100000;
-> START TRANSACTION;
-> WHILE i <= num_rows DO
-> INSERT INTO `order` (`user_id`, `amount`, `status`, `order_date`)
-> VALUES (
-> FLOOR(RAND() * max_user_id) + 1,
-> ROUND(RAND() * 1000, 2),
-> ELT(FLOOR(RAND() * 3) + 1, 'pending', 'completed', 'cancelled'),
-> DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 730) DAY)
-> );
-> IF i % 1000 = 0 THEN
-> COMMIT;
-> START TRANSACTION;
-> END IF;
-> SET i = i + 1;
-> END WHILE;
-> COMMIT;
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql>
执行调用：
mysql> CALL InsertOrders(500000);
Query OK, 0 rows affected (10.22 sec)
3. 验证数据量
mysql> SELECT 'user' AS table_name, COUNT(*) AS total_rows FROM user
-> UNION ALL
-> SELECT 'order', COUNT(*) FROM `order`;
+------------+------------+
| table_name | total_rows |
+------------+------------+
| user | 100000 |
| order | 500000 |
+------------+------------+
2 rows in set (0.04 sec)
现在已经拥有 10 万用户和 50 万订单。
1.2 不仅仅是表格：使用 TREE 格式
在 MySQL 8.0.16 及以后版本，推荐使用 FORMAT=TREE 或 EXPLAIN ANALYZE。传统的表格格式有时会掩盖执行路径的细节。
场景：查询年龄大于 25 岁的用户及其订单金额。
mysql> EXPLAIN FORMAT=TREE
-> SELECT u.name, u.age, o.amount
-> FROM user u
-> JOIN `order` o ON u.id = o.user_id
-> WHERE u.age > 25;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=224922 rows=166301)
-> Filter: (o.user_id is not null) (cost=50288 rows=498953)
-> Table scan on o (cost=50288 rows=498953)
-> Filter: (u.age > 25) (cost=0.25 rows=0.333)
-> Single-row index lookup on u using PRIMARY (id=o.user_id) (cost=0.25 rows=1)
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)
技术解析：
- 这里出现了
Hash Join！这是 MySQL 8.0 的重大改进。 - 解读：MySQL 优化器判断
user表（经过年龄过滤后）和order表数据量较大，且连接字段无索引，因此选择将小表（过滤后的 user）构建成哈希表，然后扫描大表（order）进行探测。 - 结论：虽然使用了 Hash Join，但
Table scan仍然是危险的信号，标志着我们需要通过索引来优化。
1.3 动真格：EXPLAIN ANALYZE
EXPLAIN ANALYZE 不仅展示执行计划，还会实际执行 SQL（注意对于写操作需谨慎），并返回每个步骤的实际耗时、循环次数和返回行数。
mysql> EXPLAIN ANALYZE
-> SELECT u.name, u.age, o.amount
-> FROM user u
-> JOIN `order` o ON u.id = o.user_id
-> WHERE u.age > 25;
| EXPLAIN
| -> Nested loop inner join (cost=224922 rows=166301) (actual time=2.94..1187 rows=418952 loops=1)
-> Filter: (o.user_id is not null) (cost=50288 rows=498953) (actual time=2.9..224 rows=500000 loops=1)
-> Table scan on o (cost=50288 rows=498953) (actual time=2.89..179 rows=500000 loops=1)
-> Filter: (u.age > 25) (cost=0.25 rows=0.333) (actual time=0.00163..0.00173 rows=0.838 loops=500000)
-> Single-row index lookup on u using PRIMARY (id=o.user_id) (cost=0.25 rows=1) (actual time=0.00143..0.00146 rows=1 loops=500000)
1 row in set (1.27 sec)
输出包含具体的耗时数据（如 actual time=0.2..3.5 rows=100）。通过对比“预估行数”（rows）和“实际行数”（actual rows），我们可以判断优化器的统计信息是否过时。
1.4 添加索引并观察变化
现在为连接字段和过滤字段创建索引：
-- 为 user 表的 age 字段创建索引（用于 WHERE 过滤）
mysql> CREATE INDEX idx_user_age ON user(age);
Query OK, 0 rows affected (0.28 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 为 order 表的 user_id 字段创建索引（用于 JOIN 连接）
mysql> CREATE INDEX idx_order_user_id ON `order`(user_id);
Query OK, 0 rows affected (1.31 sec)
Records: 0 Duplicates: 0 Warnings: 0
再次执行 EXPLAIN ANALYZE：
mysql> EXPLAIN ANALYZE
-> SELECT u.name, u.age, o.amount
-> FROM user u
-> JOIN `order` o ON u.id = o.user_id
-> WHERE u.age > 25
-> LIMIT 10;
| EXPLAIN
| -> Limit: 10 row(s) (cost=109953 rows=10) (actual time=1.46..1.52 rows=10 loops=1)
-> Nested loop inner join (cost=109953 rows=250465) (actual time=1.46..1.52 rows=10 loops=1)
-> Index range scan on u using idx_user_age over (25 < age), with index condition: (u.age > 25) (cost=22290 rows=49533) (actual time=0.891..0.893 rows=2 loops=1)
-> Index lookup on o using idx_order_user_id (user_id=u.id) (cost=1.26 rows=5.06) (actual time=0.306..0.308 rows=5 loops=2)
1 row in set (0.05 sec)
现在执行计划变成了 Nested Loop Join，并且使用了索引范围扫描和索引查找，避免了全表扫描，性能大幅提升。
1.5 进入“ORDER BY LIMIT”索引误选实战
现在已经熟悉了 EXPLAIN 的基本用法，复现一个生产环境常见的坑：ORDER BY ... LIMIT 导致优化器选错索引。
1.5.1 为 created_at 创建索引
mysql> CREATE INDEX idx_created ON user(created_at);
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0
1.5.2 执行查询（不加任何 Hint）
mysql> EXPLAIN ANALYZE
-> SELECT * FROM user
-> WHERE age > 30
-> ORDER BY created_at DESC
-> LIMIT 10;
| EXPLAIN
| -> Limit: 10 row(s) (cost=1.02 rows=10) (actual time=0.144..0.189 rows=10 loops=1)
-> Filter: (`user`.age > 30) (cost=1.02 rows=10) (actual time=0.143..0.186 rows=10 loops=1)
-> Index scan on user using idx_created (reverse) (cost=1.02 rows=20) (actual time=0.126..0.166 rows=13 loops=1)
1 row in set (0.01 sec)
执行计划显示使用了 idx_created 索引逆序扫描，仅扫描了 13 行就找到了所需的 10 条记录，耗时仅 0.189 ms，非常高效。
1.5.3 分析是否误选
如果输出显示 Index scan using idx_created 并且扫描的行数非常大（例如数万行才找到 10 条满足 age>30 的记录），那就说明优化器选错了。本例中扫描行数很少，说明优化器选择正确。
1.5.4 强制使用正确索引
mysql> EXPLAIN ANALYZE
-> SELECT * FROM user FORCE INDEX (idx_user_age)
-> WHERE age > 30
-> ORDER BY created_at DESC
-> LIMIT 10;
| EXPLAIN
| -> Limit: 10 row(s) (cost=22290 rows=10) (actual time=165..165 rows=10 loops=1)
-> Sort: `user`.created_at DESC, limit input to 10 row(s) per chunk (cost=22290 rows=49533) (actual time=165..165 rows=10 loops=1)
-> Index range scan on user using idx_user_age over (30 < age), with index condition: (`user`.age > 30) (cost=22290 rows=49533) (actual time=0.728..153 rows=73797 loops=1)
1 row in set (0.17 sec)
强制使用 idx_user_age 后，需要先扫描 73797 行满足 age>30 的记录，然后排序，最后取 10 条，耗时 165 ms，比优化器的选择慢了约 1000 倍。
1.5.5 调整优化器开关（另一种解法）
mysql> SET SESSION optimizer_switch = 'prefer_ordering_index=OFF';
Query OK, 0 rows affected (0.00 sec)
mysql> EXPLAIN ANALYZE
-> SELECT * FROM user
-> WHERE age > 30
-> ORDER BY created_at DESC
-> LIMIT 10;
| EXPLAIN
| -> Limit: 10 row(s) (cost=1.02 rows=10) (actual time=0.0547..0.092 rows=10 loops=1)
-> Filter: (`user`.age > 30) (cost=1.02 rows=10) (actual time=0.0538..0.0902 rows=10 loops=1)
-> Index scan on user using idx_created (reverse) (cost=1.02 rows=20) (actual time=0.052..0.0862 rows=13 loops=1)
1 row in set (0.00 sec)
关闭 prefer_ordering_index 后，优化器依然选择了 idx_created 索引，因为基于成本计算该方案依然最优。这说明开关并非盲目改变行为，而是让优化器更纯粹地基于成本做决策。
1.6 关键洞察：什么才是真正的“索引误选”？
这次的实验表明：优化器的选择往往比直觉更聪明。在 ORDER BY ... LIMIT 场景中，如果满足 WHERE 条件的记录比例适中，并且 LIMIT 很小，那么从排序索引开始扫描，直到凑足 LIMIT 条记录，通常是最快的方式。这被称为 “Index Skip Scan” 的变体或 “Loose Index Scan” 的思想。
要复现真正的“误选”，可以尝试调整 age 的过滤条件，让满足条件的记录变得极少，例如：
-- 假设年龄大于 60 的用户非常少（在随机数据中约 10%）
mysql> EXPLAIN ANALYZE
-> SELECT * FROM user
-> WHERE age > 60
-> ORDER BY created_at DESC
-> LIMIT 10;
| EXPLAIN
| -> Limit: 10 row(s) (cost=11457 rows=10) (actual time=46.1..46.2 rows=10 loops=1)
-> Sort: `user`.created_at DESC, limit input to 10 row(s) per chunk (cost=11457 rows=25460) (actual time=46.1..46.1 rows=10 loops=1)
-> Index range scan on user using idx_user_age over (60 < age), with index condition: (`user`.age > 60) (cost=11457 rows=25460) (actual time=0.731..41.8 rows=13970 loops=1)
|
1 row in set (0.04 sec)
如果此时优化器仍然选择 idx_created，并且实际扫描了大量行（比如数万行）才找到 10 条，那就说明它“误选”了。这时可以比较强制使用 idx_user_age 的性能。
二、顺藤摸瓜：利用 Performance Schema 定位系统故障
当慢查询不止一两条，而是整个数据库性能骤降时，需要从“大海”里捞针。MySQL 的 Performance Schema 就是这只“针”。
2.1 抓住等待的源头
发现数据库响应变慢，想看看当前线程在等待什么。
查看当前正在执行的语句（特别是状态异常的）：
mysql> SELECT * FROM performance_schema.events_statements_current\G
*************************** 1. row ***************************
THREAD_ID: 78
EVENT_ID: 601852
END_EVENT_ID: NULL
EVENT_NAME: statement/sql/select
SOURCE: init_net_server_extension.cc:103
TIMER_START: 4514488477037333000
TIMER_END: 4514488477799518000
TIMER_WAIT: 762185000
LOCK_TIME: 16000000
SQL_TEXT: SELECT * FROM performance_schema.events_statements_current
DIGEST: 91caf112a1e8c015485be7c90b3abc04f3aa12909461daf3d3c002cc88ed6f0f
DIGEST_TEXT: SELECT * FROM `performance_schema` . `events_statements_current`
CURRENT_SCHEMA: perf_lab
...
1 row in set (0.00 sec)
2.2 模拟一个锁等待场景并观察 Performance Schema
为了真正体验 Performance Schema 的威力，需要制造一个真实的阻塞场景。
2.2.1 准备工作：两个终端窗口
- 终端 1：当前你的 MySQL 会话（称为 Session A）。
- 终端 2：新开一个终端，用同样的方式连接到 MySQL（称为 Session B）。
2.2.2 步骤 1：在 Session A 中开启事务并更新一行但不提交
在 Session A 中执行：
mysql> USE perf_lab;
Database changed
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE `order` SET status = 'processing' WHERE id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- 注意不要执行 COMMIT 或 ROLLBACK
现在 Session A 持有了 id=1 这一行的行锁（X 锁）。
2.2.3 步骤 2：在 Session B 中尝试更新同一行
在 Session B 中执行：
mysql> USE perf_lab;
Database changed
mysql> UPDATE `order` SET status = 'completed' WHERE id = 1;
这条更新语句会被阻塞，因为 Session A 还未释放锁。此时 Session B 会一直等待。
2.2.4 步骤 3：查询 Performance Schema 观察等待事件
回到 Session A（或新开一个 Session C），执行以下查询查看当前正在执行的语句：
mysql> SELECT * FROM performance_schema.events_statements_current\G
*************************** 1. row ***************************
THREAD_ID: 108
EVENT_ID: 10
END_EVENT_ID: 11
EVENT_NAME: statement/sql/update
SOURCE: init_net_server_extension.cc:103
TIMER_START: 4514738879943552000
TIMER_END: 4514788908775942000
TIMER_WAIT: 50028832390000
LOCK_TIME: 50007567000000
SQL_TEXT: UPDATE `order` SET status = 'completed' WHERE id = 1
DIGEST: e21020e9019a5b1b1e0402ac901d63e85dcd28efe05daa94aeb5982603907586
DIGEST_TEXT: UPDATE `order` SET STATUS = ? WHERE `id` = ?
CURRENT_SCHEMA: perf_lab
...
*************************** 2. row ***************************
THREAD_ID: 109
EVENT_ID: 13
END_EVENT_ID: NULL
EVENT_NAME: statement/sql/select
...
2 rows in set (0.00 sec)
查看当前等待事件（锁等待）：
mysql> SELECT * FROM performance_schema.events_waits_current\G
Empty set (0.00 sec)
events_waits_current 为空，说明等待事件的消费者未启用。
解决办法：启用等待事件消费者
mysql> SELECT * FROM performance_schema.setup_consumers;
+----------------------------------+---------+
| NAME | ENABLED |
+----------------------------------+---------+
| events_waits_current | NO |
...
+----------------------------------+---------+
16 rows in set (0.01 sec)
mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE 'events_waits%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> SELECT * FROM performance_schema.events_waits_current\G
*************************** 1. row ***************************
THREAD_ID: 1
EVENT_ID: 26
END_EVENT_ID: 26
EVENT_NAME: wait/synch/mutex/sql/LOCK_thread_cache
...
4 rows in set (0.00 sec)
查看锁等待关系：
mysql> SELECT * FROM performance_schema.data_lock_waits\G
*************************** 1. row ***************************
ENGINE: INNODB
REQUESTING_ENGINE_LOCK_ID: 140562586816512:886:618:5:327:140562468495360
REQUESTING_ENGINE_TRANSACTION_ID: 74739
REQUESTING_THREAD_ID: 108
REQUESTING_EVENT_ID: 4635
REQUESTING_OBJECT_INSTANCE_BEGIN: 140562468495360
BLOCKING_ENGINE_LOCK_ID: 140562586815704:1522:618:5:327:140562468489184
BLOCKING_ENGINE_TRANSACTION_ID: 74738
BLOCKING_THREAD_ID: 109
BLOCKING_EVENT_ID: 7538
BLOCKING_OBJECT_INSTANCE_BEGIN: 140562468489184
1 row in set (0.00 sec)
查看所有当前锁：
mysql> SELECT * FROM performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140562586815704:1522:1684:140562468492176
ENGINE_TRANSACTION_ID: 74738
THREAD_ID: 109
EVENT_ID: 7531
OBJECT_SCHEMA: perf_lab
OBJECT_NAME: order
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140562468492176
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140562586816512:886:1684:140562468498272
ENGINE_TRANSACTION_ID: 74739
THREAD_ID: 108
EVENT_ID: 4621
OBJECT_SCHEMA: perf_lab
OBJECT_NAME: order
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140562468498272
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140562586815704:1522:618:5:327:140562468489184
ENGINE_TRANSACTION_ID: 74738
THREAD_ID: 109
EVENT_ID: 7538
OBJECT_SCHEMA: perf_lab
OBJECT_NAME: order
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140562468489184
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 1
*************************** 4. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140562586816512:886:618:5:327:140562468495360
ENGINE_TRANSACTION_ID: 74739
THREAD_ID: 108
EVENT_ID: 4635
OBJECT_SCHEMA: perf_lab
OBJECT_NAME: order
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140562468495360
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: WAITING
LOCK_DATA: 1
4 rows in set (0.00 sec)
2.2.5 步骤 4：查找阻塞源头
查看阻塞者正在执行的语句：
mysql> SELECT * FROM performance_schema.events_statements_current WHERE THREAD_ID = 109\G
*************************** 1. row ***************************
THREAD_ID: 109
EVENT_ID: 13953
END_EVENT_ID: NULL
EVENT_NAME: statement/sql/select
SOURCE: init_net_server_extension.cc:103
TIMER_START: 4515583210560793000
TIMER_END: 4515583210889773000
TIMER_WAIT: 328980000
LOCK_TIME: 3000000
SQL_TEXT: SELECT * FROM performance_schema.events_statements_current WHERE THREAD_ID = 109
DIGEST: 1aae5ea510a7f0a4b984fe6e47bf5f1db8eaed764d0cebef84051b061631bb54
DIGEST_TEXT: SELECT * FROM `performance_schema` . `events_statements_current` WHERE `THREAD_ID` = ?
CURRENT_SCHEMA: perf_lab
...
1 row in set (0.00 sec)
查看被阻塞者正在执行的语句：
mysql> SELECT * FROM performance_schema.events_statements_current WHERE THREAD_ID = 108\G
*************************** 1. row ***************************
THREAD_ID: 108
EVENT_ID: 4593
END_EVENT_ID: 4679
EVENT_NAME: statement/sql/update
SOURCE: init_net_server_extension.cc:103
TIMER_START: 4515476794273953000
TIMER_END: 4515526802400819000
TIMER_WAIT: 50008126866000
LOCK_TIME: 50007395000000
SQL_TEXT: UPDATE `order` SET status = 'completed' WHERE id = 1
DIGEST: e21020e9019a5b1b1e0402ac901d63e85dcd28efe05daa94aeb5982603907586
DIGEST_TEXT: UPDATE `order` SET STATUS = ? WHERE `id` = ?
CURRENT_SCHEMA: perf_lab
...
1 row in set (0.00 sec)
查看当前所有线程的状态：
mysql> SELECT THREAD_ID, PROCESSLIST_ID, PROCESSLIST_USER, PROCESSLIST_HOST, PROCESSLIST_COMMAND, PROCESSLIST_TIME
-> FROM performance_schema.threads
-> WHERE PROCESSLIST_COMMAND != 'Sleep';
+-----------+----------------+------------------+------------------+---------------------+------------------+
| THREAD_ID | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_COMMAND | PROCESSLIST_TIME |
+-----------+----------------+------------------+------------------+---------------------+------------------+
| 46 | 5 | event_scheduler | localhost | Daemon | 4515733 |
| 50 | 7 | NULL | NULL | Daemon | 4515733 |
| 109 | 16 | root | localhost | Query | 0 |
+-----------+----------------+------------------+------------------+---------------------+------------------+
3 rows in set (0.00 sec)
查看元数据锁（MDL）的持有和等待情况：
mysql> SELECT * FROM performance_schema.metadata_locks;
+-------------------+--------------------+---------------------------+-----------------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------------+--------------------+---------------------------+-----------------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE | perf_lab | order | NULL | 140554966307696 | SHARED_WRITE | TRANSACTION | GRANTED | sql_parse.cc:6156 | 109 | 7509 |
| TABLE | performance_schema | data_lock_waits | NULL | 140554966096928 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6156 | 109 | 7574 |
...
+-------------------+--------------------+---------------------------+-----------------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
18 rows in set (0.00 sec)
在 metadata_locks 表中，OWNER_THREAD_ID 持有锁的线程 ID，GRANTED 字段为 YES 表示持有锁，为 NO 表示正在等待。
2.2.6 步骤 5：清理环境
在 Session A 中提交事务释放锁：
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
此时 Session B 的更新会立即完成：
mysql> UPDATE `order` SET status = 'completed' WHERE id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> UPDATE `order` SET status = 'completed' WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
2.3 额外提示：直接观察阻塞的超简单方法
如果觉得 Performance Schema 太复杂，MySQL 也提供了更直观的 SHOW PROCESSLIST 命令：
mysql> SHOW FULL PROCESSLIST;
+----+-----------------+-----------+----------+---------+---------+------------------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+----------+---------+---------+------------------------+-----------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 4515862 | Waiting on empty queue | NULL |
| 15 | root | localhost | perf_lab | Sleep | 33 | | NULL |
| 16 | root | localhost | perf_lab | Query | 0 | init | SHOW FULL PROCESSLIST |
+----+-----------------+-----------+----------+---------+---------+------------------------+-----------------------+
3 rows in set, 1 warning (0.00 sec)
三、系统与参数优化的落地实操
在 CentOS 7 上跑 Dockerized MySQL，有些“坑”是必须踩平的，特别是 NUMA 架构和 I/O 调度器。
3.1 操作系统层：告别“笨拙”的 I/O
CentOS 7 默认的 I/O 调度器可能是 cfq（完全公平队列），这对于 SSD 和数据库来说简直是噩梦。
检查与优化：
# 在宿主机上执行，查看磁盘调度器
[root@instance2 ~]# cat /sys/block/sda/queue/scheduler
noop [deadline] cfq
如果是 cfq，建议修改为 noop 或 none（让硬件或 MySQL 自己管理）。同时需要关注 NUMA 问题，如果宿主机启用了 NUMA，可能导致性能波动。建议临时关闭 NUMA：
[root@instance2 ~]# echo 0 > /proc/sys/vm/zone_reclaim_mode
[root@instance2 ~]# iostat -x 1
Linux 3.10.0-693.el7.x86_64 (instance2) 03/04/2026 _x86_64_ (8 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
0.99 0.00 0.29 0.01 0.00 98.71
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sdb 0.00 0.43 0.00 5.81 0.19 124.88 43.06 0.00 0.46 5.12 0.46 0.23 0.13
sda 0.00 2.97 0.01 5.71 0.33 47.89 16.87 0.00 0.55 7.09 0.54 0.08 0.05
sdc 0.00 0.00 0.00 0.00 0.00 0.00 84.00 0.00 5.56 4.70 49.25 4.22 0.00
scd0 0.00 0.00 0.00 0.00 0.00 0.00 114.22 0.00 0.39 0.39 0.00 0.33 0.00
dm-0 0.00 0.00 0.01 8.68 0.33 47.89 11.10 0.00 0.47 7.11 0.46 0.05 0.05
dm-1 0.00 0.00 0.00 0.00 0.00 0.00 45.57 0.00 1.70 1.70 0.00 1.66 0.00
3.2 MySQL 层：8.0 的关键参数陷阱
MySQL 8.0 默认配置偏向于“保守兼容”，而非“高性能”。
缓冲池（Buffer Pool）调优：
-- 在容器内执行，查看当前状态
mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| innodb_buffer_pool_size | 5368709120 |
+-------------------------+------------+
1 row in set (0.00 sec)
mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
+---------------------------------------+-----------+
| Variable_name | Value |
+---------------------------------------+-----------+
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 0 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 116051370 |
| Innodb_buffer_pool_reads | 1739 |
+---------------------------------------+-----------+
5 rows in set (0.00 sec)
如果 Innodb_buffer_pool_reads 远大于 0，说明磁盘读多，缓冲池太小。在 Docker 容器中，通常建议设置为容器可用内存的 70% 左右。
一个 8.0 的坑：innodb_doublewrite_pages
MySQL 8.0.20 之后，innodb_doublewrite_pages 默认值为 4，在某些 SSD 场景下会导致 DDL 操作慢 3 倍。
-- 检查当前设定
mysql> SHOW VARIABLES LIKE 'innodb_doublewrite_pages';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_doublewrite_pages | 4 |
+--------------------------+-------+
1 row in set (0.00 sec)
建议调整为 128 或更高（如果你的磁盘支持），编辑配置文件，在 [mysqld] 部分添加或修改：
[root@instance2 mysql8]# vim cnf/my.cnf
[mysqld]
innodb_doublewrite_pages = 128
[root@instance2 mysql8]# docker-compose restart
Restarting mysql-8 ... done
[root@instance2 mysql8]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
8791b5a1ee54 kingbase:v1 "/bin/bash /home/kin…" 2 months ago Up 7 weeks 0.0.0.0:4321->54321/tcp, :::4321->54321/tcp kingbase
960a0ba8dcb4 mysql:8.0.42-debian "docker-entrypoint.s…" 7 months ago Up 3 seconds 33060/tcp, 0.0.0.0:3325->3306/tcp, :::3325->3306/tcp mysql-8
验证修改是否生效：
mysql> SHOW VARIABLES LIKE 'innodb_doublewrite_pages';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_doublewrite_pages | 128 |
+--------------------------+-------+
1 row in set (0.01 sec)
技术解析：双写缓冲区（Doublewrite Buffer）是为了保证数据页的完整性。innodb_doublewrite_pages 控制每次刷新的批次大小。批次太小（如 4），会导致频繁的刷新操作，无法利用 SSD 的高 IOPS 特性。
四、实战复盘：解决“ORDER BY LIMIT”的索引误选
这是生产环境常见的疑难杂症：一个 ORDER BY ... LIMIT 的查询，即使条件字段有索引，优化器也可能“脑残”地选择了排序字段的索引，导致扫描大量数据。
4.1 场景模拟
要查询“最近注册的、年龄大于 30 的 10 个用户”。
mysql> EXPLAIN ANALYZE
-> SELECT * FROM user
-> WHERE age > 30
-> ORDER BY created_at DESC
-> LIMIT 10;
| EXPLAIN
| -> Limit: 10 row(s) (cost=1.02 rows=10) (actual time=0.132..0.242 rows=10 loops=1)
-> Filter: (`user`.age > 30) (cost=1.02 rows=10) (actual time=0.13..0.238 rows=10 loops=1)
-> Index scan on user using idx_created (reverse) (cost=1.02 rows=20) (actual time=0.127..0.231 rows=13 loops=1)
1 row in set (0.03 sec)
最新查询结果完美印证了优化器的智能决策：对于 age > 30 这样高选择性的条件，优化器选择了 idx_created 索引逆序扫描，仅用 13 行就找到了所需的 10 条记录，耗时仅 0.242 ms。是期待的“最优计划”。
4.2 现象分析
执行计划可能显示使用了 idx_created 索引。
- 优化器逻辑：如果优化器认为
age > 30的数据非常多，那么通过idx_created索引顺序扫描，直到找到 10 条满足age > 30的记录就停止，可能比先过滤 age 再排序要快。 - 现实打脸：如果
age > 30的数据其实很少，或者分布很散，那么这种策略就会变成“全表扫描式的排序”，性能极差。
4.3 解决方案：使用优化器 Hint
在 MySQL 8.0 中，有两种武器：
1. 强制指定索引（Index Hint）：
mysql> SELECT * FROM user FORCE INDEX (idx_age)
-> WHERE age > 30
-> ORDER BY created_at DESC
-> LIMIT 10;
+-------+-----------+------+-----------------------+---------------------+
| id | name | age | email | created_at |
+-------+-----------+------+-----------------------+---------------------+
| 68838 | User68838 | 44 | user68838@example.com | 2026-03-04 15:28:46 |
| 60672 | User60672 | 35 | user60672@example.com | 2026-03-04 15:28:46 |
| 43711 | User43711 | 66 | user43711@example.com | 2026-03-04 15:28:46 |
| 58074 | User58074 | 41 | user58074@example.com | 2026-03-04 15:28:46 |
| 81978 | User81978 | 45 | user81978@example.com | 2026-03-04 15:28:46 |
| 58996 | User58996 | 46 | user58996@example.com | 2026-03-04 15:28:46 |
| 17704 | User17704 | 35 | user17704@example.com | 2026-03-04 15:28:45 |
| 28536 | User28536 | 36 | user28536@example.com | 2026-03-04 15:28:45 |
| 17836 | User17836 | 31 | user17836@example.com | 2026-03-04 15:28:45 |
| 19214 | User19214 | 47 | user19214@example.com | 2026-03-04 15:28:45 |
+-------+-----------+------+-----------------------+---------------------+
10 rows in set (0.20 sec)
2. 调节优化器开关（如果上述方法无效）：
-- 对于特定会话，关闭优先使用排序索引的优化策略
mysql> SET SESSION optimizer_switch = 'prefer_ordering_index=OFF';
Query OK, 0 rows affected (0.00 sec)
-- 再次执行原查询
mysql> SELECT * FROM user
-> WHERE age > 30
-> ORDER BY created_at DESC
-> LIMIT 10;
+-------+-----------+------+-----------------------+---------------------+
| id | name | age | email | created_at |
+-------+-----------+------+-----------------------+---------------------+
| 81978 | User81978 | 45 | user81978@example.com | 2026-03-04 15:28:46 |
| 68838 | User68838 | 44 | user68838@example.com | 2026-03-04 15:28:46 |
| 60672 | User60672 | 35 | user60672@example.com | 2026-03-04 15:28:46 |
| 58996 | User58996 | 46 | user58996@example.com | 2026-03-04 15:28:46 |
| 58074 | User58074 | 41 | user58074@example.com | 2026-03-04 15:28:46 |
| 43711 | User43711 | 66 | user43711@example.com | 2026-03-04 15:28:46 |
| 39372 | User39372 | 53 | user39372@example.com | 2026-03-04 15:28:45 |
| 33616 | User33616 | 63 | user33616@example.com | 2026-03-04 15:28:45 |
| 31596 | User31596 | 66 | user31596@example.com | 2026-03-04 15:28:45 |
| 28536 | User28536 | 36 | user28536@example.com | 2026-03-04 15:28:45 |
+-------+-----------+------+-----------------------+---------------------+
10 rows in set (0.00 sec)
技术解析：prefer_ordering_index 是 MySQL 5.7 引入、8.0 延续的一个优化器选项，告诉优化器不要过度迷信“通过排序索引直接拿数据”的方式。通过 EXPLAIN 对比前后 rows 扫描数的变化，会看到立竿见影的效果。
4.4 深入理解优化器决策：使用 Optimizer Trace
如果想知道优化器为什么选择某个索引，可以开启优化器追踪（optimizer trace），它会详细记录每个可能的执行计划及其成本计算过程。
-- 开启追踪
mysql> SET SESSION optimizer_trace='enabled=on';
Query OK, 0 rows affected (0.00 sec)
-- 执行查询（注意：不要加 FORCE INDEX，让优化器自由选择）
mysql> SELECT * FROM user WHERE age > 30 ORDER BY created_at DESC LIMIT 10;
+-------+-----------+------+-----------------------+---------------------+
| id | name | age | email | created_at |
+-------+-----------+------+-----------------------+---------------------+
| 81978 | User81978 | 45 | user81978@example.com | 2026-03-04 15:28:46 |
| 68838 | User68838 | 44 | user68838@example.com | 2026-03-04 15:28:46 |
| 60672 | User60672 | 35 | user60672@example.com | 2026-03-04 15:28:46 |
| 58996 | User58996 | 46 | user58996@example.com | 2026-03-04 15:28:46 |
| 58074 | User58074 | 41 | user58074@example.com | 2026-03-04 15:28:46 |
| 43711 | User43711 | 66 | user43711@example.com | 2026-03-04 15:28:46 |
| 39372 | User39372 | 53 | user39372@example.com | 2026-03-04 15:28:45 |
| 33616 | User33616 | 63 | user33616@example.com | 2026-03-04 15:28:45 |
| 31596 | User31596 | 66 | user31596@example.com | 2026-03-04 15:28:45 |
| 28536 | User28536 | 36 | user28536@example.com | 2026-03-04 15:28:45 |
+-------+-----------+------+-----------------------+---------------------+
10 rows in set (0.00 sec)
-- 查看追踪结果（输出很长，建议用 \G 格式化）
mysql> SELECT * FROM information_schema.optimizer_trace\G
*************************** 1. row ***************************
QUERY: SELECT * FROM user WHERE age > 30 ORDER BY created_at DESC LIMIT 10
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `user`.`id` AS `id`,`user`.`name` AS `name`,`user`.`age` AS `age`,`user`.`email` AS `email`,`user`.`created_at` AS `created_at` from `user` where (`user`.`age` > 30) order by `user`.`created_at` desc limit 10"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`user`.`age` > 30)",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`user`.`age` > 30)"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`user`.`age` > 30)"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`user`.`age` > 30)"
}
]
}
},
{
"substitute_generated_columns": {
}
},
{
"table_dependencies": [
{
"table": "`user`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
]
},
{
"rows_estimation": [
{
"table": "`user`",
"range_analysis": {
"table_scan": {
"rows": 99066,
"cost": 10029
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_user_age",
"usable": true,
"key_parts": [
"age",
"id"
]
},
{
"index": "idx_created",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_age",
"usable": true,
"key_parts": [
"age",
"id"
]
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"skip_scan_range": {
"potential_skip_scan_indexes": [
{
"index": "idx_user_age",
"usable": false,
"cause": "query_references_nonkey_column"
},
{
"index": "idx_age",
"usable": false,
"cause": "query_references_nonkey_column"
}
]
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_user_age",
"ranges": [
"30 < age"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"in_memory": 0.544444,
"rows": 49533,
"cost": 17336.8,
"chosen": false,
"cause": "cost"
},
{
"index": "idx_age",
"ranges": [
"30 < age"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"in_memory": 0,
"rows": 49533,
"cost": 17336.8,
"chosen": false,
"cause": "cost"
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
}
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`user`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 99066,
"filtering_effect": [
],
"final_filtering_effect": 0.5,
"access_type": "scan",
"resulting_rows": 49533,
"cost": 10026.9,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 49533,
"cost_for_plan": 10026.9,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`user`.`age` > 30)",
"attached_conditions_computation": [
{
"table": "`user`",
"rechecking_index_usage": {
"recheck_reason": "low_limit",
"limit": 10,
"row_estimate": 49533
}
}
],
"attached_conditions_summary": [
{
"table": "`user`",
"attached": "(`user`.`age` > 30)"
}
]
}
},
{
"optimizing_distinct_group_by_order_by": {
"simplifying_order_by": {
"original_clause": "`user`.`created_at` desc",
"items": [
{
"item": "`user`.`created_at`"
}
],
"resulting_clause_is_simple": true,
"resulting_clause": "`user`.`created_at` desc"
}
}
},
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"steps": [
],
"index_order_summary": {
"table": "`user`",
"index_provides_order": true,
"order_direction": "desc",
"index": "idx_created",
"plan_changed": true,
"access_type": "index"
}
}
},
{
"finalizing_table_conditions": [
{
"table": "`user`",
"original_table_condition": "(`user`.`age` > 30)",
"final_table_condition ": "(`user`.`age` > 30)"
}
]
},
{
"refine_plan": [
{
"table": "`user`"
}
]
},
{
"considering_tmp_tables": [
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
]
}
}
]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)
-- 关闭追踪
mysql> SET SESSION optimizer_trace='enabled=off';
Query OK, 0 rows affected (0.00 sec)
4.5 优化器决策深度解读
4.5.1 优化器如何评估索引选项？
在 rows_estimation 阶段，优化器分析了两个可用索引：
idx_user_age（以及重复的idx_age，实际是同一个索引）：对于条件age > 30，估算扫描行数rows=49533，成本cost=17336.8。- 全表扫描的成本：
cost=10026.9，扫描行数99066，但经过age > 30过滤后，返回行数49533。
注意，此时优化器并没有考虑 idx_created 索引，因为该索引的列 created_at 不参与 WHERE 条件，所以不在范围分析的“potential_range_indexes”中。
4.5.2 转折点：ORDER BY 优化介入
在 reconsidering_access_paths_for_index_ordering 步骤中，优化器开始考虑如何利用索引避免排序：
"index_order_summary": {
"table": "`user`",
"index_provides_order": true,
"order_direction": "desc",
"index": "idx_created",
"plan_changed": true,
"access_type": "index"
}
这里的关键是 "plan_changed": true，意味着优化器改变了之前的方案。它发现 idx_created 索引天然按照 created_at 排序（逆序），如果采用该索引，可以直接按顺序扫描并应用 age > 30 过滤，直到找到 10 条记录就停止。这种策略的成本估算极低（因为 LIMIT 10 大大减少了需要扫描的行数），因此最终选择了这个方案。
总结
- 执行计划深度解读：使用
EXPLAIN FORMAT=TREE和EXPLAIN ANALYZE获取真实执行信息，对比预估与实际行数，发现优化器统计信息的偏差。 - Performance Schema 故障排查：通过启用等待事件消费者，定位锁等待和系统瓶颈，利用
data_lock_waits和data_locks找出阻塞源头。 - 系统与参数调优：调整 I/O 调度器、NUMA 参数、缓冲池大小及双写缓冲区批次大小，充分发挥硬件性能。
- 索引误选案例：理解优化器在“ORDER BY LIMIT”场景下的决策逻辑，并通过
optimizer_trace验证。学会使用索引提示或优化器开关干预错误的选择。
作者注：
—— 本文所有操作及测试均基于 MySQL 8.0.42 社区版，在 CentOS 7 + Docker 环境下完成，核心围绕 优化器索引选择行为、EXPLAIN 深度解读与 Performance Schema 故障定位实战 展开。请注意，MySQL 8.0 版本持续迭代，部分特性与功能表现可能随版本更新发生变化，请以 MySQL 官方文档 最新内容为准。
—— 以上仅为个人实战经验总结，不代表行业普遍观点。所有操作均需在具备足够权限的环境下执行，涉及生产环境时请提前做好数据备份与灰度测试，避免影响业务稳定性。文中案例与实操思路仅供参考，若与实际项目场景巧合，纯属无意。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
