MySQL 调优与索引优化

简介

MySQL 是最流行的开源关系数据库，性能优化是 DBA 和后端开发的核心技能。本文涵盖索引优化、慢查询分析、参数调优和 SQL 优化技巧，帮助解决生产环境中的性能瓶颈。

特点

• 1.索引优化 — B+Tree 索引原理和使用策略
• 2.慢查询 — 定位和分析慢 SQL
• 3.参数调优 — InnoDB 核心参数配置
• 4.SQL 技巧 — 编写高效 SQL 的最佳实践

索引优化

索引类型

-- 1. 主键索引
ALTER TABLE orders ADD PRIMARY KEY (id);

-- 2. 唯一索引
CREATE UNIQUE INDEX uk_order_no ON orders(order_no);

-- 3. 普通索引
CREATE INDEX idx_user_id ON orders(user_id);

-- 4. 联合索引（最左前缀原则）
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);

-- 5. 前缀索引（长字符串）
CREATE INDEX idx_email_prefix ON users(email(20));

-- 6. 全文索引
CREATE FULLTEXT INDEX ft_content ON articles(title, content);

EXPLAIN 分析

-- 使用 EXPLAIN 分析查询执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';

-- 关键字段：
-- type: 访问类型（从好到差）
--   system > const > eq_ref > ref > range > index > ALL
--   理想：ref 或以上，避免 ALL（全表扫描）

-- key: 实际使用的索引
-- rows: 预估扫描行数
-- Extra: 额外信息
--   Using index — 覆盖索引（好）
--   Using where — 服务端过滤
--   Using filesort — 文件排序（需优化）
--   Using temporary — 临时表（需优化）

-- 好的 EXAMPLE
EXPLAIN SELECT order_no, total_amount FROM orders
WHERE user_id = 100 AND status = 'paid';
-- type: ref, key: idx_user_status_created, Extra: Using index

-- 需要优化的 EXAMPLE
EXPLAIN SELECT * FROM orders WHERE DATE(created_at) = '2026-04-11';
-- type: ALL, key: NULL, Extra: Using where
-- 问题：函数导致索引失效

索引使用规则

-- 1. 最左前缀原则
-- 索引 idx_user_status_created(user_id, status, created_at)
SELECT * FROM orders WHERE user_id = 100;                           -- 命中
SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';       -- 命中
SELECT * FROM orders WHERE status = 'paid';                         -- 不命中！缺少最左列
SELECT * FROM orders WHERE user_id = 100 AND created_at > '2026-01-01'; -- 部分命中（只有 user_id）

-- 2. 索引失效场景
-- 函数计算
SELECT * FROM orders WHERE YEAR(created_at) = 2026;          -- 不命中
SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'; -- 命中

-- 隐式类型转换
SELECT * FROM orders WHERE order_no = 12345;                  -- 不命中（order_no 是 varchar）
SELECT * FROM orders WHERE order_no = '12345';                -- 命中

-- LIKE 前缀通配符
SELECT * FROM users WHERE name LIKE '%张';                     -- 不命中
SELECT * FROM users WHERE name LIKE '张%';                     -- 命中

-- OR 条件
SELECT * FROM orders WHERE user_id = 100 OR amount > 1000;    -- 不命中
SELECT * FROM orders WHERE user_id = 100                       -- 分别建索引，用 UNION
UNION
SELECT * FROM orders WHERE amount > 1000;

-- NOT IN / !=
SELECT * FROM orders WHERE status != 'cancelled';              -- 不命中

慢查询分析

开启慢查询日志

-- 查看慢查询状态
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;   -- 超过 1 秒记录
SET GLOBAL log_queries_not_using_indexes = ON;  -- 记录未用索引的查询

-- 慢查询日志位置
SHOW VARIABLES LIKE 'slow_query_log_file';

分析慢查询

# 使用 mysqldumpslow 分析
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log
# -s t: 按查询时间排序
# -t 10: 显示前10条

# 使用 pt-query-digest（更强大）
pt-query-digest /var/lib/mysql/slow.log > slow_report.txt

常见慢查询优化

-- 1. 避免 SELECT *
-- 差
SELECT * FROM orders WHERE user_id = 100;
-- 好
SELECT id, order_no, total_amount, status FROM orders WHERE user_id = 100;

-- 2. 分页优化
-- 差（深度分页慢）
SELECT * FROM orders ORDER BY id LIMIT 100000, 10;
-- 好（游标分页）
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;

