MySQL慢查询诊断与索引优化实战：从EXPLAIN到线上调优的全流程

小编 • • 数据库

慢查询的发现与定性

慢查询优化是数据库运维中最高频的工作。发现慢查询的途径有三个：慢查询日志（Slow Log）、information_schema.processlist实时捕获、以及Performance Schema的事件记录。慢查询日志是最基础的手段，开启后记录所有执行时间超过long_query_time的SQL。

# 开启慢查询日志并配置参数
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

# 查看当前配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

long_query_time的默认值是10秒，这对大多数业务来说太宽松了。线上环境建议设为0.5-1秒，配合log_queries_not_using_indexes捕获全表扫描。需要注意，降低阈值会增加日志量，需要配套日志轮转策略。

EXPLAIN执行计划深度解读

EXPLAIN是分析SQL执行计划的核心工具，但很多开发者只关注type列是否为ALL。实际上需要综合多个列才能判断执行计划是否合理。

关键列解读

type列（访问类型，从优到差）：system > const > eq_ref > ref > range > index > ALL。ALL是全表扫描，必须优化。index是全索引扫描，看起来比ALL好，但如果扫描行数很大，实际性能可能和ALL差不多。

key列：实际使用的索引名。如果为NULL说明没有使用索引。possible_keys列显示可选索引，key列显示实际选中索引——如果possible_keys有值但key为NULL，说明优化器评估后放弃了索引，需要关注rows估算值来判断原因。

rows列：预估扫描行数。这个值是优化器基于统计信息的估算值，不一定精确，但量级是可靠的。rows乘以filtered百分比就是最终参与计算的数据行数，越小越好。

Extra列：额外信息。需要重点关注的值：
– Using filesort：额外排序操作，需要优化
– Using temporary：使用临时表，常见于GROUP BY和DISTINCT
– Using index：覆盖索引，性能好
– Using where：Server层过滤，检查是否可以下推到存储引擎

# 分析慢查询的执行计划
EXPLAIN SELECT o.order_id, o.amount, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at BETWEEN '2026-07-01' AND '2026-07-31'
  AND o.status = 'PAID'
ORDER BY o.amount DESC
LIMIT 20;

# 问题：rows=50000且filtered=10%，实际匹配5000行
# filesort说明排序没有利用索引

索引设计实战：避免常见反模式

反模式1：索引列上使用函数

# 错误：索引列上使用函数，索引失效
SELECT * FROM orders WHERE DATE(created_at) = '2026-07-29';

# 正确：范围查询，可以利用索引
SELECT * FROM orders 
WHERE created_at >= '2026-07-29 00:00:00' 
  AND created_at < '2026-07-30 00:00:00';

反模式2：联合索引列顺序错误

联合索引遵循最左前缀原则。索引(a, b, c)可以支持a、(a,b)、(a,b,c)的查询，但不能支持(b,c)或(c)的查询。

# 场景：按状态和时间范围查询订单
# 常见查询模式：
# WHERE status = 'PAID' AND created_at BETWEEN ... AND ...
# WHERE status = 'PAID' AND user_id = 123

# 错误索引：把范围查询列放在前面
# INDEX idx_wrong (created_at, status, user_id)

# 正确索引：等值查询列在前，范围查询列在后
CREATE INDEX idx_order_query ON orders (status, user_id, created_at);

反模式3：索引选择性低却建索引

选择性低的列（如性别、状态只有2-3个值）不适合单独建索引。MySQL优化器在这种情况下会选择全表扫描，因为回表代价高于顺序扫描。

# 评估索引选择性
SELECT 
    COUNT(DISTINCT status) / COUNT(*) AS status_selectivity,
    COUNT(DISTINCT customer_id) / COUNT(*) AS customer_selectivity
FROM orders;

# status_selectivity: 0.00003 (状态只有5个值，不适合单列索引)
# customer_selectivity: 0.65 (适合建索引)

覆盖索引优化：减少回表次数

回表（Bookmark Lookup）是InnoDB查询的重要性能瓶颈。二级索引找到主键后需要回聚簇索引获取完整行数据，每次回表都是一次随机IO。覆盖索引让查询所需的所有列都包含在索引中，避免回表。

# 原查询：需要回表获取amount和status
SELECT order_id, amount, status FROM orders WHERE customer_id = 12345;

