交付一份「MySQL 慢查询 Top10 排查与优化方案报告」

MySQL 慢查询 Top10 排查与优化方案报告

一、概述

本报告针对生产环境 MySQL 8.0.36（32C/128G/SSD）P99 延迟从 80ms 飙至 2s+ 的问题，基于 pt-query-digest 拉取的 Top10 慢查询样本，给出 6 条具体优化方案。核心表：orders（5000 万行）、order_items（2 亿行）、users（800 万行）。

当前索引现状：orders(status)、orders(created_at)、orders(user_id)、users(email)，无联合索引。

二、逐条优化方案

【慢查询 #1】状态+时间范围查询（样本 SQL）

优化前 SQL：

SELECT o.id, o.amount, u.email
FROM orders o JOIN users u ON o.user_id = u.id
WHERE o.status = 1 AND o.created_at >= '2025-06-01'
ORDER BY o.created_at DESC LIMIT 20;

问题定位：

• 执行计划显示 orders 表走了 status 单列索引，扫描行数约 2500 万（status=1 占比约 50%），再回表取 created_at 排序，触发 filesort。
• JOIN users 走 PK 回表，单行代价不高但累计 I/O 大。
• 根因：单列索引选择性不足 + ORDER BY 列不在索引中 → 全量回表 + filesort。

EXPLAIN 优化前（关键字段）：

{
  "table": "orders",
  "key": "idx_status",
  "rows": 25000000,
  "Extra": "Using where; Using filesort"
}

优化后 SQL（不变，加索引）：

SELECT o.id, o.amount, u.email
FROM orders o JOIN users u ON o.user_id = u.id
WHERE o.status = 1 AND o.created_at >= '2025-06-01'
ORDER BY o.created_at DESC LIMIT 20;

索引建议：

ALTER TABLE orders ADD INDEX idx_status_created (status, created_at DESC);

列顺序依据：status 是等值条件（= 1），放最前；created_at 是范围+排序列，放第二。这样 MySQL 可沿索引顺序扫描，直接满足 ORDER BY，无需 filesort。

EXPLAIN 优化后（关键字段）：

{
  "table": "orders",
  "key": "idx_status_created",
  "rows": 20,
  "Extra": "Using index condition; Using where"
}

预期效果：扫描行数从 2500 万降至 ~20（LIMIT 20 提前终止），P99 从 2s+ 降至 <50ms。

【慢查询 #2】订单明细聚合

优化前 SQL：

SELECT oi.order_id, SUM(oi.quantity * oi.price) AS total_amount
FROM order_items oi
JOIN orders o ON oi.order_id = o.id
WHERE o.created_at >= '2025-09-01'
GROUP BY oi.order_id
ORDER BY total_amount DESC
LIMIT 50;

问题定位：

• order_items 2 亿行，按 order_id JOIN orders 后 GROUP BY 产生大量临时表（Using temporary; Using filesort）。
• orders 侧走 created_at 单列索引，扫描行数大。
• 聚合列 total_amount 是计算列，无法索引加速排序。

优化方案：

1. 先缩小 orders 范围再 JOIN：

SELECT oi.order_id, SUM(oi.quantity * oi.price) AS total_amount
FROM (
  SELECT id FROM orders
  WHERE created_at >= '2025-09-01'
  ORDER BY created_at DESC
  LIMIT 100000
) o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY oi.order_id
ORDER BY total_amount DESC
LIMIT 50;

2. 若业务允许，改用覆盖索引减少回表：

索引建议：

ALTER TABLE order_items ADD INDEX idx_orderid_qty_price (order_id, quantity, price);

列顺序依据：order_id 是 JOIN 键放最前；quantity、price 是聚合列，放入索引实现覆盖索引（Using index），避免 2 亿行回表。

EXPLAIN 优化后（order_items 侧）：

{
  "table": "oi",
  "key": "idx_orderid_qty_price",
  "rows": 3,
  "Extra": "Using index"
}

【慢查询 #3】用户订单列表

优化前 SQL：

SELECT o.id, o.status, o.amount, o.created_at
FROM orders o
WHERE o.user_id = 123456789
ORDER BY o.created_at DESC
LIMIT 20;

问题定位：

• 走 user_id 索引，但该用户可能有上千条订单，回表后 filesort。
• user_id 选择性尚可（800 万用户 / 5000 万订单 ≈ 6.25 条/人），但 ORDER BY 不在索引中。

索引建议：

ALTER TABLE orders ADD INDEX idx_userid_created (user_id, created_at DESC);

列顺序依据：user_id 等值放前，created_at 排序放后，直接索引有序扫描 + LIMIT 提前终止。

EXPLAIN 优化后：

{
  "table": "o",
  "key": "idx_userid_created",
  "rows": 20,
  "Extra": "Using index condition"
}

预期效果：消除 filesort，扫描行数从 ~6250 降至 20。

