首页  思维导图  详情

MySQL

2021-08-25 19:12:41   0  举报





仅支持查看

AI智能生成

MySQL

架构图

作者其他创作

大纲/内容

存储引擎

行格式

记录的额外信息

变长字段长度列表

列字段是变长数据类型的列值长度按照逆序存放

VARCHAR(M) 、 VARBINARY(M) 、各种 TEXT 类型，各种 BLOB 类型

NULL值列表

将每个允许存储 NULL 的列对应一个二进制位，二进制位按照列的顺序逆序排列

二进制位的值为 1 时，代表该列的值为 NULL 。

记录头信息

delete_mask

- 标记着当前记录是否被删除，占用1个二进制位，
- 这些被删除的记录之所以不立即从磁盘上移除，因为移除它们之后把其他的记录在磁盘上重新排列需要性能消耗，所以只是打一个删除标记
- 所有被删除掉的记录都会组成一个垃圾链表，在这个链表中的记录占用的空间称之为所谓的可重用空间，可复用
- 将这个delete_mask位设置为1和将被删除的记录加入到垃圾链表中其实是两个阶段

min_rec_mask

B+树的每层非叶子节点中的最小记录都会添加该标记

n_owned

当前记录拥有的记录数

heap_no

- 当前记录在本页中的位置
- InnoDB会自动给每个页里边儿加最小记录Infimum(0) 最大记录Supremum(1)

record_type

- 0 表示普通记录
- 1 表示B+树非叶节点记录
- 2 表示最小记录
- 3 表示最大记录

next_record

从当前记录的真实数据到下一条记录的真实数据的地址偏移量(链表)

记录的真实数据

row_id

行ID, 唯一标识一条记录

transaction_id

事务ID

roll_pointer

回滚指针

数据列

页目录(Page Directory)

1. 将所有正常的记录（包括最大和最小记录，不包括标记为已删除的记录）划分为几个组。
2. 每个组的最后一条记录（也就是组内最大的那条记录）的头信息中的 n_owned属性表示该记录拥有多少条记录，也就是该组内共有几条记录。
3. 将每个组的最后一条记录地址偏移量单独提取出来按顺序存储到靠近页的尾部的地方，这个地方就是所谓的 Page Directory ，也就是页目录
4. 页面目录中的这些地址偏移量被称为槽（英文名： Slot ），所以这个页面目录就是由槽组成的。

如何从页目录查找记录: 因为各个槽代表的记录的主键值都是从小到大排序的，所以我们可以使用所谓的二分法来进行快速查找

InnoDB数据页结构

1. File Header ，表示页的一些通用信息，占固定的38字节。
2. Page Header ，表示数据页专有的一些信息，占固定的56个字节。
3. Infimum + Supremum ，两个虚拟的伪记录，分别表示页中的最小和最大记录，占固定的 26 个字节。
4. User Records ：真实存储我们插入的记录的部分，大小不固定。
5. Free Space ：页中尚未使用的部分，大小不确定。
6. Page Directory ：页中的某些记录相对位置，也就是各个槽在页面中的地址偏移量
7. File Trailer ：用于检验页是否完整的部分，占用固定的8个字节。

MyISAM存储引擎与InnoDB存储引擎的区别？

索引类型

InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的；
MyISAM是非聚集索引，它也是使用B+Tree作为索引结构，但是索引和数据文件是分离的，索引保存的是数据文件的指针。

InnoDB辅助索引和主键索引之间存在层级关系；MyISAM辅助索引和主键索引则是平级关系。
InnoDB 如果添加其他二级索引，二级索引查询就需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也相应都会很大

叶子结点

InnoDB聚集索引, 存放主键对应的行记录, 非聚集索引, 存放索引列+主键
MyISAM所有的索引存储的是索引列+数据存放的地址指针, 指向存放数据文件的对应行地址

主键

InnoDB 必须要有主键，MyISAM可以没有主键；
InnoDB 如果我们没有明确去指定创建主键索引。它会帮我们隐藏的生成一个 6 byte 的 int 型的索引作为主键索引

事务

InnoDB 支持事务，MyISAM 不支持

记录数

InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。
而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）；

全文索引

InnodbMySQL 5.7 版本以后支持全文索引，而MyISAM支持全文索引，在全文索引领域的查询效率上MyISAM速度更快高；

锁

InnoDB支持表级锁、行级锁，默认为行级锁；而 MyISAM 仅支持表级锁。
InnoDB 的行锁是实现在索引上的，而不是锁在物理行上。如果访问未命中索引，也是无法使用行锁，将会退化为表锁

外键

InnoDB 支持外键，而 MyISAM 不支持

存储方式

Innodb存储文件有frm、ibd，;
【InnoDB 中，.frm文件：保存的是表结构定义描述文件；.ibd文件：保存的是表中的数据内容, 索引和数据保存在一起】；
Myisam是frm、MYD、MYI。
【MyISAM中，.frm文件：保存的是表结构定义描述文件，.MYD文件：保存的是数据内容，.MYI文件：保存的是索引内容, 索引数据分开】

Memory存储引擎

Memory中基于Hash索引，底层是 Hash 表，对于精确查询非常高效。无法通过索引做区间查询。只能扫描全表。（范围查询、排序、组合索引效率不高）

数据都是存储在内存中，IO效率比其他引擎高很多；（优点：效率高）
服务重启后数据会丢失，内存数据表默认只有16M（缺点：保证不了持久性）
支持 Hash索引，B Tree索引，默认为 Hash索引
不支持大数据存储类型，如 blog，text 等
表级锁

索引

没有索引

一个页中的查找

以主键为搜索条件

1. 在页目录中使用二分法快速定位到对应的槽，
2. 然后再遍历该槽对应分组中的记录即可快速找到指定的记录。

以其他列作为搜索条件

1. 因为在数据页中并没有对非主键列建立所谓的页目录，所以我们无法通过二分法快速定位相应的槽。
2. 只能从最小记录开始依次遍历单链表中的每条记录，然后对比每条记录是不是符合搜索条件。很显然，这种查找的效率是非常低的。

在很多页中查找

1. 没有索引, 只能从第一个页沿着双向链表一直往下找到记录所在的页
2. 从所在的页内中查找相应的记录

目录项记录

目录项中的两个列是主键和页号，复用了之前存储用户记录的数据页来存储目录项，用来表示目录项的记录称为目录项记录

创建删除索引

ALTER TABLE table_name ADD INDEX index_name (column_list);
CREATE INDEX index_name ON table_name (column_list);
alter table user_index drop KEY name;

InnoDB索引分类

聚簇索引

使用记录主键值的大小进行记录和页的排序

- 页内的记录是按照主键的大小顺序排成一个单向链表。
- 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
- 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。

B+ 树的叶子节点存储的是完整的用户记录

所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

聚簇索引就是数据的存储方式（所有的用户记录都存储在了叶子节点），

非聚簇索引

二级索引

使用索引列(非主键)的值进行记录和页的排序