-- 3. 批量操作替代循环
-- 差：循环单条插入
-- 好：
INSERT INTO order_items (order_id, product_id, quantity) VALUES
(1, 101, 2), (1, 102, 1), (1, 103, 5);

-- 4. 子查询改 JOIN
-- 差
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE status = 'active');
-- 好
SELECT o.* FROM orders o INNER JOIN users u ON o.user_id = u.id WHERE u.status = 'active';

-- 5. EXISTS 替代 IN（大数据集）
-- 差
SELECT * FROM orders WHERE user_id IN (SELECT id FROM vip_users);
-- 好
SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM vip_users v WHERE v.id = o.user_id);

InnoDB 参数调优

核心参数

# /etc/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
# 内存配置（假设 8GB 内存）
innodb_buffer_pool_size = 4G          # 缓冲池大小（物理内存的 50-70%）
innodb_buffer_pool_instances = 4      # 缓冲池实例数
innodb_log_buffer_size = 64M          # 日志缓冲区

# 日志配置
innodb_log_file_size = 512M           # Redo 日志文件大小
innodb_log_files_in_group = 2         # 日志文件数量
innodb_flush_log_at_trx_commit = 1    # 事务日志刷盘策略
# 0 — 每秒刷盘（性能好，可能丢数据）
# 1 — 每次提交刷盘（安全，默认）
# 2 — 每次提交写 OS 缓存，每秒刷盘

# 连接配置
max_connections = 500                  # 最大连接数
wait_timeout = 600                     # 非交互连接超时（秒）
interactive_timeout = 600              # 交互连接超时

# 查询缓存（MySQL 8.0 已移除）
# query_cache_size = 0                 # MySQL 8.0 不需要

# 临时表
tmp_table_size = 256M                  # 内存临时表大小
max_heap_table_size = 256M             # MEMORY 表最大大小

# 排序和连接
sort_buffer_size = 4M                  # 排序缓冲区
join_buffer_size = 4M                  # 连接缓冲区
read_rnd_buffer_size = 8M              # 随机读缓冲区

# 二进制日志
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7                   # 保留 7 天
max_binlog_size = 500M

# 字符集
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci

参数选择参考

内存	innodb_buffer_pool_size	max_connections
4GB	2G	200
8GB	5G	500
16GB	10G	800
32GB	22G	1000

监控与诊断

常用监控 SQL

-- 当前连接
SHOW PROCESSLIST;
SHOW STATUS LIKE 'Threads_connected';

-- InnoDB 状态
SHOW ENGINE INNODB STATUS;

-- 缓冲池命中率
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- 命中率 = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)
-- 应 > 99%

-- 锁等待
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
SELECT * FROM information_schema.INNODB_LOCKS;

-- 表状态
SHOW TABLE STATUS LIKE 'orders';

-- 索引使用统计
SELECT * FROM sys.schema_index_statistics
ORDER BY rows_selected DESC LIMIT 10;

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

Optimizer Trace 深度分析

使用 Optimizer Trace

-- 开启 Optimizer Trace（查看优化器决策过程）
SET optimizer_trace='enabled=on';
SET optimizer_trace_max_mem_size=1048576;  -- 1MB

-- 执行要分析的查询
SELECT * FROM orders WHERE user_id = 100 AND status = 'paid' ORDER BY created_at DESC LIMIT 20;

-- 查看 Trace 信息
SELECT * FROM information_schema.OPTIMIZER_TRACE\G

-- 关闭 Trace
SET optimizer_trace='enabled=off';

-- Trace 关键字段解读：
-- steps: 优化器的各个步骤
--   join_preparation — SQL 解析和重写
--   join_optimization — 选择执行计划（最关键）
--   join_execution — 执行阶段
--   considered_execution_plans — 考虑过的所有执行计划
--   best_execution_plan — 最终选择的计划及原因
--   cost — 每个计划的预估代价

索引提示（Index Hints）

-- 强制使用指定索引
SELECT * FROM orders FORCE INDEX (idx_user_status_created)
WHERE user_id = 100 AND status = 'paid';

-- 建议使用指定索引（不强制）
SELECT * FROM orders USE INDEX (idx_user_id)
WHERE user_id = 100;

-- 忽略指定索引
SELECT * FROM orders IGNORE INDEX (idx_created_at)
WHERE user_id = 100 ORDER BY created_at DESC;

-- 适用场景：
-- 1. 优化器选错了索引
-- 2. 统计信息不准确导致计划偏差
-- 3. 临时验证某个索引的效果
-- 注意：生产环境应优先修复统计信息，而不是依赖 Hints

