MySQL 查询、事务与索引

一条 SELECT 如何执行

以 MySQL 8.0/InnoDB 为例，典型链路是：

1. 连接器：建立连接、身份认证、权限校验和连接管理；
2. 解析器：词法/语法分析，生成语法树并识别表、列、函数等；
3. 预处理器：校验表列是否存在、权限等，并处理部分改写；
4. 优化器：基于统计信息生成执行计划，选择索引、连接顺序和访问方式；
5. 执行器：按计划调用存储引擎读取/修改数据，进行条件过滤、聚合、排序等，并向客户端返回结果；
6. 存储引擎：例如 InnoDB，负责索引、页缓存、事务、锁和实际数据读写。

MySQL 的 Query Cache 已在 MySQL 8.0 移除，不能再把它作为现代 MySQL SELECT 的固定步骤。应用/Redis/CDN 缓存通常更可控。

用 EXPLAIN 或 EXPLAIN ANALYZE 查看实际访问路径、扫描行数、索引使用和执行耗时，是 SQL 优化的起点。

事务与 ACID

事务把多个操作组成不可分割的工作单元：

• 原子性（Atomicity）：全部成功，或全部回滚；InnoDB 主要通过 undo log 支撑回滚。
• 一致性（Consistency）：事务前后都满足业务约束和数据库约束；它是最终目标，依赖原子性、隔离性、持久性及应用正确性共同实现。
• 隔离性（Isolation）：并发事务的中间状态不应产生不符合隔离级别的干扰。
• 持久性（Durability）：提交后的数据在故障后仍可恢复；redo log 的 WAL 等机制是关键支撑。

隔离级别与并发问题

隔离级别	脏读	不可重复读	幻读	说明
Read Uncommitted	可能	可能	可能	可读到未提交数据
Read Committed	不会	可能	可能	每次一致性读可能取得新快照
Repeatable Read	不会	不会	实现相关	InnoDB 默认；一致性读使用同一快照，当前读通过 next-key lock 等避免很多幻读场景
Serializable	不会	不会	不会	效果近似串行，代价高、并发低

• 脏读：读到其他事务尚未提交、可能回滚的数据。
• 不可重复读：同一事务两次读同一行，读到已提交的不同值。
• 幻读：同一条件两次查询，结果行集合因其他事务插入/删除而变化。

隔离级别不是万能的业务一致性方案。转账、扣库存等关键逻辑还应使用正确的条件更新、唯一约束、锁或乐观并发控制。

MySQL 常见存储引擎

引擎	特点
InnoDB	默认引擎；支持事务、崩溃恢复、行级锁、外键（按需使用）、MVCC；适合绝大多数 OLTP 场景
MyISAM	表级锁，不支持事务与崩溃恢复；历史引擎，特定只读/遗留场景才考虑
MEMORY	数据主要驻内存，重启后内容丢失；受容量与数据类型限制，适合临时数据等特定需求

它们是存储引擎，主要决定表数据/索引存储与事务锁等行为，不是“查询执行引擎”。

为什么 InnoDB 索引使用 B+ 树

B+ 树是多路平衡搜索树，适配磁盘/页式存储：

• 非叶子节点只保存键与子节点指针，单页可容纳更多键，树更矮，查找所需页 I/O 更少；
• 所有记录（聚簇索引的完整行，二级索引的索引键及主键）位于叶子层，查询路径稳定；
• 叶子页按键有序并通过链表连接，范围扫描、排序和前缀查询高效；
• 节点分裂/合并只影响局部路径，且页大小固定，适合数据库缓冲池管理。

B 树单点查询不具有 O(1) 复杂度，同样为对数级；B+ 树的优势更多来自更高扇出、叶子顺序链和统一的数据位置。

索引失效/未被使用的常见原因

严格说很多情况是“优化器可能不选索引”而非索引绝对失效，应以执行计划为准：

• 不满足联合索引最左前缀，或在前导列上使用范围条件后，后续列难以继续用于常规定位；
• 对索引列做函数、表达式或隐式类型转换，如 WHERE DATE(created_at) = ...、字符串列与数字常量比较；
• LIKE '%keyword' 前置通配符无法走普通 B+ 树前缀索引；
• !=、<>、低选择性条件、返回行比例过高时，全表扫描可能更便宜；
• OR 两侧无合适索引、统计信息不准，或索引合并成本较高；
• ORDER BY、GROUP BY 与索引顺序不匹配，仍需 filesort/临时表。

优化方向：让列保持“裸列”参与比较、设计符合查询模式的联合索引、控制返回列和行数、更新统计信息，并使用 EXPLAIN ANALYZE 验证，而不是机械加索引。