MySQL 8.0慢查询诊断与索引优化实战：从EXPLAIN到Performance Schema

小编 • • 数据库

MySQL慢查询诊断的系统化方法

MySQL慢查询是数据库性能问题的最常见表现，但诊断慢查询不能仅靠经验猜测。系统化的诊断流程包含三层：慢查询日志定位问题SQL、EXPLAIN分析执行计划、Performance Schema深入观测运行时指标。三层逐步深入，从”哪个SQL慢”到”为什么慢”再到”慢在哪里”，形成完整的诊断闭环。

慢查询日志配置与问题SQL筛选

慢查询日志是诊断的起点。MySQL 8.0中建议的配置参数：

-- my.cnf 核心配置
slow_query_log = ON
long_query_time = 0.5          -- 超过500ms的查询记录
log_queries_not_using_indexes = ON  -- 未使用索引的查询也记录
slow_query_log_file = /var/log/mysql/slow.log
min_examined_row_limit = 100   -- 扫描行数低于100的查询忽略

-- 在线设置（无需重启）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_queries_not_using_indexes = ON;

使用mysqldumpslow对慢日志进行聚合分析，快速定位高频慢查询：

# 按查询时间排序，取Top 10
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 按出现次数排序，取Top 10
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

EXPLAIN执行计划深度解读

EXPLAIN是分析SQL执行计划的核心工具。MySQL 8.0的EXPLAIN输出包含12列信息，重点关注以下5列：

type列：访问类型，从最优到最差排序为system > const > eq_ref > ref > range > index > ALL。出现ALL（全表扫描）时必须优化。

key列：实际使用的索引名。为NULL表示未使用索引。

rows列：预估扫描行数。高rows值通常是性能瓶颈的直接原因。

filtered列：过滤比例。低filtered值（<10%）意味着索引选择性差。

Extra列：额外信息。需重点关注的值包括Using filesort（额外排序）、Using temporary（临时表）、Using where（WHERE过滤）。

以下为典型慢查询的EXPLAIN分析与优化示例：

-- 原始查询：按创建时间范围筛选并按更新时间排序
SELECT id, title, status, updated_at
FROM orders
WHERE created_at BETWEEN '2026-07-01' AND '2026-08-01'
  AND status IN ('pending', 'processing')
ORDER BY updated_at DESC
LIMIT 50;

-- EXPLAIN结果（优化前）
-- type: ALL, key: NULL, rows: 2850000, Extra: Using where; Using filesort

-- 优化方案：创建覆盖索引
ALTER TABLE orders
  ADD INDEX idx_created_status_updated (created_at, status, updated_at);

-- EXPLAIN结果（优化后）
-- type: range, key: idx_created_status_updated,
-- rows: 18500, filtered: 100, Extra: Using index condition; Backward index scan

-- 扫描行数从285万降至1.85万，执行时间从2.3s降至0.02s

Performance Schema运行时观测

当EXPLAIN无法解释性能差异时（如数据分布不均匀导致的统计信息偏差），Performance Schema提供更底层的运行时指标。关键监控视图：

-- 查看当前正在执行的SQL及其等待事件
SELECT
    t.PROCESSLIST_ID,
    t.PROCESSLIST_INFO,
    ew.EVENT_NAME AS wait_event,
    ew.TIMER_WAIT / 1000000000 AS wait_ms
FROM performance_schema.threads t
JOIN performance_schema.events_waits_current ew
    ON t.THREAD_ID = ew.THREAD_ID
WHERE t.PROCESSLIST_INFO IS NOT NULL
ORDER BY ew.TIMER_WAIT DESC
LIMIT 10;

-- 查看SQL语句的历史执行统计
SELECT
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_TIMER_WAIT / 1000000000 AS total_wait_s,
    AVG_TIMER_WAIT / 1000000000 AS avg_wait_ms,
    SUM_ROWS_EXAMINED,
    SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

索引优化策略与常见陷阱

最左前缀原则。联合索引(a, b, c)支持a、(a,b)、(a,b,c)三种查询条件组合，但不支持b或(b,c)作为查询条件的索引查找。在字段顺序设计时，将等值查询字段放在前面、范围查询字段放在后面：