- 页内的记录是按照索引列的大小顺序排成一个单向链表。
- 各个存放用户记录的页也是根据页中记录的索引列大小顺序排成一个双向链表。
- 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的索引列大小顺序排成一个双向链表

1. B+ 树的叶子节点存储的并不是完整的用户记录，而只是索引列+主键这两个列的值。
2. B+ 树的非叶子节点中不再是主键+页号的搭配，而变成了索引列+主键+页号的搭配。

1. 如果我们需要完整的用户记录或者其他当前信息, 我们必须再根据主键值去聚簇索引中再查找一遍完整的用户记录。
2. 不将用户记录放在二级索引的叶子结点因为太占空间, 相当于每建立一棵 B+ 树都需要把所有的用户记录再都拷贝一遍。

联合索引

以多个列的大小为排序规则建立的B+树称为联合索引，本质上也是一个二级索引。(c2列+c3列)

1. 先把各个记录和页按照 c2 列进行排序。
2. 在记录的 c2 列相同的情况下，采用 c3 列进行排序
3. 目录项记录都由 c2 +c3 +页号这三个部分组成，各条记录先按照 c2 列的值进行排序，如果记录的 c2 列相同，则按照 c3 列的值进行排序。
4. B+ 树叶子节点处的用户记录由 c2 、 c3 和主键 c1 列组成。

回表

根据非聚簇索引查到的是索引列对应的数据和主键值, 如果需要查找其他非索引列的数据或者数据完整的记录需要根据主键值到聚簇索引中查找完整的记录, 这个过程称之为回表(一次查询用到了2棵B+树)

为什么不把所有的记录放到非聚簇索引的叶子结点?

太占空间, 相当于每建立一棵 B+ 树都需要把所有的用户记录再都拷贝一遍。

在修改用户记录的时候, 所有的索引都需要修改叶子结点的记录,维护成本高, 尤其涉及增删导致的页分裂甚至需要更新非叶子结点

哈希索引

类似于数据结构中HASH表，当我们在mysql中用哈希索引时，主要就是通过Hash算法将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；
如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储

前缀索引

index(field(10))，使用字段值的前10个字符建立索引，
使用字段的部分前缀字符建立索引, 多余字段值较长, 并且前面几个字段差别较大的列比较合适, 节省了创建索引的空间成本

InnoDB的B+树索引的注意事项

一个B+树索引的根节点自诞生之日起，便不会再移动

内节点中目录项记录的唯一性

索引列值+主键值+页号保证了目录下记录的唯一性, 否则插入索引列值相同的记录不知道该插入到哪个位置

一个页面最少存储2条记录

这是因为B+树本质上就是一个大的多层级目录，每经过一个目录时都会过滤掉许多无效的子目录，直到最后访问到存储真实数据的目录。

MyISAM中的索引

使用B+树索引，但是却将索引和数据分开存储

- 将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为数据文件(MYD)。
- 这个文件并不划分为若干个数据页，有多少记录就往这个文件中塞多少记录就成了。
- 我们可以通过行号而快速访问到一条记录

使用 MyISAM 存储引擎的表会把索引信息另外存储到一个称为索引文件(MYI) 。

叶子结点存储的是索引列+行号, 不管是不是主键索引, 都需要进入数据文件通过行号找到对应的数据, 所以MYISAM索引都是二级索引, 都需要回表

B+树索引适用的条件

全值匹配

搜索条件中的列和索引列一致并且是等值匹配

只要索引覆盖了条件中的列, 并且是等值匹配, 顺序不影响, 因为查询优化器会重排这些条件使得可以用索引查询

匹配左边的列

搜索条件中的各个列必须是联合索引中从最左边连续的列

数据页和记录按照联合索引的索引列从左到右进行排序,在第一个列排序的基础上, 相同的第一列按第二列排序, 跳过左边的列, 剩下的列不是有序的

匹配列前缀

以字符串的前缀建立索引, 只要列字符串值前缀的区分度大可以到达近似按整个字符串匹配的效果但是节省了空间和时间成本, 比较的时候无需比较所有的字符, 并且B+树索引存储的数据减少, 无需存储所有的字符串

匹配范围值

范围匹配最左列

找到条件范围的左右端点, 在叶子结点上从左端点一直读到右端点因为记录是双链表前后连接的

等值匹配最左列并范围匹配其他列

最左边列等值匹配,, 查找到的记录是按照第二列排序的, 所以第二个条件如果是最左列的右边一列, 可以用到索引进行范围查找, 但是后续的索引列无法继续使用索引

范围查询索引最左列在内的多个列

只能用到最左列的索引, 查到符合最左列的数据后, 需要进行全部记录的搜索找到符合剩下所有条件的记录

排序

使用联合索引进行排序

ORDER BY 的子句后边的列的顺序也必须按照索引列的顺序给出

文件排序

无法使用索引排序, 需要将记录加载到内存中, 通过排序算法对记录进行排序(在内存或磁盘进行的排序)

不可以使用索引排序

ASC、DESC混用

索引只按照索引列的顺序升序排序, 对于升降序混合的排序规则, 无法使用索引

WHERE子句中出现非排序使用到的索引列

先查找符合where条件的记录再根据要求排序, 查找阶段无法使用索引

排序列包含非同一个索引的列

排序列使用了复杂的表达式

回表的代价

顺序IO(访问二级索引)

访问二级索引, 对于索引列的范围查询, 是顺序IO, 因为叶子结点根据索引列排序, 查询到的记录是几种分布在一个或多个数据页, 效率较高

随机IO(访问聚簇索引)

但是根据索引列记录得到的主键进行回表, 访问聚簇索引的过程因为, 这些查询二级索引得到的主键值并不是排序的, 可能分散在内存的不同的位置, 导致查询磁盘的过程分散, 速度也就慢了, 因为不能一次读取连续的数据, 需要等待磁盘旋转到对应的磁道扇区才能获得数据

全表扫描 vs 二级索引 + 回表

需要回表的记录数越多，就越倾向于使用全表扫描

加了 LIMIT 子句,这样需要回表的记录特别少，优化器就会倾向于使用二级索引 + 回表的方式执行查询。

覆盖索引

查询列表全部包含在二级索引或者联合索引, 查询到的记录中包含了这些查询列的值, 就无须到聚簇索引中回表查询了

索引不推荐使用 select * 而是查询列表写全需要查询的列, 这样存在对应的二级索引或联合索引就无须回表了

如何选择索引

只为用于搜索、排序或分组的列创建索引

用不到的列, 无须创建索引, 节省时间空间成本

列的基数(某列不重复的数据个数)

为那些列的基数大的列建立索引, 基数大表示不同的值多, 这样查询可以一次性过滤掉比较多的记录, 节省了IO成本, 获得更高的查询效率

索引列的类型尽量小

尽量让索引列使用较小的类型, 数据类型越小, 查询比较的操作越快, 保存索引的成本更小, 一个页可以保存更多的记录