高级索引特性

索引条件下推（ICP）

-- Index Condition Pushdown（MySQL 5.6+）
-- 将 WHERE 条件下推到存储引擎层过滤，减少回表次数

-- 场景：联合索引 idx_user_status_created(user_id, status, created_at)
-- 查询只用了索引的前两列，第三列作为过滤条件
SELECT * FROM orders
WHERE user_id = 100 AND status = 'paid' AND created_at > '2026-01-01';

-- 没有 ICP：存储引擎返回 user_id=100 AND status='paid' 的所有行，
--           然后 Server 层过滤 created_at
-- 有 ICP：存储引擎在索引中直接过滤 created_at，减少回表

-- 查看是否使用了 ICP
EXPLAIN SELECT * FROM orders
WHERE user_id = 100 AND status = 'paid' AND created_at > '2026-01-01';
-- Extra: Using index condition

-- 控制开关
SET optimizer_switch='index_condition_pushdown=on';
SET optimizer_switch='index_condition_pushdown=off';

Multi-Range Read（MRR）

-- MRR 优化随机 I/O 为顺序 I/O
-- 适用场景：范围查询 + 回表

-- 开启 MRR
SET optimizer_switch='mrr=on';
SET optimizer_switch='mrr_cost_based=off';  -- 禁用代价评估，强制使用

-- 验证 MRR 生效
EXPLAIN SELECT * FROM orders
WHERE user_id BETWEEN 100 AND 200 AND status = 'paid';
-- Extra: Using MRR

-- 原理：
-- 1. 先通过索引获取主键集合
-- 2. 将主键按页排序
-- 3. 顺序回表查询，减少随机 I/O
-- 4. 对二级索引范围扫描 + 回表场景效果显著

JOIN 优化

JOIN 类型与优化

-- 1. Nested-Loop Join（默认）
-- 小表驱动大表
SELECT o.*, u.username
FROM orders o                          -- 大表（被驱动表）
INNER JOIN users u ON o.user_id = u.id -- 小表（驱动表）
WHERE u.status = 'active';

-- 优化器会自动选择小表作为驱动表
-- 也可以用 STRAIGHT_JOIN 强制指定驱动表顺序
SELECT o.*, u.username
FROM users u STRAIGHT_JOIN orders o ON o.user_id = u.id
WHERE u.status = 'active';

-- 2. Block Nested-Loop Join（无索引时）
-- 在内存中构建驱动表的 hash 表
-- 优化：增大 join_buffer_size
SET join_buffer_size = 8M;

-- 3. Batched Key Access（BKA）
-- 配合 MRR 使用，批量回表
SET optimizer_switch='batched_key_access=on';

-- JOIN 优化要点：
-- 1. 确保 JOIN 字段有索引
-- 2. 小表驱动大表
-- 3. 只 SELECT 需要的字段
-- 4. 避免 JOIN 超过 3 张表

子查询优化策略

-- 1. 标量子查询 → JOIN
-- 差
SELECT o.*, (SELECT name FROM users WHERE id = o.user_id) AS username
FROM orders o;
-- 好
SELECT o.*, u.name AS username
FROM orders o LEFT JOIN users u ON o.user_id = u.id;

-- 2. IN 子查询 → EXISTS 或 JOIN
-- 数据量小用 JOIN，数据量大用 EXISTS
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM vip_users v WHERE v.user_id = o.user_id);

-- 3. NOT IN → NOT EXISTS（NOT IN 遇到 NULL 会返回空结果）
-- 差
SELECT * FROM orders WHERE user_id NOT IN (SELECT user_id FROM blacklist);
-- 好
SELECT * FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM blacklist b WHERE b.user_id = o.user_id);

-- 4. 派生表（子查询在 FROM 中）
-- MySQL 5.7 之前派生表无法下推条件
-- MySQL 8.0 支持 derived_merge
SET optimizer_switch='derived_merge=on';

事务与锁优化

事务优化

-- 1. 减少事务持有时间
-- 差：事务中包含网络调用
BEGIN;
INSERT INTO orders (...) VALUES (...);
-- HTTP 调用第三方支付（可能耗时数秒）
UPDATE orders SET status = 1 WHERE id = LAST_INSERT_ID();
COMMIT;

-- 好：将非 DB 操作移到事务外
INSERT INTO orders (...) VALUES (...);
-- 先提交，再调用第三方
COMMIT;
-- 第三方回调后更新状态
BEGIN;
UPDATE orders SET status = 1 WHERE id = ?;
COMMIT;

