MySQL生产查询慢，该如何排查

✅ 一、首先判断：是不是慢查询？怎么判断？

1. 开启 MySQL 慢查询日志

永久生效需要编辑my.cnf配置以下内容

[mysqld]
# 开启慢查询日志开关 (1=开启, 0=关闭)
slow_query_log = 1

# 慢查询日志文件路径 (建议指定一个空间充足的路径)
slow_query_log_file = /var/log/mysql/mysql-slow.log

# 定义慢查询的时间阈值 (单位:秒)，通常设为 1 或 2
long_query_time = 1

# (可选) 记录未使用索引的 SQL
# log_queries_not_using_indexes = 1

查看是否开启慢日志

-- 是否开启慢日志
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

-- 临时打开慢日志（只对当前实例）
SET global slow_query_log = 1;
SET global long_query_time = 1;   -- 超过1秒算慢查询

✅ 二、实时监控是否有 SQL 正在执行很久

1）查看当前执行时间较长的 SQL

SHOW PROCESSLIST;

重点字段：

字段	含义
Time	SQL 已经执行的时间
State	waiting for lock / sending data / starting 等状态
Info	具体 SQL

如果看到大量 Locked/Waiting for table metadata lock → 说明锁竞争导致慢

✅ 三、通过慢日志 + pt-query-digest 分析热点慢 SQL（最常用）

安装 Percona 工具（Linux）

yum install percona-toolkit -y #centos系统如果提示没有可用安装包执行以下命令

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm -y
percona-release enable tools release
yum install percona-toolkit -y


sudo apt-get install percona-toolkit #ubuntu系统

分析慢日志

pt-query-digest /var/lib/mysql/mysql-slow.log > report.txt

你能看到：

• 哪条 SQL 最慢（Total Time）
• 哪条 SQL 执行次数最多
• 哪条 SQL 平均时间最长
• 每条 SQL 的耗时分布（95% 分位）

这是 生产环境排查慢 SQL 的第一利器。

✅ 四、对慢 SQL 使用 EXPLAIN 分析执行计划

EXPLAIN SELECT ...;

重点关注：

EXPLAIN 字段	问题含义
type = ALL	全表扫描（最常见慢原因）
rows 非常大	访问行太多
key = NULL	没有索引命中
extra = Using filesort / Using temporary	排序或临时表，性能差

最常见两个问题：

❌ 4.1 索引没走

原因可能是：

• 条件字段没有索引
• 联合索引的顺序不对（最左前缀原则）
• where 条件对索引列做了函数/运算
• 字段类型不一致引起隐式转换（最常见） 例如：

WHERE phone = 13800001111   -- phone 是 varchar，但常量是 int

这会导致 隐式转换 varchar → int，索引失效！

❌ 4.2 排序 / 分组导致慢

如果你看到 EXPLAIN：

Using filesort
Using temporary

说明 ORDER BY / GROUP BY 没有索引支持，引发磁盘排序。

解决：

• 添加对应索引
• 使用覆盖索引
• 尽量避免 %like% 开头模糊匹配

✅ 五、排查是否锁导致查询慢

执行：

SHOW ENGINE INNODB STATUS\G;

查看：

LATEST DETECTED DEADLOCK
TRANSACTIONS
LOCK WAIT

常见问题：

状态	含义
waiting for table metadata lock	一个事务在等待另一个事务提交 DDL
lock wait timeout exceeded	等待行级锁导致的慢
S lock / X lock	读写锁冲突

如何快速看锁：

#MySQL5.7
SELECT * FROM information_schema.innodb_locks; #用于显示事务持有/等待的锁
SELECT * FROM information_schema.innodb_lock_waits;#用于显示事务之间的等待关系
SELECT * FROM information_schema.innodb_trx; #用于显示当前事务
#锁等待查询SQL
SELECT
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_state AS waiting_state,
    r.trx_started AS waiting_start_time,
    r.trx_query AS waiting_query,

    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_state AS blocking_state,
    b.trx_started AS blocking_start_time,
    b.trx_query AS blocking_query,

    l.lock_mode AS lock_mode,
    l.lock_type AS lock_type,
    l.lock_table AS lock_table,
    l.lock_index AS lock_index,
    l.lock_data AS lock_data

FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_locks l 
      ON w.requested_lock_id = l.lock_id
JOIN information_schema.innodb_trx r 
      ON w.requesting_trx_id = r.trx_id
JOIN information_schema.innodb_trx b 
      ON w.blocking_trx_id = b.trx_id;



