MySQL调优深度解析：数据库层面 + SQL层面全攻略
🎯 写在前面：MySQL是后端开发中最常用的数据库，而数据库性能优化是每个开发者必须掌握的技能。从索引设计到SQL编写，从配置调优到架构优化，这篇文章将带你全面掌握MySQL调优的核心技能，让你的查询从秒级降到毫秒级！
目录导航
一、MySQL架构概览
1.1 整体架构图
┌─────────────────────────────────────────────────────────────────────────────┐
│ MySQL 整体架构 │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌───────────────────────────────────────────────────────────────────┐ │
│ │ 连接层 (Connection Layer) │ │
│ │ ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐ │ │
│ │ │ 连接池 │ │ 认证 │ │ 线程管理│ │ 连接限制 │ │ │
│ │ └─────────┘ └─────────┘ └─────────┘ └─────────┘ │ │
│ └──────────────────────────┬────────────────────────────────────────┘ │
│ │ │
│ ┌────────────────────────────▼────────────────────────────────────────┐ │
│ │ 服务层 (Server Layer) │ │
│ │ │ │
│ │ ┌──────────────┐ ┌──────────────┐ ┌──────────────────────────┐ │ │
│ │ │ SQL接口 │ │ 优化器 │ │ 缓存 (Query Cache) │ │ │
│ │ │ DML/DDL/CLR │ │ Optimizer │ │ (MySQL 8.0已移除) │ │ │
│ │ └──────────────┘ └──────────────┘ └──────────────────────────┘ │ │
│ │ │ │ │ │
│ │ ▼ ▼ │ │
│ │ ┌───────────────────────────────────────────────────────────────┐│ │
│ │ │ 解析器 (Parser) ││ │
│ │ │ 词法分析 ──▶ 语法分析 ──▶ 语义分析 ──▶ 生成执行计划 ││ │
│ │ └───────────────────────────────────────────────────────────────┘│ │
│ │ │ │ │
│ │ ▼ │ │
│ │ ┌───────────────────────────────────────────────────────────────┐│ │
│ │ │ 查询缓存 (已移除) ││ │
│ │ └───────────────────────────────────────────────────────────────┘│ │
│ └──────────────────────────┬────────────────────────────────────────┘ │
│ │ │
│ ┌────────────────────────────▼────────────────────────────────────────┐ │
│ │ 存储引擎层 (Storage Engine Layer) │ │
│ │ │ │
│ │ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │ │
│ │ │ InnoDB │ │ MyISAM │ │ Memory │ │ Archive │ │ │
│ │ │ (默认/推荐) │ │ (不支持事务)│ │ (内存表) │ │ (归档存储) │ │ │
│ │ └─────────────┘ └─────────────┘ └─────────────┘ └─────────────┘ │ │
│ │ │ │
│ └──────────────────────────┬────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ ┌─────────────────────────┐ │
│ │ 文件系统层 │ │
│ │ *.frm *.ibd *.MYD *.MYI│ │
│ └─────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
1.2 InnoDB vs MyISAM
| 特性 | InnoDB | MyISAM |
|---|---|---|
| 事务支持 | ✅ 支持 | ❌ 不支持 |
| 外键约束 | ✅ 支持 | ❌ 不支持 |
| 行锁 | ✅ 支持 | ❌ 表锁 |
| MVCC | ✅ 支持 | ❌ 不支持 |
| 崩溃恢复 | ✅ 自动恢复 | ❌ 需手动修复 |
| 全文索引 | ✅ 5.6+支持 | ✅ 原生支持 |
| 存储结构 | .ibd (表空间) | .MYD + .MYI |
| COUNT(*) | 全表扫描 | 元数据存储 |
| 适用场景 | 事务/高并发 | 只读/静态表 |
1.3 MySQL日志体系
┌─────────────────────────────────────────────────────────────────────────┐
│ MySQL 日志体系 │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │
│ │ Redo Log │ │ Undo Log │ │ Bin Log │ │
│ │ (重做日志) │ │ (回滚日志) │ │ (归档日志) │ │
│ └──────┬──────┘ └──────┬──────┘ └──────┬──────┘ │
│ │ │ │ │
│ ▼ ▼ ▼ │
│ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │
│ │ 物理日志 │ │ 逻辑日志 │ │ 主从同步 │ │
│ │ 记录页面 │ │ 记录SQL │ │ 数据恢复 │ │
│ │ 修改 │ │ 回滚操作 │ │ │ │
│ └─────────────┘ └─────────────┘ └─────────────┘ │
│ │
│ ┌─────────────┐ ┌─────────────┐ │
│ │ Error Log │ │ Slow Query │ │
│ │ (错误日志) │ │ Log │ │
│ └─────────────┘ │ (慢查询日志)│ │
│ └─────────────┘ │
└─────────────────────────────────────────────────────────────────────────┘
日志参数配置：
┌─────────────────────────────────────────────────────────────────────────┐
│ │
│ # Binlog配置 │
│ log_bin = mysql-bin │
│ binlog_format = ROW # 推荐ROW格式 │
│ sync_binlog = 1 # 每次事务同步 │
│ expire_logs_days = 7 # Binlog保留7天 │
│ │
│ # Redo Log配置 │
│ innodb_log_file_size = 1G # 日志文件大小 │
│ innodb_log_files_in_group = 3 # 日志文件数量 │
│ innodb_flush_log_at_trx_commit = 1 # 事务提交刷盘策略 │
│ │
└─────────────────────────────────────────────────────────────────────────┘
二、索引核心原理
2.1 索引类型全景图
┌─────────────────────────────────────────────────────────────────────────┐
│ MySQL 索引类型 │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ 按数据结构分 │ │
│ │ │ │
│ │ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │ │
│ │ │ B+Tree │ │ Hash │ │ R-Tree │ │ │
│ │ │ (默认/通用) │ │ (Memory) │ │ (空间位置) │ │ │
│ │ └─────────────┘ └─────────────┘ └─────────────┘ │ │
│ │ │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ 按字段特性分 │ │
│ │ │ │
│ │ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │ │
│ │ │ 主键索引 │ │ 唯一索引 │ │ 普通索引 │ │ │
│ │ │ (聚簇索引) │ │ (唯一约束) │ │ (辅助索引) │ │ │
│ │ └─────────────┘ └─────────────┘ └─────────────┘ │ │
│ │ │ │
│ │ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │ │
│ │ │ 前缀索引 │ │ 全文索引 │ │ 复合索引 │ │ │
│ │ │(字符串前缀) │ │ (FULLTEXT) │ │(多列组合) │ │ │
│ │ └─────────────┘ └─────────────┘ └─────────────┘ │ │
│ │ │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────┘
2.2 B+Tree索引原理（重点！）
┌─────────────────────────────────────────────────────────────────────────┐
│ B+Tree 结构图 │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────┐ │
│ │ 根节点 │ │
│ │ (索引页) │ │
│ └──────┬──────┘ │
│ ┌────────────┼────────────┐ │
│ ▼ ▼ ▼ │
│ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │
│ │ 索引页1 │ │ 索引页2 │ │ 索引页3 │ │
│ │ 索引页 │ │ 索引页 │ │ 索引页 │ │
│ └──────┬──────┘ └──────┬──────┘ └──────┬──────┘ │
│ │ │ │ │
│ ┌──────────────┼──────────────┼──────────────┼──────────────┐ │
│ ▼ ▼ ▼ ▼ ▼ │
│ ┌────┐ ┌────┐ ┌────┐ ┌────┐ ┌────┐ ┌────┐ ┌────┐ ┌────┐ ┌────┐ ┌────┐│
│ │Data│ │Data│ │Data│ │Data│ │Data│ │Data│ │Data│ │Data│ │Data│ │Data││
│ └────┘ └────┘ └────┘ └────┘ └────┘ └────┘ └────┘ └────┘ └────┘ └────┘│
│ 叶子节点(数据页) │
│ │
│ 特点： │
│ • 平衡多路搜索树 │
│ • 所有数据在叶子节点 │
│ • 叶子节点用双向链表连接 │
│ • 非叶子节点只存储索引（键值） │
│ │
└─────────────────────────────────────────────────────────────────────────┘
2.3 聚簇索引 vs 辅助索引
┌─────────────────────────────────────────────────────────────────────────┐
│ 聚簇索引 vs 辅助索引 │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ 聚簇索引 (Clustered Index) │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ 主键索引 = 聚簇索引 │ │
│ │ │ │
│ │ 特点： │ │
│ │ • 叶子节点存储完整行数据 │ │
│ │ • 每个表只能有一个聚簇索引 │ │
│ │ • 数据按主键顺序物理存储 │ │
│ │ │ │
│ │ 示例： │ │
│ │ ┌────────┬────────┬──────────┐ │ │
│ │ │ id(PK) │ name │ email │ │ │
│ │ ├────────┼────────┼──────────┤ │ │
│ │ │ 1 │ 张三 │ zhang@...│ ──▶ 叶子节点存完整行 │ │
│ │ │ 3 │ 李四 │ li@... │ │ │
│ │ │ 5 │ 王五 │ wang@... │ │ │
│ │ └────────┴────────┴──────────┘ │ │
│ │ │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
│ 辅助索引 (Secondary Index) │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ 普通索引 = 辅助索引 = 非聚簇索引 │ │
│ │ │ │
│ │ 特点： │ │
│ │ • 叶子节点存储主键值（不是行数据） │ │
│ │ • 需要回表查询完整数据 │ │
│ │ • 一个表可以有多个辅助索引 │ │
│ │ │ │
│ │ 示例（基于name的索引）： │ │
│ │ ┌────────┬────────┐ │ │
│ │ │ name │ 主键值 │ │ │
│ │ ├────────┼────────┤ │ │
│ │ │ 张三 │ 1 │ ──▶ 叶子节点只存主键，返回需回表查id=1 │ │
│ │ │ 李四 │ 3 │ │ │
│ │ │ 王五 │ 5 │ │ │
│ │ └────────┴────────┘ │ │
│ │ │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────┘
回表查询流程：
┌─────────────────────────────────────────────────────────────────────────┐
│ │
│ SELECT * FROM user WHERE name = '李四'; │
│ │
│ ① 先在name索引树查找 → 得到主键id=3 │
│ ② 再到主键索引树查找 → 获取完整行数据（回表） │
│ │
│ ┌───────────────────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ name索引树 主键索引树 │ │
│ │ ┌─────────┐ ┌─────────┐ │ │
│ │ │ 张三:1 │ │ 1:完整行 │ │ │
│ │ │ 李四:3 │ ──查──▶ id=3 │ 3:完整行 │ │ │
│ │ │ 王五:5 │ │ 5:完整行 │ │ │
│ │ └─────────┘ └─────────┘ │ │
│ │ │ │
│ │ 只存主键值 ✓ 存完整数据 ✓ │ │
│ └───────────────────────────────────────────────────────────────────┘ │
│ │
│ ⚠️ 优化方法：覆盖索引，避免回表 │
│ │
│ SELECT id, name FROM user WHERE name = '李四'; ──▶ 覆盖索引，无需回表 │
│ │
└─────────────────────────────────────────────────────────────────────────┘
2.4 索引失效场景（面试必问！）
┌─────────────────────────────────────────────────────────────────────────┐
│ 索引失效的 11 种场景 │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ ❌ 1. 以%开头的LIKE查询 │
│ SELECT * FROM user WHERE name LIKE '%四'; // 索引失效 │
│ SELECT * FROM user WHERE name LIKE '四%'; // 索引生效 │
│ │
│ ❌ 2. 索引列参与运算 │
│ SELECT * FROM user WHERE age + 1 = 20; // 索引失效 │
│ SELECT * FROM user WHERE age = 19; // 索引生效 │
│ │
│ ❌ 3. 索引列使用函数 │
│ SELECT * FROM user WHERE YEAR(create_time) = 2024; // 失效 │
│ SELECT * FROM user WHERE create_time >= '2024-01-01'; // 生效 │
│ │
│ ❌ 4. 类型转换导致索引失效 │
│ age是INT类型： │
│ SELECT * FROM user WHERE age = '18'; // 索引失效（字符串） │
│ SELECT * FROM user WHERE age = 18; // 索引生效（数字） │
│ │
│ ❌ 5. OR条件中有非索引列 │
│ SELECT * FROM user WHERE name = '李四' OR age = 18; // 索引失效 │
│ // age无索引，整体失效 │
│ │
│ ✅ 6. OR条件全部有索引（改用 UNION） │
│ SELECT * FROM user WHERE name = '李四' │
│ UNION │
│ SELECT * FROM user WHERE age = 18; // 索引生效 │
│ │
│ ❌ 7. 不符合最左前缀原则（复合索引） │
│ 索引: (name, age, city) │
│ SELECT * FROM user WHERE age = 18; // 索引失效 │
│ SELECT * FROM user WHERE city = '北京'; // 索引失效 │
│ SELECT * FROM user WHERE name = '李四'; // 索引生效 ✓ │
│ SELECT * FROM user WHERE name = '李四' AND age = 18; // 生效 ✓ │
│ │
│ ❌ 8. 使用NOT IN、NOT EXISTS │
│ SELECT * FROM user WHERE age NOT IN (18, 20); // 索引失效 │
│ SELECT * FROM user WHERE age NOT BETWEEN 18 AND 30; // 索引失效 │
│ │
│ ❌ 9. 使用 != 或 <> │
│ SELECT * FROM user WHERE name != '李四'; // 索引失效 │
│ │
│ ❌ 10. 统计COUNT(索引列)优化失败 │
│ SELECT COUNT(name) FROM user; // MySQL 8.0可优化 │
│ │
│ ❌ 11. 数据量过小（全表扫描更快） │
│ 表只有100条数据，查询随机记录 // 可能走全表扫描 │
│ │
└─────────────────────────────────────────────────────────────────────────┘
三、数据库层面调优
3.1 SQL执行计划分析（EXPLAIN）
┌─────────────────────────────────────────────────────────────────────────┐
│ EXPLAIN 执行计划详解 │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ 用法：EXPLAIN SELECT ... 或 EXPLAIN ANALYZE SELECT ... │
│ │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ id │ SELECT查询的序列号 │ │
│ │ select_type │ SELECT类型 (SIMPLE/PRIMARY/SUBQUERY等) │ │
│ │ table │ 访问的表 │ │
│ │ type │ 连接类型 (system/const/eq_ref/ref/range等) │ │
│ │ possible_keys │ 可能使用的索引 │ │
│ │ key │ 实际使用的索引 │ │
│ │ key_len │ 索引长度 │ │
│ │ ref │ 与索引比较的列 │ │
│ │ rows │ 预计扫描的行数 │ │
│ │ filtered │ 过滤百分比 │ │
│ │ Extra │ 附加信息 (Using index/Using where等) │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
│ type 列详解（性能从好到差）： │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ system > const > eq_ref > ref > range > index > ALL │ │
│ │ │ │ │ │ │ │ │ │ │
│ │ │ 主键/唯一 │ 非唯一 │ 索引 │ 全索引 │ 全表 │ │
│ │ 最佳 索引查找 │ 索引查找 │ 范围扫描 │ 扫描 │ 扫描 │ │
│ │ │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────┘
3.2 SQL性能优化公式
┌─────────────────────────────────────────────────────────────────────────┐
│ SQL性能优化核心公式 │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ 性能 = 扫描行数 × 单行处理成本 │ │
│ │ │ │
│ │ ↓ │ │
│ │ 优化 = 减少扫描行数 + 降低单行成本 │ │
│ │ │ │
│ │ ↓ │ │
│ │ 核心 = 索引优化 + 查询结构优化 │ │
│ │ │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
│ 优化优先级： │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ ① 索引优化（最重要） ──▶ 减少扫描行数 │ │
│ │ ② SQL结构优化 ──▶ 避免全表扫描 │ │
│ │ ③ 业务逻辑优化 ──▶ 减少查询次数 │ │
│ │ ④ 架构优化 ──▶ 分库分表/读写分离 │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────┘
3.3 数据库配置调优
┌─────────────────────────────────────────────────────────────────────────┐
│ MySQL 关键参数调优 │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ 连接相关 │ │
│ │ ───────────────────────────────────────────────────────────────│ │
│ │ max_connections = 2000 最大连接数 │ │
│ │ wait_timeout = 600 空闲连接超时(秒) │ │
│ │ thread_cache_size = 64 线程缓存大小 │ │
│ │ connection_pool_size 连接池大小(应用层) │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ 缓冲池配置 (InnoDB Buffer Pool) │ │
│ │ ───────────────────────────────────────────────────────────────│ │
│ │ innodb_buffer_pool_size = 12G 缓冲池大小 (建议50-80%) │ │
│ │ innodb_buffer_pool_instances = 4 缓冲池实例数 │ │
│ │ innodb_buffer_pool_load_at_startup = ON 预热加载 │ │
│ │ │ │
│ │ 计算公式： │ │
│ │ 缓冲池大小 = 可用内存 × 60%~80% │ │
│ │ 例如：16G内存 → innodb_buffer_pool_size = 10G │ │
│ │ │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ 日志配置 │ │
│ │ ───────────────────────────────────────────────────────────────│ │
│ │ innodb_log_file_size = 1G Redo日志文件大小 │ │
│ │ innodb_log_files_in_group = 3 日志文件组数量 │ │
│ │ innodb_flush_log_at_trx_commit = 1 事务提交刷盘策略 │ │
│ │ │ │
│ │ 刷盘策略详解： │ │
│ │ = 1 (安全最高) 每次提交同步刷盘 │ │
│ │ = 2 (性能较好) 提交到系统缓存，每秒刷盘 │ │
│ │ = 0 (性能最高) 每秒刷盘，可能丢失1秒数据 │ │
│ │ │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ 查询优化 │ │
│ │ ───────────────────────────────────────────────────────────────│ │
│ │ sort_buffer_size = 4M 排序缓冲区大小 │ │
│ │ join_buffer_size = 4M 连接缓冲区大小 │ │
│ │ read_buffer_size = 2M 读缓冲区大小 │ │
│ │ read_rnd_buffer_size = 4M 随机读缓冲区 │ │
│ │ tmp_table_size = 64M 临时表大小 │ │
│ │ max_heap_table_size = 64M 内存表大小 │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ 表相关 │ │
│ │ ───────────────────────────────────────────────────────────────│ │
│ │ table_open_cache = 4096 表缓存大小 │ │
│ │ table_definition_cache = 2048 表定义缓存 │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────┘
3.4 慢查询日志配置
┌─────────────────────────────────────────────────────────────────────────┐
│ 慢查询日志配置 │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ # 启用慢查询日志 │
│ slow_query_log = ON │
│ slow_query_log_file = /var/log/mysql/slow.log │
│ │
│ # 超过1秒的查询记录到慢日志 │
│ long_query_time = 1 │
│ │
│ # 记录未使用索引的查询（MySQL 5.6+） │
│ log_queries_not_using_indexes = ON │
│ │
│ # 记录管理操作（MySQL 5.6+） │
│ log_slow_admin_statements = ON │
│ │
│ # 设置格式（MySQL 5.1+） │
│ log_slow_verbosity = 'full' │
│ │
│ ───────────────────────────────────────────────────────────────────────│
│ │
│ # my.cnf 配置示例 │
│ [mysqld] │
│ slow_query_log = 1 │
│ long_query_time = 1 │
│ slow_query_log_file = /data/mysql/slow.log │
│ log_queries_not_using_indexes = 1 │
│ │
└─────────────────────────────────────────────────────────────────────────┘
四、SQL层面调优
4.1 常见SQL优化法则
┌─────────────────────────────────────────────────────────────────────────┐
│ SQL优化黄金法则 │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │
│ │ ✓ 法则1 │ │ ✓ 法则2 │ │ ✓ 法则3 │ │ ✓ 法则4 │ │
│ │ SELECT具体 │ │ LIMIT分页 │ │ 避免SELECT *│ │ 批量操作 │ │
│ │ 字段名 │ │ 优化 │ │ 指定字段 │ │ 效率更高 │ │
│ └──────────────┘ └──────────────┘ └──────────────┘ └──────────────┘ │
│ │
│ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │
│ │ ✓ 法则5 │ │ ✓ 法则6 │ │ ✓ 法则7 │ │ ✓ 法则8 │ │
│ │ 适当冗余字段│ │ COUNT/Exists│ │ JOIN优于子查询│ │ 合理使用 │ │
│ │ 减少JOIN │ │ 场景区分 │ │ │ │ 临时表 │ │
│ └──────────────┘ └──────────────┘ └──────────────┘ └──────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────┘
4.2 SELECT字段优化
❌ 不推荐：
SELECT * FROM user WHERE id = 1;
├─ 读取所有字段
├─ 增加网络传输
└─ 无法使用覆盖索引
✅ 推荐：
SELECT id, name, email FROM user WHERE id = 1;
├─ 只读取需要的字段
├─ 减少网络IO
└─ 可能使用覆盖索引（Using index）
4.3 分页查询优化
┌─────────────────────────────────────────────────────────────────────────┐
│ 分页查询优化 │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ 原始分页（偏移量大时很慢）： │
│ SELECT * FROM orders ORDER BY id LIMIT 1000000, 10; │
│ ├─ 问题：MySQL先扫描1000010行，丢弃前1000000行 │
│ └─ 性能：O(n)，n越大越慢 │
│ │
│ ───────────────────────────────────────────────────────────────────────│
│ │
│ ✅ 优化方案1：延迟关联 │
│ │
│ SELECT * FROM orders o ──▶ JOIN延迟 │
│ INNER JOIN (SELECT id FROM orders ──▶ 子查询只查主键 │
│ ORDER BY id LIMIT 1000000, 10) t │
│ ON o.id = t.id; │
│ │
│ ───────────────────────────────────────────────────────────────────────│
│ │
│ ✅ 优化方案2：记录上次位置 │
│ │
│ # 上一页最大id = 1000000 │
│ SELECT * FROM orders WHERE id > 1000000 │
│ ORDER BY id LIMIT 10; │
│ │
│ 性能：O(1)，直接定位 │
│ │
│ ───────────────────────────────────────────────────────────────────────│
│ │
│ ✅ 优化方案3：游标分页（最适合大数据量） │
│ │
│ # 记录游标位置 │
│ SELECT * FROM orders │
│ WHERE id > #{lastId} │
│ ORDER BY id LIMIT 10; │
│ │
│ 前端传入 lastId，下次查询更高效 │
│ │
└─────────────────────────────────────────────────────────────────────────┘
4.4 JOIN优化
┌─────────────────────────────────────────────────────────────────────────┐
│ JOIN 优化详解 │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ JOIN原理： │ │
│ │ │ │
│ │ 驱动表选择原则：小表驱动大表（减少循环次数） │ │
│ │ │ │
│ │ 示例： │ │
│ │ user表: 10000条 │ │
│ │ order表: 100000条 │ │
│ │ │ │
│ │ ❌ SELECT * FROM user u INNER JOIN order o ON u.id = o.user_id │ │
│ │ 驱动表: user (10000行) × 循环查找order │ │
│ │ │ │
│ │ ✅ SELECT * FROM order o INNER JOIN user u ON u.id = o.user_id │ │
│ │ 驱动表: order (100000行) × 循环查找user │ │
│ │ │ │
│ │ 等等！小表应该是user，但大表order作为驱动表更好？ │ │
│ │ │ │
│ │ 真相：用EXPLAIN查看，哪个是驱动表由优化器决定 │ │
│ │ 通常会用索引，让小表做驱动表 │ │
│ │ │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ JOIN类型选择： │ │
│ │ │ │
│ │ INNER JOIN ──▶ 两表都有的数据 │ │
│ │ LEFT JOIN ──▶ 左表全部 + 右表匹配 │ │
│ │ RIGHT JOIN ──▶ 右表全部 + 左表匹配 │ │
│ │ STRAIGHT_JOIN ──▶ 强制左表为驱动表 │ │
│ │ │ │
│ │ 优化建议： │ │
│ │ • 关联字段加索引 │ │
│ │ • 避免 SELECT *，只查需要的字段 │ │
│ │ • 尽量使用 INNER JOIN（MySQL优化器更友好） │ │
│ │ │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────┘
4.5 聚合查询优化
┌─────────────────────────────────────────────────────────────────────────┐
│ 聚合查询优化 │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ COUNT(*) / COUNT(1) / COUNT(字段) 区别： │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ COUNT(*) │ │
│ │ ├─ 统计所有行数，包含NULL │ │
│ │ ├─ InnoDB优化：遍历索引，不取值 │ │
│ │ └─ 性能：★★★★★ │ │
│ │ │ │
│ │ COUNT(字段) │ │
│ │ ├─ 统计非NULL的行数 │ │
│ │ └─ 性能：★★★☆☆ │ │
│ │ │ │
│ │ MyISAM：COUNT(*) 直接读取元数据（无WHERE条件） │ │
│ │ InnoDB：COUNT(*) 全表扫描（有WHERE条件也要扫描） │ │
│ │ │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
│ 优化技巧： │
│ │
│ ① 利用缓存： │
│ SELECT COUNT(*) FROM orders; │
│ // 放入Redis，定期更新，不用每次查数据库 │
│ │
│ ② 近似值（允许误差）： │
│ SELECT TABLE_ROWS FROM information_schema.TABLES │
│ WHERE TABLE_NAME = 'orders'; │
│ // 不精确，但快 │
│ │
│ ③ 统计表： │
│ CREATE TABLE order_stats ( │
│ date DATE PRIMARY KEY, │
│ count INT │
│ ); │
│ // 每天定时统计，更新到统计表 │
│ │
└─────────────────────────────────────────────────────────────────────────┘
4.6 批量操作优化
┌─────────────────────────────────────────────────────────────────────────┐
│ 批量操作优化 │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ 插入10000条数据的性能对比： │
│ │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ 方式 │ 耗时 │ 相对性能 │ │
│ │ ──────────────────────────────────────────────────────────────│ │
│ │ 单条INSERT │ 100秒 │ 基准线 │ │
│ │ 批量INSERT (500条/批) │ 0.5秒 │ 200倍提升 │ │
│ │ LOAD DATA INFILE │ 0.1秒 │ 1000倍提升 │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
│ ───────────────────────────────────────────────────────────────────────│
│ │
│ ✅ 批量插入优化： │
│ │
│ # 每次插入500-1000条 │
│ INSERT INTO user (name, email) VALUES │
│ ('张三1', 'z1@test.com'), │
│ ('张三2', 'z2@test.com'), │
│ ... │
│ ('张三1000', 'z1000@test.com'); │
│ │
│ # 事务包装（减少事务开销） │
│ START TRANSACTION; │
│ INSERT INTO user ... VALUES ...; │
│ INSERT INTO user ... VALUES ...; │
│ ... │
│ COMMIT; │
│ │
│ # 关闭自动提交 │
│ SET autocommit = 0; │
│ ... 批量SQL ... │
│ COMMIT; │
│ SET autocommit = 1; │
│ │
│ ───────────────────────────────────────────────────────────────────────│
│ │
│ ✅ LOAD DATA（最快的方式）： │
│ │
│ LOAD DATA INFILE '/tmp/users.csv' │
│ INTO TABLE user │
│ FIELDS TERMINATED BY ',' │
│ LINES TERMINATED BY '\n' │
│ (name, email, create_time); │
│ │
└─────────────────────────────────────────────────────────────────────────┘
五、慢查询优化实战
5.1 慢查询分析流程
┌─────────────────────────────────────────────────────────────────────────┐
│ 慢查询优化五步法 │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌──────────┐ │
│ │ Step 1 │ 开启慢查询日志 │
│ │ 定位慢SQL│ slow_query_log = ON │
│ └────┬─────┘ long_query_time = 1 │
│ │ │
│ ▼ │
│ ┌──────────┐ │
│ │ Step 2 │ 分析慢查询日志 │
│ │ 提取SQL │ pt-query-digest / mysqldumpslow │
│ └────┬─────┘ │
│ │ │
│ ▼ │
│ ┌──────────┐ │
│ │ Step 3 │ EXPLAIN分析执行计划 │
│ │ 分析执行 │ 检查type/keys/rows/Extra │
│ └────┬─────┘ │
│ │ │
│ ▼ │
│ ┌──────────┐ │
│ │ Step 4 │ PROFILE分析耗时分布 │
│ │ 分析耗时 │ SHOW PROFILE / PERFORMANCE_SCHEMA │
│ └────┬─────┘ │
│ │ │
│ ▼ │
│ ┌──────────┐ │
│ │ Step 5 │ 优化 + 验证 + 回归 │
│ │ 优化验证 │ 添加索引 / 重写SQL / 业务调整 │
│ └──────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────┘
5.2 实战案例分析
案例1：未合理使用索引
原始SQL：
SELECT * FROM orders
WHERE DATE(create_time) = '2024-03-15'
AND status = 1;
执行计划分析：
┌─────────────────────────────────────────────────────────────────┐
│ id │ type │ key │ rows │ Extra │
│ 1 │ ALL │ NULL │ 50000 │ Using where; Using filesort │
│ │
│ 问题：DATE()函数导致索引失效，全表扫描 │
│ rows = 50000，Using filesort（磁盘排序） │
└─────────────────────────────────────────────────────────────────┘
优化方案：
-- 方案1：改为范围查询
SELECT * FROM orders
WHERE create_time >= '2024-03-15 00:00:00'
AND create_time < '2024-03-16 00:00:00'
AND status = 1;
-- 方案2：建复合索引 (status, create_time)
CREATE INDEX idx_status_create ON orders(status, create_time);
优化后执行计划：
┌─────────────────────────────────────────────────────────────────┐
│ id │ type │ key │ rows │ Extra │
│ 1 │ range │ idx_status_create │ 50 │ Using index condition │
└─────────────────────────────────────────────────────────────────┘
性能提升：rows从50000降到50，性能提升1000倍
案例2：深分页问题
原始SQL：
SELECT * FROM orders
WHERE user_id = 10086
ORDER BY id DESC
LIMIT 1000000, 10;
执行计划：
┌─────────────────────────────────────────────────────────────────┐
│ id │ type │ key │ rows │ Extra │
│ 1 │ index │ PRIMARY │ 1000010│ Using where; Using filesort │
└─────────────────────────────────────────────────────────────────┘
优化方案：
-- 记录上次查询最大id
SELECT * FROM orders
WHERE user_id = 10086
AND id < #{lastMaxId} -- lastMaxId = 1000000
ORDER BY id DESC
LIMIT 10;
优化后执行计划：
┌─────────────────────────────────────────────────────────────────┐
│ id │ type │ key │ rows │ Extra │
│ 1 │ range │ PRIMARY │ 10 │ Using where │
└─────────────────────────────────────────────────────────────────┘
性能提升：rows从1000010降到10，性能提升10万倍
案例3：子查询改JOIN
原始SQL（子查询）：
SELECT * FROM user
WHERE id IN (
SELECT user_id FROM order WHERE amount > 1000
);
执行计划分析：
┌─────────────────────────────────────────────────────────────────┐
│ id │ type │ key │ rows │ Extra │
│ 1 │ ALL │ NULL │ 10000│ Using where │
│ 2 │ DEP_SUBQUERY│ index │ 100 │ Using index │
│ │
│ 问题：先执行子查询，再外层全表扫描 │
└─────────────────────────────────────────────────────────────────┘
优化方案（JOIN）：
SELECT DISTINCT u.*
FROM user u
INNER JOIN order o ON u.id = o.user_id
WHERE o.amount > 1000;
优化后执行计划：
┌─────────────────────────────────────────────────────────────────┐
│ id │ type │ key │ rows │ Extra │
│ 1 │ index │ PRIMARY │ 100 │ Using index │
│ 1 │ eq_ref │ user_id │ 10 │ Using where; Using join │
└─────────────────────────────────────────────────────────────────┘
性能提升：利用索引，避免子查询开销
六、分库分表与读写分离
6.1 分库分表策略
┌─────────────────────────────────────────────────────────────────────────┐
│ 分库分表策略 │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ 为什么要分？ │ │
│ │ 单表数据量 > 1000万 → 查询性能急剧下降 │ │
│ │ 单库QPS > 5000 → 数据库成为瓶颈 │ │
│ │ 单机磁盘不够 → 无法水平扩展 │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
│ ───────────────────────────────────────────────────────────────────────│
│ │
│ 分库分表维度： │
│ │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ 垂直拆分 水平拆分 │ │
│ │ (按业务/字段) (按数据) │ │
│ │ │ │
│ │ ┌─────────────┐ ┌─────────────┐ │ │
│ │ │ 数据库A │ │ 表1 │ │ │
│ │ │ 业务模块1 │ │ 2024年数据 │ │ │
│ │ └─────────────┘ └─────────────┘ │ │
│ │ ┌─────────────┐ ┌─────────────┐ │ │
│ │ │ 数据库B │ │ 表2 │ │ │
│ │ │ 业务模块2 │ │ 2023年数据 │ │ │
│ │ └─────────────┘ └─────────────┘ │ │
│ │ │ │
│ │ 解决：单库字段过多 解决：单表数据过多 │ │
│ │ │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────┘
6.2 分片键选择
┌─────────────────────────────────────────────────────────────────────────┐
│ 分片键（Shard Key）选择 │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ 选择原则： │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ ✓ 业务最常用的查询条件 │ │
│ │ ✓ 数据分布均匀（避免数据倾斜） │ │
│ │ ✓ 单个分片内查询（避免跨分片查询） │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
│ 示例：订单表分库分表 │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ CREATE TABLE orders ( │ │
│ │ id BIGINT PRIMARY KEY, │ │
│ │ user_id BIGINT NOT NULL, ← 分片键 ✓ │ │
│ │ order_no VARCHAR(32) NOT NULL, ← 查询条件 │ │
│ │ amount DECIMAL(10,2), │ │
│ │ created_at DATETIME │ │
│ │ ) ENGINE=InnoDB │ │
│ │ │ │
│ │ SHARDING KEY: user_id │ │
│ │ │ │
│ │ 分片规则：user_id % 4 │ │
│ │ ├─ user_id % 4 = 0 → orders_0 │ │
│ │ ├─ user_id % 4 = 1 → orders_1 │ │
│ │ ├─ user_id % 4 = 2 → orders_2 │ │
│ │ └─ user_id % 4 = 3 → orders_3 │ │
│ │ │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
│ ⚠️ 注意：按user_id分片，以下查询会跨分片 │
│ SELECT * FROM orders WHERE order_no = 'xxx'; // 全分片扫描 │
│ │
└─────────────────────────────────────────────────────────────────────────┘
6.3 读写分离
┌─────────────────────────────────────────────────────────────────────────┐
│ 读写分离架构 │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────┐ │
│ │ 应用层 │ │
│ │ (读写分离) │ │
│ └──────┬──────┘ │
│ │ │
│ ┌───────────────┼───────────────┐ │
│ │ 写 │ 读 │ │
│ ▼ ▼ │
│ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │
│ │ 主库 │ │ 从库1 │ │ 从库2 │ │
│ │ (写操作) │ │ (读操作) │ │ (读操作) │ │
│ │ │ │ │ │ │ │
│ │ Master │───▶│ Slave 1 │───▶│ Slave 2 │ │
│ └─────────────┘ └─────────────┘ └─────────────┘ │
│ │ │
│ │ 主从同步 (Binlog) │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ 实现方案： │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ 1. MySQL Router (官方) │ │
│ │ 2. ProxySQL │ │
│ │ 3. 业务层读写分离（Druid/ShardingSphere-JDBC） │ │
│ │ 4. 自主实现（AOP注解路由） │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
│ 注意：主从延迟问题 │
│ ├─ 写完后立即读可能读到旧数据 │
│ ├─ 解决方案：读主库 / 延迟删除 / GTID │
│ └─ 重要业务不走读写分离 │
│ │
└─────────────────────────────────────────────────────────────────────────┘
七、常见面试题
7.1 索引基础
Q1：B+Tree相比B-Tree的优势？
| 对比 | B-Tree | B+Tree |
|---|---|---|
| 存储位置 | 节点存储数据 | 非叶子节点只存储索引 |
| 树高度 | 较高 | 更低（IO更少） |
| 范围查询 | 需要中序遍历 | 叶子节点链表相连 |
| 查询稳定性 | 不稳定 | 所有查询到叶子节点 |
Q2：聚簇索引和非聚簇索引的区别？
核心区别：
- 聚簇索引：叶子节点存储完整行数据
- 非聚簇索引：叶子节点只存储主键值，需要回表
特点：
- 每个表只有一个聚簇索引
- 建议使用自增主键（紧凑存储，减少页分裂）
7.2 SQL优化
Q3：如何定位慢SQL？
步骤：
1. 开启慢查询日志
2. 使用 EXPLAIN 分析执行计划
3. 关注 type（ALL最差）、rows（扫描行数）
4. 使用 SHOW PROFILE 分析各阶段耗时
5. 添加合适索引或重写SQL
Q4：如何优化分页查询？
方法1：延迟关联
SELECT * FROM orders o
INNER JOIN (SELECT id FROM orders LIMIT 1000000, 10) t
ON o.id = t.id;
方法2：记录上次最大ID
SELECT * FROM orders WHERE id > #{lastMaxId} LIMIT 10;
方法3：游标分页
前端传入lastId，后端基于lastId查询
Q5：什么是覆盖索引？
定义：查询的所有字段都在索引树中，无需回表
示例：
索引：(name, age)
SELECT name, age FROM user WHERE name = '李四';
-- ✓ 覆盖索引，无需回表
SELECT * FROM user WHERE name = '李四';
-- ❌ 需要回表获取其他字段
7.3 事务与锁
Q6：MySQL事务隔离级别？
| 级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| READ UNCOMMITTED | ✓ 可能 | ✓ 可能 | ✓ 可能 |
| READ COMMITTED | ✗ 不可能 | ✓ 可能 | ✓ 可能 |
| REPEATABLE READ | ✗ 不可能 | ✗ 不可能 | ✗ 不可能* |
| SERIALIZABLE | ✗ 不可能 | ✗ 不可能 | ✗ 不可能 |
- MySQL InnoDB在REPEATABLE READ下通过MVCC+Next-Key Lock解决幻读
Q7：什么是MVCC？
MVCC = Multi-Version Concurrency Control 多版本并发控制
核心原理：
- 每行数据隐藏两个字段：创建版本号、删除版本号
- 查询时读取创建版本≤当前事务ID、删除版本>当前事务ID的数据
- 实现读写并发，提高性能
InnoDB MVCC工作流程：
┌─────────────────────────────────────────────────────────────────┐
│ SELECT │
│ ├─ 读取创建版本 ≤ 当前事务ID │
│ └─ 读取删除版本 > 当前事务ID 或 未删除 │
│ │
│ INSERT │
│ └─ 新增行，创建版本 = 当前事务ID │
│ │
│ DELETE │
│ └─ 删除行，删除版本 = 当前事务ID │
│ │
│ UPDATE │
│ ├─ 新增一行，创建版本 = 当前事务ID │
│ └─ 旧行删除，删除版本 = 当前事务ID │
└─────────────────────────────────────────────────────────────────┘
Q8：什么是Next-Key Lock？
作用：解决幻读问题
锁结构：
┌─────────────────────────────────────────────────────────────────┐
│ Next-Key Lock = Record Lock + Gap Lock │
│ │
│ 记录锁（Record Lock）：锁住索引记录 │
│ 间隙锁（Gap Lock）：锁住索引之间的间隙 │
│ │
│ 示例： │
│ SELECT * FROM orders WHERE id = 5 FOR UPDATE; │
│ │
│ ├─ Record Lock：锁住 id=5 这条记录 │
│ └─ Gap Lock：锁住 (4, 5) 和 (5, 6) 之间的间隙 │
│ │
│ 防止其他事务在 id=5 附近插入/删除数据 │
└─────────────────────────────────────────────────────────────────┘
7.4 架构相关
Q9：分库分表后如何跨分片查询？
方案：
1. 异构索引表：维护全局映射表
2. ES搜索：将数据同步到ES进行搜索
3. 分片路由：业务层先聚合再返回
4. 分布式事务：ShardingSphere/TDDL处理
Q10：MySQL主从延迟如何处理？
原因：
- 主库写入，从库同步有延迟
- 从库配置低
- 大事务同步时间长
解决方案：
1. 读一致性要求高的业务，读主库
2. 延迟删除：写操作后延迟一段时间再读从库
3. 异步消息通知：写成功后发消息，消费后再查询
4. GTID模式：提高从库追主库的速度
5. 配置从库更高规格
总结
┌─────────────────────────────────────────────────────────────────────────┐
│ MySQL调优知识体系总结 │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────────┐ ┌─────────────────┐ ┌─────────────────┐ │
│ │ 索引优化 │ │ SQL优化 │ │ 配置优化 │ │
│ │ │ │ │ │ │ │
│ │ • B+Tree原理 │ │ • 避免全表扫描 │ │ • Buffer Pool │ │
│ │ • 聚簇/辅助索引 │ │ • 覆盖索引 │ │ • 日志配置 │ │
│ │ • 最左前缀原则 │ │ • 分页优化 │ │ • 连接参数 │ │
│ │ • 索引失效场景 │ │ • JOIN优化 │ │ • 查询缓冲 │ │
│ │ │ │ │ │ │ │
│ └────────┬────────┘ └────────┬────────┘ └────────┬────────┘ │
│ │ │ │ │
│ └────────────────────┼─────────────────────┘ │
│ ▼ │
│ ┌─────────────────────────┐ │
│ │ 架构层面优化 │ │
│ │ │ │
│ │ • 读写分离 │ │
│ │ • 分库分表 │ │
│ │ • 缓存加速 │ │
│ │ • 慢查询监控 │ │
│ └─────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────┘
调优口诀：
┌─────────────────────────────────────────────────────────────────────────┐
│ │
│ 慢查询，先定位；EXPLAIN，看执行； │
│ 全表扫，加索引；回表多，覆盖索； │
│ 分页深，游标分；JOIN多，小驱动； │
│ 事务大，拆小做；主从延迟，读主库； │
│ 数据多，分库表；配置优，性能好！ │
│ │
└─────────────────────────────────────────────────────────────────────────┘
写在最后
🎯 核心记忆点：
- 索引遵循最左前缀原则，避免%开头LIKE
- 聚簇索引存行数据，辅助索引存主键
- 分页优化用延迟关联或游标分页
- 读写分离要注意主从延迟
- 分库分表选择均匀分布的分片键
📢 讨论话题：大家在MySQL调优中还遇到过哪些问题？有什么独家的优化技巧？评论区见！
👇 往期推荐：
- Java线程池从入门到精通：框架自带 vs 自定义，我该怎么选？
- 2026求职旺季Java面试必杀技：让面试官刮目相看的深度技术指南
- Java JDK版本全攻略：从JDK 8到JDK 21，一次性把版本升级讲清楚
👍 点赞 + 关注，我们下期再见！