尤其是主键, 因为聚簇索引存储主键, 其他二级索引也都存储了二级索引

索引字符串值的前缀

长字符串在查询比较的时候更耗时, 存储消耗更大的空间, 对于前缀差别较大的列比较适合, 因为只需要前缀就可以得到相同的区分度, 但是减少了比较的时间和存储的成本

主键自增

插入记录最好按照主键递增的顺序插入, 这样避免了在一堆数据中间插入一条记录导致满页产生页分裂, 这样可能导致内结点的结构发生变化, 如果主键自增, 每次插入的记录都在叶子结点最右边, 最多造成最右页的也分裂而不会对前面的页造成影响

避免冗余和重复索引

增加维护成本

让索引列在比较表达式中单独出现

尽量不要让索引列存在任何表达式中出现而是单独存在, 这样可以使用索引, 否则堆索引列进行运算后, 只能全表扫描. 因为查询条件因为表达式已经不是原来的索引列的值, 也就不能使用索引

B+树

- 存放用户记录的数据页，目录项记录的数据页，都把它们存放到 B+ 树这个数据结构中了，所以我们也称这些数据页为节点。
- 实际用户记录其实都存放在B+树的最底层的节点上，这些节点也被称为叶子节点或叶节点，
- 其余用来存放目录项的节点称为非叶子节点或者内节点，其中 B+ 树最上边的那个节点也称为根节点
- 规定最下边的那层，也就是存放我们用户记录的那层为第 0 层
- B+ 树都不会超过4层
- 通过主键值去查找某条记录最多只需要做4个页面内的查找
针对数据页所做的目录就是索引

为什么使用B+数索引

不使用Hash索引

很难找到合理的hash算法降低hash碰撞
不能进行范围查询, 只支持等值查询再进行回表
需要将hash表全部加载到内存占据较多宝贵的内存空间
memory使用hash索引因为数据都在内存, 范湖查询快于磁盘交互

不用二叉树红黑树

一个结点只能分二叉, 存储的数据有限, 因为数的深度过深导致IO次数变多, 影响数据读取的效率

B树

内结点存储数据, 一个页包含的索引信息, 一次读取页可以过滤的信息较少, 并且每层结点独立, 对于返回查询较慢

B树 vs B+树

B+树内部节点都是索引，没有记录，叶子节点同时存放键和记录
B树索引和记录存放在内部节点和叶子节点, 叶子节点各自独立

IO效率

B+树内结点只存储索引不包含记录, 一次读取，可以在内存页中获取更多的索引信息，有利于更快地缩小查找范围, 并且减少IO次数
B树所有结点都存储记录, 对于一次读取只能获得有限的索引信息, 增加了IO次数

热点数据查询

B+树所有记录都放在叶子结点, 都需要从根结点到叶子结点的查询, 效率比较稳定
B树将频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率

范围查询

B+树通过索引可以确定记录在叶子结点层的起始和终止页, 从左到右查询即可, 因为是双链表. 查询全部数据找到记录最小主键的记录从叶子结点从左到右扫描即可
B树所有结点都存储记录, 范围查询效率较低, 涉及更多次IO. 查询全部数据, 需要按层扫描, 涉及更多的页面置换

Hash索引 vs B+树索引

hash索引底层是hash表，进行查找时，对索引列进行hash就可以获取到相应的hash值(key)得到对应的主键，之后进行回表查询获得实际数据。
B+树底层实现是多路平衡查找树。对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。

等值查询

hash索引进行等值查询更快(一般情况下无hash碰撞)

范围查询

hash索引无法进行范围查询

因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询

hash索引不支持使用索引进行排序

hash索引不支持模糊查询以及多列索引的最左前缀匹配

回表

hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引，覆盖索引等)的时候可以只通过索引完成查询。

hash碰撞

hash索引虽然在等值查询上较快，但是不稳定。性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。
而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。

聚簇索引和非聚簇索引的区别

- 聚簇索引叶子结点存放的是数据, 并且是以主键排序的
- 非聚簇索引叶子结点存放的是索引键对应记录的主键
主要区别在于数据与索引的存储形式, 聚簇索引是在一起的而非聚簇索引是分开的

常见名词

回表

- 通过二级索引或者联合索引, 需要查找的数据不包括在索引列中, 所以二级索引联合索引的叶子结点没有包含需要的所有数据
- 需要根据查询出来的行记录的主键值去到聚簇索引找到这条主键对应的完全行记录得到我们需要的数据

索引覆盖

- 通过二级索引或者联合索引, 叶子结点中的数据包含了我们需要的列, 无需再去聚簇索引查询,
- 索引的列包含了全部的查询的列, 省去了回表的时间

最左匹配

查询条件对应的列按照联合索引列的顺序从左到右依次匹配, 遇到范围查询就不能再使用索引查询, 因为联合索引按照索引列从左到右升序排列, 这样对于索引列从左到右的排列, 自然的用到了索引排序的特点查询效率较快

索引下推

查询条件用到了索引, 这样索引列在磁盘上排序的, 在从磁盘获取数据的时候, 直接根据查询条件返回符合条件的记录, 而不是遇到符合条件一部分就返回再在内存中做筛选, 因为索引覆盖, 数据是排序的, 影响较小, 但是减少了IO次数,

MRR(multi_range_read)

- 利用二级索引查找age>10的记录得到了1000条记录的id(主键)值
- 正常需要根据这些id去聚簇索引中找到对应的行记录, 每个都需要从根结点到叶子结点1000 O(log(depth))
- 我们得到id值后可以对这些id进行排序然后到聚簇索引进行范围查询这样减少了查询的次数, 尽可能将随机IO变成多个顺序IO

FIC(fast index create)

给当前表添加一个Share锁, 插入删除数据不会有创建临时表的消耗, 还是源文件

连接

连接的本质就是把各个连接表中的记录都取出来将匹配的组合加入结果集并返回给用户

连接查询的执行过程

两表连接查询中，驱动表只需要访问一次，被驱动表可能被访问多次

第一个需要查询的表，这个表称之为驱动表。根据这个表的查询条件得到对应的记录

针对驱动表产生的结果集中的每一条记录，分别需要到 t2 表中查找匹配的记录

内连接和外连接

内连接 (INNER JOIN)

驱动表中的记录在被驱动表中找不到匹配的记录，该记录不会加入到最后的结果集
ON WHERE 等价

驱动表和被驱动表是可以互换的，并不会影响最后的查询结果。

外连接 (LEFT | RIGHT JOIN)

驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。对应的被驱动表记录的各个字段使用 NULL 值填充
必须使用 ON 子句来指出连接条件。

左外连接

选取左侧的表为驱动表。

右外连接

选取右侧的表为驱动表。

嵌套循环连接（Nested-Loop Join）

驱动表只访问一次，但被驱动表却可能被多次访问，访问次数取决于对驱动表执行单表查询后的结果集中的记录条数的连接执行方式

使用索引加快连接速度

