MySQL 8.0 性能调优实战笔记（最终完整版）

前言

在应用开发中，MySQL 作为最流行的开源关系型数据库，其性能直接影响系统的响应速度与可用性。本笔记从实际开发中遇到的常见性能问题出发，结合 MySQL 8.0 的新特性，系统讲解性能调优的思路、方法与最佳实践。内容覆盖查询优化、增删改优化、DDL 变更、连接管理、配置调优、故障排查等全链路场景，并包含高频面试考点与生产环境避坑指南，适合初学者入门及中级开发者进阶参考。

目录

1. 性能问题根源剖析

2. 查询优化篇

   • 深分页问题与解决方案
   • 多表关联与多表深分页
   • COUNT 查询的性能陷阱
   • ORDER BY + LIMIT 深层优化
   • GROUP BY 优化策略
   • DISTINCT vs GROUP BY
   • 子查询优化：派生表合并与物化
   • UNION 与 UNION ALL 的差异
   • SQL 通用优化技巧
   • EXPLAIN 与 EXPLAIN ANALYZE
   • MySQL 8.0 新特性应用

3. 索引设计最佳实践

   • 索引设计原则
   • 最左前缀与覆盖索引
   • 索引下推（ICP）与 MRR
   • MySQL 8.0 索引新特性
4. INSERT 操作性能调优

5. UPDATE / DELETE 操作性能调优

   • 死锁深度解析
   • 热点更新（秒杀场景）
6. 线上大表结构变更（Online DDL）
7. 数据库连接管理与连接池调优
8. 配置参数调优
9. 监控、诊断与故障排查

10. 高频陷阱与补充专题

    • 隐式类型转换深度案例
    • 大字段（TEXT/BLOB）优化策略
    • 字符集与排序规则的影响
    • 乐观锁与悲观锁选择
    • 事务隔离级别与 Binlog 格式
    • 分区表使用场景
11. 总结与思维导图

一、性能问题根源剖析

1.1 MySQL 执行一条查询的流程

客户端发送SQL → 连接器 → 查询缓存(8.0已弃用) → 解析器 → 优化器 → 执行器 → 存储引擎

优化器基于成本（CPU 和 I/O）选择执行计划，理解这一过程是调优的基础。

1.2 性能问题的主要类型

二、查询优化篇

2.1 深分页问题——从原理到解决方案

2.1.1 深分页为什么慢？

-- 获取第10001到10010条记录
SELECT * FROM t_order ORDER BY id LIMIT 10000, 10;

当 offset 较大时，MySQL 必须从头扫描 offset + N 条记录，然后丢弃前 offset 条，导致扫描行数呈线性增长。若依赖二级索引且不满足覆盖索引，还会发生大量无意义的回表随机 I/O。

2.1.2 方案一：书签分页/游标分页（最推荐）

用上一页最后一条数据的主键作为“书签”直接定位。

-- 优化后：假设上一页最后一条 id=100000
SELECT id, name, price FROM goods 
WHERE category = 1 AND id > 100000 
ORDER BY id LIMIT 20;

处理排序字段重复值：

-- 按创建时间倒序分页
SELECT id, name, price, create_time FROM goods 
WHERE category = 1 
  AND (create_time < '2026-01-15 18:30:00' 
    OR (create_time = '2026-01-15 18:30:00' AND id < 100000))
ORDER BY create_time DESC, id DESC 
LIMIT 20;

2.1.3 方案二：子查询/延迟关联

-- 阿里巴巴推荐写法
SELECT * FROM t_order 
WHERE id >= (
    SELECT id FROM t_order ORDER BY id LIMIT 1000000, 1
)
ORDER BY id LIMIT 10;

-- 或使用 JOIN
SELECT t1.* FROM t_order t1
INNER JOIN (
    SELECT id FROM t_order ORDER BY id LIMIT 100000, 10
) t2 ON t1.id = t2.id;

2.1.4 方案三：覆盖索引避免回表