# 创建覆盖索引
CREATE INDEX idx_customer_covering ON orders (customer_id, order_id, amount, status);

# EXPLAIN结果中Extra列出现"Using index"说明覆盖索引生效
# 性能提升：从扫描5000行+5000次回表，到直接从索引返回结果

覆盖索引的代价是索引体积增大，影响INSERT/UPDATE性能。在写入频繁的表上使用覆盖索引需要权衡读写比例。一般来说，读写比超过10:1时覆盖索引收益明显。

线上优化操作的安全规范

在正在运行的数据库上执行DDL需要格外谨慎。MySQL 8.0的Online DDL虽然支持INSTANT和INPLACE算法，但并非所有ALTER操作都能在线执行。

# 安全的索引创建方式
# 1. 使用ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE
ALTER TABLE orders 
  ADD INDEX idx_order_query (status, user_id, created_at),
  ALGORITHM=INPLACE, LOCK=NONE;

# 2. MySQL 8.0的不可见索引（先建后验证）
ALTER TABLE orders 
  ADD INDEX idx_test (customer_id, created_at) INVISIBLE;

# 验证无性能问题后设为可见
ALTER TABLE orders ALTER INDEX idx_test VISIBLE;

# 3. 大表索引创建使用pt-online-schema-change
# pt-online-schema-change --alter "ADD INDEX idx_xxx(col)" #   --host=127.0.0.1 --user=dba --ask-pass #   D=production,t=orders #   --chunk-size=1000 --max-load=Threads_running=50

慢查询治理的长效机制

单次优化只能解决当前问题，长效机制才能防止慢查询反复出现。建立三层防护：

第一层：代码审核。在Merge Request中增加SQL审查环节，使用SQL解析工具（如Soar/SQLCheck）自动检测常见反模式。对新上线的SQL要求提供EXPLAIN结果。

第二层：慢查询告警。对slow query log做实时解析，5分钟内扫描行数超过10万或执行时间超过5秒的查询触发告警。

第三层：定期巡检。每周分析Top 20慢查询，识别新增的慢查询和执行次数上升的已有慢查询。使用pt-query-digest做汇总分析：

# pt-query-digest分析慢日志
pt-query-digest /var/log/mysql/slow.log --since "7 days" \
  --limit 20 --output slowlog-report.txt

通过三层防护，慢查询在开发阶段被拦截、运行时被监控、长期被治理，数据库性能才能保持稳定。优化不是一锤子买卖，而是持续运营。

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

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

相关推荐

MySQL慢查询诊断与索引优化实战：从全表扫描到毫秒级响应

小编 • • 数据库

慢查询是数据库性能问题的放大器

一条慢查询在高并发下会锁住大量行、占用Buffer Pool、阻塞其他事务，引发级联延迟。定位和消灭慢查询是MySQL性能调优的第一步，也是最投入产出比最高的一步。这篇从慢查询日志配置到EXPLAIN执行计划解读，再到索引设计，给出完整诊断链路。

慢查询日志配置与自动采集

先确保慢查询日志开启并设置合理阈值：

-- my.cnf
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.1           -- 超过100ms记录
min_examined_row_limit = 100    -- 扫描少于100行不记录（排除小表误报）
log_queries_not_using_indexes = ON  -- 强制记录未用索引的查询

线上环境建议long_query_time设0.1秒而非默认10秒。大部分劣化查询在0.1-1秒区间就能被发现。

用pt-query-digest自动分析慢日志：

pt-query-digest /var/log/mysql/slow.log --since "24h ago"   --limit 20 --order-by Query_time:sum

输出按总执行时间排序的TOP 20慢查询，包含执行次数、平均耗时、扫描行数、EXPLAIN示例。直接定位到最值得优化的SQL。

EXPLAIN执行计划深度解读

拿到慢查询SQL后第一步EXPLAIN：

EXPLAIN SELECT o.id, o.total, 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 50;

关键字段判读：

  • type：ALL=全表扫描（危险），index=全索引扫描，range=范围扫描，ref=索引等值查找，const=常量表。目标是让每张表至少到ref级别。
  • key：实际用了哪个索引，NULL表示没走索引。
  • rows：预估扫描行数，差距越大越危险。
  • Extra：Using filesort=额外排序（危险），Using temporary=临时表（危险），Using index=覆盖索引（理想）。

索引优化五种典型场景

场景一：WHERE条件缺索引