对于驱动表查询出来的记录, 到被驱动表中查询的时候可以使用索引加快查询的速度, 这取决于二级索引+回表的方式是否快于全表扫描

基于块的嵌套循环连接（Block Nested-Loop Join）

核心思想将驱动表中的记录放入join buffer 将被驱动表加载到内存内的时候, 一次性和join buffer中的驱动表记录进行匹配, 减少了将被驱动表加载到内存中次数, 减少IO,

扫描一个表的过程其实是先把这个表从磁盘上加载到内存中，然后从内存中比较匹配条件是否满足。
被驱动表被访问多次的，如果被驱动表中的数据多而且不能使用索引进行访问，那就相当于要从磁盘上读好几次这个表，这个 I/O 代价就非常大了
驱动表结果集中有多少条记录，就得把被驱动表从磁盘上加载到内存中多少次进行比较
可不可以在把被驱动表的记录加载到内存的时候，一次性和多条驱动表中的记录做匹配，这样就大大减少重复从磁盘上加载被驱动表的代价了?

join buffer

- 执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个 join buffer 中，
- 然后开始扫描被驱动表，每一条被驱动表的记录一次性和 join buffer 中的多条驱动表记录做匹配，
- 因为匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的 I/O 代价
- 最好的情况是 join buffer 足够大，能容纳驱动表结果集中的所有记录，这样只需要访问一次被驱动表就可以完成连接操作了
- 驱动表的记录并不是所有列都会被放到 join buffer 中，只有查询列表中的列和过滤条件中的列才会被放到 join buffer 中，所以再次提醒我们，最好不要把 * 作为查询列表，只需要把我们关心的列放到查询列表就好了，这样还可以在 join buffer 中放置更多的记录

Buffer Pool

为了缓存磁盘中的页，在 MySQL 服务器启动的时候就向操作系统申请了一片连续的内存, 叫做 Buffer Pool

Buffer Pool内部组成

前面存放每个缓存页对应的控制块

页所属的表空间编号、页号、缓存页在 Buffer Pool 中的地址、链表节点信息、一些锁信息以及 LSN 信息

free链表

所有空闲的缓存页对应的控制块作为一个节点放到一个链表中

这个链表定义了一个基节点，里边儿包含着链表的头节点地址，尾节点地址，以及当前链表中节点的数量等信息

每当需要从磁盘中加载一个页到 Buffer Pool 中时，就从 free链表中取一个空闲的缓存页

flush链表

存储脏页(dirty page)的链表，凡是修改过的缓存页对应的控制块都会作为一个节点加入到一个链表中

每次修改缓存页后，我们并不着急立即把修改同步到磁盘上，而是在未来的某个时间点进行同步

LRU链表

创建一个链表，这个链表是为了按照最近最少使用的原则去淘汰缓存页的

简单LRU链表

访问的页不存在于buffer pool: 把页从磁盘加载到 Buffer Pool 中的缓存页时，就把该缓存页对应的控制块作为节点塞到链表的头部。

访问的页存在于buffer pool: 直接把该页对应的控制块移动到 LRU链表的头部。

存在的问题

预读机制导致: 加载到 Buffer Pool 中的页不一定被用到。

全表扫描导致: 使用频率偏低的页被同时加载到 Buffer Pool 时，可能会把那些使用频率非常高的页从Buffer Pool 中淘汰掉。

划分区域的LRU链表

热数据(young区域)

一部分存储使用频率非常高的缓存页

冷数据(old区域)

另一部分存储使用频率不是很高的缓存页

针对预读的页面可能不进行后续访情况的优化

当磁盘上的某个页面在初次加载到Buffer Pool中的某个缓存页时，该缓存页对应的控制块会被放到old区域的头部。
这样针对预读到 Buffer Pool 却不进行后续访问的页面就会被逐渐从old 区域逐出，而不会影响 young 区域中被使用比较频繁的缓存页

针对全表扫描时，短时间内访问大量使用频率非常低的页面情况的优化

- 在对某个处在 old 区域的缓存页进行第一次访问时就在它对应的控制块中记录下来这个访问时间，
- 如果后续的访问时间与第一次访问的时间在某个时间间隔内，那么该页面就不会被从old区域移动到young区域的头部，否则将它移动到young区域的头部
- innodb_old_blocks_time 控制间隔时间, 默认值是 1s

只有被访问的缓存页位于 young 区域的 1/4 的后边，才会被移动到 LRU链表头部，

LRU链表分区+控制访问间隔时间: 使得用不到的预读页面以及全表扫描的页面都只会被放到 old 区域，不影响 young 区域中的缓存页。

事务

数据库事务是指作为单个逻辑工作单元执行的一系列操作（SQL语句）。这些操作要么全部执行，要么全部不执行。

原子性（Atomicity）

处于一个事务中的操作要么全做要么全不做, 不可分割, 强调事务的不可分割.

undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql

一致性（Consistency）

事务的执行的前后数据的完整性保持一致 , 从一个一致性的状态到了另一个一致性的状态

代码层面来保证

隔离性（Isolation）

一个事务执行的过程中,不应该受到其他事务的干扰

MVCC来保证

持久性（Durability）

事务一旦结束,数据就持久到数据库

内存+redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，事务提交通过redo log刷盘，宕机的时候可以从redo log恢复

MySQL中事务的语法

开启事务

BEGIN

START TRANSACTION;

READ ONLY ：

标识当前事务是一个只读事务

READ WRITE ：

标识当前事务是一个读写事务

WITH CONSISTENT SNAPSHOT ：

启动一致性读

中止事务

ROLLBACK

ROLLBACK 语句就代表中止并回滚一个事务，
如果事务在执行过程中遇到了某些错误而无法继续执行的话，事务自身会自动的回滚

保存点(savepoint)

事务对应的数据库语句中打几个点，我们在调用 ROLLBACK 语句时可以指定会滚到哪个点，而不是回到最初的原点

SAVEPOINT 保存点名称;
ROLLBACK TO 保存点名称
RELEASE SAVEPOINT 保存点名称

提交事务

COMMIT

隐式提交

SET autocommit = ON

DDL(Data definition language)

CREATE 、ALTER 、 DROP

事务控制或关于锁定的语句

一个事务还没提交或者回滚时就又使用 START TRANSACTION 或者 BEGIN 语句开启了另一个事务时，会隐式的提交上一个事务

当前的 autocommit 系统变量的值为 OFF ，我们手动把它调为 ON 时，也会隐式的提交前边语句所属的事务

加载数据的语句

使用 LOAD DATA 语句来批量往数据库中导入数据时，也会隐式的提交前边语句所属的事务。

脏读, 幻读, 不可重复读

脏读（ Dirty Read ）

一个事务读到了另一个未提交事务修改过的数据

不可重复读（Non-Repeatable Read）

一个事务读到另一个已经提交的事务修改过的数据

幻读（Phantom）

一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来

事务的隔离级别

READ UNCOMMITTED ：未提交读。

READ COMMITTED ：已提交读。