#MySQL8.0
SELECT * FROM performance_schema.data_locks; #查看当前锁（最常用）
SELECT * FROM performance_schema.data_lock_waits; #查看锁等待（谁在等谁）
SELECT * FROM performance_schema.events_transactions_current; #查看事务信息（类似 innodb_trx）
SELECT * FROM performance_schema.events_transactions_history; #查看历史事务（正在进行/已完成）
#锁等待查询SQL
SELECT
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,

    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query,

    dl.lock_type,
    dl.lock_mode,
    dl.object_schema,
    dl.object_name,
    dl.index_name,
    dl.lock_data

FROM performance_schema.data_lock_waits w

JOIN performance_schema.data_locks dl
      ON dl.engine_lock_id = w.requesting_engine_lock_id

JOIN information_schema.innodb_trx r
      ON r.trx_id = dl.engine_transaction_id

JOIN information_schema.innodb_trx b
      ON b.trx_id = (
            SELECT dl2.engine_transaction_id
            FROM performance_schema.data_locks dl2
            WHERE dl2.engine_lock_id = w.blocking_engine_lock_id
      );

MySQL 5.7 锁等待字段汇总（innodb_trx / innodb_locks / innodb_lock_waits）

🟦 1. 等待方（Waiting）—— 被阻塞的事务

• waiting_trx_id（来自 innodb_trx）：等待锁的事务 ID —— 当前被阻塞的事务
• waiting_thread（来自 innodb_trx）：被阻塞事务的线程 ID —— 可用于 KILL 解锁
• waiting_state（来自 innodb_trx）：被阻塞事务的状态 —— 常见为 LOCK WAIT / RUNNING
• waiting_start_time（来自 innodb_trx）：被阻塞事务开始时间 —— 判断是否长事务
• waiting_query（来自 innodb_trx）：被阻塞事务正在执行的 SQL —— 被锁卡住的 SQL

---

🟦 2. 阻塞方（Blocking）—— 持锁的事务

• blocking_trx_id（来自 innodb_trx）：阻塞别人的事务 ID —— 该事务持有锁
• blocking_thread（来自 innodb_trx）：阻塞事务的线程 ID —— 可 KILL <id> 解锁
• blocking_state（来自 innodb_trx）：阻塞事务状态 —— RUNNING / SLEEP 等
• blocking_start_time（来自 innodb_trx）：阻塞事务开始时间 —— 判断是否长事务占锁
• blocking_query（来自 innodb_trx）：阻塞者执行的 SQL —— 真正导致锁等待的语句

---

🟦 3. 锁详情（Lock Detail）—— 锁住了什么？

• lock_mode（来自 innodb_locks）：锁模式 —— X / S / IX / IS（排他/共享/意向锁）
• lock_type（来自 innodb_locks）：锁类型 —— RECORD（行锁）或 TABLE（表锁）
• lock_table（来自 innodb_locks）：被锁的表 —— 格式如 mall/order
• lock_index（来自 innodb_locks）：锁定的索引 —— PRIMARY 或二级索引
• lock_data（来自 innodb_locks）：被锁的数据内容 —— 主键 ID 或索引值

---

🟦 4. 锁等待关系（Waits）—— 谁在等谁？

• requesting_trx_id（来自 innodb_lock_waits）：等待锁的事务 ID —— 对应 waiting_trx_id
• requested_lock_id（来自 innodb_lock_waits）：等待的锁 ID —— 用于查锁详情
• blocking_trx_id（来自 innodb_lock_waits）：阻塞事务 ID —— 对应 blocking_trx_id
• blocking_lock_id（来自 innodb_lock_waits）：阻塞的锁 ID —— 指向阻塞锁的具体明细

MySQL 8.0 锁等待字段汇总（data_lock_waits / data_locks / innodb_trx）

🟦 1. 等待方（Waiting）—— 被阻塞的事务

• waiting_trx_id（来自 innodb_trx）：等待锁的事务 ID —— 当前被锁住无法继续的事务
• waiting_thread（来自 innodb_trx）：被阻塞事务的 MySQL 线程 ID —— 可用于 KILL <id>
• waiting_query（来自 innodb_trx）：被阻塞事务正在执行的 SQL —— 等待锁的 SQL

---

🟦 2. 阻塞方（Blocking）—— 持锁导致别人等待的事务

• blocking_trx_id（来自 innodb_trx）：占有锁的事务 ID —— 导致其他事务等待
• blocking_thread（来自 innodb_trx）：阻塞事务的线程 ID —— KILL 它可立即释放锁
• blocking_query（来自 innodb_trx）：阻塞者正在执行的 SQL —— 引发锁等待的 SQL