-- 业务查询模式：WHERE user_id = ? AND created_at BETWEEN ? AND ?
-- 索引设计：等值字段在前，范围字段在后
ALTER TABLE orders
  ADD INDEX idx_user_created (user_id, created_at);

索引选择性计算。选择性低的字段不适合单独建索引，可通过公式评估：

-- 计算各字段的选择性（越接近1越好）
SELECT
    COUNT(DISTINCT status) / COUNT(*) AS status_selectivity,
    COUNT(DISTINCT user_id) / COUNT(*) AS user_id_selectivity,
    COUNT(DISTINCT created_at) / COUNT(*) AS created_at_selectivity
FROM orders;

-- 典型结果：status 0.001, user_id 0.85, created_at 0.99
-- status选择性极低，单独建索引无意义

索引失效的常见原因。对索引列使用函数（WHERE DATE(created_at) = ‘2026-08-06’）、隐式类型转换（VARCHAR列传入整型参数）、LIKE前缀通配符（LIKE ‘%keyword’）、OR条件中部分列无索引，均会导致索引失效。修复方式分别为：改用范围查询替代函数、确保参数类型一致、使用全文索引替代LIKE、拆分查询或覆盖OR两侧索引。

优化效果验证与持续监控

索引优化后，必须通过Performance Schema对比优化前后的执行统计，验证实际效果而非仅看EXPLAIN输出。持续监控方案：配置Prometheus MySQL Exporter采集慢查询指标，设置阈值告警，当慢查询频率突增时自动触发诊断流程。定期执行ANALYZE TABLE更新统计信息，避免统计信息过时导致执行计划退化。MySQL慢查询优化不是一次性工作，而是需要持续监控、诊断、优化的闭环工程。

原创文章，作者：小编，如若转载，请注明出处：https://www.yunthe.com/mysql80-man-cha-xun-zhen-duan-yu-suo-yin-you-hua-shi-zhan/

赞 (0)
小编小编
0
上一篇 2026年8月6日
下一篇 2026年8月6日

相关推荐

MySQL 8.0 慢查询诊断与索引优化实战：从 EXPLAIN 到执行计划调优

小编 • • 数据库

慢查询是数据库性能问题的第一信号

线上数据库遇到响应变慢，排查切入点永远是慢查询日志。一条慢查询可能占用 80% 的数据库资源，阻塞其他正常请求。MySQL 8.0 的慢查询日志配合 Performance Schema，能精确定位问题 SQL 和资源瓶颈。找到慢查询后，通过 EXPLAIN 分析执行计划，调整索引和 SQL 写法，往往能把查询耗时从秒级降到毫秒级。

慢查询日志配置与分析

MySQL 8.0 慢查询日志参数：

-- my.cnf
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
min_examined_row_limit = 100
log_queries_not_using_indexes = ON
log_slow_admin_statements = ON

long_query_time=0.5 表示执行超过 500ms 的查询记录到日志。min_examined_row_limit=100 排除扫描行数过少的查询（可能是缓存命中，无优化价值）。log_queries_not_using_indexes 记录全表扫描的 SQL。

分析慢日志用 mysqldumpslow 或 pt-query-digest：

# 按查询时间排序，取前 10 条
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# pt-query-digest 提供更详细的分析
pt-query-digest /var/log/mysql/slow.log > slow_report.txt

pt-query-digest 的报告会按影响大小排列 SQL，同时给出执行次数、平均耗时、扫描行数等指标，直接锁定最需要优化的 SQL。

EXPLAIN 执行计划详解

在问题 SQL 前加 EXPLAIN 查看执行计划：

EXPLAIN SELECT o.order_id, o.amount, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'PENDING'
  AND o.created_at > '2026-07-01'
ORDER BY o.amount DESC
LIMIT 20;

关键列解读：

type：访问类型，从好到差排序：system > const > eq_ref > ref > range > index > ALL。ALL 表示全表扫描，必须优化。index 表示全索引扫描，比 ALL 稍好但仍然是全量扫描。

key：实际使用的索引。如果为 NULL 说明没走索引。