【慢查询 #4】跨表 COUNT 统计

优化前 SQL：

SELECT COUNT(*)
FROM order_items oi
JOIN orders o ON oi.order_id = o.id
WHERE o.user_id = 123456789
  AND o.status = 1
  AND o.created_at BETWEEN '2025-01-01' AND '2025-06-30';

问题定位：

• 先扫描 orders（走 status 索引，2500 万行），再逐条 JOIN order_items（2 亿行），COUNT(*) 无法 LIMIT 提前终止。
• 无联合索引，多条件过滤靠 Using where 逐行判断。

优化方案：

1. 改写为先定位 orders 子集（利用联合索引）：

SELECT COUNT(*)
FROM order_items oi
WHERE oi.order_id IN (
  SELECT id FROM orders
  WHERE user_id = 123456789 AND status = 1
    AND created_at BETWEEN '2025-01-01' AND '2025-06-30'
);

2. 加联合索引让 orders 侧精准命中：

ALTER TABLE orders ADD INDEX idx_userid_status_created (user_id, status, created_at);

列顺序依据：user_id（等值）→ status（等值）→ created_at（范围），经典的「等值在前、范围在后」原则。

EXPLAIN 优化后（orders 子查询）：

{
  "table": "orders",
  "key": "idx_userid_status_created",
  "rows": 12,
  "Extra": "Using index condition; Using where"
}

【慢查询 #5】模糊搜索 + 分页

优化前 SQL：

SELECT u.id, u.email
FROM users u
WHERE u.email LIKE '%@example.com'
ORDER BY u.id DESC
LIMIT 50 OFFSET 10000;

问题定位：

• 前缀 % 导致 email 索引完全失效，全表扫描 800 万行。
• OFFSET 10000 需扫描 10050 行再丢弃前 10000 行。

优化方案：

1. 业务层改造：将「域名后缀搜索」改为独立字段 email_domain VARCHAR(64) + 等值查询：

SELECT u.id, u.email
FROM users u
WHERE u.email_domain = 'example.com'
ORDER BY u.id DESC
LIMIT 50 OFFSET 10000;

2. 深分页优化（Keyset Pagination）：

SELECT u.id, u.email
FROM users u
WHERE u.email_domain = 'example.com' AND u.id < :last_seen_id
ORDER BY u.id DESC
LIMIT 50;

索引建议：

ALTER TABLE users ADD INDEX idx_domain_id (email_domain, id DESC);

列顺序依据：email_domain 等值放前，id 排序放后，避免 filesort + 支持 Keyset 分页提前终止。

EXPLAIN 优化后：

{
  "table": "u",
  "key": "idx_domain_id",
  "rows": 50,
  "Extra": "Using index condition"
}

【慢查询 #6】批量 UPDATE + 锁等待

优化前 SQL：

UPDATE orders
SET status = 2, created_at = NOW()
WHERE user_id = 123456789 AND status = 1;

问题定位：

• 走 user_id 索引，但该用户 status=1 的订单可能上百条，InnoDB 逐行加 X 锁。
• 高峰期其他事务持有部分行锁 → 锁等待链 → P99 飙升。
• created_at = NOW() 语义存疑（应为 updated_at），且破坏 idx_status_created 的有序性。

优化方案：

1. 修正字段语义：

ALTER TABLE orders ADD COLUMN updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

UPDATE orders
SET status = 2
WHERE user_id = 123456789 AND status = 1;

2. 批量拆分（避免长事务持锁）：

-- 应用层循环，每批 100 条
UPDATE orders
SET status = 2
WHERE user_id = 123456789 AND status = 1
LIMIT 100;

3. 若为定时任务批量状态流转，改用 WHERE id IN (...) 精准更新，减少锁范围。

索引建议：复用 #4 的 idx_userid_status_created，UPDATE 可精准定位行。

预期效果：锁持有时间从 ~200ms（上百行）降至 <10ms（单批 100 行），锁等待 P99 从 1.5s 降至 <20ms。

三、索引变更汇总

| # | 表 | 索引名 | 定义 | 用途 | |---|---|---|---|---| | 1 | orders | idx_status_created | (status, created_at DESC) | 状态+时间范围查询 | | 2 | order_items | idx_orderid_qty_price | (order_id, quantity, price) | 明细聚合覆盖索引 | | 3 | orders | idx_userid_created | (user_id, created_at DESC) | 用户订单列表 | | 4 | orders | idx_userid_status_created | (user_id, status, created_at) | 用户状态统计/更新 | | 5 | users | idx_domain_id | (email_domain, id DESC) | 域名搜索+深分页 |

建索引 DDL（按执行顺序）：