-- 创建覆盖索引（包含查询所需的所有字段）
ALTER TABLE student ADD INDEX idx_age_name_id(age, name, id);
SELECT id, age, name FROM student WHERE age = 18 LIMIT 5000, 10;

2.1.5 方案四：阈值倒序法

当页码超过阈值时，使用倒序查询第一页。

SELECT * FROM t_order WHERE id > 0 ORDER BY id DESC LIMIT 20;

2.1.6 方案五：业务层面优化

• 限制最大页数（如100页）
• 搜索 + 过滤结合缩小数据范围
• 无限滚动代替跳页

2.2 多表关联查询与多表深分页

2.2.1 JOIN 性能陷阱

复杂 JOIN 超时的主要原因是优化器选错驱动表导致全表扫描、临时表和文件排序。

2.2.2 优化原则一：尽量转化为单表查询

先在一张表上完成过滤和分页，再用 ID 列表去其他表补充字段。

SELECT t1.*, t2.extra_field 
FROM tableA t1
JOIN (
    SELECT id FROM tableA 
    WHERE condition
    ORDER BY score DESC 
    LIMIT 100000, 20
) tmp ON t1.id = tmp.id
LEFT JOIN tableB t2 ON t1.id = t2.a_id;

2.2.3 优化原则二：小表驱动大表

使用 STRAIGHT_JOIN 强制指定驱动表。

SELECT * FROM small_table 
STRAIGHT_JOIN large_table ON small_table.id = large_table.small_id
WHERE small_table.status = 1;

2.2.4 多表深分页的解决方案

策略一：先分页后关联（同上）
策略二：使用 CTE（MySQL 8.0）

WITH paged_ids AS (
    SELECT id FROM tableA 
    WHERE condition 
    ORDER BY id LIMIT 100000, 20
)
SELECT a.*, b.extra_field 
FROM paged_ids p
JOIN tableA a ON p.id = a.id
LEFT JOIN tableB b ON a.id = b.a_id;

2.3 COUNT 查询的性能陷阱与优化

2.3.1 COUNT(*) 在 InnoDB 中的实现

InnoDB 没有维护表级行数计数器（因为 MVCC 下不同事务看到的行数不同），因此 SELECT COUNT(*) FROM large_table 会触发全索引扫描，大表上非常慢。

2.3.2 优化策略

策略一：使用近似值

-- 从统计信息获取估算行数（不精确但极快）
SELECT TABLE_ROWS FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'db' AND TABLE_NAME = 'orders';

策略二：使用计数表

CREATE TABLE order_stat (
    date DATE PRIMARY KEY,
    cnt INT NOT NULL
) ENGINE=InnoDB;

-- 插入订单时原子更新
INSERT INTO orders VALUES (...);
INSERT INTO order_stat VALUES (CURDATE(), 1) 
    ON DUPLICATE KEY UPDATE cnt = cnt + 1;

策略三：Redis 计数器（适用于高实时性场景）
策略四：覆盖索引加速 COUNT

CREATE INDEX idx_create_time ON orders(create_time);
SELECT COUNT(*) FROM orders WHERE create_time >= '2026-01-01';

2.3.3 COUNT(1) vs COUNT(*) vs COUNT(列)

• COUNT(*) 和 COUNT(1) 性能完全相同，优化器会转换为 COUNT(*)
• COUNT(col) 统计该列非 NULL 的行数

2.4 ORDER BY + LIMIT 的深层优化

2.4.1 排序缓冲区与优先队列

当排序无法使用索引时，MySQL 使用 sort_buffer_size 内存排序，超出则使用磁盘文件。对于带 LIMIT 的排序，MySQL 5.6+ 采用优先队列（Priority Queue）算法，只需维护一个大小为 N 的堆，无需全量排序，大幅降低内存和磁盘使用。

2.4.2 监控排序状态

SHOW STATUS LIKE '%sort%';

• Sort_merge_passes：外部归并排序次数（应尽量为0）
• Sort_range：范围扫描排序次数
• Sort_scan：全表扫描排序次数

