MySQL学习记录引用出处 https://xiaolincoding.com/ OrderBy ,Join 语法底层

引用出处

OrderBy ,Join 语法底层

“Order by 是如何工作的”？

全字段排序

对于一条

SELECT name,phone FROM mb_user WHERE phone = '18151607680' ORDER BY name limit 1000;

这样一条简单的查询语句，phone是加索引的，它的order by 是如何工作的呢？

对于phone = "18151607680" 这条语句首先，MySQL会把所有电话为该号码的用户筛选出来。
其次，初始化 sort_buffer集合, 放入 name,phone 字段。
取满足条件的数据，将值填入sort_buffer集合中。
对sort_buffer的name进行排序
然后返回结果集。

对于按照name来排序这一行为，在内存中运算还是在磁盘中运算，取决于另一个重要的参数 —— sort_buffer_size

如果要排序的数据量小于sort_buffer_size，那么就在内存中运算，如果超过了，就在磁盘中运算。

如果sort_buffer_size非常小，如果数据量很大，那也就意味着需要分成的临时文件的份数变多，每个文件分别排序，之后整合成一份大的文件。

rowid排序

对于全字段排序，如果需要选择的字段太多，也就意味着sort_buffer_size 很容易被撑满，那么能在内存中计算的概率就不大了，需要区分多个临时文件，毫无疑问是性能更差的。

那么如果 MySQL 认为单行排序长度太大了会怎么做？

如果我们能改变 max_length_for_sort_data 参数的话，倘若查询出来的单行数据大于这个参数，那么就会采取另外一个算法。

首先对于phone = "18151607680" 这条语句首先，MySQL会把所有电话为该号码的用户筛选出来。
其次，初始化 sort_buffer集合, 放入 name,id字段。
取满足条件的数据，将值填入sort_buffer集合中。
对sort_buffer的name进行排序
根据id重新回表中查询phone参数
将sort_buffer 中的name,id + 重新查到的phone组合返回

总结：其实就是把需要排序的字段和id放入sort_buffer中，多余字段先不查，排完序后再根据id查，非常优雅！

两者比较

其实rowid 和全字段排序告诉我们，能尽量用内存计算就用内存计算，如果实在不够，就用磁盘计算。

对于Innodb来说，rowid计算是会造成回表读的，所以不会被优先选择。

对于MySQL来说，排序是一个成本比较大的操作，但倘若数据原本就有序的话，那么是不是就可以不用排序了？

我们可以将 phone,name创建一个联合索引，我们可以利用联合索引的字段有序这一个特性，来避免再sort_buffer 中排序的步骤，省了很多麻烦！

MySQL 学习指引

连接的原理

select a.m1,a.n1,b.m2,b.n2 from
table_a a join table_b b on a.m1 = b.m2
where a.n1 > 10

对于这么一个简单的语句，join是如何执行的呢？

首先，对于驱动表table_a而言，会先执行where条件进行筛选过滤，得到驱动表的集合
对于得到集合的每一条数据，对 b.m2 = a.m1 在table_b表中进行查询。a表中筛选出多少条数据，就去b表查询多少次

这也是嵌套循环连接算法。

基于块的嵌套循环连接

扫描一个表的过程是先需要把表数据从磁盘加载到内存中，然后进行筛选。对于成千万条数据来说，一次性加载到内存中，很容易出现内存不够的情况。

但倘若加载一条数据到内存又进行释放，则又会出现I/O次数过多，导致时间过长。对于嵌套循环连接算法中，我们知道，每个从驱动表中查询到的数据，都需要再次到被驱动表中进行查询。这会有大量的I/0。

为了避免过量的 I/O 代价，我们需要减少访问被驱动表的次数。

为此，MySQL设计了一个join buffer的区域。这是执行连接查询前申请的一块固定的内存区域。它会先将驱动表的结果集放入 join buffer 中，然后才开始扫描被驱动表，让被驱动表的一条数据和 join buffer 每一条进行比对。这些都是在内存中比对的。

