MySQL一揪到底
前言
汇总罗列MySQL点点滴滴
参考书籍
点点翻翻了下面这些，各有特点，各有所长属于是
- 《MySQL是怎样运行的 从根儿上理解MySQL》：白话“啰嗦”，但是容易理解
- 《MySQL技术内幕 InnoDB存储引擎 第2版 》：章节脉络很清楚，偏向InnoDB源码的，可作为补充看，非DBA不值得长时间深挖。
- 《高性能MySQL》：宝典，整体介绍全面，对MySQL整体脉络看的比较清楚
- 官网手册(5.7版本)：这没得说，最全的。PS：英语读的累的用Google Translate页面翻译，专业名词翻译的可读性较高。
更新日历
- 2025-03-03
- 调整文章结构
- 删除冗余章节
- 补充MVCC对事务隔离级别的实现总结
- 2024年11月
- 结合《MySQL技术内幕 InnoDB存储引擎》等书/官方文档作为细节知识点补充，完善本2年前的博客。
- 对原有知识重点部分加粗，补充若干图片辅助理解
- 删除部分废话
一些底层数据结构比较
为什么InnoDB要选择用B+树
InnoDB 存储引擎使用 B+ 树 作为其索引结构的主要原因在于 B+ 树在数据库系统中具有显著的优势，能够高效支持数据库的查询、插入、删除和范围查询等操作。
- 高效的查询性能
- B+ 树是一个平衡多路搜索树，所有叶子节点都在同一层，保证了查询的时间复杂度为 O(log n) 其中 n 是索引项的数量。
- 对于数据库系统来说，B+ 树的高度通常很低（3-4 层），即使数据量非常大，查询也只需要几次磁盘 I/O 操作。
- 适合磁盘存储
- B+ 树的节点大小通常设计为与磁盘块（Page）大小一致（如 16KB），这样可以最大化利用磁盘 I/O，减少读取次数。
- 由于磁盘 I/O 是数据库操作的主要性能瓶颈，B+ 树通过减少磁盘访问次数来提升性能。
- 支持范围查询
- B+ 树的所有数据都存储在叶子节点上，并且叶子节点之间通过指针连接成一个有序链表。
- 这种结构非常适合范围查询（如
BETWEEN、>、<等操作），因为只需要遍历叶子节点链表即可，而不需要回溯到上层节点。
- 减少树的高度
- B+ 树的分支因子（每个节点的子节点数）较大，通常为几百到几千，这使得树的高度较低。
- 较低的树高度意味着更少的磁盘 I/O 操作，从而提高了查询效率。
- 数据存储与索引分离
- 在 B+ 树中，非叶子节点只存储索引键（Key），而不存储实际数据，这样可以容纳更多的索引项，进一步减少树的高度。
- 实际数据存储在叶子节点中，这种设计使得索引和数据分离，便于管理和优化。
- 支持高效的插入和删除
- B+ 树在插入和删除操作时能够保持树的平衡，避免退化为链表。
- 插入和删除的时间复杂度也是 O(log n) ，适合频繁更新的数据库场景。
- 适合大规模数据
- B+ 树能够很好地处理大规模数据，因为它的高度增长非常缓慢（对数级别）。
- 即使数据量达到数百万甚至数十亿条记录，B+ 树仍然能够保持高效的查询性能。
- 与 InnoDB 的事务特性兼容
- InnoDB 支持事务和 MVCC（多版本并发控制），B+ 树的索引结构能够很好地支持这些特性。
- 例如，B+ 树的叶子节点可以存储指向多个版本数据的指针，从而支持 MVCC。
- 支持聚簇索引
- InnoDB 使用 B+ 树实现聚簇索引（Clustered Index），即将数据行直接存储在叶子节点中。
- 这种设计使得主键查询非常高效，因为数据行和索引存储在一起，减少了额外的磁盘 I/O。
- 对比其他数据结构的优势
- 与 B 树对比：B+ 树的所有数据都存储在叶子节点中，非叶子节点只存储索引键，这使得 B+ 树的查询性能更稳定，且更适合范围查询。
- 与哈希索引对比：哈希索引虽然适合等值查询，但不支持范围查询和排序操作，而 B+ 树可以同时支持等值查询和范围查询。
- 与二叉树对比：二叉树的高度较高，且不适合磁盘存储，而 B+ 树的高度低且适合磁盘 I/O。
总结
InnoDB 使用 B+ 树作为索引结构，主要是因为 B+ 树在查询性能、范围查询、磁盘 I/O 优化、插入删除效率等方面具有显著优势。它能够很好地支持数据库的核心操作，同时适应大规模数据存储和高并发场景。这些特性使得 B+ 树成为关系型数据库中最常用的索引结构之一。
B+树与B树的细节对比
B+ 树、B 树和是常见的多路平衡搜索树，它们在数据库和文件系统中广泛应用。以下是它们之间的主要差异总结：
- B 树（B-Tree）
- 定义：
- B 树是一种平衡多路搜索树，每个节点可以包含多个键和多个子节点。
- 所有节点（包括内部节点和叶子节点）都可以存储数据。
- 特点：
- 每个节点的键值按升序排列。
- 非叶子节点既存储键值，也存储指向子节点的指针。
- 叶子节点之间没有指针连接。
- 优点：
- 适合随机查询，查询路径较短。
- 缺点：
- 范围查询效率较低，因为叶子节点之间没有直接连接。
- 数据分布在所有节点中，可能导致查询性能不稳定。
- 定义：
- B+ 树（B Plus Tree）
- 定义：
- B+ 树是 B 树的一种变种，所有数据都存储在叶子节点中，内部节点只存储键值和指向子节点的指针。
- 特点：
- 非叶子节点只存储索引键，不存储实际数据。
- 所有叶子节点通过指针连接成一个有序链表。
- 数据只存储在叶子节点中。
- 优点：
- 范围查询效率高，因为叶子节点之间通过指针连接，可以快速遍历。
- 查询性能更稳定，因为所有查询都需要遍历到叶子节点。
- 更适合磁盘存储，因为非叶子节点可以存储更多的键值，减少树的高度。
- 缺点：
- 对于随机查询，可能需要遍历到叶子节点，路径较长。
- 定义：
- B 树与 B+ 树的对比
特性 B 树 B+ 树 数据存储位置 所有节点（内部节点和叶子节点）都可以存储数据。 数据只存储在叶子节点中，内部节点只存储索引键。 叶子节点连接 叶子节点之间没有指针连接。 叶子节点之间通过指针连接成有序链表。 范围查询效率 较低，需要回溯到上层节点。 较高，直接遍历叶子节点链表即可。 查询性能稳定性 不稳定，数据分布在所有节点中。 稳定，所有查询都需要遍历到叶子节点。 树的高度 较高，因为内部节点存储数据。 较低，因为内部节点只存储索引键。 适用场景 适合随机查询。 适合范围查询和磁盘存储。 - B- 树的误解
- 有些人误认为 B- 树是 B 树的一种变种，但实际上 B- 树就是 B 树，两者是相同的结构。
- 这种误解可能源于对“B-”符号的错误理解，实际上“B-”只是 B 树的另一种写法。
- 总结
- B 树：数据可以存储在所有节点中，适合随机查询，但范围查询效率较低。
- B+ 树：数据只存储在叶子节点中，适合范围查询和磁盘存储，查询性能更稳定。
红黑树与B+树之间的差异
B+ 树和红黑树是两种常见的平衡树结构，它们在数据库、文件系统和各种算法中都有广泛应用。以下是它们的详细对比：
- 基本定义
- B+ 树：
- 一种多路平衡搜索树，每个节点可以包含多个键和多个子节点。
- 所有数据都存储在叶子节点中，内部节点只存储索引键。
- 叶子节点通过指针连接成有序链表。
- 红黑树：
- 一种二叉平衡搜索树，每个节点最多有两个子节点。
- 通过颜色标记（红色和黑色）和旋转操作保持树的平衡。
- 数据存储在所有节点中。
- B+ 树：
- 树的结构
- B+ 树：
- 多叉树结构，分支因子较大（通常为几百到几千）。
- 树的高度较低，适合存储大规模数据。
- 红黑树：
- 二叉树结构，每个节点最多有两个子节点。
- 树的高度较高，适合存储小规模数据。
- B+ 树：
- 平衡性
- B+ 树：
- 通过节点的分裂和合并操作保持平衡。
- 平衡性较强，适合磁盘存储和范围查询。
- 红黑树：
- 通过颜色标记和旋转操作保持平衡。
- 平衡性较弱，但足以保证基本操作的时间复杂度。
- B+ 树：
- 查询性能
- B+ 树：
- 查询时间复杂度为
O(log n)，但由于分支因子较大，实际查询路径较短。 - 适合范围查询，因为叶子节点通过指针连接成有序链表。
- 查询时间复杂度为
- 红黑树：
- 查询时间复杂度为
O(log n)，但由于是二叉树，查询路径较长。 - 适合等值查询，范围查询效率较低。
- 查询时间复杂度为
- B+ 树：
- 插入和删除性能
- B+ 树：
- 插入和删除操作的时间复杂度为
O(log n)。 - 可能需要分裂或合并节点，但操作相对高效。
- 插入和删除操作的时间复杂度为
- 红黑树：
- 插入和删除操作的时间复杂度为
O(log n)。 - 可能需要多次旋转操作来保持平衡。
- 插入和删除操作的时间复杂度为
- B+ 树：
- 存储效率
- B+ 树：
- 适合磁盘存储，因为节点大小通常与磁盘块大小一致，可以减少磁盘 I/O 操作。
- 内部节点只存储索引键，可以容纳更多的键值。
- 红黑树：
- 适合内存存储，因为节点较小且结构简单。
- 在磁盘存储中效率较低，因为树的高度较高，可能导致更多的磁盘 I/O。
- B+ 树：
- 适用场景
- B+ 树：
- 数据库索引（如 MySQL 的 InnoDB 引擎）。
- 文件系统（如 NTFS、ReiserFS）。
- 大规模数据存储和范围查询。
- 红黑树：
- 内存中的数据结构（如 C++ 的
std::map、Java 的TreeMap）。 - 小规模数据存储和等值查询。
- 实时系统和算法中需要快速插入和删除的场景。
- 内存中的数据结构（如 C++ 的
- B+ 树：
- 优缺点对比
特性 B+ 树 红黑树 结构 多叉树，分支因子大。 二叉树，每个节点最多两个子节点。 高度 较低，适合大规模数据。 较高，适合小规模数据。 查询性能 适合范围查询，查询路径短。 适合等值查询，查询路径较长。 插入和删除性能 高效，但可能需要分裂或合并节点。 高效，但可能需要多次旋转操作。 存储效率 适合磁盘存储，减少磁盘 I/O。 适合内存存储，磁盘存储效率低。 适用场景 数据库索引、文件系统、大规模数据存储。 内存数据结构、实时系统、小规模数据存储。 - 总结
- B+ 树 更适合大规模数据存储和范围查询，尤其是在磁盘存储场景中，因为它能够减少磁盘 I/O 操作并保持较低的树高度。
- 红黑树 更适合内存中的小规模数据存储和等值查询，因为它结构简单且插入删除操作高效。
选择使用 B+ 树还是红黑树，取决于具体的应用场景和需求。在数据库和文件系统中，B+ 树是更常见的选择；而在内存数据结构和实时系统中，红黑树则更为常用。
MySQL索引使用常见问题
在使用 MySQL 索引时，可能会遇到一些常见问题，这些问题可能会影响查询性能、数据一致性或数据库的维护。
- 索引未生效
- 问题描述：
- 查询性能没有提升，执行计划显示未使用索引。
- 可能原因：
- 查询条件不符合索引的最左前缀原则。
- 使用了函数或表达式（如
WHERE YEAR(column) = 2023）。 - 数据类型不匹配（如字符串列与数字比较）。
- 索引选择性太低（如布尔类型字段）。
- 解决方法：
- 确保查询条件符合索引的最左前缀原则。
- 避免在索引列上使用函数或表达式。
- 确保查询条件中的数据类型与索引列一致。
- 对于低选择性的列，考虑是否需要创建索引。
- 问题描述：
- 索引选择性差
- 问题描述：
- 索引列的值重复率很高（如性别、状态等字段），导致索引效果不佳。
- 可能原因：
- 索引列的唯一性太低，查询时仍然需要扫描大量数据。
- 解决方法：
- 对于低选择性的列，避免单独创建索引。
- 可以结合高选择性的列创建复合索引。
- 问题描述：
- 过多的索引
- 问题描述：
- 表中创建了过多的索引，导致写操作（插入、更新、删除）变慢。
- 可能原因：
- 每个索引都需要维护，写操作时会增加额外的开销。
- 解决方法：
- 仅对频繁查询的列创建索引。
- 定期审查和删除未使用的索引。
- 问题描述：
- 未使用复合索引
- 问题描述：
- 查询条件涉及多个列，但未使用复合索引。
- 可能原因：
- 未创建合适的复合索引。
- 查询条件不符合复合索引的最左前缀原则。
- 解决方法：
- 根据查询条件创建合适的复合索引。
- 确保查询条件符合复合索引的最左前缀原则。
- 问题描述：
- 索引失效
- 问题描述：
- 索引在某些情况下失效，导致查询性能下降。
- 可能原因：
- 使用了
OR条件（如WHERE a = 1 OR b = 2）。 - 使用了
NOT、!=或<>操作符。 - 查询条件中使用了
LIKE以通配符开头（如LIKE '%abc'）。
- 使用了
- 解决方法：
- 尽量避免使用导致索引失效的操作符。
- 对于
LIKE查询，尽量使用前缀匹配（如LIKE 'abc%'）。
- 问题描述：
- 索引碎片化
- 问题描述：
- 索引碎片化导致查询性能下降。
- 可能原因：
- 频繁的插入、更新和删除操作导致索引页不连续。
- 解决方法：
- 定期优化表（如
OPTIMIZE TABLE）。 - 对于 InnoDB 表，可以通过重建索引来减少碎片。
- 定期优化表（如
- 问题描述：
- 隐式类型转换
- 问题描述：
- 查询条件中的数据类型与索引列不一致，导致索引失效。
- 可能原因：
- 例如，索引列是字符串类型，但查询条件使用了数字。
- 解决方法：
- 确保查询条件中的数据类型与索引列一致。
- 问题描述：
- 覆盖索引未生效
- 问题描述：
- 查询需要回表（访问数据行），未能利用覆盖索引。
- 可能原因：
- 查询中包含了未包含在索引中的列。
- 解决方法：
- 创建覆盖索引，确保查询所需的列都包含在索引中。
- 问题描述：
- 索引统计信息不准确
- 问题描述：
- 查询优化器选择了不合适的索引，导致性能下降。
- 可能原因：
- 索引统计信息未及时更新。
- 解决方法：
- 使用
ANALYZE TABLE更新索引统计信息。
- 使用
- 问题描述：
- 索引列顺序不当
- 问题描述：
- 复合索引的列顺序不合理，导致索引未生效或效果不佳。
- 可能原因：
- 复合索引的列顺序不符合查询条件的最左前缀原则。
- 解决方法：
- 根据查询条件调整复合索引的列顺序。
- 问题描述：
- 索引列过长
- 问题描述：
- 索引列的长度过长，导致索引占用大量存储空间，且查询性能下降。
- 可能原因：
- 例如，对长文本字段（如
VARCHAR(255)）创建索引。
- 例如，对长文本字段（如
- 解决方法：
- 对长文本字段使用前缀索引（如
INDEX(column(10))）。 - 避免对过长的列创建索引。
- 对长文本字段使用前缀索引（如
- 问题描述：
- 未使用唯一索引
- 问题描述：
- 数据重复导致查询性能下降。
- 可能原因：
- 未对唯一性字段创建唯一索引。
- 解决方法：
- 对唯一性字段创建唯一索引，以提高查询性能和数据一致性。
- 问题描述：
- 索引与排序不匹配
- 问题描述：
- 查询中的排序操作未使用索引，导致性能下降。
- 可能原因：
- 排序字段未包含在索引中。
- 排序方向与索引不一致（如
ASC和DESC混合使用）。
- 解决方法：
- 确保排序字段包含在索引中。
- 确保排序方向与索引一致。
- 问题描述：
- 索引与分组不匹配
- 问题描述：
- 查询中的分组操作未使用索引，导致性能下降。
- 可能原因：
- 分组字段未包含在索引中。
- 解决方法：
- 确保分组字段包含在索引中。
- 问题描述：
- 索引与连接查询不匹配
- 问题描述：
- 连接查询未使用索引，导致性能下降。
- 可能原因：
- 连接字段未创建索引。
- 解决方法：
- 对连接字段创建索引。
- 问题描述：
总结
MySQL 索引使用中的常见问题主要包括索引未生效、选择性差、过多索引、索引失效、碎片化等。解决这些问题需要结合具体的查询场景和数据特点，合理设计索引，并定期优化和维护索引。通过正确使用索引，可以显著提升查询性能和数据操作效率。
MySQL知识点汇总
一、基础概念
- 数据库三大范式
- 第一范式（1NF）：字段不可再分，原子性。
- 第二范式（2NF）：消除部分依赖，确保每列完全依赖主键。
- 第三范式（3NF）：消除传递依赖，非主键列直接依赖主键。
- CHAR与VARCHAR的区别
CHAR定长，适合短且固定长度（如MD5），效率高但可能浪费空间；VARCHAR变长，适合长度不固定（如用户名），节省空间但需额外字节记录长度。
- InnoDB与MyISAM的区别
- 事务：InnoDB支持事务，MyISAM不支持。
- 锁粒度：InnoDB支持行锁，MyISAM仅表锁。
- 外键：InnoDB支持外键，MyISAM不支持。
- 崩溃恢复：InnoDB通过redo log实现崩溃安全，MyISAM依赖修复工具。
- 索引结构：InnoDB使用聚簇索引，MyISAM是非聚簇索引。
二、索引与性能优化
- 索引的优缺点
- 优点：加速查询、唯一性约束。
- 缺点：占用空间、降低写操作速度（需维护B+树）。
- B+树 vs B树 vs 哈希索引
- B+树：多路平衡树，适合范围查询，非叶子节点仅存键值，叶子节点链表连接。
- B树：节点存储数据，范围查询效率低。
- 哈希索引：O(1)查询，但无法范围查询，仅适合等值查询（如Memory引擎）。
- 聚簇索引与非聚簇索引
- 聚簇索引：数据与索引一起存储（如InnoDB主键索引）。
- 非聚簇索引：索引与数据分离（如MyISAM），需二次回表查询。
- 最左前缀原则
- 联合索引
(a,b,c)生效场景：a=1、a=1 AND b=2、a=1 AND b=2 AND c=3，但b=2或c=3单独使用时不生效。
- 索引失效的常见场景
- 对字段使用函数（如
WHERE UPPER(name) = 'A'）。 - 类型隐式转换（如字符串字段用数字查询）。
- OR条件中部分列无索引。
- LIKE以通配符开头（
'%abc'）。
三、事务与锁
- ACID特性
- 原子性（Undo Log）：事务要么全完成，要么全不完成。
- 隔离性（锁+MVCC）：并发事务互不干扰。
- 持久性（Redo Log）：提交后数据永久保存。
- 一致性：通过前三者保证数据逻辑一致。
- 隔离级别与问题
隔离级别 脏读 不可重复读 幻读 实现方式 读未提交 ✅ ✅ ✅ 无锁 读已提交（RC） ❌ ✅ ✅ 语句级快照（MVCC） 可重复读（RR） ❌ ❌ ✅ 事务级快照（MVCC+间隙锁） 串行化 ❌ ❌ ❌ 表级锁
- 隔离级别与问题
- MVCC机制
- 通过
DB_TRX_ID（事务ID）和DB_ROLL_PTR（回滚指针）实现多版本。 - ReadView判断数据可见性：活跃事务列表、最小事务ID、最大事务ID。
- 死锁的产生与解决
- 原因：多个事务互相等待对方释放锁。
- 解决：设置超时（
innodb_lock_wait_timeout）或主动检测死锁并回滚代价小的事务。
MySQL执行计划（EXPLAIN）分析最佳实践
一、基础操作与核心字段解读
- 获取执行计划
1
EXPLAIN [FORMAT=JSON|TREE] SELECT ... -- 推荐JSON格式获取详细信息
- 关键字段：
字段 说明 id查询层级（子查询顺序） select_type查询类型（SIMPLE/PRIMARY/SUBQUERY等） type访问类型（性能核心指标，从优到差： system>const>ref>range>index>ALL）key实际使用的索引 key_len索引使用的字节长度（判断是否完全使用联合索引） rows预估扫描行数（越小越好） Extra附加信息（ Using index/Using where/Using temporary等）
type字段解析
Extra字段
MySQL执行计划中的Extra字段提供了查询执行过程中的额外信息，用于说明优化器如何处理查询。以下是常见Extra字段值及其含义：
| 值 | 含义 | 优化建议 |
|---|---|---|
| Using where | 服务器层对存储引擎返回的数据进行二次过滤（WHERE条件未完全被索引覆盖）。 | 检查WHERE条件是否可被索引完全覆盖，或添加更合适的索引。 |
| Using index | 使用覆盖索引（查询字段全在索引中），无需回表。 | 尽量将SELECT字段加入索引，避免访问数据行。 |
| Using temporary | 需要创建临时表处理查询（常见于GROUP BY、DISTINCT、UNION等操作）。 | 为GROUP BY/DISTINCT字段添加索引，或减少中间结果集大小。 |
| Using filesort | 需对结果进行额外排序（无法利用索引排序）。 | 为ORDER BY字段添加索引，或调整索引顺序与排序方向一致。 |
| Using index condition | 启用索引条件下推（ICP），存储引擎层提前过滤数据，减少回表次数。 | 确认联合索引顺序是否合理，优先将高频过滤条件放在索引左侧。通常只在联合索引场景经常遇到，即便遇到这个extra信息有时候未必是真正的ICP |
| Using join buffer | 使用连接缓冲区（Block Nested Loop或Batched Key Access）优化多表连接。 | 检查被驱动表是否有合适索引，或调整驱动表顺序（小表作为驱动表）。 |
| Impossible WHERE | WHERE条件永远为假（如1=0），查询直接返回空集。 |
检查业务逻辑，避免无效条件。 |
| Select tables optimized away | 查询被优化为无需访问表（如使用聚合函数MIN()/MAX()且索引已覆盖）。 |
无需优化，表明查询已被充分优化。 |
| Using MRR | 启用多范围读取优化，将随机I/O转为顺序I/O（常用于范围查询）。 | 确保优化器统计信息准确，或调整mrr_cost_based参数。 |
| Using index for group-by | 利用索引优化GROUP BY操作（松散索引扫描）。 | 确保GROUP BY字段为索引最左前缀，且无范围查询干扰。 |
| Using intersect(a, b); Using where | 索引正交场景，往往是在多个单列索引场景，查询条件分别覆盖，会走索引合并后再查询 | 如果能优化索引的话，最好还是直接建立联合索引，减少低效的索引合并；如果不好优化，尝试改写SQL逻辑，或者使用固化索引的方式提升一部分查询性能 |
性能优化重点
- 优先消除
Using filesort和Using temporary：通过索引优化减少排序和临时表开销。 - 利用覆盖索引：减少回表操作，提升查询速度。
- 关注
Using index condition：联合索引设计需匹配查询条件顺序。
二、性能优化优先级判断
- 1. 优先关注
type字段- 目标：尽可能达到
const、ref或range，避免index（全索引扫描）和ALL（全表扫描）。 - 示例优化：
1
2
3
4-- 全表扫描（type=ALL）
EXPLAIN SELECT * FROM users WHERE phone = '13800138000';
-- 优化：为phone字段添加索引
ALTER TABLE users ADD INDEX idx_phone (phone);
- 目标：尽可能达到
- 2. 检查索引使用情况
- 未使用索引：
key为NULL且type=ALL→ 需添加索引。 - 索引未完全使用：联合索引的
key_len小于定义长度 → 调整查询条件顺序或索引。1
-- 索引(a, b, c)，查询WHERE a=1 AND c=3 → key_len仅计算a字段
- 未使用索引：
- 3. 覆盖索引优化（Using index）
- 目标：
Extra出现Using index，避免回表。1
2
3-- 示例：查询字段全部在索引中
CREATE INDEX idx_name_age ON users (name, age);
EXPLAIN SELECT name, age FROM users WHERE name = 'John'; -- Using index
- 目标：
三、关键问题诊断与解决
- 1. 全表扫描（type=ALL）
- 原因：无索引、索引失效（如使用函数）、查询条件跳过索引最左前缀。
- 解决：
- 添加匹配WHERE条件的索引。
- 重写查询条件避免函数或隐式转换。
- 2. 临时表与排序（Using temporary; Using filesort）
- 场景：GROUP BY或ORDER BY未使用索引排序。
- 优化：
- 为排序字段添加索引，确保与查询条件匹配。
- 减少排序数据量（如LIMIT分页）。
- 3. 嵌套循环与JOIN优化
- 驱动表选择：小表作为驱动表（
EXPLAIN中id=1的表）。 - 避免笛卡尔积：检查JOIN条件是否遗漏，导致
rows暴增。
- 驱动表选择：小表作为驱动表（
- 4. 子查询优化
- 优先使用JOIN替代子查询：
1
2
3
4-- 低效
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
-- 优化
SELECT users.* FROM users JOIN orders ON users.id = orders.user_id;
- 优先使用JOIN替代子查询：
四、高级分析技巧
- 1. JSON格式深度解析
1
2EXPLAIN FORMAT=JSON SELECT ...
-- 解析`cost_info`估算执行成本，对比不同查询计划的代价- 关注
query_cost（总成本）、prefix_cost（当前步骤成本）。
- 关注
- 2. 索引选择性检查
- 公式：
索引选择性 = 不同值数量 / 总行数（越接近1越好）。 - 示例：
1
2-- 计算city字段选择性
SELECT COUNT(DISTINCT city)/COUNT(*) FROM users;
- 公式：
- 3. 执行计划对比
- 使用
EXPLAIN对比不同查询写法或索引方案的性能差异。 - 工具辅助：
pt-visual-explain可视化执行计划。
- 使用
五、结合其他工具
- 慢查询日志
- 开启慢日志定位高耗时SQL：
1
2slow_query_log = ON
long_query_time = 2
- 开启慢日志定位高耗时SQL：
- 性能模式（Performance Schema）
- 监控锁等待、IO开销等底层行为：
1
SELECT * FROM performance_schema.events_waits_history;
- 监控锁等待、IO开销等底层行为：
- SHOW PROFILE
- 分析查询各阶段耗时（需先启用）：
1
2
3SET profiling = 1;
SELECT ...;
SHOW PROFILE FOR QUERY 1;
- 分析查询各阶段耗时（需先启用）：
总结
- 核心流程：
EXPLAIN→ 定位瓶颈（type/rows/Extra） → 针对性优化（索引/查询改写）。 - 优化原则：减少扫描数据量、避免临时表/排序、利用覆盖索引。
- 持续监控：结合慢查询日志和性能模式，定期审查执行计划。
MySQL核心原理（部分）
这部分主要涉及事务相关的底层，也是平时非DBA工程师之外容易碰的到的较为复杂的场景。
具体的MVCC工作原理部分，参考下文的《InnoDB事务》小节
MySQL相关的MVCC
MySQL的MVCC（Multi-Version Concurrency Control）是一种通过维护数据多版本实现高并发事务处理的机制，核心目标是减少读写操作的锁冲突，提升数据库并发性能。以下是其底层工作原理的详细总结：
1. MVCC 核心组件
- 1.1 隐藏字段
InnoDB 表中每行记录包含三个隐藏字段：- DB_TRX_ID（6字节）：记录最后修改该行的事务ID。
- DB_ROLL_PTR（7字节）：指向该行数据历史版本的指针（Undo Log 链）。
- DB_ROW_ID（6字节）：行唯一标识（当无主键时自动生成）。
- 1.2 Undo Log（回滚日志）
- 存储数据的历史版本，每个事务修改数据前会生成一个Undo Log，记录修改前的数据。
- 用于事务回滚、MVCC读取旧版本数据。
- 版本链结构：通过
DB_ROLL_PTR将多个Undo Log串联成链表，形成数据的版本链。
- 1.3 ReadView（读视图）
- 事务执行快照读（Snapshot Read）时生成的“数据可见性快照”，决定当前事务能看到哪些版本的数据。
- 包含以下关键信息：
m_ids：生成ReadView时活跃的事务ID列表。min_trx_id：活跃事务中的最小事务ID。max_trx_id：下一个即将分配的事务ID（当前最大事务ID + 1）。creator_trx_id：创建该ReadView的事务ID。
2. 数据可见性规则
事务通过 版本链 和 ReadView 判断数据的可见性：
- 版本遍历：从最新数据开始，沿
DB_ROLL_PTR遍历Undo Log链。 - 事务ID比对：对每个版本的
DB_TRX_ID进行判断：- 如果
DB_TRX_ID < min_trx_id：该版本在ReadView生成前已提交，可见。 - 如果
DB_TRX_ID >= max_trx_id：该版本由未来事务生成，不可见。 - 如果
min_trx_id ≤ DB_TRX_ID < max_trx_id：- 若
DB_TRX_ID在m_ids中：生成该版本的事务未提交，不可见。 - 否则：事务已提交，可见。
- 若
- 如果
- 终止条件：找到第一个可见的版本或遍历完版本链。
3. 不同隔离级别的实现
- 3.1 Read Committed（RC）
- 每次执行快照读时生成新的ReadView，确保能读取其他事务已提交的最新数据。
- 可能出现不可重复读（同一事务内多次读取结果不一致）。
- 3.2 Repeatable Read（RR）
- 第一次快照读时生成ReadView，后续所有读操作复用该ReadView，保证事务内一致性。
- 通过版本链和ReadView实现可重复读，避免幻读（通过Next-Key Locks进一步解决）。
- 3.3 Serializable
- 不使用MVCC，所有读操作加共享锁，退化为基于锁的并发控制。
4. 快照读 vs 当前读
- 快照读（Snapshot Read）：
- 普通
SELECT语句，基于MVCC读取历史版本。 - 无锁，性能高。
- 普通
- 当前读（Current Read）：
- 加锁的读操作（如
SELECT ... FOR UPDATE、UPDATE、DELETE）。 - 读取最新数据并加锁，保证数据一致性。
- 加锁的读操作（如
5. 数据清理（Purge机制）
- Purge线程：定期删除不再需要的Undo Log和历史版本数据。
- 清理条件：当没有事务需要访问某个历史版本时（即所有ReadView的最小
max_trx_id超过该版本的DB_TRX_ID）。
6. 关键流程示例
- 事务A（ID=100）修改数据：
- 生成Undo Log，记录旧数据。
- 更新行的
DB_TRX_ID=100，DB_ROLL_PTR指向Undo Log。
- 事务B（ID=200）读取数据：
- 生成ReadView，假设此时活跃事务为 [150, 180]。
- 遍历版本链，发现
DB_TRX_ID=100 < min_trx_id=150，数据可见。
7. 注意事项
- 长事务问题：长时间未提交的事务会阻止Undo Log清理，导致表膨胀。
- 隔离级别选择：根据业务需求权衡一致性与性能（如RC允许更高并发，RR保证可重复读）。
- 版本链长度：频繁更新会导致版本链过长，影响查询性能。
总结
MySQL通过MVCC机制，结合Undo Log、隐藏字段和ReadView，实现了高效的读写并发控制。
RC和RR隔离级别通过不同的ReadView生成策略平衡一致性与性能，而Purge机制确保历史数据及时清理。
理解MVCC有助于优化事务设计，避免长事务和性能瓶颈。
不同事务隔离级别应用场景
事务隔离级别用于平衡数据库并发性能和数据一致性，不同场景需选择不同级别。以下是各隔离级别的适用场景、示例及原因分析：
1. 读未提交（Read Uncommitted）
核心问题：允许脏读（读到其他事务未提交的数据）。
适用场景：对数据准确性要求低、高并发且允许短暂不一致的场景。
示例：
- 场景：实时统计大屏（如双11GMV实时展示）。
- 操作：统计系统读取订单表的未提交数据，快速更新大屏数据。
- 原因：允许脏读可提升性能，短暂的数据误差可接受。
2. 读已提交（Read Committed）
核心问题：避免脏读，但允许不可重复读（同一事务两次读取结果不同）。
适用场景：平衡性能与一致性。
示例：
- 场景：用户查询银行账户余额。
- 操作：用户A查询余额为1000元，事务未提交时用户B转账并提交，用户A再次查询余额变为900元。
- 原因：避免脏读导致用户看到错误余额，但允许并发更新（如转账）。
3. 可重复读（Repeatable Read）
核心问题：避免不可重复读，但可能幻读（同一查询条件返回新插入的行）。
适用场景：需要事务内多次读取一致的场景。
示例：
- 场景：银行日终对账。
- 操作：事务开始时读取账户A余额为1000元，期间其他事务无法修改该数据，保证对账逻辑一致性。
- 原因：MVCC机制（如MySQL）提供快照读，确保事务内数据稳定，避免对账错误。
4. 串行化（Serializable）
核心问题：完全禁止并发问题（脏读、不可重复读、幻读），但性能最低。
适用场景：对数据一致性要求极高且并发量低的场景。
示例：
- 场景：证券交易系统的资金交割。
- 操作：事务A扣减用户资金时，事务B必须等待事务A提交后才能操作。
- 原因：避免资金交割的并发冲突（如双重扣款），确保绝对一致。
对比总结
| 隔离级别 | 典型场景 | 性能 | 数据一致性要求 | 数据库默认支持 |
|---|---|---|---|---|
| 读未提交 | 实时统计、日志分析 | 最高 | 低（允许脏读） | 较少使用（如SQLite调试模式） |
| 读已提交 | 账户查询、订单状态更新 | 高 | 中等（避免脏读） | Oracle、PostgreSQL |
| 可重复读 | 财务对账、库存预占 | 中等 | 较高（事务内一致） | MySQL |
| 串行化 | 金融结算、票务锁座 | 最低 | 最高（完全隔离） | 需显式设置 |
选择建议
- 优先默认级别：大多数场景使用数据库默认隔离级别（如MySQL用
可重复读，PostgreSQL用读已提交）。 - 升级条件：仅在业务逻辑强制要求时提高隔离级别（如金融核心操作）。
- 降级条件：对一致性容忍度高且追求性能时（如大数据分析），可考虑读未提交。
通过合理选择隔离级别，可在业务需求和系统性能之间找到最佳平衡点。
MySQL默认隔离级别下如何有效避免幻读
MySQL在可重复读（RR）级别下通过 MVCC快照读 + 间隙锁（Gap Lock） 组合解决幻读，具体策略如下：
【勘误】上述通过DS结论是错误的，MySQL是通过Next-Key Lock（临键锁）这种机制来一定程度上解决幻读的
(续)补充快照读与当前读
1. 结论前置
MySQL的可重复读（RR）隔离级别并非完全免疫幻读，其防幻读能力取决于操作类型（快照读 vs 当前读）和具体场景。以下分情况详解：
2. 快照读（Snapshot Read）：无幻读
- 机制：事务首次查询生成ReadView，后续读取同一快照，忽略其他事务提交的新数据。
- 示例：
1
2
3
4
5
6
7
8
9
10-- 事务A
BEGIN;
SELECT * FROM users WHERE age > 20; -- 快照读，返回3条记录（id=1,2,3）
-- 事务B插入并提交
INSERT INTO users (id, age) VALUES (4, 25);
COMMIT;
-- 事务A再次快照读
SELECT * FROM users WHERE age > 20; -- 仍返回3条（id=1,2,3），无幻读 - 结论：纯快照读操作（普通SELECT）不会出现幻读。
3. 当前读（Current Read）：依赖间隙锁防幻读
- 机制：
SELECT ... FOR UPDATE、UPDATE、DELETE等操作触发间隙锁，锁定索引范围，阻止其他事务插入。 - 示例：
1
2
3
4
5
6-- 事务A
BEGIN;
SELECT * FROM users WHERE age > 20 FOR UPDATE; -- 当前读，加间隙锁（假设age索引存在）
-- 事务B尝试插入
INSERT INTO users (id, age) VALUES (4, 25); -- 阻塞，直到事务A提交 - 结论：正确使用索引并显式加锁时，当前读可避免幻读。
核心原理比对
| 维度 | 快照读（Snapshot Read） | 当前读（Current Read） |
|---|---|---|
| 定义 | 基于MVCC的多版本数据读取，不阻塞其他事务 | 基于锁机制的实时数据读取，可能阻塞其他事务 |
| 触发操作 | 普通SELECT（无锁） |
SELECT ... FOR UPDATE、UPDATE、DELETE、INSERT |
| 数据可见性 | 事务开始时或首次查询生成的ReadView决定 | 读取最新已提交数据，并加锁阻止其他事务修改 |
| 锁机制 | 无锁 | 加共享锁（S锁）或排他锁（X锁） |
| 隔离级别影响 | 在RR级别下使用首次ReadView，RC级别下每次生成新ReadView | 所有隔离级别均强制加锁 |
| 典型场景 | 报表查询、历史数据分析 | 库存扣减、订单支付等需要强一致性的操作 |
4. 边界场景：混合操作中的“伪幻读”
4.1 混合快照读与当前读
- 现象：同一事务内，快照读后执行当前读可能看到新数据。
1
2
3
4
5
6
7
8
9
10-- 事务A
BEGIN;
SELECT * FROM users WHERE age > 20; -- 快照读，返回3条
-- 事务B插入并提交
INSERT INTO users (id, age) VALUES (4, 25);
COMMIT;
-- 事务A执行当前读
SELECT * FROM users WHERE age > 20 FOR UPDATE; -- 当前读，返回4条（含id=4） - 本质：非幻读，而是当前读与快照读的差异。事务A的当前读会获取最新数据并加锁，但此操作属于设计预期。
- 现象：同一事务内，快照读后执行当前读可能看到新数据。
4.2 UPDATE操作引入新数据
- 现象：UPDATE语句可能修改其他事务插入的数据，导致事务内数据变化。
1
2
3
4
5
6
7
8
9
10
11-- 事务A
BEGIN;
SELECT * FROM users WHERE age > 20; -- 快照读，返回3条
-- 事务B插入并提交
INSERT INTO users (id, age) VALUES (4, 25);
COMMIT;
-- 事务A执行UPDATE（触发当前读）
UPDATE users SET name = 'test' WHERE age > 20; -- 更新4条（含id=4）
SELECT * FROM users WHERE age > 20; -- 快照读仍返回3条，但id=4被更新后可见 - 本质：UPDATE操作修改了其他事务插入的行，使其被事务A“私有化”（因事务A的修改），后续快照读可见该行。并非幻读，而是数据归属变化。
- 现象：UPDATE语句可能修改其他事务插入的数据，导致事务内数据变化。
5. 幻读的真正漏洞：无索引全表扫描
- 场景：WHERE条件字段无索引，导致全表间隙锁升级为表锁失败。
1
2
3
4
5
6-- 表无索引，事务A执行范围更新
BEGIN;
UPDATE users SET name = 'test' WHERE age > 20; -- 全表扫描，加间隙锁失败，退化为逐行锁
-- 事务B仍可插入数据
INSERT INTO users (id, age) VALUES (4, 25); -- 成功插入，事务A的UPDATE未锁定所有间隙 - 结论：无索引时，间隙锁无法精准覆盖范围，可能发生幻读。
- 场景：WHERE条件字段无索引，导致全表间隙锁升级为表锁失败。
6. 总结与最佳实践
- RR级别防幻读的条件：
- 使用索引，确保间隙锁生效。
- 显式加锁（如
FOR UPDATE）控制关键操作。
- “伪幻读”误区：
- 混合快照读与当前读的结果差异不属于幻读，而是操作类型不同所致。
- 规避风险：
- 为高频查询字段添加索引。
- 避免长事务，减少锁竞争。
- 关键业务逻辑显式加锁（如
SELECT ... FOR UPDATE）。
- RR级别防幻读的条件：
终极结论
MySQL的RR隔离级别在正确使用索引和锁的情况下可避免幻读，但在无索引或混合操作场景中仍存在边界风险。开发人员需理解底层机制，通过合理设计索引和事务逻辑，而非完全依赖隔离级别。
Redo Log、Undo Log、Binlog 的底层实现原理及应用场景总结
1. Redo Log（重做日志）
- 底层实现原理：
- 物理日志：记录数据页的物理修改（如页号、偏移量、修改后的值）。
- 写入流程：
- 事务修改数据时，先写入Redo Log Buffer（内存缓冲区）。
- 按一定策略（如事务提交时）将日志刷入磁盘的
ib_logfile0、ib_logfile1等文件。
- 顺序写入：Redo Log 文件是循环写入的，通过 LSN（Log Sequence Number） 标记日志顺序。
- 崩溃恢复：数据库启动时，通过比较数据页的LSN和Redo Log的LSN，将未落盘的操作重放。
- 应用场景：
- 崩溃恢复：确保事务的持久性（Durability），即使宕机也能恢复已提交的事务。
- Write-Ahead Logging (WAL) ：先写日志再写数据页，减少随机IO，提升写入性能。
- 关键配置：
innodb_flush_log_at_trx_commit：控制Redo Log刷盘策略（0-延迟写，1-实时刷盘，2-写入OS缓存）。
2. Undo Log（回滚日志）
- 底层实现原理：
- 逻辑日志：记录事务修改前的数据快照（如旧值、旧版本的行记录）。
- 存储结构：
- 存储在回滚段（Rollback Segment）中，每个回滚段包含多个Undo Slot。
- 通过
DB_ROLL_PTR指针形成多版本链，支持MVCC。
- 事务隔离：
- 事务回滚时，通过Undo Log恢复数据到旧版本。
- 为读操作提供历史版本（如RC/RR隔离级别的快照读）。
- 应用场景：
- 事务回滚：保证事务的原子性（Atomicity）。
- MVCC支持：实现非锁定读（快照读），提升并发性能。
- 长事务问题：未提交事务的Undo Log无法清理，可能导致表空间膨胀。
- 清理机制：
- Purge线程：定期清理不再需要的Undo Log（当无活跃事务依赖旧版本时）。
3. Binlog（二进制日志）
- 底层实现原理：
- 逻辑日志：记录所有数据库的DDL和DML操作（如SQL语句或行变更）。
- 写入流程：
- 事务提交时，按
sync_binlog配置将日志写入磁盘的mysql-bin.00000x文件。
- 事务提交时，按
- 日志格式：
- Statement：记录原始SQL（可能引发主从不一致，如使用随机函数）。
- Row：记录行的变更（安全但日志量大）。
- Mixed：混合模式，自动选择Statement或Row。
- 应用场景：
- 主从复制：从库通过重放Binlog实现数据同步。
- 数据恢复：通过
mysqlbinlog工具恢复误删数据。 - 审计：分析数据库操作历史。
- 关键机制：
- 两阶段提交：
- 事务提交时，先写Redo Log（Prepare状态），再写Binlog，最后提交Redo Log（Commit状态）。
- 确保Redo Log和Binlog的一致性。
- 两阶段提交：
三者的区别与协作
| 特性 | Redo Log | Undo Log | Binlog |
|---|---|---|---|
| 层级 | InnoDB引擎层 | InnoDB引擎层 | MySQL Server层 |
| 日志类型 | 物理日志（页修改） | 逻辑日志（数据快照） | 逻辑日志（操作记录） |
| 写入时机 | 事务执行中持续写入 | 事务修改数据前生成 | 事务提交时写入 |
| 主要作用 | 崩溃恢复、持久性 | 事务回滚、MVCC | 主从复制、数据恢复 |
| 生命周期 | 数据落盘后可覆盖 | 事务提交后可能保留 | 长期保留（可配置过期时间） |
协作流程（以两阶段提交为例）：
- 事务修改数据，生成Undo Log和Redo Log。
- 事务提交时，Redo Log标记为Prepare状态。
- Binlog写入磁盘。
- Redo Log标记为Commit状态。
实际应用注意事项
- Redo Log：
- 设置合理的
innodb_log_file_size避免频繁切换日志文件。
- 设置合理的
- Undo Log：
- 监控长事务，避免Undo表空间膨胀（如
information_schema.INNODB_TRX）。
- 监控长事务，避免Undo表空间膨胀（如
- Binlog：
- 主从复制建议使用Row格式，保证数据一致性。
- 调整
sync_binlog和binlog_group_commit平衡性能与安全。
总结
- Redo Log：保障数据持久性和崩溃恢复，物理日志，核心是WAL机制。
- Undo Log：支持事务回滚和MVCC，逻辑日志，构建多版本数据链。
- Binlog：实现主从复制和数据恢复，逻辑日志，依赖两阶段提交保证一致性。
三者协同工作，确保MySQL的事务ACID特性、高并发能力和数据可靠性。理解其原理及配置优化，对设计高可用、高性能数据库系统至关重要。
若主库故障，如何通过“本地消息表”或“binlog同步”恢复数据？
在主库故障场景下，通过 本地消息表 或 binlog同步 恢复数据是保障系统高可用的核心手段。以下是两种方案的详细恢复流程及关键注意事项：
一、通过本地消息表恢复数据
- 1. 核心原理
在业务数据库中维护一张消息表，记录所有未同步的事务操作。通过异步任务确保数据最终一致性。 - 2. 故障恢复流程
- 步骤1：切换从库为新主库
1
2
3
4# MySQL主从切换命令示例
STOP SLAVE; # 停止从库复制
RESET SLAVE ALL; # 清除从库复制信息
CHANGE MASTER TO MASTER_HOST=''; # 解除主从关系 - 步骤2：恢复未同步消息
- 查询本地消息表：找出主库故障前未发送到消息队列的消息。
1
2
3SELECT * FROM message_table
WHERE status = 'UNSENT'
AND created_time < '主库故障时间'; - 重新投递消息：将未发送消息重新发布到消息队列。
- 消费者幂等处理：确保消息重复执行不会导致数据异常。
1
2
3
4
5
6
7public void handleMessage(Message msg) {
if (redis.setnx(msg.getId(), "PROCESSED") == 1) {
// 执行业务逻辑（如插入订单）
orderService.createOrder(msg.getData());
messageTable.updateStatus(msg.getId(), "SENT");
}
}
- 查询本地消息表：找出主库故障前未发送到消息队列的消息。
- 步骤3：数据校验
- 对比新主库与消息队列消费后的数据一致性。
- 修复差异记录（如手动补单）。
- 步骤1：切换从库为新主库
- 3. 适用场景
- 业务层需强保障数据最终一致性（如订单创建）。
- 主库故障后允许短暂数据延迟同步。
二、通过binlog同步恢复数据
- 1. 核心原理
利用MySQL的binlog日志实现主从数据同步，主库故障后从库通过解析binlog恢复数据。 - 2. 故障恢复流程
场景A：主库可恢复（如硬盘未损坏）
定位binlog位置：
- 从库查看已同步的binlog文件名及位置：
1
2SHOW SLAVE STATUS\G
-- 关键字段：Master_Log_File、Read_Master_Log_Pos - 主库解析binlog，找到未同步的日志范围：
1
2
3mysqlbinlog --start-position=Read_Master_Log_Pos \
--database=your_db_name \
/var/lib/mysql/master-bin.00000X > recovery.sql
- 从库查看已同步的binlog文件名及位置：
数据恢复：
- 将解析出的SQL应用到从库（新主库）：
1
mysql -u root -p < recovery.sql
- 将解析出的SQL应用到从库（新主库）：
场景B：主库不可恢复（如物理损坏）
- 从库提升为主库：
1
2STOP SLAVE;
RESET SLAVE ALL; - 基于从库binlog重建数据：
- 若从库已开启log_slave_updates，其binlog包含完整数据变更。
- 解析从库binlog，恢复到其他节点：
1
2
3mysqlbinlog --start-datetime="2024-03-20 00:00:00" \
--stop-datetime="2024-03-20 10:00:00" \
/var/lib/mysql/slave-bin.00000X | mysql -u root -p
- 从库提升为主库：
- 3. 关键配置
确保binlog完整性：1
2
3
4
5
6
7# my.cnf配置
[mysqld]
server-id = 1
log_bin = /var/lib/mysql/mysql-bin.log
expire_logs_days = 7
binlog_format = ROW # 推荐ROW格式，记录行级变更
gtid_mode = ON # 启用GTID简化故障切换 - 4. 适用场景
- 数据库层原生数据同步需求。
- 主库故障后需快速重建完整数据副本。
三、两种方案的对比与选择建议
| 维度 | 本地消息表 | binlog同步 |
|---|---|---|
| 一致性保障 | 最终一致性（业务层兜底） | 强一致性（数据库层同步） |
| 恢复速度 | 依赖消息队列消费速度 | 直接解析日志，速度较快 |
| 侵入性 | 需业务代码集成消息表 | 对业务透明，仅依赖数据库配置 |
| 适用场景 | 分布式事务、跨服务数据同步 | 数据库主从复制、数据备份恢复 |
| 复杂度 | 高（需处理消息投递、幂等） | 中（依赖DBA技能与工具链） |
四、注意事项
- binlog格式选择：
- STATEMENT：记录SQL语句，可能因函数导致主从不一致。
- ROW（推荐）：记录行级变更，数据一致性更高。
- GTID模式：
- 启用GTID（Global Transaction Identifier）可简化主从切换流程。
- 故障恢复时直接按GTID范围同步，无需手动定位binlog位置。
- 数据校验工具：
- 使用Percona Toolkit的
pt-table-checksum校验主从数据一致性。 - 定期执行校验任务，提前发现潜在差异。
- 使用Percona Toolkit的
- 容灾演练：
- 定期模拟主库故障，测试切换流程与数据恢复时间（RTO）。
五、总结
- 本地消息表：适合业务层需要自定义补偿逻辑的场景，需与消息队列配合，实现最终一致性。
- binlog同步：适合数据库层原生高可用需求，依赖MySQL复制机制，恢复效率高。
最佳实践：
- 核心交易系统可同时使用两种方案：
- binlog同步保障数据库层数据强一致。
- 本地消息表兜底跨服务事务的最终一致。
- 定期备份binlog并测试恢复流程，确保故障时可快速响应。
MySQL索引下推（Index Condition Pushdown, ICP）核心解析
1. 概念定义
索引下推（ICP）是MySQL 5.6+引入的查询优化技术，允许存储引擎在扫描索引时直接过滤WHERE条件中的索引列，减少回表次数，提升查询效率。
2. 工作原理
- 传统流程（无ICP）：
- 存储引擎通过索引检索满足最左前缀条件的数据。
- 将满足索引条件的记录全部回表到主键索引获取完整数据。
- 服务层对所有回表后的数据进一步过滤非索引条件。
- ICP流程：
- 存储引擎通过索引检索时，直接应用WHERE中所有可用的索引列条件（即使非最左前缀）。
- 仅回表满足所有索引条件的数据，减少无效回表。
- 服务层仅处理剩余的非索引条件。
3. 适用场景
- 复合索引部分条件过滤：
索引(a, b)，查询WHERE a > 10 AND b = 5→ ICP允许在索引扫描阶段过滤b=5。 - 范围查询后的等值过滤：
索引(age, city)，查询WHERE age BETWEEN 20 AND 30 AND city='北京'→ 存储引擎直接过滤city='北京'。 - 减少回表数据量：
索引列条件过滤后，仅需回表少量数据，降低I/O开销。
4. 生效条件
- 使用二级索引：仅对非主键索引生效。
- WHERE条件含索引列：至少有一个条件能通过索引过滤。
- 非覆盖索引查询：需要回表获取完整数据（若覆盖索引无需回表，ICP不生效）。
5. 示例对比
表结构：user(id PK, name, age INDEX, city)
查询语句：
1
2SELECT * FROM user
WHERE age > 18 AND city = '上海';
无ICP时：
- 索引
age找到所有age > 18的记录。 - 回表获取全部记录。
- 服务层过滤
city='上海'。
- 索引
启用ICP后：
- 索引
age扫描时，若city在WHERE条件中且可下推，直接过滤city='上海'。 - 仅回表满足
age > 18 AND city='上海'的记录。
- 索引
6. 性能影响
- 减少回表数据量：ICP可过滤70%-90%无效数据，极大降低磁盘I/O。
- 降低CPU消耗：服务层处理的数据行数减少。
- 典型优化场景：复合索引的非首列条件、范围查询后的精确匹配。
7. 查看与配置
- 开启状态检查：
1
2SHOW VARIABLES LIKE 'optimizer_switch';
-- 确认index_condition_pushdown=on - 强制关闭ICP（调试用）：
1
SET optimizer_switch = 'index_condition_pushdown=off';
8. 限制与注意事项
- 不适用覆盖索引：若查询字段全在索引中，无需回表，ICP不生效。
- 条件复杂度限制：无法下推含函数或复杂表达式的条件（如
UPPER(name) = 'A'）。 - 联合索引顺序敏感：需至少使用最左前缀触发索引扫描。
总结
索引下推通过将WHERE条件下推至存储引擎层，在扫描索引时提前过滤数据，尤其对复合索引的非首列条件优化效果显著。合理设计索引结合ICP，可大幅减少回表操作，是高性能SQL查询的关键优化手段之一。
【原理】InnoDB特性
插入缓冲
这里面分为两个部分
Insert Buffe
Insert Buffer和数据页一样，也是物理页的一个组成部分。
InnoDB开创性设计了Insert Buffer，对于非聚集索引的插入或更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在，则直接插入；若不在，则先放入一个Insert Buffer对象中。
数据库这个非聚集的索引已经插到叶子节点，而实际并没有，只是存放在另一个位置。然后再以一定的频率和情况进行Insert Buffer和辅助索引叶子节点的merge操作，这时通常能将多个插入合并到一个操作中（因为在一个索引页中），这就大大提高了对于非聚集索引插入的性能。
Insert Buffer使用需要有两个条件：
- 索引是二级缩影
- 索引不是唯一的
可以通过show engin innodb status\G来查看当前InnoDB引擎状态，1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 2 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
0.00 hash searches/s,00 non-hash searches/s
Change Buffer
从InnoDB0.x版本开始引入了Change Buffer，可以看作为是Insert Buffer的升级。对DML操作（I\D\U）都进行缓冲，分别细分为：Insert Buffer、Delete Buffer、Purge Buffer。
两次写（Double Write）
Insert Buffer带给InnoDB是性能上的提升，double write则是带给InnoDB的是数据页的可靠性。
在应用(apply)重做日志前，用户需要一个页的副本，当写入失效发生时，先通过页的副本来进行还原，再进行重做，这个就是Double Write。
自适应哈希索引（Adaptive Hash Index）
InnoDB会监控表上各个索引页的查询，如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，这个就称之为自适应哈希索引。
具体的后文中有介绍。
异步IO（Async IO）
为了提高磁盘操作性能，当前的数据库系统都是采用异步IO的方式来处理磁盘操作，InnoDB也是如此。
刷新邻接页（Flush Neighbor Page）
其工作原理为：当刷新一个脏页时，InnoDB会检测该页所在区(extent)的所有页，如果时脏页，那么一起刷新。这样通过AIO可以将多个IO写入操作合并成一个IO操作，这个工作机制在传统机械磁盘有着显著优势，固态硬盘这个优势不明显。
从InnoDB2.x版本开始，可以通过参数innodb_flush_neighbors来控制这个特性的开关。
【原理】InnoDB架构
补充一张5.7版本InnoDB的整体架构图
模块解释：
- 从整体上看（从os page cache分）：
- 上半部分是实例层，存放在内存里
- 下半部分是物理层，存放在文件系统中
- 从各个功能上看：
- master thread：负责调度其它各个线程，包含主循环（Loop）(1s和10s)、后台循环（Background Loop）、刷新循环（Flush Loop）、暂停循环（Suspend Loop）
- 1秒操作：
- 日志缓冲刷新到磁盘（会总是操作，即使事务还没有提交）
- 最多可能刷100个新脏页到磁盘
- 执行合并，改变插入缓冲的操作
- 若当前没有用户活动，可能切换到后台循环等
- 10秒操作：
- 刷新可能最多100个脏页到磁盘
- 合并至多5个Change Buffer（总是）
- 日志缓冲刷新到磁盘（总是）
- 删除无用的undo页（总是）
- 刷新100个或者10个脏页到磁盘（总是）产生一个检查点（总是）等
- 1秒操作：
- buf dump thread：负责把buffer pool中的内容dump到物理文件中，以便快速热加载。
- page cleaner thread：负责把buffer pool中的脏页刷新到磁盘（5.7版本后才有，之前都是交给主线程完成的）
- purge thread：负责将不再使用的undo日志进行回收
- read thread：处理用户的读请求，并负责将数据页从磁盘上读出来
- write thread：负责数据页从缓冲区写出到磁盘
- redo log thread：负责把日志缓冲中的内容刷新到redo log文件中
- Insert Buffer Thread：负责把insert buffer中的内容刷新到磁盘
- Buffer Pool内容：数据和索引页、undo页、insert buffer页、自适应哈希页、数据字典和锁信息
- Addtional Memory Pool：已经废弃
- redo log buffer：存储数据修改所产生的redo log
- double write buffer：双写所需要的
- master thread：负责调度其它各个线程，包含主循环（Loop）(1s和10s)、后台循环（Background Loop）、刷新循环（Flush Loop）、暂停循环（Suspend Loop）
InnoDB行记录存储结构
COMPACT行格式
有两大块组成：
- 记录的额外信息
- 边长字段长度列表
- NULL值列表
- 记录头信息
- 记录的真实数据
DYNAMIC行格式
跟COMPACT基本类似，只是在溢出页处存储的数据的不一样
InnoDB数据页结构
官方释义这种存放记录的页为索引（INDEX）页
结构一览
其中每个结构的功能如下图所示：
记录在页中的存储过程
在页的7 个组成部分中，我们自己存储的记录会按照指定的行格式存储到UserRecords 部分。在一开始生成页的时候，其实并没有UserRecords部分，每当插入一条记录时，都会从FreeSpace部分申请一个记录大小的控件，并将这个空间划分到UserRecords部分。当用完之后，就是需要新申请一个页继续存储。
记录头中的信息
先来一个存储示意图：
对照每个记录头属性含义：
结合UserRecords的一个存储示例示意图来仔细看看每个标志位的含义（省略了一些列）：
其中
- deleted_flag：只是用于标记当前记录是否是删除状态，真是数据行记录还是存储在这里。之所以不从磁盘上移除，是因为如果移除之后，还需要在磁盘上重新排列其它数据，频繁的删除的话，会极大的带来性能消耗。所有的删除记录会单独组成一个垃圾链表（这个过程牵扯到undo日志）
- min_rec_flag：B+树每层非叶子节点中的最小的目录项记录都会添加该标记。
- n_owned：用于标识当前页目录分组中所包含的行记录数量（包含两个头尾虚拟记录本身）。PS：这个过程具体看下一个小节，页目录创建过程。
- heap_no：看上面的Demo示意图，4条记录的heap_no分别是2、3、4、5，少了0和1。InnoDB在申请页空间初始化的时候，会创建两条伪记录（有的称之为虚拟记录）。一条代表页面中的最小记录（Infimum记录），另一条代表页面中的最大记录（Supremum记录）。
- 这两个记录的构造十分简单，都是由5字节大小的记录头信息和8字节大小的一个固定单词组成的（2组一共占用固定26字节）：
![虚拟记录结构示意图]()
由于Infimum和Supremum这两条记录是InnoDB默认创建的记录，为了与用户记录区分，会单独存储，如下图所示：![虚拟记录存放示意图]()
- 如果在删除数据的时候，这个heap_no短时间内是不会变的。结合前面的deleted_flag标志位，当有新增数据来的时候，会复用当前这行记录的空间的。
- 这两个记录的构造十分简单，都是由5字节大小的记录头信息和8字节大小的一个固定单词组成的（2组一共占用固定26字节）：
- next_record：非常重要的一个属性，表示从当前记录的真实数据到下一条巨鹿的真实数据的距离。
- 如果为正数：说明当前记录的下一条记录在当前记录的后面；
- 如果为负数：说明当前记录的下一条记录在当前记录的前面。
![使用箭头替代next_record指针]()
这个设计，刚刚好向左就是记录头信息，向右读取就是真实数据。在某些场景下，可以使记录考前的字段和他们对应的字段长度信息在内存中靠的更近，这可能会提高缓存的命中率。
Page Directory(页目录-不确定)
本质上，先要知道设计目录页的目的：是为了解决在一个数据页中的记录（单向链表组成的）如何快速定位数据位置并访问。你就类比你去图书馆借书的流程，是不是先要去检索书录，完了找到对于书架，再去找对应编号所在范围，然后以此寻找直至找到对应的书，页目录的工作原理就是这样的。
先看一下页目录创建的大致流程：
- 将所有正常记录（包括Infimum和Supremum两个，但不包括已经移除到垃圾链表中的记录）划分几个组
- 每个组的最后一条记录的头信息中的n_owned属性表示该当前组内共有几条记录
- 将每个组中最后一条记录在页面中的地址偏移量（这个记录的真实数据与页面中第0个字节之间的距离）单独提取出来，按顺序存储到靠近页尾部的地方。这个地方就是Page Directory(页目录)。页目录中的这些地址偏移量称之为槽（slot），每个槽占用2字节。页目录就是由多个槽组成。
看个例子：
比如当前表有6条记录，InnoDB分成2组，第一组只有一条Infimum记录，第二组是剩余的5跳记录。2个组，就对应有俩个槽，每个槽中存放每个组中最大的那条记录在页面中的地址偏移量，如下图所示：
其中需要注意的是：
- 图中页目录俩槽里面的数字，就标识当前组的记录所在页中的地址偏移量。112就代表Supremum记录偏移112字节
- 两个n_owned值就分别代表当前分组的记录数量
此时我们简化一下，用箭头来示意一下，如下图所示：
分组依据
InnoDB对每个分组的记录条数是有规定的：对于Infimum记录所在的分组只能有1条记录，Supremum记录所在的分组拥有的记录条数只能在18条之间，剩下的分组记录条数范围只能是在48条之间。
PS：你可能问为什么要这样做？有说法是说跟概率学中的泊松分布有关。，具体不懂。。。
分组的步骤大致如下：
- 初始情况下，一个数据页中只有Infimum记录和Supremum记录2条，分别属于两个组，对于页目录中也只有2槽，分别代表Infimum记录和Spuremum记录在页面中的地址偏移量。
- 之后每插入一条记录，都会从页目录中找到对应记录的主键值比待插入记录的主键值大并且差值最小的槽(从本质上来说，槽是一个组内最大的那条记录在页面中的地址偏移量，通过槽可以快速找到对应的记录的主键值) .然后把该糟对应的记录的n_owned值加1，表示本组内又添加了一条记录， 直到该组中的记录数等于8个。
- 当一个组中的记录数等于8后，再插入一条记录时，会将组中的记录拆分成两个组，其中一个组中为4条记录，另一个是5条记录。这个拆分过程会在页目录中新增一个槽， 记录这个新增分组中最大的那条记录的偏移量。
在一个数据页中查找指定主键值的记录，过程分为两步：
- 通过二分法确定该记录所在分组对应的槽，然后找到该槽所在分组中主键值最小的那条记录。
- 通过记录的next record 属性遍历该槽所在的组中的各个记录。
Page Header(页头部-56字节)
存储在数据页中的记录的状态信息，如数据页中存放了多少条记录、Free Space在页面中的地址偏移量、页目录中存储了多少个槽等。
File Header(文件头-38字节)
存储各个类型的页信息（共有信息），其中就包含索引页（FIL_PAGE_INDEX），固定38字节
- FIL_PAGE_SPACE_OR_CHKSUM：页面校验和（跟文件尾中的保持对应）
- FIL_PAGE_OFFSET：就理解是每个页拥有的单独的页号
- FIL_PAGE_TYPE：当前页的类型。
- FIL_PAGE_PREV和FIL_PAGE_NEXT：数据页的前驱和后继指针，不同数据页是通过双向链表关联的。
页类型的分类：
| 类型名称 | 十六进制 | 描述 |
|————-|:———————–:|:———————–:|
| FIL_PAGE_TYPE_ALLOCATED | 0x0000 | 最新分配，还未使用 |
| FIL_PAGE_UNDO_LOG | 0x0002 | undo日志页 |
| FIL_PAGE_INODE | 0x0003 | 存储段信息 |
| FIL_PAGE_IBUF_FREE_LIST | 0x0004 | Change Buffer空闲列表 |
| FIL_PAGE_IBUF_BITMAP | 0x0005 | Change Buffer的一些属性 |
| FIL_PAGE_TYPE_SYS | 0x0006 | 存储一些系统数据 |
| FIL_PAGE_TYPE_TRX_SYS | 0x0007 | 事务系统数据 |
| FIL_PAGE_TYPE_FSP_HDR | 0x0008 | 表空间头部信息 |
| FIL_PAGE_TYPE_XDES | 0x0009 | 存储区的一些属性 |
| FIL_PAGE_TYPE_BLOB | 0x000A | 溢出页 |
| FIL_PAGE_INDEX | 0x45BF | 索引页，也就是我们所说的数据页 |
File Trailer(文件尾-8字节)
这部分8字节组成，主要处理页一致性，前4字节代表页的校验和，后4字节代表页面被修改时对应的LSN的后4字节，正常情况下与File Header中的FIL_PAGE_LSN的后4字节相同。这部分也用来校验页的完整性
InnoDB的Buffer Pool
啥是Buffer Pool
在开始学习InnoDB存储结构的时候，我们就知道，MySQL的存储的数据按照数据页的方式存储到磁盘上的。但是，我们访问的时候，不可能时刻访问磁盘（效率巨低）。此时我们要改善访问过程怎么办呢？MySQL的设计者们，就通过引入缓存的概念，来优化从磁盘加载数据的过程。在MySQL中这部分缓存就称之为buffer Pool(缓冲池)。
在MySQL中默认的大小是128MB，可以通过查看系统变量查看：show variables like '%buffer_pool_size%';
Buffer Pool的组成
首先要知道，缓冲池是MySQL向操作系统内存申请的一块连续的内存存储空间（为啥要连续，翻看介绍InnoDB的存储结构的相关章节）。缓冲池中为每一个缓冲页（就是数据页，为了和正常的数据页区分，这里称之为缓冲页）都设置了一个控制块，每个控制块中包含了，如表空间编号、页号、缓冲页所在的缓冲池的地址、链表节点信息等等。对于的内存空间，看起来像下面这样：
中间的碎片就会，当加载好的缓冲页并为其分配好控制块信息之后，剩下来的空间，就是碎片信息。当然，也可能刚好就够了，没有碎片。
Free链表管理
先来想一个问题，现在要将磁盘中的数据页读到缓冲池中的缓冲页中去，我们怎么知道哪个缓冲页的是空闲的？这里使用到的是一个Free链表。我们假设当前这个Buffer Pool中可容纳的缓冲页的数量是n，那么增加了free链表（一个双向链表）的效果图，如下所示：
注意看这个双向链表，有个头节点，这里面存放了这个链表的收尾地址和节点数量等信息。注意这块节点存储，并不在缓冲池申请的内存区域中。
有了这个节点，读取存放的时候，就容易了，从头往后读，拿到空闲的缓冲页后，填上对应的控制块信息并从free链表中移除该节点，就表明这个缓冲页已经被使用了。
缓冲页的哈希表
大体上总结：通过建立表空间号+页号的Key，把缓冲页的控制块信息作为Value，建立一个哈希表，就可以快速访问了。
实际上就是：哈希索引（下文有详细说明）
flush链表的管理
如果我修改了缓冲池中的某个缓冲页的数据，如果此时数据还没回写到磁盘上，那么此时数据就与磁盘上的数据不一致了，这样的缓冲页也称之为脏页（dirty page）。当然，你说我更新完，立马回写到磁盘上行不行？行，但是这不又回到我们为啥要加缓冲区的问题上了么？所以，在围绕磁盘读写IO上，MySQL设计者们真的是下足了功夫。
这时候，如果我们不立马更新到磁盘上，那么后面如果要更新到磁盘上的时候，我们又怎么知道哪个页是脏页呢？
不得不新开一组链表来维护这些信息，而维护脏页的链表就称之为flush链表。结构就略了，跟上面的free链表一样的玩法。
等等，似乎还有一个问题，什么时候刷新？这个后面会介绍
LRU链表的管理
没错，正如这个小节标题所示，这个算法就是大学数据结构、操作系统等课程中提到的，最常用的一种页面置换算法，这不MySQL这里也用到了。
在介绍InnoDB是怎么用LRU算法之前，先考虑个最基本的问题，缓冲池大小是固定的，势必有用完的时候。用完之后，我们势必要考虑淘汰哪个页的问题，我们这里就用到一个LRU链表，来按最近最少使用的原则去淘汰缓冲页。
简单的LRU链表
正如上述所述，我们可以用一个简单的LRU链表来管理这些页，当需要访问某个页时，可以按照下面的方式处理LRU链表：
- 如果该页不在Buffer Pool中， 在把该页从磁盘加载到Buffer Pool中的缓冲页时，就把该缓冲页对应的控制块作为节点塞到LRU链表的头部；
- 如果该页已经被加载到Buffer Pool中， 则直接把该页对应的控制块移动到LRU链表的头部。
划分区域的LRU链表
这里又会有两个场景的问题： - MySQL预读功能（read ahead）会影响缓冲页的命中率，也就是预读进去的内容，不是我们访问所需的，那么势必又要从磁盘去加载。
- 一不小心全表扫描了，有非常多的使用频率偏低的页被同时加载到Buffer Pool中，会把那些使用频率非常高的页从BufferPool中淘汰。
针对这两种场景，InnoDB的设计者，则将LRU链表分成了下面两块区域： - 一部分存储使用频率非常高的缓冲页，这一部分链表也称为热数据，或者称之为young区域
- 另一部分存储使用频率不高的缓冲页，这部分链表也称为冷数据，或者称之为old区域。
![LRU链表分区域管理示意图]()
InnoDB按照一定比例来划分这两个区域，默认情况下old区域占整个LRU链表的37%。可以通过innodb_old_blocks_pct来修改预读不被访问的优化
通过利用上面提到的两个链表的分区，把预读的放到old区域头部，这样如果不被访问，逐步淘汰也不影响热数据。全表扫描时的优化
在对某个处于old区域的缓冲页进行第一次访问的时候，就在它对应的控制块中记录这个访问时间。如果后续的访问时间与第一次访问的时间在某个时间间隔内，这个页面就不会从old区域移动到yongg区域的头部。这个时间间隔通过innodb_old_blocks_time控制。一些其它的优化
对于热数据区，InnoDB通过只有被访问的缓冲页位于young区域 1/4的后面的时候，才会被移动到LRU链表的头部。这样就可以降低调整LRU链表的频率。其它的一些链表
【了解】针对其它类型的数据页的时候延申出来的用于管理Buffer Pool的链表
刷新脏页到磁盘
了解一个原理两种形式：MySQL后台有专门负责的线程（可以翻到文章开始的那个InnoDB架构图，看看这个线程在哪里）每隔一段时间就把脏页刷新到磁盘，
刷新的方式主要有两种：
- 从LRU链表的冷数据中刷新一部分到磁盘
- 从flush链表中刷新一部分页面到磁盘
关于多个缓冲池
MySQL可以通过配置系统参数，来指定BufferPool的实例数，因为在多线程的访问环境下，如果只有单一的缓冲池，每次操作的时候，都要加锁处理，所以势必影响效率。
通过系统参数 innodb_buffer_pool_instances 来设置
【原理】InnoDB索引相关
整体上MySQL的索引总结：
- 从数据结构上来分：B+树索引、哈希索引、以及全文(FullText)索引和R-Tree索引（用于对GIS数据类型创建SPATIAL索引）
- 从物理存储角度：聚簇索引、非聚簇索引
- 从逻辑角度：主键索引、普通索引、或者单列索引、多列索引、唯一索引，非唯一索引
B+树索引
数据页组成的几个部分：
- 各个数据页组成一个双向链表
- 每个数据页中的记录按照主键值的从小到大的顺序组成一个单向链表
没有索引时的查找
在一个页中查找
这个好理解，要么按照有主键的方式查找，要么从Infimum从头遍历
在很多页中查找
分为两个步骤：
- 定位到记录所在的页
- 从所在的页内查找相应的记录
索引的目的时什么呢？就是优化第一个步骤的，即怎么快速定位到记录所在的页，会极大的影响查询速度。
索引
【备注】在书中，介绍这个索引的时候，先通过构建一个最简单的索引、再结合简单索引带来的问题，引出B+树的概念。这个介绍较为复杂的概念的时候，还是挺有意思的，循序渐进。
真正的用户数据存放在B+树的根节点（InnoDB中标记为第0层），其余层均为索引页，即在记录头中的record_type=1 目录项记录
聚簇索引（Clustered Index）
B+树索引的特点（这里不是介绍B+树数据结构的特定）：
- 使用记录主键值的大小进行记录和页的排序：
- 页的构成
- 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表
- 存放目录项的页分为不同层级，在同一层级中的页也是根据页中的目录项记录的主键大小顺序排成一个双向链表
- B+树的叶子节点存储的时完整的用户记录。
二级索引（Secondary Index）
聚簇索引的特定是：只能搜索条件是主键时才能发挥作用。如果遇到其它列查询条件，提升的办法就是按照指定列再建一个B+树索引（简单场景，实际要复杂）
这样的B+树索引与聚簇索引的不同：
- 使用指定列的大小记录进行记录和页的排序：
- 页：按照指定列的大小顺序排成一个单向链表
- 各个存放用户记录的页也是根据页中记录的指定列大小顺序排成一个双向链表
- 存放目录项记录的页分为不同的层级，在同一层级中的页也是根据页中目录项记录的指定列大小顺序排成一个双向链表.
- B+ 树的叶子节点存储的并不是完整的用户记录，而只是指定列+主键这两个列的值.
- 目录项记录中不再是主键+页号的搭配， 而变成了指定列+页号的搭配
这里牵扯到一个回表的概念：例如上图所示，我们在检索一个数据项（通过非主键查询）的时候，而最终的B+树节点存储的是指定列和主键信息，而完整的数据项信息，还要通过这个主键再去查询聚簇索引，这个过程就称之为回表。
为啥要回表？如果按照聚簇索引概念，B+树存放完整的数据页信息，那么意味着，每建立一个非主键索引，都要完整的存储一份数据页信息，这样的空间会非常大。
联合索引（Compound Index）
同时以多个列的大小作为排序规则，本质依旧是一个二级索引，只不过是按照多列的大小为排序规则，依次进行排序建立数据页。
InnoDB中B+树索引的注意事项
根页面固定
一个B+树索引的根节点自创建时，就不会再移动（页号不会再变化）
内节点中的目录项记录的唯一性
为了让新插入的记录能找到自己处于哪个页中，二级索引的内节点的目录项记录实际上有3个部分组成：
- 索引列的值
- 主键值
- 页号
一个页面至少容纳2条记录
MyISAM中的索引方案简介
MyISAM的索引是和数据分开存储的，如下图存储空间示意图所示：
使用MyISAM存储引擎的表会把索引信息单独存到另外一个文件中（称之为索引文件）。MyISAM会为表的主键单独创建一个索引，只不过再索引的叶子节点存储的不是完整的用户记录，而是主键值与行号的组合。也就是先通过索引找到对应的行号，再用行号去数据文件中找到完整的记录。
侧面说明，MyISAM查询都要进行一次回表操作，也就是说在MyISAM中建立的索引相当于全是二级索引！
书中一句总结：InnoDB中的“索引即数据，数据即索引”，而在MyISAM中确是“索引是索引，数据是数据”
自适应哈希索引
自适应哈希索引（Adaptive Hash Index, AHI）的诞生为了更快的对缓冲池中的B+数页进行查找，InnoDB会监控对表上各个索引页的查询，如果观察到建立哈希索引可以带来速度上的提升（热点索引），则建立哈希索引。
在InnoDB中使用哈希算法来对字典进行查找，其冲突机制采用链表的方式，哈希函数采用除法散列【h(k) = k mod m】的方式。
对于缓冲池页的哈希表来说，在缓冲池中的Page页都有一个chain指针，它指向相同哈希函数值的页。
而对于除法散列，m的取值略大于2倍的缓冲池页数量的质数。例如：当前innodb_buffer_pool_size的大小为10M，则共有640个16KB的页。对于缓冲池页内存的哈希表来说，就需要分配640 x 2 = 1280个hash槽，但是由于1280不是质数，需要取一个比1280略大的质数，应该就是1399，所以启动的时候会分配1399个槽的哈希表，用来哈希查询所在缓冲池中的页。
Hash索引问题
- 不支持范围索引查询，只能支持等值索引查询
InnoDB缓冲页查找下标
InnoDB的表空间都一个space_id，用户所要查询的应该是某个表空间的某个连续的16KB的页，即偏移量offset。InnoDB将space_id左移20位，然后加上这个space_id和offset，即关键字K=space_id<<20 + space_id + offset，然后再通过除法散列到各个槽中去查询。
如下图所示，我的服务器中的mysql的hash index状态：![服务器AHI状态]()
InnoDB的全局索引
5.6版本之后InnoDB存储引擎开始支持全文索引，5.7版本之后通过使用ngram插件开始支持中文。之前仅支持英文，因为是通过空格作为分词的分隔符，对于中文来说是不合适的
MySQL允许在char、varchar、text类型上建立全文索引
TODO
B+树索引的使用
- 每个索引都对应一裸B+ B+ 树分为好多层， 最下边一层是叶子节点，其余的是内节点。所有用户记录都存储在B+ 树的叶子节点， 所有目录项记录都存储在内节点。
- InnoDB 存储引擎会自动为主键建立聚簇索引(如果没有显式指定主键或者没有声明不允许
存储NUUL的UNIQUE 键，它会自动添加主键) ， 聚簇索引的叶子节点包含完整的用户记录。 - 我们可以为感兴趣的列建立二级索引，二级索引的叶子节点包含的用户记录由索引列和主键组成·如果想通过二级索引查找完整的用户记录，需要执行目表操作， 也就是在通过二级索引找到主键值之后，再到聚簇索引中查找完整的用户记录
- B+ 树中的每层节点都按照索引列的值从小到大的顺序排序组成了双向链表，而且每个页内的记录(无论是用户记录还是目录项记录)都按照索引列的值从小到大的顺序形成了一个单向链表。
如果是联合索引， 则页面和记录先按照索引列中前面的列的值排序: 如果该列的值相同，再按照索引列中后面的列的值排序。
比如， 我们对列c2 和c3建立了联合索引idx_c2_c3(c2，c3，那么该索引中的页面和记录就先按照c2 列的值进行排序;如果c2 列的值相同， 再按照c3 列的值排序.
贴一个图（注意看里面节点的排序规则）：![联合索引排序结构示意图]()
- 通过索引查找记录时，是从B+ 树的根节点开始一层一层向下搜索的.由于每个页面(无论是内节点页面还是叶子节点页面〉中的记录都划分成了若干个组， 每个组中索引列值最大的记录在页内的偏移量会被当作槽依次存放在页目录中(当然， 规定Supremum 记录比任何用户记录都大) ，因此可以在页目录中通过二分法快速定位到索引列等于某个值的记录.
索引使用的代价
- 空间代价：数据量庞大的表中，索引占据的存储空间是不容小觑的
- 时间上代价：
- B+树本身的维护，需要耗费性能，对页面进行分裂、回收动作等
- 如果多索引，往往你的查询语句可能需要跨索引查询，索引多了可能会导致成本分析时间过长，从而影响查询语句性能
应用B+树索引
这个章节其实就是阐述命中索引与索引命中失效的场景。这个章节里面，介绍了结果，细微的地方还是少了一点介绍，比如为什么 key1 like '%suf'形成的扫描区间是(-∞，+∞)呢？
关于全表扫描，这是一个非常可怕（性能角度）的方案，但是也是一种万能执行的方案，所有查询都能执行。
联合索引为什么会遵从最左原则？书中分析的几个联合索引查询的场景，其实有答案的，就是因为构成联合索引的B+树就是按照创建时从左到右的数据，来构建内节点的。
更好的创建和使用索引（三星索引）
三星索引是评判索引设计的一个比较好的准则：
- 索引将相关的记录放到一起则获得一星；
- 如果索引中的数据顺序和查找中的排列顺序一致则获得二星（排序星）
- 如果索引中的列包含了查询中需要的全部列则获得三星（宽索引星）
细分要点
- 只为用于搜索、排序或分组的列创建索引
- 考虑索引列中不重复值的格式
- 索引列的类型尽量小
- 为列前缀建立索引：【备注】这种确实还没怎么遇到过，不过是一种形式，字符串的前几位作为索引
- 覆盖索引：彻底减少回表带来的操作，可以在返回列中只包含索引列，这样就无需回表了。
- 让索引列以列名的形式在搜索条件中单独出现：这种就是索引失效的场景，结合最左原则
- 主键聚簇索引列一定要自增：很好理解，因为要排序，所以减少不必要的数据页的分配操作，自增
- 避免建立冗余索引
【原理】InnoDB逻辑存储结构
MySQL的数据目录
数据目录的结构
除了我们创建的数据库、表、视图和触发器等用户数据·除了这些用户数据，为了让程序更好地运行. MySQL 也会创建一些额外的数据。
数据库在文件系统中的表示
当我们新建一个数据库的时候，MySQL会帮我们做两件事：
- 在数据目录下创建一个与数据库名同名的子目录(或者说是文件夹)
- 在与该数据库名同名的子目录下创建一个名为
db.opt的文件.这个文件中包含了该数据库的一些属性，比如该数据库的字符集和比较规则。表在文件系统中的表示
每个表的信息可以分为两种： - 表结构定义
- 表中的数据
MySQL中定义了一个表名.frm的格式的文件，作为表结构的定义
关于数据，则在不同的存储引擎下，结构时不同的InnoDB如何存储表数据
InnoDB的设计者提出了表空间（table space）或者文件空间（file space）的概念。 - 系统表空间（system tablesapce）
这个系统表空间可以对应文件系统上一个或多个实际的文件。在默认情况下，lnnoDB 会在数据目录下创建一个名为ibdata1，大小为12MB 的文件，这个文件就是对应的系统表空间在文件系统上的表示。这个文件是自扩展文件，如果不够用时会自动扩展。 - 独立表空间（file-per-table tablespace）
在MySQL6.6 以及之后的版本中，InnoDB 不再默认把各个袤的数据存储到系统表空间中，而是为每一个表建立一个独立表空间。也就是说，我们创建了多少个表，就有多少个独立表空间。在使用独立表空间来存储表数据时， 会在该表所属数据库对应的子目录下创建一个表示该独立表空间的文件，其文件名和表名相同，只不过添加了一个.ibd扩展名。 - 其它类型表空间
还有比如通用表空间（general tablesapce）、undo表空间（undo tablesapce）、临时表空间（temporary tablesapce）等MyISAM是如何存储表数据
结合之前的介绍，“索引是索引，数据是数据”的概念。MyISAM的分别对索引和数据创建了：.MYD和.MYI两个文件其它文件
还包含： - 服务器进程文件：每运行一个MySQL 服务器程序，都意味着启动一个进程. MySQL服务器会把自己的进程ID 写入到这个文件中。
- 服务器日志文件：服务器运行期间，会产生各种各样的日志，错误日志、二进制日志、redo 日志等。
- SSL 和RSA 证书与密钥文件：主要是为了客户端和服务器安全通信而创建的一些文件。
InnoDB的表空间
先来预览一下：
常用的页面类型（回顾）
InnoDB是以页为单位管理存储空间的。我们的聚簇索引（也就是完整的表数据）和其他的二级索引都是以B+树的形式保存到表空间中，而B+树的节点就是数据页。
所有类型数据页共有部分：File Header与File Trailer其中关于Header的：
独立表空间结构
InnoDB支持多种类型的表空间，我们重点关注独立表空间和系统表空间。
然而需要抓住一个主线：后面的一大堆乱七八糟的结构，都是为了一件事：尽可能的减少随机IO，但是又不想让数据量少的表浪费空间。
关于区（extent）
为了更好的管理页，InnoDB设计了区（extent）的概念
对于16KB的页来说，连续的64个页就是一个区(extent)，一个区默认占用1MB空间。
表空间被划分为许多连续的区，每个区默认由64个页组成，每256个区划分为一组，每个组的最开始的几个页面类型是固定的。
从结构定义上来看，为啥又要有区？
就是为了在物理位置上连续的64个页，来尽可能的减少随机I/O（机械磁盘的磁头重新定位，SSD的寻址）
关于段（segment）
问题：我们在使用B+数执行查询时知识在扫描叶子节点记录，如果不区分叶子节点和非叶子节点，统统把节点代表的页面放到申请的区里面，那么扫描跟从头到位扫描有啥区别？
在InnoDB中，为了区分B+树的叶子节点与非叶子节点的存储，存放叶子和非叶子节点的数据，称之为一个段。也就是说，一个索引会生成两个段，一个叶子节点段和一个非叶子节点段。
在这个过程中，如果对于较小的表，也严格用这套规则，势必会浪费空间。针对小表，提出碎片（fragment）的概念，一个碎片区中的页属于段A，有些属于段B，有些页甚至不属于任何段。碎片区直属于表空间， 并不属于任何一个段。
区的分类
区大致分为4中类型，或者说是4中状态（State），如下图所示：
分别释义为：
- 空闲的区：现在还没有用到这个区中的任何页面。
- 有剩余空闲页面的碎片区：表示碎片区中还有可被分配的空闲页面。
- 没有剩余空闲页面的碎片区：表示碎片区中的所有页面都被分配使用，没有空闲页面。
- 附属于某个段的区：我们知道，每一个索引都可以分为叶子节点段和非叶子节点段。除此之外，lnnoDB还会另外定义一些特殊用途的段，当这些段中的数据量很大时，将使用区作为基本的分配单位（也就是提级的道理），这些区中的页面完全用于存储该段中的数据(而碎片区可以存储属于不同段的数据).
【备注】MySQL怎样运行一书中提到了一个比喻，很形象（看过亮剑的肯定立马能懂bushi）：如果把表空间比作一个集团军，段就相当于师，区就相当于团。一般来说，团都是隶属于某个师，就像是处于FSEG 的区全都隶属于某个段；而处于FREE、FREE FRAG 以及FULL_FRAG 这3 种状态的区却直接隶属于表空间，就像独立团直接听命于军部一样。
关于XDES Entry结构
为了方便管理这些区，设计了一个XDES(Extent Descriptor) Entry的结构，结构示意图如下图所示：
每个段中的区对应的XDES Entry 结构建立了3 个链表：
- FREE链表：同一个段中， 所有页面都是空闲页面的区对应的XDES Entry 结构会被加入到这个链表中。注意， 这与直属于表空间的FREE 链袭区别开了，此处的FREE 链表是附属于某个段的链表。
- NOT FULL链表：同一个段中， 仍有空闲页面的区对应的XDES Entry结构会被加入到这个链表中。
- FULL链表：同一个段中，己经没有空闲页面的区对应的 XDES Entry 结构会被加入的这个链表中。
1 | create table t ( |
拿上面这个建表语句为例，表t共有两个索引：一个聚簇索引和一个二级索引。
- 所以这个表共有4个段（叶子节点和非叶子节点各一个），每个段都会维护上述的3个链表，总共12个链表。
- 再加上前文说到的3个直属于表空间的3个链表，
- 整个独立表空间共需要维护15个链表。
所以计算公式是：维护的XDES Entry链表个数 = 索引个数 * 2 * 3 + 3
链表基节点
前面的一大堆链表，怎么找到某个链表呢？为了解决快速寻找某个链表的头尾节点问题，设计了一个链表基节点的概念（List Base Node），结构示意图如下图所示：
段的结构
逻辑上的概念，由若干个零散的页面以及一些完整的区组成。定义了一个INODE Entry结构，示意图如下图所示：
各个类型页的存储的细节
对前面的各个逻辑概念底层到底存储在表空间什么地方做了一层详细的介绍
FSP_HDR类型
第一个组的第一个页面，也是表空间的第一个页面， 页号为0，结构示意图如下图所示：
组成的部分含义如下表所示：
其中：
- File Space Header部分：用于存储表空间的整体属性，如表空间ID、表空间拥有的页面数等。
- XDES Entry部分：紧挨着File Space Header部分的就是XDES Entry 部分了。XDES Entry 就存储在表空间的第一个页面中。一个XDES Entry 结构的大小是40 字节，由于一个页面的大小有限， 只能存放数量有限的XDES Entry 结构， 所以我们才把256 个区划分成一组。
XDES类型
与FSP_HDR类型非常相似，区别不同的是，无需记录表空间的整体属性，如下图所示：
IBUF_BITMAP类型
这种类型的页中记录一些有关Change Buffer的东西。一个对于Insert以及Update和Delete过程中，产生的随机IO问题，进行优化的一个方案。书中没有过多介绍，先知道有这么个结构，本质还是一棵B+树。
INODE类型
INODE类型的页就是为了存储INODE Entry结构存在的，结构示意图如下托所示：
INODE类型具体节点释义如下图所示：
其中List Node fro INODE Page List，这里面维护了两个链表，具体操作含义同前文中的INODE Entry中维护的3个链表节点类似：
- SEG_INODES_FULL:
- SEG_INODES_FREE:
而上面两个链表访问的基节点就是在前文中的FSP_HDR页类型中保存的，这样一来就可以快速访问了。
Segment Header结构的运用
一个索引产生两个段，分别是叶子节点段和非叶子节点段，而每个段都会对于一个INODE Entry结构。怎么知道某个段对应哪个INODE Entry结构呢？
PAGE_HEADR中（在前文提到InnoDB中关于数据页【INDEX类型的页】结构时提到的），如下图所示：
这两个类型都占用10字节，而这个两个结构中，都运用了一个叫Segment Header的结构，如下图所示：
其结构对于的描述信息如下图所示：
系统表空间
系统表空间与独立表空间类似，只不过整个MySQL进程只有一个系统表空间，用来存放一些有关整个系统相关联信息的，所以这个表空间的ID，即SpaceID=0，0号表空间。
系统表空间整体结构，如下图所示：
可以与前文独立表空间结构比较，发现前三个都是一致的，但是后面几个则是系统表空间所独有的：
除了这几个特有的页意外，extend1和extend2两个区，也就是页号从64~191的这128个页面称之为Doublewrite Buffer(双写缓冲区)。（MySQL的一大特性哦，这里登场了）此时就需要知道一点，这些特有的多数跟事务和多版本控制（MVCC）操作相关即可！细节在后续事务介绍中，还有详细的描述
InnoDB数据字典
再次回顾一下一条insert语句执行的过程：
- 首先，平时我们通过Insert向MySQL插入的记录称之为用户数据。但是，当我们向MySQL一个表中插入一条用户记录的时候，MySQL先要校验插入语句对应的表是否存在，以及插入的列和表中列是否符合，也就是语法校验的过程。
- 其次，如果语法校验没有问题，还需要知道表的聚簇索引和所有二级索引对于的根页面是哪个表空间和哪个页面
- 然后，再把记录插入到对应的B+树中
从上述过程就可以知道，除了维护各个B+树所需要的相关信息外，还要很多其他的信息才能运行。
保存的额外信息（主要）
- 某个表属于哪个表空间，表里面有多少列
- 表对应的每个列的类型是什么
- 该表有多少个索引，每个索引对应的列是哪几个字段，该索引对应的根页面在哪个表空间里面
- 该表有哪些外键，外键对应的表有哪些列
- 某个表空间对应的文件系统上的路径是什么
上述信息不仅仅是为了Insert而但是，只是为了更好的管理数据，而不得已存储的额外信息。因此，InnoDB的设计者们，额外的定义了一些列的系统内部表(internal system table)：
其中SYS_TABLES, SYS_COLUMNS, SYS_INDEXES, SYS_FIELDS尤为重要，称之为基本系统表（basic_system_tables），下面分别介绍相关信息：
- SYS_TABLES表
表相关的列如下：![SYS_TABLES表的列]()
SYS_TABLES表有两个索引：
- 以NAME为主键的聚簇索引
- 以ID列建立的二级索引
SYS_COLUMNS表
![SYS_COLUMNS表的列]()
该表只有一个索引，即以(TABLE_ID,POS)为联合主键的聚簇索引SYS_INDEXES表
![SYS_INDEXES表的列]()
该表也只有一个索引，即以(TABLE_ID,ID)为联合主键的聚簇索引SYS_FIELDS表
![SYS_FIELDS表的列]()
该表只有一个索引，即以(INDEX_ID,POS)为联合主键的聚簇索引Data Dictionary Header页面
这个页面非常特殊，页号为7，类型为SYS的页面，这个页面记录的上述等几个表的数据字典信息，其结构如下：![页号为7的页的结构示意图]()
可以看到这个页号有下面几个部分组成：![页号为7的页的组成部分及其描述]()
其中，比较特殊的是，Data Dictionary Header头部信息：
- Max Row ID: 列中隐藏row_id的值来源于这个全局共享的ROW_ID字段
- Max Index ID: 全局共享索引ID，每次新建一个索引时，就会将该字段加1
- Max Space ID: 道理同上
- Mix ID Low(unused)
- Root of SYS_TABLES clust index: 表示SYS_TABLES表聚簇索引页的根的页号
- Root of SYS_TABLES_IDS sec index: 表示SYS_TABLES表为ID建立的二级索引的根页面的页号
- Root of SYS_COLUMNS clust index: 表示SYS_COLUMNS表聚簇索引页的根的页号
- Root of SYS_INDEXES clust index: 表示SYS_INDEXES 表聚簇索引页的根的页号
- Root of SYS_FILEDS clust index: 表示SYS_FILEDS 表聚簇索引页的根的页号
- information_schema系统数据库
这里提供的系统数据库的表，仅仅是可以理解为，设计者为了方便后续排查底层问题时，开发的一个查询入口。真正的系统数据库表，还是那些个SYS类型的页。
【原理】InnoDB执行原理
单表访问方法
关于访问方法
MySQL设计者把MySQL 执行查询语句的方式称为访问方法(access methond) 或者访问类型
单表书中使用的演示表对应的DDL脚本如下：
1 | create table single_table ( |
书中示例插入1W条示例数据，建议读者自行插入，除了主键列外，随机生成。如需要，不妨参考如下一个简易的存储过程，入参就是插入表的条数：
1 | DELIMITER $$ |
const
通过主键或者唯一二级索引列来定位一条记录的访问方法定义为const (意思是常数级别的， 代价是可以忽略不计的) 。不过要达到const访问，却有些限制：
- 主键列
- 唯一二级索引与一个常量(【备注】书中写的是常数，个人觉得这个地方用常量表述更贴切，下同)进行比较
- 如果主键列和唯一索引是由多个列构成，则需要每个列都与常量比较时才有效（只有对多个列进行等值比较时，才能保证结果唯一，才能起效）
书中提到了一个特例，唯一索引与NULL比较，因为唯一索引列并不限制NULL值的数量，所以下述语句是可能有多条记录的，也就是不可以使用const访问方法来执行：
1 | -- type=ref,Extra=Using index condition |
ref
有时候我们需要将一个普通的二级索引与常量进行比较，如下面的SQL所示：
1 | -- type=ref |
对于这个查询，当然可以通过全表扫描的方式查询，不过也可以通过idx_key1的方式查询，此时的查询步骤可以解释为：
- 此时对于的扫描区间是：[‘abc’, ‘abc’]，这也是一个单点扫描区间。
- 可以定位到key1=’abc’的第一条记录
- 沿着记录的单向链表向后扫描，当某条记录不符合’abc’时为止
- 由于查询的返回列是全部，所以拿到二级索引记录对应的主键ID，再从聚簇索引回表查询拿到所有用户记录（实际进行的过程是，没命中一个记录，就会立即回表进行查询，而不是等待遍历完）
因为普通二级索引并不限制值的唯一，所以二级索引的遍历的效率完全取决于扫描区间中命中的记录数量。把这种搜索条件为二级索引列与常量进行【等值比较】，形成的扫描区间为单点扫描区间， 采用二级索引来执行查询的访问方法称为ref。
有两个特别的点需要了解：
- 由于二级索引（包含唯一索引）并不限制存储NULL的值，也就是如果通过
key IS NULL的形式进行检索的话，所以查询时最多到ref，而非const查询 - 对于索引列中包含多个列的二级索引来说，只要从索引最左列开始，进行与常量值【等值】比较时，就可以进行ref查询。反之，如下面语句：
1 | -- 这样的范围比较查询则无法调用ref进行查询，而是后面提到的：range |
ref_or_null
有时， 我们不仅想找出某个二级索引列的值等于某个常量的记录，而且还想把该列中值为NULL的记录也找出来。比如下面这个查询：
1 | select * from single_table where key1 = 'abc' or key1 is null; |
当使用二级索号|而不是全表扫描的方式执行该查询时，对应的扫描区间就是[NULL, NULL]以及[‘abc’， ‘abc’] ，此时执行这种类型的查询所使用的访问方法就称为ref_or_null。
为什么要单独加个这种操作呢？其实是因为列值为NULL的记录，会被存放在索引页的最左边。
range
有时候我们要进行复杂条件查询的时候，例如下面这个查询语句：
1 | select * from single_table where key2 in (1438, 6238) or (key2 >= 38 and key2 <= 79); |
如果仅使用idx_key2进行查询，那么此时的对应的扫描区间就是：[1438, 1438] 、[6328 ,6328] 以及[38 , 79]。此时则把**”使用索引执行查询时， 对应的扫描区间为若干个单点扫描区间或者范围扫描区间的访问方法称为range。有两种例外要记得：
- 仅包含一个单点扫描区间的访问方法不能称为range访问方法。要看实际情况，可能是const，也可能是ref
- 扫描区间为(-∞，+∞)的访问方法也不能称为range访问方法。
index
来看看比较有意思的两个查询场景：
1 | -- type=index |
1 | -- type=ALL |
这两个查询都有个特点，查询条件都是key_part2，该列虽然在二级索引idx_key_part中，但是由于不是最左列，所以无法形成合适的查询范围区间来减少扫描的数量，从而无法使用ref与range查询。但是，第一个查询有如下特点：
- 查询返回列只包含key_part1,key_part2,key_part3这三列，这三列刚好也都在idx_key_part索引中。
- 搜索条件中，只有key_part2，这列也刚好在该索引中。
这个查询过程，可以解释为：也就是说，我们可以直接遍历idx_key_part索引的所有二级索引记录， 针对获取到的每一条二级索引记录，都判断key_part2 = ‘abc’ 条件是否成立。如果成立，就从中读取出key_part1,key_part2,key_part3这3 个列的值并将它们发送给客户端。很显然， 在这种使用idx_key_part索引执行上述查询的情况下，对应的扫描区间就是(-∞ ， +∞ ) 。
但是要知道，虽然扫描区间是针对idx_key_part所有记录，但是遍历索引的代价是要比扫描全部聚簇索引的代价要小得多，MySQL的设计者，把这种遍历全部二级索引的方法称之为index查询。
【备注】注意，这里笔者贴了一个鲜明的反例，就是第二条查询语句，返回的是所有列，此时查询方法就有天壤之别。究其原因是，本身没有形成有效的查询范围，虽然遍历idx_key_part2全部索引，可以使用index查询。但是，要获取全部数据，所以必定要回表查询，这俩叠加，代价太大，就是性能最差的全表扫描了。不妨，再看看下面3中场景，查询方法又都分别对应什么呢？
1 | -- type=index |
关于index查询，还有个特例，当如果添加了”order by 主键“的条件，此时也会走index查询，如下语句所示：
1 | select * from single_table order by id; |
【备注】同样，主键我们知道，也会存在联合主键。如果变成联合主键，再添加order by，结果可能又该是什么呢？如果order by的列是联合主键的非最左列，又会怎样？建议你上手试试，巩固一下前面学习的知识。
all
这个没什么好说，就是我们老生常谈的全表扫描，对于InnoDB来说就是扫描全部的聚簇索引记录。
注意事项
二级索引+回表相关
在实际工作中，往往我们将会遇到查询条件需要多列场景的查询，如下面查询SQL示例：
1 | select * from single_table where key1='abc' and key2 > 1000; |
如果分别用这俩条件分析查询条件，很容易得到结果。但是实际情况，MySQL查询优化器会通过访问表中的少量数据或者直接根据事先生成的统计数据， 来计算[‘abc’ ,’abc’]扫描区间包含多少条记录，再计算(1000, +∞)扫描区间包含多少记录，再根据特定的算法来计算出这两个扫描区间的成本分别是多少。
一般来说， 等值查找比范围查找需要扫描的记录数更少(也就是ref 访问方法一般比range访问方法好； 但这并不总是成立， 也有可能在采用ref 方法访问时，相应的索引列为特定值的行数特别多)。假定，优化器最后决定使用idx_key1索引来执行查询，那么这个过程可以描述为下述步骤：
- 先通过idx_key1 对应的B+树定位到扫描区间[‘abc’ ,’abc’]中的第一条二级索引记录。
- 根据从步骤1中得到的二级索引记录的主键值执行回表操作， 得到完整的用户记录，再检测该记录是否满足key2> 1000 条件。如果满足则将其发送给客户端，否则将其忽略。
- 再根据该记录所在的单向链表找到下一条二级索引记录，重复步骤2中的操作，直到某条二级索引记录不满足key1 = ‘abc’ 条件为止。
关于MRR优化
MySQL中使用了一种名为Disk-Sweep Multi-Range Read(MRR, 多范围读取)的优化措施，来减少上述场景中的随机I/O开销（相比顺序IO性能很慢，基本上差2个数量级都有可能）。更多细节，请参考MySQL官方文档相关说明：
Multi-Range Read Optimization
传统的回表问题在于：二级索引所符合的记录数下的主键ID很大程度是随机的（即主键顺序是乱的），那么回表就会带来随机IO的压力。
这时候MMR优化概括的原理：先扫描一定量的二级索引，完了收集到这块的索引的主键ID后排序好，完了再到聚簇索引中检索。以此类推。MMR设计的初衷就是为了减少随机IO的产生。
MySQL中是通过变量： read_rnd_buffer_size 大小来控制MRR单次处理的范围数。默认：256KB
1 | -- 262144 256KB |
索引合并
MySQL 在” 一般情况下”只会为单个索引生成扫描区间，但还存在特殊情况. 在这些特殊情况下. MySQL 也可能为多个索引生成扫描区间。MySQL 设计者把这种使用多个索引来完成一次查询的执行方法称为index merge (索引合并）
Intersection索引合并
比如：
1 | select * from single_table where key1='a' and key3='b'; |
这个场景，优化器可能最后会用idx_key1，也可能会用idx_key3（【备注】如果只用其中一个索引，可以想一想最后会是什么方法执行，会是ref么？），当然也可能两者均使用，这时候就会遇到Intersection（交集）索引合并，从字面意义上就能看出来，选取最后的交集出结果，这个过程是这样的：
- 再使用idx_key1扫描区间[‘a’,’a’]的同时，使用idx_key3扫描区间[‘b’,’b’]的记录。
- 两者结果找出ID列相同的结果（共有的主键结果），再进行ID回表操作，最终返回查询结果。
通过上述过程，就可以少很多回表操作。
Union索引合并
从字面意义上，也可以理解，最终是取并集查询的。
比如下面一个查询场景：
1 | select * from single_table where key1='a' OR key3='b'; |
与Intersection不同的是，中间的条件是或了。这时候细节流程就会变成选取两个索引范围中的并集，并且将ID去重后，再进行回表操作。这样就会减少回表的代价。
再来看一个复杂一点的，这里就可以先进行交集合并和再进行并集合并查询：
1 | select * from single_table where (key_part1 = 'a' and key_part2 = 'b' and key_part3 = 'c') or (key1 = 'a' and key3 = 'b') |
值得注意的是，最后进行的是union(idx_key_part,idx_key1)，还是union(idx_key_part,idx_key3)，还是union(idx_key_part,intersection(idx_key1, idx_key3))这个就要看实际场景了。
sort-union索引合并
Union 索引合并的使用条件太苛刻，它必须保证从各个索引中扫描到的记录的主键值是有序的。因为要取并集还要去重，如果是乱序的话，并集后去重的代价还是不小的。
比如下面的语句：
1 | select * from single_table key1 <'a' or key3 > 'z'; |
这个时候，就无法直接用unoin合并，这时候可以这样操作：
- 先根据key1 <’a’ 条件从idx_key1 二级索引中获取二级索引记录，并将获取到的二级索引记录的主键进行排序。
- 再根据key3 > ‘z’ 条件从idx_key3 二级索引中获取二级索引记录，并将获取到的二级索引记录的主键值进行排序。
- 再根据上述排序好的记录，再进行Union合并操作即可。
针对这种先排序，后合并的场景，称之为sort-union合并。
【备注】这时候，不妨看看下面三种场景，可能最后的结果又是什么？会不会使用sort-union合并呢？如果你一下没有感觉，建议重新翻翻书本第7章的相关概念 :)
1 | explain select * from single_table where key1 > 'a' or key3 < 'z'; |
连接的原理
关于连接查询
宽泛的可以概括出，连接就是多张表，按照一定的匹配规则（连接条件，无条件的连接，就是笛卡尔积）形成的一个聚合的查询结果的过程。
本章节中涉及到的两个简单的示例表：
1 | -- t1表 |
连接过程的简介
【备注】书中从一个简单的连接查询开始作为入口，剖析了一下这个连接查询的过程，值得仔细研读。
先看一个简单的连接查询：
1 | select * from t1, t2 where t1.m1 > 1 and t1.m1 = t2.m2 and t2.n2 < 'd'; |
上面语句中涉及到了3个查询条件：
- t1.m1 > 1
- t1.m1 = t2.m2
- t2.n2 < ‘d’
这个连接查询的大致过程：
首先确定第一个需要查询的表， 这个表称为驱动表。怎么确定，其实就是在上面一章中单表查询的场景，在里面选取代价最小的进行查询即可。我们这里假设对t1表全表扫描，执行条件t1.m1>1的判断，取得满足条件的结果，我们假定满足的t1.m1的记录有2条，分别为：2，3。
步骤1中驱动表中得到的记录，都需要到t2表中查找匹配的记录，此时被查询的t2表也称之为被驱动表。查询条件中的：
t1.m1 = t2.m2就变成了t2.m2 = 2或者t2.m2 = 3，也就是，我们在被驱动表中查询的时候，查找满足下面的条件的记录即可：1
t2.m2 = 2 and t2.n2 < 'd' OR t2.m2 = 3 and t2.n2 < 'd'
我们可以清楚的看到，被驱动表可能是需要查询多次的。
当然这里只是为了拆解查询过程，实际肯定不是等着驱动表查询记录，然后再进行被驱动表的查询。实际是，驱动表的记录满足之后，会立即去被驱动表中查询满足条件的记录。
内连接和外连接
书本中在本小节和后续章节介绍中，使用到的两张表如下：
1 | create table student( |
针对上述流程，我们先考虑一个简单的场景：查询两张表学号相同的的成绩、科目信息，则可以是这样的查询语句：
1 | select s1.number,s1.name,s2.subject,s2.score from student s1, score s2 where s1.number = s2.number; |
查询结果是这样的：
但是，我们再仔细看一下，这里面只有两位同学有成绩，明明有三位同学，往往我们也需要查询出缺考的同学的成绩。这个需求的本质查询概念是这样的：针对驱动表中的每条记录，即使在被驱动表中没有找到与之匹配的记录，也仍然需要把该驱动表记录加入到结果集。为了解决这个问题，就有了内连接和外连接的概念。:
- 对于内连接的两个表，若驱动表中的记录在被驱动表中找不到匹配的记录，则该记录不会加入到最后的结果集。前面提到的连接都是内连接。
- 对于外连接的两个表，即使驱动表中的记录在被驱动表中没有匹配的记录，也仍然需要加入到结果集。
在MySQL中，根据驱动表的选取的不同，外连接可以分为两种：
- 左外连接：选取左侧的表作为驱动表
- 右外连接：选取右侧的表作为驱动表
但是，往往我们的实际场景，有时候要把外连接中的驱动表的一些数据返回，有时候又要过滤一些，这时候就要使用过滤条件了，而在外连接中不同的过滤条件效果是不一样的：
- where子句中的过滤条件：where子句中的过滤条件就是我们平时见的那种。不论是内连接还是外连接， 凡是不符
合WHERE子句中过滤条件的记录都不会被加入到最后的结果集。 - on子句中的过滤条件：对于外连接的驱动表中的记录来说，如果无法在被驱动表中找到匹配ON子句中过滤条件
的记录， 那么该驱动表记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充。
需要注意的是：ON子句是传门为”外连接驱动表中的记录在被驱动表找不到匹配记录时，是否应该把该驱动表记录加入结果集中“这个场景提出的。所以，如果把ON子句放到内连接中，MySQL会把它像WHERE子句一样对待。也就是说，内连接中的WHERE子句和ON子句是等价的。
左(外)连接语法
1 | select * from t1 left [outer] join t2 on 连接条件 [where 普通过滤条件]; |
上述场景的连接查询语句就可以写成下面这样：
1 | select s1.number,s1.name,s2.subject,s2.score from student s1 left join score s2 on s1.number = s2.number; |
【备注】这个连接查询的细节就不用多说了，但是希望你注意，下面两种查询的边界条件是完全不一样的，要注意甄别on与where条件成立的时机：
1 | select s1.number,s1.name,s2.subject,s2.score from student s1 left join score s2 on s1.number = s2.number where s2.score > 80; |
右(外)连接语法
细节不用赘述，查询的驱动表从左边的表变为右边的表。
内连接语法
内连接和外连接的根本区别就是在驱动表中的记录不符合ON子句中的连接条件时，内连接不会把该记录加入到最后的结果集中。
其实在内连接中，最简单的语法是在FROM语句后面直接跟上多张表，但是MySQL为内连接提供了多种语法，以t1表、t2表为结果示例。
1 | select * from t1 [inner | cross] join t2 [on 连接条件] [where 普通过滤条件]; |
换句话说，也就是在MySQL中，下面三种写法是等价的：
- select * from t1 join t2;
- select * from t1 inner join t2;
- select * from t1 cross join t2;
连接的原理（内部实现）
嵌套循环连接
内连接的过程，我们可以概括为下面的两个步骤（重温）：
- 选取驱动表，使用与驱动表相关的过滤条件，选取代价最低的单表访问方法来执行对驱动表的单表查询。
- 对上述步骤，查询驱动表得到的结果集中的每一条记录，都分别到被驱动表中查找匹配的记录。
【备注】书中贴了一个大图，值得仔细看一下
如果有3个表，那么上述过程查询的结果集作为新的驱动表，再与第3张表一起，重复上面的过程。其余以此类推。
这个过程就像是一个嵌套的循环，所以这种” 驱动表只访问一次，但被驱动表却可能访问多次，且访问次数取决于对驱动表执行单表查询后的结果集中有多少条记录”的连接执行方式称为嵌套循环连接（Nested-Loop Join），这是最简单也是最笨拙的一种连接查询算法。
使用索引加快连接速度
【备注】这个过程记住两点：1. 连接查询的条件，如果是索引，是可以加快检索的速度的；2. 连接查询的时候，尽量不要返回所有列，如果能返回索引列就能解决问题，就返回这些信息。这两点步骤的拆解过程，参考关于单表访问过程中的命中过程。
基于块的嵌套循环连接
在前面的嵌套循环示例中，仅仅几条这个是很简单的。但是如果当表数据量成千上万的时候，势必会牵扯到反复访问磁盘的过程。我们是否可以在把被驱动表中的记录加载到内存时，一次性地与驱动表中的多条记录进行匹配呢？这样就可以大大减少重复从磁盘上加载被驱动表的代价了。提出了一个名为Join Buffer (连接缓冲区）的概念。
在执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个Join Buffer中，然后开始扫描被驱动表，每一条被驱动表的记录一次性地与Join Buffer中的多条驱动表记录进行匹配。由于匹配动作都是在内存中完成，所以大大减少磁盘IO访问的过程。
设计者，把这种基于连接缓冲区的嵌套循环连接称之为 ，基于块的嵌套循环连接(Block Nested-Loop Join ) 算法。
基于成本优化
什么是成本？
一条查询语句的成本在MySQL中的体现是由两个方面组成的：
- I/O成本：我们的表经常使用的MyISAM 、InnoDB 存储引擎都是将数据和索引存储到磁盘上。当查询表中的记录时，需要先把数据或者索引加载到内存中，然后再进行操作。这个从磁盘到内存的加载过程损耗的时间称为I/O成本。
- CPU成本：读取记录以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称为CPU 成本。
这里面先要知道两个默认的正对InnoDB存储引擎的成本常数：
单表查询成本
这里的使用的示例的表，还是前面涉及的single_table。
基于成本的优化步骤
MySQL的优化器会找出所有可以用来执行该语句的方案，并在对比这些方案之后找出成本最低的方案。这个成本最低的方案就是所谓的执行计划。之后才会调用存储引擎提供的接口真正地执行查，这个过程总结一下就是下面这样：
- 根据搜索条件，找出所有可能使用的索引。
- 计算全表扫描的代价
- 计算使用不同索引执行查询的代价
- 对比各种执行方案的代价，找出成本最低的那个方案。
先从一个稍微复杂一点的单表查询语句来说说优化步骤：
1 | select * from single_table where |
第一步：根据搜索条件，找出所有可能使用的索引。
再看看，对于B+树索引来说，只要索引列和常数使用：**=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN 、! = (不等于也可以写成<>)或者LIKE操作符连接起来，就会产生一个扫描区间（用LIKE匹配字符串前缀时，也会产生一个扫描区间）。也就是说，这些条件是都有可能使用到索引的，把一个查询中可能使用到的索引称之为possible keys**。
来分析分析上述语句中可能使用到索引的场景：
- key1 IN (‘a’, ‘b’, ‘c’)**：可以使用索引idx_key1**。
- key2 > 10 AND key2 < 1000：可以使用二级索引uk_key2。
- key3 > key2：不能形成有效的查询区间，不能使用索引。
- **key_part1 like ‘%hello%’**：没有通过前缀匹配，不能形成有效的查询区间，不能使用索引。
- **common_feild = ‘123’**：压根既不是索引列，索引不能使用索引。
综上能使用到的索引，就是：idx_key1 和 uk_key2。
第二步：计算全表扫描的代价
一：MySQL提供了一个查看表状态信息的方法：show table status，如下面信息所示：
1 | mysql> show table status like 'single_table'\G |
在计算全表信息的时候，会用到这个状态信息中的两个字段：
Rows：表中记录的条数。对于MyISAM是准确值，而对于InnoDB来说是一个估计值。我自己建的表中的记录数是9999条，这里是10167。
Data_length：表占用的存储字节数。对于MyISAM也是准确值，而对于InnoDB该值相当于聚簇索引存储空间大小，也及由此可以算出聚簇索引的页面数量。
~~ Data_length = 聚簇索引的页面数量 * 每个页面大小 ~~
~~ 在计算全表扫描的成本的时候，就会用到上面两个值外加上一点附加值最后得出一个最终成本数值。 ~~
结合上文提到的计算公式：Data_length=1589248字节，换算页大小：1589248/16/1024 = 97页
IO成本：97 *0 +1 =1
CPU成本：10167(rows) *2 +0 = 2034.4
最后合计（两成本相加）：98.1 + 2034.4 = 2132.5，这个就是上面表数据计算出来的全表扫描cost成本
PS：2年前的原始表数据已经丢失，这里没办法贴一个trace结果。
第三步：计算使用不同索引执行查询的代价
这个过程，整体可以分为，先从唯一二级索引，再到普通二级索引计算。中间也会涉及，记录数，回表数等相关计算。以及会包括是否需要索引合并的场景。
普通二级索引的计算公式如下：
- IO成本：区间/等值条件数 *0
- CPU成本：估算记录数（通过估算页平均记录数和估算页数量乘积） *2 +01(微调系数)
- 回表成本（一条记录等于一个页数量）：
- IO成本：记录数 *0
- CPU成本：记录数 2（索引比较的时候，这个成本不计算在内；但是最终决策的时候，会纳入计算*）
基于索引统计数据的成本计算
有时候在使用索引查询数据的时候，会形成很多单点扫描区间，例如使用IN查询，就会产生。例如下面这个场景：
1 | select * from single_table where key1 in ('a', 'b', 'c', ..., 'azz3'); |
由于idx_key1并不是唯一索引，所以这么多单点区间并不能确定到底有多少条记录。而针对众多单点扫描区间的记录来说，往往是通过扫描B+树的区间的最左记录和区间最右记录，然后再通过这两条记录之间有多少记录（少的时候，可以精确算，记录多的时候只能估算）。而，这种通过二级索引的区间范围来计算某个扫描区间内的记录数的方法，称之为index dive。
这里也有个问题，就是什么时候使用index dive来计算？加入单点区间很多很多，都通过index dive很显然不现实。所以，MySQL是通过一个系统参数来设定阈值的，你可以通过如下的查询语句来查看这个参数值，默认是200。
1 | mysql> show variables like '%dive%'; |
如果超过这个阈值，则需要通过系统索引统计数据来进行估算。可以通过：**show index from [表名]**，来查询这些统计数据，如下所示，就是我在我本地机器的统计结果信息：
每个列的具体含义，如下图所示：
那么上文中提到的通过索引统计数据来估算记录数是怎么做的呢？用到上述结果中的两个值：
- 使用SHOW TABLE STATUS 语句显示出来的Rows值：
- 使用SHOW INDEX 语句显示出来的Cardinality属性：
一个值的重复次数大约等于Rows除以Cardinality值，用我们上述的示例，就是：10167 / 9999 ≈ 1条，也就是单点区间只有1条记录。假如上面的IN查询语句形成的单点区间有20000个，那么最后的记录数估算就是：20000 * 1(上述算的的重复个数) = 20000条记录。虽然简单，但是由于选取的两个数据本来就是不准的，所以这种场景实际执行效率可能并不是这样的。
连接查询的成本
条件过滤（condition filtering）
对于两张表的连接查询来说，它的查询成本由两个部分组成：
- 单词查询驱动表的成本
- 多次查询被驱动表的成本
我们把查询驱动表后得到的记录称之为驱动表的扇出（fanout）(【备注】在MySQL 8中对这块有巨大的提升优化：https://dev.mysql.com/doc/refman/8.0/en/performance-schema-events-waits-current-table.html)。
在实际查询过程中，往往会通过关联条件的过程中，进行数据量猜测**，把这个猜测的过程称之为条件过滤(Condition Filtering)**。这个过程可能使用到索引、也可能使用到统计数据等等。
【备注】书中有提到这个猜测的规则，叫启发式(heuristic)规则。这项优化技术，似乎在多表查询中均有涉及。有兴趣的可以看看这本书籍中的介绍：https://apprize.best/data/mysql/14.html
两表连接成本分析
连接查询的总成本计算公式是这样的：
*连接查询总成本 = 单词访问驱动表的成本 + 驱动表扇出值 * 单次访问被驱动表的成本*
InnoDB统计数据是如何收集的
统计数据的存储方式
知道一件事：InnoDB针对统计数据提供了两种存储方式：永久性存储统计数据和非永久性地存储统计数据。提供了系统变量：innodb_stats_persistent来控制。
innodb_table_status
innodb_index_status
基于规则的优化
子查询形式
一般子查询有两种展现形式：
- 一种在SELECT子句中
- 一种在FROM子句中：这种往往在FROM后面会带上子查询的别名，这种别名也称之为派生表
- 在WHERE或ON子句的表达式中
子查询的过程
TODO
EXPLAIN详解
【备注】explain是在我们工作中经常要关注的一个工具，为了写出更高效的语句，通常往往要借助MySQL的explain工具进行语句分析，看看执行器是不是高效的运作了。
执行计划输出中的各列详解
table
EXPLALN语句输出的每条记录都对应着某个单表的访问方法， 该条记录的table列代表该表的表名。
id
查询语句中每出现一个SELECT关键字， 设计MySQL的大叔就会为它分配一个唯一的地值， 这个id值就是EXPLAN时输出的第一列。
普通连接查询中，即使有多个select，ID分配的也是相同的。但是如果包含子查询的场景，则会内外顺序进行区分，例如下面查询：
1 | mysql> explain select * from single_table where key1 in (select key1 from single_table2) or key3 = 'a'; |
select_type
分别都来看看每个类型的含义：
- SIMPLE：查询语句中不包含UNION 或者子查询的查询都算作SIMPLE类型；连接查询的select_type也是SIMPLE。
- PRIMARY：对于包含UNION 、UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，其中最左边那个查询的select_type 值就是PRlMARY。
- UNION：对于包含UNION 或者UNION ALL的大查询来说，它是由几个小查询组成的；其中除了最左边的那个小查询以外，其余小查询的select_type值就是UNION。
- UNION RESULT：MySQL选择使用临时表来完成UNION查询的去重工作，针对该临时表的查询的select_type就是UNION RESULT。
- SUBQUERY：如果包含子查询的查询语句不能够转为对应的半连接形式，并且该子查询是不相关子查询，而且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY。
- DEPENDANENT SUBQUERY：如果包含子查询的查询语句不能够转为对应的半连接形式， 并且该子查询被查询优化器转换为相关子查询的形式，则该子查询的第一个SELECT关键字代表的那个查询的select_type就是DEPENDENT SUBQUERY。
- DEPENDANENT UNION：在包含UNION 或者UNION ALL 的大查询中，如果各个小查询部依赖于外层查询，则除了最左边的那个小查询之外，其余小查询的select_type 的值就是DEPENDENT UNlON。
- DERIVED：包含派生表的查询中，如果是以物化派生表的方式执行查询。
- MATERIALIZED：当查询优化器在执行包含子查询的语句时，选择将予查询物化之后与外层查询进行连接查询。
partitions
这个mysql的分区表的含义，标记的是分区表的编号，即这个查询扫描过的分区表
type
单表的查询方式，前面也有提到过一些。完整的有：system 、const、eq_ref、ref、fulltext、ref_or_null 、index_range 、index_subquery、range、index、ALL。
- system：当表中只有一条记录并且该表使用的存储引攀(比如MyISAM 、MEMORY )的统计数据是精确的， 那么对该表的访问方法就是system。当然如果换成InnoDB，即使就是一条记录，这个也是会成ALL的。
- const：根据主键或者唯一索引列与常量进行等值匹配的时候
- eq_ref：执行连接查询时，如果被驱动表是通过主键或者不允许存储NULL值的唯一二级索引列等值匹配的方式进行访问的(如果该主键或者不允许存储NULL值的唯一二级索引是联合索引，则所有的索引列都必须进行等值比较) ，则对该被驱动袤的访问方法就是eq_ref。
- ref：当通过普通的二级索引列与常值进行等值匹配的方式来查询某个表时
- fulltext：全文索引
- ref_or_null：当对普通二二级索引列进行等值匹配且该索引列的值也可以是NULL 值时
- index_range：一般情况下只会为单个索引生成扫描区间
- unique_subquery：类似于两表连接中被驱动表的eq_ref访问方法，unique_subquery针对的是一些包含肘子查询的查询语句。如果查询优化器决定将IN子查询转换为EXISTS子查询， 而且子查询在转换之后可以使用主键或者不允许存储NULL值的唯一二级索引进行等值匹配。如下面执行所示：
1 | mysql> explain select * from single_table s1 where common_feild in (select id from single_table2 s2 where s1.common_feild = s2.common_feild) or s1.key3 = 'a'; |
- index_subquery：与unique_subquery类似，只不过访问子查询中的表时使用的时普通的索引。
- range：如果使用泵’引获取某些单点扫描区间的记录，那么就可能使用到range访问方法。
- index：当可以使用索引覆盖，但需要扫描全部的索引记录时。通常往往出现在联合索引中，返回列是联合索引中的一列，查询条件也是联合索引中的一列，但是查询条件不能形成有效的扫描区间，所以只能全量扫描索引了。
- ALL：全表扫描
possible_key和key
possible keys 列表示在某个查询语句中，对某个表执行单表查询的时候可能用到的索引有哪些；key则表示实际用到的索引。
key_len
例如下面这个语句：
1 | explain select * from single_table s1 where s1.key1 > 'a' and s1.key1 < 'b'; |
执行计划是这样的：
根据我们经验得出，这个扫描区间就是(‘a’, ‘b’)，有时候我们想通过执行计划判断出形成的扫描区间是什么，此时key_len字段就有用处了。MySQL的设计者们，为边界条件中包含的列位于了一个key_len的值，我们看到上述语句的key_len是303，303是怎么来的呢？
对于key_len值，是由下面3个部分组成的：
- 该列的实际数据最多占用的存储空间的长度。对于固定的长度的，如INT的，就是4个字节；对于变长的，例如varchar(100)，在UTF8字符集下最多就是300字节，而在utf8mb4下，就是400.
- 如果该列可以存储NULL值，则key_len在上述基础之上再加1。
- 对于变长类型的列来说，都会有2个字节的空间来存储该变列的实际数据占用的存储空间长度，key_len的值在原先的基础上还要加2。（【备注】如果对这个有疑问的，看看前面的存储结构）
所以上面我们执行的语句的执行计划的key_len是303是这么来的：key_len = 100 * 3 + 1 + 2
ref
当访问方法是const、eq_ref、ref、ref_or_null 、unique_subquery、index_ subquery 中的其中一个时， ref列展示的就是与索引列进行等值匹配的东西是啥。
rows
在查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的rows列就代表该表的估计行数.如果使用索引来执行查询，执行计划的rows列就代表预计扫描的索引记录数。
filtered
一句话概括，可以用于来评估当前表执行计划中，总共命中扫描数(rows) 与 其余查询条件估计范围(filtered)可以预测出最终结果数。例如下面的查询语句：
1 | mysql> explain select * from single_table inner join single_table2 on single_table.key1 = single_table2.key1 where single_table.common_feild = 'a'; |
分析一下就是：以single_table为驱动表，用的是idx_key1索引，满足条件的记录是10167条。而filtered就是满足其余条件（这里指common_feild=’a’）所占的比例，这里是10%。就意味着，10167条数据中有10%的记录满足common_feild='a'。
extra
顾名思义， Extra 列是用来说明一些额外信息的， 我们可以通过这些额外信息来更准确地理解MySQL到底如何执行给定的查询语句。
optimizer trace的神奇功效
【备注】这张值得仔细研究，往往你通过explain分析了SQL语句的执行计划，只知其一，但是你并不一定了解其中为什么这么选择了。经验丰富的老手，可能会通过以往的经验，告诉你为什么这里用了这个索引，为什么没有命中，为什么效率比较差什么的。但是，通常情况下，要怎么来分析呢？MySQL（特别注意：5.6版本以后才有这个功能）提供了一个非常有意思的机制，就是optimizer trace。通过这个玩意儿，你就能很清楚的值得，优化器优化执行的步骤是怎样的。所以，在研究学习阶段，建议你可以把这个功能打开，完了仔细研究学习一哈。
关于optimizer trace
通过如下语句可以看当前MySQL的optimizer trace的配置
1 | mysql> show variables like 'optimizer_trace'; |
默认情况下enabled=off，也就是关闭的。one_line就算了，结果一行输出，我们又不是程序，一行太难看了。
通过:
1 | set optimizer_trace = 'enabled=on'; |
来打开这个功能。
optimizer trace表分别有4列：
- query：输入的查询语句
- trace：优化过程的JSON格式文本
- missing_bytes_beyond_max_mem_size：在执行计划的生成过程中可能会输出很多内容，如果超过某个限制，多余的文本将不会显示。
- insufficient_privileges：表示是否有权限查看执行计划的生成过程， 默认值是0. 表示有权限查看执行计划的生成过程；只有某些特殊情况下，它的值才是1 .我们暂时不关心这个字段的值。
使用optimizer trace的过程
- 打开optimizer trace：打开方式上文介绍了
- 输入自己的查询语句（也可以是执行计划）
- 从optimizer_trace表中查看上一个查询的过程优化：
1
select * from information_schema.optimizer_trace；
- 可能还要观察其他语句执行的优化过程：重复2、3两步。
- 当停止查看语句的优化过程时，把optimizer_trace功能关闭
这里贴一个示例SQL，供后续分析使用：1
2
3
4
5select * from single_table where
key1 > 'a' and
key2 < 1000000 and
key3 in ('a', 'b', 'c') and
common_feild = 'abc';
【备注】如果是随机插入字符串的，按照这个条件改造几条数据。
分析过程
【备注】这个过程会很长，请耐心看看，建议你直接copy到一个JSON格式化工具中，方便查看。下述步骤，结合书中的介绍，以及自己的造的数据相关，在你的数据库中，可能执行结果并不是这样，这个要具体场景具体分析，理解其中的步骤含义即可。
注意：如果你要自己执行，通过命令行的方式连接到你的MySQL服务器上执行，我在我windows机器上，用的社区版的MySQL安装大礼包中，自带的MySQL7 Command Line Client执行的（本质就是命令行，就是省去了第一步的连接过程，运行输入个密码就行了）
1 | { |
后面还有两列，分别都是0，说明一：没有因为优化过程文本太多而丢弃的文本。说明二：权限字段也是0，这部分就略去不展示了。
虽然很多，但是还是非常有规律可循的，我们来总结一下：
- prepare阶段
- optimize阶段
- execute阶段
而对于单表查询阶段：主要关注的是optimize阶段的row_estimation过程
对于多表连接查询来说：我们主要关注的是considered_execution_plans过程
InnoDB事务相关
事务简介
事务的概念
事务的4个特性：A（Atomicity）、C（Consistency）、I（Isolation）、D（Durability）
- 原子性：
- 一致性：
- 隔离性：
- 持久性：
为了把需要这个四个特性的一个或者多个数据库操作称之为事务（transaction）
根据这些操作所执行的不同阶段把事务大致划分成下面几个状态：
- 活动的(active) ：事务对应的数据库操作正在执行过程中时，我们就说该事务处于活动的状态。
- 部分提交的（partially committed）：当事务中的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并没有刷新到磁盘时，我们就说该事务处于部分提交的状态。
- 失败的（failed）：当事务处于活动的状态或者部分提交的状态时，可能遇到了某些错误(数据库自身的错误、操作系统错误或者直接断电等）而无法继续执行， 或者入为停止了当前事务的执行， 我们就说该事务处于失败的状态。
- 中止的（aborted）：如果事务执行了半截而变为失败的状态，需要撤销失败事务对当前数据库造成的影响，也就是回滚操作。当回滚操作执行完毕后，也就是数据库恢复到了执行事务之前的状态，我们就说该事务处于中止的状态。
- 提交的（committed）：当一个处于部分提交的状态的事务将修改过的数据都刷新到磁盘中之后，我们就可以说该事务处于提交的状态。
![事务状态转换图]()
MySQL中的事务表现
【备注】先要明白一点，目前只有InnoDB和NDB（分布式），这两种存储引擎，才支持事务。
在InnoDB中，知道几种语法，一般我们很少直接通过手工命令方式来执行事务，在正常业务代码中，都会通过数据库连接中间件来统一管理事务。
redo日志
redo日志概念
【备注】关于redo日志概念部分很好理解，在缓冲池那节，我们提到过，在更新了缓冲页还没有刷新磁盘的时候，如果出现问题，这个中间过程是可能会导致数据不一致的。再来上节中提到的事务操作，我们怎么保证持久性，即使碰到数据库崩溃的场景，我们依旧能恢复崩溃前的状态？
MySQL的设计者们，通过一种类似于记录操作流水的方式来做的持久性的。
因为在系统因崩溃而重启时严要按照上述内容所记录的步骤重新更新数据页，所以上述内容也称为重做日志(redo log) 。
记住redo log的两个特点：
- redo日志占用空间非常小
- redo日志顺序写入磁盘的：在执行事务的过程中，每执行一条语句，就可能产生若干条redo日志，这些日志是顺序写入磁盘的。
redo日志格式
![redo日志通用结构]()
其中： - type：redo日志类型
- spaceID：表空间ID
- page number：页号
- data：这条redo日志的具体内容
undo日志
事务的原子性告诉我们，要么做，要么统一不做，所以每当要对一条记录进行修改的时候（insert、delete、update）：
- 在插入一条记录时，至少要把这条记录的主键值记下来，这样之后回滚时只需要把这个主键值对应的记录删掉就好了;
- 在删除一条记录时，至少要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了。
- 在修改一条记录时，至少要把被更新的列的旧值记下来，这样之后回滚时再把这些列更新为旧值就好了。
这些为了回滚而记录的东西称为撤销日志(undo log)，称之为undo日志。
事务ID
一个事务剋以是一个只读事务，也可以是一个读写事务。
只有在事务对表中的记录进待改动（增删改）时才会为这个事务分配一个唯一的事务id。
事务ID的生成
与隐藏列row_id基本类似：
- 服务器会在内存中维护一个全局变量，每当需要为某个事务分配事务id时，就会把变量的值当作事务id分配给该事务，并且把变量自增1。
- 每当这个变量的值为256的倍数时，就会将该变量的值刷新到系统表空间中页号为5的页面中一个名为Max_Trx_ID的属性中，这个属性占用8字节存储空间。
- 当系统下一次重新启动时，会将这个属性加载到内存中，将该值加上256之后赋值给前面提到的全局变量（加值的原因是上次关机时，该全局变量的值可能大于磁盘页面中的Max Trx ID属性）。
trx_id隐藏列
InnoDB记录行格式的时候，聚簇索引的记录会完整保存用户数据以外，还会自动添加名为trx_id、roll_pointer的隐藏列。
trx_id就是对聚簇索引记录进行改动的语句所在的事务对应的事务id。
roll_pointer：是指向一条undo日志地址的指针，由7个字节组成，共包含4个属性![roll_pointer结构示意图]()
undo日志格式
不同的操作日志格式是不一样的
TODO
事务隔离级别和MVCC
虽然数据库管理系统可以通过redo日志、undo日志这些手段来保证事务的原子性。但是，如果出现了并发场景，即不同的客户端、不同时间前后分别对同一数据主题进行操作，就可能带来问题。
比如，A账户向B账户前后发起了两笔转账，这两笔事务操作，假设称之为T1、T2。起始账户A有11元，账户B有2元，共计13元。
且看下图：
但是执行完之后，这两账户总额变成了18元了。在这个场景下的事务一致性的要求是“参与转账的账户的总余额保持不变”，而在上面这个场景过程中，很显然不满足一致性要求。
这就要求我们使用某种手段？虽制让这些事务按照顺序一个一个单独地执行， 或者最终执行的效果和单独执行一样。也就七是说我们希望让这些事务” 隔离”地执行，互不干涉，这也就是事务的隔离性。
这里使用到的示例表，如下：
1 | create table hero ( |
PS：要解决事务并发执行，这里可以回想到JUC下面的一堆操作了，针对读读、读写、写读、写写产生的互斥一致性问题，多数是怎么解决的。
事务并发执行时遇到的一致性问题
- 脏写（Dirty Write）
如果一个事务修改了另一个未提交的事务修改过的数据，这就意味着出现了脏写的现象。 - 脏读（Dirty Read）
如果一个事务读到了另一个未提交事务修改过的数据，就意味着发生了脏读现象。 - 不可重复读（Non-Repeatable Read）
如果一个事务修改了另一个未提交事务读取的数据， 就意味着发生了不可重复读现象，或者叫模糊读(Fuzzy Read) 现象。 - 幻读(Phantom, /ˈfan(t)əm/)
如果一个事务先根据某些搜索条件查询出一些记录，在该事务未提交时，另一个事务写入一些了符合那些搜索条件的记录(这里的写入可以指INSERT、DELETE、UPDATE 操作) ，就意味着发生了幻读现象
【备注】在书中补充说明了在MySQL场景下，幻读的现象：对于MySQL 来说，幻读强调的就是一个事务在按照某个相同的搜索条件多次读取记录时，在后读取时读到了之前没有读到的记录。这个后读取到的之前没有读到的记录可以是由别的事务执行INSERT 语句插入的，也可能是别的事务执行了更新记录键值的UPDATE语句而插入的。
SQL标准中的4种隔离级别
我们按照可能导致一致性问题的严重性给这些现象排一下序：脏写 > 脏读 > 不可重复读 > 幻读
SQL标准中，根据上面4个不同的严重程度，设立了4个隔离级别，以“舍弃一部分隔离性来换取一部分性能”，具体如下4种：
- READ UNCOMMITTED：未提交读
- READ COMMITTED：已提交读
- REPEATABLE READ：可重复读
- SERIALIZABLE：可串行化
![SQL标准中规定的并发事务执行过程中可能发生的现象]()
【备注】书中还提到了一个延申的知识点，SQL92标准中没有定义脏写的现象，在95年发表的论文《A critique of ANSI SQL isolation levels》中阐述了更为细致的事务隔离级别，如丢失更新、读偏斜、写偏斜等，有兴趣的可以直接戳连接下载该PDF文档阅读一番。 A critique of ANSI SQL isolation levels.pdf在MySQL中的情况
MySQL 虽然支持4种隔离级别， 但与SQL标准中规定的各级隔离级别允许发生的现象却有些出入，MySQL在REPEATABLE READ隔离级别下，可以很大程度上禁止幻读现象的发生(关于如何禁止会在后文详细说明)。
MySQL的默认隔离级别是REPEATABLE READ。
MVCC原理
对于使用InnoDB存储引擎的表来说， 它的聚簇索引记录中都包含下面这两个必要的隐藏列（row_id 并不是必要的; 在创建的表中有主键时，或者有不允许为NULL的UNIQUE键时，都不会包含row_id列)：
- trx_id：一个事务每次对某条聚簇索引记录进行改动时，都会把该事务的事务id赋值给trx_id隐藏列。
- roll_pointer：每次对某条聚簇索引记录进行改动时， 都会把旧的版本写入到undo日志种。这个隐藏列就相当于一个指针，可以通过它找到该记录修改前的信息。
假设，此时hero表（DDL上文贴出了）中有一条数据，如下图所示：![mvcc示例数据]()
假设插入该记录的事务id(trx_id)为80，那么此条记录的示意图如下图所示：![mvcc示例数据结构示意图]()
假设之后两个事务id分别为100、200的事务对这条记录进行update操作，操作流程如下图所示：![update操作流程]()
由于每对记录进行一次改动，都会记录一条undo日志。每条undo日志也都有一条roll_pointer属性，通过这个属性可以将这些undo日志串联成一个链表，所以现在的情况就如下图所示：![undo日志串成一个链表]()
【备注】这里只是为了方便清楚看到结构，所以每条undo日志都会有country列，实际上由于我们这个例子中并没有对该列进行过update操作，所以实际是对应的undo日志中并没有该列数据的。
在每次更新该记录后，都会将旧值放到一条undo 日志中(就算是该记录的一个旧版本)。随着更新次数的增多，所有的版体都会被roll_pointer属性连接成一个链表， 这个链表称为版本链。版本链的头节点就是当前记录的最新值。另外，每个版本中还包含生成该版本时对应的事务id。这个很重要，后面就会用这个记录的版本链来控制并发事务访问相同时的行为，我们把这种机制称之为多版本并发控制（Multi-Version Concurrency Control, MVCC）。ReadView
再来看看，针对4种事务隔离级别场景，我们怎么用版本链中的事务版本信息来满足隔离性的要求： - READ UNCOMMITTED：由于可以允许读到未提交事务修改过的记录，所以直接读取记录的最新版本就行了。
- SERIALIZABLE：MySQL是通过加锁的方式来实现的
- READ COMMITTED和REPEATABLE READ，都要保证读到以及提交的事务的修改记录。换到版本链的场景就是，需要判断版本链中的哪个版本是当前事务可见的。换句话说，也只有这俩场景需要通过MVCC来控制。
针对上面的问题，提出ReadView（也称为：“一致性视图”）的概念。ReadView里面主要包含4个重要的内容：
- m_ids：在生成ReadView时，当前系统中活跃的读写事务的事务id列表。
- min_trx_id：在生成ReadView时，当前系统中活跃的读写事务中最小的事务id; 也就是m_ids中的最小值。
- max_trx_id：在生成ReadView时，系统应该分配给下一个事务的事务id值。
【备注】注意一下，这里的max_trx_id不是m_ids里面的id最大值。而是预分配给下一个事务的id。比如现在有事务id分别为1、2、3，3号事务提交之后，此时m_ids里面是1、2。此时min_trx_id=1，而max_trx_id应该是4（下一个事务来的id）。 - creator_trx_id：生成该ReadView的事务的事务id。
有了这个ReadView后，在访问某条记录时， 只需要按照下面的步骤来判断记录的某个版本是否可见。
- 如果被访问版本的trx_id属性值小于ReadView中的min_trx_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
- 如果被访问版本的trx_id ，属性值大于或等于ReadView 中的max_trx_id值， 表明生成该版本的事务在当前事务生成ReadVìew后才开启，所以该版本不可以被当前事务访问。
- 如果被访问版本的trx_id属性值在ReadView的min_trx_id 和max_trx_id之间，则需要判断trx_id属性值是否在m_ids列表中。
- READ COMMITTED：每次读取数据前都生成一个ReadView
- REPEATEABLE READ：只在第一次读取数据时生成一个ReadView。这个也就解决了脏读问题。
【备注】由上面两个生成ReadView的时机不同，所以也就诞生的，为什么这两者关于“不可重复读”的场景的差异。因为，READ COMMITTED每次读取都生成ReadView，在这期间发生事务变更的话，你读取的时候不知道之前发生了什么的，因此之前的事务是有可能已经改变了数据。但是，REPEATEABLE READ，只在第一次读取时生成，保留了当前时刻的状态，因此不管你中间（m_ids列表中的事务）发生了什么，REPEATEABLE READ事务是肯定能够发现的。这就是两者为什么出现的差异。
我们再看看这张图：![SQL标准中规定的并发事务执行过程中可能发生的现象]()
再来解释一下为什么这种MVCC控制局面下，能够产生上面的效果 - 首先READ COMMITTED为啥能避免脏读，不能避免不可重复读嘞？每次都生成ReadView，新事务来直接比较就行了，所以怎么可能读到当前创建这个ReadView的事务还未提交的数据呢？
- 其次REPEATEABLE READ，脏读就不说了，人家第一次读的时候就记录了原始快照信息了，肯定能解决。为啥能解决不可重复读呢？因为保留了当前时刻的状态，因此不管你中间（m_ids列表中的事务）发生了什么，REPEATEABLE READ事务是肯定能够发现的，通过上面那个流程图比较场景。
还有个问题，即便有了MVCC，是如何还能再次产生幻读的？答案是能的。
我们来举个场景，依旧拿上面的那个hero表来举例子，我们用默认的REPEATEABLE READ隔离级别来举例：
- 100号的事务，开启事务并查询了第一次，只有一条记录。
- 此时200号的事务立马开启新事务的同时，在时间编号4的位置插入了一条number=2的记录后，立马提交；
- 此时100号，后续查询后，调用了一个update语句，这个语句在100号事务自己来看，应该是不存在的，但是更新成功了
- 100号事务在时间编号8的位置查询的时候，发现查到了2条记录。此时就发生了幻读。
我们再结合上述ReadView产生的时机，这个场景的幻读为啥解决不了。
因为，200号事务调用insert的时候，是会影响当前的ReadView，但是人家立马提交了，在m_ids中已经不存在了啊。结合上面的那个流程图，出现的场景就在最下面的判断的时机，如下图所示：![mvcc下幻读产生原因]()
那么怎么避免呢？需要用到后面的锁的知识了。
在此之前还有个小问题，普通二级索引下mvc怎么工作的？
二级索引与MVCC
前面的知识知道，只有聚簇索引中才有trx_id和roll_pointer隐藏列。如果某个查询使用的是二级索引来执行查询的，是如何判断可见性的呢？
答案是利用到在数据页中Page Header中的一个属性：PAGE_MAX_TRX_ID，作为判断是否需要回表寻找原始ReadView（回到文章上面那个表格截图看看）
这个过程大致是这样的：
- 每当对该页面中的记录执行增删改操作的时候，如果执行该操作事务的trx_id大于PAGE_MAX_TRX_ID，就会把PAGE_MAX_TRX_ID值设置为执行该操作事务的事务id。当select语句访问某个二级索引记录的时候，首先会看一下对应的ReadView的min_trx_id是否大于该页面的PAGE_MAX_TRX_ID。如果是，说明该页面中的所有记录都对该ReadView可见；否则就进行下面一步；
- 利用二级索引记录中的主键值进行回表操作，得到对应的聚簇索引记录后再按照前面的方式找到对该ReadView可见的第一个版本，然后判断该版本中想要的二级索引列值是否利用该二级索引查询时的值相同。如果是，就把这条记录发送给其它客户端（如果WHERE自子句中还有其它查询条件还要继续判断），否则就跳过该记录。
与purge操作
知道一个概念，InnoDB会把当前系统中所有的ReadView按照创建时间形成了一个链表。当执行purge操作的时候（有个专门的后台线程，文章最开始的架构图中有介绍），就把当前系统中最早生成的ReadView给取出来。经过一系列比较后（在这个过程中如果包含delete mark操作而产生的undo日志），会将对应的undo日志记录给彻底删除。
总结就是：当前系统中，如果最早生成的ReadView不再访问undo日志以及打了删除标记的记录，则可以通过purge操作将他们清除。
总结MVCC实现事务隔离级别的过程
一、MVCC 工作原理
版本链（Undo Log）
- 每行数据包含隐藏字段：
DB_TRX_ID：最后一次修改该行的事务ID。DB_ROLL_PTR：指向旧版本数据的指针（版本链）。
- 更新/删除时，旧数据存入Undo Log，形成版本链。
- 每行数据包含隐藏字段：
Read View（读视图）
- 事务首次查询时生成，包含：
m_ids：活跃事务ID列表。min_trx_id：最小活跃事务ID。max_trx_id：预分配的最大事务ID。creator_trx_id：当前事务ID。
- 可见性规则：
- 数据行的
DB_TRX_ID<min_trx_id→ 可见。 DB_TRX_ID∈m_ids→ 不可见。DB_TRX_ID≥max_trx_id→ 不可见。
- 数据行的
- 事务首次查询时生成，包含：
二、隔离级别处理
| 隔离级别 | Read View生成时机 | 核心行为 | 解决的问题 |
|---|---|---|---|
| READ UNCOMMITTED | 不生成Read View | 直接读取最新数据（含未提交） | 无 |
| READ COMMITTED | 每次SELECT生成新Read View | 只能看到其他事务已提交的数据 | 脏读 |
| REPEATABLE READ | 事务首次SELECT生成Read View | 整个事务使用同一Read View，保证一致性 | 不可重复读 |
| SERIALIZABLE | 不依赖MVCC，全部加锁 | 串行执行，读写互斥 | 幻读 |
三、关键机制
- RC vs RR
- RC：每次查询看到最新提交，可能“不可重复读”。
- RR：首次快照冻结数据，同一事务内数据一致。
- 幻读处理
- 标准RR：MVCC无法完全避免幻读（新插入数据可能符合条件）。
- MySQL优化：通过Next-Key Lock（临键锁） 锁住索引间隙，阻止其他事务插入。
- 快照读 vs 当前读
- 快照读：普通SELECT，基于Read View读取历史版本。
- 当前读：SELECT FOR UPDATE/INSERT/UPDATE/DELETE，读取最新数据并加锁。
锁
MySQL中的锁处理，完全可以联想JUC中的处理，有很多共通性。
解决并发事务带来问题的两种方式
并发事务带来的访问问题，大致分为下面三类：
- 读 - 读情况：并发事务相继读取相同的记录。读取操作本身不会对记录产生任何影响，所以允许这种情况产生。
- 写 - 写情况：并发事务相继对相同的记录进行改动。
- 读 - 写或写 - 读情况：也就是一个事务进行读取操作，另一个事务进行改动操作。
写-写情况
写写情况下会发生脏写情况，任何一种隔离级别都不允许这种情况发生。在多个未提交事务叶继对一条记录迸行改动时， 需要让它们排队执行。这个排队的过程其实是通过为该记录加锁对实现的。![锁结构与记录关联]()
读-写或写-读情况
怎么避免脏读、不可重复读、幻读这些现象呢？有两种可选方案： - 方案1：读操作使用MVCC，写操作进行加锁。
解释起来，普通的select语句在READ COMMITTED和REPEATABLE READ隔离级别下会使用到MVCC读取记录。
在READ COMMITTED隔离级别下，一个事务在执行过程中每次执行select操作时，都会生成一个ReadView。ReadView的存在本身就保证了事务不可以读取到未提交的事务所做的更改，也就是避免了脏读现象。在REPEATABLE READ隔离级别下，一个事务在执行过程中只有一次执行select操作才产生一个ReadView，之后的select都是复用这个ReadView，这样也就避免了不可重复读和一定程度的幻读现象。 - 方案2：读、写都进行加锁
幻读现象的产生是词为某个事务读取了符合某些搜索条件的记录， 之后别的事务又插入了符合相同搜索条件的新记录， 导致该事务再次读取相同搜索条件的记录时，可以读到别的事务插入的新记录， 这些新插入的记录就称为幻影记录。采用加锁的方式避免，幻读现象就有那么一点点麻烦，因为当前事务在第一次读取记录时那些幻影记录并不存在， 所以在读取的时候加锁就有点尴尬了一一因为我们并不知道给谁加锁。
如果采用MVCC方式，读-写操作彼此并不冲突，性能更高；如果采用加锁的方式，读-写彼此需要阻塞运行，从而影响性能。一致性读
事务利用MVCC进行的读取操作称之为一致性读（Consistent Read），或者一致性无锁读（有的资料也称之为快照读）。
所有普通的select语句（plain select）在READ COMMITTED和REPEATABLE READ隔离级别下都算是一致性读，比如：
1 | select * from t1; |
一致性读并不会对表中的任何记录进行加锁操作，其它事务可以自由地对表中的记录进行改动。
锁定读
共享锁和独占锁
【备注】哎？这里就跟JUC高度吻合了，哈哈哈
- 共享锁（Shared lock）：简称S锁，在事务要读取一条记录时，需要先获取该记录的S锁。
- 独占锁（Exclusive Lock）：简称X锁，在事务改动一条记录时，需要先获取该记录的X锁。
锁定读的语义
前面说的，采用加锁的方式避免脏读、不可重复读、幻读这些现象，在读取一条记录的时候获取该记录的S锁。
其实这不是严谨的，有时候我们想在读取记录时就获取记录的X锁，从而禁止别的事务读写该记录。我们把这种读取记录前就为该记录加锁的读取方式称之为锁定读（Locking Read）。MySQL提供了下面两种特殊的select语句格式来支持锁定读。 - 对读取记录加S锁如果当前事务执行了该语句，那么它会读取到的记录加S锁，这样可以允许别的事务继续或许这些记录的S锁，但是不能获取这些记录的X锁。如果别的记录想要获取这些记录的X锁（select for update或者直接想要修改这语句时），那么就会被阻塞，直到当前记录提交之后将这些记录上的S锁释放掉。
1
select ... lock in share mode;
- 对读取记录加X锁这个锁一加，就不允许别的事务做任何操作了，即允许获得S锁，也不允许获得X锁，只能阻塞等待。
1
select ... for update;
写操作(解释锁在写操作中的应用)
平常所用到的写操作无非时IUD操作这三种： - DELETE：对一条记录执行Delete操作的过程其实是先在B+树中定位到这条记录的位置，然后获取这条记录的X锁，最后在执行delete mark操作。
- UPDATE：对一条记录进行update操作时分为下面3中情况
- 如果未修改该记录的键值并且被更新的列所占用的存储空间在修改前后未发生变化，则先在B+树中定位到这条记录的位置，然后再获取记录的X锁，最后在原记录的位置进行修改操作。
- 如果未修改记录的键值并且至少有一个被更新的列占用的存储空间在修改前后发生变化，则先在B+树中定位到这条记录的位置，然后获取记录的X锁，之后将该记录彻底删除掉（把记录彻底移除到垃圾链表中），最后再插入一条新记录。
- 如果修改了该记录的键值，则相当于再原纪录上执行delete操作之后再来一条insert操作，加锁的操作就按照delete和insert的规则进行了
- INSERT：一般情况下，新插入的一条记录受隐式锁保护，不需要在内存中为其生成对应的锁结构。
多粒度锁
前面提到的锁都是针对记录的，可以称其为行级锁或者行锁。
对一条记录加行锁，影响的也只是这条记录而已，我们就说这个行锁的粒度比较细。其实一个事务也可以在表级别进行加锁，自然就称之为表级锁或者表锁。
给表加的锁也可以分为共享锁和独占锁：
- 如果一个事务给表加S锁，那么：
- 别的事务可以继续获得该表的S锁
- 别的事务可以继续获得该表中的某些记录的S锁
- 别的事务不可以继续获得该表的X锁
- 别的事务不可以继续获得该表中某些记录的X锁
- 如果一个事务给表加X锁（独占这个表），那么：
别的事务不可以继续获得该表的S锁
别的事务不可以继续获得该表中的某些记录的S锁
别的事务不可以继续获得该表的X锁
别的事务不可以继续获得该表中某些记录的X锁
这时候有个问题，当一个表已经有表锁的情况下，我们怎么知道有没有行记录有行锁？
InnoDB提出了一种称之为意向锁（Intention Lock）的东西：
- 意向共享锁（Intention Shared Lock）：简称IS锁，当事务准备在某条记录上加S锁的时候，先在表级别上加一个IS锁。
- 意向独占锁（Intention Exclusive Lock）：简称IX锁，当事务准备在某条记录上加X锁时，需要先在表级别加一个IX锁。
总结就是：IS锁、IX锁是表级锁，它们的提出仅仅是为了在之后加表级别的S锁和X锁时，可以快速判断表中的记录是否被上锁，以免用遍历的方式来查看表中没有上锁的记录。
看一下各个表级别的各种锁的兼容性：![表级别锁的兼容性]()
InnoDB中的锁
InnoDB存储引擎即支持表级锁，也支持行级锁。表级锁，一般我们开发中显示使用用的少，具体看看行级锁
表级锁
了解几个场景的表级锁
- 表级别的AUTO-INC锁：就是给主键字段加上AUTO_INCREMENT属性，当插入数据的时候，系统会自动给这个属性进行递增赋值。
行级锁
InnoDB中的行锁花样很多，换句话说，即使对同一条记录加行锁，如果记录的类型不同，起到的功效也是不同的。
TODORecord Lock
单个行记录上的锁Gap Lock
间隙锁，锁定一个范围，但不包括记录本身Next-Key Lock
Gap Lock + Record Lock，锁定一个范围的同时锁定记录本身Insert Intention Lock
隐式锁
语句加锁分析
普通的select语句
不同隔离级别下，普通的select语句具有不同的表现： - 在READ UNCOMMITTED下，不加锁，直接读取记录的最新版本；可能出现脏读、不可重复读和幻读现象
- 在READ COMMITTED下，不加锁；在每次执行普通的select语句都会生成一个ReadView，避免脏读现象
- 在REPEATABLE READ下，不加锁；
- 在SERIALIZABLE下，需要分两种情况讨论：
- select … lock in share mode
- select … for update
- update …
- delete …
TODO
MySQL的一些其它特性
前面众多主要围绕InnoDB的，这里罗列一些跟MySQL高级特性相关的知识，有些在笔者之前工作中是大量使用的
分区表
一、分区表核心原理
- 数据分治逻辑
- 将大表按规则拆分为多个物理子表（分区），每个分区独立存储，但对应用透明（逻辑上仍为单表）。
- 存储结构：每个分区对应独立的
.ibd文件，元数据记录分区规则。
- 查询优化机制
- 分区修剪（Partition Pruning）：根据WHERE条件自动过滤无关分区，减少数据扫描量。
- 并行扫描：对多个分区并行查询（需结合存储引擎特性）。
- 分区键（Partition Key）
- 必须为表的主键或唯一索引的一部分，或全部列。
- 分区键的选择直接影响查询效率和数据分布均衡性。
二、分区类型与适用场景
| 分区类型 | 定义 | 适用场景 |
|---|---|---|
| RANGE分区 | 按分区键范围划分（如日期、数值区间） | 时间序列数据（日志、订单按年/月分区） |
| LIST分区 | 按分区键的离散值列表划分（如地区ID、状态码） | 离散值分类存储（按地区、业务线归档） |
| HASH分区 | 对分区键哈希取模，均匀分布数据 | 数据均匀分布，无明确范围或列表需求 |
| KEY分区 | 类似HASH，但使用MySQL内置哈希函数，支持非整数分区键 | 与HASH类似，但分区键可为字符串类型 |
| 复合分区 | 组合两种分区（如先RANGE再HASH） | 超大规模数据二次拆分 |
三、使用注意事项
- 1. 分区键选择
- 高频查询条件：分区键应包含在WHERE条件中，否则无法触发分区修剪。
1
2-- 分区键为`created_at`，查询需包含该字段才能生效
SELECT * FROM logs WHERE created_at BETWEEN '2023-01-01' AND '2023-01-31'; - 数据分布均衡：避免分区键值倾斜（如90%数据集中在一个分区）。
- 避免频繁更新：修改分区键值可能触发跨分区迁移，导致性能下降。
- 高频查询条件：分区键应包含在WHERE条件中，否则无法触发分区修剪。
- 2. 性能优化
- 索引设计：
- 每个分区独立维护本地索引，全局索引需手动创建（MySQL 8.0+支持全局索引）。
- 优先在分区键上建索引，避免全分区扫描。
- 分区数量控制：
- 分区数过多（如>1000）导致元数据管理开销增大，建议不超过100个。
- 避免跨分区查询：
1
2-- 跨多个分区的聚合查询性能差
SELECT COUNT(*) FROM logs; -- 需扫描全部分区
- 索引设计：
- 3. 维护操作
- 分区管理：
- 新增分区：
ALTER TABLE logs ADD PARTITION (PARTITION p2024 VALUES LESS THAN ('2025-01-01')); - 删除分区：
ALTER TABLE logs DROP PARTITION p2020;（直接删除文件，效率高）
- 新增分区：
- 数据归档：
- 通过
EXCHANGE PARTITION将分区数据迁移到外部表，实现快速归档。
- 通过
- 备份恢复：
- 可单独备份特定分区，但需注意元数据一致性。
- 分区管理：
- 4. 限制与兼容性
- 功能限制：
- 不支持外键、全文索引、空间索引。
- 子查询、存储过程等可能无法充分利用分区修剪。
- 版本差异：
- MySQL 5.7+支持更多分区类型（如
RANGE COLUMNS）。 - MySQL 8.0+支持全局索引（
GLOBAL INDEX），减少本地索引冗余。
- MySQL 5.7+支持更多分区类型（如
- 功能限制：
四、分区表 vs 分库分表
| 维度 | 分区表 | 分库分表 |
|---|---|---|
| 扩展性 | 单机内扩展，受限于磁盘和CPU | 支持跨节点水平扩展 |
| 复杂度 | 开发透明，无需业务改造 | 需处理分布式事务、跨库JOIN等复杂性 |
| 适用数据量 | 单机可承载的数据量（通常TB级） | 超大规模数据（PB级） |
| 维护成本 | 低（自动分区修剪、标准SQL操作） | 高（需中间件、数据迁移工具） |
五、最佳实践示例
场景：日志表按月份分区，快速清理过期数据。
1 | -- 1. 创建RANGE分区表 |
总结
适用场景：
- 数据量巨大但无需分布式扩展的单机表。
- 按时间或类别清理数据的场景（如日志、订单历史）。
避坑指南：
- 避免分区键选择不当导致数据倾斜。
- 分区表不是银弹，超大规模数据优先考虑分库分表。
- 监控分区锁争用（如频繁的
TRUNCATE PARTITION）。
决策建议：
- 数据量在单机承载范围内时，优先使用分区表简化管理。
- 结合业务查询模式设计分区键，确保分区修剪生效。
分布式(XA)事务
此部分可参考，业务相关篇章的介绍：
分布式事务TCC