rows：预估扫描行数。值越大，查询成本越高。

Extra：额外信息。重点关注：
– Using filesort：额外的排序操作，消耗 CPU 和临时表
– Using temporary：使用了临时表，GROUP BY 或 DISTINCT 常见
– Using index：覆盖索引，不回表，理想状态
– Using where：在存储引擎层过滤后，还需在 Server 层二次过滤

索引设计实战：覆盖索引与最左匹配

上面的查询，假设 orders 表现状只有主键索引和 idx_customer_id。

问题一：WHERE 条件没走索引

EXPLAIN 结果显示 type: ALL，rows: 500000，全表扫描。status 字段没有索引，MySQL 必须逐行检查。

创建组合索引：

CREATE INDEX idx_status_created ON orders(status, created_at);

创建后再看 EXPLAIN，type 变为 range，rows 降到约 5000。

问题二：ORDER BY 触发 filesort

虽然 WHERE 走了索引，但 ORDER BY amount DESC 仍然需要 filesort。把排序字段也加入索引：

CREATE INDEX idx_status_created_amount ON orders(status, created_at, amount);

此时 EXPLAIN 的 Extra 列出现 Using index，查询完全走覆盖索引，不回表、不排序。

最左匹配原则：组合索引 (status, created_at, amount) 支持以下查询条件：
– WHERE status = ?（使用第 1 列）
– WHERE status = ? AND created_at > ?（使用第 1、2 列）
– WHERE status = ? AND created_at > ? ORDER BY amount（使用全部 3 列）

不支持 WHERE created_at > ?（跳过了第 1 列，索引失效）。

索引失效的常见场景与修复

1. 函数操作导致索引失效

-- 索引失效：在列上使用函数
SELECT * FROM orders WHERE DATE(created_at) = '2026-07-28';

-- 索引有效：改写为范围查询
SELECT * FROM orders
WHERE created_at >= '2026-07-28 00:00:00'
  AND created_at < '2026-07-29 00:00:00';

2. 隐式类型转换

-- phone 是 VARCHAR 类型，传入整数触发隐式转换
SELECT * FROM users WHERE phone = 13800138000;  -- 索引失效
SELECT * FROM users WHERE phone = '13800138000';  -- 索引有效

3. OR 条件破坏索引

-- OR 两边字段都有索引时可能走 index_merge，效率不稳定
SELECT * FROM orders WHERE status = 'PENDING' OR amount > 10000;

-- 优化：用 UNION 替代
SELECT * FROM orders WHERE status = 'PENDING'
UNION
SELECT * FROM orders WHERE amount > 10000;

4. LIKE 前缀通配符

SELECT * FROM users WHERE name LIKE '%张';  -- 索引失效
SELECT * FROM users WHERE name LIKE '张%';   -- 索引有效

Performance Schema 深度诊断

当慢查询日志不够用时，Performance Schema 提供更细粒度的诊断数据：

-- 查看当前正在执行的 SQL 及其等待事件
SELECT * FROM performance_schema.events_statements_current
WHERE TIMER_WAIT > 1000000000;  -- 运行超过 1 秒

-- 查看哪类 SQL 消耗最多时间
SELECT DIGEST_TEXT,
       COUNT_STAR,
       SUM_TIMER_WAIT / 1000000000 AS total_seconds,
       AVG_TIMER_WAIT / 1000000000 AS avg_seconds
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

内存诊断：

-- 查看哪个 SQL 创建了最多的临时表
SELECT DIGEST_TEXT,
       SUM_CREATED_TMP_DISK_TABLES,
       SUM_CREATED_TMP_TABLES
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_CREATED_TMP_DISK_TABLES > 0
ORDER BY SUM_CREATED_TMP_DISK_TABLES DESC
LIMIT 10;

磁盘临时表意味着数据量超出了 tmp_table_size 配置，需要优化 GROUP BY/ORDER BY 逻辑或增大内存临时表限制。

索引维护与统计信息更新

索引建好不是终点。数据量变化后，MySQL 的统计信息可能过时，导致执行计划走偏：

-- 手动更新统计信息
ANALYZE TABLE orders;