如果不用join buffer的话，MySQL会先加载驱动表的一条数据，然后再用被驱动表的每一条数据进行比对。之后加载驱动表的第二条数据，再加载被驱动表的每一条数据比对。相比于上面一种，明显复杂很多。

我们可以通过配置MySQL的 join_buffer_size 根据自己的机器去监控调整。

Join 连接算法

对于日常业务交易场景来说，需要查询的数据是较少的，查询语句都是很简单的。此时优化器会使用Nested Loop Join算法。

但是如果面向数据分析的业务，数据量是巨大的。此时优化器会使用Hash Join算法。

Nested Loop Join

这个算法用伪代码来看是这样的：

for each row a in tableA matching condition:
    lookup index idx_s on tableB where index_key = r
    if (found)
      send to client

表tableA 通过where条件去筛选，筛选后的数据会在表tableB上对应的索引上进行查询。

如果是left join，那么左边的表是驱动表，如果是right join 那么右表就是驱动表。如果用inner join 那么数据量少的就是驱动表

Hash Join

首先会扫描驱动表，将驱动表符合的数据放入哈希表中。
扫描第二张表的时候，在hash表中进行选择。

执行一条select 语句，期间发生了什么？

连接。MySQL会根据你输入的用户名密码，去用户表中查询，如果能查到，就允许连接。
查缓存。对比当前执行的sql，和之前运行过的sql，如果有相同的，就返回一样的结果。但是一般用不到，因为数据一变，就会把缓存全部清除。
词法分析，语法分析。对输入的sql进行语法校验，顺序校验等。
预处理。将通配符一类的字段展开。
优化阶段。选择最合适的索引，最优的解决方案。
执行阶段。执行计划，从存储引擎中查询，返回给客户端。

存储结构

MySQL一行记录是如何存储的？

表空间的文件结构

对于我们见到的每一条数据，我们把它称为一行。

由于数据库如果一行一行的读取，I/O次数大，效率低，因此多个行会存储在页中，每个页是16KB，每次读取不是读一行，而是一次性把一页读入进内存。这也意味着最多能保证16KB的连续存储空间。

页是InnoDB存储引擎磁盘管理的最小单位，意味着每次读写都是以16KB为单位的。

在面对大型表格，往往数据查询较为耗时，可以对该表格进行分区，比如按照时间，地理，范围等。当只查询访问部分数据的时候，MySQL只需要访问相关分区，不需要访问整张表。

表空间是由各个段组成的，段又是由各个区组成的。段往往分成数据段，索引段和回滚段

Compact行格式长啥样？

这是InnoDb提供的一种行格式，其它行格式大同小异。

它分为记录的额外信息和记录的真实数据两部分。

记录的额外信息：

变长字段长度列表。对于varchar定义的字段来说，它是不定长的比如一个 ‘a’ 就占用 1字节，一个 ‘abc’ 就占用3字节。他们会在变长字段列表中和列值字段相对，逆序存放。逆序存放的意义是，其实记录头信息的区域里存放了下一个记录的指针，也就是说，起点是在记录头信息的区域，向左向右读都是“正序”。
同理，null值区域也是逆序排放的。二进制数为 1 的时候表示为null，为0表示为非空。比如列1，2为 null 和 ‘ab’。那么，null值区域存放的就是 0，1。
记录头信息区域，记录了下一条记录的位置，因为记录和记录之间使用链表关联的。同时记录了此条数据是否被删除，可以理解为逻辑删除。

记录的真实数据：

row_id:如果建表的时候有主键或者唯一约束列，就没有row_id，否则就有，这是非必须的。
trx_id:表示是哪个事务生成的。
roll_pointer:表示上个版本的指针。

Varchar（n）最大取多少？

MySQL规定，一行记录除了TEXT,BLOBS这种大对象类型之外，其它所有的列占用的字节长度不能超过 65535 字节。

一行所占用的字节数包括变长字段长度区域和null值区域和真实数据区域的长度之和，最大是65535 字节。

行溢出后，MySQL是如何处理的？

