MySQL性能调优实战：慢查询定位、索引优化与执行计划深度解读

小编 • • 数据库

MySQL性能调优的切入点

数据库性能问题80%来自慢查询，慢查询的80%来自缺失或无效的索引。这条经验法则在实际运维中反复验证。MySQL性能调优不是调几个参数就能解决的，而是需要从慢查询定位、执行计划分析、索引设计三个环节系统推进。这篇实战指南以问题诊断为导向，给出可直接操作的调优路径。

慢查询定位：从慢日志到Performance Schema

开启慢查询日志是第一步。在my.cnf中配置：

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5          # 超过500ms记录
log_queries_not_using_indexes = 1  # 未使用索引的查询也记录
min_examined_row_limit = 100  # 扫描行数低于100的不记录

用mysqldumpslow统计最耗时的TOP10慢查询：

mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

输出按总耗时排序，关注Rows_examined远大于Rows_sent的查询——这是索引缺失的典型信号。

MySQL 8.0+可使用Performance Schema的events_statements_summary_by_digest表做更精细的分析：

SELECT
  DIGEST_TEXT AS query,
  COUNT_STAR AS exec_count,
  SUM_TIMER_WAIT / 1000000000 AS total_time_sec,
  AVG_TIMER_WAIT / 1000000000 AS avg_time_sec,
  SUM_ROWS_EXAMINED AS rows_examined,
  SUM_ROWS_SENT AS rows_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

EXPLAIN执行计划深度解读

拿到慢查询SQL后，EXPLAIN是分析的第一步。关键字段解读：

type列（访问类型，从优到差）：

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

出现ALL表示全表扫描，必须优化。index是全索引扫描，比ALL略好但仍然低效。range及以上是合理的访问类型。

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

rows列：预估扫描行数。这个值与实际行数可能有偏差，但数量级趋势是可靠的。

Extra列：额外信息，高频出现的值：

-- Using index: 覆盖索引，无需回表，最优情况
-- Using where: Server层过滤，存储引擎返回了过多数据
-- Using filesort: 额外排序，需优化
-- Using temporary: 使用临时表，常见于GROUP BY无索引
-- Using index condition: 索引条件下推(ICP)，是好事

实战案例——一个慢查询的调优过程：

-- 原始查询：订单表按用户ID和创建时间范围查询
SELECT * FROM orders
WHERE user_id = 10086
  AND created_at BETWEEN '2026-07-01' AND '2026-07-31'
ORDER BY created_at DESC
LIMIT 20;

-- EXPLAIN结果：
-- type: ALL, rows: 2800000, Extra: Using where; Using filesort
-- 全表扫描 + 额外排序，性能灾难

创建复合索引：

ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at);

-- 优化后EXPLAIN：
-- type: ref, key: idx_user_created, rows: 1500
-- Extra: Using index condition; Backward index scan
-- 扫描行数从280万降到1500，排序利用索引有序性消除filesort

索引设计原则与常见陷阱

1. 最左前缀原则：复合索引(a, b, c)可以覆盖a、(a, b)、(a, b, c)的查询，但不覆盖b或(b, c)的查询。索引列顺序按区分度从高到低排列。

2. 避免索引列做函数运算：

-- 无法使用索引
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
-- 改为范围查询，走索引
SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

3. 隐式类型转换导致索引失效：user_id是varchar类型，查询条件WHERE user_id = 10086会触发隐式转换，索引失效。必须WHERE user_id = '10086'。

4. 覆盖索引减少回表：如果查询只需要索引中的列，MySQL直接从索引返回数据，无需回表查主键。将SELECT的列控制在索引覆盖范围内：

-- 覆盖索引查询，Extra: Using index
SELECT user_id, created_at, status FROM orders
WHERE user_id = 10086;
-- 对应索引: idx_user_created_status (user_id, created_at, status)

InnoDB Buffer Pool调优

除了索引优化，Buffer Pool大小是影响查询性能的全局参数。建议设置为物理内存的60-75%：

[mysqld]
innodb_buffer_pool_size = 12G           # 16GB内存的服务器
innodb_buffer_pool_instances = 4         # 多实例减少锁争用
innodb_old_blocks_time = 1000           # 防止全表扫描冲掉热数据

监控Buffer Pool命中率：Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads)，低于99%说明Pool不够大或存在大量冷数据扫描。

原创文章，作者：小编，如若转载，请注明出处：https://www.yunthe.com/mysql-xing-neng-diao-you-shi-zhan-man-cha-xun-ding-wei-suo/

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

相关推荐