-- 查看索引基数（cardinality 越高，索引选择越好）
SHOW INDEX FROM orders;

生产环境建议在低峰期定期执行 ANALYZE TABLE，频率根据数据变更速度调整。日增万级记录的表每天一次，日增百级记录的表每周一次。

冗余索引清理：

-- 查找冗余索引
SELECT s.table_schema, s.table_name, s.index_name,
       GROUP_CONCAT(s.column_name ORDER BY s.seq_in_index) AS columns
FROM information_schema.statistics s
WHERE s.table_schema = 'your_db'
GROUP BY s.table_schema, s.table_name, s.index_name
HAVING columns LIKE 'status%'
   AND s.index_name != 'PRIMARY';

如果存在 (status) 和 (status, created_at) 两个索引，前者是冗余的，可以删除。

MySQL 性能优化是一个从发现问题到验证修复的闭环：慢查询日志定位问题 SQL，EXPLAIN 分析执行计划，索引优化消除全表扫描和 filesort，Performance Schema 做深度诊断，定期维护统计信息和清理冗余索引。每一步都有明确的输入输出，最终目标是让每条 SQL 都走最优执行计划。

原创文章，作者：小编，如若转载，请注明出处：https://www.yunthe.com/mysql80-man-cha-xun-zhen-duan-yu-suo-yin-you-hua-shi-zhan/

赞 (0)
小编小编
0
上一篇 2026年7月28日
下一篇 2026年7月28日

相关推荐

MySQL 8.0慢查询诊断与索引优化实战：从EXPLAIN执行计划到InnoDB缓冲池调优全流程

小编 • • 数据库

慢查询问题的排查路径

线上MySQL出现查询耗时飙升时，盲目加索引往往是无效甚至有害的。系统化的排查路径是：确认慢查询范围→获取执行计划→分析扫描行数和索引使用情况→针对性优化。MySQL 8.0提供了更丰富的EXPLAIN信息、Performance Schema指标和sys库视图，完整诊断链路比5.7方便得多。本文从一条真实慢查询的排查出发，覆盖执行计划分析、索引策略、SQL改写和实例级调优。

慢查询日志配置与捕获

-- my.cnf 核心配置
[mysqld]
slow_query_log = ON
long_query_time = 0.5          # 超过0.5秒记录
slow_query_log_file = /var/log/mysql/slow.log
log_queries_not_using_indexes = ON
min_examined_row_limit = 100   # 扫描行少于100不记录，减少噪音

-- 动态调整（无需重启）
SET GLOBAL long_query_time = 0.5;
SET GLOBAL slow_query_log = ON;

通过sys库快速查看Top 10慢查询：

-- 按平均执行时间排序的Top 10慢查询
SELECT *
FROM sys.statements_with_runtimes_in_95th_percentile
ORDER BY avg_latency DESC
LIMIT 10;

-- 查看当前未完成的长事务
SELECT trx_id, trx_state, trx_started,
       trx_query, trx_tables_locked, trx_rows_locked
FROM information_schema.innodb_trx
ORDER BY trx_started;

EXPLAIN执行计划深度解读

以一条分页查询为例，拆解EXPLAIN各字段的含义：

EXPLAIN ANALYZE
SELECT o.order_id, o.amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'PAID'
  AND o.created_at >= '2026-07-01'
ORDER BY o.created_at DESC
LIMIT 20;

MySQL 8.0的EXPLAIN ANALYZE会输出实际执行耗时，比传统EXPLAIN更有参考价值。关注以下字段：

字段 含义 优化方向
type 访问类型 目标：ref/eq_ref/range，避免ALL和index
key 实际使用的索引 NULL表示未走索引
rows 预估扫描行数 越小越好，对比实际行数判断统计信息准确性
filtered 过滤比例 低于10%说明索引选择性差
Extra 额外信息 Using filesort/Using temporary需要重点关注

常见问题解读：

  • Using filesort：排序未走索引，需要额外排序操作。解决方案：创建覆盖排序字段的复合索引
  • Using temporary：使用了临时表，常见于GROUP BY无索引场景。解决方案：为GROUP BY字段创建索引
  • Using index：覆盖索引，查询所有字段都在索引中，无需回表，性能最优

