个人简介
作者： ShunWah
公众号： “顺华星辰运维栈”主理人。持有认证： OceanBase OBCA/OBCP、MySQL OCP、OpenGauss、崖山 DBCA、亚信 AntDBCA、翰高 HDCA、GBase 8a | 8c | 8s、Galaxybase GBCA、Neo4j Graph Data Science Certification、NebulaGraph NGCI & NGCP、东方通 TongTech TCPE 等多项权威认证。
获奖经历： 在OceanBase&墨天轮征文大赛、OpenGauss、TiDB、YashanDB、Kingbase、KWDB 征文等赛事中多次斩获一、二、三等奖，原创技术文章常年被墨天轮、CSDN、ITPUB 等平台首页推荐。
- 公众号_ID：顺华星辰运维栈
- CSDN_ID： shunwahma
- 墨天轮_ID：shunwah
- ITPUB_ID： shunwah
- IFClub_ID：shunwah
前言
在高并发业务场景的持续压力下，MySQL 8.0 的 InnoDB 存储引擎纵然以稳健著称，也难免遭遇性能“暗礁”——突如其来的查询卡顿、难以捉摸的事务阻塞、或是磁盘空间的无声告急。这些现象背后，往往是锁竞争、日志配置与索引设计这三类核心问题在作祟。面对故障，许多开发者容易陷入“重启”或盲目调整配置的误区，而非从原理层面精准定位根源。
本文基于 MySQL 8.0.42 的真实测试环境，将通过“场景复现 → 问题定位 → 深度修复”的完整链路，手把手带您穿透表象，直击性能故障的核心。我们将不仅提供立即可用的排查命令，更会深入剖析每一类问题的底层逻辑与优化哲学，助您构建起从紧急止损到长效优化的系统性解决能力。无论您是遭遇性能瓶颈的运维工程师，还是希望防患于未然的开发者，本文都将是一份值得珍藏的实战指南。
一、场景测试：复现三类 InnoDB 性能故障
为精准定位故障根源，我们先通过模拟业务场景复现问题，所有命令均基于 MySQL 8.0.42 环境，需提前准备测试表与数据。
1.1 登录数据库
[root@worker3 pingkai]# mysql -uroot -h127.0.0.1 -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 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>
1.2 创建并选择测试数据库
mysql> CREATE DATABASE IF NOT EXISTS lock_test; Query OK, 1 row affected (0.02 sec) mysql> USE lock_test; Database changed mysql>
1.3 锁等待与死锁场景
前置准备：创建订单表并插入测试数据
1.3.1 创建订单表（status 字段初始无索引）
mysql> CREATE TABLE order_info ( -> id INT PRIMARY KEY AUTO_INCREMENT, -> order_no VARCHAR(32) NOT NULL, -> status TINYINT NOT NULL COMMENT '0-待支付，1-已支付，2-已取消', -> create_time DATETIME DEFAULT CURRENT_TIMESTAMP -> ); Query OK, 0 rows affected (0.08 sec) mysql>
1.3.2 插入测试数据
mysql> DELIMITER //CREATE PROCEDURE insert_order_data()BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 100000 DOmysql> CREATE PROCEDURE insert_order_data() INSERT INTO order_info (order_no, status)
VALUES (CONCAT('ORD', LPAD(i, 8, '0')), FLOOR(RAND() * 3));
-> BEGIN
-> DECLARE i INT DEFAULT 1;
-> WHILE i <= 100000 DO
-> INSERT INTO order_info (order_no, status)
-> VALUES (CONCAT('ORD', LPAD(i, 8, '0')), FLOOR(RAND() * 3));
-> SET i = i + 1;
-> END WHILE;
-> END //Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
mysql>
1.3.3 执行存储过程插入数据
mysql> CALL insert_order_data(); Query OK, 1 row affected (1 min 17.10 sec) mysql>
1.3.4 验证数据插入成功
mysql> SELECT * FROM order_info LIMIT 10; +----+-------------+--------+---------------------+| id | order_no | status | create_time | +----+-------------+--------+---------------------+| 1 | ORD00000001 | 1 | 2025-09-21 19:36:18 | | 2 | ORD00000002 | 0 | 2025-09-21 19:36:18 | | 3 | ORD00000003 | 0 | 2025-09-21 19:36:18 | | 4 | ORD00000004 | 1 | 2025-09-21 19:36:18 | | 5 | ORD00000005 | 1 | 2025-09-21 19:36:18 | | 6 | ORD00000006 | 1 | 2025-09-21 19:36:18 | | 7 | ORD00000007 | 1 | 2025-09-21 19:36:18 | | 8 | ORD00000008 | 1 | 2025-09-21 19:36:18 | | 9 | ORD00000009 | 2 | 2025-09-21 19:36:18 | | 10 | ORD00000010 | 0 | 2025-09-21 19:36:18 | +----+-------------+--------+---------------------+10 rows in set (0.01 sec) mysql>
1.3.5 复现行锁等待场景
会话1（Session A）：
mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE order_info SET status = 1 WHERE id = 1; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql>
会话2（Session B）：
mysql> UPDATE order_info SET status = 2 WHERE id = 1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql>
1.3.6 查看锁等待情况
打开第三个会话查看锁状态：
mysql> SHOW ENGINE INNODB STATUS; ===================================== 2025-09-21 19:45:25 140366518916864 INNODB MONITOR OUTPUT =====================================-- 输出内容过长，此处省略详细输出 --
mysql> SELECT * FROM performance_schema.data_locks; +--------+-----------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +--------+-----------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+| INNODB | 140366919789784:400098:1129:140366846152560 | 2635433 | 50 | 500048 | lock_test | order_info | NULL | NULL | NULL | 140366846152560 | TABLE | IX | GRANTED | NULL | | INNODB | 140366919789784:400098:67:5:2:140366846149504 | 2635433 | 50 | 500048 | lock_test | order_info | NULL | NULL | PRIMARY | 140366846149504 | RECORD | X,REC_NOT_GAP | GRANTED | 1 | +--------+-----------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+2 rows in set (0.00 sec) mysql>
1.3.7 注意事项
- 确保MySQL使用的是InnoDB引擎（默认）
- 确保
id = 1的记录确实存在
mysql> SELECT * FROM order_info WHERE id = 1; +----+-------------+--------+---------------------+| id | order_no | status | create_time | +----+-------------+--------+---------------------+| 1 | ORD00000001 | 1 | 2025-09-21 19:36:18 | +----+-------------+--------+---------------------+1 row in set (0.00 sec) mysql>
- 如果会话2没有出现等待，可能是因为：
- 会话1的事务已提交或回滚
- MySQL隔离级别设置不同
- 使用了不同的MySQL版本（某些版本可能优化了锁机制）
1.3.8 解决锁等待
要解决这个锁等待，只需在会话1中提交或回滚事务：
-- 在会话1中执行COMMIT; -- 或 ROLLBACK;
1.4 redo/undo 日志满场景
前置准备：创建大表并配置较小的日志容量
1.4.1 创建临时数据表
mysql> CREATE TABLE temp_data ( -> name VARCHAR(64) NOT NULL, -> age INT NOT NULL -> ); Query OK, 0 rows affected (0.09 sec) mysql>
1.4.2 插入 100 万条测试数据
mysql> DELIMITER //
mysql> CREATE PROCEDURE insert_temp_data()
-> BEGIN
-> DECLARE i INT DEFAULT 1;
-> WHILE i <= 1000000 DO
-> INSERT INTO temp_data (name, age) VALUES (CONCAT('user', i), i%100);
-> SET i = i + 1;
-> END WHILE;
-> END //Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
mysql> CALL insert_temp_data();
Query OK, 1 row affected (12 min 13.34 sec)
mysql>
1.4.3 临时将 redo 日志容量设为 1GB
mysql> SET GLOBAL innodb_redo_log_capacity = 1073741824; Query OK, 0 rows affected (0.06 sec) mysql>
1.4.4 复现步骤
- 执行批量插入，触发日志堆积
mysql> CREATE TABLE user_data LIKE temp_data; Query OK, 0 rows affected (0.06 sec) mysql> INSERT INTO user_data (name, age) SELECT name, age FROM temp_data; Query OK, 1000000 rows affected (4.07 sec) Records: 1000000 Duplicates: 0 Warnings: 0 mysql>
- 查看磁盘使用情况
[root@worker3 mysql]# pwd/data/pingkai/mysql [root@worker3 mysql]# lsauto.cnf #ib_16384_1.dblwr lock_test mysql.sock.lock sysca-key.pem ib_buffer_pool mysql performance_schema test_db ca.pem ibdata1 mysqld.log private_key.pem undo_001 client-cert.pem ibtmp1 mysqld.pid public_key.pem undo_002 client-key.pem #innodb_redo mysql.ibd server-cert.pem#ib_16384_0.dblwr #innodb_temp mysql.sock server-key.pem[root@worker3 mysql]# du -sh /data/pingkai/mysql/* | grep -E "ib_redo_log|ibdata1"12M /data/pingkai/mysql/ibdata1 [root@worker3 mysql]#
1.5 索引失效场景
前置准备：创建用户表并添加索引
1.5.1 创建用户表，phone 字段加索引
mysql> CREATE TABLE `user` ( -> id INT PRIMARY KEY AUTO_INCREMENT, -> phone VARCHAR(20) NOT NULL COMMENT '手机号（字符串类型）', -> nickname VARCHAR(64) NOT NULL -> ); Query OK, 0 rows affected (0.04 sec) mysql> CREATE INDEX idx_phone ON `user`(phone); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql>
1.5.2 插入 10 万条测试数据
mysql> DELIMITER //
mysql> CREATE PROCEDURE insert_user_data()
-> BEGIN
-> DECLARE i INT DEFAULT 1;
-> WHILE i <= 100000 DO
-> INSERT INTO `user` (phone, nickname) VALUES (CONCAT('138', LPAD(i, 8, '0')), CONCAT('nick', i));
-> SET i = i + 1;
-> END WHILE;
-> END //Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
mysql> CALL insert_user_data();
Query OK, 1 row affected (1 min 15.12 sec)
mysql>
1.5.3 复现步骤
- 执行含类型转换的查询（索引失效）
mysql> SELECT * FROM `user` WHERE phone = 13800000001; +----+-------------+----------+| id | phone | nickname | +----+-------------+----------+| 1 | 13800000001 | nick1 | +----+-------------+----------+1 row in set (0.05 sec) mysql>
- 执行含函数操作的查询（索引失效）
mysql> SELECT * FROM `user` WHERE SUBSTRING(phone, 1, 3) = '138';
- 执行正常查询（索引生效，对比耗时）
mysql> SELECT * FROM `user` WHERE phone = '13800000001'; +----+-------------+----------+| id | phone | nickname | +----+-------------+----------+| 1 | 13800000001 | nick1 | +----+-------------+----------+1 row in set (0.00 sec) mysql>
1.6 与锁问题的关联分析
1.6.1 行锁等待与undo日志
当事务修改存在行时，InnoDB通过undo日志实现MVCC（多版本并发控制）。若长事务持有行锁且未提交，其他事务更新同一行会触发锁等待。undo日志膨胀会加剧以下问题：
- 长事务占用undo空间：导致innodb_undo_log_truncate频繁触发，I/O压力上升。
- 间隙锁（Gap Lock）冲突：在REPEATABLE READ隔离级别下，更新操作可能锁定索引间隙，引发死锁。
mysql> SHOW VARIABLES LIKE 'innodb_undo%'; ERROR 4031 (HY000): The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior. No connection. Trying to reconnect... Connection id: 13 Current database: lock_test +--------------------------+-------+| Variable_name | Value | +--------------------------+-------+| innodb_undo_directory | ./ | | innodb_undo_log_encrypt | OFF | | innodb_undo_log_truncate | ON | | innodb_undo_tablespaces | 2 | +--------------------------+-------+4 rows in set (0.07 sec) mysql>
1.6.2 死锁诊断建议
通过SHOW ENGINE INNODB STATUS查看LOCK WAIT日志，定位锁竞争源头。例如：使用innodb_metrics监控锁等待：
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS -> WHERE NAME LIKE 'lock%';-- 输出内容过长，此处省略详细输出 --
二、问题定位：精准捕捉故障核心原因（附排查命令）
当MySQL数据库出现性能故障时，盲目调整配置往往会适得其反。本节将结合实际测试场景，通过"MySQL内置工具+系统命令"的组合方式，精准定位三类InnoDB性能故障的核心原因。
2.1 锁等待与死锁：从"锁日志"找冲突点
锁等待和死锁问题通常表现为事务卡顿、查询超时，需要通过专业工具追踪锁的持有和等待关系。
2.1.1 查看当前锁等待情况
mysql> SHOW ENGINE INNODB STATUS\G-- 输出内容过长，此处省略详细输出 --
2.1.2 使用性能模式查看数据锁
mysql> SELECT * FROM performance_schema.data_locks;-- 输出内容过长，此处省略详细输出 --
2.1.3 分析死锁完整日志
在SHOW ENGINE INNODB STATUS输出结果中查找"LATEST DETECTED DEADLOCK"部分。
2.2 redo/undo 日志满：从"配置+事务"双维度排查
日志空间满通常表现为写入操作卡顿或失败，需要从配置参数、事务状态和磁盘占用三个维度排查。
2.2.1 查看日志配置与使用状态
mysql> SHOW VARIABLES LIKE 'innodb_redo_log_capacity'; +--------------------------+------------+| Variable_name | Value | +--------------------------+------------+| innodb_redo_log_capacity | 1073741824 | +--------------------------+------------+1 row in set (0.00 sec) mysql> SHOW VARIABLES LIKE 'innodb_log_buffer_size'; +------------------------+----------+| Variable_name | Value | +------------------------+----------+| innodb_log_buffer_size | 16777216 | +------------------------+----------+1 row in set (0.00 sec) mysql> SHOW VARIABLES LIKE 'innodb_undo%'; +--------------------------+-------+| Variable_name | Value | +--------------------------+-------+| innodb_undo_directory | ./ | | innodb_undo_log_encrypt | OFF | | innodb_undo_log_truncate | ON | | innodb_undo_tablespaces | 2 | +--------------------------+-------+4 rows in set (0.00 sec)
2.2.2 查看InnoDB日志状态详情
mysql> SHOW ENGINE INNODB STATUS\G-- 输出内容过长，此处省略详细输出 --
2.2.3 检查长期运行的闲置事务
SELECT t.trx_id AS '事务ID', t.trx_started AS '事务开始时间', TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) AS '事务持续时间(秒)', p.id AS '会话ID', p.user AS '用户', p.host AS '客户端地址', p.info AS '执行的SQL'FROM information_schema.innodb_trx tLEFT JOIN information_schema.processlist p ON t.trx_mysql_thread_id = p.idWHERE TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) > 300ORDER BY TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) DESC;
2.2.4 查看磁盘实际占用
# 查看MySQL数据目录总占用df -h /data/pingkai/mysql/# 查看redo日志文件大小ls -lh /data/pingkai/mysql/#innodb_redo/# 查看undo表空间文件大小ls -lh /data/pingkai/mysql/undo_*# 查看ibdata1文件大小du -sh /data/pingkai/mysql/ibdata1
2.3 索引失效：用"执行计划"验证索引使用情况
索引失效会导致查询性能急剧下降，需要通过执行计划分析索引使用情况。
2.3.1 分析含类型转换的SQL（索引失效）
mysql> EXPLAIN ANALYZE -> SELECT * FROM `user` WHERE phone = 13800000001; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| -> Filter: (`user`.phone = 13800000001) (cost=10103 rows=10015) (actual time=0.445..62.6 rows=1 loops=1) -> Table scan on user (cost=10103 rows=100147) (actual time=0.437..48.3 rows=100000 loops=1) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set, 2 warnings (0.10 sec)
2.3.2 分析含函数操作的SQL（索引失效）
mysql> EXPLAIN ANALYZE -> SELECT * FROM `user` WHERE SUBSTRING(phone, 1, 3) = '138'; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| EXPLAIN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| -> Filter: (substr(`user`.phone,1,3) = '138') (cost=10103 rows=100147) (actual time=0.165..41.1 rows=100000 loops=1) -> Table scan on user (cost=10103 rows=100147) (actual time=0.16..27.7 rows=100000 loops=1) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.05 sec)
2.3.3 分析正常SQL（索引生效）
mysql> EXPLAIN ANALYZE -> SELECT * FROM `user` WHERE phone = '13800000001'; +------------------------------------------------------------------------------------------------------------------------------+| EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------+| -> Index lookup on user using idx_phone (phone='13800000001') (cost=0.35 rows=1) (actual time=0.04..0.0423 rows=1 loops=1) | +------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
执行计划关键字段解读：
| 字段 | 索引失效特征 | 索引生效特征 |
|---|---|---|
type |
ALL（全表扫描） |
ref或
range（索引扫描） |
key |
NULL（未使用索引） |
idx_phone（使用目标索引） |
rows |
接近表总记录数（如100000） | 极小值（如1） |
filtered |
100.00（需过滤所有行） | 10.00以下（精准过滤） |
execution time |
较长（如50ms以上） | 较短（如1ms以下） |
三、问题修复：从"临时止损"到"长效优化"（附修复命令）
针对不同故障，需采取"紧急修复+根源优化"的两步策略，既解决当前问题，又避免后续复发。
3.1 索引优化实战：从清理冗余到规范编写的全流程方案
索引是提升MySQL查询性能的核心工具，但不合理的索引设计（如冗余索引、无效索引）会增加写入开销，甚至引发性能衰退。以下是从"冗余索引清理""索引失效预防"到"定期性能审计"的完整优化方案。
3.1.1 清理冗余/未使用索引，降低写入成本
查看未使用的索引：
mysql> SELECT COUNT(*) -> FROM performance_schema.table_io_waits_summary_by_index_usage -> WHERE INDEX_NAME IS NOT NULL; +----------+| COUNT(*) | +----------+| 147 | +----------+1 row in set (0.00 sec)
查询未使用的索引：
mysql> SELECT
-> t.OBJECT_SCHEMA AS '数据库名',
-> t.OBJECT_NAME AS '表名',
-> t.INDEX_NAME AS '未使用的索引',
-> s.INDEX_TYPE AS '索引类型',
-> GROUP_CONCAT(s.COLUMN_NAME ORDER BY s.SEQ_IN_INDEX) AS '索引字段',
-> tb.TABLE_ROWS AS '表数据量'
-> FROM performance_schema.table_io_waits_summary_by_index_usage t
-> LEFT JOIN information_schema.statistics s
-> ON t.OBJECT_SCHEMA = s.TABLE_SCHEMA
-> AND t.OBJECT_NAME = s.TABLE_NAME
-> AND t.INDEX_NAME = s.INDEX_NAME
-> LEFT JOIN information_schema.tables tb
-> ON t.OBJECT_SCHEMA = tb.TABLE_SCHEMA
-> AND t.OBJECT_NAME = tb.TABLE_NAME
-> WHERE
-> t.INDEX_NAME IS NOT NULL
-> AND t.OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema', 'sys', 'information_schema')
-> AND t.COUNT_STAR = 0
-> AND s.INDEX_NAME IS NOT NULL
-> GROUP BY t.OBJECT_SCHEMA, t.OBJECT_NAME, t.INDEX_NAME, s.INDEX_TYPE, tb.TABLE_ROWS
-> ORDER BY tb.TABLE_ROWS DESC;
+--------------+--------+--------------------+--------------+--------------+--------------+| 数据库名 | 表名 | 未使用的索引 | 索引类型 | 索引字段 | 表数据量 |
+--------------+--------+--------------------+--------------+--------------+--------------+| lock_test | user | PRIMARY | BTREE | id | 100147 |
+--------------+--------+--------------------+--------------+--------------+--------------+1 row in set (0.00 sec)
验证主键使用情况：
mysql> SELECT -> OBJECT_SCHEMA, -> OBJECT_NAME, -> INDEX_NAME, -> COUNT_FETCH AS '通过索引获取行数', -> COUNT_STAR AS '显式查询次数' -> FROM performance_schema.table_io_waits_summary_by_index_usage -> WHERE -> OBJECT_SCHEMA = 'lock_test' -> AND OBJECT_NAME = 'user' -> AND INDEX_NAME = 'PRIMARY'; +---------------+-------------+------------+--------------------------+--------------------+| OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | 通过索引获取行数 | 显式查询次数 | +---------------+-------------+------------+--------------------------+--------------------+| lock_test | user | PRIMARY | 0 | 0 | +---------------+-------------+------------+--------------------------+--------------------+1 row in set (0.00 sec)
3.1.2 制定SQL编写规范，从源头避免索引失效
禁止对索引字段做函数操作：
| 错误写法（索引失效） | 正确写法（索引生效） | 优化说明 |
|---|---|---|
SELECT * FROM user WHERE SUBSTRING(phone,1,3) = '138'; |
1. 创建函数索引：
CREATE INDEX idx_phone_prefix ON user((SUBSTRING(phone,1,3)));
2. 执行查询： SELECT * FROM user WHERE SUBSTRING(phone,1,3) = '138'; |
函数索引需在字段外包裹括号，仅支持MySQL 8.0+ |
禁止索引字段与常量的类型转换：
| 错误写法（索引失效） | 正确写法（索引生效） | 原理说明 |
|---|---|---|
SELECT * FROM user WHERE phone = 13800000001;（phone为VARCHAR） |
SELECT * FROM user WHERE phone = '13800000001'; |
数字13800000001会被转换为字符串，但索引字段phone被当作数字处理，导致索引无法匹配 |
3.1.3 定期审计SQL性能，建立长效监控
开启慢查询日志：
mysql> SET GLOBAL slow_query_log = ON; Query OK, 0 rows affected (0.01 sec) mysql> SET GLOBAL long_query_time = 1; Query OK, 0 rows affected (0.00 sec) mysql> SHOW VARIABLES LIKE 'slow_query_log_file'; +---------------------+--------------------------------------+| Variable_name | Value | +---------------------+--------------------------------------+| slow_query_log_file | /data/pingkai/mysql/worker3-slow.log | +---------------------+--------------------------------------+1 row in set (0.01 sec)
分析查询计划：
mysql> EXPLAIN ANALYZE -> SELECT * FROM order_info WHERE status = 1; +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| EXPLAIN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| -> Filter: (order_info.`status` = 1) (cost=9822 rows=9750) (actual time=0.529..66.5 rows=33620 loops=1) -> Table scan on order_info (cost=9822 rows=97502) (actual time=0.526..55.4 rows=100000 loops=1) | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.08 sec)
优化索引设计：
mysql> ALTER TABLE order_info ADD INDEX idx_status_create_time (status, create_time); Query OK, 0 rows affected (0.41 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SELECT status, create_time FROM order_info WHERE status = 1; +--------+---------------------+33620 rows in set (0.01 sec)
强制索引（谨慎使用）：
mysql> SELECT * FROM order_info USE INDEX (idx_status) WHERE status = 1; +-------+-------------+--------+---------------------+33620 rows in set (0.07 sec)
3.2 优化决策：确认无业务依赖→安全删除（推荐）
若排查后确认
idx_phone无任何业务查询依赖，删除可减少写入开销（
INSERT/UPDATE/DELETE无需维护该索引），操作步骤如下：
3.2.1 再次备份索引（防误删回滚）
– 备份idx_phone索引创建语句（已验证可用，需保存到本地文档）
mysql> SELECT CONCAT( 'CREATE INDEX idx_phone ON lock_test.user (phone);' -> CONCAT( -> 'CREATE INDEX idx_phone ON lock_test.user (phone);' -> ) AS '索引重建语句'; +---------------------------------------------------+ | 索引重建语句 | +---------------------------------------------------+ | CREATE INDEX idx_phone ON lock_test.user (phone); | +---------------------------------------------------+ 1 row in set (0.00 sec) mysql>
**索引重建语句 **
mysql> CREATE INDEX idx_phone ON lock_test.user (phone); Query OK, 0 rows affected (0.49 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql>
3.2.2 低峰期执行删除
选择业务流量最低的时段（如凌晨2-4点）执行，避免元数据锁（MDL）阻塞写入操作：
– 删除未使用的idx_phone索引
mysql> DROP INDEX idx_phone ON lock_test.user; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql>
3.2.3 删除后验证
– 1. 确认索引已删除
mysql> SELECT INDEX_NAME FROM information_schema.statistics -> WHERE TABLE_SCHEMA = 'lock_test' AND TABLE_NAME = 'user' AND INDEX_NAME = 'idx_phone'; Empty set (0.00 sec) mysql>
– 返回Empty set，说明删除成功
– 2. 验证写入性能提升（可选）
– 对比删除前后批量插入耗时：
– 删除后写入耗时应缩短（因少维护一个索引）
mysql> INSERT INTO lock_test.user (phone, nickname)
-> VALUES ('13811111111', 'nick111'), ('13822222222', 'nick222');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
四、总结
通过本文的深度演练，我们可以清晰地看到，MySQL InnoDB 的性能故障排查绝非“头痛医头、脚痛医脚”的简单操作，而是一个需要系统性思维和精准工具链支撑的严谨过程。
在锁等待问题中，我们认识到长事务是万恶之源，学会了利用 performance_schema.data_locks 和 SHOW ENGINE INNODB STATUS 透视锁的争夺，并通过事务拆分、索引优化来从根本上减少竞争。
在日志满问题中，我们理解了 Redo/Undo 日志的协同工作机制，学会了从配置参数、活跃事务、磁盘空间三个维度进行立体排查，并通过合理设置日志容量、监控长事务来保障日志系统的健康运转。
在索引失效问题中，我们领教了隐式类型转换和函数操作对索引的“致命伤害”，掌握了使用 EXPLAIN ANALYZE 精准验证索引使用情况的技巧，并通过建立 SQL 编写规范、定期清理冗余索引来确保索引始终高效。
归根结底，优秀的数据库性能源于对细节的掌控和对原理的尊重。本文提供的命令与脚本是您工具箱中的利器，但更重要的是其中所体现的排查方法论和优化思想。建议您将文中的案例与自身业务场景结合，形成常态化的监控与审计机制，从而让 MySQL 在高压力的生产环境中始终保持敏捷与稳定。
作者注：
——本文所有操作及测试均基于 RPM 模式部署 MySQL 8.0.42 完成。请注意，MySQL 8.0.42 版本处于持续迭代中，部分语法或功能可能随更新发生变化，请以 Oracle mysql官方文档最新内容为准。——以上仅为个人思考与建议，不代表行业普适观点。以上所有操作均需在具备足够权限的环境下执行，涉及生产环境时请提前做好备份与测试。文中案例与思路仅供参考，若与实际情况巧合，纯属无意。期待与各位从业者共同探讨更多可能！