---

🟦 3. 锁详情（Lock Detail）—— 锁住了什么资源？

来自 performance_schema.data_locks

• lock_type：锁类型 —— RECORD（行锁）或 TABLE（表锁）
• lock_mode：锁模式 —— X（排他锁）/ S（共享锁）/ IX / IS 等
• object_schema：锁定对象的数据库名 —— 例如 mall
• object_name：锁定对象的表名 —— 例如 order
• index_name：锁作用的索引 —— PRIMARY 或二级索引
• lock_data：被锁定的具体数据 —— 通常为主键值或索引键值（例如 10001）

---

🟦 4. 锁等待关系（Wait Mapping）—— 谁在等谁？

来自 performance_schema.data_lock_waits（使用 engine_lock_id 关联）

• requesting_engine_lock_id：等待锁的内部锁 ID —— 对应 waiting 事务尝试获取的锁
• blocking_engine_lock_id：阻塞锁的内部锁 ID —— 对应 blocking 事务持有的锁

（在你的 SQL 中已被转换为 waiting_trx_xxx / blocking_trx_xxx）

查询锁源头

SELECT * FROM sys.innodb_lock_waits;
-- 或者
SELECT * FROM information_schema.innodb_trx WHERE trx_state = 'LOCK WAIT';

✅ 六、分析是否由于大表导致慢查询

查看表大小

SELECT table_name, data_length/1024/1024 AS data_MB
FROM information_schema.tables 
WHERE table_schema='你的库名'
ORDER BY data_length DESC;

如果表超过 百万级, 无索引查询肯定会慢。

解决方案：

• 加索引
• 分库分表
• 冷热数据归档

✅ 七、检查数据库是否资源不足（CPU / IO / Buffer pool）

查看 InnoDB buffer pool 命中率

SHOW ENGINE INNODB STATUS;
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';

低于 99% 就不正常，说明 MySQL 正在频繁从磁盘读数据。

解决：

• 调大 buffer pool
• 避免大表全表扫描

查看是否 IO 成瓶颈

iostat -x 1

如果 await > 10ms，说明 IO 慢。

✅ 八、排查 SQL 是否写法不合理（常见坑）

❌ 在 where 里对字段做运算/函数 — 导致索引失效

WHERE DATE(create_time) = '2024-01-01'

改为：

WHERE create_time >= '2024-01-01 00:00:00' 
  AND create_time <  '2024-01-02 00:00:00'

❌ 使用 %like% 导致全表扫描

WHERE name LIKE '%abc%'

换用：

• ES
• 前缀索引
• ngram 分词

❌ 过多 JOIN，缺少索引

✅ 九、整个慢 SQL 排查 SOP（生产流程）

（1）监控：SHOW PROCESSLIST 看是否被锁
（2）分析慢日志：pt-query-digest 找出最慢 SQL
（3）对具体 SQL 执行 EXPLAIN 分析执行计划
（4）检查索引：是否命中索引？是否最左前缀？有无隐式转换？
（5）检查是否 filesort / temporary
（6）检查服务器是否瓶颈：buffer pool / IO / CPU
（7）优化 SQL 或建立索引

✅ 十、面试标准答案

排查 MySQL 慢查询通常分为两类：  
单条 SQL 慢 和 数据库整体慢。

首先通过 `SHOW PROCESSLIST` 看是否存在 SQL 卡在锁等待、排序、sending data 等状态。  
然后检查 MySQL 的慢日志，并使用 `pt-query-digest` 统计热点慢 SQL。

对具体慢 SQL，用 `EXPLAIN` 和 MySQL8 的 `EXPLAIN ANALYZE` 分析执行计划，重点关注是否命中索引、rows 扫描行数、是否出现 filesort 或 temporary。

如果索引没命中，检查字段是否有隐式转换、函数操作、联合索引顺序是否合理，并优化 SQL 写法或补全索引。

如果 SQL 命中索引但仍然慢，检查是否有锁等待，通过 `innodb_lock_waits`、`innodb_trx`、`SHOW ENGINE INNODB STATUS` 排查行锁或 metadata lock。

若是整体变慢，查看系统层面的 CPU / IO / 网络压力，InnoDB buffer pool 命中率，连接数是否耗尽，以及主从复制是否延迟。

最后根据分析结果优化索引、改写 SQL、调整 InnoDB 参数或做数据分区/归档。

#查看连接数是否爆满
#SHOW STATUS LIKE 'Threads_connected';
#SHOW VARIABLES LIKE 'max_connections';