-- 慢查询: 全表扫描200万行
SELECT * FROM orders WHERE status = 'PAID' AND created_at > '2026-07-01';

-- 添加复合索引
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

-- 优化后: type=range, rows≈5000

复合索引遵循最左前缀原则，status在前因为等值过滤性更好，created_at在后做范围扫描。

场景二：ORDER BY导致filesort

-- 慢查询: Using filesort
SELECT * FROM orders WHERE user_id = 1001 ORDER BY created_at DESC LIMIT 20;

-- 索引覆盖排序
ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at);

-- 优化后: Extra消失filesort，type=ref

场景三：函数破坏索引

-- 索引失效: 对created_at用函数
SELECT * FROM orders WHERE DATE(created_at) = '2026-07-28';  -- 全表扫描！

-- 改写: 范围查询替代函数
SELECT * FROM orders 
WHERE created_at >= '2026-07-28' AND created_at < '2026-07-29';  -- 走索引

场景四：隐式类型转换

-- user_id是int类型，传了字符串
SELECT * FROM orders WHERE user_id = '1001';  -- 索引失效！

-- 修正: 类型匹配
SELECT * FROM orders WHERE user_id = 1001;  -- 走索引

场景五：覆盖索引消除回表

-- 回表查询: 取name需要回主键
SELECT id, status, created_at FROM orders WHERE user_id = 1001;

-- 覆盖索引: 所有查询列都在索引里
ALTER TABLE orders ADD INDEX idx_user_cover (user_id, status, created_at);

-- 优化后: Extra=Using index, 零回表

索引设计原则与反模式

索引不是越多越好，每个额外索引增加写开销约5-10%。设计原则：

  • 单表索引数不超过6个，总索引列数不超过15列
  • 高选择性列在前（区分度高的放最左）
  • 等值条件列在范围条件列前面
  • 频繁更新的列不要建太多索引

评估索引选择性：

SELECT COUNT(DISTINCT status) / COUNT(*) AS selectivity FROM orders;
-- selectivity接近1.0说明该列区分度高，适合建索引
-- 0.1以下说明值分布极不均匀，索引效果差

线上索引变更安全操作

大表加索引会锁表，用pt-online-schema-change做在线DDL：

pt-online-schema-change   --alter "ADD INDEX idx_status_created (status, created_at)"   --host=127.0.0.1 --port=3306   --user=admin --password=xxx   D=prod,t=orders   --chunk-size=1000 --max-load=Threads_running=50   --critical-load=Threads_running=100   --execute

分chunk执行，每1000行检查一次负载，超过--max-load自动暂停。索引创建完成前不影响业务读写。

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

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

相关推荐

MySQL慢查询诊断与索引优化实战：从EXPLAIN到线上调优全流程

小编 • • 数据库

MySQL慢查询是线上数据库性能问题的头号杀手

数据库运维中80%的性能问题源自慢查询。一个未经优化的查询语句在高并发场景下可以拖垮整个实例——锁等待蔓延、连接池耗尽、主从延迟飙升。MySQL性能调优的核心不是调参数，而是把慢查询找出来、看懂执行计划、加对索引。

慢查询日志配置与捕获

开启慢查询日志是诊断的第一步，生产环境建议长期开启：

-- 动态开启（无需重启）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过1秒记录
SET GLOBAL log_queries_not_using_indexes = ON;  -- 记录未用索引的查询
SET GLOBAL min_examined_row_limit = 100;  -- 扫描行低于100的不记录

-- 持久化到my.cnf
[mysqld]
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/log/mysql/slow.log
log_queries_not_using_indexes = 1

用mysqldumpslow分析慢查询日志Top N：

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

# 按返回记录数排序
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log

# -s t: 按查询时间 -s r: 按返回记录数 -s l: 按锁定时间 -s c: 按查询次数

Percona的pt-query-digest比mysqldumpslow更强大，输出执行计划指纹和详细统计：

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

# 只分析最近1小时的慢查询
pt-query-digest --since '1h ago' /var/log/mysql/slow.log

EXPLAIN执行计划深度解读

拿到慢查询语句后，EXPLAIN是分析执行计划的唯一标准工具。但很多人只看type列，忽略了其他关键信息。

EXPLAIN SELECT o.order_id, o.amount, u.username
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.amount DESC
LIMIT 50;

EXPLAIN输出的关键字段及其含义：