我们知道，磁盘和内存交互的基本单位是页。一个页的大小是 16KB,如果一行采用 Blob,Text很有可能超过一页大小。为此我们需要对多余的数据放入溢出页中。然后在真实数据中，用一部分区域指向溢出页地址，从而找到剩余的页

为什么MySQL使用B+树作为索引？

在谈这个问题之前，我们要清除，I/O次数越多，消耗的时间也就越大。因此我们需要减少I/O次数。且速度要快。

B树是什么？

相比于AVL树，由于AVL树本质是一个二叉树，随着数目的增大，树的高度会增高，I/O次数会增大。B树为了降低树的高度，每一个节点可以存放多个子节点。

打个比方，如果B树是一个3阶的B树，那么每个节点最多有3个子节点，每个节点最多有3-1个子项。

B树的优点：是一颗AVL树，控制高度，减少I/O。也是棵搜索树，搜索效率高。同时相比于一些其它的平衡二叉树，它每隔节点能存放更多数据，每个节点的子节点也更多，因此它更矮，I/O次数更少。

什么是B+树？

B+树就是对B树的升级。它仅仅只有叶子节点才会存放数据，其它节点都是用来放索引。同时叶子节点之间构成了有序链表，方便排序。非叶子节点有多少个子节点意味着有多少个索引。

相比于B树，B+树有以下几个优点：

从单点查询来看，由于B+树非叶子节点仅仅存放索引，不放实际数据，这意味着非叶子节点能放更多的数据，因此它更加矮胖，I/O次数更少。并且，B树的查询效率波动大，有的时候是在非叶子节点查到数据，有的时候是在叶子节点查到。
从插入和删除效率来看，B+树删除一个节点有的时候是直接从叶子节点删除，树的结构不用发生太大的变化，删除的速度就很快了，这取决于B树有很多的冗余节点。如下图，删0004只需要变化子结点即可。而B树为了维护这个平衡，经常要做旋转操作。

范围查询。B树的所有叶子节点都用一条链表来连接。这方便查找。比如查大于2的节点只需要向右边遍历即可。B树没有这样的结构。

MySQL中的B+树

MySQL中，非叶子节点负责存放索引，和对应索引页的地址。而真正的叶子节点就是存放索引和数据

索引组织表

OLTP指的是联机事务处理，是一种处理实时交易和业务操作的计算机系统。它通常用于处理企业日常的业务操作，如订单处理、库存管理、客户服务等。相比其他类型的系统，OLTP系统更注重数据的实时性和准确性，因此需要高效的数据处理和事务管理能力。

MySQL的InnoDB的存储引擎的结构就是索引组织表,对于海量的并发业务中，他是必不可少的。

对于堆表而言，数据和索引是分开存储的，索引是排序后的数据，索引的叶子节点存放数据在堆表中的地址，如果这个数据发生了变更，那么所有的索引中的地址都要更新。

这是索引组织表，数据根据主键排序存放在索引中，主键索引也叫聚簇索引。

二级索引

二级索引可以理解成，一张以主键为id，索引名称为name的一张表，在用户搜索这个二级索引的时候，会查这张表，然后找到主键id，然后根据这个主键id再回表去查原来的数据。

SELECT id FROM idx_name WHERE name = ?

SELECT * FROM User WHERE id = _id; -- 回表

当插入数据的时候，也需要维护这两张表。

联合索引

CREATE INDEX table_name ON table (a, b);

MySQL会根据a,b两个字段构建索引树，非叶子节点不存除了a,b以外的其它字段，每个节点存多个值，每个值都有一定间隔，相当于目录，由叶子节点展示完整的信息，字段包括a,b,主键。

如果a在b的前面，索引树会按照a的排序来构建，找到a后再找b。

因此我们在查询的时候要遵循最左匹配原则。

Where a=1，where a= 3,b=2可以走索引。单纯一个 where b=2无法走索引。

范围查询

对于 where a > 1 and b = 2来说，a能走索引，b走不了，是因为对于叶子节点来说，他是个双向链表，对于a来说是有序，b来说是无序的，因此扫描范围是（2，+∞）。

