mysql 性能优化面试要点

一、总览与排查思路类

Q1：发现 MySQL 慢，你的排查思路是什么？

1. 定位：开慢查询日志，找 TOP SQL。

2. 分析：EXPLAIN 看执行计划，type、key、rows、Extra。

3. 优化：加/改索引、改写 SQL、调整表结构。

4. 验证：对比优化前后耗时、扫描行数。

5. 兜底：缓存、读写分离、分库分表。

Q2：慢查询日志怎么用？

• 开启：slow_query_log=ON，long_query_time=1。

• 查看：mysqldumpslow、pt-query-digest 分析 TOP SQL。

• log_queries_not_using_indexes 记录未走索引的查询。

Q3：EXPLAIN 关注哪些字段？

Q4：Extra 常见值含义？

• Using index：覆盖索引，好。

• Using where：Server 层过滤。

• Using filesort：额外排序，需优化。

• Using temporary：临时表，常见 GROUP BY、DISTINCT。

• Using index condition：索引下推。

二、索引优化类

Q5：怎么判断索引该不该建？

• 看查询频率、区分度（cardinality）、是否覆盖、写入代价。

• 区分度低（如性别）不适合单独建。

• 联合索引优先，能复用最左前缀。

Q6：联合索引怎么设计顺序？

• 等值查询列在前，范围查询列在后。

• 区分度高的列在前。

• 兼顾最左前缀，让高频单列查询也能用上。

• 例：WHERE a=? AND b=? AND c>?，建 (a,b,c)。

Q7：索引失效有哪些场景？

1. 对索引列做函数或运算。

2. 隐式类型转换。

3. LIKE '%abc'。

4. OR 有一侧没索引。

5. 不满足最左前缀。

6. !=、NOT IN、IS NOT NULL 有时失效。

7. 优化器估算成本高主动放弃。

Q8：覆盖索引怎么优化查询？

• 让查询列全在索引里，避免回表。

• 例：SELECT a,b FROM t WHERE a=?，建 (a,b)。

• EXPLAIN 显示 Using index。

Q9：索引下推（ICP）是什么？

• 把 WHERE 中能在索引里判断的条件下推到引擎层。

• 减少回表次数。

• MySQL 5.6+ 支持，Extra 显示 Using index condition。

Q10：索引越多越好吗？

• 不好。占空间、写放大、优化器选择成本高。

• 原则：为高频查询建，联合索引复用，控制数量。

三、SQL 优化类

Q11：深分页 LIMIT 100000,10 为什么慢？怎么优化？

• 慢在要扫描并丢弃前 100000 行。

• 优化：

  1. 延迟关联：先用覆盖索引取主键，再回表。

     sql

     SELECT * FROM t JOIN (SELECT id FROM t ORDER BY id LIMIT 100000,10) x USING(id);

  2. 游标：WHERE id > last_id LIMIT 10。

  3. 业务限制页码。

Q12：ORDER BY 怎么优化？

• 让排序列与索引顺序一致，满足最左前缀，避免 filesort。

• 例：索引 (a,b)，ORDER BY a,b 可避免排序。

• 无法避免时，调 sort_buffer_size。

Q13：GROUP BY 怎么优化？

• 利用索引有序性，避免临时表。

• 先过滤再分组。

• 必要时用 WHERE 替代 HAVING。

Q14：JOIN 怎么优化？

• 小表驱动大表，减少外层循环。

• 关联列建索引。

• 避免 JOIN 字段类型不一致。

• 控制 join_buffer_size。

• 大表 JOIN 考虑冗余字段或分库分表。

Q15：IN 和 EXISTS 怎么选？

• 子查询结果小用 IN，外层表小用 EXISTS。

• 本质是小表驱动大表。

• 现代优化器多数能自动优化。

Q16：COUNT(*) 慢怎么优化？

• InnoDB 不存总数，要扫索引。

• 优化：

  • 用最小的二级索引扫。

  • 维护计数表，异步更新。

  • 用 Redis 计数。

  • 近似值用 SHOW TABLE STATUS。

Q17：批量插入怎么优化？

• 合并 INSERT INTO ... VALUES (...),(...),...。