– **type**：访问类型，从好到差排序：system > const > eq_ref > ref > range > index > ALL。出现ALL就是全表扫描，必须优化
– **key**：实际使用的索引，NULL表示没走索引
– **rows**：预估扫描行数，这个值乘以多个表的结果就是总扫描量
– **Extra**：额外信息，出现Using filesort（额外排序）或Using temporary（临时表）要重点关注

常见问题模式：

1. type=ALL + rows值极大：缺少索引或索引失效
2. type=ref但rows仍然很大：索引区分度低，需要优化索引列顺序
3. Extra出现Using filesort：排序字段没有走索引
4. Extra出现Using temporary：GROUP BY或DISTINCT产生了临时表

索引优化：从建索引到避免索引失效

索引优化的核心原则是遵循最左前缀匹配和覆盖索引。

联合索引的列顺序决定了索引的可用性。最左前缀规则要求查询条件必须从索引最左列开始匹配：

-- 创建联合索引
ALTER TABLE orders ADD INDEX idx_status_created_amount (status, created_at, amount);

-- 能走索引：匹配最左前缀
SELECT * FROM orders WHERE status = 'PAID';
SELECT * FROM orders WHERE status = 'PAID' AND created_at > '2026-07-01';

-- 不能走索引：跳过了status列
SELECT * FROM orders WHERE created_at > '2026-07-01';

-- 能走索引：匹配前两列，第三列用于排序消除filesort
SELECT * FROM orders WHERE status = 'PAID'
  AND created_at > '2026-07-01'
ORDER BY amount DESC LIMIT 50;

覆盖索引——查询的所有列都在索引中，不需要回表：

-- 非覆盖索引：需要回表查username
SELECT order_id, amount, username FROM orders WHERE status = 'PAID';

-- 覆盖索引：所有查询列在索引中
SELECT status, created_at, amount FROM orders WHERE status = 'PAID';

-- EXPLAIN中Extra列出现Using index就是覆盖索引

索引失效的常见陷阱：

-- 1. 函数运算导致索引失效
-- 错误：对索引列使用函数
SELECT * FROM orders WHERE DATE(created_at) = '2026-07-28';
-- 正确：范围查询
SELECT * FROM orders WHERE created_at >= '2026-07-28' AND created_at < '2026-07-29';

-- 2. 隐式类型转换
-- 如果user_id是varchar类型，以下查询会隐式转换导致索引失效
SELECT * FROM orders WHERE user_id = 12345;
-- 正确：保持类型一致
SELECT * FROM orders WHERE user_id = '12345';

-- 3. LIKE前缀通配符
SELECT * FROM orders WHERE order_no LIKE '%20260728';  -- 索引失效
SELECT * FROM orders WHERE order_no LIKE 'ORD20260728%';  -- 走索引

SQL查询优化实战案例

案例：分页查询越翻越慢

-- 原始SQL：深分页性能极差
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;
-- MySQL需要扫描1000020行再丢弃前1000000行

-- 优化方案1：延迟关联（推荐）
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 20) t
ON o.id = t.id;
-- 子查询只走索引扫描id列，不回表；外层只查20条回表

-- 优化方案2：游标分页（适合无限滚动场景）
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;
-- 基于上一页最后一条记录的ID继续查询，O(1)复杂度

案例：GROUP BY产生临时表

-- 原始SQL：Using temporary
SELECT status, COUNT(*) FROM orders GROUP BY status;

-- 优化：如果分组列有索引，MySQL可以直接利用索引的有序性
-- 不需要临时表
ALTER TABLE orders ADD INDEX idx_status (status);

-- 更复杂的场景：多列分组
SELECT status, DATE(created_at), COUNT(*)
FROM orders
GROUP BY status, DATE(created_at);

-- 无法直接用索引优化DATE()函数，改写为：
SELECT status, created_at_date, COUNT(*)
FROM orders
WHERE created_at >= '2026-07-01' AND created_at < '2026-08-01'
GROUP BY status, created_at_date;

线上索引变更的安全操作

大表加索引不能直接ALTER TABLE——会锁表。生产环境使用pt-online-schema-change或gh-ost进行在线DDL：

