首页 思维导图 详情

MySQL最全底层知识树

2023-06-21 09:46:24 0 举报
AI智能生成
MySQL底层知识树，学习路线
MySQL
mysql底层原理
MySql Cluster架构
Mysql高可用集群
数据库
作者其他创作
大纲/内容
第14章 不好看就要多整容-MySQL基于规则的优化（内含关于子查询优化二三事儿）
1 条件化简
1.1 移除不必要的括号
1.2 常量传递（constant_propagation）
1.3 等值传递（equality_propagation）
1.4 移除没用的条件（trivial_condition_removal）
1.5 表达式计算
1.6 HAVING子句和WHERE子句的合并
1.7 常量表检测
2 外连接消除
空值拒绝（ reject-NULL ）
在被驱动表的WHERE子句符合空值拒绝的条件后，外连接和内连接可以相互转换。这种转换带来的好处就是查询优化器可以通过评估表的不同连接顺序的成本，选出成本最低的那种连接顺序来执行查询。
3 子查询优化
3.1 子查询语法
子查询的位置
SELECT 子句中
SELECT (SELECT m1 FROM t1 LIMIT 1);
FROM 子句中
SELECT m, n FROM (SELECT m2 + 1 AS m, n2 AS n FROM t2 WHERE m2 > 2) AS t;
派生表
WHERE 或 ON 子句中
SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2);
按返回的结果集区分子查询
标量子查询
那些只返回一个单一值的子查询称之为 标量子查询
行子查询
返回一条记录的子查询，不过这条记录需要包含多个列
列子查询
列子查询自然就是查询出一个列的数据喽，不过这个列的数据需要包含多条记录
表子查询
子查询的结果既包含很多条记录，又包含很多个列
按与外层查询关系来区分子查询
不相关子查询
子查询可以单独运行出结果，而不依赖于外层查询的值
相关子查询
子查询的执行需要依赖于外层查询的值
子查询在布尔表达式中的使用
使用 = 、 > 、 < 、 >= 、 <= 、 <> 、 != 、 <=> 作为布尔表达式的操作符
这里的子查询只能是标量子查询或者行子查询，也就是子查询的结果只能返回一个 单一的值或者只能是一条记录
SELECT * FROM t1 WHERE m1 < (SELECT MIN(m2) FROM t2);
SELECT * FROM t1 WHERE (m1, n1) = (SELECT m2, n2 FROM t2 LIMIT 1);
[NOT] IN/ANY/SOME/ALL子查询
IN 或者 NOT IN
操作数 [NOT] IN (子查询)
SELECT * FROM t1 WHERE (m1, n2) IN (SELECT m2, n2 FROM t2);
ANY/SOME （ ANY 和 SOME 是同义词）
操作数 comparison_operator ANY/SOME(子查询)
SELECT * FROM t1 WHERE m1 > ANY(SELECT m2 FROM t2);
意思是只要子查询结果集中存在某个值和给定的操作数做 comparison_operator 比较 结果为 TRUE ，那么整个表达式的结果就为 TRUE ，否则整个表达式的结果就为 FALSE 。
ALL
操作数 comparison_operator ALL(子查询)
SELECT * FROM t1 WHERE m1 > ALL(SELECT m2 FROM t2);
的意思是子查询结果集中所有的值和给定的操作数做 comparison_operator 比较结果 为 TRUE ，那么整个表达式的结果就为 TRUE ，否则整个表达式的结果就为 FALSE 。
EXISTS子查询
[NOT] EXISTS (子查询)
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2)
仅仅需要判断子查询的结果集中是否有记录，而不在乎它的记录具体是个啥，可以使用把 EXISTS 或者 NOT EXISTS 放在子查询语句前边
3.2 子查询在MySQL中是怎么执行的
小白执行方式
不相关子查询
SELECT * FROM s1   WHERE key1 IN (SELECT common_field FROM s2);
先单独执行 (SELECT common_field FROM s2) 这个子查询。
然后在将上一步子查询得到的结果当作外层查询的参数再执行外层查询 SELECT * FROM s1 WHERE key1 IN (...) 。
相关子查询
SELECT * FROM s1   WHERE key1 IN (SELECT common_field FROM s2 WHERE s1.key2 = s2.key2);
先从外层查询中获取一条记录，本例中也就是先从 s1 表中获取一条记录。
然后从上一步骤中获取的那条记录中找出子查询中涉及到的值，本例中就是从 s1 表中获取的那条记录
中找出 s1.key2 列的值，然后执行子查询。
最后根据子查询的查询结果来检测外层查询 WHERE 子句的条件是否成立，如果成立，就把外层查询的那条记录加入到结果集，否则就丢弃。
再次执行第一步，获取第二条外层查询中的记录，依次类推～
标量子查询、行子查询的执行方式
不相关
对于包含不相关的标量子查询或者行子查询的查询语句来说，MySQL会分别独立的执行外层查询和子 查询，就当作两个单表查询就好了
相关
IN子查询优化
不相关
不直接将不相关子查询的结果集当作外层查询的参数，而是将该结果集写
入一个临时表里。物化 （Materialize ）
该临时表的列就是子查询结果集中的列。
写入临时表的记录会被去重。（主键或者唯一索引）
建立基于内存的使用 Memory 存储引擎的临时表，而且 会为该表建立哈希索引。
如果子查询的结果集非常大，超过了系统变量 tmp_table_size 或者 max_heap_table_size ，临时表会转而 使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为 B+ 树索引。
物化表转连接
相当于表 s1 和子查询物化表 materialized_table 进行内连接
转为半连接
SELECT * FROM s1  WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');
只关心 s2 表中是否存在记录满足 s1.key1 = s2.common_field 这个条
件，而不关心具体有多少条记录与之匹配
SELECT s1.* FROM s1 SEMI JOIN s2  ON s1.key1 = s2.common_field  WHERE key3 = 'a';
对于 s1 表的某条记录来说，我们只关心在 s2 表中是否存在与之匹配的记录是否存在，而不关心具体有多少条记录与之匹配，
最终的结果集中只保留 s1 表的记录。
相关
转为半连接
SELECT * FROM s1   WHERE key1 IN (SELECT common_field FROM s2 WHERE s1.key3 = s2.key3);
SELECT s1.* FROM s1 SEMI JOIN s2   ON s1.key1 = s2.common_field AND s1.key3 = s2.key3;
然后就可以使用我们上边介绍过的 DuplicateWeedout 、 LooseScan 、 FirstMatch 等半连接执行策略来执行查 询，当然，如果子查询的查询列表处只有主键或者唯一二级索引列，还可以直接使用 table pullout 的策略来执 行查询
由于相关子查询并不是一个独立的查询，所以不能转换为物化表来执行查询。
将子查询转换为semi-join（半连接，内部）
实现方式
Table pullout （子查询中的表上拉）
当子查询的查询列表处只有主键或者唯一索引列时，可以直接把子查询中的表 上拉 到外层查询的 FROM 子句 中，并把子查询中的搜索条件合并到外层查询的搜索条件
DuplicateWeedout execution strategy （重复值消除）
建立一个临时表
CREATE TABLE tmp (  id PRIMARY KEY  );
每当某条 s1 表中的记录要加入结果集时，就首先把这条记录的 id 值加入到 这个临时表里，如果添加成功，说明之前这条 s1 表中的记录并没有加入最终的结果集，现在把该记录添加 到最终的结果集；如果添加失败，说明这条之前这条 s1 表中的记录已经加入过最终的结果集，这里直接把 它丢弃
LooseScan execution strategy （松散索引扫描）
扫描索引，但只取值相同的记录的第一条去做匹配操作
Semi-join Materialization execution strategy
物化后连接
仅支持不相关子查询
FirstMatch execution strategy （首次匹配）
semi-join的适用条件
SELECT ... FROM outer_tables   WHERE expr IN (SELECT ... FROM inner_tables ...) AND ...
SELECT ... FROM outer_tables   WHERE (oe1, oe2, ...) IN (SELECT ie1, ie2, ... FROM inner_tables ...) AND ...
该子查询必须是和 IN 语句组成的布尔表达式，并且在外层查询的 WHERE 或者 ON 子句中出现。
外层查询也可以有其他的搜索条件，只不过和 IN 子查询的搜索条件必须使用 AND 连接起来。
该子查询必须是一个单一的查询，不能是由若干查询由 UNION 连接起来的形式。
该子查询不能包含 GROUP BY 或者 HAVING 语句或者聚集函数。
不适用于semi-join的情况
外层查询的WHERE条件中有其他搜索条件与IN子查询组成的布尔表达式使用 OR 连接起来
使用 NOT IN 而不是 IN 的情况
在 SELECT 子句中的IN子查询的情况
子查询中包含 GROUP BY 、 HAVING 或者聚集函数的情况
子查询中包含 UNION 的情况
优化不能转为 semi-join 查询的子查询
对于不相关子查询来说，可以尝试把它们物化之后再参与查询
不管子查询是相关的还是不相关的，都可以把 IN 子查询尝试专为 EXISTS 子查询
outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)
EXISTS (SELECT inner_expr FROM ... WHERE subquery_where AND outer_expr=inner_expr)
ANY/ALL子查询优化
不相关子
< ANY (SELECT inner_expr ...) 转换为 < (SELECT MAX(inner_expr) ...)
> ANY (SELECT inner_expr ...) 转换为 > (SELECT MIN(inner_expr) ...)
< ALL (SELECT inner_expr ...) 转换为 < (SELECT MIN(inner_expr) ...)
> ALL (SELECT inner_expr ...) 转换为 > (SELECT MAX(inner_expr) ...)
[NOT] EXISTS子查询的执行
不相关
先执行子查询，得出该 [NOT] EXISTS 子查询的结果是 TRUE 还 是 FALSE ，并重写原先的查询语句
相关
对于派生表的优化
外层查询的 FROM 子句后，那么这个子查询的结果相当于一个 派生表
两种执行策略
把派生表物化
派生表和外层合并，重写为没有派生表
当派生表中有这些语句就不可以合并
聚集函数，比如MAX()、MIN()、SUM()啥的
DISTINCT
GROUP BY
HAVING
LIMIT
UNION 或者 UNION ALL
派生表对应的子查询的 SELECT 子句中含有另一个子查询
...
第15章 查询优化的百科全书-Explain详解（上）
1 执行计划输出中各列详解
1.1 table
EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表
的表名
1.2 id
查询语句中每出现一个 SELECT 关键字，设计 MySQL 的大叔就会为它分配一个唯一的 id 值
在连接查询的执行计划中，每个表都会对应一条记录，这些记录的id列的值是相同的，出 现在前边的表表示驱动表，出现在后边的表表示被驱动表。
查询优化器可能对涉及子查询的查询语句进行重写，从而转换为连接查询。
1.3 select_type
SIMPLE
查询语句中不包含 UNION 或者子查询的查询都算作是 SIMPLE 类型
如单表查询、连接查询
PRIMARY
对于包含 UNION 、 UNION ALL 或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询 的 select_type 值就是 PRIMARY
UNION
对于包含 UNION 或者 UNION ALL 的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询以 外，其余的小查询的 select_type 值就是 UNION
UNION RESULT
MySQL 选择使用临时表来完成 UNION 查询的去重工作，针对该临时表的查询的 select_type 就是 UNION RESULT
SUBQUERY
如果包含子查询的查询语句不能够转为对应的 semi-join 的形式，并且该子查询是不相关子查询，并且查询 优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个 SELECT 关键字代表的那个查 询的 select_type 就是 SUBQUERY
由于select_type为SUBQUERY的子查询由于会被物化，所以只需要执行一遍
DEPENDENT SUBQUERY
如果包含子查询的查询语句不能够转为对应的 semi-join 的形式，并且该子查询是相关子查询，则该子查询 的第一个 SELECT 关键字代表的那个查询的 select_type 就是 DEPENDENT SUBQUERY
select_type为DEPENDENT SUBQUERY的查询可能会被执行多次
DEPENDENT UNION
在包含 UNION 或者 UNION ALL 的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小 查询之外，其余的小查询的 select_type 的值就是 DEPENDENT UNION
DERIVED
对于采用物化的方式执行的包含派生表的查询，该派生表对应的子查询的 select_type 就是 DERIVED
MATERIALIZED
当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，该子查询对 应的 select_type 属性就是 MATERIALIZED
UNCACHEABLE SUBQUERY
UNCACHEABLE UNION
1.4 partitions
1.5 type
system
当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的 访问方法就是 system
const
根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法 就是 const
eq_ref
在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者 唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是 eq_ref
ref
当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是 ref
fulltext
全文索引
ref_or_null
当对普通二级索引进行等值匹配查询，该索引列的值也可以是 NULL 值时，那么对该表的访问方法就可能是 ref_or_null
index_merge
一般情况下对于某个表的查询只能使用到一个索引，但我们唠叨单表访问方法时特意强调了在某些场景下可 以使用 Intersection 、 Union 、 Sort-Union 这三种索引合并的方式来执行查询
unique_subquery
类似于两表连接中被驱动表的 eq_ref 访问方法， unique_subquery 是针对在一些包含 IN 子查询的查询语 句中，如果查询优化器决定将 IN 子查询转换为 EXISTS 子查询，而且子查询可以使用到主键进行等值匹配的 话，那么该子查询执行计划的 type 列的值就是 unique_subquery
index_subquery
index_subquery 与 unique_subquery 类似，只不过访问子查询中的表时使用的是普通的索引
range
如果使用索引获取某些 范围区间 的记录，那么就可能使用到 range 访问方法
index
当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是 index
ALL
全表扫描
1.6 possible_keys和key
possible_keys 列表示在某个查询语句中，对某个表执行单表查询时可能用 到的索引有哪些， key 列表示实际用到的索引有哪些
1.7 key_len
key_len 列表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度
对于使用固定长度类型的索引列来说，它实际占用的存储空间的最大长度就是该固定值，对于指定字符集的 变长类型的索引列来说，比如某个索引列的类型是 VARCHAR(100) ，使用的字符集是 utf8 ，那么该列实际占 用的最大存储空间就是 100 × 3 = 300 个字节。
如果该索引列可以存储 NULL 值，则 key_len 比不可以存储 NULL 值时多1个字节。
对于变长字段来说，都会有2个字节的空间来存储该变长列的实际长度。
1.8 ref
当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是 const 、 eq_ref 、 ref 、 ref_or_null 、
unique_subquery 、 index_subquery 其中之一时， ref 列展示的就是与索引列作等值匹配的东东是个啥，比如
只是一个常数或者是某个列或者是一个函数。
1.9 rows
如果查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的 rows 列就代表预计需要扫描的行 数，如果使用索引来执行查询时，执行计划的 rows 列就代表预计扫描的索引记录行数。
1.10 filtered
扇出值就是 rows × filtered% =
扇出值A ，这说明还要对被驱动表执行大约 A 次查询。
第16章 查询优化的百科全书-Explain详解（下）
1 执行计划输出中各列详解
1.1 Extra
No tables used
查询语句的没有 FROM 子句
Impossible WHERE
查询语句的 WHERE 子句永远为 FALSE
No matching min/max row
当查询列表处有 MIN 或者 MAX 聚集函数，但是并没有符合 WHERE 子句中的搜索条件的记录
Using index
当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用索引覆盖的情况
Using index condition（索引条件下推）
有些搜索条件中虽然出现了索引列，但却不能使用到索引
SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';
暂时先不回表，在二级索引后先去查询符合like条件
Using where
当使用全表扫描来执行对某个表的查询，并且该语句的 WHERE 子句中有针对该表的搜索条件
当使用索引访问来执行对某个表的查询，并且该语句的 WHERE 子句中有除了该索引包含的列之外的其他搜索 条件
Using join buffer (Block Nested Loop)
在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度， MySQL 一般会为其分配一块名叫 join buffer 的内存块来加快查询速度，也就是我们所讲的 基于块的嵌套循环算法
Not exists
当我们使用左（外）连接时，如果 WHERE 子句中包含要求被驱动表的某个列等于 NULL 值的搜索条件，而且 那个列又是不允许存储 NULL 值的
没有必要到被驱动表中找到全部符合ON子句条件的记录，这样可以稍微节省一点性能
Using intersect(...) 、 Using union(...) 和 Using sort_union(...)
索引合并
Zero limit
LIMIT 子句的参数为 0
Using filesort
文件排序
Using temporary
比如去重、排序之类的，比如我们在执行许多包含 DISTINCT 、 GROUP BY 、 UNION 等子句的查询过程中，如果不能有效利用索引来完成查询，
MySQL 很有可能寻求通过建立内部的临时表来执行查询
Start temporary, End temporary
将 IN 子查询转换成 semi-join ，而 semi-join 又 有好多种执行策略，当执行策略为 DuplicateWeedout
LooseScan
将 In 子查询转为 semi-join 时，如果采用的是 LooseScan 执行策略
FirstMatch(tbl_name)
在将 In 子查询转为 semi-join 时，如果采用的是 FirstMatch 执行策略
2 Json格式的执行计划
EXPLAIN 语句输出中缺少了一个衡量执行计划好坏的重要属性 —— 成本
在 EXPLAIN 单词和真正的查询语句中间加上 FORMAT=JSON 。
read_cost 是由下边这两部分组成的：
IO 成本
检测 rows × (1 - filter) 条记录的 CPU 成本
eval_cost 是这样计算的：
检测 rows × filter 条记录的成本。
3 Extented EXPLAIN
使用 EXPLAIN 语句查看了某个查询的执行计划后，紧接着 还可以使用 SHOW WARNINGS 语句查看与这个查询的执行计划有关的一些扩展信息
第17章 神兵利器-optimizer trace表的神器功效
SHOW VARIABLES LIKE 'optimizer_trace';
enabled 值为 off ，表明这个功能默认是关闭的。
方便的查看优化器生成执行计划的整个过程，这个功能的开启与关闭由系统变量 optimizer_trace 决定
OPTIMIZER_TRACE 表4个列
QUERY ：表示我们的查询语句。
TRACE ：表示优化过程的JSON格式文本。
优化过程大致分为了三个阶段：
prepare 阶段
optimize 阶段
execute 阶段
MISSING_BYTES_BEYOND_MAX_MEM_SIZE ：由于优化过程可能会输出很多，如果超过某个限制时，多余的文本将不会被显示，这个字段展示了被忽略的文本字节数。
INSUFFICIENT_PRIVILEGES ：表示是否没有权限查看优化过程，默认值是0，只有某些特殊情况下才会是1 ，我们暂时不关心这个字段的值。
使用步骤
1. 打开optimizer trace功能 (默认情况下它是关闭的)
SET optimizer_trace="enabled=on";
2. 输入查询语句
SELECT ...;
3. 从OPTIMIZER_TRACE表中查看上一个查询的优化过程
SELECT * FROM information_schema.OPTIMIZER_TRACE;
4. 关闭optimizer trace功能
SET optimizer_trace="enabled=off";
第18章 调节磁盘和CPU的矛盾-InnoDB的Buffer
1 缓存的重要性
2 InnoDB的Buffer Pool
2.1 啥是个Buffer Pool
MySQL 服务器启动的时候就向操作系统申请了一片连续的内存，他 们给这片内存起了个名，叫做 Buffer Pool （中文名是 缓冲池 ）
默认128M，最小5M
[server]
innodb_buffer_pool_size = 268435456（设置为256M）
2.2 Buffer Pool内部组成
缓存页
大小和在磁盘上默认的页大小是一样的，都是 16KB
控制块
每个缓存页对应的控制信息占用的内存大小是相同的，我们就把每个页对应的控制信息占用的一块内存
控制块和缓存页是一一对应的，它们都被存放到 Buffer Pool 中，其中控制块被存放到 Buffer Pool 的前边，缓存页被存放到 Buffer Pool 后边
如果你把 Buffer Pool 的大小设置的刚刚好的话，也可能不会
产生 碎片
2.3 free链表的管理
把所有空闲的缓存页对应的控制块作为一个节点放到一个链表中，这个链表也可以被称作 free链表 （或者说空闲链表）。
为了管理好这个 free链表 ，特意为这个链表定义了一个 基节点 ，里边儿包含着链表的头
节点地址，尾节点地址，以及当前链表中节点的数量等信息。（每个基节点只占用40字节大小，不包含在Buffer Pool）
2.4 缓存页的哈希处理
表空间号 + 页号 作为 key ， 缓存页 作为 value 创建一个哈希表
2.5 flush链表的管理
修改了 Buffer Pool 中某个缓存页的数据，那它就和磁盘上的页不一致了，这样的缓存页也被称为 脏 页 （英文名： dirty page ）。
链表的构造和 free链表 差不多
2.6 LRU链表的管理
1 缓存不够的窘境
需要清除一些旧的缓存页
2 简单的LRU链表
创建一个链表，由于这个链表是为了 按照最近最少使用 的原则去淘汰缓存页的，所以这个链表可以被称为 LRU链表 （LRU的英文全称：Least Recently Used）
处理 LRU链表
如果该页不在 Buffer Pool 中，在把该页从磁盘加载到 Buffer Pool 中的缓存页时，就把该缓存页对应的控制块 作为节点塞到链表的头部。
如果该页已经缓存在 Buffer Pool 中，则直接把该页对应的 控制块 移动到 LRU链表 的头部。
只要我们使用到某个缓存页，就把该缓存页调整到 LRU链表 的头部，这样 LRU链表 尾部就是最近最少使用的缓存页喽
3 划分区域的LRU链表
简单LRU链表存在两个问题
预读 （ read ahead ）
线性预读
随机预读
需要扫描全表的查询语句
数据量大的话，总是大换血，大大降低缓存命中率
简单LRUl链表导致
加载到 Buffer Pool 中的页不一定被用到。
如果非常多的使用频率偏低的页被同时加载到 Buffer Pool 时，可能会把那些使用频率非常高的页从Buffer Pool 中淘汰掉。
解决问题初方案，按照一定比例分成两截
热数据（young区域）
冷数据（old区域）
按照比例分，看系统变量 innodb_old_blocks_pct 的值来确定 old 区域的比例
进一步优化
针对预读的页面可能不进行后续访情况的优化
当磁盘上的某个页面在初次加载到Buffer Pool中的某个缓存页时，该缓存页对应 的控制块会被放到old区域的头部。
针对全表扫描时，短时间内访问大量使用频率非常低的页面情况的优化
个间隔时间是由系统变量innodb_old_blocks_time（默认值是 1000｜毫秒）
在对某个处在 old 区域的缓存页进行第一次访问时就在它对应的控制块中记录下来这个访问时间，如果后续的访问时间与第一次访问的时间在某个时间间隔内，那么该页面就不会被从old区域移动到young区域的头部，否则将它移动到young区域的头部。
4 更进一步优化LRU链表
为了不频繁把young区缓存页移到头部，只对1/4后的缓存页进行移动
2.7 其他的一些链表
2.8 刷新脏页到磁盘
从 LRU链表 的冷数据中刷新一部分页面到磁盘。BUF_FLUSH_LRU
从 flush链表 中刷新一部分页面到磁盘。BUF_FLUSH_LIST
刷新单个页面到磁盘中的刷新方式被称之为 BUF_FLUSH_SINGLE_PAGE 。
2.9 多个Buffer Pool实例
以在 Buffer Pool 特别大的时候，我们可以把它们拆分成若干个小的 Buffer Pool ，每个 Buffer Pool 都称为一个 实例
设置 innodb_buffer_pool_instances
每个 Buffer Pool 实例实际占内存空间公式
innodb_buffer_pool_size/innodb_buffer_pool_instances
当innodb_buffer_pool_size的值小于1G的时候设置多个实例是无效的，InnoDB会默认把
innodb_buffer_pool_instances 的值修改为1
2.10 innodb_buffer_pool_chunk_size
说一个 Buffer Pool 实例其实是由若干个 chunk 组成的，一个 chunk 就代表一片连续的内存空间
如图Buffer Pool 就是由2个实例组成的，每个实例中又包含2个 chunk
innodb_buffer_pool_chunk_size的值只能在服务器启动时指定，在服务器运行过程中是不可以修改的。
默认值是 134217728 ，也就是 128M 。
2.11 配置Buffer Pool时的注意事项
innodb_buffer_pool_size 必须是 innodb_buffer_pool_chunk_size × innodb_buffer_pool_instances 的倍数（这主要是想保证每一个 Buffer Pool 实例中包含的 chunk 数量相同）。
如果在服务器启动时， innodb_buffer_pool_chunk_size × innodb_buffer_pool_instances 的值已经大 于 innodb_buffer_pool_size 的值，那么 innodb_buffer_pool_chunk_size 的值会被服务器自动设置为 innodb_buffer_pool_size/innodb_buffer_pool_instances 的值。
2.12 Buffer Pool中存储的其它信息
存储锁信息、自适应哈希索引等
2.13 查看Buffer Pool的状态信息
mysql> SHOW ENGINE INNODB STATUS\G
Total memory allocated
代表 Buffer Pool 向操作系统申请的连续内存空间大小，包括全部控制块、缓存页、以及碎片的大小。
Dictionary memory allocated
为数据字典信息分配的内存空间大小，注意这个内存空间和 Buffer Pool没啥关系，不包括在 Total memory allocated 中。
Buffer pool size
该 Buffer Pool 可以容纳多少缓存 页 ，注意，单位是 页
Free buffers
当前 Buffer Pool 还有多少空闲缓存页，也就是 free链表 中还有多少个节点
Database pages
LRU 链表中的页的数量，包含 young 和 old 两个区域的节点数量
Old database pages
LRU 链表 old 区域的节点数量
Modified db pages
脏页数量，也就是 flush链表 中节点的数量
Pending reads
正在等待从磁盘上加载到 Buffer Pool 中的页面数量
Pending writes LRU
即将从 LRU 链表中刷新到磁盘中的页面数量。
Pending writes flush list
即将从 flush 链表中刷新到磁盘中的页面数量。
Pending writes single page
即将以单个页面的形式刷新到磁盘中的页面数量。
Pages made young
LRU 链表中曾经从 old 区域移动到 young 区域头部的节点数量。
Page made not young
old页不符合时间间隔，无法去到young区头部时+1
youngs/s
每秒从 old 区域被移动到 young 区域头部的节点数量。
non-youngs/s
表每秒由于不满足时间限制而不能从 old 区域移动到 young 区域头部的节点数量
Pages read 、 created 、 written
代表读取，创建，写入了多少页。后边跟着读取、创建、写入的速率。
Buffer pool hit rate
表示在过去某段时间，平均访问1000次页面，有多少次该页面已经被缓存到Buffer Pool 了。
young-making rate
在过去某段时间，平均访问1000次页面，有多少次访问使页面移动到 young 区域的头部了（包括old 也包括young的移到头部）
not (young-making rate
在过去某段时间，平均访问1000次页面，有多少次访问没有使页面移动到 young 区域的头部。（同上）
LRU len
代表 LRU链表 中节点的数量。
unzip_LRU
unzip_LRU链表 中节点的数量
I/O sum ：最近50s读取磁盘页的总数。
I/O cur ：现在正在读取的磁盘页数量。
I/O unzip sum ：最近50s解压的页面数量。
I/O unzip cur ：正在解压的页面数量。
3 总结
第19章 从猫爷被杀说起-事务简介
1 事务的起源
1.1 原子性（Atomicity）
要么全做，要么全不做的规则
1.2 隔离性（Isolation）
不仅要保证这些操作以 原子性 的方式执行完成，而且要保证其它的状态转换不会影响到本次状态转换
1.3 一致性（Consistency）
数据库某些操作的原子性和隔离性都是保证一致性的一种手段，在操作执行完成后保证符合所有既定的约束则是一种结果
1.4 持久性（Durability）
转换对应的数据库操作所修改的数据都应该在磁盘上保留下来，不论之后发生了什么事故，本次转换造成的影响都不应该被丢失掉
2 事务的概念
把需要保证 原子性 、 隔离性 、 一致性 和 持久性 的一个或多个数据库操作称之为一个 事务 （英文名是： transaction ）。
事务的状态
活动的（active）
事务对应的数据库操作正在执行过程中
部分提交的（partially committed）
当事务中的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并没有刷新到磁盘时
失败的（failed）
当事务处在 活动的 或者 部分提交的 状态时，可能遇到了某些错误（数据库自身的错误、操作系统错误或者直接断电等）而无法继续执行，或者人为的停止当前事务的执行
中止的（aborted）
当 回滚 操作执行完毕时，也就是数据库恢复到了执行事务之前的状态
提交的（committed）
当一个处在 部分提交的 状态的事务将修改过的数据都同步到磁盘上之后
只有当事务处于提交的或者中止的状态时，一个事务的生命周期才算是结束了。
3 MySQL中事务的语法
3.1 开启事务
BEGIN [WORK];
START TRANSACTION;（作用一样，功能更多）
修饰符
READ ONLY
标识当前事务是一个只读事务，也就是属于该事务的数据库操作只能读取数据，而不能修改数据。
READ WRITE
标识当前事务是一个读写事务，也就是属于该事务的数据库操作既可以读取数据，也可以修改数据。
WITH CONSISTENT SNAPSHOT
启动一致性读
注意默认情况
3.2 提交事务
COMMIT [WORK]
3.3 手动中止事务
ROLLBACK [WORK]
中止并回滚一个事务
ROLLBACK 语句是我们程序员手动的去回滚事务时才去使用的，如果事务在执行过程中遇到了某些错误而无法继续执行的话，事务自身会自动的回滚。
3.4 支持事务的存储引擎
前只有 InnoDB 和 NDB 存储引擎支持
如果某个事务中包含了修改使用不支持事务的存储引擎的表，那么对该使用不支持事务的存储引擎的表所做的修改将无法进行回滚。
3.5 自动提交
系统变量 autocommit
SHOW VARIABLES LIKE 'autocommit';
默认值为 ON
关闭自动提交的方法
显式的的使用 START TRANSACTION 或者 BEGIN 语句开启一个事务。
SET autocommit = OFF;
写入的多条语句就算是属于同一个事务了，直到我们显式的写出 COMMIT 语句来把这个事务提交掉，或者显式的写出 ROLLBACK 语句来把这个事务回滚掉。
3.6 隐式提交
定义或修改数据库对象的数据定义语言（Data definition language，缩写为： DDL ）。
使用 CREATE 、ALTER 、 DROP 等语句去修改这些所谓的数据库对象（数据库 、 表 、 视图 、 存储过程 等等）
隐式使用或修改 mysql 数据库中的表
使用 ALTER USER 、 CREATE USER 、 DROP USER 、 GRANT 、 RENAME USER 、 REVOKE 、 SET PASSWORD 等语句
事务控制或关于锁定的语句
当我们在一个事务还没提交或者回滚时就又使用 START TRANSACTION 或者 BEGIN 语句开启了另一个事务时，会隐式的提交上一个事务
当前的 autocommit 系统变量的值为 OFF ，我们手动把它调为 ON 时
使用 LOCK TABLES 、 UNLOCK TABLES 等关于锁定的语句
加载数据的语句
使用 LOAD DATA 语句来批量往数据库中导入数据时
关于 MySQL 复制的一些语句
使用 START SLAVE 、 STOP SLAVE 、 RESET SLAVE 、 CHANGE MASTER TO 等语句时
其它的一些语句
使用 ANALYZE TABLE 、 CACHE INDEX 、 CHECK TABLE 、 FLUSH 、 LOAD INDEX INTO CACHE 、 OPTIMIZE TABLE 、 REPAIR TABLE 、 RESET 等语句
3.7 保存点
在事务对应的数据库语句中打几个点，我们在调用 ROLLBACK 语句时可以指定会滚到哪个点，而不是回到最初的原点。
增加保存点
语法：SAVEPOINT 保存点名称;
回滚到某个保存点
语法：ROLLBACK [WORK] TO [SAVEPOINT] 保存点名称;
如果 ROLLBACK 语句后边不跟随保存点名称的话，会直接回滚到事务执行之前的状态。
删除保存点
语法：RELEASE SAVEPOINT 保存点名称;
第20章 说过的话就一定要办到-redo日志（上）
1 事先说明
2 redo日志是个啥
为什么有这个日志
如何保证这个 持久性
简单粗暴的做法
在事务提交完成之前把该事务所修改的所有页面都刷新到磁盘
问题
刷新一个完整的数据页太浪费了
随机IO刷起来比较慢
让已经提交了的事务对数据库中数据所做的修改永久生效，即使后来系统崩溃，在重启后也能把这种修改恢复出来。
把修改了哪些东西记录一下就好
优点
redo 日志占用的空间非常小
redo 日志是顺序写入磁盘的
3 redo日志格式
通用结构
子主题记录了一下事务对数据库做了哪些修改
type ：该条 redo 日志的类型。
space ID ：表空间ID。
page number ：页号。
data ：该条 redo 日志的具体内容。
3.1 简单的redo日志类型
物理日志
记录一下在某个页面的某个偏移量处修改了几个字节的值，具体被修改的内容是啥就好了
日志类型
MLOG_1BYTE （ type 字段对应的十进制数字为 1 ）：表示在页面的某个偏移量处写入1个字节的 redo 日志类型。
MLOG_2BYTE （ type 字段对应的十进制数字为 2 ）：表示在页面的某个偏移量处写入2个字节的 redo 日志类型。
MLOG_4BYTE （ type 字段对应的十进制数字为 4 ）：表示在页面的某个偏移量处写入4个字节的 redo 日志类型。
MLOG_8BYTE （ type 字段对应的十进制数字为 8 ）：表示在页面的某个偏移量处写入8个字节的 redo 日志类型。
MLOG_1BYTE 、 MLOG_2BYTE 、 MLOG_4BYTE 类型的 redo 日志结构和 MLOG_8BYTE 的类似
MLOG_WRITE_STRING （ type 字段对应的十进制数字为 30 ）：表示在页面的某个偏移量处写入一串数据。
是因为不能确定写入的具体数据占用多少字节，所以需要在日志结构中添加一个 len 字段
3.2 复杂一些的redo日志类型
物理日志有局限性
一条简单的插入语句，可能修改的地方就很多
可能更新 Page Directory 中的槽信息。
Page Header 中的各种页面统计信息，比如 PAGE_N_DIR_SLOTS 表示的槽数量可能会更改， PAGE_HEAP_TOP代表的还未使用的空间最小地址可能会更改， PAGE_N_HEAP 代表的本页面中的记录数量可能会更改
需要更新上一条记录的记录头信息中的 next_record 属性来维护这个单向链表
...
方案一：在每个修改的地方都记录一条 redo 日志。
方案二：将整个页面的 第一个被修改的字节 到 最后一个修改的字节 之间所有的数据当成是一条物理 redo日志中的具体数据。
复杂日志
日志类型
MLOG_REC_INSERT （对应的十进制数字为 9 ）：表示插入一条使用非紧凑行格式的记录时的 redo 日志类型。
MLOG_COMP_REC_INSERT （对应的十进制数字为 38 ）：表示插入一条使用紧凑行格式的记录时的 redo 日志类型。

MLOG_COMP_PAGE_CREATE （ type 字段对应的十进制数字为 58 ）：表示创建一个存储紧凑行格式记录的页面的 redo 日志类型。
MLOG_COMP_REC_DELETE （ type 字段对应的十进制数字为 42 ）：表示删除一条使用紧凑行格式记录的redo 日志类型。
MLOG_COMP_LIST_START_DELETE （ type 字段对应的十进制数字为 44 ）：表示从某条给定记录开始删除页面中的一系列使用紧凑行格式记录的 redo 日志类型。
MLOG_COMP_LIST_END_DELETE （ type 字段对应的十进制数字为 43 ）：与 MLOG_COMP_LIST_START_DELETE类型的 redo 日志呼应，表示删除一系列记录直到 MLOG_COMP_LIST_END_DELETE 类型的 redo 日志对应的记录为止。
MLOG_ZIP_PAGE_COMPRESS （ type 字段对应的十进制数字为 51 ）：表示压缩一个数据页的 redo 日志类型。
物理层面
这些日志都指明了对哪个表空间的哪个页进行了修改。
逻辑层面
在系统奔溃重启时，并不能直接根据这些日志里的记载，将页面内的某个偏移量处恢复成某个数据，而是需要调用一些事先准备好的函数，执行完这些函数后才可以将页面恢复成系统奔溃前的样子。
3.3 redo日志格式小结
redo日志会把事务在执行过程中对数据库所做的所有修改都记录下来，在之后系统奔溃重启后可以把事务所做的任何修改都恢复出来
4 Mini-Transaction
4.1 以组的形式写入redo日志
不可分割的组
更新 Max Row ID 属性时产生的 redo 日志是不可分割的。
向聚簇索引对应 B+ 树的页面中插入一条记录时产生的 redo 日志是不可分割的。
向某个二级索引对应 B+ 树的页面中插入一条记录时产生的 redo 日志是不可分割的。
还有其他的一些对页面的访问操作时产生的 redo 日志是不可分割的
为什么要有组？
如果在悲观插入的过程中只记录了一部分 redo日志，那么在系统奔溃重启时会将索引对应的 B+ 树恢复成一种不正确的状态
如何保证操作B+树原子性
多条redo日志操作
MLOG_MULTI_REC_END ， type 字段对应的十进制数字为 31 ，该类型的 redo 日志结构很简单，只有一个 type 字段
系统奔溃重启进行恢复时，只有当解析到类型为 MLOG_MULTI_REC_END 的 redo 日志，才认为解析到了一组完整的 redo 日志，才会进行恢复。否则的话直接放弃前边解析到的 redo 日志。
只生成一条 redo 日志
如果 type 字段的第一个比特位为 1 ，代表该需要保证原子性的操作只产生了单一的一条 redo 日志，否则表示该需要保证原子性的操作产生了一系列的 redo 日志。
4.2 Mini-Transaction的概念
对底层页面中的一次原子访问的过程称之为一个 Mini-Transaction ，简称 mtr
一个事务可以包含若干条语句，每一条语句其实是由若干个 mtr 组成，每一个 mtr 又可以包含若干条 redo 日志
5 redo日志的写入过程
5.1 redo log block
通过 mtr 生成的 redo 日志都放在了大小为 512字节的 页 中。把用来存储 redo 日志的页称为 block
一个 redo log block
管理信息
log block header
LOG_BLOCK_HDR_NO ：每一个block都有一个大于0的唯一标号，本属性就表示该标号值。
LOG_BLOCK_HDR_DATA_LEN ：表示block中已经使用了多少字节，初始值为 12 （因为 log block body 从第12个字节处开始）。随着往block中写入的redo日志越来也多，本属性值也跟着增长。如果 log block body已经被全部写满，那么本属性的值被设置为 512 。
LOG_BLOCK_FIRST_REC_GROUP ：一条 redo 日志也可以称之为一条 redo 日志记录（ redo log record ），一个 mtr 会生产多条 redo 日志记录，这些 redo 日志记录被称之为一个 redo 日志记录组（ redo log record group ）。 LOG_BLOCK_FIRST_REC_GROUP 就代表该block中第一个 mtr 生成的 redo 日志记录组的偏移量（其实也就是这个block里第一个 mtr 生成的第一条 redo 日志的偏移量）。
LOG_BLOCK_CHECKPOINT_NO ：表示所谓的 checkpoint 的序号， checkpoint 是我们后续内容的重点。
log block trailer
LOG_BLOCK_CHECKSUM ：表示block的校验值，用于正确性校验
5.2 redo日志缓冲区
在服务器启动时就向操作系统申请了一大片称之为 redo log buffer 的连续内存空间，翻译成中文就是 redo日志缓冲区（ log buffer）
log buffer内存空间被划分成若干个连续的 redo log block
启动参数 innodb_log_buffer_size 来指定 log buffer 的大小（MySQL 5.7.21 这个版本默认值为 16MB）
5.3 redo日志写入log buffer
应该写在哪个block 的哪个偏移量处？
全局变量 buf_free
不是每生成一条 redo 日志，就将其插入到 log buffer 中，而是每个 mtr 运行过程中产生的日志先暂时存到一个地方，当该 mtr 结束的时候，将过程中产生的一组 redo 日志再全部复制到 log buffer 中。
多个事务下写入过程
事务 T1 的两个 mtr 分别称为 mtr_T1_1 和 mtr_T1_2 。 事务 T2 的两个 mtr 分别称为 mtr_T2_1 和 mtr_T2_2 。
不同事务的 mtr 可能是交替写入 log buffer 的
第21章 说过的话就一定要办到-redo日志（下）
1 redo日志文件
1.1 redo日志刷盘时机
log buffer 空间不足时
事务提交时
为了保证持久性，必须要把修改这些页面对应的 redo 日志刷新到磁盘。
后台线程不停的刷刷刷
后台有一个线程，大约每秒都会刷新一次 log buffer 中的 redo 日志到磁盘。
正常关闭服务器时
做所谓的 checkpoint 时
...
1.2 redo日志文件组
默认
MySQL 的数据目录（使用 SHOW VARIABLES LIKE 'datadir' 查看）下默认有两个名为 ib_logfile0 和ib_logfile1 的文件， log buffer 中的日志默认情况下就是刷新到这两个磁盘文件中
启动参数
innodb_log_group_home_dir
指定了 redo 日志文件所在的目录，默认值就是当前的数据目录。
innodb_log_file_size
数指定了每个 redo 日志文件的大小(MySQL 5.7.21 默认值为 48MB )
innodb_log_files_in_group
指定 redo 日志文件的个数，默认值为2，最大值为100。
以 ib_logfile[数字] （ 数字 可以是 0 、 1 、 2 ...）的形式进行命名；
总共的 redo 日志文件大小其实就是： innodb_log_file_size × innodb_log_files_in_group 。
1.3 redo日志文件格式
将log buffer中的redo日志刷新到磁盘的本质就是把block的镜像写入日志文件中，所以 redo 日志文件其实也是由若干个 512 字节大小的block组成
redo 日志文件组中的每个文件大小/格式都一样，分两部分；
循环是从每个文件的2048个字节开始。
前2048个字节，也就是前4个block是用来存储一些管理信息的。
log file header ：描述该 redo 日志文件的一些整体属性；
checkpoint1 ：记录关于 checkpoint 的一些属性
checkpoint2 ：结构和 checkpoint1 一样。
从第2048字节往后是用来存储 log buffer 中的block镜像的。
2 Log Sequeue Number
 Log Sequeue Number 的全局变量，就是： 日志序列号 ，简称 lsn；
规定初始的 lsn 值为 8704 （也就是一条 redo 日志也没写入时，lsn 的值为 8704 ）。
统计 lsn 的增长量时，是按照实际写入的日志量加上占用的 log block header 和 log block trailer 来计算的；
每一组由mtr生成的redo日志都有一个唯一的LSN值与其对应，LSN值越小，说明redo日志产生的越早。
系统第一次启动后初始化 log buffer 
mtr_1 产生的 redo 日志量为200字节，那么 lsn 就要在 8716 的基础上增加 200 ，变为8916
mtr_2 产生的 redo 日志量为1000字节，写入 log buffer,以 lsn 的值需在8916 的基础上增加 1000 + 12×2 + 4× 2 = 1032 。
2.1 flushed_to_disk_lsn
buf_next_to_write 的全局变量
全局变量flushed_to_disk_lsn
系统第一次启动后，向 log buffer 中写入了 mtr_1 、 mtr_2 、 mtr_3 这三个 mtr 产生的 redo 日志
随后进行将 log buffer 中的block刷新到 redo 日志文件的操作，假设将 mtr_1 和 mtr_2 的日志刷新到磁盘
2.2 lsn值和redo日志文件偏移量的对应关系
初始时的 LSN 值是 8704 ，对应文件偏移量 2048 ，之后每个 mtr 向磁盘中写入多少字节日志， lsn 的值就增长多少。
2.3 flush链表中的LSN
把在mtr执行过程中可能修改过的页面加入到Buffer Pool的flush链表。
两个关于页面何时修改的属性
oldest_modification ：如果某个页面被加载到 Buffer Pool 后进行第一次修改，那么就将修改该页面的mtr 开始时对应的 lsn 值写入这个属性。
newest_modification ：每修改一次页面，都会将修改该页面的 mtr 结束时对应的 lsn 值写入这个属性。也就是说该属性表示页面最近一次修改后对应的系统 lsn 值。
举例
mtr_1 执行过程中修改了 页a ，那么在 mtr_1 执行结束时，就会将 页a 对应的控制块加入到 flush链表 的头部。
（把 oldest_modification 缩写成了 o_m ，把 newest_modification 缩写成了 n_m）
mtr_2 执行过程中又修改了 页b 和 页c 两个页面，那么在 mtr_2 执行结束时，就会将 页b 和 页c对应的控制块都加入到 flush链表 的头部。
mtr_3 执行过程中修改了 页b 和 页d ，不过 页b 之前已经被修改过了，所以它对应的控制块已经被插入到了 flush 链表，所以在 mtr_3 执行结束时，只需要将 页d 对应的控制块都加入到 flush链表 的头部即可。
总结
flush链表中的脏页按照修改发生的时间顺序进行排序，也就是按照oldest_modification代表的LSN值进行排序，被多次更新的页面不会重复插入到flush链表中，但是会更新newest_modification属性的值
3 checkpoint
为什么有checkpoint？
判断某些redo日志占用的磁盘空间是否可以覆盖的依据就是它对应的脏页是否已经刷新到磁盘里。
举例
mtr_1 和 mtr_2 生成的 redo 日志都已经被写到了磁盘上，但是它们修改的脏页仍然留在 Buffer Pool
如果 页a 被刷新到了磁盘，那么它对应的控制块就会从 flush链表 中移除
引出全局变量 checkpoint_lsn
代表当前系统中可以被覆盖的 redo 日志总量是多少，初始值也是8704
checkpoint
现在 页a 被刷新到了磁盘， mtr_1 生成的 redo 日志就可以被覆盖了，可以进行一个增加checkpoint_lsn 的操作，这个过程称之为做一次 checkpoint 。
做一次 checkpoint，两步
步骤一：计算一下当前系统中可以被覆盖的 redo 日志对应的 lsn 值最大是多少。
就是flush链表 的尾节点的 oldest_modification 值赋值给 checkpoint_lsn。
步骤二：将 checkpoint_lsn 和对应的 redo 日志文件组偏移量以及此次 checkpint 的编号写到日志文件的管理信息（就是 checkpoint1 或者 checkpoint2 ）中。
记录完 checkpoint 的信息之后， redo 日志文件组中各个 lsn 值的关系
变量 checkpoint_no
目前系统做了多少次 checkpoint，每做一次checkpoint ，该变量的值就加1。
每一个 redo 日志文件都有 2048 个字节的管理信息，但是上述关于checkpoint的信息只会被写到日志文件组的第一个日志文件的管理信息中。不过我们是存储到 checkpoint1 中还是 checkpoint2 中呢？当 checkpoint_no 的值是偶数时，就写到 checkpoint1 中，是奇数时，就写到checkpoint2 中。
3.1 批量从flush链表中刷出脏页
一般都是后台线程刷脏，什么情况会用户线程刷脏呢？
3.2 查看系统中的各种LSN值
SHOW ENGINE INNODB STATUS\G
Log sequence number ：代表系统中的 lsn 值，也就是当前系统已经写入的 redo 日志量，包括写入 log buffer 中的日志。
Log flushed up to ：代表 flushed_to_disk_lsn 的值，也就是当前系统已经写入磁盘的 redo 日志量。
Pages flushed up to ：代表 flush链表 中被最早修改的那个页面对应的 oldest_modification 属性值。
Last checkpoint at ：当前系统的 checkpoint_lsn 值。
4 innodb_flush_log_at_trx_commit的用法
系统变量有3个可选的值
0 ：在事务提交时不立即向磁盘中同步 redo 日志，这个任务是交给后台线程做的。
1 ：在事务提交时需要将 redo 日志同步到磁盘，可以保证事务的 持久性 。 默认值为1。
2 ：在事务提交时需要将 redo 日志写到操作系统的缓冲区中，但并不需要保证将日志真正的刷新到磁盘。
5 崩溃恢复
5.1 确定恢复的起点
redo 日志文件组的第一个文件的管理信息中有两个block都存储了 checkpoint_lsn 的信息，要选取最近发生的那次checkpoint的信息。
5.2 确定恢复的终点
普通block的 log block header 部分有一个称之为 LOG_BLOCK_HDR_DATA_LEN 的属性
5.3 怎么恢复
举例
redo 日志文件中有5条 redo 日志
使用哈希表
把 space ID 和 page number 相同的 redo日志放到哈希表的同一个槽里
之后就可以遍历哈希表，因为对同一个页面进行修改的 redo 日志都放在了一个槽里，所以可以一次性将一
个页面修复好（避免了很多读取页面的随机IO），这样可以加快恢复速度
同一个页面的 redo 日志是按照生成时间顺序进行排序的，所以恢复的时候也是按照这个顺序进行恢复
跳过已经刷新到磁盘的页面
可能后台线程又不断的从 LRU链表 和 flush链表 中将一些脏页刷出 Buffer Pool 。
怎么知道某个 redo 日志对应的脏页是否在奔溃发生时已经刷新到磁盘了呢？
页对应的FIL_PAGE_LSN 代表的 lsn 值肯定大于 checkpoint_lsn 的值就是崩溃前刷好的
6 遗漏的问题：LOG_BLOCK_HDR_NO是如何计算的
公式：((lsn / 512) & 0x3FFFFFFFUL) + 1
第22章 后悔了怎么办-undo日志（上）
1 事务回滚的需求
把回滚时所需的东西都给记下来
为了回滚而记录的这些东东称之为撤销日志（ undo log｜undo日志）
2 事务id
2.1 给事务分配id的时机
分配方式
对于只读事务来说，只有在它第一次对某个用户创建的临时表执行增、删、改操作时才会为这个事务分配一个 事务id ，否则的话是不分配 事务id 的。
（select语句用到的内部临时表不会分配事务id）
对于读写事务来说，只有在它第一次对某个表（包括用户创建的临时表）执行增、删、改操作时才会为这个事务分配一个 事务id ，否则的话也是不分配 事务id 的。（在这个事务中全是查询语句，并没有执行增、删、改的语句，那也就意味着这个事务并不会被分配一个 事务id 。）
只有在事务对表中的记录做改动时才会为这个事务分配一个唯一的 事务id 。
2.2 事务id是怎么生成的
是一个递增的数字，与row_id的生成策略很像
2.3 trx_id隐藏列
聚簇索引的记录除了会保存完整的用户数据以外，而且还会自动添加名为trx_id、roll_pointer的隐藏列，如果用户没有在表中定义主键以及UNIQUE键，还会自动添加一个名为row_id的隐藏列。（某个对这个聚簇索引记录做改动的语句所在的事务对应的 事务id）
3 undo日志的格式
InnoDB 存储引擎在实际进行增、删、改一条记录时，都需要先把对应的 undo日志 记下来。
3.1 INSERT操作对应的undo日志
类型为TRX_UNDO_INSERT_REC 的 undo日志
undo no 在一个事务中是从 0 开始递增的，也就是说只要事务没提交，每生成一条 undo日志 ，那么该条日志的 undo no 就增1。
如果记录中的主键只包含一个列，那么在类型为 TRX_UNDO_INSERT_REC 的 undo日志 中只需要把该列占用的存储空间大小和真实值记录下来，如果记录中的主键包含多个列，那么每个列占用的存储空间大小和对应的真实值都需要记录下来（图中的 len 就代表列占用的存储空间大小， value 就代表列的真实值）。
只记录主键，删除聚簇索引会顺带把所有的二级索引相应的记录也一并删除
举例
开启事务，插入两条数据
第一条 undo日志 的 undo no 为 0 ，记录主键占用的存储空间长度为 4 ，真实值为 1
第二条 undo日志 的 undo no 为 1 ，记录主键占用的存储空间长度为 4 ，真实值为 2 。
（与第一条 undo日志 对比， undo no 和主键各列信息有不同）
roll pointer隐藏列的含义
本质上就是一个指向记录对应的 undo日志 的一个指针
3.2 DELETE操作对应的undo日志
页面信息补充
正常记录链表
垃圾链表
删除阶段举例
简化版的示意图，只把记录的 delete_mask 标志位展示了出来
删除语句执行分两个阶段
阶段一：仅仅将记录的 delete_mask 标识位设置为 1 ，其他的不做修改（其实会修改记录的 trx_id 、roll_pointer 这些隐藏列的值）。【delete mark 】

「正常记录链表 中的最后一条记录的 delete_mask 值被设置为 1 ，但是并没有被加入到 垃圾链表 。也就是此时记录处于一个 中间状态。
在删除语句所在的事务提交之前，被删除的记录一直都处于这种所谓的 中间状态 。」
阶段二：当该删除语句所在的事务提交之后，会有专门的线程后来真正的把记录删除掉。【purge】
将被删除记录加入到 垃圾链表 时，实际上加入到链表的头节点处，会跟着修
改 PAGE_FREE 属性的值。
补充说明：页面的Page Header部分有一个PAGE_GARBAGE属性，
阶段二完成同时，PAGE_GARBAGE的值会加上删除的记录占用的大小；
每次插入会看PAGE_FREE指向的头节点是否够用，不够用就向页面申请空间，
如果够，则直接插入，若小于头节点的大小，则会产生碎片，碎片大小也会增加进PAGE_GARBAGE；
直到插入记录没有空间了，就开一张临时页进行重排，然后再复制回本页
只需要对阶段一回滚，阶段二已提交
TRX_UNDO_DEL_MARK_REC 类型的 undo日志
old trx_id 和 old roll_pointer 属性
delete mark 操作前，需要把该记录的旧的 trx_id 和 roll_pointer 隐藏列的值都给记到对应的 undo日志
比方说在一个事务中，我们先插入了一条记录，然后又执行对该记录的删除操作
版本链
索引列各列信息
如果某个列被包含在某个索引中，那么它的相关信息就应该被记录
相关信息包括该列在记录中的位置（用 pos 表示），该列占用的存储空间大小（用 len 表示），该列实际值（用 value 表示）。
本质就是<pos, len, value> 的一个列表，主要是用在事务提交后，对该 中间状态记录 做真正删除的阶段二【purge 】中使用的
举例
比如我们把 id 为1的那条记录删除掉
delete mark 操作对应的 undo日志 的结构
undo no
old trx_id和old roll_pointer
索引列各列信息
主键（6 个字节）
idx_key1（5个字节）
index_col_info_len
13=6【主键】+5【idx_key1】+2【自身占用2字节】
3.3 UPDATE操作对应的undo日志
1 不更新主键的情况
就地更新（in-place update）
举例
不能进行 就地更新
可以执行 就地更新
先删除掉旧记录，再插入新记录
注意：这里的删除不是先delete mark 然后purge，而是由用户线程同步执行真正的删除操作，真正删除之后紧接着就要根据各个列更新后的值创建的新记录插入。
新数据小于旧数据
直接重用被加入到 垃圾链表 中的旧记录所占用的存储空间
新数据大于旧数据
需要在页面中新申请空间使用，如果本页面内已经没有可用的空间，就需要进行页面分裂操作，然后再插入新记录。
类型为 TRX_UNDO_UPD_EXIST_REC 的 undo日志
n_updated 属性
本条 UPDATE 语句执行后将有几个列被更新
<pos, old_len, old_value>
分别表示被更新列在记录中的位置、更新前该列占用的存储空间大小、更新前该列的真实值。
索引列各列信息
如果在 UPDATE 语句中更新的列包含索引列，那么也会添加 索引列各列信息 这个部分，否则的话是不会添加这个部分的。
举例
把id为2的那条记录更新一下
更新的列大小都没有改动，就地更新
TRX_UNDO_UPD_EXIST_REC 的 undo日志
undo no
old roll_pointer
索引列各列信息
2 更新主键的情况
聚簇索引中分了两步处理
将旧记录进行 delete mark 操作
与上面不更新主键的删除完全不一样
在 UPDATE语句所在的事务提交前，对旧记录只做一个 delete mark 操作，在事务提交后才由专门的线程做purge操作，把它加入到垃圾链表中。
根据更新后各列的值创建一条新记录，并将其插入到聚簇索引中（需重新定位插入的位置）
每对一条记录的主键值做改动时，会记录2条 undo日志
在对该记录进行 delete mark 操作前，会记录一条类型为 TRX_UNDO_DEL_MARK_REC 的 undo日志 ；
之后插入新记录时，会记录一条类型为 TRX_UNDO_INSERT_REC 的 undo 日志
扩展：为TRX_UNDO_UPD_DEL_REC的undo日志
第23章 后悔了怎么办-undo日志（下）
1 通用链表结构
很多链表有这样的节点结构
Pre Node Page Number 和 Pre Node Offset 的组合就是指向前一个节点的指针
Next Node Page Number 和 Next Node Offset 的组合就是指向后一个节点的指针。
基节点的结构，包含头节点 、 尾节点 以及链表长度信息
List Length 表明该链表一共有多少节点。
First Node Page Number 和 First Node Offset 的组合就是指向链表头节点的指针。
Last Node Page Number 和 Last Node Offset 的组合就是指向链表尾节点的指针。
使用 List Base Node 和 List Node 这两个结构组成的链表
2 FIL_PAGE_UNDO_LOG页面
简称undo页面
Undo Page Header 是 Undo页面 所特有的
TRX_UNDO_PAGE_TYPE ：本页面准备存储什么种类的 undo日志 。
两个大类
TRX_UNDO_INSERT （使用十进制 1 表示）：，一般由 INSERT 语句产生，或者在 UPDATE 语句中有更新主键的情况也会产生此类型的 undo日志 。
TRX_UNDO_UPDATE （使用十进制 2 表示），一般由 DELETE 、 UPDATE 语句产生的 undo日志 属于这个大类。
TRX_UNDO_PAGE_START ：表示在当前页面中是从什么位置开始存储 undo日志 的，或者说表示第一条 undo日志 在本页面中的起始偏移量。
TRX_UNDO_PAGE_FREE ：与上边的 TRX_UNDO_PAGE_START 对应，表示当前页面中存储的最后一条 undo 日志结束时的偏移量，或者说从这个位置开始，可以继续写入新的 undo日志 。
假设现在向页面中写入了3条 undo日志
TRX_UNDO_PAGE_NODE ：代表一个 List Node 结构（链表的普通节点）。
3 Undo页面链表
3.1 单个事务中的Undo页面链表
TRX_UNDO_PAGE_NODE 属性连成了链表；
第一个 Undo页面 给标了出来，称它为 first undo page ，其余的 Undo页面 称之为 normal undo page。
一个事务执行过程中，可能混着执行 INSERT 、 DELETE 、 UPDATE 语句；
同一个 Undo页面 要么只存储 TRX_UNDO_INSERT 大类的 undo日志 ，要么只存储TRX_UNDO_UPDATE 大类的 undo日志；
可能需要2个 Undo页面 的链表，一个称之为 insert undo链表 ，另一个称之为 update undo链表
对普通表和临时表的记录改动时产生的 undo日志 要分别记录；
一个事务中最多有4个以 Undo页面 为节点组成的链表。
按需分配，啥时候需要啥时候再分配，不需要就不分配
刚刚开启事务时，一个 Undo页面 链表也不分配。
当事务执行过程中向普通表中插入记录或者执行更新记录主键的操作之后，就会为其分配一个 普通表的insert undo链表 。
当事务执行过程中删除或者更新了普通表中的记录之后，就会为其分配一个 普通表的update undo链表 。
当事务执行过程中向临时表中插入记录或者执行更新记录主键的操作之后，就会为其分配一个 临时表的insert undo链表 。
当事务执行过程中删除或者更新了临时表中的记录之后，就会为其分配一个 临时表的update undo链表 。
4 undo日志具体写入过程
4.1 段（Segment）的概念
4.2 Undo Log Segment Header
每一个 Undo页面 链表都对应着一个 段 ，称之为 Undo Log Segment 。
first undo page（Undo页面 链表的第一个页面）
比普通页面多了个 Undo Log Segment Header
TRX_UNDO_STATE ：本 Undo页面 链表处在什么状态。
TRX_UNDO_ACTIVE ：活跃状态，也就是一个活跃的事务正在往这个段里边写入 undo日志 。
TRX_UNDO_CACHED ：被缓存的状态。处在该状态的 Undo页面 链表等待着之后被其他事务重用。
TRX_UNDO_TO_FREE ：对于 insert undo 链表来说，如果在它对应的事务提交之后，该链表不能被重用，那么就会处于这种状态。
TRX_UNDO_TO_PURGE ：对于 update undo 链表来说，如果在它对应的事务提交之后，该链表不能被重用，那么就会处于这种状态。
TRX_UNDO_PREPARED ：包含处于 PREPARE 阶段的事务产生的 undo日志 。
TRX_UNDO_LAST_LOG ：本 Undo页面 链表中最后一个 Undo Log Header 的位置
TRX_UNDO_FSEG_HEADER ：本 Undo页面 链表对应的段的 Segment Header 信息（10字节结构，通过这个信息可以找到该段对应的 INODE Entry ）。
TRX_UNDO_PAGE_LIST ： Undo页面 链表的基节点。
基节点只存在于 Undo页面 链表的第一个页面，也就是 first undo page 中
4.3 Undo Log Header
同一个事务向一个 Undo页面 链表中写入的 undo日志 算是一个组
Undo页面 链表的第一个页面
Undo Log Header（undo日志组信息）
TRX_UNDO_TRX_ID ：生成本组 undo日志 的事务 id 。
TRX_UNDO_TRX_NO ：事务提交后生成的一个需要序号，使用此序号来标记事务的提交顺序（先提交的此序号小，后提交的此序号大）。
TRX_UNDO_DEL_MARKS ：标记本组 undo 日志中是否包含由于 Delete mark 操作产生的 undo日志 。
TRX_UNDO_LOG_START ：表示本组 undo 日志中第一条 undo日志 的在页面中的偏移量。
TRX_UNDO_XID_EXISTS ：本组 undo日志 是否包含XID信息。
TRX_UNDO_DICT_TRANS ：标记本组 undo日志 是不是由DDL语句产生的。
TRX_UNDO_TABLE_ID ：如果 TRX_UNDO_DICT_TRANS 为真，那么本属性表示DDL语句操作的表的 table id 。
TRX_UNDO_NEXT_LOG ：下一组的 undo日志 在页面中开始的偏移量。
TRX_UNDO_PREV_LOG ：上一组的 undo日志 在页面中开始的偏移量。
TRX_UNDO_HISTORY_NODE ：一个12字节的 List Node 结构，代表一个称之为 History 链表的节点。
4.4 小结
5 重用Undo页面
Undo页面 链表是否可以被重用的条件
该链表中只包含一个 Undo页面 。
该 Undo页面 已经使用的空间小于整个页面空间的3/4。
两种链表在被重用时的策略
insert undo链表（覆盖）
update undo链表（不能覆盖，只能追加）
6 回滚段
6.1 回滚段的概念
Rollback Segment Header 的页面
TRX_RSEG_MAX_SIZE
TRX_RSEG_HISTORY_SIZE ： History 链表占用的页面数量。
TRX_RSEG_HISTORY ： History 链表的基节点。
TRX_RSEG_FSEG_HEADER ：
本 Rollback Segment 对应的10字节大小的 Segment Header 结构，
通过它可以找到本段对应的 INODE Entry 。
TRX_RSEG_UNDO_SLOTS ：各个 Undo页面 链表的 first undo page 的 页号 集合，也就是 undo slot 集合。
undo slot
各个 Undo页面 链表的 frist undo page 的 页号 ，他们把这些 页号 称之为 undo slot 。
Rollback Segment（回滚段）
每一个 Rollback Segment Header 页面都对应一个回滚段
6.2 从回滚段中申请Undo页面链表
初始情况，Rollback Segment Header 的undo slot 都设置为 FIL_NULL（0xFFFFFFFF）；
表示undo slot 不指向任何页面
事务需要分配
遍历1024个undo slot
如果undo slot 值是FIL_NULL，则分配
如果不是FIL_NULL，则跳过
遍历结束都不是FIL_NULL，则回滚并报错
当事务提交，undo slot的变化
如果该 undo slot 指向的 Undo页面 链表符合被重用的条件，
并将该页面（first undo page）转为 被缓存 状态。
被加入到不同的 undo cached链表
对应的 Undo页面 链表是insert undo链表 则被加入 insert undo cached链表
对应的 Undo页面 链表是update undo链表 则被加入 update undo cached链表
如果不符合被重用的条件
如果对应的 Undo页面 链表是 insert undo链表，状态转为 TRX_UNDO_TO_FREE
之后该 Undo页面 链表对应的段会被释放掉（也就意味着段中的页面可以被挪作他用），
然后把该 undo slot 的值设置为 FIL_NULL 。
如果对应的 Undo页面 链表是 update undo链表，状态转为 TRX_UNDO_TO_PRUGE
将该 undo slot 的值设置为 FIL_NULL ，然后将本次事务写入的一组undo 日志放到所谓的 History链表 中
（需要注意的是，这里并不会将 Undo页面 链表对应的段给释放掉，因为这些 undo 日志还有用呢～）。
6.3 多个回滚段
定义了 128 个回滚段，131072 个 undo slot
系统表空间的第 5 号页面的某个区域包含了128个8字节大小的格子
每个格子8个字节（相当于指针，指向某个表空间的某个页面）
4字节大小的 Space ID ，代表一个表空间的ID。
4字节大小的 Page number ，代表一个页号。
不同的回滚段可能分布在不同的表空间中
从系统到undo 链表
6.4 回滚段的分类
两大类
第 0 号、第 33～127 号回滚段（对普通表）
第 0 号回滚段必须在系统表空间中
（就是说第 0 号回滚段对应的 Rollback Segment Header 页面必须在系统表空间中）
第 33～127 号回滚段既可以在系统表空间中，也可以在自己配置的 undo 表空间中（需要配置）
对普通表的记录做了改动需要分配 Undo页面 链表时，必须从这一类的段中分配相应的 undo slot 。
第 1～32 号回滚段（对临时表）
必须在临时表空间（对应着数据目录中的 ibtmp1 文件）中
为什么分类？
因为undo也是写入页面的过程
redo日志 的类型，比方说 MLOG_UNDO_HDR_CREATE 、 MLOG_UNDO_INSERT 、 MLOG_UNDO_INIT 等等
崩溃后无需还原对临时表的undo操作
在修改针对普通表的回滚段中的Undo页面时，需要记录对应的redo日志，而修改针对临时表的回滚段中的Undo页面时，不需要记录对应的redo日志。
6.5 为事务分配Undo页面链表详细过程
对普通表
获取回滚段
查看cache链表有没有可用undo slot
如果没有缓存可用，则需要分配一个undo slot
还没有就报错
重新分配一个 Undo Log Segment（不是从cache），申请first undo page 
undo日志 写入到上边申请的 Undo页面 链表
对临时表步骤一样
如果一个事务在执行过程中既对普通表的记录做了改动，又对临时表的记录做了改动，那么需要为这个记录分配2个回滚段。
并发执行的不同事务其实也可以被分配相同的回滚段，只要分配不同的undo slot就可以了。
7 回滚段相关配置
7.1 配置回滚段数量
启动参数innodb_rollback_segments 来配置回滚段的数量（1~128）
针对临时表的回滚段数量一直是 32（所以设置33跟1效果一样）
7.2 配置undo表空间
第 33~127 号回滚段可以通过配置放到自定义的 undo表空间 中。
初始化完成，之后就不能再次更改
通过 innodb_undo_directory 指定 undo表空间 所在的目录，如果没有指定该参数，则默认 undo表空间 所在的目录就是数据目录。
通过 innodb_undo_tablespaces 定义 undo表空间 的数量。该参数的默认值为 0 ，表明不创建任何 undo表空间 。
好处
第24章 一条记录的多幅面孔-事务的隔离级别与
1 事前准备
2 事务隔离级别
2.1 事务并发执行遇到的问题
脏写（ Dirty Write ）
如果一个事务修改了另一个未提交事务修改过的数据，那就意味着发生了 脏写
脏读（ Dirty Read ）
如果一个事务读到了另一个未提交事务修改过的数据，那就意味着发生了 脏读
不可重复读（Non-Repeatable Read）
如果一个事务只能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值，那就意味着发生了 不可重复读
幻读（Phantom）
如果一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先 的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来，那就意味着发生了 幻读
重点强调了读取到了之前读取没有获取到的记录
为某个事务读了一个范围的记录，之后别的事务在该范围内插入了新记录，
该事务再次读取该范围的记录时，可以读到新插入的记录，
所以幻读问题准确的说并不是因为读取和写入一条相同记录而产生的
严重性：脏写 > 脏读 > 不可重复读 > 幻读
2.2 SQL标准中的四种隔离级别
设立一些隔离级别，隔离级别越低，越严重的问题就越可能发生。
4个 隔离级别
READ UNCOMMITTED ：未提交读。
READ COMMITTED ：已提交读。
REPEATABLE READ ：可重复读。
SERIALIZABLE ：可串行化。
隔离级别为什么没有脏写？
这是因为脏写这个问题太严重了，不论是哪种隔离级别，都不允许脏写的情况发生。
2.3 MySQL中支持的四种隔离级别
MySQL在REPEATABLE READ隔离级别下，是可以禁止幻读问题的发生的
MySQL 的默认隔离级别为 REPEATABLE READ（可手动修改）
1 如何设置事务的隔离级别
使用 GLOBAL 关键字（在全局范围影响）
只对执行完该语句之后产生的会话起作用。
当前已经存在的会话无效
使用 SESSION 关键字（在会话范围影响）
对当前会话的所有后续的事务有效
该语句可以在已经开启的事务中间执行，但不会影响当前正在执行的事务。
如果在事务之间执行，则对后续的事务有效。
上述两个关键字都不用（只对执行语句后的下一个事务产生影响）
只对当前会话中下一个即将开启的事务有效。
下一个事务执行完后，后续事务将恢复到之前的隔离级别。
该语句不能在已经开启的事务中间执行，会报错的。
启动参数 transaction-isolation
--transaction-isolation=SERIALIZABLE
查看系统变量 transaction_isolation
3 MVCC原理
3.1 版本链
聚簇索引记录中都包含两个必要的隐藏列
（ row_id 并不是必要的，有主键或者非NULL的UNIQUE键时）：
trx_id
roll_pointer
举例
插入该记录的 事务id 为 80
假设之后两个 事务id 分别为 100 、 200 的事务对这条记录进行 UPDATE 操作
roll_pointer 属性连接成一个链表-版本链
版本链的头节点就是当前记录最新的值
每个版本中还包含生成该版本时对应的 事务id
3.2 ReadView
隔离级别怎么实现的？
SERIALIZABLE（锁实现）
READ UNCOMMITTED（读到未提交，读最新即可）
READ COMMITTED 和 REPEATABLE READ（需要 ReadView）
ReadView 中主要包含4个比较重要的内容
m_ids ：在生成 ReadView 时当前系统中活跃的读写事务的 事务id 列表。
min_trx_id ：在生成 ReadView 时当前系统中活跃的读写事务中最小的 事务id ，也就是 m_ids 中的最小值。
max_trx_id ：生成 ReadView 时系统中应该分配给下一个事务的 id 值。「下一个，不是最大值」
creator_trx_id ：生成该 ReadView 的事务的 事务id 。
判断记录的某个版本是否可见
trx_id = creator_trx_id ->可见
着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问
trx_id < min_trx_id ->可见
该版本的事务在当前事务生成 ReadView 前已经提交，所以该版本可以被当前事务访问。
trx_id > max_trx_id ->不可见
该版本的事务在当前事务生成 ReadView 后才开启，所以该版本不可以被当前事务访问。
min_trx_id < trx_id < max_trx_id
trx_id 在 m_ids 列表中 -> 不可见
创建 ReadView 时生成该版本的事务还是活跃的，该版本不可以被访问；
trx_id 不在 m_ids 列表中 -> 可见
创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问。
不可见 则顺着版本链 roll_pointer 往下找，直到没有可见版本，则查询不到
在 MySQL 中， READ COMMITTED 和 REPEATABLE READ 隔离级别的的一个非常大的区别就是它们生成ReadView的时机不同。
1 READ COMMITTED —— 每次读取数据前都生成一个ReadView
使 用READ COMMITTED隔离级别的事务在每次查询开始时都会生成一个独立的ReadView。
举例
1） 有两个 事务id 分别为 100 、 200 的事务在执行
现在有一个使用 READ COMMITTED 隔离级别的事务【SELECT1】
先生成一个 ReadView【m_ids=[100, 200]，min_trx_id 为 100 ， max_trx_id 为 201 ， creator_trx_id 为 0】
最新列“张飞”【trx_id=100 ，在 m_ids 列表内】 -> 不可见
“关羽” 【trx_id=100 ，在 m_ids 列表内】 -> 不可见
“刘备” 【trx_id=80，< min_trx_id】-> 可见
返回给用户的版本就是这条列 name 为 '刘备' 的记录
把 事务id 为 100 的事务提交一下
再到 事务id 为 200 的事务中更新一下表 hero 中 number 为 1 的记录
使用 READ COMMITTED 隔离级别的事务中继续查找这个 number 为 1 的记录【SELECT2】
又会单独生成一个 ReadView 【m_ids=[200]，min_trx_id 为 200 ， max_trx_id 为 201 ， creator_trx_id 为 0】
最新列“诸葛亮”【trx_id=200 ，在 m_ids 列表内】 -> 不可见
“赵云”【trx_id=200 ，在 m_ids 列表内】 -> 不可见
“张飞” 【trx_id=100，< min_trx_id】-> 可见
返回给用户的版本就是这条列 name 为 '张飞' 的记录
2 REPEATABLE READ —— 在第一次读取数据时生成一个ReadView
举例
分析【SELECT1】-> 结果一摸一样
先生成一个 ReadView【m_ids=[100, 200]，min_trx_id 为 100 ， max_trx_id 为 201 ， creator_trx_id 为 0】
最新列“张飞”【trx_id=100 ，在 m_ids 列表内】 -> 不可见
“关羽” 【trx_id=100 ，在 m_ids 列表内】 -> 不可见
“刘备” 【trx_id=80，< min_trx_id】-> 可见
返回给用户的版本就是这条列 name 为 '刘备' 的记录
分析【SELECT2】
在执行 SELECT1 时已经生成过 ReadView 了，所以此时直接复用之前的 ReadView
最新列“诸葛亮”【trx_id=200 ，在 m_ids 列表内】 -> 不可见
“赵云”【trx_id=200 ，在 m_ids 列表内】 -> 不可见
“张飞” 【trx_id=100，在 m_ids 列表内】 -> 不可见
“关羽” 【trx_id=100 ，在 m_ids 列表内】 -> 不可见
“刘备” 【trx_id=80，< min_trx_id】-> 可见
返回给用户的版本就是这条列 name 为 '刘备' 的记录
说两次 SELECT 查询得到的结果是重复的，这就是 可重复读 的含义
3.3 MVCC小结
MVCC （Multi-Version Concurrency Control ，多版本并发控制）指的就是
在使用 READ COMMITTD 、 REPEATABLE READ 这两种隔离级别的事务在执行普通的 SEELCT 操作时访问记录的版本链的过程，
这样子可以使不同事务的 读-写 、 写-读 操作并发执行，从而提升系统性能。
READ COMMITTD 、REPEATABLE READ 这两个隔离级别的一个很大不同就是：生成ReadView的时机不同，
READ COMMITTD在每一次进行普通SELECT操作前都会生成一个ReadView，
而REPEATABLE READ只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复使用这个ReadView就好了。
4 关于purge
我们说 insert undo 在事务提交之后就可以被释放掉了，而 update undo 由于还需要支持 MVCC ，不能立即删除掉。
为了支持 MVCC ，对于 delete mark 操作来说，仅仅是在记录上打一个删除标记，并没有真正将它删除掉。
在确定系统中包含最早产生的那个 ReadView 的事务不会再访问某些 update undo日志 以及被打了删除标记的记录后，有一个后台运行的 purge线程 会把它们真正的删除掉。
第25章 工作面试老大难-锁
1 解决并发事务带来问题的两种基本方式
并发事务访问相同记录的情况大致可以划分为3种
读-读
读取操作本身不会对记录有影响，并不会引起什么问题，所以允许这种情况的发生。
写-写
即并发事务相继对相同的记录做出改动。
需要让它们排队执行，这个排队的过程其实是通过 锁 来实现的。
锁 其实是一个内存中的结构，在事务执行前本来是没有锁的，也就是说一开始是没有 锁结构 和记录进行关联的
当一个事务想对这条记录做改动时（简化）
trx信息 ：代表这个锁结构是哪个事务生成的。
is_waiting ：代表当前事务是否在等待。
在事务 T1 提交之前，另一个事务 T2 也想对该记录做改动
在事务 T1 提交之后，就会把该事务生成的 锁结构 释放掉，
然后看看还有没有别的事务在等待获取锁，-> T2;
【is_waiting -> false】
几种容易混淆的说法
不加锁
就是不需要在内存中生成对应的 锁结构 ，可以直接执行操作
获取锁成功｜加锁成功
就是在内存中生成了对应的 锁结构 ，而且锁结构的 is_waiting 属性为 false ，也就是事务可以继续执行操作。
获取锁失败｜加锁失败｜没有获取到锁
就是在内存中生成了对应的 锁结构 ，不过锁结构的 is_waiting 属性为 true ，也就是事务需要等待，不可以继续执行操作。
读-写 或 写-读
也就是一个事务进行读取操作，另一个进行改动操作。
怎么解决 脏读 、 不可重复读 、 幻读 这些问题呢？
两种可选的解决方案
方案一：读操作利用多版本并发控制（ MVCC ），写操作进行 加锁 。
方案二：读、写操作都采用 加锁 的方式。
采用加锁的方式解决幻读问题就有那么一丢丢麻烦了，因为当前事务在第一次读取记录时那些幻影记录并不存在，所以读取的时候加锁就有点尴尬 —— 因为你并不知道给谁加锁
采用 MVCC 方式的话， 读-写 操作彼此并不冲突，性能更高，采用 加锁 方式的话， 读-写 操作彼此需要排队执行，影响性能。
一般情况下我们当然愿意采用 MVCC 来解决 读-写 操作并发执行的问题，
但是业务在某些特殊情况下，要求必须采用 加锁 的方式执行，那也是没有办法的事。
1.1 一致性读（Consistent Reads）
事务利用 MVCC 进行的读取操作 【一致性读 ｜ 一致性无锁读 ｜ 快照读】 
一致性读 并不会对表中的任何记录做 加锁 操作，其他事务可以自由的对表中的记录做改动。
1.2 锁定读（Locking Reads）
1 共享锁和独占锁
既要允许 读-读 情况不受影响，又要使 写-写 、 读-写 或 写-读 情况中的操作相互阻塞
共享锁（ Shared Locks ｜ S锁）在事务要读取一条记录时，需要先获取该记录的 S锁 。
独占锁（ 排他锁 ｜ Exclusive Locks ｜ X锁）在事务要改动一条记录时，需要先获取该记录的 X锁 。
2 锁定读的语句
两种比较特殊的 SELECT 语句格式
对读取的记录加 S锁
允许别的事务继续获取这些记录的 S锁;
但是不能获取这些记录的 X锁
如果别的事务想要获取这些记录的 X锁 ，那么它们会阻塞，直到当前事务提交之后将这些记录上的 S锁 释放掉。
对读取的记录加 X锁
既不允许别的事务获取这些记录的 S锁;
也不允许获取这些记录的 X锁
如果别的事务想要获取这些记录的 S锁 或者 X锁 ，那么它们会阻塞，直到当前事务提交之后将这些记录上的 X锁 释放掉。
1.3 写操作
DELETE
先在 B+ 树中定位到这条记录的位置，然后获取一下这条记录的 X 锁 ，然后再执行 delete mark 操作。
可以把这个定位待删除记录在 B+ 树中位置的过程看成是一个获取 X锁 的 锁定读 。
UPDATE
UPDATE 操作时分为三种情况
如果未修改该记录的主值并且被更新的列占用的存储空间在修改前后未发生变化（满足就地更新条件），则先在 B+ 树中定位
到这条记录的位置，然后再获取一下记录的 X锁 ，最后在原记录的位置进行修改操作。
可以把这个定位待修改记录在 B+ 树中位置的过程看成是一个获取 X锁 的 锁定读 。
如果未修改该记录的主键并且至少有一个被更新的列占用的存储空间在修改前后发生变化，则先在B+ 树中定位到这条记录的位置，然后获取一下记录的 X锁 ，将该记录彻底删除掉（就是把记录彻底移入垃圾链表），最后再插入一条新记录。这个定位待修改记录在 B+ 树中位置的过程看成是一个获取 X 锁 的 锁定读 ，新插入的记录由 INSERT 操作提供的 隐式锁 进行保护。
如果修改了该记录的主键，则相当于在原记录上做 DELETE 操作之后再来一次 INSERT 操作，
加锁操作就需要按照 DELETE 和 INSERT 的规则进行了。
INSERT
新插入一条记录的操作并不加锁；
通过 隐式锁 来保护这条新插入的记录在本事务提交前不被别的事务访问
在一些特殊情况下INSERT操作也是会获取锁
2 多粒度锁
除了行级锁 还有表级锁
给表加的锁也可以分为 共享锁（ S锁 ）和 独占锁 （ X锁 ）：
意向锁 （Intention Locks ）【表级锁】
意向共享锁（Intention Shared Lock ｜ IS锁）当事务准备在某条记录上加 S锁 时，需要先在表级别加一个 IS锁 。
意向独占锁（Intention Exclusive Lock ｜ IX锁）当事务准备在某条记录上加 X锁 时，需要先在表级别加一个 IX锁 。
为什么要有意向锁？
IS、IX锁是表级锁，仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录，
IS锁和IX锁是兼容的，IX锁和IX锁是兼容的。
表级别的各种锁的兼容性
3 MySQL中的行锁和表锁
3.1 其他存储引擎中的锁
MyISAM 、 MEMORY 、 MERGE 存储引擎
只支持表级锁
不支持事务
在同一时刻只允许一个会话对表进行写操作，最好用在只读，或者大部分都是读操作，或者单用户的情景下
MyISAM存储引擎中有一个称之为Concurrent Inserts的特性，支持在对MyISAM表读取时同时插入记录，这样可以提升一些插入速度
3.2 InnoDB存储引擎中的锁
1 InnoDB中的表级锁
S锁 、 X锁
在对某个表执行 SELECT 、 INSERT 、 DELETE 、 UPDATE 语句时， 不会为这个表添加表级别的 S锁 或者 X锁 的。
server层 使用 元数据锁 （Metadata Locks ｜ MDL ）
 ALTER TABLE 、 DROP TABLE 这类的 DDL 语句时 与 如 SELECT 、 INSERT 、 DELETE 、 UPDATE 的语句会互相发生阻塞
只会在一些特殊情况下，比方说崩溃恢复过程中用到。
以手动获取
LOCK TABLES t READ ： InnoDB 存储引擎会对表 t 加表级别的 S锁 。
LOCK TABLES t WRITE ： InnoDB 存储引擎会对表 t 加表级别的 X锁 。
IS锁 、 IX锁
AUTO-INC锁
自动给 AUTO_INCREMENT 修饰的列递增赋值的原理
采用 AUTO-INC 锁
执行插入语句时就在表级别加一个 AUTO-INC 锁，然后为每条待插入记录的 AUTO_INCREMENT 修饰的列分配递增的值，在该语句执行结束后，再把 AUTO-INC 锁释放掉。
插入语句在执行前不可以确定具体要插入多少条记录（无法预计即将插入记录的数量），比方说使用 INSERT ... SELECT 、 REPLACE ... SELECT 或者 LOAD DATA 这种插入语句，一般是使用AUTO-INC 锁为 AUTO_INCREMENT 修饰的列生成对应的值。
作用范围只是单个插入语句
采用一个轻量级的锁
不需要等到整个插入语句执行完才释放锁。
确定插入条数使用，避免锁表，可以提升性能
innodb_autoinc_lock_mode的系统变量
值为0
一律采用AUTO-INC锁
值为1
两种方式混着来（也就是在插入记录数量确定时采用轻量级锁，不确定时使用AUTO-INC锁）
值为2
一律采用轻量级锁
不同事务中的插入语句为AUTO_INCREMENT修饰的列生成的值是交叉的，在有主从复制的场景中是不安全的
2 InnoDB中的行级锁
Record Locks【LOCK_REC_NOT_GAP】（记录锁）
有 共享锁（S锁） 和 独占锁（X锁） 之分
Gap Locks【LOCK_GAP】（Gap/间隙 锁）
仅仅是为了防止插入幻影记录
虽然有 共享gap锁 和 独占gap锁 这样的说法，但是它们起到的作用都是相同的。
举例
(3, 8) 这个区间的新记录是不允许立即插入的
问题：如何给(20,+∞)加间隙锁
使用 数据页 的两条伪记录
在页面的 Supremum 记录加上一个 gap锁
Next-Key Locks【LOCK_ORDINARY】（Next-Key锁）
记录锁和Gap锁的合体
Insert Intention Locks【LOCK_INSERT_INTENTION】（插入意向锁）
事务在等待的时候也需要在内存中生成一个 锁结构 ，表明有事务想在某个 间隙 中插入新记录，但是现在在等待。
由于 T1 持有 gap锁 ，所以 T2 和 T3 需要生成一个 插入意向锁 的 锁结构 并且处于等待状态。
插入意向锁并不会阻止别的事务继续获取该记录上任何类型的锁
隐式锁（事务id起作用）
聚簇索引记录
trx_id 隐藏列  记录着最后改动该记录的 事务id 。
什么时候添加锁（X锁）？
当事物T1新插入一条聚簇索引记录后，该记录的 trx_id 隐藏列就是 T1的事务id ，
如果其他事务此时想对该记录添加 S锁 或者 X锁 时，先看 trx_id 是否活跃，
如果是，那么就给T1事务创建一个 X锁 （创建T1锁结构， is_waiting 属性是 false ），
然后自己进入等待状态（创建自己的锁结构， is_waiting 属性是 true ）。
二级索引记录
没有 trx_id 隐藏列
页面的 Page Header 部分有一个 PAGE_MAX_TRX_ID 属性，代表对该页面做改动的最大的 事务id
PAGE_MAX_TRX_ID 属性值 < 当前最小的活跃 事务id
是，则页面事务都已提交，不加锁
否则，查询二级索引记录，回表后重复聚簇索引锁表步骤
3.3 InnoDB锁的内存结构
行级锁放到同一锁结构的条件
在同一个事务中进行加锁操作
被加锁的记录在同一个页面中
加锁的类型是一样的
等待状态是一样的
InnoDB 存储引擎中的 锁结构
锁所在的事务信息（指针）【表锁、行锁】
哪个事务生成了这个 锁结构
索引信息（指针）【行锁】
记录加锁的记录是属于哪个索引的
表锁／行锁信息
表锁
记载着这是对哪个表加的锁，还有其他的一些信息。
行锁
Space ID ：记录所在表空间。
Page Number ：记录所在页号。
n_bits：使用了多少比特位（行锁结构的末尾放置了一堆比特位）
计算公式：n_bits = (1 + ((n_recs + LOCK_PAGE_BITMAP_MARGIN) / 8)) * 8。
n_recs 指的是当前页面中一共有多少条记录（算上伪记录和在垃圾链表中的记录）；
LOCK_PAGE_BITMAP_MARGIN 是一个固定的值 64 。
type_mode【表锁、行锁】
一个32位的数，被分成了 lock_mode 、 lock_type 和 rec_lock_type 三个部分
各个部分
锁的模式（ lock_mode ），占用低4位
LOCK_IS （十进制的 0 ）：表示共享意向锁，也就是 IS锁 。【表级】
LOCK_IX （十进制的 1 ）：表示独占意向锁，也就是 IX锁 。【表级】
LOCK_S （十进制的 2 ）：表示共享锁，也就是 S锁 。【表级、行级】
LOCK_X （十进制的 3 ）：表示独占锁，也就是 X锁 。【表级、行级】
LOCK_AUTO_INC （十进制的 4 ）：表示 AUTO-INC锁 。【表级】
锁的类型（ lock_type ），占用第5～8位（只使用5、6位）
LOCK_TABLE （十进制的 16 ），也就是当第5个比特位置为1时，表示表级锁。
LOCK_REC （十进制的 32 ），也就是当第6个比特位置为1时，表示行级锁。
行锁的具体类型（ rec_lock_type ），使用其余的位来表示。【行级】
【lock_type=LOCK_REC才使用】
LOCK_ORDINARY （十进制的 0 ）：表示 next-key锁 。
LOCK_GAP （十进制的 512 ）：也就是当第10个比特位置为1时，表示 gap锁 。
LOCK_REC_NOT_GAP （十进制的 1024 ）：也就是当第11个比特位置为1时，表示 正经记录锁 。
LOCK_INSERT_INTENTION （十进制的 2048 ）：也就是当第12个比特位置为1时，表示插入意向锁。
LOCK_WAIT （十进制的 256 ） ：也就是当第9个比特位置为 1 时，表示 is_waiting 为 true ，也就是当前事务尚未获取到锁，处在等待状态；
当这个比特位为 0 时，表示 is_waiting 为 false ，也就是当前事务获取锁成功。
其他的类型：还有一些不常用的类型我们就不多说了。
其他信息
一堆比特位【行锁】
比特位的数量 = n_bits 属性
每条记录在 记录头信息 中都包含一个 heap_no 属性；
伪记录 Infimum 的 heap_no 值为 0 ， Supremum 的 heap_no 值为 1 ，
之后每插入一条记录， heap_no值就增1。
一个比特位对映页内一条记录(heap_no)
为了编码方便，所以看起来很怪
4 更多内容
第26章 写作本书时用到的一些重要的参考资料
1 感谢
1.1 一些链接
1.2 一些书籍
1.3 说点不好的
1.4 结语
 binlog（binary log｜二进制日志）
binlog 中记载了数据库发生的变化
binlog的作用
用于复制
一主多从 ，即一台主服务器（Master）和多台从服务器（Slave）
改变数据库状态的请求（DDL、DML等），就将它们发送给主服务器；
对于单纯的查询（如 SELECT语句）请求，就将它们发送给从服务器
主从同步
用于恢复
定时备份数据库 （mysqldump命令）
操作失误数据丢失，可以从上一次全量备份时间点往后，到失误操作的时间点进行恢复
配置binlog
binlog在文件系统中的内容
MySQL服务器还会在相同的路径下生成一个关于binlog的索 引文件：****-bin.index
查看binlog的语句
事件（很多，几个熟悉的）
WRITE_ROWS_EVENT：插入记录。 
UPDATE_ROWS_EVENT：更新记录。 
DELETE_ROWS_EVENT：删除记录。
mysqlbinlog工具的使用
mysqlbinlog ./****-bin.000001
以 # at xx 开头的表示这是一个事件的开始，
binlog日志版本
v4版本从MySQL 5.0就开始使用
binlog日志文件结构概览
基本格式
每个binlog日志文件的前4个字节是固定的，即： 0xfe626963 。
每个binlog日志文件都是由若干事件构成的。
每个binlog日志文件所存储的第1个事件都是一个称作 格式描述事件（format description event）的特殊事件。
事件
event header部分 
timestamp（4字节）：产生该事件时的时间戳。
typecode（1字节）：该事件的类型，事件的类型在枚举结构 Log_event_type 中列举出来 。比方说 格式描述事件 的typecode就是 15。
server_id（4字节）：产生该事件的主机的server_id。
event_length（4字节）：该事件总大小（包括event header + event data）。
next_position（4字节）：下一个事件的位置。
flags（2字节）：该事件的一些附加属性（称作flags）。
extra_headers（不确定大小）：目前这个字段尚未使用（也就是占用的大小为0）。
event data部分描述了该事件所特有的一些信息
基于语句（Statement）和基于行(Row)的binlog
启动选项 binlog-format
--binlog-format=STATEMENT
--binlog-format=ROW
--binlog-format=MIXED
基于语句的binlog
UPDATE s1 SET common_field = 'xx' WHERE id > 9990;
基于行的binlog
UPDATE s1 SET common_field = 'xxx' WHERE id > 9990;
基于语句的binlog的问题
可能会造成主服务器和从服务器维护的数据不一致的 情况。
redo、undo、buffer pool、binlog，谁先谁后
举例
已有数据的 索引
UPDATE hero SET country = '汉' WHERE name >= 'x荀彧';
MySQL优化器分析成本
方案一：使用全表扫描执行查询，即扫描全部聚簇索引记录，我们可以认为此时的扫描区间就 是 (-∞, +∞) 。
方案二：使用二级索引idx_name执行查询，此时的扫描区间就是 ['x荀彧', +∞) 。
真正开始执行
处理扫描区间的第一条记录
步骤1：server层向InnoDB层索要符合条件的第一条记录。
步骤2：Innodb存储引擎便会通过二级索引查到记录，再回表，将聚簇索引记录返回server层。
步骤3：server层判断是否需要更新，如果需要，让InnoDB真正的执行更新记录的操作。
步骤4：InnoDB收到更新请求后，先更新记录的聚簇索引记录，再更新记录的二级索引记录。 
最后将更新结果返回给server层。
处理扫描区间的第二条记录
步骤1：server层继续向InnoDB索要下一条记录。
步骤2：第一条二级索引记录（单向链表）的头信息的 next_record 取到下一条二级索引记录。回表，将聚簇索引记录再返回给server层。
后面三四步跟处理第一条一样
后续跟处理第二条一样
详细的更新过程
先把undo日志写到Undo页面中以及记录相应的redo日志
真正修改聚簇索 引记录
首先更新系统字段trx_id以及roll_pointer：
然后真正的修改记录内容
记录更新的redo日志
先记录修改页面的redo日志，然后再真正的修改页面
更新二级索引记录
更新二级索引记录时不会再记录undo日志，但由于是在修改页面内容，会先记录相应的redo日 志
记录binlog
暂时存在内存，等事务提交才会把事务执行产生的所有binlog日志统一写到binlog文件
XA事务与两阶段提交
分布式事务
 什么是分布式事务
一个大事务包含多个小事务，有一个小事务不成功，则全部回滚
可以是不同存储引擎
可以是不同系统（跨行转账）
XA规范
2个角色
事务协调器 （Transaction Coordinator）或者资源管理器（Resource Manager）。
事务管理器（Transaction Manager）
提交一个全局事务，必须 分为2步
Prepare阶段：准备提交一个全局事务
Commit阶段：通知要提交还是要回滚
MySQL中的XA事务
外部XA
客户端程序（资源管理器）
MySQL服务器（事务管理器）
步骤
开启一个XA事务
XA {START|BEGIN} xid
发送属于这个 XA事务的各条语句
所有语句都输入完
XA END xid
询问MySQL服务器是否准 备好提交这个XA事务
XA PREPARE xid
MySQL服务器 收到此语句后，就需要做准备提交前的工作了，
比如把该事务执行过程中所产生的redo日志刷 新到磁盘等。
结束XA事务
提交XA事务
对于处于 PREPARE 状态的XA事务
XA COMMIT xid [ONE PHASE]
XA事务尚处于 IDEL 状态
XA COMMIT xid ONE PHASE
让MySQL服务器回滚xid所标识的事务
XA ROLLBACK xid
查看Prepare状态的XA事务
XA RECOVER
状态轮换图
应用
跨行转账
数据库中间件
应用程序发送SQL给中间件
中间件分析数据分别在哪些数据库服务上
应用服务发送事务提交
中间件先给各个服务器发送 XA PREPARE 语句
服务器都ok，则发送 XA COMMIT
各个服务器把提交成功的消息返 回给中间件，中间件就可以通知应用程序事务提交成功了。
内部XA
应用
涉及多个存储引擎的事务
保证 binlog和存储引擎所做的修改是一致的
有binlog参与的内部XA事务
Prepare阶段
存储引擎将该事务执行过程中产生的redo日志刷盘，并且将本事务的状态设置 为 PREPARE 。
binlog啥也不干
MySQL 5.7以及之后的版本中，在Prepare阶段刷新redo日志的操作会被推迟到 Commit阶段才真正执行。（组提交 （group commit））
Commit阶段
先将事务执行过程中产生的binlog刷新到硬盘，再执行存储引擎的提交工作。
崩溃恢复
Undo页面链表 对应的事务状态是什么
第1章 装作自己是个小白-重新认识MySQL
1 MySQL的客户端／服务器架构
客户端
server层
连接管理 、 查询缓存 、 语法解析 、 查询优化
存储引擎层
物理文件层
2 MySQL的安装
2.1 bin目录下的可执行文件
3 启动MySQL服务器程序
3.1 UNIX里启动服务器程序
3.2 Windows里启动服务器程序
4 启动MySQL客户端程序
4.1 连接注意事项
5 客户端与服务器连接的过程
5.1 TCP/IP
5.2 命名管道和共享内存
5.3 Unix域套接字文件
6 服务器处理客户端请求
6.1 连接管理
6.2 解析与优化
6.3 存储引擎
7 常用存储引擎
InnoDB 具备外键支持功能的事务存储引擎
MEMORY 置于内存的表
MERGE 用来管理多个MyISAM表构成的表集合
MyISAM 主要的非事务处理存储引擎
8 关于存储引擎的一些操作
8.1 查看当前服务器程序支持的存储引擎
8.2 设置表的存储引擎
第2章 MySQL的调控按钮-启动选项和系统变量
1 在命令行上使用选项
1.1 选项的长形式和短形式
2 配置文件中使用选项
2.1 配置文件的路径
2.2 配置文件的内容
2.3 特定MySQL版本的专用选项组
2.4 配置文件的优先级
2.5 同一个配置文件中多个组的优先级
2.6 defaults-file的使用
3 命令行和配置文件中启动选项的区别
4 系统变量
4.1 系统变量简介
4.2 查看系统变量
4.3 设置系统变量
4.4 启动选项和系统变量的区别
5 状态变量
第3章 乱码的前世今生-字符集和比较规则
1 字符集和比较规则简介
1.1 字符集简介
1.2 比较规则简介
1.3 一些重要的字符集
2 MySQL中支持的字符集和排序规则
2.1 MySQL中的utf8和utf8mb4
2.2 字符集的查看
2.3 比较规则的查看
3 字符集和比较规则的应用
3.1 各级别的字符集和比较规则
3.1.1 服务器级别
3.1.2 数据库级别
3.1.3 表级别
3.1.4 列级别
3.1.5 仅修改字符集或仅修改比较规则
3.1.6 各级别字符集和比较规则小结
3.2 客户端和服务器通信中的字符集
3.2.1 编码和解码使用的字符集不一致的后果
3.2.2 字符集转换的概念
3.2.3 MySQL中字符集的转换
3.3 比较规则的应用
4 总结
第4章 从一条记录说起-InnoDB记录结构
1 准备工作
2 InnoDB页简介
3 InnoDB行格式
3.1 指定行格式的语法
3.2 COMPACT行格式
3.2.1 记录的额外信息
变长字段长度列表
只存储值为 非NULL 的列内容占用的长度，
值为 NULL 的列的长度是不储存的
NULL值列表
二进制位的值为 1 时，代表该列的值为 NULL 。 二进制位的值为 0 时，代表该列的值不为 NULL
记录头信息
heap_no
伪记录 Infimum 的 heap_no 值为 0 ， 
Supremum 的 heap_no 值为 1 ，
之后每插入一条记录， heap_no值就增1
3.2.2 记录的真实数据
隐藏列
DB_ROW_ID 行ID
DB_TRX_ID 事务ID
DB_ROLL_PTR 回滚指针
3.2.3 CHAR(M)列的存储格式
3.3 Redundant行格式
3.3.1 CHAR(M)列的存储格式
3.4 行溢出数据
3.4.1 VARCHAR(M)最多能存储的数据
3.4.2 记录中的数据太多产生的溢出
3.4.3 行溢出的临界点
3.5 Dynamic和Compressed行格式
4 总 结
第5章 盛放记录的大盒子-InnoDB数据页结构
1 不同类型的页简介
2 数据页结构的快速浏览
各个数据页组成一个双向链表
3 记录在页中的存储
3.1 记录头信息的秘密
4 Page Directory（页目录）
两条伪记录
Infimum 记录，表示该页面中最小的记录。
Supremum 记录，表示该页面中最大的记录。
对于最小记录所在的分组只能有 1 条记录，
最大记录所在的分组拥有的记录条数只能在 1~8 条之间，剩下的分组中记录的条数范围只能在是 4~8 条之间
分组的步骤
初始情况下一个数据页里只有最小记录和最大记录两条记录，它们分属于两个分组。
之后每插入一条记录，都会从 页目录 中找到主键值比本记录的主键值大并且差值最小的槽，然后把该槽对 应的记录的 n_owned 值加1，表示本组内又添加了一条记录，直到该组中的记录数等于8个。
在一个组中的记录数等于8个后再插入一条记录时，会将组中的记录拆分成两个组，一个组中4条记录，另一 个5条记录。这个过程会在 页目录 中新增一个 槽 来记录这个新增分组中最大的那条记录的偏移量。
在一个数据页中查找指定主键值的记录的过程分为两步
1. 通过二分法确定该记录所在的槽，并找到该槽中主键值最小的那条记录。
2. 通过记录的 next_record 属性遍历该槽所在的组中的各个记录。
n_owned 值为本组中记录的数量
5 Page Header（页面头部）
PAGE_DIRECTION
PAGE_N_DIRECTION
PAGE_BTR_SEG_LEAF和PAGE_BTR_SEG_TOP
PAGE_BTR_SEG_LEAF 记录着叶子节点段对应的 INODE Entry 结构的地址是哪个表空间的哪个
页面的哪个偏移量， PAGE_BTR_SEG_TOP 记录着非叶子节点段对应的 INODE Entry 结构的地址是哪个表空间的哪
个页面的哪个偏移量。这样子索引和其对应的段的关系就建立起来了。不过需要注意的一点是，因为一个索引只
对应两个段，所以只需要在索引的根页面中记录这两个结构即可。
6 File Header（文件头部）
7 File Trailer
为了检测一个页是否完整（也就是在同步的时候有没有发生只同步
一半的尴尬情况）
分成2个小部分
前4个字节代表页的校验和
后4个字节代表页面被最后修改时对应的日志序列位置（LSN）
8 总结
页的大小=16k
第6章 快速查询的秘籍-B+树索引
1 没有索引的查找
1.1 在一个页中的查找
1.2 在很多页中查找
2 索引
2.1 一个简单的索引方案
2.2 InnoDB中的索引方案
2.2.1 聚簇索引
2.2.2 二级索引
2.3 InnoDB的B+树索引的注意事项
2.3.1 根页面万年不动窝
2.3.2 内节点中目录项记录的唯一性
2.3.3 一个页面最少存储2条记录
2.4 MyISAM中的索引方案简单介绍
数据文件
按照记录的插入顺序存储
索引文件
都是二级索引
索引的叶子节点中存储的不是完整的用户记录，而是 主键值（索引行） + 行号 的组
合。
2.5 MySQL中创建和删除索引的语句
总结：InnoDB中的索引即数据，数据即索引，而MyISAM中却是索引是索引、数据是数
据
第7章 好东西也得先学会怎么用-B+树索引的使用
1 索引的代价
2 B+树索引适用的条件
2.1 全值匹配
2.2 匹配左边的列
2.3 匹配列前缀
2.4 匹配范围值
2.5 精确匹配某一列并范围匹配另外一列
2.6 用于排序
2.7 用于分组
3 回表的代价
3.1 覆盖索引
4 如何挑选索引
4.1 只为用于搜索、排序或分组的列创建索引
4.2 考虑列的基数
4.3 索引列的类型尽量小
4.4 索引字符串值的前缀
4.5 让索引列在比较表达式中单独出现
4.6 主键插入顺序
4.7 冗余和重复索引
5 总结
第8章 数据的家-MySQL的数据目录
1 数据库和文件系统的关系
2 MySQL数据目录
2.1 数据目录和安装目录的区别
2.2 如何确定MySQL中的数据目录
3 数据目录的结构
3.1 数据库在文件系统中的表示
3.2 表在文件系统中的表示
3.3 视图在文件系统中的表示
3.4 其他的文件
4 文件系统对数据库的影响
5 MySQL系统数据库简介
第9章 存放页面的大池子-InnoDB的表空间
1 回忆一些旧知识
1.1 页面类型
1.2 页面通用部分
File Header ：记录页面的一些通用信息
File Trailer ：校验页是否完整，保证从内存到磁盘刷新时内容的一致性。
2 独立表空间结构
2.1 区（extent）的概念
物理上连续的64个页就是一个 区 （默认1M）
每256个区被划分成一组
第一个组最开始的3个页面的类型是固定的
FSP_HDR 类型
IBUF_BITMAP 类型
INODE 类型
其余各组最开始的2个页面的类型是固定的
XDES 类型
IBUF_BITMAP 类型
为什么引入区，因为需要让相邻的页在物理上也是相邻，避免随机IO
2.2 段（segment）的概念
一个索引会生成2个段，一个叶子节点段，一个非叶子节点段。
后为某个段分配存储空间的策略
在刚开始向表中插入数据的时候，段是从某个碎片区以单个页面为单位来分配存储空间的。
当某个段已经占用了32个碎片区页面之后，就会以完整的区为单位来分配存储空间。
2.3 区的分类
4种类型
空闲的区：现在还没有用到这个区中的任何页面。
有剩余空间的碎片区：表示碎片区中还有可用的页面。
没有剩余空间的碎片区：表示碎片区中的所有页面都被使用，没有空闲页面。
附属于某个段的区。每一个索引都可以分为叶子节点段和非叶子节点段，除此之外InnoDB还会另外定义一些特殊作用的段，在这些段中的数据量很大时将使用区来作为基本的分配单位。
区的4种状态（ State ）
处于 FREE 、 FREE_FRAG 以及 FULL_FRAG 这三种状态的区都是独立的，算是直属于表空间；而处于 FSEG 状态的区是附属于某个段的。
XDES Entry 的结构（全称就是Extent Descriptor Entry）
Segment ID
List Node
Pre Node Page Number 和 Pre Node Offset 的组合就是指向前一个 XDES Entry 的指针
Next Node Page Number 和 Next Node Offset 的组合就是指向后一个 XDES Entry 的指针。
State
Page State Bitmap
3.1 XDES Entry链表
捋向某个段中插入数据的过程
1 段中数据较少，需要碎片页
2 当段中数据已经占满了32个零散的页后，就直接申请完整的区来插入数据了。
每个段中的区对应的 XDES Entry 结构建立了三个链表
FREE 链表：同一个段中，所有页面都是空闲的区对应的 XDES Entry 结构会被加入到这个链表。注意和直属于表空间的 FREE 链表区别开了，此处的 FREE 链表是附属于某个段的。
NOT_FULL 链表：同一个段中，仍有空闲空间的区对应的 XDES Entry 结构会被加入到这个链表。
FULL 链表：同一个段中，已经没有空闲空间的区对应的 XDES Entry 结构会被加入到这个链表。
3.2 链表基节点
List Base Node 结构
List Length 表明该链表一共有多少节点，
First Node Page Number 和 First Node Offset 表明该链表的头节点在表空间中的位置。
Last Node Page Number 和 Last Node Offset 表明该链表的尾节点在表空间中的位置。
3.3 链表小结
表空间是由若干个区组成的，每个区都对应一个 XDES Entry 的结构，直属于表空间的区对应的 XDESEntry 结构可以分成 FREE 、 FREE_FRAG 和 FULL_FRAG 这3个链表；
每个段可以附属若干个区，每个段中的区对应的 XDES Entry 结构可以分成 FREE 、 NOT_FULL 和 FULL 这3个链表
2.4 段的结构
段都定义了一个 INODE Entry 结构来记录一下段中的属性
Segment ID
NOT_FULL_N_USED
3个 List Base Node
Magic Number
Fragment Array Entry
2.5 各类型页面详细情况
5.1 FSP_HDR 类型
第一个组的第一个页面，当然也是表空间的第一个页面，页号为 0 。
File Header
File Space Header存储表空间的一些整体属性
List Base Node for FREE List 、 List Base Node for FREE_FRAG List 、 List Base Node for FULL_FRAG List 。
FRAG_N_USED
在 FREE_FRAG 链表中已经使用的页面数量，方便之后在链表中查找空闲的页面。
FREE Limit
该字段表示的页号之前的区都被初始化了，之后的区尚未被初始化。
Next Unused Segment ID
当前表空间中最大的段ID的下一个ID，创建新段时直接有id可用
Space Flags存储了好多表空间的属性
List Base Node for SEG_INODES_FULL List 和 List Base Node for SEG_INODES_FREE List
段太多，需要页面组成链表才够用，这两个就是这个作用
SEG_INODES_FULL 链表
SEG_INODES_FREE 链表
XDES Entry
每个区对应的 XDES Entry 结构的地址是固定的，方便直接访问区
Empty Space
File Trailer
5.2 XDES 类型
每个分组（去掉第一个组）的第一个页面只需要记录本组内所有的区对应的 XDES Entry 结构
与 FSP_HDR 类型的页面对比，除了少了 File Space Header 部分之外，也就是除了少了记录表空间整体属性的部分之外，其余的部分是一样一样的
5.3 IBUF_BITMAP 类型
每个分组的第二个页面的类型都是 IBUF_BITMAP
5.4 INODE 类型
第一个分组的第三个页面的类型是 INODE，
存储 INODE Entry 结构
INODE Entry
每个 INODE Entry 结构占用192字节，一个页面里可以存储 85 个这样的结构。
List Node for INODE Page List
可以指向上（下）一个INODE页
新创建一个段（创建索引时就会创建段）时，都会创建一个 INODE Entry 结构与之对应，存储 INODE Entry 的大致过程
先看看 SEG_INODES_FREE 链表是否为空，如果不为空，直接从该链表中获取一个节点，也就相当于获取到一个仍有空闲空间的 INODE 类型的页面，然后把该 INODE Entry 结构防到该页面中。当该页面中无剩余空间时，就把该页放到 SEG_INODES_FULL 链表中。
如果 SEG_INODES_FREE 链表为空，则需要从表空间的 FREE_FRAG 链表中申请一个页面，修改该页面的类型为 INODE ，把该页面放到 SEG_INODES_FREE 链表中，与此同时把该 INODE Entry 结构放入该页面。
2.6 Segment Header 结构的运用
Page Header中的 PAGE_BTR_SEG_LEAF 和 PAGE_BTR_SEG_TOP 都占用10个字节，对应一个叫 Segment Header 的结构，
表空间ID、页号、页内偏移量 可以唯一定位一个 INODE Entry 的地址
2.7 真实表空间对应的文件大小
3 系统表空间
3.1 系统表空间的整体结构
InnoDB数据字典
InnoDB存储引擎特意定义了一些列的内部系统表（internal system table）来记录这些这些 元数据 ：
Data Dictionary Header
Segment Header
有关数据字典的信息当成一个段来分配存储空间，我们就姑且称之为 数据字典段 吧
Data Dictionary Header
Max Row ID
不论哪个拥有 row_id 列的表插入一条记录时，该记录的 row_id 列的值就是 Max Row ID 对应的值，然后再把 Max Row ID 对应的值加1，也就是说这个 Max Row ID 是全局共享的。
Max Table ID
所有的表都对应一个唯一的ID，每次新建一个表时，就会把本字段的值作为该表的ID，然后自增本字段的值。
Max Index ID
所有的索引都对应一个唯一的ID，每次新建一个索引时，就会把本字段的值作为该索引的ID，然后自增本字段的值。
Max Space ID
所有的表空间都对应一个唯一的ID，每次新建一个表空间时，就会把本字段的值作为该表空间的ID，然后自增本字段的值。
Root of SYS_TABLES clust index
代表 SYS_TABLES 表聚簇索引的根页面的页号。
Root of SYS_TABLE_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_FIELDS clust index
代表 SYS_FIELDS 表聚簇索引的根页面的页号。
InnoDB数据字典（管理元数据）
为了更好的管理我们这些用户数据而不得已引入的一些额外数据，这些数据也称为 元数据
SYS_TABLES表
以 NAME 列为主键的聚簇索引
以 ID 列建立的二级索引
SYS_COLUMNS表
以 (TABLE_ID, POS) 列为主键的聚簇索引
SYS_INDEXES表
以 (TABLE_ID, ID) 列为主键的聚簇索引
SYS_FIELDS表
以 (INDEX_ID, POS) 列为主键的聚簇索引
information_schema系统数据库
3.2 总结图
第10章 条条大路通罗马-单表访问方法
1 访问方法（access method）的概念
2 const
3 ref
4 ref_or_null
5 range
6 index
7 all
8 注意事项
8.1 重温 二级索引 + 回表
8.2 明确range访问方法使用的范围区间
8.3 索引合并
Intersection合并
交集 。这里是说某个查询可以使用多个二级索引，将从多个二级索引中查询到 的结果取交集
Union合并
并集，适用于使用不同索引的搜索条件之间使用 OR 连接起来的情况。
Sort-Union合并
先按照二级索引记录的主键值进行排序，之后按照 Union 索引合并方式执行的方式称之为 SortUnion 索引合并
第11章 两个表的亲密接触-连接的原理
1 连接简介
1.1 连接的本质
1.2 连接过程简介
1.3 内连接和外连接
2 连接的原理
2.1 嵌套循环连接（Nested-Loop Join）
2.2 使用索引加快连接速度
2.3 基于块的嵌套循环连接（Block Nested-Loop Join）
尽量减少访问被驱动表的次数
join buffer 的大小是可以通过启动参数或者系统变量 join_buffer_size 进行配置，默认大小为 262144字
节 （也就是 256KB ），最小可以设置为 128字节
第12章 谁最便宜就选谁-MySQL基于成本的优化
1 什么是成本
2 单表查询的成本
2.1 准备工作
2.2 基于成本的优化步骤
2.3 基于索引统计数据的成本计算
index dive
不是唯一二级索引，所以并不能确定一个 单点区间对应的二级索引记录的条数有多少，需要我们去计算
先获取索 引对应的 B+ 树的 区间最左记录 和 区间最右记录 ，然后再计算这两条记录之间有多少记录(记录条数少的时候 可以做到精确计算，多的时候只能估算)
3 连接查询的成本
3.1 准备工作
3.2 Condition filtering介绍
3.3 两表连接的成本分析
3.4 多表连接的成本分析
4 调节成本常数
第13章 兵马未动，粮草先行-InnoDB统计数据是如何收集的
1 两种不同的统计数据存储方式
2 基于磁盘的永久性统计数据
2.1 innodb_table_stats
2.2 innodb_index_stats
2.3 定期更新统计数据
3 基于内存的非永久性统计数据
4 innodb_stats_method的使用
5 总结
评论
0 条评论
下一页
为你推荐
查看更多
B树
B树
MySQL 相关最全的知识点
MySQL 相关最全的知识点
最全钢琴谱
最全钢琴谱
Mybatis底层源码
Mybatis底层源码
MySQL 解析树
MySQL 解析树
B+树
B+树
账户树
账户树
账户树
账户树
mysql
mysql
长青树模式
长青树模式