- 难度： 中等
- MySQL 数据库
推荐答案
回答重点
MySQL 架构分两层：Server 层负责连接管理、SQL 解析、查询优化这些逻辑处理，存储引擎层负责数据的实际存取。一条 SQL 从发出到拿到结果，大概经过这么几步：
- 客户端先跟 MySQL 建立连接，连接器负责验证账号密码和权限，确认你能对哪些库表做什么操作。
- 老版本会先查查询缓存，看有没有一模一样的 SQL 缓存过结果。MySQL 8.0 把这个功能砍了，因为表一更新缓存就全失效，命中率太低。
- 分析器对 SQL 做词法分析和语法分析。先把字符串拆成一个个 token，识别出 SELECT、表名、列名、WHERE 条件这些元素；再按语法规则检查 SQL 写得对不对，最后生成一棵抽象语法树。
- 优化器拿到语法树后，决定用哪个索引、多表 JOIN 时先查哪张表、子查询要不要改写成 JOIN，最终生成一个执行计划。
- 执行器按照执行计划，调用存储引擎的接口一行一行地读数据，做条件过滤，最后把结果集返回给客户端。
连接器建连、分析器解析、优化器决策、执行器调度、存储引擎存取，这就是完整的流程。
扩展知识
拿一条查询语句走一遍完整流程，一步步拆解。 以 SELECT * FROM user WHERE id = 1 为例。
客户端发起连接请求，通过 TCP 三次握手跟 MySQL Server 建立连接。连接器收到后，先验证用户名密码，错了返回 Access denied for user。验证通过后，连接器会去权限表里查这个用户有哪些权限，后面这个连接里的所有操作都用这套权限，哪怕中途用 GRANT 改了权限，也得重新连接才生效。
连接建好之后，如果一直没有操作，连接会处于空闲状态。默认8小时后自动断开，这个时间由 wait_timeout 参数控制。长连接用多了可能导致内存涨得厉害，因为 MySQL 执行过程中的临时内存是管理在连接对象里的，连接不断内存就不释放。解决办法是定期断开重连，或者用 MySQL 5.7 之后的 mysql_reset_connection 重置连接状态。
分析器先做词法分析，把 SELECT * FROM user WHERE id = 1 拆成一个个 token：SELECT 是关键字，* 表示所有列，FROM 后面的 user 是表名，WHERE 后面是过滤条件，id 是列名，1 是常量值。
然后做语法分析，按 MySQL 的语法规则检查这些 token 组合在一起对不对。语法有问题就报 You have an error in your SQL syntax。语法分析完成后会生成一棵抽象语法树 AST，这棵树描述了 SQL 的结构，但还没有决定怎么执行。
优化器拿到语法树后开始干事。它要决定用哪个索引、怎么扫描表。id 是主键，走主键索引肯定最快，这个比较简单。复杂的场景比如多表 JOIN，优化器要决定表的连接顺序，一般原则是小表驱动大表。如果一个查询有多个索引可选，优化器会根据统计信息估算每个方案的成本，选代价最小的。
优化器还会做一些 SQL 改写，比如把子查询转成 JOIN、常量折叠、条件下推这些。改写完成生成最终的执行计划，可以用 EXPLAIN 命令查看执行计划的细节：type 列显示是全表扫描还是索引扫描，key 列显示实际用了哪个索引，rows 列估算要扫描多少行。
执行器按照执行计划开始执行。先再检查一遍对这张表有没有查询权限，然后调用存储引擎的接口。InnoDB 提供了一套 Handler API，执行器通过这套接口操作数据。
对于 SELECT * FROM user WHERE id = 1 这条语句，执行器调用 InnoDB 接口，InnoDB 通过主键索引在 B+ 树上找到 id=1 的叶子节点，直接拿到完整的行数据返回。如果查询条件不是主键，比如 WHERE name = 'Yes'，可能要走二级索引，先找到对应的主键值，再回表查完整数据。
查询和更新的区别
上面讲的是查询语句的流程。如果是 INSERT、UPDATE、DELETE 这种更新语句，前面连接、解析、优化的流程差不多，区别在执行阶段。
更新语句需要写日志来保证数据安全和崩溃恢复。InnoDB 会先写 redo log，记录这次修改的物理变化；然后写 undo log，记录修改前的数据用于回滚；Server 层还要写 binlog，用于主从复制和数据恢复。
为了保证 redo log 和 binlog 的一致性，MySQL 用了两阶段提交：先写 redo log 标记为 prepare 状态，再写 binlog，最后把 redo log 标记为 commit。这样即使中途崩溃，恢复时也能根据日志状态判断事务该提交还是回滚。
更新语句还涉及锁。InnoDB 默认行锁，通过 MVCC 实现读写不阻塞。UPDATE 语句执行时会先读取要修改的行，加上排他锁，修改完成后在事务提交时释放锁。
优化器不是万能的
优化器依赖统计信息来估算成本，统计信息不准就可能选错执行计划。常见的坑：
- 表数据量变化大但没及时 ANALYZE TABLE，统计信息过期了。
- 数据分布不均匀，优化器按平均值估算，实际扫描行数差很多。
- 多个索引看起来都能用，优化器选了不是最优的那个。
遇到执行计划不对的情况，可以用 FORCE INDEX 强制指定索引，或者用 STRAIGHT_JOIN 强制指定表连接顺序。但这些 hint 用多了代码会很难维护，根本办法还是保证统计信息准确、索引设计合理。
面试官追问
提问：分析器和优化器有什么区别？感觉都是处理 SQL 的。
回答：分析器只管 SQL 写得对不对，不管怎么执行最快。它把 SQL 字符串拆成 token，检查语法，生成一棵描述 SQL 结构的语法树。优化器拿到这棵树后，要决定具体怎么执行：用哪个索引、表连接顺序、子查询要不要改写。同一条 SQL，语法树是固定的，但执行计划可能有很多种，优化器负责挑一个成本最低的。
提问：为什么 MySQL 8.0 把查询缓存砍掉了？
回答：查询缓存的失效策略太激进了，只要表有任何写操作，这张表相关的所有缓存全部失效。高并发写的场景下，缓存几乎没有命中的机会，反而增加了维护缓存的开销。读多写少的场景用 Redis 这种专业缓存更靠谱，MySQL 自己的查询缓存属于鸡肋功能，干脆砍了省事。
提问：执行器调用存储引擎接口，这个接口大概是什么样的？
回答：存储引擎提供的是 Handler API，是一套统一的接口规范。主要的方法有：ha_open 打开表，ha_rnd_next 全表扫描读下一行，ha_index_read 索引定位读取，ha_write_row 写入一行，ha_delete_row 删除一行。不同的存储引擎实现这套接口，Server 层不用关心底层是 InnoDB 还是 MyISAM，代码一样调。这也是 MySQL 能支持多种存储引擎的原因。
提问：EXPLAIN 看到执行计划走了索引，但查询还是很慢，可能是什么原因？
详细解答可以看：MySQL 中使用索引一定有效吗？如何排查索引效果？
回答：几种常见情况。第一，回表次数太多，二级索引命中了几十万行，每行都要回表查完整数据，随机 IO 拉垮了。第二，索引选择性太差，比如在性别字段上建索引，过滤效果很差。第三，返回字段太多，不是覆盖索引，大量数据传输耗时。第四，索引树太大内存装不下，频繁从磁盘读取。看 EXPLAIN 重点关注 rows 估算值和 Extra 列有没有 Using filesort、Using temporary 这些。