若 Sort_merge_passes 持续增长，可适当增大 sort_buffer_size。

2.5 GROUP BY 优化策略

2.5.1 松散索引扫描（Loose Index Scan）

当 GROUP BY 列是索引最左前缀且 SELECT 仅包含 MIN/MAX 聚合函数时，MySQL 可仅扫描分组的第一行，性能极佳。

2.5.2 避免临时表和文件排序

EXPLAIN 中出现 Using temporary; Using filesort 是需要优化的信号。优化方法：创建满足 GROUP BY 和 ORDER BY 的复合索引，或使用窗口函数替代复杂分组子查询。

2.6 DISTINCT 与 GROUP BY 的选择

• 单列去重：两者性能相近
• 多列去重：GROUP BY 可能更快（可利用松散索引扫描）
• DISTINCT 会产生临时表做去重，大数据量时性能较差

建议：多列去重优先考虑 GROUP BY，并确保索引覆盖。

2.7 子查询优化：派生表合并与物化

MySQL 8.0 对子查询优化显著增强：

• 派生表合并：优化器尝试将 FROM 子句中的子查询展开并与外层合并，减少临时表。
• 物化：无法合并时，子查询结果存入临时表并自动创建索引加速 JOIN。

建议：能用 JOIN 解决的问题尽量用 JOIN，避免依赖优化器“黑盒”行为。

2.8 UNION 与 UNION ALL 的性能差异

• UNION ALL：直接合并结果集，不去重
• UNION：需额外临时表和排序去重，大数据量下比 UNION ALL 慢数倍

规则：除非确实需要去重，否则一律使用 UNION ALL。

2.9 SQL 语句通用优化技巧

2.9.1 避免索引失效

2.9.2 其他技巧

• 只查询需要的字段，避免 SELECT *
• 分页时先获取主键再关联详情
• 合理使用 IN 和 EXISTS（EXISTS 通常更快）

2.10 EXPLAIN 与 EXPLAIN ANALYZE

2.10.1 EXPLAIN 关键字段

2.10.2 EXPLAIN ANALYZE（MySQL 8.0.18+）

真实执行查询并报告每个操作的实际时间和行数。

EXPLAIN ANALYZE 
SELECT u.name, o.order_no 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.age > 25;

2.11 MySQL 8.0 新特性应用

2.11.1 窗口函数

-- 获取每个用户的最新订单
WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time DESC) AS rn
    FROM user_orders
)
SELECT * FROM ranked WHERE rn = 1;

2.11.2 公用表表达式（CTE）

WITH active_users AS (
    SELECT id, name FROM users WHERE status = 'active'
),
user_orders AS (
    SELECT u.id, COUNT(o.id) AS order_count
    FROM active_users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.id
)
SELECT * FROM user_orders WHERE order_count > 10;

三、索引设计最佳实践

3.1 索引设计原则

应该创建索引的列：

• WHERE 子句中的过滤条件列
• ORDER BY / GROUP BY 的列
• JOIN 关联字段

不应该创建索引的列：

• 低选择性的列（区分度低于10%）
• 高频更新的列
• 大字段（TEXT、BLOB）

3.2 最左前缀与覆盖索引

组合索引遵循最左前缀匹配，创建时需考虑查询模式。

CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time, id, order_no, amount);

覆盖索引：查询所需字段全部包含在索引中，无需回表。

3.3 索引下推（ICP）与 Multi-Range Read（MRR）

3.3.1 索引下推（ICP）

MySQL 5.6+ 默认开启。使用二级索引时，将索引列条件直接下推到存储引擎过滤，减少回表次数。EXPLAIN 中 Extra 显示 Using index condition。

3.3.2 MRR（Multi-Range Read）

将二级索引回表时的随机 I/O 优化为顺序 I/O。通过 optimizer_switch 控制。

SET optimizer_switch='mrr=on,mrr_cost_based=off';

3.4 MySQL 8.0 索引新特性

四、INSERT 操作性能调优