REPEATABLE READ ：可重复读。

SERIALIZABLE ：可串行化。

Log

MySQL服务器层面

binlog

`binlog`用于记录数据库执行的更新数据库操作信息，以二进制的形式保存在磁盘中。
`binlog`是`mysql`的逻辑日志，并且由`Server`层进行记录，使用任何存储引擎的`mysql`数据库都会记录`binlog`日志。

`binlog`是通过追加的方式进行写入的，可以通过`max_binlog_size`参数设置每个`binlog`文件的大小，
当文件大小达到给定值之后，会生成新的文件来保存日志。

binlog使用场景

主从复制：在`Master`端开启`binlog`，然后将`binlog`发送到各个`Slave`端，`Slave`端重放`binlog`从而达到主从数据一致。

数据恢复：通过使用`mysqlbinlog`工具来恢复数据

binlog日志格式

`STATMENT`

- 基于`SQL`语句的复制(`statement-based replication, SBR`)，每一条会修改数据的sql语句会记录到`binlog`中。
- 优点：不需要记录每一行的变化，减少了`binlog`日志量，节约了`IO`, 从而提高了性能；
- 缺点：在某些情况下会导致主从数据不一致，比如执行`sysdate()`、`slepp()`等。

`ROW`

- 基于行的复制(`row-based replication, RBR`)，不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了。
- 优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题；
- 缺点：会产生大量的日志，尤其是`alter table`的时候会让日志暴涨

`MIXED`

- 基于`STATMENT`和`ROW`两种模式的混合复制(`mixed-based replication, MBR`)，
- 一般的复制使用`STATEMENT`模式保存`binlog`，
- 对于`STATEMENT`模式无法复制的操作使用`ROW`模式保存`binlog`

binlog和redo log的先后顺序及group commit

事务提交

先写binlog 再写 redolog

- flush阶段：向内存中写入每个事务的二进制日志。
- sync阶段：将内存中的二进制日志刷盘。若队列中有多个事务，那么仅一次fsync操作就完成了二进制日志的刷盘操作。
- commit阶段：leader根据顺序调用存储引擎层事务的提交，

binlog vs redo log

- redo log是属于innoDB层面，binlog属于MySQL Server层面的，这样在数据库用别的存储引擎时可以达到一致性的要求。
- redo log是物理日志，记录该数据页更新的内容；binlog是逻辑日志，记录的是这个更新语句的原始逻辑
- redo log是循环写，日志空间大小固定；binlog是追加写，是指一份写到一定大小的时候会更换下一个文件，不会覆盖。
- redo log作为异常宕机或者介质故障后的数据恢复使用。binlog可以作为恢复数据使用，主从复制搭建，

InnoDB存储引擎层面

redolog

逻辑日志: 记录事务对数据页做了哪些修改
将第0号表空间的100号页面的偏移量为1000处的值更新为 2 。

为什么需要redo log?

保证事务的持久性, 保证成功提交的事务对数据库的更改永久的保存到了磁盘, 如果每个事务结束都刷新到磁盘存在问题

1. 因为`Innodb`是以`页`为单位进行磁盘交互的，一个事务可能只修改一个数据页里面的几个字节，将完整的数据页刷到磁盘的话，浪费资源！
2. 一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差！

- redo 日志占用的空间非常小
- 存储表空间ID、页号、偏移量以及需要更新的值所需的存储空间是很小的
- redo 日志是顺序写入磁盘的
- 在执行事务的过程中，每执行一条语句，就可能产生若干条 redo 日志，这些日志是按照产生的顺序写入磁盘的，也就是使用顺序IO。

redo log基本组成

- 一个是内存中的日志缓冲(`redo log buffer`)，另一个是磁盘上的日志文件(`redo log file`)。
- `mysql`每执行一条`DML`语句，先将记录写入`redo log buffer`，后续某个时间点再一次性将多个操作记录写到`redo log file`。
- 这种先写日志，再写磁盘的技术就是`MySQL`里经常说到的`WAL(Write-Ahead Logging)` 技术。

用户空间下的缓冲区数据一般情况下是无法直接写入磁盘的，中间必须经过操作系统内核空间缓冲区(`OS Buffer`)。
因此，`redo log buffer`写入`redo log file`实际上是先写入`OS Buffer`，然后再通过系统调用`fsync()`将其刷到`redo log`

0（延迟写）

事务提交时不会将`redo log buffer`中日志写入到`os buffer`，而是每秒写入`os buffer`并调用`fsync()`写入到`redo log file`中。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据。

1（实时写，实时刷）

事务每次提交都会将`redo log buffer`中的日志写入`os buffer`并调用`fsync()`刷到`redo log file`中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差。

2（实时写，延迟刷）

每次提交都仅写入到`os buffer`，然后是每秒调用`fsync()`将`os buffer`中的日志写入到`redo log file`。

redo log记录形式

`redo log`实际上记录数据页的变更，而这种变更记录是没必要全部保存，因此`redo log`实现上采用了大小固定，循环写入的方式，当写到结尾时，会回到开头循环写日志
在innodb中，既有`redo log`需要刷盘，还有`数据页`也需要刷盘，`redo log`存在的意义主要就是降低对`数据页`刷盘的要求。

- `write pos`表示`redo log`当前记录的`LSN`(逻辑序列号)位置，
- `check point`表示数据页更改记录刷盘后对应`redo log`所处的`LSN`(逻辑序列号)位置。
- `write pos`到`check point`之间的部分是`redo log`空着的部分，用于记录新的记录；
- `check point`到`write pos`之间是`redo log`待落盘的数据页更改记录。
- 当`write pos`追上`check point`时，会先推动`check point`向前移动，空出位置再记录新的日志。

undolog

为了回滚而记录的这些信息称之为撤销日志( undo log)

- 你插入一条记录时，至少要把这条记录的主键值记下来，之后回滚的时候只需要把这个主键值对应的记录删掉就好了。
- 你删除了一条记录，至少要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了。
- 你修改了一条记录，至少要把修改这条记录前的旧值都记录下来，这样之后回滚时再把这条记录更新为旧值就好了

事务ID

给事务分配id的时机

只读事务

对于只读事务来说，只有在它第一次对某个用户创建的临时表执行增、删、改操作时才会为这个事务分配一个事务id ，否则的话是不分配事务id

临时表: 在许多查询的执行过程中， MySQL 可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们在执行许多包含 DISTINCT 、 GROUP BY 、 UNION 等子句的查询过程中，

读写事务

对于读写事务来说，只有在它第一次对某个表（包括用户创建的临时表）执行增、删、改操作时才会为这个事务分配一个事务id ，否则的话也是不分配事务id 的

事务ID如何生成

服务器内存中维护一个全局变量, 分配事务时,将变量值分配给事务, 变量值+1
每当这个变量的值为 256 的倍数时，就会将该变量的值刷新到系统表空间的页号为 5 的页面中一个称之为Max Trx ID 的属性处
系统重新启动时，会将Max Trx ID 属性加载到内存中，将该值加上256之后赋值给我们前边提到的全局变量