• 关闭自动提交，事务批量提交。

• LOAD DATA INFILE 最快。

• 调 bulk_insert_buffer_size。

Q18：UPDATE/DELETE 大表怎么优化？

• 分批：LIMIT 循环删。

• 走索引，避免锁全表。

• 低峰执行，控制事务大小。

• 必要时用 pt-archiver。

四、表结构与设计类

Q19：字段类型怎么选？

• 更小更简单：INT 够用不用 BIGINT。

• 金额用 DECIMAL，不用 FLOAT。

• 定长用 CHAR，变长用 VARCHAR。

• 时间用 DATETIME 或 TIMESTAMP，注意 2038。

• 避免 NULL，用默认值。

Q20：为什么要避免 SELECT *？

• 传输多余列，增加网络和内存。

• 可能无法用覆盖索引，导致回表。

• 表结构变更时易出错。

Q21：范式与反范式怎么取舍？

• 范式减少冗余，保证一致性。

• 反范式冗余字段，减少 JOIN，提升查询。

• 读多写少可适当反范式。

Q22：大表怎么优化？

• 冷热分离，历史数据归档。

• 垂直拆分：拆冷热字段。

• 水平拆分：分库分表。

• 加缓存。

Q23：VARCHAR(255) 和 VARCHAR(20) 有区别吗？

• 存储按实际长度，但索引和排序可能用最大长度。

• 更小更利于内存和临时表。

• 按业务选合适长度。

五、事务与锁优化类

Q24：长事务有什么危害？

• 占 undo log，版本链长，影响 MVCC。

• 持锁久，阻塞并发，易死锁。

• 连接和内存占用大。

• 主从延迟大。

Q25：怎么减少锁冲突？

• 走索引，缩小锁范围。

• 降隔离级别到 RC，减少间隙锁。

• 小事务，快提交。

• 固定顺序访问资源。

Q26：RR 和 RC 怎么选？

• RR：一致性强，间隙锁多，并发低。

• RC：并发高，无间隙锁，但可能幻读。

• 互联网业务常用 RC + binlog ROW。

六、架构与高可用优化类

Q27：读写分离怎么用？

• 主库写，从库读。

• 中间件：ShardingSphere、MyCat。

• 注意主从延迟，读己之写。

Q28：主从延迟怎么解决？

• 半同步复制、并行复制。

• 读己之写：写后短时间读主。

• 分片，减少单库压力。

• 监控 Seconds_Behind_Master。

Q29：分库分表怎么分？

• 垂直：按业务或字段拆。

• 水平：按分片键拆，如 user_id 取模。

• 解决跨库 JOIN、分布式事务、全局 ID、分页。

Q30：缓存怎么用？

• 热点数据放 Redis。

• 缓存穿透、击穿、雪崩要处理。

• 缓存与 DB 一致性：先更新 DB 再删缓存。

七、参数与硬件类

Q31：常用优化参数有哪些？

Q32：innodb_buffer_pool_size 怎么设？

• 设物理内存的 50%~70%。

• 让热数据常驻内存，减少磁盘 I/O。

• 太小会频繁刷盘，太大影响 OS。

Q33：连接数怎么设？

• max_connections 按并发和内存调。

• 用连接池，避免频繁建连。

• 连接过多会上下文切换、内存压力。

八、高频陷阱题

Q34：加了索引一定快吗？

• 不一定。区分度低、返回大量行、写多读少时可能拖慢。

• 优化器可能放弃索引。

Q35：EXPLAIN 的 rows 一定准吗？

• 是估算值，可能偏差大。

• 统计信息不准时用 ANALYZE TABLE 更新。

Q36：为什么优化器会选错索引？

• 统计信息不准、采样偏差、成本估算与实际不符。

• 用 FORCE INDEX、ANALYZE TABLE 或改写 SQL。

Q37：分页越往后越慢，根本原因？

• 要扫描并丢弃前面所有行。

• 用游标或延迟关联解决。

Q38：NULL 对索引和性能有影响吗？

• IS NULL 可用索引，但区分度低。

• NULL 占空间，聚合和比较要特殊处理。

• 尽量设 NOT NULL + 默认值。