4.1 插入操作的成本构成

官方数据显示：单条插入的固定开销（连接、解析等）远大于数据写入本身。索引维护也是主要开销之一。

4.2 批量插入：最有效的优化手段

-- ❌ 单条循环插入
INSERT INTO orders(user_id, amount) VALUES (1, 100);

-- ✅ 多值批量插入（每批 500-1000 条）
INSERT INTO orders(user_id, amount) VALUES 
(1, 100), (2, 200), (3, 300), ...;

4.3 LOAD DATA：最快的数据导入方式

LOAD DATA LOCAL INFILE '/path/to/data.csv'
INTO TABLE orders
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
(user_id, amount, create_time);

4.4 事务优化：用 COMMIT 包裹批量操作

SET autocommit = 0;
-- 批量插入
COMMIT;
SET autocommit = 1;

4.5 按主键顺序插入（InnoDB 特有）

主键顺序插入可大幅减少页分裂，提升性能。

4.6 临时关闭约束检查（数据迁移场景）

SET unique_checks = 0;
SET foreign_key_checks = 0;
-- 执行大量 INSERT
SET unique_checks = 1;
SET foreign_key_checks = 1;

警告：仅在数据完整性已由业务保证时使用。

4.7 自增 ID 的批量申请机制

MySQL 8.0 批量插入时会一次性申请一批连续 ID，导致 ID 可能出现“断崖”，这是正常优化，不建议强行修复。

4.8 INSERT 优化速查表

五、UPDATE / DELETE 操作性能调优

5.1 无索引导致全表锁（严重事故）

-- ❌ 危险：user_id 没有索引
UPDATE orders SET status = 'expired' WHERE user_id = 100;
-- 结果：全表扫描 → 对所有行加锁 → 整个表被锁死

必须确保 WHERE 条件列有索引。

5.2 批量删除的正确姿势

-- ✅ 安全：分批删除
DELETE FROM logs WHERE create_time < '2025-01-01' LIMIT 1000;
-- 循环执行，每批之间 sleep 一下

5.3 死锁问题深度解析

5.3.1 死锁原因：锁顺序不一致

-- 事务A：先订单后库存
BEGIN;
UPDATE orders SET status = 'paid' WHERE id = 100;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 200;
COMMIT;

-- 事务B：先库存后订单（相反顺序！）
BEGIN;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 200;
UPDATE orders SET status = 'paid' WHERE id = 100;
COMMIT;

5.3.2 诊断死锁

开启死锁日志：

innodb_print_all_deadlocks = ON

查看锁状态：

SHOW ENGINE INNODB STATUS\G

5.3.3 避免死锁的策略

• 统一加锁顺序：所有事务按相同顺序访问表
• 尽量使用主键更新：减少辅助索引上的锁竞争
• 缩短事务时间：把非数据库操作移出事务
• 使用 SKIP LOCKED / NOWAIT（MySQL 8.0）

SELECT * FROM tasks WHERE status = 'pending' 
FOR UPDATE SKIP LOCKED LIMIT 10;

5.4 热点更新问题（秒杀场景）

5.4.1 应用层方案

• Redis 预扣库存 + 异步落库
• 消息队列削峰
• 排队机制

5.4.2 数据库层方案

云厂商的热点更新功能可自动探测热点行并排队执行。

5.5 MySQL 8.0 批量更新：CTE 方式

WITH tmp AS (
    SELECT 'new_name_1' AS name, 'id_1' AS id
    UNION ALL
    SELECT 'new_name_2' AS name, 'id_2' AS id
)
UPDATE users, tmp 
SET users.name = tmp.name 
WHERE users.id = tmp.id;

5.6 UPDATE/DELETE 优化速查表

六、线上大表结构变更（Online DDL）

6.1 三种 DDL 算法对比

6.2 安全执行 DDL 的标准语法

ALTER TABLE user 
ADD COLUMN age INT NOT NULL DEFAULT 0,
ALGORITHM = INSTANT, LOCK = NONE;