MySQL性能调优实战：慢查询定位、索引策略与执行计划深度解读

小编 • • 数据库

慢查询日志配置与自动化分析

性能调优的第一步是找到瓶颈。MySQL慢查询日志是最直接的性能诊断工具，但默认未开启。

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;  -- 超过500ms记录
SET GLOBAL log_queries_not_using_indexes = ON;  -- 未用索引的查询也记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 确认生效
SHOW VARIABLES LIKE 'slow_query%';

生产环境不建议长期开启全量慢查询日志，会影响IO。建议在排查窗口期临时开启，用pt-query-digest分析。

# 用Percona Toolkit分析慢查询
pt-query-digest /var/log/mysql/slow.log --limit 95% --outliers F=3,S=1M

# 输出样例：
# Rank Query ID       Response time  Calls  R/Call  V/M
# ==== ============= ============== ====== ======= ====
#    1 0x5A1B2C3D...  1254.5 62.3%   3451   0.364  0.12
#    2 0x7E8F9A0B...   432.1 21.5%    892   0.484  0.05

Rank 1的查询贡献了62.3%的响应时间，这就是优化重点。拿到Query ID后回slow.log找原始SQL。

EXPLAIN执行计划深度解读

拿到慢SQL后，用EXPLAIN分析执行计划。很多人只看type列，这不够，每一列都有含义。

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

关键列解读：

type：访问类型，从优到差：system > const > eq_ref > ref > range > index > ALL。出现ALL意味着全表扫描，必须优化。

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

rows：预估扫描行数。这个值×filtered百分比才是实际返回行数的参考。

Extra：重点关注以下几种：
– Using filesort：排序未走索引，需要额外排序操作
– Using temporary：使用了临时表，常见于GROUP BY无索引场景
– Using index condition：ICP下推，是好事
– Using where; Using join buffer：关联查询没有索引，依赖缓冲区处理

索引设计策略：避免无效索引

不是加了索引就能加速。以下几种情况索引无效：

1. 对索引列使用函数或表达式

-- 无效：对created_at使用函数
SELECT * FROM orders WHERE DATE(created_at) = '2026-07-23';
-- 有效：范围查询
SELECT * FROM orders WHERE created_at >= '2026-07-23' AND created_at < '2026-07-24';

2. 联合索引的最左前缀原则

-- 索引：idx_user_status_created (user_id, status, created_at)
-- 能走索引
WHERE user_id = 1
WHERE user_id = 1 AND status = 'PAID'
WHERE user_id = 1 AND status = 'PAID' AND created_at > '2026-07-01'

-- 不能走索引（跳过了user_id）
WHERE status = 'PAID'
WHERE status = 'PAID' AND created_at > '2026-07-01'

3. 隐式类型转换导致索引失效

-- 如果user_id是varchar类型
-- 无效：传入整数，MySQL做隐式转换
SELECT * FROM users WHERE user_id = 12345;
-- 有效：传入字符串
SELECT * FROM users WHERE user_id = '12345';

分页查询优化：告别OFFSET

深度分页（OFFSET 100000 LIMIT 20）的问题是MySQL要扫描前100020行然后丢弃前100000行。

-- 慢：传统OFFSET分页
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

-- 快：游标分页（适合连续翻页场景）
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;

-- 折中方案：子查询延迟关联
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 100000, 20
) t ON o.id = t.id;

子查询方案只走覆盖索引查id，再用id回表取完整数据，IO量大幅减少。

线上索引变更的安全生产流程

线上加索引会锁表（MySQL 5.6之前）或消耗大量IO（Online DDL期间）。安全做法：

# 1. 使用pt-online-schema-change无锁加索引
pt-online-schema-change   --alter "ADD INDEX idx_status_created (status, created_at)"   --execute   --max-load=Threads_running=100   --critical-load=Threads_running=200   --chunk-size=1000   D=production,t=orders

# 2. 或使用gh-ost（GitHub的方案）
gh-ost   --user=root --password=xxx   --host=127.0.0.1   --database=production   --table=orders   --alter="ADD INDEX idx_status_created (status, created_at)"   --allow-on-master   --chunk-size=1000   --execute

两个工具的核心原理相同：创建影子表 → 增量同步数据 → 同步完成后原子切换。全程不锁原表。建议在低峰期执行，同时设置max-load阈值——如果主库压力超过阈值自动暂停。

原创文章，作者：小编，如若转载，请注明出处：https://www.yunthe.com/mysql-xing-neng-diao-you-shi-zhan-man-cha-xun-ding-wei-suo/

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

相关推荐