对于 where a >= 1 and b = 2来说，当a = 1，b=2的时候是走索引的，因为a=1下的b是有序的，所以是用到了联合索引。

对于 between and 来说，由于它是双闭区间，因此，他也是走了索引的。

对于 where a like j% and b = 2来说，如果 a = 'j' 有两条，那么a,b都会走索引。

索引下推

对于 where a> 1 and b = 2,我们知道，只有a走了联合索引，对于老版本的mysql没有索引下推的功能，只能根据a=1找到对应的主键id，回表找b的值比较，一个个回表。

但是索引下推会优先看看组合索引里面有没有该字段，如果有就优先对它进行判断，减少回表次数。

什么时候适用索引？

索引并非适用全部场景。因为创建索引意味着额外维护索引树，增删改查效率会降低。

需要：

排序某字段，group by 某字段
字段有唯一性限制

不需要：

查询的字段不在where语句内，或者group by内
基数率低，如性别
更改多，需要不停维护索引树
数据量少

索引优化的手段？

使用前缀索引。针对某个字段太长，占用空间太大，只对前几个字符做索引，当查询的时候 value 值要小于设置索引的长度，不然肯定走不了索引且会全表扫描。
覆盖索引优化。就是联合索引多连合几个需要的字段，这样查询的时候就会发现，需要查的数据都在联合索引里面，就不用回主键索引表了。
使用自增主键。因为自增主键的新增操作都是追加，效率高。如果不是，还得找插入位置，有的页满了，有的页没满，空间利用效率也低。
索引列设置not null。null在每行记录会多占用一个null 值列表字段，多占用一字节，且索引存在null 难以优化。
防止索引失效，注意失效场景。

总结

事务

并发事务会引发什么问题？

脏读：事务B读到事务A更新后的数据，但是事务A的行为回滚，读到的数据是脏数据。

不可重复读：同一次事务查询的一条数据的值不同。

幻读：同一次事务多次查询某一范围的记录，记录数不一致

事务隔离的几种级别

读未提交：一个事务读到的数据是另一个数据未提交但修改后的数据。

读已提交：当一个事务提交后，他的变更才能被其它事务看到。

可重复读：一个事务执行过程中看到的某条记录前后均一致。MySQL默认隔离级别。

串行化：加上读写锁，如果发生读写冲突，后访问的事务必须等前一个事务执行完成。

可重复读是如何工作的？

这是MySQL默认的隔离级别，理论上来说会发生幻读现象，但是MySQL利用MVCC的机制，减少了幻读现象的发生，至于为什么不用串行化，是因为读写锁的效率低。

在新开启的事务中，在可重复读的隔离机制下，MySQL会新建一个ReadReview。并且整个事务过程都使用这个ReadReview。这个ReadReview包含几块：创建它的事务id,当前正在活跃的事务ids,最小事务id,下一个事务的id。

在该事务中，如果想要对某条记录进行读取，首先，他会读取该条记录的trx_id，trx_id是创建该条记录的事务id。如果当前的事务id小于最小事务id，那么说明这条记录是在本事务开启前就已经创建好的，那么直接读取即可。如果这个事务id处于活跃的事务id列表，那么就redolog的roll_pointer找到上一个事务修改这条数据的记录，直到当前的数据的事务id是小于最小事务id。如果当前事务id大于最大事务id，则说明是本事务后开启的事务修改的记录，那么也直接根据roll_pointer向前追溯，直到当前的数据的事务id是小于最小事务id。

读提交是如何工作的?

相比于可重复读，读提交的工作原理类似，只是生成ReadReview的时机不同。可重复读的生成时机是每次事务开始时，并且每次事务只开启一次。读提交是每次查询的时候生成ReadReview，这样就能及时看到谁是正在活跃的事务，也就是说，其它事务只要是提交了，那就直接变得不活跃，那么读提交在利用roll_pointer向前追溯的时候就能选择提交后的数据了。

快照读是如何避免幻读的？

MVCC，上面已经提到了。