6.3 gh-ost：大表变更的终极方案

对于不支持 INSTANT 的大表 DDL，推荐使用 gh-ost（GitHub Online Schema Migration），它基于 binlog 回放增量数据，无触发器，可暂停、可限流、原子切换。

6.4 DDL 与 MDL 锁

DDL 需要获取 MDL 写锁，若存在长事务未提交，DDL 会阻塞后续所有 DML。操作前应检查长事务：

SELECT * FROM information_schema.innodb_trx 
WHERE trx_started < NOW() - INTERVAL 10 MINUTE;

七、数据库连接管理与连接池调优

7.1 连接数爆满问题

原因：连接池配置过大、慢查询堆积、连接泄漏、瞬时流量高峰。

解决方案：合理配置 max_connections 与连接池大小。

[mysqld]
max_connections = 500
thread_cache_size = 32
table_open_cache = 2000
open_files_limit = 10000

7.2 连接池“假死”与空闲连接回收

连接池的空闲超时时间必须小于数据库的 wait_timeout。

Druid 保活配置：

spring:
  datasource:
    druid:
      test-while-idle: true
      validation-query: SELECT 1
      time-between-eviction-runs-millis: 30000
      min-evictable-idle-time-millis: 180000

HikariCP 配置：

spring:
  datasource:
    hikari:
      idle-timeout: 180000
      max-lifetime: 600000
      keepalive-time: 30000   # MySQL 8.0 特有

7.3 MySQL 8.0 驱动层注意事项

高可靠 URL 模板：

jdbc:mysql://host:3306/db?useSSL=false&serverTimezone=Asia/Shanghai&tcpKeepAlive=true&allowPublicKeyRetrieval=true

八、配置参数调优

8.1 关键参数配置

[mysqld]
# InnoDB 缓冲池大小（物理内存的 70%-80%）
innodb_buffer_pool_size = 6G

# 重做日志大小（缓冲池的 25% 左右）
innodb_log_file_size = 1536M

# 日志刷新策略（2=每秒刷新，平衡方案）
innodb_flush_log_at_trx_commit = 2

# 最大连接数
max_connections = 500

# 线程缓存
thread_cache_size = 32

# 排序缓冲区（会话级）
sort_buffer_size = 2M

# 连接缓冲区
join_buffer_size = 2M

8.2 缓冲池命中率监控

SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- 命中率 = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)
-- 目标 > 99%

8.3 MySQL 8.0 优化器参数

prefer_ordering_index：控制优化器对排序索引的偏好，可适时关闭。

SET SESSION optimizer_switch='prefer_ordering_index=OFF';

九、监控、诊断与故障排查

9.1 慢查询日志

[mysqld]
slow_query_log = 1
long_query_time = 1.0
log_queries_not_using_indexes = 1
log_slow_extra = 1   # MySQL 8.0.14+，记录更多信息

分析工具：

• mysqldumpslow
• pt-query-digest

9.2 Performance Schema 与 Sys Schema

-- 查看最耗时语句
SELECT * FROM sys.statement_analysis LIMIT 10;

-- 查看冗余索引
SELECT * FROM sys.schema_redundant_indexes;

-- 查看未使用的索引
SELECT * FROM sys.schema_unused_indexes;

9.3 故障排查流程图

9.3.1 写入操作慢排查

1. 检查是否是单条操作 → 改用批量操作
2. 检查 WHERE 条件是否有索引 → 加索引
3. 检查是否被锁阻塞 → SHOW ENGINE INNODB STATUS
4. 检查是否有死锁 → 查看错误日志
5. 检查配置参数（autocommit、buffer_pool_size）
6. 检查磁盘 I/O（iostat）

9.3.2 连接数爆满排查

1. SHOW PROCESSLIST → 查看连接和 SQL
2. 检查是否有慢查询堆积 → 优化慢 SQL
3. 检查是否有连接泄漏 → 查看 Sleep 连接时长
4. 临时调高 max_connections 应急
5. 调优应用连接池配置