# pt-online-schema-change添加索引
pt-online-schema-change \
  --host=127.0.0.1 --port=3306 --user=admin --password=pwd \
  --alter "ADD INDEX idx_status_created (status, created_at)" \
  --database=production --table=orders \
  --chunk-size=1000 --max-load=Threads_running=50 \
  --critical-load=Threads_running=100 \
  --execute

# --chunk-size：每次处理1000行
# --max-load：负载超过50个运行线程时暂停
# --critical-load：负载超过100个线程时中止

MySQL 8.0+的ALGORITHM=INPLACE也可以避免锁表，但大表操作仍建议在低峰期执行，并密切监控主从延迟。

慢查询优化是一个循环过程：捕获慢查询 → EXPLAIN分析 → 加索引/改写SQL → 验证效果 → 继续监控。没有银弹，每个查询的优化方案都取决于数据分布和访问模式。

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

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

相关推荐

MySQL慢查询诊断与索引优化实战：EXPLAIN执行计划深度解析

小编 • • 数据库

MySQL性能调优工作中，慢查询诊断是最常见也最关键的任务。一条低效SQL可能导致整个数据库实例的连接池耗尽，引发雪崩效应。本文从慢查询日志采集、EXPLAIN执行计划解读、索引优化策略到SQL重写技巧，系统化梳理MySQL慢查询排查的完整工作流，所有命令和配置均在MySQL 8.0环境中验证。

慢查询日志开启与pt-query-digest分析工具

开启慢查询日志，设置阈值和输出格式：

-- 动态开启（无需重启）
SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;          -- 超过1秒记录
SET GLOBAL log_queries_not_using_indexes = ON;  -- 未使用索引的查询也记录
SET GLOBAL min_examined_row_limit = 100;  -- 检查行数小于100不记录
SET GLOBAL log_slow_admin_statements = ON;

-- 持久化配置 my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_slow_verbosity = QUERY_PLAN,EXPLAIN   -- 记录执行计划

使用Percona Toolkit的pt-query-digest分析慢查询日志，按指纹聚合相似查询：

# 安装pt-query-digest
yum install -y percona-toolkit

# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log > slow_report.txt

# 只分析最近1小时的慢查询
pt-query-digest --since 1h /var/log/mysql/slow.log

# 输出按执行时间排序的TOP 10慢查询
pt-query-digest --order-by Query_time:sum \
    --limit 10 /var/log/mysql/slow.log

pt-query-digest输出示例解读：

# Profile 中关键列：
# Rank: 慢查询排名
# Query ID: 查询指纹ID（相同SQL模板聚合）
# Response time: 总响应时间及占比
# Calls: 执行次数
# R/Call: 平均每次执行时间
# V/M: 方差/均值比，值越大表示执行时间越不稳定

# 10秒内出现5000次的慢查询
#  rank count  time    query
#  1    5000   120.5s  SELECT * FROM orders WHERE user_id = ? AND status = ?

# 高V/M值的查询需要重点排查——参数不同导致执行计划差异

EXPLAIN执行计划字段深度解读

EXPLAIN是MySQL查询优化的核心诊断工具，每个字段都承载着执行计划的关键信息：

EXPLAIN SELECT o.order_id, o.amount, u.username
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'PAID' AND o.created_at > '2026-01-01'
ORDER BY o.amount DESC
LIMIT 20;
+----+-------------+-------+------------+--------+---------------------+---------------------+---------+------------------+------+----------+-----------------------+
| id | select_type | table | partitions | type   | possible_keys       | key                 | key_len | ref              | rows | filtered | Extra                 |
+----+-------------+-------+------------+--------+---------------------+---------------------+---------+------------------+------+----------+-----------------------+
|  1 | SIMPLE      | o     | NULL       | range  | idx_status,idx_date | idx_status          | 102     | NULL             | 8500 |    33.33 | Using index condition |
|  1 | SIMPLE      | u     | NULL       | eq_ref | PRIMARY             | PRIMARY             | 8       | test.o.user_id   |    1 |   100.00 | NULL                  |
+----+-------------+-------+------------+--------+---------------------+---------------------+---------+------------------+------+----------+-----------------------+

关键字段分析：

type（访问类型），性能从好到差：system > const > eq_ref > ref > range > index > ALL。生产环境要求至少达到range级别，ALL代表全表扫描必须优化。

key_len（索引长度），反映索引使用情况。计算公式：字段字节数 × 字符集系数 + 可空标记(1字节)。上述示例中idx_status的key_len=102，表示status字段为varchar(50) utf8mb4（50×4+1可空+2长度=203）但实际只用了前缀。