当前读是如何避免幻读的？

select ... for update

MySQL在可重复读的隔离级别下，为了解决当前读出现的幻读问题，引出了间隙锁的概念。

在一次事务的第一次使用当前读的时候，会对这部分select的数据加锁，不给其它事务在当前事务结束前添加。

幻读是否被完全解决了？

失效的第一种场景：使用快照读后，使用了当前读。

失效的第二种场景：事务A先用快照读，事务B新增一条数据，事务A修改这条数据，此时这个数据的trx_id就是事务A，那么再利用快照读，读到的结果和第一次不同。

锁

MySQL有哪些锁？

表锁

一锁就是整张表，如果加了表级别的读锁，那么接下来对这个表的写就会阻塞。

MDL锁（元数据锁）

当数据库进行增删改查的时候，每当开启一个事务，就会开启一个MDL读锁，这个读锁是读读不冲突的。当要修改这个表的表结构的时候，会加上一个MDL写锁，这个写锁如果加上，那么后续的读、写都是会阻塞的。

意向锁

意向锁是在，对一些记录加上共享锁，或者独占锁之前加的一个锁。

//先在表上加上意向共享锁，然后对读取的记录加共享锁
select ... lock in share mode;
//先表上加上意向独占锁，然后对读取的记录加独占锁
select ... for update;

意向锁是表级别的锁，他的意义就是快速判断表里面是否有记录被加锁。因为在加共享锁，独占锁之前都要加意向锁。

记录锁(Record Lock)

上面都是表级锁，这个是行级锁，对于某一条记录加上独占锁，那么就无法写也无法读这条记录，如果加上共享锁，那么可以读，无法写。

间隙锁(Gap Lock)

这是出现在可重复读隔离级别下的锁。可以锁住一个范围，在这个范围内无法删除，新增数据。这个是为了解决幻读问题提出来的锁。相同范围不会阻塞。

Next-key Lock

这是Record Lock 和 Gap Lock的结合，不仅锁住范围，而且锁住记录本身。如果锁住相同范围，则会阻塞。因为记录锁的独占型的读写，写写都是冲突的。

插入意向锁

插入意向锁是，如果一个事务想插入被间隙锁锁住的区间一条数据，那么这个事务就会生成一个插入意向锁，然后将锁的状态设置为等待，然后这个事务就会一直阻塞，直到有间隙锁的事务执行完成后再插入。

插入意向锁是一种行锁。可以理解成，如果A先利用间隙锁锁住了一个区间，B如果想在这个区间插入数据，就先插入这个插入意向锁，然后这个锁休眠，直到拿到这个锁，才能真正插入。

MySQL死锁了怎么办

事务A的第一个查询语句，是for update，加上了独占型的next-key lock，next-key lock不仅会锁住记录本身还会锁住一个范围，不让插入，此时由于表中没有 order_no = 1007 的记录，那么MySQL此时就会利用间隙锁锁住（1006,+∞)的记录。又由于间隙锁如果锁的范围不同，那么就不互斥，所以事务B也锁住了（1006，+∞）的记录。

此时事务A试图在插入间隙获取插入间隙锁，而此时这个范围被事务B的间隙锁锁住了。由于插入 意向锁 和间隙锁是冲突的，当其它事务拥有间隙锁，只有等他释放了，才能获取插入意向锁。

因此事务A在等事务B释放间隙锁。而事务B的插入操作又在等A释放间隙锁。造成了循环等待。

如何避免死锁？

设置事务等待锁的超时时间
开启死锁检测

业务角度: 将order_no设置为唯一索引列，直接insert,不查了。但这样不好的一点就是当我们插入一个已经存在的订单也会发生异常

死锁发生的条件

互斥，循环等待，不可强占用，占有且等待。

三大日志

为什么需要undo log?

增删改是隐式开启事务的

Undo log的记录时间是事务执行过程中。也就是事务开启后，commit 前。

Undo log 也叫回滚日志，如果删除了一条数据，他会把这条记录内容记下来，更新的时候会把原始值记下来。

