一、MySQL 性能优化常见面试题清单类别高频问题索引索引原理、B树、聚簇/非聚簇、最左前缀、覆盖索引、索引失效SQL慢 SQL 排查、EXPLAIN、JOIN 优化、分页优化架构读写分离、分库分表、主从复制、缓存锁行锁/表锁、间隙锁、死锁、MVCC存储InnoDB vs MyISAM、redo/undo、Buffer Pool实战如何定位慢查询、如何优化一条 SQL、百万数据分页二、性能优化回答框架建议背这个结构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 12. 表结构设计原则说明选合适类型INT比BIGINT省空间VARCHAR(50)比VARCHAR(255)索引更小避免 NULL能用NOT NULL 默认值就用索引和计算更简单适度反范式高频查询可适当冗余减少 JOIN大字段拆分文本、JSON 放单独表避免拖慢主表分区表按时间/范围分区适合历史数据归档反例用SELECT *单表几千万行还不考虑归档或分表所有字段都建索引3. 索引优化面试最爱问索引原理B树InnoDB 默认 B树 索引聚簇索引叶子节点存整行数据主键二级索引叶子节点存主键值需要 回表索引设计原则WHERE / ORDER BY / GROUP BY 高频字段考虑索引区分度高 的字段更适合建索引如手机号 性别最左前缀联合索引(a, b, c)可用于a、a,b、a,b,c覆盖索引查询字段都在索引里避免回表索引不是越多越好写入要维护索引占空间索引失效场景必背-- 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. 优化器判断全表扫更快-- 小表、返回行数过多时可能不走索引实战优化套路-- 优化前回表 -filesortSELECT * FROM orders WHERE user_id 100 ORDER BY create_time DESC LIMIT 10;-- 优化后联合索引 (user_id, create_time)覆盖索引SELECT id, create_time FROM ordersWHERE user_id 100 ORDER BY create_time DESC LIMIT 10;4. SQL 优化慢 SQL 排查流程1. 开启慢查询日志 / 用 performance_schema2. EXPLAIN 看执行计划3. 看 type、key、rows、Extra4. 改索引 / 改 SQL / 改表结构5. 压测验证EXPLAIN 重点字段字段关注点typesystem const eq_ref ref range index ALL至少到range最好refkey实际用到的索引rows预估扫描行数越小越好Extra避免Using filesort、Using temporary常见 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 oJOIN (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 再删缓存较常见分库分表时机信号分表单表千万级、索引变大、维护变慢分库连接数、I/O、备份恢复成为瓶颈分片键选择用户 ID、订单 ID 等高频查询字段避免跨分片 JOIN、事务连接池应用侧用连接池HikariCP、Druid避免每次请求新建连接四、InnoDB 内核相关加分项概念作用Buffer Pool数据页、索引页缓存减少磁盘 I/Oredo log崩溃恢复WALundo log回滚、MVCCbinlog主从复制、数据恢复MVCC读写不阻塞实现可重复读两阶段提交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。七、高频追问速答追问回答要点聚簇索引和非聚簇索引聚簇叶子存整行一张表一个非聚簇叶子存主键需回表为什么用 B 树不用 B 树叶子链表便于范围查询非叶子只存 key单页能放更多索引树更矮覆盖索引是什么查询列都在索引中不需回表如(user_id, create_time)只查这两列如何定位慢 SQL慢日志、pt-query-digest、EXPLAIN、Performance Schema主从延迟怎么办并行复制、业务容忍、强一致走主库、关键链路异步补偿分库分表后怎么做分页游标分页、禁止跨库 OFFSET 大分页、搜索引擎ES索引下推ICP5.6 在存储引擎层先过滤减少回表一个表多少数据该分表没有固定值一般单表 500 万~2000 万 或索引/维护明显变慢时考虑八、和 Redis 优化对比串联记忆维度MySQLRedis瓶颈磁盘 I/O、锁、复杂 SQL内存、单线程、热 key核心手段索引、SQL、架构数据结构、Pipeline、集群缓存角色被缓存的对象做缓存的层典型组合MySQL Redis 读写分离缓存热点保护 MySQL