MySQL性能优化面试全攻略

一、MySQL 性能优化常见面试题清单

二、性能优化回答框架（建议背这个结构）

MySQL 优化我会从 5 层来看：硬件/配置 → 表结构 → 索引 → SQL → 架构。先定位瓶颈，再针对性优化，避免盲目加索引。

三、分层优化详解

1. 硬件与配置层

硬件

• CPU：复杂查询、排序、JOIN 多时更重要
• 内存：innodb_buffer_pool_size通常设为物理内存的 50%~70%
• 磁盘：SSD，随机 I/O 对数据库影响极大
• 网络：主从复制、分库分表时带宽要够

关键参数

# 缓冲池（最重要） innodb_buffer_pool_size = 8G # 日志 innodb_log_file_size = 1G innodb_flush_log_at_trx_commit = 1 # 强一致 # = 2 性能更好，可能丢 1 秒数据 # 连接 max_connections = 500 wait_timeout = 600 # 慢查询 slow_query_log = 1 long_query_time = 1

2. 表结构设计

反例

• 用SELECT *
• 单表几千万行还不考虑归档或分表
• 所有字段都建索引

3. 索引优化（面试最爱问）

索引原理（B+树）

• InnoDB 默认 B+树 索引
• 聚簇索引：叶子节点存整行数据（主键）
• 二级索引：叶子节点存主键值，需要 回表

索引设计原则

1. WHERE / ORDER BY / GROUP BY 高频字段考虑索引
2. 区分度高 的字段更适合建索引（如手机号 > 性别）
3. 最左前缀：联合索引(a, b, c)可用于a、a,b、a,b,c
4. 覆盖索引：查询字段都在索引里，避免回表
5. 索引不是越多越好：写入要维护索引，占空间

索引失效场景（必背）

-- 1. 对索引列做函数/运算
WHERE YEAR(create_time) = 2024 -- 失效
WHERE create_time >= '2024-01-01' -- 有效

-- 2. 隐式类型转换
WHERE phone = 13800138000 -- phone 是 varchar，可能失效

-- 3. 左模糊
WHERE name LIKE '%张%' -- 失效
WHERE name LIKE '张%' -- 可能用到索引

-- 4. OR 一侧无索引
WHERE a = 1 OR b = 2 -- b 无索引则可能全表扫

-- 5. 违反最左前缀
索引 (a,b,c)，条件只有 b、c

-- 6. 优化器判断全表扫更快
-- 小表、返回行数过多时可能不走索引

实战优化套路

-- 优化前：回表 + -filesort
SELECT * FROM orders WHERE user_id = 100 ORDER BY create_time DESC LIMIT 10;

-- 优化后：联合索引 (user_id, create_time)，覆盖索引
SELECT id, create_time FROM orders
WHERE user_id = 100 ORDER BY create_time DESC LIMIT 10;

4. SQL 优化

慢 SQL 排查流程

1. 开启慢查询日志 / 用 performance_schema

2. EXPLAIN 看执行计划

3. 看 type、key、rows、Extra

4. 改索引 / 改 SQL / 改表结构

5. 压测验证

EXPLAIN 重点字段

常见 SQL 优化

**避免 SELECT ***

-- 差
SELECT * FROM user WHERE id = 1;
-- 好
SELECT id, name, phone FROM user WHERE id = 1;

小表驱动大表

-- 差：大表驱动
SELECT * FROM big_table b JOIN small_table s ON b.id = s.big_id;

-- 好：小表放前面（MySQL 优化器也会调整，但写法要清晰）
SELECT * FROM small_table s JOIN big_table b ON s.big_id = b.id;

深分页优化

-- 慢：OFFSET 大时要跳过大量行
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

-- 方案1：延迟关联
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 10) t ON o.id = t.id;

-- 方案2：游标分页（推荐）
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;

JOIN 优化

• 被驱动表关联字段要有索引
• JOIN 字段类型一致
• 控制 JOIN 表数量，必要时用冗余字段

批量操作

-- 差：循环单条 INSERT
-- 好
INSERT INTO user (name, phone) VALUES
('a', '1'), ('b', '2'), ('c', '3');

-- 大批量可分批，每批 500~1000 条

COUNT 优化

-- 慢
SELECT COUNT(*) FROM orders WHERE status = 1;

-- 若只要近似值，可用统计表或缓存
-- 若 status 区分度高，建 (status) 或 (status, id) 索引

5. 架构层优化

读写分离

• 写走主库，读走从库
• 注意 主从延迟 导致读到旧数据
• 强一致读走主库，或关键业务做延迟双读

缓存（Redis）

• 热点数据放缓存，减轻 DB 压力
• 注意 缓存穿透、击穿、雪崩
• 更新策略：Cache Aside（先更 DB 再删缓存）较常见

分库分表

• 分片键选择：用户 ID、订单 ID 等高频查询字段
• 避免跨分片 JOIN、事务

连接池

• 应用侧用连接池（HikariCP、Druid）
• 避免每次请求新建连接

四、InnoDB 内核相关（加分项）

两阶段提交：redo log + binlog 保证数据一致性。

五、锁与事务（常和性能一起问）

• 行锁：InnoDB 默认，锁粒度小
• 间隙锁：可重复读下防幻读，可能降低并发
• 死锁：SHOW ENGINE INNODB STATUS查看，尽量按相同顺序访问行
• 大事务危害：锁持有久、undo 多、主从延迟大 → 事务尽量短

六、完整面试回答模板（1~2 分钟版）

我在项目里做 MySQL 优化，一般按这个流程：

1. 先定位
看慢查询日志、EXPLAIN、监控（QPS、连接数、锁等待、Buffer Pool 命中率）。先找 TOP 慢 SQL，不盲目优化。

2. 索引层
给高频 WHERE/ORDER BY 加合适索引；用联合索引和最左前缀；尽量覆盖索引减少回表；避免在索引列上函数运算、隐式转换、左模糊。

3. SQL 层
避免SELECT *；深分页用游标或延迟关联；批量用 batch insert；小表驱动大表；减少不必要的 JOIN 和子查询。

4. 表结构
字段类型合适、必要反范式、大字段拆分、历史数据归档或分区。

5. 配置与架构
Buffer Pool 给够内存；读写分离扛读流量；热点走 Redis；单表过大就分表，库成为瓶颈再分库。

6. 验证
优化前后对比EXPLAIN、执行时间、压测结果，并观察线上监控。

举个例子：订单列表慢，发现WHERE user_id=? ORDER BY create_time没合适索引，建了(user_id, create_time)联合索引，type 从 ALL 变成 ref，耗时从 2s 降到 50ms。

七、高频追问速答

八、和 Redis 优化对比（串联记忆）