《MySQL 是怎样运行的：从根儿上理解 MySQL》
x
截止到服务器程序完成了查询优化为止，还没有真正的去访问真实的数据表，MySQL 服务器把数据的存储和提取操作都封装到了一个叫存储引擎的模块里。我们知道表是由一行一行的记录组成的，但这只是一个逻辑上的概念，物理上如何表示记录，怎么从表中读取数据，怎么把数据写入具体的物理存储器上，这都是存储引擎负责的事情。为了实现不同的功能，MySQL 提供了各式各样的存储引擎，不同存储引擎管理的表具体的存储结构可能不同，采用的存取算法也可能不同。
小贴士：为什么叫`引擎`呢？因为这个名字更拉风～ 其实这个存储引擎以前叫做`表处理器`，后来可能人们觉得太土，就改成了`存储引擎`的叫法，它的功能就是接收上层传下来的指令，然后对表中的数据进行提取或写入操作。
“表处理器”其实更直白地说明了 storage engine 的本质（正如 docker 的 cgroup 原本的名称 process container 这么直白的说明了“docker 容器的本质是进程”），其实就是用来存放数据的东西。只不过因为数据类型不同（数据量大小、冷热、读写操作频率等），所以就需要不同的 storage engine 来满足其需求（数据在不同 SE 中存放的格式通常是不同的，比如说 memory 都不用磁盘来存储数据，也就是说关闭服务器后表中的数据就消失了。再想想 myisam 和 innodb 的区别，index 什么的。）。
还是拿图书馆类比，mysql server 的所有处理过程其实都与书架上的书籍无关（“没有访问到真实的数据表”）。
4、InnoDB 数据页结构 & 记录结构
InnoDB 有 4 种行格式，分别是 compact, redundant, dynamic, compressed.
一个数据页可以被大致划分为 7 个部分，分别是
File Header，表示页的一些通用信息，占固定的 38 字节。
Page Header，表示数据页专有的一些信息，占固定的 56 个字节。
Infimum + Supremum，两个虚拟的伪记录，分别表示页中的最小和最大记录，占固定的 26 个字节。
User Records：真实存储我们插入的记录的部分，大小不固定。
Free Space：页中尚未使用的部分，大小不确定。
Page Directory：页中的某些记录相对位置，也就是各个槽在页面中的地址偏移量，大小不固定，插入的记录越多，这个部分占用的空间越多。
File Trailer：用于检验页是否完整的部分，占用固定的 8 个字节。
这里就是 innodb page 的数据结构
4、InnoDB 会把页中的记录划分为若干个组，每个组的最后一个记录的地址偏移量作为一个槽，存放在 Page Directory 中，所以在一个页中根据主键查找记录是非常快的，分为两步：
1、通过二分法确定该记录所在的槽。
2、通过记录的 next_record 属性遍历该槽所在的组中的各个记录。
5、每个数据页的 File Header 部分都有上一个和下一个页的编号，所以所有的数据页会组成一个双链表。
6、为保证从内存中同步到磁盘的页的完整性，在页的首部和尾部都会存储页中数据的校验和和页面最后修改时对应的 LSN 值，如果首部和尾部的校验和和 LSN 值校验不成功的话，就说明同步过程出现了问题。
page directory, page header, file header, file trailer
- "*InnoDB, arch? InnoDB数据页的数据结构? How does it works?*"
- innodb 行格式 有哪4种?
- innodb page由哪7个部分组成?
各个数据页可以组成一个 双向链表 ，而每个数据页中的记录会按照主键值从小到大的顺序组成一个 单向链表，每个数据页都会为存储在它里边儿的记录生成一个页目录 ，在通过主键查找某条记录的时候可以在 页目录 中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录(如果你对这段话有一丁点儿疑惑，那么接下来的部分不适合你，返回去看一下数据页结构吧)。
通过双向链表相关联
所有类型的 page（11 种 page 类型）都有 file header 和 file trailer 两部分
File Header：记录页面的一些通用信息
File Trailer：校验页是否完整，保证从内存到磁盘刷新时内容的一致性。
9、存放页面的大池子 —— InnoDB 的表空间
9、存放页面的大池子 —— InnoDB 的表空间 - 《MySQL 是怎样运行的：从根儿上理解 MySQL》 · IT 书架
通过前边儿的内容大家知道，表空间是一个抽象的概念，对于系统表空间来说，对应着文件系统中一个或多个实际文件；对于每个独立表空间来说，对应着文件系统中一个名为表名.ibd 的实际文件。大家可以把表空间想象成被切分为许许多多个页的池子，当我们想为某个表插入一条记录的时候，就从池子中捞出一个对应的页来把数据写进去。本章内容会深入到表空间的各个细节中，带领大家在 InnoDB 存储结构的池子中畅游。
再一次强调，InnoDB 是以页为单位管理存储空间的，我们的聚簇索引（也就是完整的表数据）和其他的二级索引都是以 B+树的形式保存到表空间的，而 B+树的节点就是数据页。我们前边说过，这个数据页的类型名其实是：FIL_PAGE_INDEX，除了这种存放索引数据的页面类型之外，InnoDB 也为了不同的目的设计了若干种不同类型的页面，为了唤醒大家的记忆，我们再一次把各种常用的页面类型提出来：
因为 table space 中的 page 太多了，为了更好地管理这些 page，所以规定连续的 64 个 page 就是一个 extent（也就是一个 extent 默认占用 1MB 空间）
而 256 个 extent 划分为一组
第一个组最开始的 3 个页面的类型是固定的，也就是说 extent 0 这个区最开始的 3 个页面的类型是固定的，分别是：
...
其余各组最开始的 2 个页面的类型是固定的，也就是说 extent 256、extent 512 这些区最开始的 2 个页面的类型是固定的，分别是：
...
好了，宏观的结构介绍完了，里边儿的名词大家也不用记清楚，只要大致记得：表空间被划分为许多连续的区，每个区默认由 64 个页组成，每 256 个区划分为一组，每个组的最开始的几个页面类型是固定的就好了。
为啥好端端的提出一个区（extent）的概念呢？我们以前分析问题的套路都是这样的：表中的记录存储到页里边儿，然后页作为节点组成 B+树，这个 B+树就是索引，然后吧啦吧啦一堆聚簇索引和二级索引的区别。这套路也没啥不妥的呀～
是的，如果我们表中数据量很少的话，比如说你的表中只有几十条、几百条数据的话，的确用不到区的概念，因为简单的几个页就能把对应的数据存储起来，但是你架不住表里的记录越来越多呀。
？？啥？？表里的记录多了又怎样？B+树的每一层中的页都会形成一个双向链表呀，File Header 中的 FIL_PAGE_PREV 和 FIL_PAGE_NEXT 字段不就是为了形成双向链表设置的么？
是的是的，您说的都对，从理论上说，不引入区的概念只使用页的概念对存储引擎的运行并没啥影响，但是我们来考虑一下下边这个场景：
我们每向表中插入一条记录，本质上就是向该表的聚簇索引以及所有二级索引代表的 B+树的节点中插入数据。而 B+树的每一层中的页都会形成一个双向链表，如果是以页为单位来分配存储空间的话，双向链表相邻的两个页之间的物理位置可能离得非常远。我们介绍 B+树索引的适用场景的时候特别提到范围查询只需要定位到最左边的记录和最右边的记录，然后沿着双向链表一直扫描就可以了，而如果链表中相邻的两个页物理位置离得非常远，就是所谓的随机 I/O。再一次强调，磁盘的速度和内存的速度差了好几个数量级，随机 I/O 是非常慢的，所以我们应该尽量让链表中相邻的页的物理位置也相邻，这样进行范围查询的时候才可以使用所谓的顺序 I/O。
所以，所以，所以才引入了区（extent）的概念，一个区就是在物理位置上连续的 64 个页。在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区为单位分配，甚至在表中的数据十分非常特别多的时候，可以一次性分配多个连续的区。虽然可能造成一点点空间的浪费（数据不足填充满整个区），但是从性能角度看，可以消除很多的随机 I/O，功大于过嘛！
XDES Entry 链表
到现在为止，我们已经提出了五花八门的概念，什么区、段、碎片区、附属于段的区、XDES Entry 结构吧啦吧啦的概念，走远了千万别忘了自己为什么出发，我们把事情搞这么麻烦的初心仅仅是想提高向表插入数据的效率又不至于数据量少的表浪费空间。现在我们知道向表中插入数据本质上就是向表中各个索引的叶子节点段、非叶子节点段插入数据，也知道了不同的区有不同的状态，再回到最初的起点，捋一捋向某个段中插入数据的过程：
“我们把事情搞这么麻烦的初心仅仅是想提高向表插入数据的效率，又不至于数据量少的表浪费空间”
到现在为止我们已经大概清楚了表空间、段、区、XDES Entry、INODE Entry、各种以 XDES Entry 为节点的链表的基本概念了，可是总有一种飞在天上不踏实的感觉，每个区对应的 XDES Entry 结构到底存储在表空间的什么地方？直属于表空间的 FREE、FREE_FRAG、FULL_FRAG 链表的基节点到底存储在表空间的什么地方？每个段对应的 INODE Entry 结构到底存在表空间的什么地方？我们前边介绍了每 256 个连续的区算是一个组，想解决刚才提出来的这些个疑问还得从每个组开头的一些类型相同的页面说起，接下来我们一个页面一个页面的分析，真相马上就要浮出水面了。
- 每个区对应的 XDES Entry 结构到底存储在表空间的什么地方？
- 直属于表空间的 FREE、FREE_FRAG、FULL_FRAG 链表的基节点到底存储在表空间的什么地方？
- 每个段对应的 INODE Entry 结构到底存在表空间的什么地方？
了解完了独立表空间的基本结构，系统表空间的结构也就好理解多了，系统表空间的结构和独立表空间基本类似，只不过由于整个 MySQL 进程只有一个系统表空间，在系统表空间中会额外记录一些有关整个系统信息的页面，所以会比独立表空间多出一些记录这些信息的页面。因为这个系统表空间最牛逼，相当于是表空间之首，所以它的表空间 ID（Space ID）是 0。
可以看到，系统表空间和独立表空间的前三个页面（页号分别为 0、1、2，类型分别是 FSP_HDR、IBUF_BITMAP、INODE）的类型是一致的，只是页号为 3 ～ 7 的页面是系统表空间特有的，我们来看一下这些多出来的页面都是干啥使的：
系统表空间和独立表空间的前三个 page 的 type 相同（分别是 FSP_HDR, IBUF_BITMAP, INODE），后面 5 个不同
除了这几个记录系统属性的页面之外，系统表空间的 extent 1 和 extent 2 这两个区，也就是页号从 64~191 这 128 个页面被称为 Doublewrite buffer，也就是双写缓冲区。不过上述的大部分知识都涉及到了事务和多版本控制的问题，这些问题我们会放在后边的章节集中唠叨，现在讲述太影响用户体验，所以现在我们只唠叨一下有关 InnoDB 数据字典的知识，其余的概念在后边再看。
Doublewrite buffer
- mysql innodb 独立表空间 的 table space, extent, segment 分别是啥? 啥关系?
- mysql为什么需要系统表空间
- mysql 系统表空间和独立表空间 有啥区别
6、快速查询的秘籍 —— B+ 树索引
6、快速查询的秘籍 —— B+ 树索引 - 《MySQL 是怎样运行的：从根儿上理解 MySQL》 · IT 书架 分别介绍了没有使用索引和（Innodb 中）使用索引，这两种情况下各自的查找过程，
前边我们详细唠叨了 InnoDB 数据页的 7 个组成部分，知道了各个数据页可以组成一个双向链表，而每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表，每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录（如果你对这段话有一丁点儿疑惑，那么接下来的部分不适合你，返回去看一下数据页结构吧）。页和记录的关系示意图如下：
7、好东西也得先学会怎么用-B+树索引的使用
bptree index 适用情况 和 失效情况 正反都要说
我们前面详细、详细又详细的介绍了 InnoDB 存储引擎的 B+树索引，我们必须熟悉下面这些结论：
每个索引都对应一棵 B+树，B+树分为好多层，最下面一层是叶子节点，其余的是内节点。所有用户记录都存储在 B+树的叶子节点，所有目录项记录都存储在内节点。
InnoDB 存储引擎会自动为主键（如果没有它会自动帮我们添加）建立聚簇索引，聚簇索引的叶子节点包含完整的用户记录。
我们可以为自己感兴趣的列建立二级索引，二级索引的叶子节点包含的用户记录由索引列 + 主键组成，所以如果想通过二级索引来查找完整的用户记录的话，需要通过回表操作，也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的用户记录。
B+树中每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表，而且每个页内的记录（不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单链表。如果是联合索引的话，则页面和记录先按照联合索引前面的列排序，如果该列值相同，再按照联合索引后边的列排序。
通过索引查找记录是从 B+树的根节点开始，一层一层向下搜索。由于每个页面都按照索引列的值建立了 Page Directory（页目录），所以在这些页面中的查找非常快。
几个关键字，内节点 叶子结点 双向链表 page directory
再加上 innodb page 结构和 记录结构
节点之间双向链表，节点内部单向链表
这怎么实现范围查询呢
单向链表 倒序查询 怎么搞
为啥不是双向
时间上的代价
每次对表中的数据进行增、删、改操作时，都需要去修改各个 B+树索引。而且我们讲过，B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收什么的操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的 B+树都要进行相关的维护操作，这还能不给性能拖后腿么？
可以认为 内节点 是对叶子节点的一层冗余吗
为了方便大家理解，我们特意标明了哪些是内节点，哪些是叶子节点。再次强调一下，内节点中存储的是目录项记录，叶子节点中存储的是用户记录（由于不是聚簇索引，所以用户记录是不完整的，缺少 country 列的值）。从图中可以看出，这个 idx_name_birthday_phone_number 索引对应的 B+树中页面和记录的排序方式就是这样的：
先按照 name 列的值进行排序。
如果 name 列的值相同，则按照 birthday 列的值进行排序。
如果 birthday 列的值也相同，则按照 phone_number 的值进行排序。
这个排序方式十分、特别、非常、巨、very very very 重要，因为只要页面和记录是排好序的，我们就可以通过二分法来快速定位查找。下面的内容都仰仗这个图了，大家对照着图理解。
索引记录是有排序的
- "***Index 创建原则?***"
- "***使用 mysql 索引时，可能会导致索引失效的错误用法?***"
MySQL 索引失效的常见场景 - 随心的风 - 博客园 where 使用 mysql 内置函数、负向查询、or（需要注意的是，实际上指的是 where 使用内置函数会导致索引失效，在 select 子句中使用还会走索引。需要注意的是隐式转换和列运算也可归入此类）、最左原则（包括“like 通配符”也可归入此类）
10、条条大路通罗马 —— 单表访问方法
10、条条大路通罗马 —— 单表访问方法 - 《MySQL 是怎样运行的：从根儿上理解 MySQL》 · IT 书架 这章其实就是 explain 的几种 type，也就是几种常用的单表 sql 语句，通常会使用哪种索引 type。之后的“注意事项”，用来说明几种索引失效的情况
设计 MySQL 的大叔把查询的执行方式大致分为下边两种：
使用全表扫描进行查询 （这种执行方式很好理解，就是把表的每一行记录都扫一遍嘛，把符合搜索条件的记录加入到结果集就完了。不管是啥查询都可以使用这种方式执行，当然，这种也是最笨的执行方式。）
使用索引进行查询 （因为直接使用全表扫描的方式执行查询要遍历好多记录，所以代价可能太大了。如果查询语句中的搜索条件可以使用到某个索引，那直接使用索引来执行查询可能会加快查询执行的时间。使用索引来执行查询的方式五花八门，又可以细分为许多种类：）
针对主键或唯一二级索引的等值查询
针对普通二级索引的等值查询
针对索引列的范围查询
直接扫描整个索引
- mysql访问类型？或者说，explain的几种type（const, ref, ref_or_null, range, index, all）分别是啥意思?
- 为什么不同sql会走不同type，以及执行这些type时各自的具体执行流程？
- mysql 索引 失效，有哪些常见场景?
11、连接的原理
11、两个表的亲密接触 —— 连接的原理 - 《MySQL 是怎样运行的：从根儿上理解 MySQL》 · IT 书架 前半部分 join 使用很简单，主要看后半部分的原理。
简单来说，就是先在驱动表（主表）中查到数据集，再根据被驱动表（副表）的 where 在这个数据集里查数据。在此基础上，就有了内连接和外连接的区别。内连接就是 主表的记录在副表中找不到匹配记录，该记录不会加入到最终的结果集。外连接则相反。
Nested-Loop Join
基于块的嵌套循环连接（Block Nested-Loop Join）
MySQL 中的 Join 的算法（NLJ、BNL、BKA） - Booksea - 博客园
MySQL 优化器特性（四）表关联之 BNL（Block Nested Loop）和 Hash Join - 技术文章 - 云掣
- join的本质是啥？
- optimizer对join的优化（NLJ, BNL, BKA）各自的工作机制？
12、谁最便宜就选谁 —— MySQL 基于成本的优化
12、谁最便宜就选谁 —— MySQL 基于成本的优化 - 《MySQL 是怎样运行的：从根儿上理解 MySQL》 · IT 书架
13、兵马未动，粮草先行 —— InnoDB 统计数据是如何收集的
13、兵马未动，粮草先行 —— InnoDB 统计数据是如何收集的 - 《MySQL 是怎样运行的：从根儿上理解 MySQL》 · IT 书架 这章比较简单，innodb 的数据肯定是部分做了持久化，部分暂存在内存。这个不用说。这章就是相关的一些具体设置。
14、不好看就要多整容 —— MySQL 基于规则的优化
14、不好看就要多整容 —— MySQL 基于规则的优化（内含关于子查询优化二三事儿） - 《MySQL 是怎样运行的：从根儿上理解 MySQL》 · IT 书架 这章就回答了“mysql 的 optimizer 有哪些优化方法（有哪些“查询重写”优化方法）”
前面“条件化简”这部分其实就是 一些常用的编译器优化操作，常量传播 cp、常量折叠 cf、死码消除、CSE、BCE、LICM
外连接消除
内连接的主表和副表可以互相转换，据此 optimizer 就会自动进行优化。而外连接则无法转换，所以无法进行自动优化。外连接消除就是 optimizer 自动把（可以转换为内连接的）外连接转内连接。文章举了一个 reject-NULL 的例子。
- 子查询在 MySQL 中是怎么执行的
ICP and index dive, (MRR, BKA, BNL)
ICP索引条件下推(Index Condition Pushdown)用索引筛选的 where 条件在存储引擎一侧进行筛选，而不是将所有 index access 的结果都放在 server 端进行 where 筛选MRR索引多范围查找(Multi-Range Read)优化器将随机 io 转化为顺序 io，以降低查询过程中 io 开销的一种手段BKA(Batched Key Access)，，在表连接过程中为了提升 join 性能而使用的 join buffer，其作用是在读取被 join 表的记录时，使用顺序 io(BKA 被使用的标识是执行计划的 extra 信息中会有 BKA 信息)。BKA 优化技术在 join 中使用，特别是在执行连接操作时，如果一个表上的索引已经排序，而另一个表上的连接列也是有序的，那么 MySQL 可以使用 BKA 来批量访问这两个表。它允许优化器批量访问索引，减少索引访问的次数，从而提高 join 的性能。BNL(Block Nested Loop)BNL 优化技术也是在 join 中使用的。当 join 无法使用更高级的连接算法（如索引连接或哈希连接）时，MySQL 会使用 BNL。BNL 通过逐行读取两个表的数据，并执行循环匹配操作，以获取满足连接条件的结果。虽然 BNL 可能会导致性能较低，但在某些情况下，它仍然是执行 join 的一种有效方法。
ICP 的原理很简单。当 MySQL 执行查询语句时，它会根据查询条件选择合适的索引来访问数据。在传统的执行方式中，MySQL 首先通过索引找到满足条件的记录的主键，然后再根据主键值从数据表中获取记录。而索引条件下推则在索引访问阶段就进行条件过滤，只返回满足查询条件的记录的主键，避免了不必要的主键查找操作。
这样做的好处是减少了磁盘 IO 和网络传输，提高了查询效率。特别是对于那些返回记录较多但满足查询条件的记录较少的查询，索引条件下推可以大幅提升性能。
这些概念太抽象了，简单来说就是“ICP 就是把本应 executor 执行的，推给 SE 来执行”
仍然用图书馆类比的话，ICP 相当于在图书检索系统操作员（executor）正式检索之前，由图书馆工作人员（存储引擎）利用索引目录（索引）进行的预筛选，这样可以减少操作员需要检查的书籍数量，提高整体检索效率。
BKA 和 BNL 都是在 join 操作时才会使用的优化技术
- ICP（Index Condition Pushdown）：ICP 在优化器中是一项重要的优化技术，它类似于图书馆的索引目录。在图书馆中，索引目录帮助读者快速找到所需书籍的位置。类比到 MySQL 中，ICP 会尽可能将查询条件下推至存储引擎层级，以减少不必要的数据读取，提高查询效率。
- MRR（Multi-Range Read）：MRR 是一种批量读取技术，类似于在图书馆中一次性获取多本书。MRR 允许 MySQL 在存储引擎层级批量读取满足查询条件的多个数据块，以减少磁盘 I/O 操作，提高查询性能。
- BKA（Batch Key Access）：BKA 是一种批量键访问技术，类似于在图书馆中通过预定书架号快速获取多本书。在 MySQL 中，BKA 允许优化器批量访问索引，以减少索引访问的次数，提高查询速度。
- BNL（Block Nested Loop）：BNL 是一种块嵌套循环技术，类似于在图书馆中通过遍历多个书架来查找所需书籍。在 MySQL 中，BNL 用于处理连接操作，它会逐行读取两个表的数据，并执行循环匹配操作，以获取符合连接条件的结果。
- Index Dive：Index Dive 类似于在图书馆中通过索引快速定位到需要的书籍的过程。在 MySQL 中，Index Dive 是一种优化技术，它允许优化器通过索引的跳跃访问，直接定位到满足查询条件的数据，而无需完全扫描索引。
ICP 就是“把查询条件放到存储引擎，能提高查询效率”，通俗来说就是，在查询过程中，直接在查询引擎层的 API 获取数据时，实现“非直接索引”过滤条件的筛选，而不是查询引擎层查询出来之后在 server 层筛选。
再则，ICP 更适合那种大量数据，但是其中只有极少数满足查询条件的场景。
MRR 是一种针对读取操作的优化技术。它通过在一个请求中读取多个连续范围的数据，以减少读取磁盘的次数。MRR 通常用于处理顺序扫描（Sequential Scan）或范围查询（Range Query）等需要从磁盘读取大量连续数据的场景。通过减少磁盘 IO 次数，MRR 可以显著提高读取操作的性能。
MRR 主要关注的是优化读取操作的效率
这些优化器技术都旨在提高查询性能，减少不必要的磁盘 I/O 和数据扫描
15、Explain
15、查询优化的百科全书 —— Explain 详解（上） - 《MySQL 是怎样运行的：从根儿上理解 MySQL》 · IT 书架
16、查询优化的百科全书 —— Explain 详解（下） - 《MySQL 是怎样运行的：从根儿上理解 MySQL》 · IT 书架
extra 提供了一些额外信息
No tables used
Impossible WHERE
No matching min/max row
Using index
Using index condition
Using where
Using join buffer (Block Nested Loop)
Not exists
Using intersect(...)、Using union(...)和 Using sort_union(...)
Zero limit
Using filesort
Using temporary
Start temporary, End temporary
LooseScan
FirstMatch(tbl_name)
EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 WHERE s1.common_field = 'a'\G
用 EXPLAIN FORMAT=JSON 给 explain 加上估算执行 cost
通过结果集中的 cost_info 查看
- mysql explain语句的 type
- mysql explain语句的 extra
- explain format=json 怎么用
17、神兵利器 —— optimizer trace 的神奇功效
17、神兵利器 —— optimizer trace 的神奇功效 - 《MySQL 是怎样运行的：从根儿上理解 MySQL》 · IT 书架
对于 MySQL 5.6 以及之前的版本来说，查询优化器就像是一个黑盒子一样，你只能通过 EXPLAIN 语句查看到最后优化器决定使用的执行计划，却无法知道它为什么做这个决策。这对于一部分喜欢刨根问底的小伙伴来说简直是灾难：“我就觉得使用其他的执行方案比 EXPLAIN 输出的这种方案强，凭什么优化器做的决定和我想的不一样呢？”
在 MySQL 5.6 以及之后的版本中，设计 MySQL 的大叔贴心的为这部分小伙伴提出了一个 optimizer trace 的功能，
optimizer trace 用来展示具体的决策过程（也就是为啥会选择这样的“执行计划”），换句话就是用来解释为啥 explain 会决定使用这个“执行计划”，而不是其他的
后面就是讲了下具体怎么使用 optimizer-trace
不过杂乱之中其实还是蛮有规律的，优化过程大致分为了三个阶段：
prepare 阶段
optimize 阶段
execute 阶段
我们所说的基于成本的优化主要集中在 optimize 阶段，对于单表查询来说，我们主要关注 optimize 阶段的"rows_estimation"这个过程，这个过程深入分析了对单表查询的各种执行方案的成本；对于多表连接查询来说，我们更多需要关注"considered_execution_plans"这个过程，这个过程里会写明各种不同的连接方式所对应的成本。反正优化器最终会选择成本最低的那种方案来作为最终的执行计划，也就是我们使用 EXPLAIN 语句所展现出的那种方案。
如果有小伙伴对使用 EXPLAIN 语句展示出的对某个查询的执行计划很不理解，大家可以尝试使用 optimizer trace 功能来详细了解每一种执行方案对应的成本，相信这个功能能让大家更深入的了解 MySQL 查询优化器。
确实如此，查询计划由 optimizer 提供（也就是基于预估 cost 的），而执行详细信息的收集则是由 executo 提供。
- 【optimizer-trace】怎么用optimizer-trace来优化sql？ 如何分析trace来改善查询的执行效率？
- optimizer-trace 和 explain-analyzer 有啥区别? 各自用来针对sql优化的哪个方面? # ***查询计划的解释是由optimizer提供的，而执行详细信息的收集是由executor提供的。*** 这点在mysql和postgres里是一样的。*也就是说几个mysql常用的优化命令，optimizer-trace是optimizer提供的，explain analyze 是 executor提供的，show status 则是一个数据汇总的命令，*
18、调节磁盘和 CPU 的矛盾 —— InnoDB 的 Buffer Pool
18、调节磁盘和 CPU 的矛盾 —— InnoDB 的 Buffer Pool - 《MySQL 是怎样运行的：从根儿上理解 MySQL》 · IT 书架 这章涉及到
通过前边的唠叨我们知道，对于使用 InnoDB 作为存储引擎的表来说，不管是用于存储用户数据的索引（包括聚簇索引和二级索引），还是各种系统数据，都是以页的形式存放在表空间中的，而所谓的表空间只不过是 InnoDB 对文件系统上一个或几个实际文件的抽象，也就是说我们的数据说到底还是存储在磁盘上的。但是各位也都知道，磁盘的速度慢的跟乌龟一样，怎么能配得上“快如风，疾如电”的 CPU 呢？所以 InnoDB 存储引擎在处理客户端的请求时，当需要访问某个页的数据时，就会把完整的页的数据全部加载到内存中，也就是说即使我们只需要访问一个页的一条记录，那也需要先把整个页的数据加载到内存中。将整个页加载到内存中后就可以进行读写访问了，在进行完读写访问之后并不着急把该页对应的内存空间释放掉，而是将其缓存起来，这样将来有请求再次访问该页面时，就可以省去磁盘 IO 的开销了。
buffer pool 本质上是 innodb 向 os 申请的一段连续内存空间。这段连续内存由控制块和缓存页组成。每个控制块和缓存页都是一一对应的，在填充足够多的控制块和缓存页的组合后，Buffer Pool 剩余的空间可能产生不够填充一组控制块和缓存页，这部分空间不能被使用，也被称为碎片。
1、Buffer Pool 本质上是 InnoDB 向操作系统申请的一段连续的内存空间，可以通过 innodb_buffer_pool_size 来调整它的大小。
2、Buffer Pool 向操作系统申请的连续内存由控制块和缓存页组成，每个控制块和缓存页都是一一对应的，在填充足够多的控制块和缓存页的组合后，Buffer Pool 剩余的空间可能产生不够填充一组控制块和缓存页，这部分空间不能被使用，也被称为碎片。
3、InnoDB 使用了许多链表来管理 Buffer Pool。
4、free 链表中每一个节点都代表一个空闲的缓存页，在将磁盘中的页加载到 Buffer Pool 时，会从 free 链表中寻找空闲的缓存页。
5、为了快速定位某个页是否被加载到 Buffer Pool，使用表空间号 + 页号作为 key，缓存页作为 value，建立哈希表。
6、在 Buffer Pool 中被修改的页称为脏页，脏页并不是立即刷新，而是被加入到 flush 链表中，待之后的某个时刻同步到磁盘上。
7、LRU 链表分为 young 和 old 两个区域，可以通过 innodb_old_blocks_pct 来调节 old 区域所占的比例。首次从磁盘上加载到 Buffer Pool 的页会被放到 old 区域的头部，在 innodb_old_blocks_time 间隔时间内访问该页不会把它移动到 young 区域头部。在 Buffer Pool 没有可用的空闲缓存页时，会首先淘汰掉 old 区域的一些页。
8、我们可以通过指定 innodb_buffer_pool_instances 来控制 Buffer Pool 实例的个数，每个 Buffer Pool 实例中都有各自独立的链表，互不干扰。
9、自 MySQL 5.7.5 版本之后，可以在服务器运行过程中调整 Buffer Pool 大小。每个 Buffer Pool 实例由若干个 chunk 组成，每个 chunk 的大小可以在服务器启动时通过启动参数调整。
#- topic: InnoDB 缓冲池(buffer-pool)
- 什么是缓冲池 buffer-pool? # 避免每次读操作都进行磁盘 IO，具体来说，缓冲池缓存了大量数据页，让 CPU 读取和写入数据时，直接和缓冲区交互，不需要操作磁盘，从而避免磁盘拖慢数据库性能的问题（*注意缓冲池是 innoDB 引擎的特性，而不是 mysql 的*）
- 缓冲池存哪些数据？ # 缓存表数据与索引数据，把磁盘上的数据加载到缓冲池，避免每次访问都进行磁盘 IO，起到加速访问的作用。
- 缓冲池的工作机制？
- 缓存页的哈希处理
- flush链表的管理
- LRU链表的管理
脏页刷新
当然，以下是 InnoDB 存储引擎中脏页刷新（Page Flush）过程的概述，涵盖了所有关键点：
1. **事务修改页**：当事务对表中的数据进行修改时，这些修改首先在内存中的页（buffer pool 中的页）上进行。一旦页被修改，它就变成一个脏页。
2. **重做日志记录**：在事务提交之前，InnoDB 会将修改记录到重做日志（redo log）中。这是为了确保在发生故障时可以使用这些日志来恢复数据。
3. **事务提交**：事务提交后，修改的页仍然是脏页，因为它们还没有被写回到磁盘上。
4. **脏页刷新触发**：InnoDB 有几种机制来触发脏页的刷新：
- **后台刷新**：后台线程（Page Cleaner）定期刷新脏页。
- **Checkpoint 触发**：当重做日志的头部（head）和尾部（tail）之间的距离（checkpoint age）达到一定阈值时，会触发刷新。
- **缓冲池压力**：当缓冲池中脏页的比例过高或需要为新的读操作腾出空间时，会触发脏页刷新。
5. **刷新邻接页**：如果启用了刷新邻接页（Flush Neighbor Page）功能，InnoDB 在刷新一个脏页时，会检查同一区（extent）内的所有页，如果这些页也是脏的，它们也会被一起刷新。
6. **自适应刷新**：InnoDB 的自适应刷新算法根据当前的写入负载和磁盘性能动态调整刷新速率，以避免刷新风暴（flush storm）。
7. **刷新操作**：脏页被写回到磁盘上，这一过程可能涉及异步 IO 操作，以提高效率。
8. **刷新完成**：一旦脏页被成功写回到磁盘，它们就不再是脏页。InnoDB 会更新重做日志的尾部位置，减少 checkpoint age。
9. **日志文件管理**：随着脏页的刷新，之前记录的重做日志可以被覆盖，以便日志文件可以循环使用。
10. **性能监控与调优**：通过监控刷新操作的性能，如通过`show engine innodb status`命令，DBA 可以对刷新过程进行调优，例如调整`innodb_page_cleaners`、`innodb_adaptive_flushing_lwm`、`innodb_io_capacity`等参数。
11. **刷新争用处理**：在高负载情况下，刷新操作可能会与其他数据库操作（如查询和索引构建）竞争资源，需要适当调整以避免性能瓶颈。
12. **系统状态反馈**：InnoDB 通过日志和状态信息提供刷新操作的反馈，帮助 DBA 了解系统的当前状态并做出相应的调整。
整个刷新过程是 InnoDB 保证数据持久性和系统性能的关键机制之一。通过智能地管理脏页的刷新，InnoDB 可以在保持高性能的同时，确保数据的安全性和完整性。
dirty pages
在整个 InnoDB 的脏页刷新（Flush）过程中，涉及到以下特性：
1. **缓冲池（Buffer Pool）**：InnoDB 使用缓冲池来缓存频繁访问的数据和索引页，减少对磁盘的 I/O 操作。
2. **脏页（Dirty Pages）**：在缓冲池中被修改但尚未写入磁盘的页。
3. **重做日志（Redo Log）**：事务提交前，修改操作首先记录到重做日志中，确保数据恢复的一致性。
4. **LSN（Log Sequence Number）**：用于记录重做日志的位置，每个事务都会生成一个新的 LSN。
5. **Checkpoint**：刷新过程中，InnoDB 会更新磁盘上数据页的状态，Checkpoint 机制用于标记哪些数据已经刷新到磁盘。
6. **刷新邻接页（Flush Neighbor Page）**：当刷新一个脏页时，如果该页所在的区（extent）内还有其他脏页，它们也会被一起刷新。
7. **自适应刷新（Adaptive Flushing）**：根据当前的系统负载和性能指标，动态调整刷新速率。
8. **异步 IO（Asynchronous IO）**：InnoDB 支持异步 IO 操作，允许脏页刷新操作与其他数据库操作并发执行。
9. **刷新线程（Page Cleaner Threads）**：InnoDB 使用专门的后台线程来刷新脏页。
10. **刷新控制参数**：
- `innodb_page_cleaners`：控制刷新脏页的线程数。
- `innodb_purge_threads`：控制清除操作的线程数。
- `innodb_io_capacity`：定义 InnoDB 刷新磁盘时的 IO 容量。
- `innodb_adaptive_flushing`：控制是否启用自适应刷新算法。
- `innodb_adaptive_flushing_lwm`：自适应刷新的最低水位参数。
- `innodb_flush_neighbors`：控制是否启用刷新邻接页特性。
11. **刷新争用处理**：在高负载情况下，InnoDB 需要处理刷新操作与其他数据库操作之间的资源争用。
12. **性能监控**：通过`show engine innodb status`等命令监控刷新操作的性能。
13. **刷新风暴（Flush Storm）**：当重做日志的空间不足以容纳新的事务提交时，InnoDB 会高速刷新脏页以释放空间，这可能导致性能问题。
14. **日志文件管理**：刷新过程中，InnoDB 需要管理重做日志文件，确保日志文件的循环使用。
15. **系统状态反馈**：InnoDB 通过日志和状态信息提供刷新操作的反馈，帮助数据库管理员了解系统的当前状态。
这些特性共同工作，确保 InnoDB 存储引擎能够有效地管理脏页刷新，同时保持高性能和数据的持久性。
InnoDB 的 redolog 就是 WAL 嘛，必然会产生脏页
为啥会出现脏页? page flush 的时机? 刷新策略?
能否给我概述一下整个 page flush 的过程？整个 flush 过程中涉及到哪些 innodb 的特性？
flush page 就是把内存中修改过的页（脏页）写回到磁盘上，以确保数据的持久性和一致性。
之所以产生脏页就是因为 InnoDB 用 WAL 来防止宕机数据丢失，也就是事务提交时，先写 redolog，再修改内存数据页，这样就产生了脏页。
那为啥要刷新呢？因为不可能把所有 buffer pool 所以需要把 buffer pool 里的脏页都刷新到磁盘里
InnoDB 通过 LSN（每条 log 的结束点，用字节偏移量来表示。每个 page 有 LSN，redo log 也有 LSN，Checkpoint 也有 LSN）来标记版本
Checkpoint 机制每次刷新多少页，从哪里取脏页，什么时间触发刷新？
- topic: InnoDB 预读 (read-ahead)
qs:
- q: 什么是预读？
x: 磁盘按页读取，如果要读取的数据就在页中，就能节省后面的磁盘 io，提高效率。数据访问遵循`集中读写`的原则，使用一些数据，大概率会使用附近的数据，这就是`局部性原理`
- q: 什么是预读失败？
- q: 如何对预读失败进行优化？
- q: 什么是缓冲池污染？
- q: 怎么解决缓冲池污染的问题？
# 预读请求是指预取缓冲池中的多个页面的异步I/O请求，以预测这些页面即将出现的需求。请求在一个区段中引入所有页面。
19、从猫爷被杀说起 —— 事务简介
19、从猫爷被杀说起 —— 事务简介 - 《MySQL 是怎样运行的：从根儿上理解 MySQL》 · IT 书架
20、redo 日志
redo 日志是什么
我们知道 InnoDB 存储引擎是以页为单位来管理存储空间的，我们进行的增删改查操作其实本质上都是在访问页面（包括读页面、写页面、创建新页面等操作）。我们前面介绍 Buffer Pool 的时候说过，在真正访问页面之前，需要把在磁盘上的页缓存到内存中的 Buffer Pool 之后才可以访问。但是在介绍事务的时候又强调过一个称之为持久性的特性，就是说对于一个已经提交的事务，在事务提交后即使系统发生了崩溃，这个事务对数据库中所做的更改也不能丢失。但是如果我们只在内存的 Buffer Pool 中修改了页面，假设在事务提交后突然发生了某个故障，导致内存中的数据都失效了，那么这个已经提交了的事务对数据库中所做的更改也就跟着丢失了，这是我们所不能忍受的（想想 ATM 机已经提示狗哥转账成功，但之后由于服务器出现故障，重启之后猫爷发现自己没收到钱，猫爷就被砍死了）。那么如何保证这个持久性呢？一个很简单的做法就是在事务提交完成之前把该事务所修改的所有页面都刷新到磁盘，但是这个简单粗暴的做法有些问题：
咋办呢？再次回到我们的初心：我们只是想让已经提交了的事务对数据库中数据所做的修改永久生效，即使后来系统崩溃，在重启后也能把这种修改恢复出来。所以我们其实没有必要在每次事务提交时就把该事务在内存中修改过的全部页面刷新到磁盘，只需要把修改了哪些东西记录一下就好，比方说某个事务将系统表空间中的第 100 号页面中偏移量为 1000 处的那个字节的值 1 改成 2 我们只需要记录一下：
只记录操作，不记录被修改后的数据，因为成本太高
这句话可以从以下几个关键点来理解 InnoDB 的 redo log 以及它如何实现 WAL（Write-Ahead Logging）策略：
1. **WAL 策略**：WAL 是一种常见的数据库日志技术，核心思想是在数据实际写入磁盘之前，先将对数据的修改操作记录到日志中。这样做可以提高性能，因为写日志通常比直接写磁盘要快，并且可以减少对磁盘的 I/O 操作。
2. **事务的持久性**：事务的持久性是 ACID 原则中的"D"，指的是一旦事务提交，它对数据库的改变就是永久性的，即使系统发生故障也不会丢失。InnoDB 通过 redo log 来实现这一点：当事务提交时，相关的修改操作已经记录在 redo log 中，如果系统发生故障，InnoDB 可以重放 redo log 中的操作，从而恢复未持久化到磁盘的数据。
3. **先记录日志再写入数据**：这是 WAL 策略的具体实现。在 InnoDB 中，当事务需要修改数据时，首先会在 redo log 中记录这次修改的详细信息，然后再将修改后的页（数据）写入内存中的缓冲池。这样做可以确保即使在数据页写入磁盘之前系统崩溃，通过 redo log 也能够恢复数据。
4. **Checkpoint 技术**：Checkpoint 是 InnoDB 中用于管理 redo log 空间和优化恢复时间的一种机制。它在 redo log 中标记了一个点，表示在这个点之前的所有事务都已经将数据持久化到磁盘。这样，当系统需要恢复时，只需要重放 Checkpoint 之后的部分日志，而不是整个日志文件，从而大大减少了恢复所需的时间。
5. **有效管理日志空间**：由于 redo log 是循环使用的，Checkpoint 还有助于释放不再需要的日志空间。一旦数据页被刷新到磁盘并且对应的事务已经提交，从 Checkpoint 点之前的日志就可以被清除，为新的事务日志腾出空间。
综上所述，InnoDB 的 redo log 通过 WAL 策略确保了事务的持久性，并通过 Checkpoint 技术有效管理了日志空间和恢复时间，从而提供了高性能和数据安全性。
- 什么是inndob的redolog? 是怎么通过redolog实现WAL的?
22、undo 日志
undo 日志具体写入过程
段（Segment）的概念
如果你有认真看过表空间那一章的话，对这个段的概念应该印象深刻，我们当时花了非常大的篇幅来介绍这个概念。简单讲，这个段是一个逻辑上的概念，本质上是由若干个零散页面和若干个完整的区组成的。比如一个 B+树索引被划分成两个段，一个叶子节点段，一个非叶子节点段，这样叶子节点就可以被尽可能的存到一起，非叶子节点被尽可能的存到一起。每一个段对应一个 INODE Entry 结构，这个 INODE Entry 结构描述了这个段的各种信息，比如段的 ID，段内的各种链表基节点，零散页面的页号有哪些等信息（具体该结构中每个属性的意思大家可以到表空间那一章里再次重温一下）。我们前面也说过，为了定位一个 INODE Entry，设计 InnoDB 的大佬设计了一个 Segment Header 的结构：
知道了表空间 ID、页号、页内偏移量，不就可以唯一定位一个 INODE Entry 的地址了么～
undolog page 链表是哪来的 干啥的
24、一条记录的多幅面孔 —— 事务的隔离级别与 MVCC
24、一条记录的多幅面孔 —— 事务的隔离级别与 MVCC - 《MySQL 是怎样运行的：从根儿上理解 MySQL》 · IT 书架
25、工作面试老大难 —— 锁
25、工作面试老大难 —— 锁 - 《MySQL 是怎样运行的：从根儿上理解 MySQL》 · IT 书架
《...》专题式讲解
30、设计 MySQL 的大叔为何偏爱 ref
30、专题式讲解 —— 设计 MySQL 的大叔为何偏爱 ref - 《MySQL 是怎样运行的：从根儿上理解 MySQL》 · IT 书架
MySQL 的内部实现
MySQL 优化器在计算回表的成本时，在使用二级索引执行查询并且需要回表的情境下，对于 ref 和 range 是很明显的区别对待的：
对于 range 来说，需要扫描多少条二级索引记录，就相当于需要访问多少个页面。每访问一个页面，回表的 I/O 成本就加 1。
比方对于查询 2 来说，需要回表的记录数是 2310，因为回表操作而计算的 I/O 成本就是 2310。
对于 ref 来说，回表开销带来的 I/O 成本存在天花板，也就是定义了一个上限值：
double worst_seeks;
这个上限值的取值是从下边两个值中取较小的那个：
- 全表记录数的十分之一（此处的全表记录数属于统计数据，是一个估计值）
- 聚簇索引所占页面的 3 倍
比方对于查询 1 来说，回表的记录数是 2310，按理说计算因回表操作带来的 I/O 成本也应该是 2310。但是由于对于 ref 访问方法，计算回表操作时带来的 I/O 成本时存在天花板，会从全表记录的十分之一（也就是 9912/10=991，9912 为估计值）以及聚簇索引所占页面的 3 倍（本例中聚簇索引占用的页面数就是 97，乘以 3 就是 291）选择更小的那个，本例中也就是 291。
小贴士：
在成本分析的代码中，range 和 index、all 是被分到一类里的，ref 是亲儿子，单独分析了一波。不过我们也可以看到，设计 MySQL 的大叔在计算 range 访问方法的代价时，直接认为每次回表都需要进行一次页面 I/O，这是十分粗暴的，何况我们的实际聚簇索引总共才 97 个页面，它却将回表成本计算为 2310，这也是很不精确的。当然，由于目前的算法无法预测哪些页面在内存中，哪些不在，所以也就将就将就用吧～
47、redo、undo、buffer pool、binlog，谁先谁后，有点儿乱
47、redo、undo、buffer pool、binlog，谁先谁后，有点儿乱 - 《MySQL 是怎样运行的：从根儿上理解 MySQL》 · IT 书架
我们讨论的是基于 InnoDB 存储引擎的表，数据会被保存在硬盘上的表空间（文件系统中的一个或多个文件）中。
InnoDB 会将磁盘中的数据看成是若干个页的集合，页的大小默认是 16KB。其中某些页面用于存储关于系统的一些属性，某些页面用于存储 undo 日志，某些页面用于存储 B+树的节点（也就是包含记录的页面），反正总共有十来种不同类型的页面。
不过不论是什么类型的页面，每当我们从页面中读取或写入数据时，都必须先将其从硬盘上加载到内存中的 buffer pool 中（也就是说内存中的页面其实就是硬盘中页面的一个副本），然后才能对内存中页面进行读取或写入。如果要修改内存中的页面，为了减少磁盘 I/O，修改后的页面并不立即同步到磁盘，而是作为脏页继续呆在内存中，等待后续合适时机将其刷新到硬盘（一般是有后台线程异步刷新）。
48、XA 事务与两阶段提交
48、XA 事务与两阶段提交 - 《MySQL 是怎样运行的：从根儿上理解 MySQL》 · IT 书架
跨行转账是一个典型的分布式事务的实例。各个银行都有自己的服务，如果狗哥在招商银行存了 10 块钱，他想把这 10 块钱转给猫爷在建设银行的账户，那么招商银行先得给狗哥账户扣 10 块，然后建设银行给猫爷账户增 10 块。而招商银行和建设银行根本就不是一个系统，招商银行给狗哥扣钱的业务放到了自己的一个事务里，建设银行给猫爷加钱的业务放到了自己的一个事务里，这两个事务其实在各自的系统中并没有什么关系，完全有可能招商银行的事务提交了，而建设银行的事务由于系统宕机而失败了，这就导致狗哥扣了钱，却没有转给猫爷的惨剧发生。所以我们必须引入一些机制，来实现分布式事务。
《MySQL 45 讲》读书笔记
00 开篇词 这一次，让我们一起来搞懂 MySQL.md
01 基础架构：一条 SQL 查询语句是如何执行的？.md
02 日志系统：一条 SQL 更新语句是如何执行的？.md
03 事务隔离：为什么你改了我还看不见？.md
04 深入浅出索引（上）.md
05 深入浅出索引（下）.md
06 全局锁和表锁 ：给表加个字段怎么有这么多阻碍？.md
07 行锁功过：怎么减少行锁对性能的影响？.md
08 事务到底是隔离的还是不隔离的？.md
09 普通索引和唯一索引，应该怎么选择？.md
10 MySQL 为什么有时候会选错索引？.md
11 怎么给字符串字段加索引？.md
12 为什么我的 MySQL 会“抖”一下？.md
13 为什么表数据删掉一半，表文件大小不变？.md
14 count()这么慢，我该怎么办？.md
15 答疑文章（一）：日志和索引相关问题.md
16 “order by”是怎么工作的？.md
17 如何正确地显示随机消息？.md
18 为什么这些 SQL 语句逻辑相同，性能却差异巨大？.md
19 为什么我只查一行的语句，也执行这么慢？.md
20 幻读是什么，幻读有什么问题？.md
21 为什么我只改一行的语句，锁这么多？.md
22 MySQL 有哪些“饮鸩止渴”提高性能的方法？.md
23 MySQL 是怎么保证数据不丢的？.md
24 MySQL 是怎么保证主备一致的？.md
25 MySQL 是怎么保证高可用的？.md
26 备库为什么会延迟好几个小时？.md
27 主库出问题了，从库怎么办？.md
28 读写分离有哪些坑？.md
29 如何判断一个数据库是不是出问题了？.md
30 答疑文章（二）：用动态的观点看加锁.md
31 误删数据后除了跑路，还能怎么办？.md
32 为什么还有 kill 不掉的语句？.md
33 我查这么多数据，会不会把数据库内存打爆？.md
34 到底可不可以使用 join？.md
35 join 语句怎么优化？.md
36 为什么临时表可以重名？.md
37 什么时候会使用内部临时表？.md
38 都说 InnoDB 好，那还要不要使用 Memory 引擎？.md
39 自增主键为什么不是连续的？.md
40 insert 语句的锁为什么这么多？.md
41 怎么最快地复制一张表？.md
42 grant 之后要跟着 flush privileges 吗？.md
43 要不要使用分区表？.md
44 答疑文章（三）：说一说这些好问题.md
45 自增 id 用完怎么办？.md
我的 MySQL 心路历程.md
结束语 点线网面，一起构建 MySQL 知识网络.md
01 基础架构：一条 SQL 查询语句是如何执行的？
从 sql 查询语句的执行，引申出 mysql arch（也就是 cpoe）
如果拿图书馆来类比 mysql 的话，就很好理解了。假设我们的需求是进入图书馆获取 n 本符合我们需要的书。
- connector 就相当于门卫，只有预约（相当于 auth）过才能进入，我们可以通过调整连接数（就是预约人数）来动态调整图书馆的负载。
- parser 就相当于图书馆的前台，是把汉语转化成该书的 ISBD 号等 MACS 专用信息（将查询语句转换为内部数据结构，相当于 compiler），并且提供这些书的位置和路线（提供查询计划）。mysql 的 parser 其实就是 compiler，词法分析、语法分析啥的。
- optimizer 就相当于优化我们去找到这本书的路线（找到最优路线，提高查询效率）。
- executor 则相当于“图书馆操作员”，负责执行优化后的查询计划（也就是带着我们），从图书馆书架（存储引擎）里查找所有需要的数据，这也解释了查询操作关键字的执行顺序，当然要先确定这些书籍的大概区域（也就是 FROM），如果需要把这些书按序排列的话，就最后再执行 Order 这种已经拿到所有书籍之后无关紧要的操作。那中间肯定就是来获取查找这些书了，也就是各种条件（where 和 groupby/having 和 select），当然书肯定不需要重复的（也就是 Distinct 去重操作）。
如果我们把存储引擎比做图书馆的所有书架的话，表、页、行分别类比成什么呢？ 表可以类比成图书馆的某个区域，每个区域存放着特定类型的书籍。页可以看作是该区域的某个书架，每个书架上放着一定数量的书籍。行可以类比成某一排书，每一排上放着一本书。
进一步的，我们可以用这些继续类比 myisam 和 innodb，myisam 就是每个分类（比如工业技术、交通运输、历史地理等等，具体查看《中国图书馆分类法》）都会有一个单独的目录，并且不允许很多人同时查找（不支持并发查找，也就是不支持事务），进行查找时直接锁表，相应的，我们可以直接通过这个单独的 MYI 索引文件查找对应的图书，非常快。
InnoDB 就灵活的多了，他是以书架为单位进行查找和存储的，所以他的目录（索引）是和某排图书在一起的（相当于每排书一个索引（主键索引），索引 id 都贴在书架该层的最前面，这时就存在两种情况，一种就是直接查目录（索引）就可以获得数据，不需要再查数据表，这个就是“索引覆盖”（或者说 Index Dive，都是类似意思）。如果目录中查不到我们需要的数据，就需要“回表”了，那么我们根据索引 id 来看需要的书是不是在这排（再多说一句，这也是为什么主键 id 最好单调递增的原因））。他是支持多人同时查找的。并且在读写操作时只锁定某排书（也就是行级锁定），粒度更细，这样就更能频繁地执行读操作和写操作。
需要注意的是 mysql 还支持同时使用多种存储引擎，也就是说在图书馆里如果有一些冷门分类，很少有新书入库的，那就用 myisam 这种类型的管理方式，如果非常热门的分类，每天都有很多用户来买书，每天书店也需要频繁补货的，就应该用 innodb。当然，如果我们把 mysql 看作是图书馆的话，不同图书馆的特性（规矩、规章制度）也不同，比如说 oracle 或者 sql server 这种的就比较死板，图书馆开店营业之前就定好了所有书架的管理模式（也就是存储引擎），之后再也不能修改，更不存在说不同分类使用不同的管理模式一说了。
另外，还需要注意一个问题，编织目录（索引）的具体方法和该“图书分类”（存储引擎）的映射关系。众所周知，对于不同图书分类也应该使用不同的方式来编写目录。比如说 R 树适用于 myisam（也有 B+Tree），hash 适用于 memory，B+Tree 则适用于 InnoDB。
- "***mysql 的查询语句的具体执行？比如 sql 语句有 select from where orderby，为啥查询语句执行的先后顺序是 FWG(H)SDO (from-where-group by-having-select-order by)？***"
02 日志系统：一条 SQL 更新语句是如何执行的？
分析器会通过词法和语法解析知道这是一条更新语句。优化器决定要使用 ID 这个索引。然后，执行器负责具体执行，找到这一行，然后更新。
还是从 cpoe 入手，parser 经过 lexical analysis 知道是更新语句，executor 负责执行具体的更新操作
与查询流程不一样的是，更新流程还涉及两个重要的日志模块，它们正是我们今天要讨论的主角：redo log（重做日志）和 binlog（归档日志）。
redolog 和 binlog，2PC
因为更新操作涉及到一致性问题（以及分布式下的一致性问题），本质上是 DT 的 2PC 方案
不知道你还记不记得《孔乙己》这篇文章，酒店掌柜有一个粉板，专门用来记录客人的赊账记录。如果赊账的人不多，那么他可以把顾客名和账目写在板上。但如果赊账的人多了，粉板总会有记不下的时候，这个时候掌柜一定还有一个专门记录赊账的账本。
如果有人要赊账或者还账的话，掌柜一般有两种做法：
一种做法是直接把账本翻出来，把这次赊的账加上去或者扣除掉；
另一种做法是先在粉板上记下这次的账，等打烊以后再把账本翻出来核算。
在生意红火柜台很忙时，掌柜一定会选择后者，因为前者操作实在是太麻烦了。首先，你得找到这个人的赊账总额那条记录。你想想，密密麻麻几十页，掌柜要找到那个名字，可能还得带上老花镜慢慢找，找到之后再拿出算盘计算，最后再将结果写回到账本上。
这整个过程想想都麻烦。相比之下，还是先在粉板上记一下方便。你想想，如果掌柜没有粉板的帮助，每次记账都得翻账本，效率是不是低得让人难以忍受？
同样，在 MySQL 里也有这个问题，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。为了解决这个问题，MySQL 的设计者就用了类似酒店掌柜粉板的思路来提升更新效率。
而粉板和账本配合的整个过程，其实就是 MySQL 里经常说到的 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘，也就是先写粉板，等不忙的时候再写账本。
具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（粉板）里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做，这就像打烊以后掌柜做的事。
如果今天赊账的不多，掌柜可以等打烊后再整理。但如果某天赊账的特别多，粉板写满了，又怎么办呢？这个时候掌柜只好放下手中的活儿，把粉板中的一部分赊账记录更新到账本中，然后把这些记录从粉板上擦掉，为记新账腾出空间。
与此类似，InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么这块“粉板”总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示。
write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。
write pos 和 checkpoint 之间的是“粉板”上还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。
有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。
要理解 crash-safe 这个概念，可以想想我们前面赊账记录的例子。只要赊账记录记在了粉板上或写在了账本上，之后即使掌柜忘记了，比如突然停业几天，恢复生意后依然可以通过账本和粉板上的数据明确赊账账目。
这个粉板和账本的类比很恰当。
redolog 就是粉板，
结合 庖丁解 InnoDB 之 REDO LOG | CatKang 的博客
所以这段内容就解决了以下问题：
- redolog 是啥？为什么需要记录 redolog？
- 需要什么样的 redolog？
- redolog 中记录了什么内容？
- redolog 是怎么组织的？
- 如何高效地写 redolog？
- 如何安全地清除 redolog？
- "***checkpoint是啥? 只跟redolog 相关吗? 跟其他日志有关吗?***"
undolog 是逻辑日志，redolog 是物理日志。但是 redolog 是由 undolog 产生的
redolog 其实就是 WAL，通过先记录日志再写入数据的方式，确保了事务的持久性，并通过 Checkpoint 技术有效管理了日志空间和恢复时间
为了获得更好的读写性能，innoDB 将数据缓存到内存 (innoDB Buffer Pool)，对磁盘数据的修改也会落后于内存，如果进程崩溃就会导致内存数据丢失，所以 innoDB 就维护了 redolog，内存数据丢失后，innoDB 会在重启时，通过重放 REDO，恢复数据
如何安全地清除 redolog?
其实就是刷盘操作（当然还有其他刷盘操作，具体的触发条件、执行方式和优化策略不同，比如同步写入、异步写、定时写等等）
用来把内存中的脏页（尚未写入磁盘的修改数据页）写入磁盘，并更新相关的日志信息，这个操作是为了保证数据的一致性，以防止系统崩溃时数据丢失。
- sharp checkpoint: mysql 关闭时，会触发把所有的脏页都刷入到磁盘上
- fuzzy checkpoint: mysql 运行时，部分刷入磁盘
index
- q: innodb索引树的高度由什么决定?
u: https://www.bilibili.com/video/BV1jb421e7iS/
x: 单条数据的大小会影响索引树的高度。bptree是由16k的page组成的，非叶子node只存index，叶子node存储index和数据本身。
面试爱问的 MySQL 索引，一个动画就了解了！ - YouTube
index 创建原则
- 左选小写修（最左前缀索引、选择性、小字段、写操作频率、修改索引） # 最主要的索引创建原则其实就是最左前缀和选择性。除此之外就是一些tips，比如什么查询频率、写操作频率（更新非常频繁的字段不适合创建索引）、小字段（对于大的文本字段甚至超长字段，不要建索引）、反向开闭（用修改代替新增）之类的
- 在什么情况下会使用最左前缀?
- 最左前缀的本质是啥? # “最左前缀”实际上就是前缀索引在复合索引场景下的使用，也就是说，复合索引中field顺序也要按照“index创建原则”来排序。最左前缀的本质就是ICP，ICP只在满足"最左前缀"条件时起作用。如果查询条件中包含了索引的非最左前缀列，ICP将无法生效，MySQL会在存储引擎层面进行完整的行过滤，这可能会导致性能下降。总结来说，"最左前缀"原则和ICP的本质是基于索引列的前缀进行索引范围扫描，以减少需要访问的行数，提高查询性能。
- Index选择性是啥? # 就是字段不重复的比例 `count(distinct col)/count(*)`（不重复的索引值（也称为基数 cardinality) 和数据表的记录总数的比值），区间为`(0,1]`，***识别度越高，扫描相同行数，需要的次数就越少，这是由 B+ 树的性质决定的***
- 前缀索引、 ***尽量使用前缀来索引，如果索引字段的值很长，最好使用值的前缀来索引*** 例如，TEXT 和 BLOG 类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度
09 普通索引和唯一索引，应该怎么选择？
09 普通索引和唯一索引，应该怎么选择？ 开篇抛出来的问题好啊，很常见的应用场景，身份证号要加 unique index 还是 index。这种场景我一直是加唯一索引的。但是本文通过读操作和写操作两种场景，尤其是是写操作下 change buffer 机制对 index 的优化。
10 MySQL 为什么有时候会选错索引？
11 怎么给字符串字段加索引？
在今天这篇文章中，我跟你聊了聊字符串字段创建索引的场景。我们来回顾一下，你可以使用的方式有：
直接创建完整索引，这样可能比较占用空间；
创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。
在实际应用中，你要根据业务字段的特点选择使用哪种方式。
这章很实用啊
mysql replication
23 MySQL 是怎么保证数据不丢的？
redolog 和 binlog 是 mysql 最核心的两个日志，“只要 redo log 和 binlog 保证持久化到磁盘，就能确保 MySQL 异常重启后，数据可以恢复”，如果想实现 replication，首先就要确保 redolog 能够写入磁盘
其实，binlog 的写入逻辑比较简单：事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。
一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。这就涉及到了 binlog cache 的保存问题。
系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。
事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache。状态如图 1 所示。
可以看到，每个线程有自己 binlog cache，但是共用同一份 binlog 文件。
图中的 write，指的就是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快。
图中的 fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为 fsync 才占磁盘的 IOPS。
redolog 写入机制
总结：
这章讲的是 binlog 和 redolog 的写入机制
- binlog 写入机制：正如上图所示，分为事务执行和事务提交两部分，事务提交之后就是 write+fsync，而事务执行可以理解为大文件分片上传，有个组装逻辑，事务执行时把日志写入 binlog cache，事务提交时，executor 吧 binlog cache 里的完整事务写入 binlog（就是 write），再 fsync 到 disk
- redolog 写入机制
24 MySQL 是怎么保证主备一致的？]
这章都是一些 binlog 相关的基础知识，
- binlog 有哪些日志格式？
- binlog的刷盘时机（什么时候把 binlog 从内存刷到磁盘）？
mixd，mix 是 statement 和 row 的混合。正常而言都是用 statement 来存，但是像主从这种没有逻辑的则用 row 格式来存statement，每一条会修改数据的 sql 都会记录在 binlog 中row，binlog 中可以不记录执行的 sql 语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了
binlog 的刷盘时机？什么时候把 binlog 从内存刷到磁盘？(跟 redis 的 sync 机制类似)
mysql 用sync_binlog参数控制 binlog 的刷盘时机，默认设置为 1
- 0：不去强制要求，由系统自行判断何时写入磁盘
- 1：每次 commit 的时候都要将 binlog 写入磁盘，最安全的设置
- N：每 N 个事务，才会将 binlog 写入磁盘
25 MySQL 是怎么保证高可用的？
25 MySQL 是怎么保证高可用的？ 这章就是讲 replication lagging 了
26 备库为什么会延迟好几个小时？
27 主库出问题了，从库怎么办？
28 读写分离有哪些坑？
接下来，我们就看一下客户端直连和带 proxy 的读写分离架构，各有哪些特点。
1. 客户端直连方案，因为少了一层 proxy 转发，所以查询性能稍微好一点儿，并且整体架构简单，排查问题更方便。但是这种方案，由于要了解后端部署细节，所以在出现主备切换、库迁移等操作的时候，客户端都会感知到，并且需要调整数据库连接信息。 你可能会觉得这样客户端也太麻烦了，信息大量冗余，架构很丑。其实也未必，一般采用这样的架构，一定会伴随一个负责管理后端的组件，比如 Zookeeper，尽量让业务端只专注于业务逻辑开发。
2. 带 proxy 的架构，对客户端比较友好。客户端不需要关注后端细节，连接维护、后端信息维护等工作，都是由 proxy 完成的。但这样的话，对后端维护团队的要求会更高。而且，proxy 也需要有高可用架构。因此，带 proxy 架构的整体就相对比较复杂。
理解了这两种方案的优劣，具体选择哪个方案就取决于数据库团队提供的能力了。但目前看，趋势是往带 proxy 的架构方向发展的。
但是，不论使用哪种架构，你都会碰到我们今天要讨论的问题：由于主从可能存在延迟，客户端执行完一个更新事务后马上发起查询，如果查询选择的是从库的话，就有可能读到刚刚的事务更新之前的状态。
这种“在从库上会读到系统的一个过期状态”的现象，在这篇文章里，我们暂且称之为“过期读”。
前面我们说过了几种可能导致主备延迟的原因，以及对应的优化策略，但是主从延迟还是不能 100% 避免的。
不论哪种结构，客户端都希望查询从库的数据结果，跟查主库的数据结果是一样的。
接下来，我们就来讨论怎么处理过期读问题。
这里，我先把文章中涉及到的处理过期读的方案汇总在这里，以帮助你更好地理解和掌握全文的知识脉络。这些方案包括：
- 强制走主库方案；
- sleep 方案；
- 判断主备无延迟方案；
- 配合 semi-sync 方案；
- 等主库位点方案；
- 等 GTID 方案。
29 如何判断一个数据库是不是出问题了？
其他
38 都说 InnoDB 好，那还要不要使用 Memory 引擎？
38 都说 InnoDB 好，那还要不要使用 Memory 引擎？
可见，InnoDB 和 Memory 引擎的数据组织方式是不同的：
- InnoDB 引擎把数据放在主键索引上，其他索引上保存的是主键 id。这种方式，我们称之为索引组织表（Index Organizied Table）。
- 而 Memory 引擎采用的是把数据单独存放，索引上保存数据位置的数据组织形式，我们称之为堆组织表（Heap Organizied Table）。
---
从中我们可以看出，这两个引擎的一些典型不同：
- InnoDB 表的数据总是有序存放的，而内存表的数据就是按照写入顺序存放的；
- 当数据文件有空洞的时候，InnoDB 表在插入新数据的时候，为了保证数据有序性，只能在固定的位置写入新值，而内存表找到空位就可以插入新值；
- 数据位置发生变化的时候，InnoDB 表只需要修改主键索引，而内存表需要修改所有索引；
- InnoDB 表用主键索引查询时需要走一次索引查找，用普通索引查询的时候，需要走两次索引查找。而内存表没有这个区别，所有索引的“地位”都是相同的。
- InnoDB 支持变长数据类型，不同记录的长度可能不同；内存表不支持 Blob 和 Text 字段，并且即使定义了 varchar(N)，实际也当作 char(N)，也就是固定长度字符串来存储，因此内存表的每行数据长度相同。
这里延伸出的问题：
heap table(堆表), IOT(索引组织表 Index Organization Table), HOT(Heap Organized Table) 的区别
- heap table: pgsql, mysql memory
- IOT: mysql innodb
- HOT: mysql myisam
oracle 中既支持堆表，也支持 IOT
Innodb 的 PAGE 结构与 HEAP 结构的类似，不过在空闲空间管理上是完全不同的。前面是 FILE HEADER/PAGE HEADER，中间是数据记录，数据记录也是从低地址往高地址写，和 Oracle 相反。这是因为 BTREE 存储结构不需要和 slotted page 一样，在块里放一个指示器，其行指示器的功能被 BTREE 替代了。
Innodb 的这种存储结构，并不存在一个十分友好的类似 Oracle 的记录物理地址的 ROWID 这样的结构。所以要想定位某条数据记录，需要使用主键或者簇主键的方式来实现。主键可以定义某条记录的唯一性地址，因此 Mysql 的某张表上的其他索引(secondary index)的索引中存储的键值不像 Oracle 那样存储 ROWID 就可以了，而是存储的是主键中这一行的地址指针。基于一个 secondary index 的查询首先找出某些行的主键，然后再去扫描一次主键索引，才能找到相关行的地址，再找到这条记录。比起有 rowid 的 Oracle 数据库，这里多了一次主键索引的扫描。
可能有些朋友会觉得，是不是 heap 结构一定优于 BTREE 结构呢?其实还是回到今天的标题，没有完美的存储引擎。针对不同的应用场景，heap 和 BTREE 各有优势。BTREE 结构写入数据时按主键排序的，而且并发写入时数据并不是按照插入顺序写入数据块，如果主键存在一定的无序性，那么并发写入的数据可以被打散到多个块中，从而缓解热块冲突的压力。而二级索引的结构虽然对读取数据的操作有影响，对于存在多条索引的数据写入，数据修改，是有优势的。因为只要主键的键值不变，行数据的变化，行在数据块中存储的变化，不需要变更第二索引。
因此我们可以十分明确的肯定，不同的存储结构都各有利弊，并不能很直接的说哪种更好。不过在开发高并发，大数据量的系统的时候，了解存储引擎的一些特点，可以有效的避免一些问题。比如在 Mysql、达梦等数据库中建表，尽可能定义一个显式的主键，从而避免系统自动添加主键。另外如果某张表的热块冲突特别严重的时候，主键可以考虑选择随机性的数据，而不是单边增长的数据，就可以有效的进行数据打散，从而降低热块冲突的可能性。
Heap 表，即使用 MEMORY 存储引擎的表，这种表的数据存储在内存中，由于硬件问题或者断电，数据容易丢失，所以只能从其他数据表中读取数据作为临时表或者只读缓存来使用。
storage, transaction, persist 几个方面
39 自增主键为什么不是连续的？
这个问题在之前看 为什么 MySQL 的自增主键不单调也不连续 - 面向信仰编程 时，就大概了解。
我先简要写下我目前的回答：
主键自增很重要，是为了保证使用 bptree（innodb）能够尽量顺序写，避免页分裂嘛。但是在 mysql8 之前，auto_increment 都是直接存在内存里的，如果 mysql 挂了重启，这个数据就没了。会重新根据主键 id+1，重新获取 auto_increment 值，
45 自增 id 用完怎么办？
今天这篇文章，我给你介绍了 MySQL 不同的自增 id 达到上限以后的行为。数据库系统作为一个可能需要 7\*24 小时全年无休的服务，考虑这些边界是非常有必要的。
每种自增 id 有各自的应用场景，在达到上限后的表现也不同：
- 表的自增 id 达到上限后，再申请时它的值就不会改变，进而导致继续插入数据时报主键冲突的错误。
- row_id 达到上限后，则会归 0 再重新递增，如果出现相同的 row_id，后写的数据会覆盖之前的数据。
- Xid 只需要不在同一个 binlog 文件中出现重复值即可。虽然理论上会出现重复值，但是概率极小，可以忽略不计。
- InnoDB 的 max_trx_id 递增值每次 MySQL 重启都会被保存起来，所以我们文章中提到的脏读的例子就是一个必现的 bug，好在留给我们的时间还很充裕。
- thread_id 是我们使用中最常见的，而且也是处理得最好的一个自增 id 逻辑了。
当然，在 MySQL 里还有别的自增 id，比如 table_id、binlog 文件序号等，就留给你去验证和探索了。
[2024-10-17] 之前 mysql 笔记相关内容精粹
MySQL 版本 CHANGELOG
- 有哪些实现 MySQL 高可用的方案？
- MySQL 高可用，有哪些核心需求？
有哪些实现 MySQL 高可用的方案？
- 有很多方案，但是都不靠谱
- 基于 MySQL 复制的方案是主流，也非常成熟，引入中间件和引入 zookeeper 虽然能将系统的可用性做的更好，可支撑的规模更大，但也对研发和运维提出了更高的要求；因此，在选择方案时，要根据业务场景和运维规模做选择
MySQL 高可用，有哪些核心需求？
- 客户端切换，如何“自动故障转移”
- 多个 MySQL 节点之间如何做数据同步
- CC 由数据库的调度器负责，事务本身感知不到可能导致数据一致性的冲突事务，调度器会 delay 或者 abort，如果 delay 就延迟到合法时机，如果 abort 就直接回滚
- 本质上是一个取舍问题，乐观锁不维护锁，吞吐很高，但是相应回滚也会比较多，而回滚比延迟的成本要高很多，所以在冲突较少和 validation 开销小的情况下，使用 OCC。LBCC 的方案则相反
这里从两个维度，对常见的并发控制机制进行分类：
1. 乐观程度
不同的实现机制，基于不同的对发生冲突概率的假设，悲观方式认为只要两个事务访问相同的数据库对象，就一定会发生冲突，因而应该尽早阻止；而乐观的方式认为，冲突发生的概率不大，因此会延后处理冲突的时机。如上图横坐标所示，乐观程度从左向右增高：
1、基于 Lock：最悲观的实现，需要在操作开始前，甚至是事务开始前，对要访问的数据库对象加锁，对冲突操作 Delay；
2、基于 Timestamp：乐观的实现，每个事务在开始时获得全局递增的时间戳，期望按照开始时的时间戳依次执行，在操作数据库对象时检查冲突并选择 Delay 或者 Abort；
3、基于 Validation：更乐观的实现，仅在 Commit 前进行 Validate，对冲突的事务 Abort
可以看出，不同乐观程度的机制本质的区别在于，检查或预判冲突的时机，Lock 在事务开始时，Timestamp 在操作进行时，而 Validation 在最终 Commit 前。相对于悲观的方式，乐观机制可以获得更高的并发度，而一旦冲突发生，Abort 事务也会比 Delay 带来更大的开销。
对应上述每种乐观程度，都可以有多版本的实现方式，多版本的优势在于，可以让读写事务与只读事务互不干扰，因而获得更好的并行度，也正是由于这一点成为几乎所有主流数据库的选择。为了实现多版本的并发控制，需要给每个事务在开始时分配一个唯一标识 TID，并对数据库对象增加以下信息：
txd-id，创建该版本的事务 TID
begin-ts 及 end-ts 分别记录该版本创建和过期时的事务 TID
pointer: 指向该对象其他版本的链表
其基本的实现思路是，每次对数据库对象的写操作都生成一个新的版本，用自己的 TID 标记新版本 begin-ts 及上一个版本的 end-ts，并将自己加入链表。读操作对比自己的 TID 与数据版本的 begin-ts，end-ts，找到其可见最新的版本进行访问。根据乐观程度多版本的机制也分为三类：
以 乐观程度 和 是否 MV 为横纵坐标，可以看到 pgsql 就使用 MVTO，而 mysql（InnoDB）和 oracle 则使用 MVRC(MM read consistency)
PostgreSQL uses multi-version timestamp ordering (MVTO) while InnoDB and Oracle use multi-version read consistency (MVRC). The main difference is that PostgreSQL is with-REDO/no-UNDO because it stores every row version in the main table, while Oracle/InnoDB implements with-REDO/with-UNDO where they reconstruct a block and/or row image from the log to provide read consistency.
MVCC 具体起到什么作用？我知道 MVCC 是用来实现再 db 中并发读写操作时保证数据一致性，那从 CAP 的角度出发，是为了保证 Consistency 吗？
与其说 MVCC 是为了保证 C，不如说是为了保证在 C 和 A 之间的平衡（想想之所以说 InnoDB 是 50%C +50%A，就知道了）
ANSI SQL 标准(1992)：基于异象
A Critique of ANSI(1995)：基于锁
A Generalized Theory(1999)：基于序列化图
Cursor Stability
Snapshot Ioslation
ANSI SQL92 用三种并发异常（Anomaly），划分出四种不同的隔离级别，将这种利弊权衡进行了（糟糕的）标准化
Consistency，一致性是最基本属性，其他三种都是为了实现一致性而存在的Isolation，用事务的隔离级别保证事务的隔离性，为了保证并发场景下的一致性，引入隔离性，不同事务之间互不影响Atomic，用 undolog 保证事务执行失败后，直接回滚Durability，用 redolog 保证事务提交后，对数据的修改是永久性的，即使系统故障也不会丢失
事务的 ACID 特性分别是什么？
Atomic，事务里的所有操作要么全部完成，要么全部失败Consistency，事务执行一定要保证系统的一致性Isolation，事务与事务之间不会互相影响，一个事务的中间状态不会被其他事务感知Durability，事务一旦完成，所有写操作都要持久化
隔离就是类似 linux kernel 的可见性，通过可见性来解决事务在并发请求（包括读写操作）时的数据一致性问题。比如什么 RU、RC、RR、SR，随着隔离级别的加强，分别解决了脏写、脏读、不可重复读和幻读的问题。隔离级别越来越高，并发性就越差。当多个事务并发时，MySQL 利用加锁和阻塞来保证事务之间不同等级的隔离性。
RU、RC、RR、SR 分别是什么？
RU读操作不加锁，可能会脏读(解决了脏写问题)RC只对记录加记录锁，而不会在记录之间加间隙锁，所以允许新的记录插入到被锁定记录的附近。所以在多次读操作时，会发生不可重复读(解决了脏读问题)RR多次读取同一范围的数据会返回第一次查询的快照，不会返回不同的数据行，但是可能会发生幻读(解决了不可重复读问题)SRInnoDB 隐式地将全部查询语句加上共享锁，解决了幻读问题，但是性能很差
RC 隔离
Read Committed（读取已提交）
- 只读取提交的数据，并等待其他事务释放排他锁。
- 这是大多数数据库系统的默认隔离级别（但不是 MySQL 默认的）。
- 它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。
- 这种隔离级别也支持所谓的不可重复读，因为同一事务的其他实例在该实例处理期间，可能会有新的 commit，所以同一个 select 可能返回不同结果
RU 隔离
Read Uncommitted（读取未提交内容）
- 在读数据时，不会检查或者使用任何锁。
- 在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。RU 很少被使用，因为它的性能比其他隔离级别好不了太多，并且会导致脏读
RR 隔离
Repeatable Read（可重复读）
事务 A 读取一条数据后，事务 B 对该数据修改并提交，事务 A 再读取该数据，读到的还是原来的内容
- 像 RC 那样读数据，但会保持共有锁，直到事务结束
- 这是 MySQL 的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。
- 不过理论上，这会导致另一个棘手的问题：幻读，简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影”行。
- InnoDB 和 Falcon 存储引擎通过 MVCC 机制解决了该问题。(其实多版本只是解决不可重复读问题，而加上间隙锁 (也就是它这里所谓的并发控制) 才解决了幻读问题)
SR 隔离
Serializable（可串行化）
- 工作方式类似于可重复读。但它不仅会锁定受影响的数据，还会锁定这个范围。这就阻止了新数据插入查询所涉及的范围。
- 这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。
脏读，t1 在修改之前提交，t2 读取，t1 回滚，t2 读取了从未提交的数据，读未提交时，读事务直接读取主记录，无论更新事务是否完成不可重复读t1 读取，t2 修改该数据并提交，t1 重新读取，数据已被修改，数据不同幻读数据不同_每次都能读到最新数据_
不同的隔离级别有不同的现象，并有不同的锁定/并发机制，隔离级别越高，数据库的并发性就越差，对于不同的事务，采用不同的隔离级别分别有不同的结果
脏读一个事务可以读取另一个尚未提交事务的修改数据 (一个事务内修改了数据，另一个事务读取并使用了这个数据)不可重复读在同一个事务中，同一个查询在 T1 时间读取某一行，在 T2 时间重新读取这一行时候，这一行的数据已经发生修改，可能被更新了，也可能被删除了 (一个事务内连续读了两次数据，中间另一个事务修改了这个数据，导致第一个事务前后两次读的数据不一致)幻读在同一事务中，同一查询多次进行时候，由于其他 insert 的事务提交，导致每次返回不同的结果集 (一个事务内修改了涉及全表的数据，另一个事务往这个表里面插入了新的数据，第一个事务出现幻读)更新丢失一个事务内变更了数据，另一个事务修改了这个数据，最后前一个事务 commit 导致另一个事务的变更丢失
MVCC 机制的原理和实现：MVCC 是什么?ReadView 是什么？高水位、低水位?
InnoDB 使用 MVCC 来支持高并发，并且实现了四个标准的隔离级别，通过间隙锁 next-key locking 策略防止幻读的出现
- MVCC 就是_读请求直接访问对应版本的数据，从而避免读写事务和只读事务互相阻塞_，同一个数据有多个版本，最大的好处是读写不冲突，只有同时写操作冲突，可以很大程度上提升性能
- MVCC 的目的就是，实现数据库的隔离级别
MVCC=版本链+ReadView（就是快照，用来做可见性判断）
通过版本链的 trx_id 和 ReadView 的高低水位比较后，决定使用哪个版本
- ReadView 的结构
- m_ids
- min_trx_id 低水位
- max_trx_id 高水位
- creator_trx_id
- undolog 版本链的结构
- trx_id，事务 id
- roll_pointer 回滚指针
RC 和 RR 有什么区别？RC 和 RR 的 MVCC 机制为什么不同？
_RC 和 RR 的区别在于 ReadView 快照生成时机不同，导致可见性不同_
- RC 在每次读取数据前都生成一个 ReadView
- RR 在第一次读取数据时生成一个 ReadView
---
- RR 和 RC 在锁方面的区别？
- RR 和 RC 在复制方面的区别？
- RR 和 RC 在一致性读方面的区别？RC 支持半一致性读，RR 不支持
为什么 MySQL 把 RR 作为默认的隔离级别呢？
MySQL 数据库的默认隔离级别是 RR，但是实际情况是使用 RR 和 RC 都不少，比如淘宝和网易都是使用的 RC 隔离级别
《MySQL 技术内幕：InnoDB 存储引擎（第 2 版）》 | 三点水
- 事务相关：支持 ACID 事务、MVCC、InnoDB 锁机制、redolog
- 性能相关：插入缓冲、double write、BP(buffer pool)、AHI(Adaptive Hash Index)自适应哈希索引
但是实际上事务相关特性，本质上也还是为了优化性能，比如说之所以用 MVCC 就是为了能够并发事务，而 InnoDB 锁机制和 redolog 本质上来说都是为了保证 MVCC 正常运行。
另外，也有一些其他特性，比如外键、行级锁定、共享表空间和独立表空间等等，这些都比较简单，就不太重要了。
插入缓冲 (insert buffer)，加速插入操作，插入缓冲用于非聚簇索引的插入和更新操作，先判断插入的非聚簇索引是否在缓存池中，如果在则直接插入，否则插入到插入缓存对象中。再以一定的频率进行插入缓冲和辅助索引叶子节点的 merge 操作，将多次插入合并到一个操作中，提高对非聚簇索引的插入性能二次写 (double write)由两部分组成，一部分是内存中的double write buffer，大小为 2MB，另一部分是物理磁盘上共享表空间连续的 128 个页，大小也为 2MB。在对缓冲池的脏页刷新时，并不直接写磁盘，而是通过 memcpy 函数将脏页先复制到内存中的该区域，之后通过double write buffer再分两次，每次 1MB 顺序地写入共享空间的物理磁盘上，然后马上调用 fsync 函数，同步磁盘，避免 OS 缓冲写带来的问题自适应哈希索引 (adaptive hash index)自动在内存中创建 hash 索引来加速读操作，innoDB 会根据访问的频率和模式，为热点页建立哈希索引，来提高查询效率。索引通过缓存池的 B+ 树页构造而来，因此建立速度很快，innoDB 存储引擎会监控对表上各个索引页的查询，如果观察到建立哈希索引可以带来速度上的提升，则建立哈希索引，所以叫做自适应哈希索引缓存池为了提高数据库的性能，引入缓存池的概念，通过参数可以设置缓存池的大小和实例个数，缓存池可以存储一下内容：索引页、数据页、undo 页、插入缓冲、自适应哈希索引、innoDB 存储的锁信息和数据字典信息 (data dict)- 使用可预测读作为磁盘数据读取方式
- innodb 源码分析之基础数据结构 · innodb 源码分析 · 看云
- MySQL 之 InnoDB 锁系统源码分析 - 掘金
- 秒懂 InnoDB 的锁 - 菜刚 RyuGou 的博客
- MySQL 全局锁、表级锁、行级锁，你搞清楚了吗？
- 共享锁 (S 锁, shared lock)
- 排他锁 (X 锁, exclusive lock)
- 意向锁 (I 锁, intention lock)
- 意向共享锁 (IS 锁, 表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的 IS 锁)
- 意向排他锁 (IX 锁, 类似上面，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的 IX 锁)
- 插入意向锁 (insert intention lock)
- 自增锁 auto-inc lock
行锁
- 记录锁 (record lock)
- 间隙锁 (gap lock)
- 临键锁 (next-key lock)
我说下我的理解哈，shared lock 和 exclusive lock 就是 mutex 和 rwmutex,所有语言里都有类似的锁机制，不多说。intention lock 其实就是 IsLocked，用来判断是否加锁，进而决定下一步操作是 sleep 还是 spin 什么的，也很常用。下面的 gaplock，next-key lock 都是 MVCC 相关的锁，可以理解。自增锁 auto-inc lock 是用来保证自增字段唯一性的，防止并发插入时，某个自增字段的数据重复。我不太理解 record lock 和 insert intention lock 有啥用。mutex(exclusive lock)不是已经能够在写操作时的行级锁定了吗?为什么还需要 record lock 呢?还有不是已经有 intention lock 了吗?为什么还需要 insert intention lock?
MySQL 选择使用 B+树而不是 B 树作为其存储引擎主要基于以下考虑:
- B+ 树的叶子节点形成有序链表，使得范围查询更加高效。在数据库中，范围查询是非常常见的操作，因此 B+树更适合处理这类查询。
- B+树的内部节点只存储索引信息，相比之下，B 树的内部节点也存储实际数据。这使得 B+树更加紧凑，可以存储更多的索引数据在内存中，提高查询性能。
- B+ 树的有序链表叶子节点可以更好地支持顺序访问，对于按顺序获取数据的查询操作，B+ 树的性能更好。
综上所述，MySQL 选择使用 B+ 树作为其存储引擎是为了提高范围查询和顺序访问的性能，并且能够更好地利用内存空间。
InnoDB 结构
mysql-innodb-arch.png
在内存结构里面，主要就是各种 buffer 缓冲区，这些缓冲区对于提升数据库性能上至关重要。
Buffer Pool，缓冲池。缓冲池允许直接从内存中处理经常使用的数据，从而加快了处理速度。在专用服务器上，通常将多达 80％的物理内存分配给缓冲池。
Change Buffer，更改缓冲区。这块缓冲区，是存放在 Buffer Pool 内部。更改缓冲区是一种特殊的数据结构，当这些页面不在 Buffer Pool 中时，该缓存可缓存对二级索引页的更改。可能由 INSERT、UPDATE，或 DELETE 操作(DML)导致的缓冲更改将在以后通过其他读取操作将页面加载到缓冲池中时合并。也就是说，其对应的数据不需要从磁盘加载到 Buffer 中，而是将变更存在在 Change Buffer 内，等待合适时机再 merge 回去。
Log Buffer，日志缓冲区。主要用来存放 redo log 数据。
在磁盘结构里面，就是各种类型的文件。所有的数据，最终都需要落到各种磁盘文件上来保证数据的持久性。核心的文件主要有：
表和索引文件。
double write buffer 文件。
undo log 文件。
redo log 文件。
表空间、表、索引这些文件，是真正数据文件。double write buffer 是由于 linux 页大小 4K，MySQL 页大小 16K，直接将 MySQL 页写到磁盘，可能导致部分成功、部分失败导致后期数据恢复存在问题，因为增加了 double write buffer 做数据恢复的时候使用。undo log 事务回滚和 MVCC 时需要找到历史快照数据。redo log 则是保证数据的持久和原子性而存在的。
catkang
ARIES，一统江湖
1992 年，IBM 的研究员们发表了《ARIES: a transaction recovery method supporting fine-granularity locking and partial rollbacks using write-ahead logging》[2]，其中提出的 ARIES 逐步成为磁盘数据库实现故障恢复的标配，ARIES 本质是一种 Redo-Undo 的 WAL 实现。 Normal 过程：修改数据之前先追加 Log 记录，Log 内容同时包括 Redo 和 Undo 信息，每个日志记录产生一个标记其在日志中位置的递增 LSN（Log Sequence Number）；数据 Page 中记录最后修改的日志项 LSN，以此来判断 Page 中的内容的新旧程度，实现幂等。故障恢复阶段需要通过 Log 中的内容恢复数据库状态，为了减少恢复时需要处理的日志量，ARIES 会在正常运行期间周期性的生成 Checkpoint，Checkpoint 中除了当前的日志 LSN 之外，还需要记录当前活跃事务的最新 LSN，以及所有脏页，供恢复时决定重放 Redo 的开始位置。需要注意的是，由于生成 Checkpoint 时数据库还在正常提供服务（Fuzzy Checkpoint），其中记录的活跃事务及 Dirty Page 信息并不一定准确，因此需要 Recovery 阶段通过 Log 内容进行修正。
Recover 过程：故障恢复包含三个阶段：Analysis，Redo 和 Undo。Analysis 阶段的任务主要是利用 Checkpoint 及 Log 中的信息确认后续 Redo 和 Undo 阶段的操作范围，通过 Log 修正 Checkpoint 中记录的 Dirty Page 集合信息，并用其中涉及最小的 LSN 位置作为下一步 Redo 的开始位置 RedoLSN。同时修正 Checkpoint 中记录的活跃事务集合（未提交事务），作为 Undo 过程的回滚对象；Redo 阶段从 Analysis 获得的 RedoLSN 出发，重放所有的 Log 中的 Redo 内容，注意这里也包含了未 Commit 事务；最后 Undo 阶段对所有未提交事务利用 Undo 信息进行回滚，通过 Log 的 PrevLSN 可以顺序找到事务所有需要回滚的修改。
除此之外，ARIES 还包含了许多优化设计，例如通过特殊的日志记录类型 CLRs 避免嵌套 Recovery 带来的日志膨胀，支持细粒度锁，并发 Recovery 等。[3]认为，ARIES 有两个主要的设计目标：
Feature：提供丰富灵活的实现事务的接口：包括提供灵活的存储方式、提供细粒度的锁、支持基于 Savepoint 的事务部分回滚、通过 Logical Undo 以获得更高的并发、通过 Page-Oriented Redo 实现简单的可并发的 Recovery 过程。
Performance：充分利用内存和磁盘介质特性，获得极致的性能：采用 No-Force 避免大量同步的磁盘随机写、采用 Steal 及时重用宝贵的内存资源、基于 Page 来简化恢复和缓存管理。
总结的太棒了
庖丁解 InnoDB 之 REDO LOG | CatKang 的博客
庖丁解 InnoDB 之 Undo LOG | CatKang 的博客
庖丁解 InnoDB 之 Buffer Pool | CatKang 的博客