数据库与SQL优化面试
本章聚焦数据库与SQL优化核心：索引原理、慢查询排查、事务隔离级别及分表分库策略。面试中，大厂常以真实业务场景考察候选人从“问题定位”到“方案落地”的完整链路，并关注企业级性能与一致性权衡。下方框架图概括优化回答路径，后附问答卡片与项目案例帮助实战化吸收。
数据库优化核心速览
| 模块 | 核心要点 | 面试高频词 |
|---|---|---|
| 索引 | B+树结构、最左前缀、覆盖索引、索引下推 | explain type/rows/key_len |
| 慢查询 | 慢日志开启、explain分析、索引优化/语句重构 | filesort、temporary、Using index |
| 事务 | ACID、MVCC实现、四种隔离级别、幻读与间隙锁 | RC与RR选择、死锁日志分析 |
| 分表分库 | 垂直/水平切分、分片算法、路由策略、全局ID | 一致性哈希、扩容迁移、分布式事务 |
面试前建议调研目标公司技术栈（MySQL/MyCAT/ShardingSphere/TiDB）。
Q1：请说说MySQL的索引底层数据结构，以及联合索引的最左前缀原则是如何实现的？
考察点：是否真正理解B+树与索引组织；能否用实际查询解释最左匹配。
回答框架：
① 索引结构：InnoDB使用B+树，非叶子节点存索引键+指针，叶子节点有序存储完整数据（聚簇）或主键（二级索引）。
② 回表与覆盖索引：二级索引查到主键后回表；若查询字段全在索引中则直接返回。
③ 最左前缀：联合索引(a,b,c)按(a,b,c)排序，跳过第一列则无法利用索引；范围查询后列失效。
示例回答：
「MySQL InnoDB索引采用B+树，叶子节点双向链表便于范围扫描。联合索引最左前缀通过比较排序规则实现：索引按字段顺序建立键值排序，若查询条件不包含最左字段，则无法在B+树中定位起始范围。例如索引(a,b)只能匹配WHERE a=1 AND b>2，无法直接跳过a单独查b。」
可能追问：
- 为什么选择B+树而不是B树？
- 如何设计索引避免filesort？
- 线上大表添加索引需要注意什么？
Q2：线上SQL突然变慢，你如何定位和优化？请详细说明排查步骤。
考察点：系统化排查能力；explain各字段含义；常见优化手段。
回答框架：
① 开启慢查询日志，设置long_query_time=1，抓取慢SQL。
② 使用EXPLAIN分析执行计划：关注type（ALL->index->range->ref->const）、rows、Extra（Using filesort/Using temporary）。
③ 优化方向：加索引/复合索引、改写SQL（拆分大IN、避免函数索引）、调整查询逻辑（延迟关联）。
④ 验证：观察优化前后查询时间及explain变化。
示例回答：
「首先用EXPLAIN查看type列：若出现ALL全表扫描且rows很大，考虑加索引。比如一条SELECT * FROM orders WHERE status=0 AND create_time>'2024-01-01'，原无索引，加联合索引(status, create_time)后type变为ref，扫描行数从100万降到5000，查询耗时从2.3s降到15ms。」
可能追问：
- 如何判断索引是否被有效使用？ key_len怎么计算？
- 表数据量大，线上加索引会不会锁表？如何在线加索引？
- 优化时发现Extra里有“Using join buffer”是什么问题？
Q3：MySQL事务隔离级别有哪些？间隙锁是什么？如何避免幻读？
考察点：隔离级别与并发问题对应关系；MVCC实现；间隙锁机制。
回答框架：
① 四种级别：READ UNCOMMITTED、READ COMMITTED（RC）、REPEATABLE READ（RR）、SERIALIZABLE。
② 幻读：RR级别下通过MVCC + 间隙锁（gap lock）解决。间隙锁锁定索引记录之间的间隙，防止其他事务插入新行。
③ 死锁：使用gant lock时可能产生死锁，可通过降低锁粒度或改用RC级别（但会损失可重复读）缓解。
示例回答：
「MySQL默认RR级别，通过MVCC实现快照读，当前读时使用Next-Key锁（行锁+间隙锁）来防止幻读。例如事务对范围(10,20)执行SELECT ... FOR UPDATE，间隙锁锁住(10,20)区间，其他事务无法插入id=15的行，从而避免幻读。但在高频插入场景下易引发死锁，可考虑将隔离级别设为RC并配合应用幂等重试。」
可能追问：
- 解释MVCC的ReadView生成策略？
- 间隙锁什么时候会退化为行锁或表锁？
- 你们公司的业务场景中隔离级别是怎么选择的？为什么？
项目案例：用户订单中心分表分库改造
项目名称：用户订单中心分表分库改造
业务背景：公司C端电商订单增长迅速，MySQL单表1.2亿数据，高峰写入TPS 3000+，主从同步延迟超过5秒，查询耗时可超过2秒。
规模指标：订单表日增500万行，总数据量约500GB；涉及用户表、商品表、订单详情表等关联查询；QPS约2000。
技术栈：MySQL 5.7 + MyCAT 1.6 + Redis（热点缓存） + 雪花算法生成全局ID + 分片键使用用户ID（取模64）。
核心难点：
1. 分片键选择：订单查询维度多（用户ID、订单ID、商家ID），需支持跨分片查询。
2. 扩容迁移：从16个分片扩容到64个分片时如何不停机迁移？
3. 分布式事务：订单创建涉及多个库（订单主表、库存表），使用TCC方案保证最终一致性。
面试高频追问：
- 为什么选择取模分片而不是一致性哈希？
- 跨分片查询（如按时间范围查所有用户订单）如何优化？
- 你们如何解决全局ID的时钟回拨问题？
- 扩容时数据迁移的具体步骤？
可迁移回答话术：
「当时我们选择了用户ID取模的方式分片，因为业务90%的查询是按用户ID查订单，跨分片查询通过MyCAT自动路由到多个节点后合并结果。针对范围查询场景，我们新建了一张时间序列表用于快速定位分片，配合Elasticsearch做订单全文搜索。扩容时采用‘双写+增量同步’方案，先扩容新分片，双写一段时间后校验数据再切流量。这个思路也可以用在其他垂直分库的场景。」
- 分片键选取逻辑：分析业务查询比例，选择最高频的维度作为路由键。
- 跨分片查询方案：可以通过中间件合并或引入异构存储（ES、ClickHouse）做查询层。
- 扩容迁移方案：双写策略适用于大部分分库分表系统，关键在于校验和灰度切换。