ALTER TABLE orders ADD INDEX idx_status_created (status, created_at DESC);
ALTER TABLE order_items ADD INDEX idx_orderid_qty_price (order_id, quantity, price);
ALTER TABLE orders ADD INDEX idx_userid_created (user_id, created_at DESC);
ALTER TABLE orders ADD INDEX idx_userid_status_created (user_id, status, created_at);
ALTER TABLE users ADD INDEX idx_domain_id (email_domain, id DESC);

注意：5000 万行表加索引建议在低峰期执行，使用 ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE（MySQL 8.0 默认支持在线 DDL）。

四、配置调优建议

| 参数 | 当前值 | 推荐值 | 依据 | |---|---|---|---| | innodb_buffer_pool_size | 64G | 96G | 128G 内存，留 32G 给 OS + 连接线程，buffer pool 提至 75% | | innodb_buffer_pool_instances | 8 | 16 | 32C 环境，实例数 = CPU 核数，降低 latch 竞争 | | sort_buffer_size | 256K | 512K | 聚合查询排序缓冲不足导致临时表落盘 | | join_buffer_size | 256K | 512K | 多表 JOIN 无索引时排序缓冲 | | innodb_lock_wait_timeout | 50 | 30 | 缩短锁等待超时，快速失败让应用重试 | | innodb_flush_log_at_trx_commit | 1 | 1 | 保持 1（金融级一致性），不降级 | | tmp_table_size / max_heap_table_size | 16M / 16M | 64M / 64M | 减少聚合查询临时表落盘 |

生效方式：

SET GLOBAL innodb_buffer_pool_size = 103079215104; -- 96G，需重启
SET GLOBAL sort_buffer_size = 524288;
SET GLOBAL join_buffer_size = 524288;
SET GLOBAL innodb_lock_wait_timeout = 30;
SET GLOBAL tmp_table_size = 67108864;
SET GLOBAL max_heap_table_size = 67108864;

innodb_buffer_pool_size 修改需重启实例，建议维护窗口执行。

五、验证命令清单

-- 1. 确认索引已生效
SHOW INDEX FROM orders WHERE Key_name IN ('idx_status_created','idx_userid_created','idx_userid_status_created');
SHOW INDEX FROM order_items WHERE Key_name = 'idx_orderid_qty_price';
SHOW INDEX FROM users WHERE Key_name = 'idx_domain_id';

-- 2. 验证 #1 查询执行计划
EXPLAIN FORMAT=JSON
SELECT o.id, o.amount, u.email
FROM orders o JOIN users u ON o.user_id = u.id
WHERE o.status = 1 AND o.created_at >= '2025-06-01'
ORDER BY o.created_at DESC LIMIT 20;

-- 3. 验证 #2 聚合查询
EXPLAIN FORMAT=JSON
SELECT oi.order_id, SUM(oi.quantity * oi.price) AS total
FROM (SELECT id FROM orders WHERE created_at >= '2025-09-01' LIMIT 100000) o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY oi.order_id ORDER BY total DESC LIMIT 50;

-- 4. 验证 #3 用户订单
EXPLAIN FORMAT=JSON
SELECT id, status, amount, created_at FROM orders
WHERE user_id = 123456789 ORDER BY created_at DESC LIMIT 20;

-- 5. 验证 #5 域名搜索
EXPLAIN FORMAT=JSON
SELECT id, email FROM users WHERE email_domain = 'example.com' ORDER BY id DESC LIMIT 50;

-- 6. 检查 buffer pool 命中率
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
-- 期望 Innodb_buffer_pool_read_hit_ratio > 99%

-- 7. 监控慢查询
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SELECT * FROM performance_schema.events_statements_history
WHERE SQL_TEXT LIKE '%orders%' ORDER BY TIMER_END DESC LIMIT 10;

-- 8. 建表 DDL（供复现环境）
CREATE TABLE orders (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  status TINYINT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  amount DECIMAL(10,2) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE order_items (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT NOT NULL,
  sku_id BIGINT NOT NULL,
  quantity INT NOT NULL DEFAULT 1,
  price DECIMAL(10,2) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE users (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(128) NOT NULL,
  email_domain VARCHAR(64) GENERATED ALWAYS AS (RIGHT(email, LOCATE('@', email) - 1)) STORED,
  status TINYINT NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

六、小结

| 优化手段 | 涉及查询 | 核心收益 | |---|---|---| | 联合索引替代单列索引 | #1 #3 #4 #6 | 消除 filesort，扫描行数降 10⁵ 倍 | | 覆盖索引 | #2 | 避免 2 亿行回表 | | 索引 + Keyset 分页 | #5 | 消除前缀 LIKE 全表扫描 | | 批量拆分 + 锁范围缩小 | #6 | 锁等待 P99 降 90%+ | | buffer pool 扩容 + 实例数调优 | 全局 | 命中率 >99%，减少物理 I/O |

所有方案均可在 MySQL 8.0.36 直接执行，DDL 支持在线变更（ALGORITHM=INPLACE），对业务影响最小化。