rows（预估扫描行数），优化器基于统计信息估算。rows值与实际行数偏差大时需要执行ANALYZE TABLE更新统计信息。

Extra（额外信息），包含执行细节：

  • Using index：覆盖索引，无需回表，最优情况
  • Using index condition：索引条件下推（ICP），减少回表次数
  • Using filesort：需要额外排序操作，需关注是否可优化
  • Using temporary：使用临时表，通常出现在GROUP BY/DISTINCT中
  • Using join buffer：使用BNL/BKA连接算法，被驱动表无可用索引

索引优化策略与联合索引设计原则

联合索引设计遵循最左前缀原则，字段顺序决定索引可用性。以订单查询场景为例：

-- 业务查询模式分析：
-- 1. WHERE status = 'PAID' AND created_at > '2026-01-01'  (高频)
-- 2. WHERE user_id = 123 AND status = 'PAID'              (高频)
-- 3. WHERE status = 'PAID' ORDER BY amount DESC            (中频)

-- 错误索引：分别为每个字段建单列索引
-- MySQL优化器只能选择一个索引，无法同时利用idx_status和idx_user_id
CREATE INDEX idx_status ON orders(status);        -- 冗余
CREATE INDEX idx_user_id ON orders(user_id);      -- 冗余
CREATE INDEX idx_created ON orders(created_at);   -- 冗余

-- 正确索引：按查询频率和区分度设计联合索引
CREATE INDEX idx_status_created ON orders(status, created_at);
CREATE INDEX idx_user_status ON orders(user_id, status);

-- 覆盖索引优化：将查询字段纳入索引避免回表
CREATE INDEX idx_status_created_covering ON orders(status, created_at, order_id, amount);

索引失效的常见场景排查：

-- 1. 隐式类型转换：字段为varchar，查询传int
EXPLAIN SELECT * FROM orders WHERE order_no = 20260723001;
-- type = ALL（全表扫描），索引失效
-- 修正：WHERE order_no = '20260723001'

-- 2. 函数操作导致索引失效
EXPLAIN SELECT * FROM orders WHERE DATE(created_at) = '2026-07-23';
-- type = ALL，索引失效
-- 修正：WHERE created_at >= '2026-07-23' AND created_at < '2026-07-24'

-- 3. LIKE以通配符开头
EXPLAIN SELECT * FROM users WHERE username LIKE '%zhang%';
-- type = ALL，索引失效
-- 修正：使用全文索引或右匹配 LIKE 'zhang%'

-- 4. OR连接条件中一侧无索引
EXPLAIN SELECT * FROM orders WHERE status = 'PAID' OR remark LIKE '%urgent%';
-- type = ALL，整个查询走全表扫描
-- 修正：拆分为UNION查询或确保两侧都有索引

-- 5. 联合索引非最左前缀
-- 索引: (status, created_at, user_id)
EXPLAIN SELECT * FROM orders WHERE created_at > '2026-01-01';
-- type = ALL，跳过了status无法使用联合索引
-- 修正：添加status条件或创建created_at单列索引

复杂SQL查询重写与性能对比

子查询优化为JOIN：

-- 优化前：相关子查询，每行执行一次子查询
SELECT * FROM orders o
WHERE o.user_id IN (
    SELECT id FROM users WHERE vip_level >= 5
);
-- 执行时间: 3.2s, rows: 850000

-- 优化后：改写为JOIN
SELECT o.* FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.vip_level >= 5;
-- 执行时间: 0.15s, rows: 1200

-- 进一步优化：使用EXISTS替代IN（MySQL 8.0优化器已自动改写）
SELECT o.* FROM orders o
WHERE EXISTS (
    SELECT 1 FROM users u WHERE u.id = o.user_id AND u.vip_level >= 5
);

分页查询深度优化：

-- 优化前：深度分页，OFFSET越大越慢
SELECT * FROM orders ORDER BY created_at DESC LIMIT 100000, 20;
-- 执行时间: 2.8s（需扫描100020行）

-- 优化方案1：延迟关联
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY created_at DESC LIMIT 100000, 20
) t ON o.id = t.id;
-- 执行时间: 0.08s（子查询走覆盖索引）