索引优化策略与常见陷阱

策略1：复合索引遵循最左前缀原则

-- 创建复合索引
CREATE INDEX idx_status_created ON orders(status, created_at);

-- 能走索引的查询
SELECT * FROM orders WHERE status = 'PAID';                    -- ✅ 走索引
SELECT * FROM orders WHERE status = 'PAID' AND created_at >= '2026-07-01';  -- ✅ 走索引

-- 不能走索引的查询
SELECT * FROM orders WHERE created_at >= '2026-07-01';        -- ❌ 跳过了最左列status

策略2：覆盖索引消除回表

-- 只需要order_id和amount，创建覆盖索引
CREATE INDEX idx_status_created_amount ON orders(status, created_at, order_id, amount);

-- EXPLAIN显示Using index，无需回表查聚簇索引
SELECT order_id, amount
FROM orders
WHERE status = 'PAID' AND created_at >= '2026-07-01'
ORDER BY created_at DESC
LIMIT 20;

策略3：避免索引失效的常见写法

-- ❌ 索引列上使用函数
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
-- ✅ 改写为范围查询
SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

-- ❌ 隐式类型转换（user_id是varchar，传入整型）
SELECT * FROM orders WHERE user_id = 12345;
-- ✅ 类型匹配
SELECT * FROM orders WHERE user_id = '12345';

-- ❌ OR条件导致索引失效
SELECT * FROM orders WHERE status = 'PAID' OR amount > 10000;
-- ✅ UNION改写
SELECT * FROM orders WHERE status = 'PAID'
UNION
SELECT * FROM orders WHERE amount > 10000;

InnoDB缓冲池调优

缓冲池（Buffer Pool）是InnoDB性能的核心，合理的配置直接影响查询响应时间：

-- 查看当前Buffer Pool配置
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

-- 推荐配置（独占服务器）
-- Buffer Pool占物理内存的70%-80%
SET GLOBAL innodb_buffer_pool_size = 12884901888;  -- 12GB（16GB服务器）
SET GLOBAL innodb_buffer_pool_instances = 8;         -- 每1.5GB一个实例

-- 多实例减少锁竞争（8.x默认已优化）
-- 监控命中率
SELECT 
  (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100 
  AS buffer_pool_hit_rate;
-- 命中率应 > 99%，低于95%需要增大Buffer Pool或优化查询

MySQL 8.0的Buffer Pool预热策略：

-- 关闭时转储Buffer Pool状态，启动时自动预热
SET GLOBAL innodb_buffer_pool_dump_at_shutdown = ON;
SET GLOBAL innodb_buffer_pool_load_at_startup = ON;

-- 手动触发转储/加载
SET GLOBAL innodb_buffer_pool_dump_now = ON;
SET GLOBAL innodb_buffer_pool_load_now = ON;

-- 查看转储状态
SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status';

预热可以在实例重启后快速恢复到停机前的缓存状态，将冷启动的查询延迟从分钟级降到秒级。

SQL改写与分页优化

深度分页（LIMIT 100000, 20）的性能问题在数据量大时尤为严重。传统写法扫描前100020行后丢弃前100000行，优化方案：

-- ❌ 传统深度分页
SELECT * FROM orders WHERE status = 'PAID' ORDER BY id LIMIT 100000, 20;
-- 扫描100020行，回表100020次

-- ✅ 延迟关联优化
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders WHERE status = 'PAID' ORDER BY id LIMIT 100000, 20
) tmp ON o.id = tmp.id;
-- 子查询走覆盖索引只扫描id，外层只回表20次

延迟关联的核心是利用覆盖索引在子查询中快速定位分页offset的id值，再通过主键回表获取完整数据。在大数据量场景下，性能提升可达10-50倍。

原创文章，作者：小编，如若转载，请注明出处：https://www.yunthe.com/mysql80-man-cha-xun-zhen-duan-yu-suo-yin-you-hua-shi-zhan/

赞 (0)
小编小编
0
上一篇 2026年7月23日
下一篇 2026年7月23日

相关推荐