Undo log 的格式是由一个 roll_pointer用来指向上一个版本的Undo log,和生成它的事务id，形成一个版本链。（MVCC）

因此，undo log 主要有两个作用。

实现MVCC
实现事务回滚，保障事务的原子性，要么全部成功，要么全部失败。

为什么需要Buffer Pool?

MySQL在更新磁盘中的数据的时候，会先把磁盘中的数据加载到内存中，MySQL为这个内存区域设计了一个缓冲池。下次读数据的时候，会直接去缓冲池中读数据，如果没有，再去磁盘，相当于一个缓存。

如果有修改操作，那么也是会直接修改缓冲池的数据，并且把这个数据标记成脏页，合适时机会重新刷回磁盘。

Buffer Pool存什么？

Buffer Pool 和磁盘一样，存储空间都是以页为基本单位，都是16KB.

在MySQL启动的时候，会为Buffer Pool申请一片连续的内存空间，然后按照16KB划分页。

存储：数据页，索引页，undo页，锁信息等。

在查询一条数据之后，缓存的时候是把整个页缓存到buffer pool中的，然后按照目录去找到对应的条数。所以查一条数据不是只缓存一条。

为什么需要 redo log?

对于在内存中被标记为脏页的数据，如果此时机器宕机，后台线程没来的及把数据写入磁盘，那么此时数据就会丢失。

因此需要redo log把已经改变的脏页做一个标记，redo log 也是物理日志。

Redo log的作用是防止系统崩溃，buffer pool数据无法刷新到磁盘中。

是否需要记录Undo页？

之前提到，如果InnoDB更新数据，会把原始数据写入 undo log,然后实际上undo log也是会先写入 buffer pool的，然后还是有后台任务同步到磁盘的。

因此，只要是在buffer pool的数据，就需要同步磁盘，因此是会记录undo log页到redo log中的。

Redo log 专注于记录更新之后的值。

Undo log 专注于记录事务开始前的数据状态。

在执行事务的时候，会记录redo log,undo log,当出现异常，直接调用undo log中的数据实现回滚，当机器宕机，用redolog的数据恢复buffer pool的数据到磁盘。

既然redo log的作用是把更新后的数据刷到磁盘，那为什么不直接写磁盘？

这其实是一个开销问题，redo log写入磁盘的效率很高，是追加操作，是顺序写。效率比直接写磁盘要高。

Redo log 什么时候刷盘

其实redo log 在生成的时候也是有一个 redo log buffer 的，跟undo log 一样，只是内存区域不在一块儿。

那么 redo log 何时把数据从内存中刷到磁盘呢？

MySQL正常关闭时
Redo log buffer 空间写入量大于开辟的空间一半的时候，会触发落盘。
InnoDB的后台线程每隔1s,持久化
事务提交的时候。innodb_flush_log_at_trx_commit 参数控制，如果是0，不会触发落盘。如果是1，直接触发落盘，如果是2下，写入操作系统文件缓存。（2是1，3的折中方案，0注重性能，1注重安全）

Redo log文件写满了怎么办？

Redo log的意义是为了记录脏页，如果脏页已经被刷盘，那么留着意义就没了，因此不用给他分配太多磁盘空间。

InnoDB为了它分配两个文件 ib_logfile0 和 ib_logfile1 这两个文件相当于一个环形链表，循环写，如果写到尾部，就覆盖。

蓝色部分是已经写了的，等待落盘的数据，红色部分是留着记录新的数据。

对于并发量大的数据，合理配置redolog 文件大小就很重要，因为如果tail 追上了head，要落盘的脏页太多，那么就会阻塞，等刷完脏页到磁盘，再继续工作。

为什么需要binlog?

Binlog 工作在server层，redolog 和 undolog工作在存储引擎层，Innodb可用。什么存储引擎都能用。

Binlog 不记录select,show 操作，而是记录表结构的变更和表数据修改的日志。

也跟时间线有关，binlog是先出的，但是没有宕机后保证数据完整性，一致性的能力，后面Innodb是以插件形式引入的，redo log 的存在实现了宕机后保证数据完整性，一致性的能力。