-- 优化方案2：游标分页（记住上一页最后一条记录的ID）
SELECT * FROM orders
WHERE id < ?  -- 上一页最后一条记录的ID
ORDER BY id DESC LIMIT 20;
-- 执行时间: 0.001s（走主键索引）

生产环境慢查询监控与预防机制

建立持续的慢查询监控体系，通过Performance Schema实时采集：

-- 启用statements digest采集
UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' WHERE NAME = 'events_statements_history_long';

-- 查询TOP 10慢SQL
SELECT 
    DIGEST_TEXT,
    COUNT_STAR as exec_count,
    ROUND(AVG_TIMER_WAIT/1000000000, 2) as avg_ms,
    ROUND(SUM_TIMER_WAIT/1000000000, 2) as total_ms,
    SUM_ROWS_EXAMINED as rows_examined,
    SUM_ROWS_SENT as rows_sent,
    ROUND(SUM_ROWS_EXAMINED/COUNT_STAR, 0) as avg_rows_examined
FROM performance_schema.events_statements_summary_by_digest
WHERE AVG_TIMER_WAIT > 1000000000  -- 平均超过1秒
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

SQL审核流程中引入索引检查规则：所有上线的DML语句必须通过EXPLAIN验证，type字段不得为ALL，rows预估不得超过1万行。CI/CD流水线中集成SQL审核工具（如Archery、Yearning），自动拦截全表扫描和缺失索引的SQL。

定期维护统计信息准确性，避免优化器选择错误的执行计划：

-- 每日凌晨低峰期执行
ANALYZE TABLE orders, users, order_items PERSISTENT FOR ALL;

-- 查看统计信息采样页数
SELECT table_name, sample_size, table_rows 
FROM information_schema.tables 
WHERE table_schema = 'production';

采样页数默认20，大表可调高至200-500以提升统计精度。统计信息过期会导致rows预估偏差，直接影响JOIN顺序选择和索引选择。配合pt-index-usage-tool定期分析索引使用率，清理冗余索引——每个多余索引增加写入开销和存储成本。

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

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

相关推荐

MySQL慢查询诊断与索引优化实战：EXPLAIN执行计划解读指南

小编 • • 数据库

MySQL慢查询定位与开启配置

MySQL性能调优的第一步是发现慢查询。慢查询日志记录所有执行时间超过阈值的SQL语句，是数据库运维中定位性能瓶颈的核心工具。生产环境慢查询阈值建议设为1秒，开发环境可设为0.1秒捕获更多待优化SQL。

查看和开启慢查询日志：

-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 动态开启（重启失效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL min_examined_row_limit = 100;

-- 持久化配置（my.cnf）
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
min_examined_row_limit = 100

log_queries_not_using_indexes开启后，未使用索引的查询即使执行时间未超过阈值也会被记录。min_examined_row_limit过滤扫描行数过少的查询，减少日志噪声。

使用mysqldumpslow工具聚合分析慢日志，按总耗时排序找出最需要优化的SQL：

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

# 按返回行数排序
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log

# 按次数排序
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# 输出示例
# Count: 245  Time=3.12s (764s)  Lock=0.01s (2s)  Rows=15000.0 (3675000)
# SELECT * FROM orders WHERE user_id = # AND status = 'S' ORDER BY created_at DESC

Count表示执行次数，Time为平均单次耗时，括号内为总耗时。上例中该SQL执行了245次，累计耗时764秒，每次返回15000行，是明显的优化目标。

EXPLAIN执行计划深度解读

定位到慢SQL后，使用EXPLAIN分析执行计划。EXPLAIN输出的12个字段中，重点关注type、key、rows、Extra四个字段。

EXPLAIN SELECT * FROM orders 
WHERE user_id = 10086 AND status = 'PAID' 
ORDER BY created_at DESC LIMIT 20;

各字段含义与判断标准：

type字段（访问类型，性能从好到差）：

system > const > eq_ref > ref > range > index > ALL

-- const: 主键或唯一索引等值查询，最多匹配1行
-- eq_ref: JOIN时被驱动表使用主键或唯一索引
-- ref: 非唯一索引等值查询
-- range: 索引范围扫描（BETWEEN, >, <, IN）
-- index: 全索引扫描，遍历整棵索引树
-- ALL: 全表扫描，性能最差，必须优化

type为ALL或index时需要重点优化。ref和range是生产环境常见且可接受的访问类型。