为什么加上256?

- 因为上一次事务可能Max_Trx_ID = 256*n + 250, 剩下的250还没刷新到磁盘
- 但是服务器崩溃了, 所以磁盘上的Max_Trx_ID = 256*n, 但是 [256 * n , 256 * n +250]都已经分配给其他事务了,
- 我们希望保证每个事务的ID都不同, 所以需要加上256 保证当前服务器启动之后分配的事务ID一定跟之前不同并且一定大于之前事务ID 的最大值
- 而中间没有分配的事务ID没关系, 因为我们只是希望保证事务ID不同并且有序但是不必要连续即可

roll pointer

本质上就是一个指向这条记录对应的 undo日志的一个指针, 占用7个字节

版本链

对于一条记录的操作会全部以undo log的形式形成一个单链表记录在roll_pointer中,
链表从头到尾是操作从新到旧的顺序, 链表头是最新的操作, 链表尾是最久的操作

一条更新语句执行的顺序

一条select语句的执行流程

客户端发起一个请求时，首先会建立一个连接

服务端会检查缓存，如果命中则直接返回，否则继续之后后面步骤

解析器根据收到的sql语句进行解析，然后对其进行词法分析，语法分析以及预处理

词法解析和语法解析(Parser)

检查sql语句的语法对不对，在这里，首先会把我们整个SQL语句打碎，比如：select name from test where id=1，就会被打散成select，name，from，test，where，id，=，1 这8个字符，并且能识别出关键字和非关键字，然后根据sql语句生成一个数据结构，也叫做解析树(select_lex)

预处理器(Preprocessor)

检查表名，列名以及其他一些信息等是不是真实存在的

查询优化器(Query Optimizer)生成执行计划

查询优化器的目的就是根据解析树生成不同的执行计划，然后选择一种最优的执行计划，MySQL 里面使用的是基于成本的优化器，哪种执行计划开销最小，就选择哪种。

调用存储引擎层API来执行查询

一条update语句的执行流程

先根据更新语句的条件，查询出对应的记录，如果有缓存，也会用到缓存

Server端调用InnoDB引擎API接口，InnoDB引擎将这条数据写到内存，同时写入redo log，并将redo log状态设置为prepare

通知Server层，可以正式提交数据了

Server层收到通知后立刻写入bin log，然后调用InnoD对应接口发出commit请求

InnoDB收到commit请求后将数据设置为commit状态

两阶段提交

两阶段提交是分布式事务的设计思想，就是首先会有请求方发出请求到各个服务器，然后等其他各个服务器都准备好之后再通知请求方可以提交了，请求方收到请求后再发出指令，通知所有服务器一起提交。

而我们这里redo log是属于存储引擎层的日志，bin log是属于Server层日志，属于两个独立的日志文件，采用两阶段提交就是为了使两个日志文件逻辑上保持一致

不采用两阶段提交

假如有一条语句id=1,age=18，我们现在要把这条数据的age更新为19：

先写 redo log 后写 binlog

假设在redo log 写完，binlog还没有写完的时候，MySQL发生了宕机(crash)。
重启后因为redo log写完了，所以会自动进行数据恢复，也就是age=19。但是由于binlog没写完就宕机( crash)了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。
然后某一天假如我们把数据丢失了，需要用bin log进行数据恢复就会发现少了这一次更新

先写binlog后写redo log

假如在binlog写完，redo log还没有写完的时候，MySQL发生了宕机(crash)。
重启后因为redo log没写完，所以无法进行自动恢复，那么数据就还是age=18了，
然后某一天假如我们把数据丢失了，需要用binlog进行恢复又会发现恢复出来的数据age=19了。

MVCC

版本连

一条记录每次更新后，都会将旧记录放到一条 undo日志中，就算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被 roll_pointer 属性连接成一个链表，我们把这个链表称之为版本链

ReadView

读取一条记录需要判断一下版本链中的哪个版本是当前事务可见的

ReadView的组成

m_ids ：

表示在生成 ReadView 时当前系统中活跃的读写事务的事务id 列表。

min_trx_id ：

表示在生成 ReadView 时当前系统中活跃的读写事务中最小的事务id ，也就是 m_ids 中的最小值。

max_trx_id ：

表示生成 ReadView 时系统中应该分配给下一个事务的 id 值。

creator_trx_id

表示生成该 ReadView 的事务的事务id 。

判断记录哪个版本对当前事务可见的规则

被访问版本的trx_id 属性值 = ReadView 中的 creator_trx_id 值，

意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。

被访问版本的 trx_id 属性值< ReadView 中的 min_trx_id 值，

表明生成该版本的事务在当前事务生成 ReadView 前已经提交，所以该版本可以被当前事务访问。

被访问版本的 trx_id 属性值> ReadView 中的 max_trx_id 值，

表明生成该版本的事务在当前事务生成 ReadView 后才开启，所以该版本不可以被当前事务访问

被访问版本的属性值 min_trx_id< trx_id< max_trx_id ，需要判断

trx_id 属性值在 m_ids 列表中

说明创建 ReadView 时生成该版本的事务还是活跃的(未提交)，该版本不可以被访问；

trx_id 属性值不在 m_ids 列表中

说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问

READ COMMITTED

每次查询开始时都会生成一个独立的ReadView。

每次查询时生成的ReadView可能包含了不同的活跃事务id集合,

导致了可能存在第一次查询时存在于活跃事务id集合中的事务在第二次查询的时候已经提交了,

并且这个在两次查询之间提交的事务id一定不会大于第二次查询时的max_trx_id (下一个待分配的事务id)

如果小于第二次查询时活跃事务id集合中的最小id, 那么这个版本是可见的

如果大于最小id, 那么这个版本的事务id就在 min_trx_id max_trx_id之间,
因为这个事务已经提交了所以不在活跃事务id集合中, 所以是可见的

这样就存在了两次查询一个同样的记录产生不同的结果

根本的原因还是每次读取数据前都会生成ReadView导致判断版本是否可见时的条件不同

REPEATABLE READ

只在第一次读取数据时生成一个ReadView

只会在第一次执行查询语句时生成一个 ReadView ，之后的查询就不会重复生成了

第一次查询时的ReadView决定以后查询时决定版本是否可见的条件

不论后续其他事务做了任何改变, ReadView的 m_ids, min_trx_id max_trx_id creator_trx_id 都是相同,

所以对于同一条记录来说不论查询几次, 因为判断版本是否可见的决定条件相同, 所以一定会产生相同的结果

MVCC小结

所谓的 MVCC （Multi-Version Concurrency Control ，多版本并发控制）指的就是在使用 READ COMMITTD 、 REPEATABLE READ 这两种隔离级别的事务在执行普通的 SELECT 操作时访问记录的版本链的过程，这样子可以使不同事务的读-写写-读操作并发执行，从而提升系统性能

锁