Redo log 和 binlog 的区别

一个是server,一个是存储引擎层。
格式不同
写入方式不同。redo log 是循环写，bin log是追加写，保存全量日志，不覆盖。
用处不同。redo log用于突发状态恢复数据，binlog 用于主从复制，备份恢复。

当我们把整个数据库的数据删掉后，可以用binlog恢复

主从复制的实现

主从复制就是主库把数据同步到从库中。

主库写binlog 日志，提交事务，更新本次存储数据。
主库有个线程，专门用于binlog同步到从库，从库会把binlog放入中继日志中，返回主库同步中继日志成功的响应。
从库除了接收binlog的I/O线程，还有sql线程用于回放binlog数据，更新自身的存储引擎。

好处：

数据备份
读操作分担到从库，减轻主库负担，提高并发能力。

MySQL主从复制模型

同步复制。MySQL主库在binlog同步给从库时候，需要等待所有从库全部返回复制成功的响应。（性能差，会因为一个从库影响到所有库）
异步复制。不会等待从库返回复制成功结构。（如果宕机了，数据容易丢失）
半同步复制。只需要等待一部分从库返回成功复制响应即可。兼顾异步同步的优点，如果主库宕机，至少还有一个从库有最新的数据。

binlog什么时候刷盘？

MySQL 为每个线程都分配了一个binlog cache,用于记录一次事务的执行。在事务执行过程中，会记录到这个cache中，然后等事务提交了，就会把这个cache写到binlog文件中。

这里的binlog文件也是操作系统的page cache,binlog cache ->page cache(binlog 文件) 不涉及 I/O。

MySQL也提供三种刷盘频率策略：

每次提交事务只同步到binlog文件里，后续binlog文件到磁盘的刷盘时机由操作系统决定。
同步binlog后，直接刷盘。
同步binlog，累积n个事务后刷盘

一般采取第三种，数目为 100 ~ 1000某个值

为什么需要两阶段提交？

我们知道，redolog 是记录脏页所在位置，更新后的信息，先到redolog buffer 中，再持久化到磁盘。

Binlog 是在事务执行时候，记录，提交后，到binlog buffer中，再到binlog文件，再到磁盘。

两者相同点在于：redolog 记录事务的修改操作，binlog同样也记录了修改操作（逻辑日志)。

两个日志逻辑独立，可能持久化过程出现半成功状态，导致逻辑不一致。

要么binlog 持久化了，redolog没有，要么redolog持久化了，binlog没有。

为了解决这个问题，MySQL使用了分布式协议——XA协议。主要是分为两阶段，两个角色。

prepare阶段：redo log作为参与者，会把此次XA的事务id写入redo log，同时，将状态设置为prepare,然后把redo log 持久化到磁盘。
Commit 阶段：把XA的事务id写入binlog,binlog成功返回写入成功后，调用存储引擎的事务提交接口，把redo log的状态从prepare设置为commit，这个状态无需持久化到磁盘，写到文件系统page cache中即可，因为两个log已经同步成功了，没必要那么急着改状态。

两阶段提交如何抗住异常重启？

查看redo log中的XID，如果redolog 由XID,binlog没有，说明异常后，binlog没同步，redolog完成了刷盘，此时，回滚事务即可。
查看redo log中的XID，如果有，且binlog 也有，说明都同步成功了，即使redolog的状态仍然为prepare，也是ok的，只是pagecache中的状态没同步过来而已。

总结：两阶段提交的成功取决于binlog写成功。

之所以重心在binlog身上，是因为从库也是根据binlog同步的，这样主从就保证了一致性。

binlog是等事务提交后才持久化到磁盘的，而redo log是在事务执行时候就已经到redo log buffer中了，后台线程每隔1s同步到磁盘。因此，redolog磁盘有数据，binlog磁盘没数据是完全有可能的。

两阶段提交的问题

两阶段提交虽然可以保证数据一致性，但是有两个问题。