key字段：实际使用的索引名称。NULL表示未使用索引。key_len字段表示索引使用的字节数，可用于判断联合索引用了几个字段。

rows字段：MySQL预估需要扫描的行数。rows越小越好，理想值接近LIMIT值。

Extra字段（附加信息）：

-- Using index: 覆盖索引，无需回表，最优
-- Using where: 通过WHERE条件过滤
-- Using temporary: 使用临时表，常见于GROUP BY、DISTINCT
-- Using filesort: 额外排序操作，需关注
-- Using join buffer: JOIN使用块嵌套循环，缺索引信号

Using temporary和Using filesort同时出现时，SQL性能通常较差，需要通过索引优化消除排序和临时表。

索引优化实战：联合索引设计与最左前缀原则

SQL查询优化的核心手段是建立合适的索引。联合索引遵循最左前缀匹配原则，字段顺序决定了索引的可用范围。设计联合索引时，将区分度高的字段放前面，等值查询字段放前面，范围查询字段放后面。

以上文慢查询为例，分析索引选择：

-- 原始查询
SELECT * FROM orders 
WHERE user_id = 10086 AND status = 'PAID' 
ORDER BY created_at DESC LIMIT 20;

-- 查看现有索引
SHOW INDEX FROM orders;

-- 方案一：创建联合索引
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);

-- 验证执行计划
EXPLAIN SELECT * FROM orders 
WHERE user_id = 10086 AND status = 'PAID' 
ORDER BY created_at DESC LIMIT 20;
-- 预期结果: type=ref, key=idx_user_status_created, 
-- Extra=Using index condition, 无filesort

联合索引(user_id, status, created_at)的设计逻辑：user_id等值查询放第一列，status等值查询放第二列，created_at用于ORDER BY排序放第三列。由于B+Tree索引有序，created_at在等值条件后天然有序，避免filesort。

常见索引失效场景诊断：

-- 1. 函数操作导致索引失效
-- 错误：WHERE DATE(created_at) = '2026-07-23'
-- 正确：WHERE created_at >= '2026-07-23' AND created_at < '2026-07-24'

-- 2. 隐式类型转换
-- 错误：user_id字段为VARCHAR，查询 WHERE user_id = 10086
-- 正确：WHERE user_id = '10086'

-- 3. LIKE以通配符开头
-- 错误：WHERE product_name LIKE '%手机%'
-- 正确：WHERE product_name LIKE '华为%'

-- 4. OR连接非索引列
-- 错误：WHERE user_id = 10086 OR product_name = 'iPhone'
-- 正确：为product_name建索引，或拆分为UNION查询

-- 5. 联合索引跳过中间列
-- 索引(a,b,c)，查询 WHERE a=1 AND c=3 不使用b列
-- 只有a走索引，c需要回表过滤

分库分表方案：ShardingSphere水平拆分配置

当单表数据量超过1000万行，B+Tree索引层级增加导致查询性能下降，数据库高可用架构中需要引入分库分表。Apache ShardingSphere-JDBC在应用层透明地完成SQL路由，对业务代码零侵入。

以订单表按user_id取模分表为例，Spring Boot配置：

spring:
  shardingsphere:
    datasource:
      names: ds0,ds1
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://db0:3306/order_db
        username: root
        password: 'password'
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://db1:3306/order_db
        username: root
        password: 'password'
    rules:
      sharding:
        tables:
          orders:
            actual-data-nodes: ds${0..1}.orders_${0..3}
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: db-mod
            table-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: table-mod
        sharding-algorithms:
          db-mod:
            type: MOD
            props:
              sharding-count: 2
          table-mod:
            type: MOD
            props:
              sharding-count: 4

上述配置将orders表拆分到2个数据库共8张物理表，分片键为user_id。ShardingSphere自动将SELECT * FROM orders WHERE user_id = 10086路由到对应物理表，业务层无感知。

分库分表后跨分片查询需要特殊处理：不带分片键的查询会广播到所有分片，性能较差。数据迁移实战中，建议先双写新旧表，逐步切换读流量，最后删除旧表。NoSQL选型应用方面，高频查询的非结构化数据可同步到Redis缓存或Elasticsearch，减轻MySQL查询压力。国产数据库如OceanBase和TiDB原生支持分布式架构，在高并发场景下可作为MySQL的替代方案，避免应用层分库分表的复杂性。

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

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

相关推荐