拾页偶有所得，随手记下
首页/笔记

MySQL 调优

MySQL / SQL 调优 / 执行计划2026.09.1430 分钟阅读

慢 SQL、索引、分页、锁等待和连接池调优。

查找慢 SQL

先从慢日志和请求追踪找到耗时较高的 SQL。

按总耗时和调用次数寻找候选语句

SELECT SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR,
       ROUND(SUM_TIMER_WAIT / 1e12, 3) AS total_s,
       ROUND(AVG_TIMER_WAIT / 1e9, 3) AS avg_ms,
       SUM_ROWS_EXAMINED, SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = DATABASE() AND DIGEST IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

选中业务库后查询 Performance Schema。计时单位为皮秒，统计值为累计值，观察当前负载时取两次快照的增量。

结合慢日志取回参数和调用次数。相同 SQL 在不同租户上的数据量可能相差很大，执行很快但调用频繁的 SQL 也值得检查。

建立可复现的订单测试数据

SELECT VERSION(), @@session.time_zone, @@transaction_isolation;
-- 以下表结构由下载的初始化脚本创建，不要重复执行
CREATE TABLE tuning_orders (
  id BIGINT NOT NULL PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  customer_id BIGINT NOT NULL,
  status TINYINT NOT NULL,
  created_at DATETIME NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  note VARCHAR(500) NOT NULL
) ENGINE=InnoDB;

初始化脚本创建十万条订单、十个租户和一万条客户记录。订单表初始只有主键，客户表主键为 (tenant_id, id)。同一租户有重复的创建时间，用于检查分页。

下载：测试表与合成数据初始化 SQL

下载：列表、日期、分页与关联的执行计划对照 SQL

在空测试库中导入初始化脚本，再执行对照脚本或逐条操作。实验使用 MySQL 8.0.45、1 个 CPU、1 GiB 容器内存和 128 MiB buffer pool。

同时记录计划、实际工作量与结果

EXPLAIN SELECT id, created_at
FROM tuning_orders
WHERE tenant_id = 7 AND status = 1
ORDER BY created_at DESC, id DESC
LIMIT 20;

EXPLAIN ANALYZE SELECT id, created_at
FROM tuning_orders
WHERE tenant_id = 7 AND status = 1
ORDER BY created_at DESC, id DESC
LIMIT 20;

EXPLAIN 查看估计计划，EXPLAIN ANALYZE 执行查询并显示实际结果。重点看 rows、loops 和耗时；多次循环节点的 rows 与时间是每次循环的平均值。

下面对比执行计划中的实际读取行数。

参考：语句摘要统计

参考：EXPLAIN 与 EXPLAIN ANALYZE

联合索引

按租户和状态筛选订单，再按创建时间倒序取 20 条。

检查过滤与排序之前读了多少行

只有主键时，计划先扫描 100000 行，筛出租户 7、状态 1 的 3333 行，再排序取 20 行。

调整前的实际节点（省略 cost 与时间）：
Limit                         rows=20     loops=1
  Sort                        rows=20     loops=1
    Filter                    rows=3333   loops=1
      Table scan              rows=100000 loops=1

Sort 输出 20 行，输入却有 3333 行。优化时要减少筛选和排序之前读取的记录。

为等值条件与排序建立联合索引

ALTER TABLE tuning_orders
  ADD INDEX idx_tenant_status_time (tenant_id, status, created_at, id),
  ALGORITHM=INPLACE, LOCK=NONE;

tenant_id 和 status 固定后，索引按 created_at、id 排列，倒序查询可以反向扫描。id 用于区分创建时间相同的记录。

id、created_at 都在索引中，可以覆盖查询。增加 amount 等返回列后，会产生回表访问。

复跑同一查询并检查写入代价

调整后的实际节点：
Limit                                      rows=20 loops=1
  Covering index lookup (reverse)           rows=20 loops=1
  index: idx_tenant_status_time
  lookup: tenant_id=7, status=1

建索引后，实际读取从 100000 行降到 20 行，显式排序也被消除。这个索引按 status 分组，不带 status 的查询需要另选索引。

参考：联合索引

参考：ORDER BY 优化

日期范围查询

在索引列上调用 DATE，会让查询先定位租户，再逐行计算日期。

建立时间索引并观察原查询

ALTER TABLE tuning_orders
  ADD INDEX idx_tenant_time (tenant_id, created_at, id),
  ALGORITHM=INPLACE, LOCK=NONE;

EXPLAIN ANALYZE SELECT COUNT(*)
FROM tuning_orders
WHERE tenant_id = 7 AND DATE(created_at) = '2026-09-14';

