# MySQL
# SQL查询语句的执行流程
- 连接器：建立tcp连接，身份认证，维护连接
- 查询缓存：语句如果命中缓存就直接返回，否则继续执行，MySQL8.0移除
- 解析SQL：语法分析，词法分析，建立语法树
- 预处理：检查表和字段是否存在，* 拓展为表上所有列
- 优化：SQL语句优化，制定成本最小的执行计划
- 执行：执行计划，从存储引擎查找数据
- 返回数据：返回给客户端和更新缓存（8.0以废除缓存）
# SQL数据如何存储
# 表空间文件的结构
表空间由段，区，页，行组成
- 行：表中的数据都是一行一行记录的
- 页：考虑到效率问题，数据库以页为单位进行读写，默认每个页大小是16kb。
- 区：每个区的大小为 1MB，对于 16KB 的页来说，连续的 64 个页会被划为一个区，这样就使得链表中相邻的页的物理位置也相邻，就能使用顺序 I/O ，避免了大量的随机IO
- 段：表空间是由各个段（segment）组成的，段是由多个区（extent）组成的。段一般分为==数据段、索引段和回滚段==等。
# InnoDB行格式
- Redundant 是很古老的行格式了， MySQL 5.0 版本之前用的行格式，现在基本没人用了。
- ==Compact== 是一种紧凑的行格式，设计的初衷就是为了让一个数据页中可以存放更多的行记录，从 MySQL 5.1 版本之后，行格式默认设置成 Compact。
- Dynamic 和 Compressed 两个都是紧凑的行格式，它们的行格式都和 Compact 差不多，因为都是基于 Compact 改进一点东西。从 MySQL==5.7== 版本之后，==默认使用 Dynamic== 行格式。
# COMPACT行格式
# ==变长字段长度列表==
这些变长字段的真实数据占用的字节数会按照列的顺序**==逆序存放==**，主要是因为「记录头信息」中指向下一个记录的指针，指向的是==下一条记录的「记录头信息」和「真实数据」之间的位置==，这样的好处是向左读就是记录额外信息，向右读就是真实数据，比较方便。因为这样可以==使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时在一个 CPU Cache Line 中，这样就可以提高 CPU Cache 的命中率。（对称记录）==
# ==NULL值列表==
如果存在为NULL的的字段，那么这样的字段都会对应一个二进制位，并且也是按照列的先后进行==逆序排列==，并且必须是==整数字节==，不足的话==高位补零==
- bit位为1，则值为null
- bit为为0，则值非null
NULL值列表只有在字段定义存在NULL值的时候有。
# ==记录头信息==
- ==delete_mask== ：标识此条数据是否被删除。从这里可以知道，我们执行 detele 删除记录的时候，并不会真正的删除记录，只是将这个记录的 delete_mask 标记为 1。
- ==next_record==：下一条记录的位置。从这里可以知道，记录与记录之间是通过链表组织的。在前面我也提到了，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便。
- ==record_type==：表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录
# ==记录的真实数据==
记录真实数据部分除了我们定义的字段，还有三个隐藏字段，分别为：==row_id、trx_id、roll_pointer==
- row_id:表没有定义主键和约束列，数据库自动生成的用于构建的唯一==自增索引字段==，==6个字节==大小；如果定义了上述键，则不存在该字段，所以该字段非必须
- trx_id: 事务id，那个事务生成的，必须，==6个字节==
- roll_pointer: 上一个版本的指针，必须，==7个字节==
# VarChar中n最大是多少
MySQL 规定除了 TEXT、BLOBs 这种大对象类型之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过 ==65535== 个字节。
根据行的组成可以知道影响因素：可变字段，null值字段，值，字符集
ascii字符集（1）：65535- 1（null字段）-2（可变字段长度列表）= ==65532==
utf-8字符集(1-4)：（65535-1-2）/ 3= ==21833==
# 行溢出怎么处理
一个页的大小是16kb，就是16384字节，一个varchar最多可以存储65532字节，对于大对象TEXT，BLOB可能更多，一个页可能存不了一条记录，这个时候就会发生行溢出，多的数据会转存到==溢出页==.
发生行溢出后，==行记录的真实数据会存一部分数据，并用20字节存储指向溢出页位置的地址==（姑且称为==半溢出==）；==Compressed 和 Dynamic 这两个行格式会不存数据，只存一个地址指针==（姑且称为==全溢出==）。
# 页空间结构
数据页中的记录按照==主键顺序组成单向链表==
页目录起到记录索引的作用，能够快速定位
# MySQL索引
# 索引分类
- 按「==数据结构==」分类：B+tree索引、Hash索引、Full-text索引。
- 按「==物理存储==」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
- 按「==字段特性==」分类：主键索引、唯一索引、普通索引、前缀索引。
- 按「==字段个数==」分类：单列索引、联合索引。（联合索引可以看成一个二级索引，只不过这个索引是多个字段拼接而来的）
# 索引的数据结构
索引采用的是B+树存储
- B tree B+树只在叶子节点存储数据，而B树的非叶子节点也存储数据，相同IO下能查询出更多的数据 同时B+树叶子节点之间采用双向链表连接，更适合顺序查询
- 二叉树 二叉树搜索复杂度log2N，B+tree根据节点允许的最大子节点个数m其复杂度是logmN，一般m大于100对于千万级数据来说B+树高度不过3-4层，而二叉树非常高了，每高一层就代表多一次磁盘IO
- Hash Hash对于等值查询很有优势，但是难以做到范围查询
# MySQL为什么使用B+tree
要设计一个 MySQL 的索引数据结构，不仅仅考虑数据结构==增删改==的时间复杂度，更重要的是要考虑==磁盘 I/0== 的操作次数。因为索引和记录都是存放在硬盘，硬盘是一个非常慢的存储设备，我们在查询数据的时候，最好能在尽可能少的磁盘 I/0 的操作次数内完成。
二分查找树虽然是一个天然的二分结构，能很好的利用二分查找快速定位数据，但是它存在一种极端的情况，每当插入的元素都是树内最大的元素，就会导致==二分查找树退化成一个链表==，此时查询复杂度就会从 O(logn)降低为 O(n)。
为了解决二分查找树退化成链表的问题，就出现了自平衡二叉树，保证了查询操作的时间复杂度就会一直维持在 O(logn) 。但是它本质上还是一个二叉树，每个节点只能有 2 个子节点，随着元素的增多，==树的高度会越来越高==。
而树的高度决定于磁盘 I/O 操作的次数，因为树是存储在磁盘中的，访问每个节点，都对应一次磁盘 I/O 操作，也就是说树的高度就等于每次查询数据时磁盘 IO 操作的次数，所以树的高度越高，就会影响查询性能。
B 树和 B+ 都是通过多叉树的方式，会将树的高度变矮，所以这两个数据结构非常适合检索存于磁盘中的数据。
但是 MySQL 默认的存储引擎 InnoDB 采用的是 B+ 作为索引的数据结构，原因有：
- B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比存储即存索引又存记录的 B 树，B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。
- B+ 树有大量的冗余节点（所有非叶子节点都是冗余索引），这些冗余索引让 B+ 树在插入、删除的效率都更高，比如删除根节点的时候，不会像 B 树那样会发生复杂的树的变化；
- B+ 树叶子节点之间用链表连接了起来，有利于范围查询，而 B 树要实现范围查询，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树。
# 索引优化
前缀索引优化 某个字段字符串的前几个字符建立索引，这样可以减小索引字段的大小，增加一个索引页中索引的数量，间接提高查询速度。但是前缀索引无法作为覆盖索引，order by也无法使用前缀索引
索引覆盖优化 从二级索引中查询的到数据，避免回表操作
主键索引最好自增
每次插入数据都是追加操作，不需要重新移动数据，调整树的结构，如果使用非自增主键可能在插入数据的时候引发页分裂和内存碎片，导致结构不紧凑，影响查询效率
索引设置为 Not Null
索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化，因为可为 NULL 的列会使索引、索引统计和值比较都更复杂，比如进行索引统计时，count 会省略值为NULL 的行。
NULL 值是一个没意义的值，但是它会占用物理空间
防止索引失效
All（全表扫描）；
index（全索引扫描）；
range（索引范围扫描）；
ref（非唯一索引扫描）；
eq_ref（唯一索引扫描）；
const（结果只有一条的主键或唯一索引扫描）。 尽量使用range及以下的方式进行查询
# 索引下推
联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
# 回表
二级索引查询到主键后去主键索引查询数据
# 覆盖索引
二级索引查询到的值包含需要查询的值
# 索引失效
使用左或者左右模糊查询 索引都是根据字段进行前缀匹配，模糊查询无法进行匹配定位到索引位置，只能采用全表扫描的办法 这种方法也不一定会失效，只有两个字段，一个主键索引，一个二级索引，查询的时候就直接查询二级索引，因为二级索引就包含了全部的字段数据（id）,索引覆盖。
采用函数 索引B+树结构中都是存储的原始索引字段的数据，进行函数计算后自然无法进行匹配；不过mysql8.0新出了函数索引，允许对字段数值进行函数操作后的值作为索引建立B+树
采用表达式 a+1=10,这种写法和采用函数差不多；a=10-1这种写法会进行优化，送给存储引擎的就是a=9，会走索引
联合索引不符合最左匹配原则 联合索引是按照第一个索引字段进行排序，只有第一个相同才会看第二个字段，以此类推。可以这样理解，联合索引就像是一个二级索引，这个二级索引是不同字段顺序拼接组成的，不符合最左匹配原则就相当于使用了左模糊查询。肯定是会失效的啊
where or前面使用索引，后面没有使用索引 where or是条件查询，只要满足一个就能查询。
字段隐式转换 字段是字符串，查询使用整型会全表扫描；但是反过来索引生效 因为MySQL在字符串和数字进行比较的时候会将字符串转化为数字进行比较。相当于进行了函数调用
select * from t_user where CAST(phone AS signed int) = 1300000001; select * from t_user where id = CAST("1" AS signed int);1
2
索引不一定就是能够排除一些数据快，有可能用了索引但是也是全局扫描了一边索引，即使这样索引也是比全表扫描要快的，因为索引数据更集中，查询字段较少，链表连接，需要的磁盘IO一般也较少。主要还是IO成本较低
# Count操作
count()是一个==聚合函数==，可以统计符合条件的数据条数。 MySQL的server层会维护一个count变量，统计到符合数据就会＋1，最后将结果返回给客户端
# Count（主键|条件）
如果没有二级索引，直接循环遍历索引B+树；当有二级索引时会遍历二级索引，如果符合条件就＋1
# Count（1）
如果没有二级索引，直接循环遍历索引B+树；当有二级索引时会遍历二级索引，如果有多个二级索引就遍历key_len最短的哪一个，查到记录就＋1，注意不会去查询字段
# Count（*）
count（*）执行时会被优化成count（0），执行流程和count（1）一样
# Count（字段）
遍历数据字段
count（1）=count(*)>count(索引字段)>count（字段）
大表count（*）语句执行较慢，可以使用explain进行估计，也可以维护一张计数表
explain select count(*) from table
# MySQL事务
# 事物的特性
A: Automicity 原子性，一个事务的操作要么成功，要么失败---》undo log回滚日志保证 C: Consistency 一致性，事务操作前后，数据满足完整性约束---》AID来保证 I: Isolation 隔离性，多个并发事务之间的数据空间是隔离的---》mvcc或锁机制 D: Durability 持久性，事务结束后对数据的修改是永久的---》redo log重做日志保证
# 并行事务
处理多个事务的时候会出现脏读，幻读，不可重复读的问题
# 脏读
一个事务读取到了另一个未提交的事务修改过的数据。
# 不可重复读
一个事务内多次查询同一个数据，所得到的数据不一样
# 幻读
在一个事务内多次查询某个符合条件的记录数量，前后查询的得到的数量不一致
# 事务的隔离级别
读未提交：一个事务所做的变更在它未提交的时候可以被其他事务看到（脏读、幻读、不可重复读）直接读取最新数据
读已提交：一个事务所做的变更在他提交之后才能被其他事务看到（幻读、不可重复读） ReadView每个语句执行前生成
可重复读：一个事务执行过程中看到的数据和启动时看到的数据一致（幻读）默认的隔离级别 ReadView开启事务前生成
串行化：在多个事务对这条记录进行读写操作时，后访问的事务必须等前一个事务执行完成，才能继续执行（null） 读写锁
# MVCC
# ReadView在MVCC如何工作
ReadView四个字段
聚簇索引记录中的两个隐藏列：
- trx_id: 修改行记录的事务id
- roll_pointer:指向undo log里面旧版本的行记录
创建ReadView后，可以将事务分为三类：
一个事务去访问记录的时候，除了自己的更新记录总是可见之外，还有这几种情况：
- 如果记录的 trx_id 值小于 Read View 中的
min_trx_id值，表示这个版本的记录是在创建 Read View 前已经提交的事务生成的，所以该版本的记录对当前事务可见。 - 如果记录的 trx_id 值大于等于 Read View 中的
max_trx_id值，表示这个版本的记录是在创建 Read View 后才启动的事务生成的，所以该版本的记录对当前事务不可见。 - 如果记录的 trx_id 值在 Read View 的min_trx_id和max_trx_id之间，需要判断 trx_id 是否在 m_ids 列表中：
- 如果记录的 trx_id 在
m_ids列表中，表示生成该版本记录的活跃事务依然活跃着（还没提交事务），所以该版本的记录对当前事务不可见。 - 如果记录的 trx_id 不在
m_ids列表中，表示生成该版本记录的活跃事务已经被提交，所以该版本的记录对当前事务可见。
- 如果记录的 trx_id 在
这种通过「版本链」来控制并发事务访问同一个记录时的行为就叫 MVCC（多版本并发控制）。
# 可重复读控制流程
可重复读是在事务启动时生成的一个ReadView，整个事务期间数据都用这个ReadView
- 事务 B 读取小林的账户余额记录，读到余额是 100 万；
- 事务 A 将小林的账户余额记录修改成 200 万，并没有提交事务；
- 事务 B 读取小林的账户余额记录，读到余额还是 100 万；
- 事务 A 提交事务；
- 事务 B 读取小林的账户余额记录，读到余额依然还是 100 万；
事务 B 第一次读小林的账户余额记录，在找到记录后，它会先看这条记录的 trx_id，此时发现 trx_id 为 50，比事务 B 的 Read View 中的 min_trx_id 值（51）还小，这意味着修改这条记录的事务早就在事务 B 启动前提交过了，所以该版本的记录对事务 B 可见的，也就是事务 B 可以获取到这条记录。
接着，事务 A 通过 update 语句将这条记录修改了（还未提交事务），将小林的余额改成 200 万，这时 MySQL 会记录相应的 undo log，并以链表的方式串联起来，形成版本链，如下图：
你可以在上图的「记录的字段」看到，由于事务 A 修改了该记录，以前的记录就变成旧版本记录了，于是最新记录和旧版本记录通过链表的方式串起来，而且最新记录的 trx_id 是事务 A 的事务 id（trx_id = 51）。
然后事务 B 第二次去读取该记录，发现这条记录的 trx_id 值为 51，在事务 B 的 Read View 的 min_trx_id 和 max_trx_id 之间，则需要判断 trx_id 值是否在 m_ids 范围内，判断的结果是在的，那么说明这条记录是被还未提交的事务修改的，这时事务 B 并不会读取这个版本的记录。而是沿着 undo log 链条往下找旧版本的记录，直到找到 trx_id 「小于」事务 B 的 Read View 中的 min_trx_id 值的第一条记录，所以事务 B 能读取到的是 trx_id 为 50 的记录，也就是小林余额是 100 万的这条记录。
最后，当事物 A 提交事务后，由于隔离级别时「可重复读」，所以事务 B 再次读取记录时，还是基于启动事务时创建的 Read View 来判断当前版本的记录是否可见。所以，即使事物 A 将小林余额修改为 200 万并提交了事务， 事务 B 第三次读取记录时，读到的记录都是小林余额是 100 万的这条记录。
就是通过这样的方式实现了，「可重复读」隔离级别下在事务期间读到的记录都是事务启动前的记录。
# 读提交控制流程
读提交就是在每条语句执行前生成ReadView
- 事务 B 读取数据（创建 Read View），小林的账户余额为 100 万；
- 事务 A 修改数据（还没提交事务），将小林的账户余额从 100 万修改成了 200 万；
- 事务 B 读取数据（创建 Read View），小林的账户余额为 100 万；
- 事务 A 提交事务；
- 事务 B 读取数据（创建 Read View），小林的账户余额为 200 万；
前两次 事务 B 读取数据时创建的 Read View 如下图：
我们来分析下为什么事务 B 第二次读数据时，读不到事务 A （还未提交事务）修改的数据？
事务 B 在找到小林这条记录时，会看这条记录的 trx_id 是 51，在事务 B 的 Read View 的 min_trx_id 和 max_trx_id 之间，接下来需要判断 trx_id 值是否在 m_ids 范围内，判断的结果是在的，那么说明这条记录是被还未提交的事务修改的，这时事务 B 并不会读取这个版本的记录。而是，沿着 undo log 链条往下找旧版本的记录，直到找到 trx_id 「小于」事务 B 的 Read View 中的 min_trx_id 值的第一条记录，所以事务 B 能读取到的是 trx_id 为 50 的记录，也就是小林余额是 100 万的这条记录。
我们来分析下为什么事务 A 提交后，事务 B 就可以读到事务 A 修改的数据？
在事务 A 提交后，由于隔离级别是「读提交」，所以事务 B 在每次读数据的时候，会重新创建 Read View，此时事务 B 第三次读取数据时创建的 Read View 如下：
事务 B 在找到小林这条记录时，会发现这条记录的 trx_id 是 51，比事务 B 的 Read View 中的 min_trx_id 值（52）还小，这意味着修改这条记录的事务早就在创建 Read View 前提交过了，所以该版本的记录对事务 B 是可见的。
正是因为在读提交隔离级别下，事务每次读数据时都重新创建 Read View，那么在事务期间的多次读取同一条数据，前后两次读的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务。
# 总结
InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它很大程度上避免幻读现象（并不是完全解决了），解决的方案有两种：
- 针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
- 针对当前读（select ... for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select ... for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。
# MySQL锁事
# MySQL有哪些锁
MySQL中根据加锁的范围可以分为三种：
- 全局锁 FTWRL
- 表级锁 表锁 元数据锁 意向锁 AUTO——INC锁
- 行级锁 Record Lock GapLock NextKey Lock
# 全局锁
执行下面的命令，整个数据库就处于只读状态
//加锁
flush tables with read lock
//解锁或者断开会话
unlock tables
2
3
4
全局锁主要用于数据库备份阶段，数据都是只读的状态，不会出现数据不一致，但是会影响业务正常进行
如果数据库的引擎支持的事务支持可重复读的隔离级别，那么在备份数据库之前先开启事务，会先创建 Read View，然后整个事务执行期间都在用这个 Read View，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。
备份数据库的工具是 mysqldump，在使用 mysqldump 时加上 –single-transaction 参数的时候，就会在备份数据库之前先开启事务
# 表级锁
MYSQL表级锁有：
- 表锁
- 元数据锁
- 意向锁
- AUTO-INC锁
表锁
对某一个表进行加锁
//表级别的共享锁，也就是读锁；
lock tables t_student read;
//表级别的独占锁，也就是写锁；
lock tables t_stuent write;
//解锁或者结束会话
unlock tables
2
3
4
5
6
7
加的锁对于加锁线程也是生效的，加共享锁那么不仅是其他线程，包括加锁线程执行写操作都是会被阻塞的
# 元数据锁
当我们进行数据库表操作时，数据库会自动加上这个锁
- 对一张表进行CRUD操作加的是MDL读锁
- 对一张表结构做变更加的是MDL写锁
MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。
MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。
因此当一个长事务后面一个线程修改表结构会因为迟迟得不到MDL锁而导致后续读操作阻塞，（申请锁的队列中写锁获取优先级高于读锁）
可以先kill这个长事务，然后在做表结构的变更
# 意向锁
- 在使用 InnoDB 引擎的表里对某些==行==记录加上「共享锁」之前，需要先在==表==级别加上一个「意向共享锁」；
- 在使用 InnoDB 引擎的表里对某些==行==记录加上「独占锁」之前，需要先在==表==级别加上一个「意向独占锁」；
也就是，当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁。
而普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的。
不过，select 也是可以对记录加共享锁和独占锁的，具体方式如下：
//先在表上加上意向共享锁，然后对读取的记录加共享锁
select ... lock in share mode;
//先表上加上意向独占锁，然后对读取的记录加独占锁
select ... for update;
2
3
4
5
==意向共享锁和意向独占锁是表级锁==，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables ... read）和独占表锁（lock tables ... write）发生冲突。
表锁和行锁是满足读读共享、读写互斥、写写互斥的。
如果没有「意向锁」，那么加「独占表锁」时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。
那么有了「意向锁」，由于在对记录加独占锁前，先会加上表级别的意向独占锁，那么在加「独占表锁」时，直接查该表是否有意向独占锁，如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录。
所以，意向锁的目的是为了快速判断表里是否有记录被加锁。
# AUTO-INC锁
表里的主键通常都会设置成自增的，这是通过对主键字段声明 AUTO_INCREMENT 属性实现的。
之后可以在插入数据时，可以不指定主键的值，数据库会自动给主键赋值递增的值，这主要是通过 AUTO-INC 锁实现的。
AUTO-INC 锁是特殊的表锁机制，锁不是再一个事务提交后才释放，而是再执行完插入语句后就会立即释放。
在插入数据时，会加一个表级别的 AUTO-INC 锁，然后为被 AUTO_INCREMENT 修饰的字段赋值递增的值，等插入语句执行完成后，才会把 AUTO-INC 锁释放掉。
那么，一个事务在持有 AUTO-INC 锁的过程中，其他事务的如果要向该表插入语句都会被阻塞，从而保证插入数据时，被 AUTO_INCREMENT 修饰的字段的值是连续递增的。
但是， AUTO-INC 锁再对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞。
因此， 在 MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种轻量级的锁来实现自增。
一样也是在插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。
InnoDB 存储引擎提供了个 innodb_autoinc_lock_mode 的系统变量，是用来控制选择用 AUTO-INC 锁，还是轻量级的锁。
- 当 innodb_autoinc_lock_mode = 0，就采用 AUTO-INC 锁，语句执行结束后才释放锁；
- 当 innodb_autoinc_lock_mode = 2，就采用轻量级锁，申请自增主键后就释放锁，并不需要等语句执行后才释放。
- 当 innodb_autoinc_lock_mode = 1：
- 普通 insert 语句，自增锁在申请之后就马上释放；
- 类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；
当 innodb_autoinc_lock_mode = 2 是性能最高的方式，但是当搭配 binlog 的日志格式是 statement 一起使用的时候，在「主从复制的场景」中会发生数据不一致的问题。
举个例子，考虑下面场景：
session A 往表 t 中插入了 4 行数据，然后创建了一个相同结构的表 t2，然后两个 session 同时执行向表 t2 中插入数据。
如果 innodb_autoinc_lock_mode = 2，意味着「申请自增主键后就释放锁，不必等插入语句执行完」。那么就可能出现这样的情况：
- session B 先插入了两个记录，(1,1,1)、(2,2,2)；
- 然后，session A 来申请自增 id 得到 id=3，插入了（3,5,5)；
- 之后，session B 继续执行，插入两条记录 (4,3,3)、 (5,4,4)。
可以看到，session B 的 insert 语句，生成的 id 不连续。
当「主库」发生了这种情况，binlog 面对 t2 表的更新只会记录这两个 session 的 insert 语句，如果 binlog_format=statement，记录的语句就是原始语句。记录的顺序要么先记 session A 的 insert 语句，要么先记 session B 的 insert 语句。
但不论是哪一种，这个 binlog 拿去「从库」执行，这时从库是按「顺序」执行语句的，只有当执行完一条 SQL 语句后，才会执行下一条 SQL。因此，在从库上「不会」发生像主库那样两个 session 「同时」执行向表 t2 中插入数据的场景。所以，在备库上执行了 session B 的 insert 语句，生成的结果里面，id 都是连续的。这时，主从库就发生了数据不一致。
要解决这问题，binlog 日志格式要设置为 row，这样在 binlog 里面记录的是主库分配的自增值，到备库执行的时候，主库的自增值是什么，从库的自增值就是什么。
所以，当 innodb_autoinc_lock_mode = 2 时，并且 binlog_format = row，既能提升并发性，又不会出现数据一致性问题。
# 行级锁
普通的 select 语句是不会对记录加锁的，因为它属于快照读。如果要在查询时对记录加行锁，可以使用下面这两个方式，这种查询会加锁的语句称为锁定读。
//对读取的记录加共享锁
select ... lock in share mode;
//对读取的记录加独占锁
select ... for update;
2
3
4
5
上面这两条语句必须在一个事务中，因为当事务提交了，锁就会被释放，所以在使用这两条语句的时候，要加上 begin、start transaction 或者 set autocommit = 0。
共享锁（S锁）满足读读共享，读写互斥。独占锁（X锁）满足写写互斥、读写互斥。
行级锁的类型主要有三类：
- Record Lock，记录锁，也就是仅仅把一条记录锁上；
- Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；
- Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。
# Record Lock
Record Lock 称为记录锁，锁住的是一条记录。而且记录锁是有 S 锁和 X 锁之分的：
- 当一个事务对一条记录加了 S 型记录锁后，其他事务也可以继续对该记录加 S 型记录锁（S 型与 S 锁兼容），但是不可以对该记录加 X 型记录锁（S 型与 X 锁不兼容）;
- 当一个事务对一条记录加了 X 型记录锁后，其他事务既不可以对该记录加 S 型记录锁（S 型与 X 锁不兼容），也不可以对该记录加 X 型记录锁（X 型与 X 锁不兼容）。
当事务执行 commit 后，事务过程中生成的锁都会被释放
# Gap Lock
Gap Lock 称为间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。
假设，表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生。
间隙锁虽然存在 X 型间隙锁和 S 型间隙锁，但是并没有什么区别，间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的
# Next-Key Lock
Next-Key Lock 称为临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。
假设，表中有一个范围 id 为（3，5] 的 next-key lock，那么其他事务即不能插入 id = 4 记录，也不能修改 id = 5 这条记录。
所以，next-key lock 即能保护该记录，又能阻止其他事务将新纪录插入到被保护记录前面的间隙中。
next-key lock 是包含间隙锁+记录锁的，如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。
比如，一个事务持有了范围为 (1, 10] 的 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，就会被阻塞。
虽然相同范围的间隙锁是多个事务相互兼容的，但对于记录锁，我们是要考虑 X 型与 S 型关系，X 型的记录锁与 X 型的记录锁是冲突的。
# 插入意向锁
一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁（next-key lock 也包含间隙锁）。
如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止（释放间隙锁的时刻），在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态。
举个例子，假设事务 A 已经对表加了一个范围 id 为（3，5）间隙锁。
当事务 A 还没提交的时候，事务 B 向该表插入一条 id = 4 的新记录，这时会判断到插入的位置已经被事务 A 加了间隙锁，于是事物 B 会生成一个插入意向锁，然后将锁的状态设置为等待状态（PS：MySQL 加锁时，是先生成锁结构，然后设置锁的状态，如果锁状态是等待状态，并不是意味着事务成功获取到了锁，只有当锁状态为正常状态时，才代表事务成功获取到了锁），此时事务 B 就会发生阻塞，直到事务 A 提交了事务。
插入意向锁名字虽然有意向锁，但是它并不是意向锁，它是一种特殊的间隙锁，属于行级别锁。
如果说间隙锁锁住的是一个区间，那么「插入意向锁」锁住的就是一个点。因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁。
插入意向锁与间隙锁的另一个非常重要的差别是：尽管「插入意向锁」也属于间隙锁，但两个事务却不能在同一时间内，一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁（当然，插入意向锁如果不在间隙锁区间内则是可以的）。
# MySQL锁的特征
# 行级锁
普通的select语句是不会加行级锁的（除了串行化），因为它属于快照读，是通过mvcc实现的
如果要在查询时对记录加行级锁，可以使用下面这两个方式，这两种查询会加锁的语句称为锁定读。
//对读取的记录加共享锁(S型锁)
select ... lock in share mode;
//对读取的记录加独占锁(X型锁)
select ... for update;
2
3
4
5
上面这两条语句必须在一个事务中，因为当事务提交了，锁就会被释放，所以在使用这两条语句的时候，要加上 begin 或者 start transaction 开启事务的语句。
pdate 和 delete 操作都会加行级锁，且锁的类型都是独占锁(X型锁)。
//对操作的记录加独占锁(X型锁)
update table .... where id = 1;
//对操作的记录加独占锁(X型锁)
delete from table where id = 1;
2
3
4
5
# Record Lock
记录锁，记录的是一条行记录，有s和x之分
- 一个事务对一条记录添加s锁，其他事务可以添加s锁，不能添加x锁
- 一个事务对一条记录添加x锁，其他事务s，x锁都不能添加
mysql > begin;
mysql > select * from t_test where id = 1 for update;
2
事务会对表中主键 id = 1 的这条记录加上 X 型的记录锁，如果这时候其他事务对这条记录进行删除或者更新操作，那么这些操作都会被阻塞。注意，其他事务插入一条 id = 1 的新记录并不会被阻塞，而是会报主键冲突的错误，这是因为主键有唯一性的约束。
当事务执行 commit 后，事务过程中生成的锁都会被释放。
Gap Lock
间隙锁，只存在于可重复读隔离级别，解决幻读
假设，表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生。
间隙锁虽然存在 X 型间隙锁和 S 型间隙锁，但是并没有什么区别，间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的。
# Next-Key Lock
临建锁，Record Lock+Gap Lock的组合，锁定一个范围包括行记录本身
假设，表中有一个范围 id 为（3，5] 的 next-key lock，那么其他事务即不能插入 id = 4 记录，也不能修改和删除 id = 5 这条记录。
next-key lock 是包含间隙锁+记录锁的，如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。这种特性是由记录锁决定的
# MySQL加锁时机
行级锁加锁的对象是索引，加锁的基本单位是next-key lock临键锁。
# 唯一索引等值查询
- 当查询的记录是「存在」的，在索引树上定位到这一条记录后，该记录的索引中的 next-key lock 会退化成「记录锁」。
- 当查询的记录是「不存在」的，在索引树找到第一条大于该查询记录的记录后，将该记录的索引中的 next-key lock 会退化成「间隙锁」。
# 唯一索引范围查询
对查询到的每一个索引添加next-key lock
大于等于，那一条符合等于的索引会编程记录锁，其他的还是临键锁
小于等于或者小于：
当条件值不存在时：扫描到终止范围记录时，该记录的索引的next-key lock会退化成间隙锁，这个终止范围记录谁也查询不到，就是一个标志，自然没必要添加记录锁， 当条件值存在时：如果是小于，终止范围记录退化成间隙锁
大于：（n,+00] || （n-1,+00]
大于等于就是 n + (n,+00]|| （n-1,+00]
小于就是 （-00,n) || （-00,n+1）
小于等于就是(-00,n] || (-00,n+1)
临键锁的区间范围只能是右边为闭区间（记录锁）
我觉得这个临键锁有一定的要求，比如范围的右区间必须是闭区间即（n,n+1]，不满足的话比如【n,n+1)就退化成了一个记录锁n和一个间隙锁(n,n+1)，再比如(n,n+1)就退化成了一个间隙锁，翻译一下就是临键锁对应的记录它只会负责到前面区间（id小于自己）的那片间隔区域
# 非唯一索引等值查询
因为存在两个索引，一个主键索引，一个二级索引，在加锁的时候会同时对这两个索引加锁，但是对主键索引只有满足条件的记录才会加锁
cc
当查询二级索引存在的时候：非唯一索引等值查询的过程是一个扫描的过程，直到扫描到第一个不符合条件的二级索引记录就停止扫描，然后在扫描的过程中，对扫描到的二级索引记录加的是 next-key 锁，而对于第一个不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。同时，在符合查询条件的记录的主键索引上加记录锁。
当查询二级索引不存在的时候：扫描到第一条不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录，所以不会对主键索引加锁。
# 非唯一索引范围查询
非唯一索引进行范围查询时，对二级索引记录加锁都是加 next-key 锁。
# 没有索引的查询
如果锁定读查询语句，没有使用索引列作为查询条件，或者查询语句没有走索引查询，导致扫描是全表扫描。那么，每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表，这时如果其他事务对该表进行增、删、改操作的时候，都会被阻塞。
不只是锁定读查询语句不加索引才会导致这种情况，update 和 delete 语句如果查询条件不加索引，那么由于扫描的方式是全表扫描，于是就会对每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表。
# MySQL日志
# Undo Log
回滚日志，是InnoDB存储引擎生成的日志，实现了事务中的原子性，主要用于==事务回滚和MVCC==
事务没有提交之前，MySQL会先记录增删改操作执行前的数据到undo log日志文件中，事务回滚时就会用到undo log
- 插入一条记录只需要记录主键内容，回滚的时候删除主键记录即可
- 更新一条记录，把被更新的旧值（未更新的字段不记录）记录下来，回滚的时候使用旧值覆盖即可
- 删除一条记录只需要把记录全都记录下来，当回滚时候插入该条记录即可
一条记录每次增删改操作产生的undo log格式都有一个roll_pointer指针和一个trx_id事务id
# Buffer Pool
InnoDB设计了一个缓冲池Buffer Pool，当修改记录后不是直接写回磁盘，而是先缓存起来，下次查询先从缓存中取。以此来提高数据库的读写性能。
- 读取数据时，先从缓存中查询，查询不到再去磁盘查找并更新缓存
- 修改数据时，先去缓存中查找，直接修改患存中的数据，然后将该页标记为脏页，后续将脏页写入磁盘
Buffer Pool把申请到的内存分为若干个大小为16kb的缓存页，存储索引，数据，还有undo页，插入缓存，自适应hash索引，锁信息等。
# Redo Log
重做日志，是InnoDB存储引擎生成的日志，实现事务的持久性，主要用于掉电故障恢复，同时将写操作从随机写替换成了顺序写，提升了IO性能
为了防止断电导致buffer pool缓存数据丢失的问题，当有一条记录更新的时候，InnoDB会先更新内存buffer pool数据页并标记为脏页，然后将对这个页的修改以redo log的形式记录下来，而且写入redo log的方式采用了追加的方式，是顺序写，比随机写高效。
产生redo log记录时会先写到redo log buffer中，最后再异步持久化到磁盘中
后续，InnoDB存储引擎会在适当时机启动后台线程将脏页持久化到磁盘中去，WAL（Write-Ahead Logging）技术。
这样一来，MySQL重启时可以根据redo log日志将数据更新到最新状态
- redo log记录的时事务完成后的最新数据
- undo log记录的时事务开始前的最新数据
事务提交之前崩溃，数据可由undo log恢复 事务提交之后崩溃，数据可由redo log恢复
# redo log刷盘的时机
- MySQL服务正常关闭
- redo log buffer记录达到内存一半
- InnoDB后台线程每隔1s将redo log buffer持久到redo log文件
- 每次事务提交，由
innodb_flush_log_at_trx_commit参数控制
- 当设置该参数为 0 时，表示每次事务提交时 ，还是将 redo log 留在 redo log buffer 中 ，该模式下在事务提交时不会主动触发写入磁盘的操作。（由每秒的后台线程控制写入磁盘）
- 当设置该参数为 1 时，表示每次事务提交时，都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘，这样可以保证 MySQL 异常重启之后数据不会丢失。
- 当设置该参数为 2 时，表示每次事务提交时，都只是缓存在 redo log buffer 里的 redo log 写到 redo log 文件，注意写入到「 redo log 文件」并不意味着写入到了磁盘，因为操作系统的文件系统中有个 Page Cache，Page Cache 是专门用来缓存文件数据的，所以写入「 redo log文件」意味着写入到了操作系统的文件缓存。（由每秒的后台线程控制写入磁盘）
# redo log写满了怎么办
InnoDB引擎有一个重做日志文件组，包含两个相同固定大小的redo log文件 redo log 是循环写的方式，相当于一个环形，InnoDB 用 write pos 表示 redo log 当前记录写到的位置，用 checkpoint 表示当前要擦除的位置，
图中的：
- write pos 和 checkpoint 的移动都是顺时针方向；
- write pos ～ checkpoint 之间的部分（图中的红色部分），用来记录新的更新操作；
- check point ～ write pos 之间的部分（图中蓝色部分）：待落盘的脏数据页记录；
如果 write pos 追上了 checkpoint，就意味着 redo log 文件满了，这时 MySQL 不能再执行新的更新操作，也就是说 MySQL 会被阻塞（因此所以针对并发量大的系统，适当设置 redo log 的文件大小非常重要），此时会停下来将 Buffer Pool 中的脏页刷新到磁盘中，然后标记 redo log 哪些记录可以被擦除，接着对旧的 redo log 记录进行擦除，等擦除完旧记录腾出了空间，checkpoint 就会往后移动（图中顺时针），然后 MySQL 恢复正常运行，继续执行新的更新操作。
所以，一次 checkpoint 的过程就是脏页刷新到磁盘中变成干净页，然后标记 redo log 哪些记录可以被覆盖的过程。
# Bin Log
归档日志，是Server层生成的日志，更新语句执行完成后，server层会生成一条binlog，待事务提交完成后统一写入Bin Log文件，主要用户数据备份和主从复制。
bin Log记录增删改操作和表的结构操作，只能用于归档，没有故障恢复能力
redo log和bin log的对比：
- binLog是server层实现的，任何存储引擎都能使用 redoLog是InnoDB实现的，专有
- binLog是追加写，文件写满就新建文件，保存的是全量日志 redoLog是循环写，日志空间大小恒定，保存未持久到磁盘的脏页日志
- binLog用于备份恢复、主从复制 redoLog用于故障恢复
- BinLog由三种文件格式 redoLog是物理日志
# BinLog三种文件格式
STATEMENT：默认格式，每一条修改数据的 SQL 都会被记录到 binlog 中（相当于记录了逻辑操作，所以针对这种格式， binlog 可以称为逻辑日志），主从复制中 slave 端再根据 SQL 语句重现。但 STATEMENT 有动态函数的问题，比如你用了 uuid 或者 now 这些函数，你在主库上执行的结果并不是你在从库执行的结果，这种随时在变的函数会导致复制的数据不一致；（AUTO_INC采用轻量锁方案会出现id不一致问题）
ROW：记录行数据最终修改后的数据，不会出现 STATEMENT 下动态函数的问题。但 ROW 的缺点是每行数据的变化结果都会被记录，比如执行批量 update 语句，更新多少行数据就会产生多少条记录，使 binlog 文件过大，而在 STATEMENT 格式下只会记录一个 update 语句而已；
MIXED：包含了 STATEMENT 和 ROW 模式，它会根据不同的情况自动使用 ROW 模式和 STATEMENT 模式；
# BinLog刷盘时机
事务执行过程中生成的binlog会先存入该线程自己的binLog cacahe，事务提交完成后，再从cache写入binlog文件，并清空cache
MySQL提供一个 sync_binlog 参数来控制数据库的 binlog 刷到磁盘上的频率：
- sync_binlog = 0 的时候，表示每次提交事务都只 write，不 fsync，后续交由操作系统决定何时将数据持久化到磁盘；（默认策略）
- sync_binlog = 1 的时候，表示每次提交事务都会 write，然后马上执行 fsync；
- sync_binlog =N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。
# 两阶段提交
事务提交后，redo log和bin log都需要持久化到磁盘，是两个独立的操作，非原子性的，可能出现数据不一致的问题
- redo log刷盘成功，宕机，bin log刷盘失败，重启恢复时，主机根据redo log恢复数据，从机接受主机bin log数据恢复，修改的那条数据一新一旧不一致
- bin log刷盘成功，宕机，redo log刷盘失败，重启恢复时，主机根据redo log恢复数据，从机接受主机bin log数据恢复，修改的那条数据一旧一新不一致
因此出现半成功的状态时，redo log影响的是主机，bin log影响的是从机
MySQL为了避免两份日志之间的逻辑不一致的问题，使用两阶段提交来解决，这是一种分布式事务一致性协议，可以保证多个操作逻辑原子性。
两阶段提交把单个事务的提交分为了两个阶段，准备阶段和提交阶段。当客户端执行commit语句或者自动提交事务的情况下，MySQL会开启一个内部事务，分两阶段完成这个内部事务的提交
准备阶段redo log刷盘持久化到磁盘
提交阶段bin log刷盘，然后将该事务redo log状态设置为commit
当发生宕机或者重启时，主机对于redo log日志，会先查看状态是否是coomit，如果是说明同一事物的redo log和bin log都刷盘成功，直接恢复数据；如果不存在则获取该内部事务id，然后去bin log日志寻找是否存在该id的binLog，存在的话也认为事务提交成功，直接恢复数据；如果不存在则该redo log对应的事务不生效，回滚事务。
总的来说，两阶段提交是看bin log是否提交成功决定的，这样设计主要是保证主库跟从库数据一致性，主库向从库看起的，自己数据旧，就将自己数据更新，自己数据新，就将自己数据回滚。
# 两阶段提交问题
- 磁盘IO次数高 每个事务提交都需要将redo log和bin log进行刷盘
- 锁竞争激烈 多事务情况下需要加锁保证单事务的两条刷盘操作连续执行
# 优化方法
MySQL引入了组提交机制：当有多个事务提交的时候，会将多个binLog刷盘操作合并成为一个，从而减少磁盘IO次数。
组提交后准备阶段不变，提交阶段分为三个过程
- flush阶段：多个事务按顺序将binLog从cache写入文件
- sync阶段：对binLog文件做fsync操作
- commit阶段，各个事务按照顺序做InnoDB commit操作
每个阶段都有一个队列，每个阶段有锁进行保护，因此保证了事务写入的顺序，第一个进入队列的事务会成为 leader，leader领导所在队列的所有事务，全权负责整队的操作，完成后通知队内其他事务操作结束。
锁粒度减小了，这样就可以多个阶段并发执行，从而提升效率。（这里原因可以类似理解为CPU的流水线工作方式）
在 MySQL 5.7 版本中，做了个改进，在 prepare 阶段不再让事务各自执行 redo log 刷盘操作，而是推迟到组提交的 flush 阶段，也就是说 prepare 阶段融合在了 flush 阶段。
这个优化是将 redo log 的刷盘延迟到了 flush 阶段之中，sync 阶段之前。通过延迟写 redo log 的方式，为 redolog 做了一次组写入，这样 binlog 和 redo log 都进行了优化。
综上所述：当MySQL磁盘IO很高时
- 设置组提交的两个参数：binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count参数，延迟binlog刷盘的时机，从而减少刷盘次数
- 将sync_binlog设置为大于1的值，表示每次提交事务都write，积累到n之后再fync
- 将innodb_flush-log_at_trx_commit设置为2，表示每次事务提交时，都只是缓存再、将redo log buffer里面的redo log记录写到redo log文件（Page Cache），由系统控制刷盘时机
# BufferPool
# 概念
MySQL数据都是存储在磁盘中的，每次读取和存入都需要进行磁盘IO，非常耗时，因此InnoDB设计了一个缓冲池BufferPool，来提高==读写==性能
- 当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取，然后更新buffer pool里的数据页---》这是读性能
- 当修改数据时，首先是修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页，最后由后台线程将脏页写入到磁盘。-----》这是提升写性能
# 结构组成
MySQL 启动的时候，InnoDB 会为 Buffer Pool 申请一片连续的内存空间，然后按照默认的16KB的大小划分出一个个的页， Buffer Pool 中的页就叫做缓存页
InnoDB 为每一个缓存页都创建了一个控制块，控制块信息包括「缓存页的表空间、页号、缓存页地址、链表节点」等等。
控制块也是占有内存空间的，它是放在 Buffer Pool 的最前面，接着才是缓存页，如下图：
# 如何管理空闲页
多次使用后，空闲页肯定分布不来连续，当添加新的缓存页时，为了能够快速定位空闲页，MySQL将空闲页对应的控制块使用链表连接起来，称为空闲链表（和操作系统内出管理有点像）
头节点保安链表的头节点，尾节点，链表包含控制头数量，链表节点包含控制块地址，并且双向链表
当磁盘中加载数据页时就从空闲链表取出一个空闲页，补充空闲页控制块信息，然后从链表中移除。
# 如何管理脏页
设计出Flush链表，和空闲链表差不多，只是控制块对应的是脏页
# Buffer Pool结构总结
- Free Page（空闲页），表示此页未被使用，位于 Free 链表；
- Clean Page（干净页），表示此页已被使用，但是页面未发生修改，位于LRU 链表。
- Dirty Page（脏页），表示此页「已被使用」且「已经被修改」，其数据和磁盘上的数据已经不一致。当脏页上的数据写入磁盘后，内存数据和磁盘数据一致，那么该页就变成了干净页。脏页同时存在于 LRU 链表和 Flush 链表。
# 如何提高缓存命中率
MySQL并没有使用简单的LRU算法（预读失败，Bufffer Pool污染）
预读失败：MySQL 是这样做的，它改进了 LRU 算法，将 LRU 划分了 2 个区域：old 区域 和 young 区域。
young 区域在 LRU 链表的前半部分，old 区域则是在后半部分，如下图：
划分这两个区域后，预读的页就只需要加入到 old 区域的头部，当页被真正访问的时候，才将页插入 young 区域的头部。如果预读的页一直没有被访问，就会从 old 区域移除，这样就不会影响 young 区域中的热点数据。
buffer污染：MySQL 是这样做的，进入到 young 区域条件增加了一个停留在 old 区域的时间判断。
在对某个处在 old 区域的缓存页进行第一次访问时，就在它对应的控制块中记录下来这个访问时间：
- 如果后续的访问时间与第一次访问的时间在某个时间间隔内，那么该缓存页就不会被从 old 区域移动到 young 区域的头部；
- 如果后续的访问时间与第一次访问的时间不在某个时间间隔内，那么该缓存页移动到 young 区域的头部；
这个间隔时间是由 innodb_old_blocks_time 控制的，默认是 1000 ms。
只有同时满足「被访问」与「在 old 区域停留时间超过 1 秒」两个条件，才会被插入到 young 区域头部
另外，MySQL 针对 young 区域其实做了一个优化，为了防止 young 区域节点频繁移动到头部。young 区域前面 1/4 被访问不会移动到链表头部，只有后面的 3/4被访问了才会。
# 脏页的刷盘时机
InnoDB 的更新操作采用的是 Write Ahead Log 策略，即先写日志，再写入磁盘，通过 redo log 日志让 MySQL 拥有了崩溃恢复能力。即使脏页刷盘时宕机数据也不会丢失
脏页需要刷入磁盘来保证缓存和磁盘数据一致性，为了提高效率一般进行批量刷盘
- 当 redo log 日志满了的情况下，会主动触发脏页刷新到磁盘；
- Buffer Pool 空间不足时，需要将一部分数据页淘汰掉，如果淘汰的是脏页，需要先将脏页同步到磁盘；
- MySQL 认为空闲时，后台线程会定期将适量的脏页刷入到磁盘；
- MySQL 正常关闭之前，会把所有的脏页刷入到磁盘；
# MySQL主从复制
MySQL主从复制依赖于binLog实现，复制的过程就是主节点将自己的binLog传输给从节点。
- MySQL 主库在收到客户端提交事务的请求之后，会先写入 binlog，再提交事务，更新存储引擎中的数据，事务提交完成后，返回给客户端“操作成功”的响应。
- 从库会创建一个专门的 I/O 线程，连接主库的 log dump 线程，来接收主库的 binlog 日志，再把 binlog 信息写入 relay log 的中继日志里，再返回给主库“复制成功”的响应。
- 从库会创建一个用于回放 binlog 的线程，去读 relay log 中继日志，然后回放 binlog 更新存储引擎中的数据，最终实现主从的数据一致性。
在完成主从复制之后，你就可以在写数据时只写主库，在读数据时只读从库，这样即使写请求会锁表或者锁记录，也不会影响读请求的执行。分担主节点压力。
# MySQL主从复制模型
- 同步复制：MySQL 主库提交事务的线程要等待所有从库的复制成功响应，才返回客户端结果。这种方式在实际项目中，基本上没法用，原因有两个：一是性能很差，因为要复制到所有节点才返回响应；二是可用性也很差，主库和所有从库任何一个数据库出问题，都会影响业务。
- 异步复制（默认模型）：MySQL 主库提交事务的线程并不会等待 binlog 同步到各从库，就返回客户端结果。这种模式一旦主库宕机，数据就会发生丢失。
- 半同步复制：MySQL 5.7 版本之后增加的一种复制方式，介于两者之间，事务线程不用等待所有的从库复制成功响应，只要一部分复制成功响应回来就行，比如一主二从的集群，只要数据成功复制到任意一个从库上，主库的事务线程就可以返回给客户端。这种半同步复制的方式，兼顾了异步复制和同步复制的优点，即使出现主库宕机，至少还有一个从库有最新的数据，不存在数据丢失的风险。
# MySQL数据迁移
# 需求背景
随着业务的不断发展，系统的访问量很高，数据也越来越多，这时就需要进行数据库的扩容
数据迁移过程需要保证：
- 迁移后数据准确不丢失
- 不影响用户体验
- 迁移后的性能和稳定性
# 方案
# 挂从库
- 在主库上建一个从库，数据开始向从库同步
- 数据同步完成后在用户流量少的时间进行停机，防止新的记录，保证主从一致性，最后将从库升级为主库
- 重新启动服务
# 双写
- 更改业务逻辑。在对数据增删改的地方同时操作老库和新库
- 开启双写，老库和新库同时写入。更新操作新库没有记录就从老库中获取，为了保证性能可使用消息队列异步更新到新库
- 利用脚本程序，将开启双写后的某一时间戳之前的的老数据迁移到新库，并且记录日志用于异常和恢复
- 通过脚本程序进行数据校验。
- 开启双读，将小部分流量切入到新库
- 读流量全部切入到新库后关闭老库写入
- 迁移完成去掉双写双读的业务代码
# 数据同步工具
Canal，DataBus。
- Canal模拟mysql slave的交互协议，把自己伪装成MySQL的从库
- 向mysql master发送dump协议
- mysql master收到dump协议，发送bin log给canal
- canal解析bin log，并进行数据处理
流程：
- 代码准备。准备Canal代码，解析binary log字节流对象，并把解析好的用户数据写入新库。
- 运行Canal代码，开始增量数据（线上产生的新数据）从老库到新库的同步。
- 利用脚本程序，将某一时间戳之前的老数据迁移到新库。注意：1，时间戳一定要选择开始运行Canal程序后的时间点（比如运行Canal代码后10分钟的时间点），避免部分老数据被漏掉；3，迁移过程一定要记录日志，尤其是错误日志，如果有些记录写入失败，我们可以通过日志恢复数据，以此来保证新老库的数据一致。
- 通过脚本程序检验数据，看新库数据是否准确以及有没有漏掉的数据
- 数据校验没问题后，开启双读，起初给新库放少部分流量，新库和老库同时读取。由于延时问题，新库和老库可能会有少量数据记录不一致的情况，所以新库读不到时需要再读一遍老库。逐步将读流量切到新库，相当于灰度上线的过程。遇到问题可以及时把流量切回老库
- 读流量全部切到新库后，将写入流量切到新库（可以在代码里加上热配置开关。注：由于切换过程Canal程序还在运行，仍然能够获取老库的数据变化并同步到新库，所以切换过程不会导致部分老库数据无法同步新库的情况）
- 关闭Canal程序
- 迁移完成。
# MySQL扩容方案
# ** 常规方案**
如果增加的节点数和扩容操作没有规划，那么绝大部分数据所属的分片都有变化，需要在分片间迁移：
- 预估迁移耗时，发布停服公告；
- 停服(用户无法使用服务)，使用事先准备的迁移脚本，进行数据迁移；
- 修改为新的分片规则；
- 启动服务器。
# 免迁移扩容
采用双倍扩容策略，避免数据迁移。扩容前每个节点的数据，有一半要迁移至一个新增节点中，对应关系比较简单。 具体操作如下(假设已有 2 个节点 A/B，要双倍扩容至 A/A2/B/B2 这 4 个节点)：
- 无需停止应用服务器；
- 新增两个数据库 A2/B2 作为从库，设置主从同步关系为：A=>A2、B=>B2，直至主从数据同步完毕(早期数据可手工同步)；
- 调整分片规则并使之生效： 原
ID%2=0 => A改为ID%4=0 => A, ID%4=2 => A2； 原ID%2=1 => B改为ID%4=1 => B, ID%4=3 => B2。 - 解除数据库实例的主从同步关系，并使之生效；
- 此时，四个节点的数据都已完整，只是有冗余(多存了和自己配对的节点的那部分数据)，择机清除即可(过后随时进行，不影响业务)。
# 相关连接
Innodb的RR到底有没有解决幻读？看不懂你打我！-innodb解决了幻读 (51cto.com) (opens new window)
public stati boolean isIsomorphic(String s, String t) {
//example: s:"add" t:"egg"; s:"adm",t:"egg"
if (s.length() != t.length()) return false;
//建立s和t的字符映射关系
Map<Character,Character> map=new HashMap<>();
//遍历s
for (int i = 0; i < s.length(); i++) {
// 判断map存在是个存在key，表明是否已经对该字符建立过映射
if(map.containsKey(s.charAt(i)))
{
//查看已经建立的映射的值和t对应位置的字符是否相同
if(map.get(s.charAt(i))!=t.charAt(i))
{
// 不相同说明又建立了一层对应关系
// 即s中的一个字符与t中的两个字符建立了映射关系
return false;
}
//直接判断下一个字符
continue;
}
// 判断当前字符对应的t中的字符已经和s前面的某个字符建立过映射关系
if(map.containsValue(t.charAt(i))) return false;
//建立s,t中对应位置的映射关系
map.put(s.charAt(i),t.charAt(i));
}
return true;
}
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Redis →