-- 2. 避免长事务
-- 查看当前长事务
SELECT * FROM information_schema.innodb_trx
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;

-- 设置事务超时（MySQL 8.0）
SET innodb_kill_idle_transaction = 60;

-- 3. 选择合适的隔离级别
-- 默认 REPEATABLE READ，大多数场景够用
-- 读多写少考虑 READ COMMITTED（减少间隙锁）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

锁等待诊断

-- 查看当前锁等待
SELECT
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

-- MySQL 8.0 使用 performance_schema
SELECT * FROM performance_schema.data_lock_waits;
SELECT * FROM performance_schema.data_locks;

-- 查看死锁日志
SHOW ENGINE INNODB STATUS;
-- 查看 LATEST DETECTED DEADLOCK 部分

-- 设置锁等待超时
SET innodb_lock_wait_timeout = 10;  -- 10秒

深度分页优化

多种分页方案对比

-- 假设场景：1000 万条订单，需要翻到第 50 万页

-- 方案一：LIMIT OFFSET（深度分页极慢）
SELECT * FROM orders ORDER BY id LIMIT 5000000, 20;
-- 扫描 5000020 行，丢弃 5000000 行

-- 方案二：游标分页（推荐）
SELECT * FROM orders WHERE id > 5000000 ORDER BY id LIMIT 20;
-- 利用主键索引，只扫描 20 行
-- 缺点：只能向前翻页，不能跳页

-- 方案三：延迟关联
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 5000000, 20
) tmp ON o.id = tmp.id;
-- 子查询只查主键（覆盖索引），减少回表数据量

-- 方案四：基于覆盖索引 + 预计算
-- 先获取目标页的起始 ID
SELECT id FROM orders ORDER BY id LIMIT 5000000, 1;
-- 再用游标查询
SELECT * FROM orders WHERE id >= ? ORDER BY id LIMIT 20;

-- 方案五：使用 BETWEEN（适合连续 ID）
SELECT * FROM orders WHERE id BETWEEN 5000001 AND 5000020 ORDER BY id;
-- 注意：有删除时可能有空洞

-- 方案六：ES/搜索引擎辅助
-- 将数据同步到 Elasticsearch，用 ES 做分页查询
-- 适合多条件搜索 + 分页场景

统计信息与执行计划稳定性

统计信息管理

-- 查看表的统计信息
ANALYZE TABLE orders;

-- MySQL 8.0 持久化统计信息
SHOW VARIABLES LIKE 'innodb_stats_persistent';
-- 默认开启，统计信息存储在磁盘

-- 手动更新统计信息
ANALYZE TABLE orders UPDATE HISTOGRAM ON total_amount
WITH 100 BUCKETS;

-- 查看直方图
SELECT column_name, histogram
FROM information_schema.column_statistics
WHERE table_name = 'orders';

-- 直方图适用场景：
-- 1. 字段数据分布不均匀
-- 2. 优化器因为统计信息不准选错计划
-- 3. 适合低选择性但有热点值的字段

-- 注意：ANALYZE TABLE 在线上执行时会加元数据锁
-- 建议在低峰期执行

执行计划绑定（MySQL 8.0）

-- 使用 SQL Hint 创建执行计划绑定
-- 当优化器选错索引时，可以固定执行计划

-- 1. 创建优化器提示
CREATE INDEX idx_force ON orders(user_id, status, created_at);

-- 2. 使用 Optimizer Hint
SELECT /*+ INDEX(o idx_force) */ *
FROM orders o
WHERE user_id = 100 AND status = 'paid';

-- 3. MySQL 8.0 SQL Patch（不需要改代码）
-- 使用 dbms_sqltune 包（企业版功能）
-- 开源替代：使用 ProxySQL 的查询规则

表结构优化

表压缩与行格式

-- 查看当前行格式
SHOW TABLE STATUS LIKE 'orders';
-- Row_format: Dynamic（默认）

-- 行格式选择：
-- Compact — MySQL 5.0+，兼容性好
-- Redundant — 最早格式，不推荐
-- Dynamic — MySQL 5.7+ 默认，支持溢出页
-- Compressed — 压缩存储，节省空间但 CPU 开销大

-- 修改行格式（节省存储空间）
ALTER TABLE orders ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

-- 表压缩适用场景：
-- 1. 读多写少的大表
-- 2. 磁盘空间紧张
-- 3. IO 是瓶颈而非 CPU