idx_tenant_time 读取该租户的 10000 行，再用 DATE 筛出 144 行。

改成左闭右开的时间范围

EXPLAIN ANALYZE SELECT COUNT(*)
FROM tuning_orders
WHERE tenant_id = 7
  AND created_at >= '2026-09-14 00:00:00'
  AND created_at <  '2026-09-15 00:00:00';

直接比较时间上下界，使用大于等于当天零点、小于次日零点的范围。TIMESTAMP 或跨时区日期需要先换算业务时区。

核对结果数量与扫描范围

对照项DATE 条件时间范围条件
COUNT(*) 结果144144
实际索引节点读取行数10000144
访问方式按租户查找后过滤租户与时间的覆盖范围扫描

COUNT(*) 仍需读取范围内符合条件的索引记录。

参考：范围访问

游标分页

LIMIT offset, size 会扫描并跳过前面的记录，偏移越大，读取越多。

观察大偏移量实际丢弃的记录

EXPLAIN ANALYZE SELECT id, created_at
FROM tuning_orders
WHERE tenant_id = 7
ORDER BY created_at DESC, id DESC
LIMIT 8000, 20;

返回 20 行，索引实际读取 8020 行，前 8000 行被跳过。

用上一页末条记录作为下一页边界

保存上一页最后一条的 created_at 和 id，作为下一页起点。下面使用排序后第 8000 条的值，与 OFFSET 查询对照。

EXPLAIN ANALYZE SELECT id, created_at
FROM tuning_orders
WHERE tenant_id = 7
  AND (
    created_at < '2026-08-14 21:00:00'
    OR (created_at = '2026-08-14 21:00:00' AND id < 20007)
  )
ORDER BY created_at DESC, id DESC
LIMIT 20;

时间相同时继续比较 id，避免漏掉同一时刻的记录。

验证页边界及产品语义

两种写法返回相同的 20 条记录，游标查询读取 20 行，OFFSET 读取 8020 行。

游标适合连续翻页。筛选条件和排序改变后，需要重新开始翻页。

参考：LIMIT 优化

参考：行构造器与范围优化

批量查询与关联

先取 20 条订单，再逐条查询客户，会产生 21 次数据库调用。

从请求追踪确认逐条查询

-- 第一次：取一页订单
SELECT id, customer_id, created_at
FROM tuning_orders
WHERE tenant_id = 7
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- 随后应用循环执行 20 次；这里的 334 是客户 ID 示例
SELECT name FROM tuning_customers
WHERE tenant_id = 7 AND id = 334;

从请求追踪中检查重复 SQL 和调用次数。

先确定订单页，再一次关联客户

EXPLAIN ANALYZE
SELECT o.id, o.created_at, c.name
FROM (
  SELECT id, tenant_id, customer_id, created_at
  FROM tuning_orders
  WHERE tenant_id = 7
  ORDER BY created_at DESC, id DESC
  LIMIT 20
) AS o
LEFT JOIN tuning_customers AS c
  ON c.tenant_id = o.tenant_id AND c.id = o.customer_id
ORDER BY o.created_at DESC, o.id DESC;

先取订单页，再按 tenant_id、id 关联客户。LEFT JOIN 保留没有客户记录的订单，客户主键保证每条订单最多匹配一位客户。

也可以收集这页的客户 ID，用 tenant_id 和 IN 一次查出，再在应用中映射。

同时检查调用次数与关联循环

关联计划查客户主键时 loops=20、rows=1，数据库内部仍访问 20 次索引，应用往返从 21 次降到 1 次。

比较接口总耗时、SQL 次数和返回数据。

参考：派生表优化

锁等待

按主键更新仍然卡住时，检查是否有其他事务持锁。

在测试库用两个会话复现锁等待

-- 会话 A：持有测试订单的行锁，暂不结束事务
START TRANSACTION;
SELECT id FROM tuning_orders WHERE id = 7 FOR UPDATE;
-- 完成会话 B 的观察后，在会话 A 执行 ROLLBACK;
-- 会话 B：独立连接；将本次测试的锁等待上限设为 5 秒
SET SESSION innodb_lock_wait_timeout = 5;
UPDATE tuning_orders SET amount = amount + 1 WHERE id = 7;

A 未释放锁时，B 会等待。观察后在 A 执行 ROLLBACK。

找到等待者与阻塞者

-- 在第三个观察连接中，于 B 等待期间执行
SELECT wait_started, wait_age, locked_table,
       waiting_pid, blocking_pid, waiting_query, blocking_query
