数据库调优
问题
数据库性能不达标时，如何系统性地进行调优？有哪些层面可以优化？
面试速答版
数据库性能不达标时，如何系统性地进行调优？ 调优是从上到下的过程，不要一上来就改参数：
- 第一步：定位。开慢查询日志、看
SHOW PROCESSLIST、PERFORMANCE_SCHEMA，找到到底是 CPU、IO、锁还是连接问题。 - 黄金法则：先量化后优化（没数据不猜）、先业务后技术（能用缓存/异步解决的不死磕数据库）、先 SQL 后参数（八成问题出在 SQL 与索引）、一次只改一个变量。
有哪些层面可以优化？ 按「收益大、成本高」到「收益小、容易做」排序：
- 架构设计：读写分离、分库分表、多级缓存、冷热分离，收益最大。
- 表结构与索引：联合索引设计、覆盖索引、字段类型选择，避免超宽表。
- SQL 语句：避免
SELECT *、优化子查询/JOIN 顺序、大分页走「延迟关联」或游标分页、IN列表不要过长。 - 服务器参数：
innodb_buffer_pool_size（占内存 60~70%，最重要）、连接数、sync_binlog、innodb_flush_log_at_trx_commit。 - 硬件与 OS：SSD/NVMe、内存、关闭 swap、调整 IO 调度器，收益最小、但也最容易做。
答案
数据库调优是一个从上到下、由粗到细的过程，不能一上来就改参数。按优化收益排序：
调优黄金法则
- 先量化后优化 —— 没有监控数据就不要猜
- 先业务后技术 —— 能通过缓存/异步解决的，不要死磕数据库
- 先 SQL 后参数 —— 80% 的性能问题出在 SQL 和索引上
- 改一个测一个 —— 每次只改一个变量，用数据验证效果
一、定位瓶颈
调优第一步是找出问题在哪，而不是盲目改配置。
1.1 慢查询日志
开启慢查询日志
-- 查看当前状态
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; -- 未使用索引的也记录
使用 mysqldumpslow 快速分析慢查询日志：
# 按执行次数排序 Top 10
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log
# 按总耗时排序 Top 10
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# 按平均耗时排序
mysqldumpslow -s at -t 10 /var/log/mysql/slow.log
1.2 SHOW STATUS 全局状态
关键性能指标
-- 查看服务器运行时间
SHOW GLOBAL STATUS LIKE 'Uptime';
-- 查询吞吐量
SHOW GLOBAL STATUS LIKE 'Questions'; -- 总查询数
SHOW GLOBAL STATUS LIKE 'Com_select'; -- SELECT 次数
SHOW GLOBAL STATUS LIKE 'Com_insert'; -- INSERT 次数
-- 连接情况
SHOW GLOBAL STATUS LIKE 'Threads_connected'; -- 当前连接数
SHOW GLOBAL STATUS LIKE 'Threads_running'; -- 活跃线程数
SHOW GLOBAL STATUS LIKE 'Max_used_connections'; -- 历史最大连接数
-- 临时表与排序
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables'; -- 磁盘临时表
SHOW GLOBAL STATUS LIKE 'Sort_merge_passes'; -- 排序合并次数
1.3 SHOW PROCESSLIST
实时查看正在执行的查询，定位锁等待、长事务等问题：
-- 查看所有连接
SHOW FULL PROCESSLIST;
-- 关注以下状态
-- Locked：等待表锁
-- Sending data：正在读取/处理大量数据
-- Sorting result：正在排序
-- Creating tmp table：正在创建临时表
-- Copying to tmp table on disk：内存临时表放不下，写磁盘了
高危状态
如果看到大量 Locked 或 Waiting for table metadata lock，说明存在锁竞争，需要检查事务与并发控制。
1.4 Performance Schema
MySQL 5.6+ 内置的细粒度性能分析工具：
分析 SQL 执行耗时分布
-- 查看哪些 SQL 最耗时（按总耗时排序）
SELECT
DIGEST_TEXT AS sql_text,
COUNT_STAR AS exec_count,
ROUND(SUM_TIMER_WAIT / 1e12, 2) AS total_sec,
ROUND(AVG_TIMER_WAIT / 1e12, 4) AS avg_sec,
SUM_ROWS_EXAMINED AS rows_examined,
SUM_ROWS_SENT AS rows_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
分析 I/O 等待
-- 查看哪些文件 I/O 最多
SELECT
FILE_NAME,
COUNT_READ,
COUNT_WRITE,
ROUND(SUM_TIMER_READ / 1e12, 2) AS read_sec,
ROUND(SUM_TIMER_WRITE / 1e12, 2) AS write_sec
FROM performance_schema.file_summary_by_instance
ORDER BY SUM_TIMER_READ + SUM_TIMER_WRITE DESC
LIMIT 10;
二、SQL 与索引优化
这是收益最高的优化层面。详细内容参考 SQL 查询优化 和数据库索引原理，这里列出调优时的检查清单。
2.1 索引检查清单
查找未使用索引的表
-- 哪些索引从没被用过（MySQL 5.6+）
SELECT
object_schema AS db_name,
object_name AS table_name,
index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
AND count_star = 0
AND object_schema NOT IN ('mysql', 'performance_schema')
ORDER BY object_schema, object_name;
查找重复/冗余索引
-- 两个索引：idx_a(a) 和 idx_ab(a, b)
-- idx_a 是冗余的，因为 idx_ab 的最左前缀已覆盖
SELECT
table_schema, table_name,
redundant_index_name, redundant_index_columns,
dominant_index_name, dominant_index_columns
FROM sys.schema_redundant_indexes;
索引优化要点
- 高选择性列优先：区分度
> 0.1的列才值得建索引（SELECT COUNT(DISTINCT col) / COUNT(*) FROM table） - 覆盖索引：让查询只读索引不回表，
EXPLAIN中 Extra 显示Using index - 避免索引失效：函数运算、隐式类型转换、
LIKE '%xx'前缀通配符 - 控制索引数量：单表索引不超过 5-6 个，过多会拖慢写入
2.2 常见 SQL 反模式
| 反模式 | 问题 | 优化方式 |
|---|---|---|
SELECT * | 读取不需要的列，无法覆盖索引 | 明确列出需要的字段 |
WHERE func(col) | 索引失效 | 改写为 col = func_inverse(val) |
ORDER BY RAND() | 全表扫描 + 文件排序 | 应用层随机或子查询限制范围 |
NOT IN (子查询) | 可能全表扫描 | 改为 NOT EXISTS 或 LEFT JOIN ... IS NULL |
大 OFFSET 分页 | OFFSET 100000 要扫描 10 万行 | 改为游标分页 WHERE id > last_id |
OR 连接不同列 | 无法使用单一索引 | 改为 UNION ALL |
| 隐式类型转换 | WHERE varchar_col = 123 索引失效 | 保持类型一致 |
2.3 大表查询优化
游标分页替代 OFFSET
-- ❌ 慢：OFFSET 越大越慢
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 100000;
-- ✅ 快：使用上一页最后一条记录的 id
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;
批量更新避免长事务
-- ❌ 一次更新 100 万行，锁表时间长
UPDATE orders SET status = 'archived' WHERE created_at < '2024-01-01';
-- ✅ 分批更新，每次 1000 行
UPDATE orders SET status = 'archived'
WHERE created_at < '2024-01-01' AND status != 'archived'
LIMIT 1000;
-- 循环执行，直到影响行数为 0
三、MySQL 参数调优
改参数前
参数调优是"最后 20% 的优化"，前 80% 应该在 SQL 和索引层面解决。盲目调参可能适得其反。
3.1 InnoDB Buffer Pool
Buffer Pool 是 InnoDB 最核心的缓存，用于缓存数据页和索引页，直接决定查询是否命中内存。
Buffer Pool 配置
-- 查看当前大小
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
-- 推荐：物理内存的 60%-80%（专用数据库服务器）
-- 例如 16GB 内存的服务器
SET GLOBAL innodb_buffer_pool_size = 12 * 1024 * 1024 * 1024; -- 12GB
监控 Buffer Pool 命中率
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests'; -- 逻辑读（从缓存）
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads'; -- 物理读（从磁盘）
-- 命中率 = 1 - (物理读 / 逻辑读) × 100%
-- 目标：> 99%。低于 95% 说明 Buffer Pool 太小
3.2 连接与线程
my.cnf 连接相关参数
[mysqld]
# 最大连接数（默认 151，生产环境通常需要调大）
max_connections = 500
# 连接超时（秒），空闲连接超过这个时间会被断开
wait_timeout = 600
interactive_timeout = 600
# 线程缓存，减少线程创建开销
thread_cache_size = 64
监控连接使用情况
-- 当前 vs 最大
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW VARIABLES LIKE 'max_connections';
-- 如果 Max_used_connections 接近 max_connections，需要调大
-- 如果 Threads_connected 远小于 max_connections，说明连接池配置过大
配合连接池使用
数据库 max_connections 需要与应用层连接池配合设置。公式：max_connections ≥ 所有应用实例的连接池大小之和 + 预留管理连接
3.3 日志与刷盘
my.cnf 日志相关参数
[mysqld]
# redo log 刷盘策略（事务持久性 vs 性能）
# 1：每次提交都刷盘（最安全，默认）
# 2：每次提交写 OS 缓存，每秒刷盘（折中）
# 0：每秒写 + 刷盘（最快，宕机可能丢 1 秒数据）
innodb_flush_log_at_trx_commit = 1
# binlog 刷盘策略
# 1：每次提交刷盘（最安全）
# 0：依赖 OS 刷盘
sync_binlog = 1
# "双 1" 配置（两个都设为 1）：最高数据安全，适合金融等场景
# 性能敏感场景可以都设为 2 或 0，但要接受宕机丢数据的风险
双 1 配置
innodb_flush_log_at_trx_commit = 1 + sync_binlog = 1 被称为双 1 配置，是数据安全的底线。非特殊场景不要修改，否则主机宕机可能丢失已提交的事务。
3.4 临时表与排序
my.cnf 内存相关参数
[mysqld]
# 内存临时表大小（超过则写磁盘，性能急剧下降）
tmp_table_size = 64M
max_heap_table_size = 64M # 两者取较小值
# 排序缓冲区（每个连接独占）
sort_buffer_size = 4M
# JOIN 缓冲区
join_buffer_size = 4M
# 注意：这些是 per-connection 的！
# 500 连接 × 4M sort_buffer = 2GB 内存
监控磁盘临时表
SHOW GLOBAL STATUS LIKE 'Created_tmp_tables'; -- 创建的临时表总数
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables'; -- 其中写磁盘的
-- 磁盘临时表比例 > 25% 说明 tmp_table_size 可能需要调大
-- 或者优化 SQL，减少需要临时表的查询
3.5 关键参数速查表
| 参数 | 默认值 | 推荐值 | 说明 |
|---|---|---|---|
innodb_buffer_pool_size | 128M | 物理内存 60-80% | InnoDB 核心缓存 |
innodb_log_file_size | 48M | 1-2G | redo log 大小，影响写入性能 |
innodb_flush_log_at_trx_commit | 1 | 1（安全）/ 2（性能） | 事务刷盘策略 |
sync_binlog | 1 | 1（安全）/ 0（性能） | binlog 刷盘策略 |
max_connections | 151 | 按需设置 | 最大连接数 |
innodb_io_capacity | 200 | 看磁盘（SSD: 2000+） | I/O 吞吐上限 |
innodb_read_io_threads | 4 | 8-16 | 读 I/O 线程数 |
innodb_write_io_threads | 4 | 8-16 | 写 I/O 线程数 |
innodb_thread_concurrency | 0 | CPU 核数 × 2 | 并发线程上限（0=不限） |
四、架构层面优化
当单机调优已达极限，需要从架构上突破。
4.1 读写分离
适用场景：读多写少（读写比 > 10:1），详见数据库高可用。
主从延迟
从库有复制延迟（通常毫秒到秒级），写入后立即读可能读不到最新数据。解决方案：
- 强制读主库：关键业务写入后的读操作走主库
- 半同步复制：至少一个从库确认收到 binlog 后主库才返回
- 延迟检测：读之前检查
Seconds_Behind_Master，延迟过大时降级读主库
4.2 缓存层
在应用和数据库之间加一层 Redis 缓存，拦截热点查询：
Cache Aside 模式
async function getUser(id: string): Promise<User> {
// 1. 先查缓存
const cached = await redis.get(`user:${id}`);
if (cached) return JSON.parse(cached);
// 2. 缓存未命中，查数据库
const user = await db.query('SELECT * FROM users WHERE id = ?', [id]);
// 3. 写入缓存（设置过期时间）
await redis.set(`user:${id}`, JSON.stringify(user), 'EX', 300);
return user;
}
缓存与数据库的一致性问题参考缓存与数据库一致性。
4.3 分库分表
当单表数据量超过千万级、单库无法承载时：
| 策略 | 说明 | 适用场景 |
|---|---|---|
| 垂直分库 | 按业务拆分数据库（用户库、订单库） | 业务解耦 |
| 垂直分表 | 大字段拆出去（主表 + 扩展表） | 减少单行大小 |
| 水平分表 | 按规则分散到多张表（按 ID 取模、按时间范围） | 单表数据量过大 |
| 水平分库 | 水平分表 + 分散到不同数据库实例 | 单库容量/连接数瓶颈 |
详见数据库设计与范式。
4.4 异步与削峰
不是所有操作都需要实时写入数据库：
适用场景：日志写入、数据统计、非关键更新。参考消息队列。
五、监控与告警
调优不是一次性的，需要持续监控。
5.1 核心监控指标
| 指标 | 采集方式 | 告警阈值建议 |
|---|---|---|
| QPS / TPS | Com_select / Com_insert+update+delete | 突增 50% 以上 |
| 慢查询数 | Slow_queries | > 10/min |
| 连接使用率 | Threads_connected / max_connections | > 80% |
| Buffer Pool 命中率 | 1 - reads/read_requests | < 95% |
| 磁盘临时表比例 | tmp_disk_tables / tmp_tables | > 25% |
| 主从延迟 | Seconds_Behind_Master | > 5s |
| 死锁次数 | Innodb_row_lock_waits | > 0（需关注） |
| 磁盘 I/O 使用率 | OS 层 iostat | > 70% |
5.2 常用监控工具
| 工具 | 特点 |
|---|---|
| Prometheus + Grafana | 开源标准方案，mysqld_exporter 采集 MySQL 指标 |
| PMM（Percona Monitoring） | Percona 出品，专为 MySQL 设计，QAN 分析强大 |
pt-query-digest | Percona Toolkit，分析慢查询日志的命令行工具 |
mysqltuner.pl | 一键检查 MySQL 配置，给出优化建议 |
使用 pt-query-digest 分析慢查询
# 分析慢查询日志，输出 Top 10 慢 SQL
pt-query-digest /var/log/mysql/slow.log --limit 10
# 对比两段时间的慢查询差异
pt-query-digest slow-before.log --output=slowlog > before.txt
pt-query-digest slow-after.log --output=slowlog > after.txt
六、调优流程总结
常见面试问题
Q1: 线上数据库慢了，你会怎么排查？
答案：
按照「定位 → 分析 → 优化 → 验证」四步走：
- 看慢查询日志：开启
slow_query_log，找到耗时最长、执行最频繁的 SQL - SHOW PROCESSLIST：看当前有没有锁等待、长事务
- EXPLAIN 分析：看是否全表扫描、索引是否命中
- 监控指标：Buffer Pool 命中率、磁盘 I/O、连接数
- 针对性优化：加索引、改写 SQL、调参数、加缓存
- 压测验证：优化后在测试环境验证效果
Q2: InnoDB Buffer Pool 的作用是什么？怎么设置大小？
答案：
Buffer Pool 是 InnoDB 的核心内存缓存，存放数据页和索引页。查询时先在 Buffer Pool 中查找，命中则直接返回（逻辑读），未命中才从磁盘读取（物理读）。
大小设置：
- 专用数据库服务器：物理内存的 60%-80%
- 共享服务器：预留足够内存给操作系统和其他进程
- 监控命中率：目标
> 99%，低于 95% 说明太小
-- 计算命中率
SELECT
(1 - (
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads')
/
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
)) * 100 AS hit_rate;
Q3: innodb_flush_log_at_trx_commit 三个值有什么区别？
答案：
这个参数控制 redo log 的刷盘策略，影响事务持久性和写入性能：
| 值 | 行为 | 安全性 | 性能 |
|---|---|---|---|
1 | 每次事务提交都写入 + 刷盘 | 最高，不丢数据 | 最慢 |
2 | 每次事务提交写入 OS 缓存，每秒刷盘 | 宕机丢最多 1 秒 | 较快 |
0 | 每秒写入 + 刷盘 | 宕机丢最多 1 秒 | 最快 |
- 金融/支付：必须设为
1（配合sync_binlog = 1双 1 配置） - 日志/统计：可以设为
2或0，换取更高的写入吞吐
Q4: 如何判断索引是否需要优化？
答案：
从三个维度检查：
- 有没有索引没用上
-- 通过 Performance Schema 找从未使用的索引
SELECT object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL AND count_star = 0
AND object_schema = 'your_db';
- 有没有该建没建的
-- 慢查询中 EXPLAIN 出现 type=ALL（全表扫描）的 SQL
-- 检查 WHERE、JOIN、ORDER BY 涉及的列有没有索引
- 有没有冗余索引
-- 利用 sys.schema_redundant_indexes 视图
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = 'your_db';
原则：增删并重 —— 不仅要加缺失索引，也要删除冗余和未使用索引。无用索引会拖慢写入和 DDL。
Q5: 大表 DDL（加字段/加索引）怎么操作不影响业务？
答案：
直接在大表上执行 ALTER TABLE 可能锁表几分钟甚至几小时。安全方式：
- Online DDL（MySQL 5.6+）
-- ALGORITHM=INPLACE 不重建表，LOCK=NONE 不加锁
ALTER TABLE orders ADD INDEX idx_status (status), ALGORITHM=INPLACE, LOCK=NONE;
不是所有 DDL 都支持 INPLACE，MySQL 官方文档列出了每种操作支持的算法。
- pt-online-schema-change（Percona 工具）
# 原理：创建新表 → 触发器同步增量 → 重命名
pt-online-schema-change \
--alter "ADD COLUMN email VARCHAR(255)" \
D=mydb,t=users \
--execute
- gh-ost（GitHub 出品）
# 原理：创建新表 → binlog 同步增量 → 切换（无触发器，更安全）
gh-ost \
--alter="ADD COLUMN email VARCHAR(255)" \
--database=mydb --table=users \
--execute
Q6: 如何优化 COUNT(*) 查询？
答案：
COUNT(*) 在 InnoDB 中需要遍历索引（因为 MVCC 下每个事务看到的行数可能不同）。优化策略：
-- ❌ 慢：全表扫描
SELECT COUNT(*) FROM orders WHERE status = 'pending';
| 方案 | 适用场景 |
|---|---|
| 覆盖索引 | 给 status 建索引，只扫索引不回表 |
| 近似值 | SHOW TABLE STATUS 的 Rows 字段（估算值） |
| 缓存计数 | Redis 维护计数器，增删时 INCR/DECR |
| 统计表 | 单独维护一张计数表，触发器或异步更新 |
EXPLAIN 估算 | EXPLAIN SELECT COUNT(*) 的 rows 列（粗略估算） |
精确计数选覆盖索引 + 缓存，不需要精确的场景用 SHOW TABLE STATUS。
Q7: MySQL 的连接数突然打满怎么办？
答案：
紧急处理：
-- 1. 查看当前连接状态
SHOW PROCESSLIST;
-- 2. 杀掉长时间运行的查询
KILL <process_id>;
-- 3. 临时调大连接数（不需要重启）
SET GLOBAL max_connections = 1000;
排查根因：
- 慢查询堆积：一个慢 SQL 占着连接不释放 → 优化 SQL
- 连接泄漏：应用未正确关闭连接 → 检查连接池配置，加
wait_timeout - 突发流量：短时间大量请求 → 加缓存、限流
- 连接池过大：每个实例连接池 50 × 10 个实例 = 500 连接 → 减小单实例池大小
长期优化：
- 配合连接池使用，控制总连接数
- 设置
wait_timeout自动断开空闲连接 - 使用 ProxySQL 等中间件做连接复用
Q8: 如何做数据库的性能基线？
答案：
性能基线是指正常状态下的关键指标值，用于对比发现异常。
- 采集基线指标（业务正常期连续采集 1-2 周）
| 指标 | 采集间隔 |
|---|---|
| QPS / TPS | 10s |
| 慢查询数 | 1min |
| 连接使用率 | 10s |
| Buffer Pool 命中率 | 1min |
| 主从延迟 | 10s |
| CPU / 内存 / 磁盘 I/O | 10s |
- 建立基线报告：计算每个指标的均值、P95、P99、峰值
- 设置告警规则：指标偏离基线超过一定比例时告警
- 定期更新：业务变化后重新采集基线
Q9: explain 中的 type 字段各值的含义？
答案：
type 表示 MySQL 访问数据的方式，从好到差排序：
| type | 含义 | 说明 |
|---|---|---|
system | 表只有一行 | const 的特例 |
const | 主键/唯一索引等值查询 | WHERE id = 1，最快 |
eq_ref | JOIN 时主键/唯一索引关联 | 每次关联只匹配一行 |
ref | 非唯一索引等值查询 | WHERE status = 'active' |
range | 索引范围扫描 | WHERE id > 100，IN (...) |
index | 全索引扫描 | 扫描整棵索引树（比全表扫描快） |
ALL | 全表扫描 | 最慢，需要优化 |
优化目标
- 至少达到
range级别 - 出现
ALL或index说明需要加索引或改写 SQL ref及以上通常不需要优化
Q10: 数据库调优的顺序是什么？为什么？
答案：
调优顺序：架构 → 表结构/索引 → SQL → 参数 → 硬件
原因是越往上收益越大：
| 层面 | 收益 | 示例 |
|---|---|---|
| 架构优化 | 10x-100x | 加缓存后数据库 QPS 降 90% |
| 索引优化 | 10x-1000x | 一条全表扫描 SQL 加索引后从 5s → 5ms |
| SQL 改写 | 2x-10x | 用 EXISTS 替代 IN 子查询 |
| 参数调优 | 1.1x-2x | 调大 Buffer Pool 提升命中率 |
| 硬件升级 | 1.5x-3x | HDD → SSD，IOPS 提升 100 倍但受限于其他瓶颈 |
越上层的优化越需要理解业务，越底层的越容易操作但收益有限。面试中回答调优问题时，展现这种分层思维比罗列参数更有价值。