怎么解决脏读、不可重复读、幻读

读操作利用 MVCC ，写操作加锁。

MVCC生成ReadView可以读取记录的历史版本但是无法读到生成ReadView时未提交的事务和之后才开启的事务

写操作针对的是记录的最新版本, 读记录的历史版本和改动记录的最新版本本身并不冲突，也就是采用MVCC 时，读-写操作并不冲突。

READ COMMITTED

一个事务在执行过程中每次执行SELECT操作时都会生成一个ReadView，ReadView的存在本身就保证了事务不可以读取到未提交的事务所做的更改，也就是避免了脏读现象；

REPEATABLE READ

一个事务在执行过程中只有第一次执行SELECT操作才会生成一个ReadView，之后的SELECT操作都复用这个ReadView，这样也就避免了不可重复读和幻读的问题。

读、写操作都加锁

一些业务场景不允许读取记录的旧版本，而是每次都必须去读取记录的最新版本

读取记录的时候也就需要对其进行加锁操作，这样也就意味着读操作和写操作也像写-写操作那样排队执行

脏读

如果另一个事务在写记录的时候就给这条记录加锁，那么当前事务就无法继续读取该记录了，所以也就不会有脏读问题的产生了。

不可重复读

如果在当前事务读取记录时就给该记录加锁，那么另一个事务就无法修改该记录，自然也不会发生不可重复读了。

幻读

采用加锁的方式解决幻读问题就有那么一丢丢麻烦了(间隙锁)，因为当前事务在第一次读取记录时那些幻影记录并不存在，所以读取的时候加锁就有点尴尬

一致性读（Consistent Reads）

事务利用 MVCC 进行的读取操作称之为一致性读，或者一致性无锁读，有的地方也称之为快照读

所有普通的 SELECT 语句（ plain SELECT ）在 READ COMMITTED 、 REPEATABLE READ 隔离级别下都算是一致性读

一致性读并不会对表中的任何记录做加锁操作，其他事务可以自由的对表中的记录做改动。

锁定读（Locking Reads）

共享锁(Shared Locks)

简称 S锁。在事务要读取一条记录时，需要先获取该记录的 S锁只可以和S锁共存

SELECT ... LOCK IN SHARE MODE;

独占锁(Exclusive Locks)

简称 X锁。在事务要改动一条记录时，需要先获取该记录的 X锁和任何锁都不能共存

SELECT ... FOR UPDATE;

写操作

DELETE

- 对一条记录做 DELETE 操作的过程其实是先在 B+ 树中定位到这条记录的位置，
- 然后获取一下这条记录的 X锁，然后再执行 delete mark 操作。

UPDATE

主键不变, 所有列存储空间不变

- 则先在 B+ 树中定位到这条记录的位置，然后再获取一下记录的 X锁，
- 最后在原记录的位置进行修改操作。

主键不变, 存在列存储空间变化

- 则先在B+ 树中定位到这条记录的位置，然后获取一下记录的 X锁，
- 将该记录彻底删除掉（就是把记录彻底移入垃圾链表），
- 最后再插入一条新记录。

主键改变

则相当于在原记录上做 DELETE 操作之后再来一次 INSERT 操作，

INSERT

通过隐式锁保证这条记录在事务提交前不被其他事务访问

意向锁(Intention Locks)

意向共享锁( Intention Shared Lock) IS锁

当事务准备在某条记录上加 S锁时，需要先在表级别加一个 IS锁

意向独占锁(Intention Exclusive Lock) IX锁

当事务准备在某条记录上加 X锁时，需要先在表级别加一个 IX锁

IS、IX锁是表级锁，它们的提出仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录，也就是说其实IS锁和IX锁是兼容的，IX锁和IX锁是兼容的

InnoDB存储引擎中的锁

表级锁

S, IS, X, IX

AUTO-INC锁

采用 AUTO-INC 锁，也就是在执行插入语句时就在表级别加一个 AUTO-INC 锁，
然后为每条待插入记录的 AUTO_INCREMENT 修饰的列分配递增的值，在该语句执行结束后，再把 AUTO-INC 锁释放掉。
这样一个事务在持有 AUTO-INC 锁的过程中，其他事务的插入语句都要被阻塞

采用一个轻量级的锁，在为插入语句生成 AUTO_INCREMENT 修饰的列的值时获取一下这个轻量级锁，
然后生成本次插入语句需要用到的 AUTO_INCREMENT 列的值之后，就把该轻量级锁释放掉，并不需要等到整个插入语句执行完才释放锁

行级锁

Record Locks ：(LOCK_REC_NOT_GAP) 记录锁

把一条记录锁上

记录S锁

记录X锁

Gap Locks ：(LOCK_GAP) 间隙锁

给某条记录加上间隙锁表示该记录与前一条记录之间的区间不允许插入记录

主要是为了解决幻读

如果想给数据页的最后一条记录后面的间隙加锁需要在最后一条记录所在的数据页的 Supremum记录加上间隙锁

Next-Key Locks( LOCK_ORDINARY)

既锁住某条记录，又锁住记录前边的间隙 = Record Lock +Gap Lock