FROM sys.innodb_lock_waits;

SELECT trx_mysql_thread_id, trx_started, trx_state, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

阻塞连接可能空闲，blocking_query 为 NULL，事务却仍持有锁。根据连接和事务时间找到对应请求，提交或回滚事务。元数据锁另查 metadata_locks。

缩短事务并用条件更新表达状态转换

SELECT FOR UPDATE 后调用远程接口，会在网络等待期间一直持锁。将可提前完成的计算移出事务，简单状态转换可以用条件更新。

-- 独立示例：在完整业务事务设计中使用
UPDATE tuning_orders
SET status = 1
WHERE id = 7 AND tenant_id = 7 AND status = 0;
SELECT ROW_COUNT() AS changed_rows;

影响 1 行表示完成 0 → 1 转换，0 行表示记录不存在或状态已变化。

复测并发冲突与回滚路径

比较锁持有时间和等待数量。事务按相同顺序访问记录可减少死锁；死锁后有限次重试整个事务，锁等待超时则先结束失败事务。

参考：InnoDB 锁等待视图

参考：死锁处理

参考：InnoDB 错误与回滚

连接池与缓冲池

高峰期排队时，检查连接池等待、活动连接和物理读取。

在同一观察窗口采集状态增量

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Threads_connected', 'Threads_running', 'Connections',
  'Max_used_connections', 'Aborted_connects',
  'Innodb_buffer_pool_read_requests', 'Innodb_buffer_pool_reads'
);
SHOW GLOBAL VARIABLES WHERE Variable_name IN (
  'max_connections', 'innodb_buffer_pool_size'
);

Threads_connected、Threads_running 是当前值，Max_used_connections 是峰值。读取次数取同一时间窗口的增量，Δreads / Δread_requests 可估算缓冲池未命中比例。

先控制连接预算，再评估内存调整

四个应用实例各开 40 个连接，总上限为 160。总预算为 80 时，每个实例可分配 20 个，还需给管理任务和其他服务留出连接。

调整后比较连接池等待、超时率、Threads_running 和吞吐量，同时检查连接泄漏与长事务。

物理读取较多且内存有余量时，可以调整 buffer pool。先记录原值，便于恢复。

-- 仅用于本笔记的独立测试实例，需要系统变量管理权限
SET @original_pool_bytes = @@global.innodb_buffer_pool_size;
SET GLOBAL innodb_buffer_pool_size = 268435456;
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_resize_status';
SELECT @@global.innodb_buffer_pool_size;
-- 等待调整完成，在相同负载下采集指标后恢复
SET GLOBAL innodb_buffer_pool_size = @original_pool_bytes;

比较整体表现而非单个命中率

检查实际生效的容量。缓存预热后再比较，SET GLOBAL 的修改需要另行持久化。

内存还要供连接、排序、临时表和操作系统使用。

参考：服务器状态变量

参考：buffer pool 在线调整

其他

估计行数与实际行数偏差较大

SHOW INDEX FROM tuning_orders;
ANALYZE TABLE tuning_orders;

先检查参数、数据倾斜和统计信息，再用 ANALYZE TABLE 更新统计并重跑查询。它会访问表并获取相应锁。

执行计划字段的快速对照

字段或标记看什么
type / key访问方式与实际索引；index 是全索引扫描，key 非空不代表读得少
rows / filtered估计读取量与过滤比例，结合连接顺序及实际计划
actual rows / loops节点每次循环的平均输出行数与循环次数，向下查看真实读取节点
Using index覆盖访问；不要与仍可能回表的 Using index condition 混淆
Using filesort / Using temporary额外排序或临时表，结合输入规模与是否落盘评价

评估索引上线、回退与写入影响

ALGORITHM=INPLACE、LOCK=NONE 在不支持相应方式时会报错。创建索引仍有元数据锁和资源开销，上线前检查长事务、磁盘空间和复制延迟。

将疑似冗余索引设为不可见，观察查询变化，再恢复可见进行对照。

ALTER TABLE tuning_orders ALTER INDEX idx_tenant_status_time INVISIBLE;
-- 复测相关查询；完成后恢复
ALTER TABLE tuning_orders ALTER INDEX idx_tenant_status_time VISIBLE;

不可见索引仍会随写入维护。评估删除后的写入收益，需要在测试库中实际删除并比较。

参考：ANALYZE TABLE

参考：EXPLAIN 输出

参考：在线 DDL 限制

参考：不可见索引

原理说明：B+ 树索引

— 本页完 —
← 返回笔记列表

搜索内容

输入关键词搜索文章、笔记与随笔。