十、高频陷阱与补充专题

10.1 隐式类型转换深度案例

10.1.1 字符串与数字比较

-- 表定义 phone VARCHAR(20) 有索引
SELECT * FROM users WHERE phone = 13800138000;  -- 索引失效
SELECT * FROM users WHERE phone = '13800138000'; -- 走索引

10.1.2 字符集转换导致索引失效

连接条件中字符集不一致会导致索引失效，应统一表字符集。

10.1.3 日期字符串比较

SELECT * FROM t WHERE date_col = '2026-04-08';   -- 走索引
SELECT * FROM t WHERE date_col = STR_TO_DATE('2026-04-08','%Y-%m-%d'); -- 索引失效

10.2 大字段（TEXT/BLOB）优化策略

10.2.1 InnoDB 行溢出机制

大字段数据存储在独立溢出页，扫描时可能产生大量随机 I/O。

10.2.2 优化手段

• 垂直拆分：将大字段分离到扩展表
• 外部存储：URL 存 MySQL，文件存 OSS
• 调整行格式：ROW_FORMAT=COMPRESSED（增加 CPU 开销）

10.3 字符集与排序规则的影响

• 统一数据库、表、列的字符集和排序规则
• 避免在查询中使用 COLLATE 子句（导致索引失效）
• utf8mb4 下索引长度计算：VARCHAR(255) 最大 1020 字节

10.4 乐观锁与悲观锁的选择

10.4.1 悲观锁

SELECT * FROM inventory WHERE id = 1 FOR UPDATE;
UPDATE inventory SET stock = stock - 1 WHERE id = 1;

特点：强一致性，并发低。

10.4.2 乐观锁

UPDATE inventory 
SET stock = stock - 1, version = version + 1 
WHERE id = 1 AND version = old_version;

特点：高并发，需处理重试。

选择依据：冲突概率高用悲观锁，概率低用乐观锁。

10.5 事务隔离级别与 Binlog 格式

10.5.1 隔离级别选择

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

10.5.2 Binlog 格式

MySQL 8.0 推荐 ROW 格式，配合 binlog_row_image=MINIMAL 减少日志量。

10.6 分区表使用场景

适合按时间范围查询的大表（如日志表），可裁剪分区加速查询，归档历史数据秒级完成。但分区表不支持外键，分区数不宜过多。

CREATE TABLE logs (
    id INT,
    log_date DATE,
    content TEXT
) PARTITION BY RANGE (YEAR(log_date)) (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026)
);

十一、总结与思维导图

11.1 调优方法论

1. 发现问题：通过监控、慢查询日志定位瓶颈
2. 分析问题：使用 EXPLAIN/EXPLAIN ANALYZE 分析执行计划
3. 制定方案：从索引、SQL、配置、架构四个层面考虑
4. 实施优化：一次只改一个点，便于评估
5. 验证效果：对比优化前后性能指标
6. 持续监控：定期检查，防止性能退化

11.2 核心要点速记

• 深分页用书签分页，不要直接用大 OFFSET
• 多表 JOIN 尽量转单表查询，先分页后关联
• WHERE 条件列必须有索引，避免全表锁
• 批量操作代替逐条操作，控制事务大小
• 死锁源于锁顺序不一致，统一加锁顺序
• DDL 优先使用 INSTANT，不行再用 gh-ost
• 连接池空闲超时 < wait_timeout
• innodb_buffer_pool_size 设内存的 70%，最重要
• 慢查询日志必开，定期分析优化

11.3 调优维度总结

使用建议
本笔记基于 MySQL 8.0 + InnoDB 存储引擎，结合生产环境实战经验整理，覆盖了 90% 以上的日常性能问题场景。建议读者在实际工作中：

1. 遇到性能问题时先查阅对应章节，理解原理后再动手优化；
2. 所有优化操作先在测试环境充分验证；
3. 调优是持续过程，养成定期分析慢日志和监控指标的习惯。
   祝你在 MySQL 调优之路上越走越远，系统永远稳定高效！