-- 不适用场景：
-- 1. 写入频繁的表
-- 2. CPU 已经是瓶颈
-- 3. 没有压缩收益的小表

OPTIMIZE TABLE

-- 回收碎片空间
OPTIMIZE TABLE orders;

-- 适用场景：
-- 1. 大量 DELETE 后表碎片率高
-- 2. VARCHAR 字段频繁更新导致碎片
-- 3. 行迁移严重

-- 查看表碎片
SELECT
    table_name,
    DATA_LENGTH,
    INDEX_LENGTH,
    DATA_FREE,
    ROUND(DATA_FREE / (DATA_LENGTH + INDEX_LENGTH) * 100, 2) AS fragmentation_pct
FROM information_schema.tables
WHERE table_schema = 'your_db'
ORDER BY DATA_FREE DESC;

-- 注意：OPTIMIZE TABLE 会锁表（MySQL 5.6）
-- InnoDB 在线 DDL（MySQL 5.6+）：
ALTER TABLE orders ENGINE=InnoDB;  -- 重建表，支持在线执行

-- 分区表的维护
ALTER TABLE orders REBUILD PARTITION p2026_q1;
ALTER TABLE orders OPTIMIZE PARTITION p2026_q1;

优点

• 1.成熟稳定 — 全球最流行的开源数据库
• 2.性能优秀 — 合理优化后性能卓越
• 3.社区活跃 — 大量优化经验可参考
• 4.工具丰富 — EXPLAIN、慢查询、监控

缺点

• 1.优化复杂 — 需要深入理解 InnoDB
• 2.参数敏感 — 不同负载需要不同配置
• 3.索引维护 — 索引过多影响写入性能
• 4.监控成本 — 持续监控和调优需要投入

总结

MySQL 调优的核心：合理建索引、分析慢查询、调整 InnoDB 参数。索引遵循最左前缀原则，避免索引失效场景。生产环境开启慢查询日志，定期用 EXPLAIN 分析。innodb_buffer_pool_size 是最重要的参数，建议设为物理内存的 50-70%。

关键知识点

• 数据库主题一定要同时看数据模型、读写模式和执行代价。
• 很多性能问题不是 SQL 语法问题，而是索引、统计信息、事务和数据分布问题。
• 高可用、备份、迁移和治理与查询优化同样重要。
• 先把数据模型、访问模式和执行代价绑定起来理解。

项目落地视角

• 所有优化前后都保留执行计划、样本 SQL 和关键指标对比。
• 上线前准备回滚脚本、备份点和校验方案。
• 把连接池、锁等待、慢查询和容量增长纳入日常巡检。
• 保留执行计划、样本 SQL、索引定义和优化前后指标。

常见误区

• 脱离真实数据分布讨论索引或分片。
• 只看单条 SQL，不看整条业务链路的事务和锁。
• 把测试环境结论直接等同于生产环境结论。
• 脱离真实数据分布设计索引。

进阶路线

• 继续向执行计划、存储引擎、复制机制和数据治理层深入。
• 把主题与 ORM、缓存、消息队列和归档策略联动起来思考。
• 沉淀成数据库设计规范、SQL 审核规则和变更流程。
• 继续深入存储引擎、复制机制、归档与冷热分层治理。

适用场景

• 当你准备把《MySQL 调优与索引优化》真正落到项目里时，最适合先在一个独立模块或最小样例里验证关键路径。
• 适合数据建模、查询优化、事务控制、高可用和迁移治理。
• 当系统开始遇到慢查询、锁冲突、热点数据或容量增长时，这类主题价值最高。

落地建议

• 先分析真实查询模式、数据量级和写入特征，再决定索引或分片策略。
• 所有优化结论都结合执行计划、样本数据和监控指标验证。
• 高风险操作前准备备份、回滚脚本与校验 SQL。

排错清单

• 先确认瓶颈在 CPU、I/O、锁等待、网络还是 SQL 本身。
• 检查执行计划是否走错索引、是否发生排序或全表扫描。
• 排查长事务、隐式类型转换、统计信息过期和参数嗅探。

复盘问题

• 如果把《MySQL 调优与索引优化》放进你的当前项目，最先要验证的输入、输出和失败路径分别是什么？
• 《MySQL 调优与索引优化》最容易在什么规模、什么边界条件下暴露问题？你会用什么指标或日志去确认？
• 相比默认实现或替代方案，采用《MySQL 调优与索引优化》最大的收益和代价分别是什么？

延伸阅读

• MySQL 高可用方案
• MySQL innodb
• MySQL index deep
• MySQL binlog