Insert Intention Locks ：((LOCK_INSERT_INTENTION) 插入意向锁

事务插入记录的时候发现当前区间被加了Gap Lock, 会生成一个插入意向锁

隐式锁

一般的插入操作不加锁

别的事务在对这条插入记录加 S锁或者 X锁时，由于隐式锁的存在，会先帮助当前事务生成一个锁结构，然后自己再生成一个锁结构后进入等待状态

通过这条新插入的记录的事务id 判断是否可以对这条记录进行读写操作, 不可以的时候再显示给这条记录加锁

主从复制

负载增加, 超过一台服务器的处理上限, 需要负载均衡, 主库写, 从库读

类型

复制类型

同步复制

当主库执行完一个事务，然后所有的从库都复制了该事务并成功执行完才返回成功信息给客户端。

异步复制

主节点不会主动推送数据到从节点，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理

主节点如果崩溃掉了，此时主节点上已经提交的事务可能并没有传到从节点上，如果此时，强行将从提升为主，可能导致新主节点上的数据不完整。

半同步复制

确保任何一个主库上的事物在提交之前至少有一个从库已经收到该事物并日志记录下来。

复制形式

一主一从

一主多从

一主一从和一主多从是我们现在见的最多的主从架构，使用起来简单有效，不仅可以实现 HA，而且还能读写分离，进而提升集群的并发能力

多主一从

多主一从可以将多个 MySQL 数据库**备份**到一台存储性能比较好的服务器上。

双主复制

双主复制，也就是可以互做主从复制，每个 master 既是 master，又是另外一台服务器的 salve。这样任何一方所做的变更，都会通过**复制**应用到另外一方的数据库中。

级联复制

- 级联复制模式下，部分 slave 的数据同步不连接主节点，而是连接**从节点**。
- 因为如果主节点有太多的从节点，就会损耗一部分性能用于 replication ，那么我们可以让 3~5 个从节点连接主节点，其它从节点作为二级或者三级与从节点连接，这样不仅可以**缓解**主节点的压力，并且对**数据一致性**没有负面影响。

复制过程

从库生成两个线程，一个 I/O 线程，一个 SQL 线程；

I/O 线程去请求主库的 binlog，并将得到的 binlog 日志写到 relay log(中继日志) 文件中；

主库会生成一个 log dump 线程，用来给从库 I/O 线程传 binlog

SQL 线程会读取 relay log 文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致；

单表访问

索引合并(index merge)

使用到多个索引来完成一次查询的执行方法称之为： index merge ，

Intersection合并

使用不同索引的搜索条件之间使用 AND 连接起来的情况
SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';
从两个二级索引查询到相关的记录, 将得到的主键id取交集, 再到聚簇索引进行回表查询完整记录, 减少了回表的次数, 降低了随机IO

可以使用的情况

二级索引列是等值匹配，联合索引每个列都必须等值匹配

因为等值匹配, 一个二级索引列相同的情况下, 根据主键ID排序,
Intersection 索引合并会把从多个二级索引中查询出的主键值求交集，如果从各个二级索引中查询的到的结果集本身就是已经按照主键排好序的，那么求交集的过程就是O(n)双指针

主键列可以是范围匹配

涉及主键的搜索条件只不过是为了从别的二级索引得到的结果集中过滤记录

Intersection索引合并的适用场景是单独根据搜索条件从某个二级索引中获取的记录数太多，导致回表开销太大，合并后可以明显降低回表开销，

Union合并

使用不同索引的搜索条件之间使用 OR 连接起来的情况
SELECT * FROM single_table WHERE key1 = 'a' OR key3 = 'b'

可以使用的情况

二级索引列是等值匹配，联合索引每个列都必须等值匹配

主键列可以是范围匹配

使用 Intersection 索引合并的搜索条件

对单独索引查询到的主键集合取并集，然后进行回表操作，将结果返回给用户。

Sort-Union合并

先按照二级索引记录的主键值进行排序，之后按照 Union 索引合并方式执行的方式称之为 Sort-Union 索引合并

Sort-Union的适用场景是单独根据搜索条件从某个二级索引中获取的记录数比较少，这样即使对这些二级索引记录按照主键值进行排序的成本也不会太高

查询优化

基于成本

成本类型

I/O 成本

从磁盘到内存这个加载的过程损耗的时间

读取一个页面花费的成本默认是 1.0 ，

CPU 成本

读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为 CPU 成本。

读取以及检测一条记录是否符合搜索条件的成本默认是 0.2

基于成本的优化步骤

根据搜索条件，找出所有可能使用的索引

possible keys

一个查询中可能使用到的索引

只要索引列和常数使用 = 、 <=> 、 IN 、 NOT IN 、 IS NULL 、 IS NOT NULL 、 > 、 < 、 >= 、 <= 、 BETWEEN 、 != （不等于也可以写成 <> ）或者 LIKE 操作符连接起来，就可以产生一个所谓的范围区间（ LIKE 匹配字符串前缀也行），也就是说这些搜索条件都可能使用到索引

计算全表扫描的代价

把聚簇索引中的记录都依次和给定的搜索条件做一下比较，把符合搜索条件的记录加入到结果集

需要将聚簇索引对应的页面加载到内存中，然后再检测记录是否符合搜索条件。

查询成本= I/O 成本+ CPU 成本

聚簇索引占用的页面数

该表中的记录数

每个表维护了一系列的统计信息, SHOW TABLE STATUS 语句来查看表的统计信息

计算使用不同索引执行查询的代价

根据索引条件找到二级索引中的范围区间, 一个范围区间看做从磁盘读取一个页对应一个I/O成本

统计每个范围区间的左右区间端点页, 统建范围区间一共多少条记录
范围区间所有的记录的数量就是多少个CPU成本因为要读取这些二级索引页获取主键信息

根据获取的记录数到聚簇索引中查询每条记录, 并对剩余的过滤条件进行比较

涉及对应记录数的I/O成本, 每条记录看做读取一个页

每条记录需要对剩下的过滤条件比较涉及CPU成本

对比各种执行方案的代价，找出成本最低的那一个

连接查询的成本

Condition filtering

猜驱动表的扇出有多少的过程

连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本

对于左（外）连接和右（外）连接查询

分别为驱动表和被驱动表选择成本最低的访问方法。

对于内连接

不同的表作为驱动表最终的查询成本可能是不同的，也就是需要考虑最优的表连接顺序。

然后分别为驱动表和被驱动表选择成本最低的访问方法。

基于规则

条件化简

移除不必要的括号

常量传递（constant_propagation）

a = 5 AND b > a
=>
a = 5 AND b > 5

等值传递（equality_propagation）

a = b and b = c and c = 5
=>
a = 5 and b = 5 and c = 5

移除没用的条件（trivial_condition_removal）

(a < 1 and b = b) OR (a = 6 OR 5 != 5)
=>
a < 1 OR a = 6

表达式计算

a = 5 + 1 => a = 6

HAVING子句和WHERE子句的合并

如果查询语句中没有出现诸如 SUM 、 MAX 等等的聚集函数以及 GROUP BY 子句，优化器就把 HAVING 子句和WHERE 子句合并起来。

外连接消除

- 内连接的驱动表和被驱动表的位置可以互换，而左（外）连接和右（外）连接驱动表和被驱动表是固定的。
- 这就导致内连接可能通过优化表的连接顺序来降低整体的查询成本，而外连接却无法优化表的连接顺序。
- 外连接和内连接的本质区别就是：
- 对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充；
- 而内连接的驱动表的记录如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录会被舍弃
- 右（外）连接和左（外）连接其实只在驱动表的选取方式上是不同的，其余方面都是一样的，所以优化器会首先把右（外）连接查询转换成左（外）连接查询

通过WHERE子句消除外连接

只要我们在搜索条件中指定关于被驱动表相关列的值不为 NULL ，那么外连接中在被驱动表中找不到符合 ON 子句条件的驱动表记录也就被排除出最后的结果集了，

外连接和内连接也就没有什么区别了

SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2 WHERE t2.n2 IS NOT NULL;

SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2 WHERE t2.m2 = 2;
<==>
SELECT * FROM t1 INNER JOIN t2 ON t1.m1 = t2.m2 WHERE t2.m2 = 2;

reject-NULL

- 我们把这种在外连接查询中，指定的 WHERE 子句中包含被驱动表中的列不为 NULL 值的条件称之为空值拒绝（ reject-NULL ）。
- 在被驱动表的WHERE子句符合空值拒绝的条件后，外连接和内连接可以相互转换。
- 这种转换带来的好处就是查询优化器可以通过评估表的不同连接顺序的成本，选出成本最低的那种连接顺序来执行查询。

子查询优化

子查询可以出现的位置

出现在某个查询语句的中的查询就被称为子查询

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);

按返回的结果集区分子查询

标量子查询