MySQL 8.0.42 故障瓶颈：锁等待、日志满与索引失效排查与优化

原创 shunwahⓂ️ 2025-09-23

694

个人简介
作者： 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 的真实测试环境，将通过“场景复现 → 问题定位 → 深度修复”的完整链路，手把手带您穿透表象，直击性能故障的核心。我们将不仅提供立即可用的排查命令，更会深入剖析每一类问题的底层逻辑与优化哲学，助您构建起从紧急止损到长效优化的系统性解决能力。无论您是遭遇性能瓶颈的运维工程师，还是希望防患于未然的开发者，本文都将是一份值得珍藏的实战指南。

公众号首图制作.png

一、场景测试：复现三类 InnoDB 性能故障

为精准定位故障根源，我们先通过模拟业务场景复现问题，所有命令均基于 MySQL 8.0.42 环境，需提前准备测试表与数据。

1.1 登录数据库

[root@worker3 pingkai]# mysql -uroot -h127.0.0.1 -p
Enter 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 DO
mysql> 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]# ls
auto.cnf           #ib_16384_1.dblwr  lock_test   mysql.sock.lock     sys
ca-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 t
LEFT JOIN information_schema.processlist p 
  ON t.trx_mysql_thread_id = p.id
WHERE TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) > 300
ORDER 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官方文档最新内容为准。

——以上仅为个人思考与建议，不代表行业普适观点。以上所有操作均需在具备足够权限的环境下执行，涉及生产环境时请提前做好备份与测试。文中案例与思路仅供参考，若与实际情况巧合，纯属无意。期待与各位从业者共同探讨更多可能！

mysql mysql创建数据库 mysql执行计划事务隔离级别墨力计划

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者