MySQL性能调优实战：慢查询定位、索引策略与执行计划深度解读

小编 • • 数据库

慢查询日志配置与自动化分析

性能调优的第一步是找到瓶颈。MySQL慢查询日志是最直接的性能诊断工具，但默认未开启。

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;  -- 超过500ms记录
SET GLOBAL log_queries_not_using_indexes = ON;  -- 未用索引的查询也记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 确认生效
SHOW VARIABLES LIKE 'slow_query%';

生产环境不建议长期开启全量慢查询日志，会影响IO。建议在排查窗口期临时开启，用pt-query-digest分析。

# 用Percona Toolkit分析慢查询
pt-query-digest /var/log/mysql/slow.log --limit 95% --outliers F=3,S=1M

# 输出样例：
# Rank Query ID       Response time  Calls  R/Call  V/M
# ==== ============= ============== ====== ======= ====
#    1 0x5A1B2C3D...  1254.5 62.3%   3451   0.364  0.12
#    2 0x7E8F9A0B...   432.1 21.5%    892   0.484  0.05

Rank 1的查询贡献了62.3%的响应时间，这就是优化重点。拿到Query ID后回slow.log找原始SQL。

EXPLAIN执行计划深度解读

拿到慢SQL后，用EXPLAIN分析执行计划。很多人只看type列，这不够，每一列都有含义。

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

关键列解读：

type：访问类型，从优到差：system > const > eq_ref > ref > range > index > ALL。出现ALL意味着全表扫描，必须优化。

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

rows：预估扫描行数。这个值×filtered百分比才是实际返回行数的参考。

Extra：重点关注以下几种：
– Using filesort：排序未走索引，需要额外排序操作
– Using temporary：使用了临时表，常见于GROUP BY无索引场景
– Using index condition：ICP下推，是好事
– Using where; Using join buffer：关联查询没有索引，依赖缓冲区处理

索引设计策略：避免无效索引

不是加了索引就能加速。以下几种情况索引无效：

1. 对索引列使用函数或表达式

-- 无效：对created_at使用函数
SELECT * FROM orders WHERE DATE(created_at) = '2026-07-23';
-- 有效：范围查询
SELECT * FROM orders WHERE created_at >= '2026-07-23' AND created_at < '2026-07-24';

2. 联合索引的最左前缀原则

-- 索引：idx_user_status_created (user_id, status, created_at)
-- 能走索引
WHERE user_id = 1
WHERE user_id = 1 AND status = 'PAID'
WHERE user_id = 1 AND status = 'PAID' AND created_at > '2026-07-01'

-- 不能走索引（跳过了user_id）
WHERE status = 'PAID'
WHERE status = 'PAID' AND created_at > '2026-07-01'

3. 隐式类型转换导致索引失效

-- 如果user_id是varchar类型
-- 无效：传入整数，MySQL做隐式转换
SELECT * FROM users WHERE user_id = 12345;
-- 有效：传入字符串
SELECT * FROM users WHERE user_id = '12345';

分页查询优化：告别OFFSET

深度分页（OFFSET 100000 LIMIT 20）的问题是MySQL要扫描前100020行然后丢弃前100000行。

-- 慢：传统OFFSET分页
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

-- 快：游标分页（适合连续翻页场景）
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;

-- 折中方案：子查询延迟关联
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 100000, 20
) t ON o.id = t.id;

子查询方案只走覆盖索引查id，再用id回表取完整数据，IO量大幅减少。

线上索引变更的安全生产流程

线上加索引会锁表（MySQL 5.6之前）或消耗大量IO（Online DDL期间）。安全做法：

# 1. 使用pt-online-schema-change无锁加索引
pt-online-schema-change   --alter "ADD INDEX idx_status_created (status, created_at)"   --execute   --max-load=Threads_running=100   --critical-load=Threads_running=200   --chunk-size=1000   D=production,t=orders

# 2. 或使用gh-ost（GitHub的方案）
gh-ost   --user=root --password=xxx   --host=127.0.0.1   --database=production   --table=orders   --alter="ADD INDEX idx_status_created (status, created_at)"   --allow-on-master   --chunk-size=1000   --execute

两个工具的核心原理相同：创建影子表 → 增量同步数据 → 同步完成后原子切换。全程不锁原表。建议在低峰期执行，同时设置max-load阈值——如果主库压力超过阈值自动暂停。

原创文章，作者：小编，如若转载，请注明出处：https://www.yunthe.com/mysql-xing-neng-diao-you-shi-zhan-man-cha-xun-ding-wei-suo/

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

相关推荐