I/O频繁。每次事务提交都会进行两次刷盘。redo log和binlog刷盘
锁竞争激烈。多事务提交的时候，如果同时崩盘。每个事务需要获得锁，走完两阶段。

如何解决？

降低I/O。引入了组提交的概念。prepare阶段不变，commit阶段会将多个binlog刷盘操作合并成一个。
降低锁粒度。binlog刷盘分三个阶段，多个事务按顺序将binlog 从cache 写入文件，从文件写入磁盘，依次按顺序调用InnoDB commit事务接口。这也是三个队列，只锁每个队列，不锁事务整个过程，多个阶段可以并发执行。

这三个阶段中，每个队列都有一个leader,很多follower,leader 负责等待每组follower，每组到达数量后，由leader负责每组的任务执行（如buffer到文件，文件到磁盘）。

总结

一条更新语句的执行周期长这样。

Server层执行器会先请求InnoDB存储引擎接口，接口根据筛选条件，走索引树，找到对应记录。如果记录在Buffer Pool中，直接返回执行器执行更新，如果不在Buffer Pool中，就从磁盘返回给执行器。（返回要更新的记录）
执行器看更新前的记录和更新后是否相同，如果相同，就不继续。如果不同，继续让InnoDB去执行真正的更新操作。（判断是否要更新）
InnoDB会为这个更新操作开启隐式事务，事务开始前，把这个记录的原始值记录下来，先写入buffer log的 undo 页面，在修改undo 页面之后，会记录相对应的 redo log 记录脏页位置。
之后后台线程会先定时，将redo log buffer写入redolog的磁盘，后把脏页的数据在合适时机，同步到磁盘中。
更新记录操作结束。
事务提交后，binlog 会记录逻辑日志，每个线程写入bin log buffer，后写入操作系统的page cache，也叫binlog文件（page cache可以保证mysql进程挂了，数据还在，但是系统挂了就不在了）,只有事务统一提交后才会写入磁盘。
事务提交两阶段。遵循XA协议，先写 XID，到redo log的，设置为prepare状态。后写XID到binlog中，之后将redo log事务改成commit状态，选择合适时机刷入redo log文件。

高可用

如何解决主从复制延迟？

对于评论场景，如发评论，先存数据库，后异步审核。

审核是查从库，存数据库存的是主库。有可能异步查评论审核由于binlog的延迟，查不到数据，造成主从复制延迟。

架构解决：

在异步调用的时候，直接把评论信息传过去，而不是只传一个id。不在从库中查。
利用缓存解决，写主库同时写Redis。审核模块先查redis，再查从库。
直接查主库，得明确主库数据量查询大不大。

主库和从库的数据库访问

利用部署的代理中间件MyCat

MySQL 复制以及应用类型

异步复制：主库不关心从库是否完成备份，最终一致。性能较高。适用于能容容忍数据丢失的场景。
半同步复制。主库保证其中一部分从库完成备份即可。
多源复制。多个不同种类的库，复制到一个从库上，这个从库有其它主库的数据，通常用于OLAP业务。
延时复制。对主库进行一天前的复制，可以做到如果有灾难性的误操作，可以在这段时间内进行恢复。

什么是分库分表？

分库：把一个数据库根据业务，场景拆分成多个库

分表：一张表拆分多个，防止单表过大。

垂直拆分：

根据业务拆分，增强抽象能力，将一张表抽象多个模型。

优点：每个业务模型独立，易于扩展

缺点：无法解决某个业务模型数据膨胀的问题，需要结合水平拆分

水平拆分：

表结构相同，根据某个字段（枚举值）拆分，也可根据数据量拆分，也可将id%num来区分。

Range 分片：根据某个字段的区间来划分，比如每个月的数据，某个商品的种类划分。这样可以进行对业务的评估。如果某个分片有热点问题，可以将这个分片再拆分，用一个分片表记录下来，每次查询先查分片表，找到对应的分片后再查询。

何时分库分表？

数据量大，事务执行缓慢的时候分表。

单库性能无法满足要求的时候分库。

MySQL学习记录