本文已参与「掘力星计划」，赢取创作大礼包，挑战创作激励金。

深入MySql难点和要点

从大的方向看，mysql分为两层，server层和存储引擎层。存储引擎可以认为是插件，像innodb和myisam引擎都是其中之一。innodb引擎功能强大，是我们现在使用的最多的引擎。

本文涉及的存储引擎都是innodb引擎，讲的地方都是根据自己的经验和知识总结而来，不是很全面，讲的片面的地方希望大家都指正。

Server

Server是支持MySQL的一切使用特性的，比如索引功能，join功能，还有各种函数功能,比如limit、group、rand和now等提供的功能。

分层功能

server从上到下分为连接层、语法解析、语义解析层、查询优化层和执行层。

连接层负责处理连接、校验权限。
语法解析和语义解析层负责解析SQL语句，看包含了哪几张表，看是CRUD中的哪一个，column列的字段是否正确等。
在查询优化层，MySQL计算这个crud语句是使用表的哪一个索引（这些语句如果有where条件命中索引的话），如果有多个索引命中，则选择效率最高的那个。

ddl语句在解析后，优化层不知道能不能帮忙做点优化（比如alter table add column选择的算法，是copy,还是选择inplace？，lock的选择，是none /shared mode/exclusive?）,感觉ddl语句的算法选择还是会由innodb引擎自己选择一个开销最小的（尽量不加锁，尽量保证online，少io(各种日志文件 )）。

优化层大概知道要扫描的行的数量，explain命令是上面优化层的分析结论.

优化层执行之后，Server执行层开始执行，和引擎交互。开始执行的时候，从客户端发起的这条查询query的状态就进入Sending data状态。比如对于insert，执行器发起一条insert指令，引擎执行完后就返回结果给执行层，server就认为执行成功了。对于一个事务，比如3条更新语句和一行delete语句，执行层也会依次给引擎发送事务的begin,…,commit指令。

执行查询的时候，会发起很精细的指令，比如

mysql> select * from T where ID=10;

ID如果没有索引，就发起

取这个表（主键索引）的第一行，判断ID是否=10，不是则跳过，是则保存在结果集（不知道是不是就是net_buffer）

取“下一行”，重复相同的逻辑

结果集返回给客户端

ID如果有主键索引，就发起:

查询这个表主键索引树的ID=10的，没查到则跳过，查到则保存在结果集

结果集返回给客户端

ID如果有普通索引，就发起:

查询这个表普通索引树的ID=10的，没查到则结束，查到则保存在结果集

取“下一行”，重复相同的逻辑

结果集返回给客户端

和引擎接口的两阶段提交

所以Server的执行层会一行一行的发起给引擎的指令，然后交互处理。对于事务，执行器在commit时候，会调用xa_prepare准备些binlog，更具体的情况是下面两阶段提交的xa协议过程。

总体效果来看执行层在事务结束的时候，会记录变更到Binlog日志文件。
（根据设置的binlog格式是row还是statement，image参数是Min还是Full来往binlog里面写入特定格式的二进制数据。）

MySQL认为所有的query(包括简单的select)都是一个事务，默认autocommit=on。这也是为什么binlog的记录里面，BEGIN（event type是Query）和commit（event type =Xid）都是一个event。

但是select语句不会记录在binlog里面。哪怕有加锁的情况，比如这个情况：

mysql复制代码begin ;

select * from t limit 1 for update ;

commit ;

BINLOG只会记录数据成功的情况，如果update实际没有更改一行数据，那么这个也不会记录的，在事务commit的时候，如果确实发生了改动，则会记录到binlog里面，另外注意binlog里面还有一个gtid的情况，这个在事务begin前都会写一个set gtid_next(这个用于特殊的场景，主备切换用的)

mysql复制代码SET @@SESSION.GTID_NEXT= '625900b7-e2b7-11eb-9c96-b8cef604b769:77166'|Gtid

BEGIN|Query

table_id: 240 (test.t)|Table_map

table_id: 240 flags: STMT_END_F |Update_rows

COMMIT /* xid=13152312 */|Xid

xid也不是事务ID，xid用于两阶段提交的redo log和binlog,保证这两者的一致性。

可是xid为什么不使用事务ID，因为事务ID是会复用的（系统中不存在这个事务，后面使用的事务当然就可以复用了），但是xid需要记录到binlog,不能重复，所以xid和trx_id没有复用。

ddl语句也会记录到binlog里面，这样备库也会按照这种方式去创建表结构，回放同样的ddl(比如drop table /create table)。

InnoDB引擎

现在我们可以看看底层的innodb引擎都做了什么。MySQL支持支持事务的ACID特性，离不开innoDB的支持。存储引擎的首要功能就是存储数据，InnoDB使用B+树结构来存储表的数据。

索引和存储数据结构

那要选择怎样的数据结构呢？

有序数组查找很快，但是插入的效率很低。
哈希表查找很快，插入和更新也快。但是为什么哈希表也不合适？哈希表完全是随机的，想要按照范围查询几乎不可能，这满足不了MySQL的业务要求。
可以用来快速查找，快速更新、删除和新增的数据结构有很多，比如平衡二叉树LV树，红黑树等。

因为MySQL要存储的数据量很大，数据存在磁盘上，如果使用二叉树，树的层级很高，查找效率很慢。为了使得查询磁盘的次数减少，需要使用B-树。B-树存储索引和数据的节点一样，但是innodb对索引和数据的存储需求不同。所以InnoDB根据实际情况（叶子节点才存储数据，非叶子节点只存储索引），使用B+树作为组织数据存储和查找（CRUD）的数据结构。

要了解B-的过程，很有必要了解B-树的性质。

B-树性质：

M为树的阶，非叶子节点最多只有M个儿子，且M>2
root节点的儿子数为【2 M】,| 0
除根节点以外的非叶子节点的儿子数为【M/2 M ]
每个节点(root除外)存放至少M/2-1和至多M-1个关键字
分裂前后、所有叶子节点位于同一层（那么一定是平衡的）

B+树叶满足b-树的要求，B+树的叶子节点的元素是所有数据，上层的数据只是用来索引、不作为最终结果。

关于B+树数据结构的增删改查这一块，算法还比较复杂，对B-树性质的理解和推演很有必要。可根据性质推演分裂过程。比如这个从一个节点分裂成5阶B-树的过程，如下可以看到：

可以发现这个5阶的树（每页存四个数据，最多5个指针）只三层已经很存储很多节点了，而Mysql每页16k大小,四层已能存储大量的数据。

怎么支持事务

innodb引擎支持事务的ACID（原子性、一致性、持久性和隔离性）。

支持这些特性的代价非常高，往往意味着不太好的扩展性(这也就是mysql的水平扩展不如nosql的原因）。

原子性

原子性的意思就是要么同时成功，要么同时失败。

为了支持事务的原子性，InnoDB引擎设计了undolog日志，事务里面的每一个操作除了会修改实际的data,还会去undolog里面增加记录.

比如X->Y的操作，undolog会记录（事务ID：Y->X），这样回滚的时候，就直译可将数据从Y改为X。

undolog的作用不仅仅是用于事务回滚，还对下面提到的隔离级别镜像读提供了最有用的帮助。在RR（可重复读隔离级别）的时候，一个数据只读到自己事务开始时候的数据，后面这行数据如果被别的事务修改了，那么这行数据则会根据undolog的记录回滚。因为undolog日志不需要的内容可以清理掉，所以分析查找undo log日志的效率还是会挺高的。

隔离性

隔离性也就是指对并发的支持，引擎支持四种事务隔离级别，分别是串行化、RR（可重复读隔离级别）、RC（读已提交隔离级别）、RU（读未提交隔离级别）。

串行化就是完全按顺序执行，用一个线程同步阻塞的执行各个事务就好了。RU是read uncommitted，这个有事务和没事务完全就没有区别。这两个极端，使用关系数据库的业务一般都是不会选的。

RC是读已提交，其他事务已经提交的修改，在当前事务是可以读到的。RC存在不一致读的问题，在t1时刻读到的数据，和在t2读到的数据不一致。对于某些审计类的业务场景，比如明细和总额在不同的时刻读，就不一致了。这会造成业务的困扰。

为了解决RC的问题，引擎设计了另外一种隔离级别RR,可重复读，在t1时刻读到的数据，和在t2读到的数据一致。也就是上面提到的undolog和READVIEW数据结构的设计使得可重复读成为可能，这种机制也叫做MVCC，多版本并发控制。

持久性

引擎需要支持持久性才能保证数据的安全可靠。内存掉电就丢数据了，引擎将数据存储到磁盘，才能保证数据的持久可靠。

假设引擎将数据存储到磁盘了，然后告诉执行器结果，执行器就将这个过程写到binlog。此时如果binlog写失败了，但是磁盘上的数据还在，这样备库和主库的数据就不一致了。

binlog写失败可能是各种各样的原因，比如磁盘满了，程序bug了，掉电了。此时执行器必须也返回一个binlog的结果给到引擎，如果binlog写失败，引擎再回滚数据。写到磁盘上的某块数据，再回滚。但是磁盘随机寻址折腾的效率很低，这么做效率很低。

为了保证性能，引擎设计了另外一种日志，redolog，如果有修改数据，在事务commit的时候，先不刷盘，而是将这个事务的修改append到redolog，这个也叫WAL（日志先行），并且标记为prepared状态，后面当执行器告知binlog也写成功的时候才标记为commit状态。

redolog是循环日志，有一个写入点（新日志都append到这里），还有一个checkpoint指针，相当于橡皮擦，checkpoint检查是否可以将检查点的的数据刷回磁盘。

redolog是存储引擎负责的，binlog是server执行器使用的。
redolog

一致性

前面讲述了ACID的原子性、隔离性和持久性三个性质在MySQL中是怎么满足的，一致性是在这三个性质得到很好满足的情况下，自然而然的结果。
比如采用了两阶段提交，来保证主背数据的一致性（binlog和redolog日志的一致性）。
在这样的情况下，业务的事务逻辑一致、主备一致、查询语义的一致等等都可以得到满足。

QA

梳理基础知识后，发现对理解MySQL还远远不够，还有很多的疑问，值得深究。

而且这一块的问题可以很有意思，也能触及到很多的细节。

1. 为什么有binlog，InnoDB还要使用redolog?

从实现来看redolog是物理层面的日志记录，binlog是逻辑层面的。
redolog能保证持久性，而binlog不行
redolog是顺序写，写磁盘效率高
redolog能保证持久性。redolog和binlog通过xid保持一致，在故障恢复的时候，binlog可以通过xid来保证恢复和redolog一致（也就和其他备份都一致了）

2、insert into t2 select * from t1这条语句是怎么加锁的？

加锁的基本单位是next-key，

RR隔离级别下，对t1的所有记录加 next-key锁。

为了保证主备的一致性，这个地方本来是statement格式的才需要加next-key锁，这样可以保证日志和数据的一致性。不然备机上面的数据就和主机上面的不一致了。

又由于历史的一些原因，现在row格式的也都是加了next-key锁的。

3. redolog在checkpoint的时候刷数据到磁盘，是直接从redolog到磁盘，还是直接将buffer pool的数据刷到磁盘？

因为redolog记录的是磁盘地址的修改，比如A物理地址，数据从X-Y

buffer-pool是最新的data，对应的磁盘地址也都在内存当中，就直接将buffer-pool flush. 但是对于宕机重启的情况，从redolog的checkpoint的点开始，记录的地址不在buffer pool，则需要先将data load到内存，然后依次应用redo log里面所有涉及的修改，再刷新回磁盘。

4. RR有没有幻读，怎么解决的？

上面提到了RR，但是没有说到幻读这个事情。这篇文章提到了MVCC，但是还没有从锁的角度来解释并发写时候的控制。

写用当前读是什么意思？首先是因为写会加锁，尤其是next_key锁，还能防止幻读。

MySQL为了保证数据的强一致性，使用了加锁机制。在RR下，加锁的基本原则是next-key锁。

5. 为啥要commit的时候才能释放锁？

commit的时候表示成功了，才释放锁。不然中间可能还存在回滚。

当另外一个写能拿到锁的时候，那么读到的内容都是已经提交的，也就是当前读其实读已提交，未提交的数据还是读不到的。

所以当前读是读已提交。

在已提交上面加锁就防止了写覆盖。

比如两个事务都执行：update set t=t+1 where id=1; 最终就是+2；如果不用当前读就是+1；

所以不要站在读的立场去理解写（因为各种隔离级别，很容易站在读的立场去理解事务中的各种操作），在写的立场去理解写就很容易理解必要要加锁

写的时候记录了undolog，不影响业务需求想要的一致性读，可重复读。

读一个范围的时候：

sql复制代码beigin

select ... for update

select ... for update

commit

这是事务两次读，两次读不一致就是幻读，所以这个必须加锁，事务结束的时候才能释放锁。

6. 为什么不能有幻读？

幻读就是指在RR隔离级别下，一个事务里两次读的数据不一样。
这首先会导致语义不一致，其次，statement格式的 binlog日志和数据不一致，还会导致主备不一致。

所以next-key锁可以保证即使是在statement格式，binlog日志和数据也是一致的。

为了防止幻读，还引入了next-key锁，防止插入到间隙。

为什么next-key锁可以防止幻读呢？有没有一个理论上的公式。没有，但有一些原则，就是默认next-key锁(为何最后非要加一个key)，唯一索引特殊处理，以及向右扫描策略。

next-key锁导致实际锁的范围很大（悲观锁），但是因为数据结构设计的原因，也只能如此了。

左开右闭的原因？可能只是一种工程原因。

不过可以从数据结构来理解锁的范围,看上面提到过的树结构：

比如要给这个t表的索引树（普通索引c）where c=19 for update加锁，那么加的是next-key锁，也就是会锁住（14，23]这里的所有记录。

7. BufferPool是Server创建的数据结构，还是InnoDB的？

BufferPool当然是引擎创建的数据结构，使用LRU算法，比如我的db的buffer pool size是5G，而磁盘的数据量是100G，不可能把所有的数据都加载到内存的，所以需要使用LRU算法来选择哪些数据可以被淘汰。最近最少使用的数据least recently used的数据将会被淘汰（还针对业务新旧数据特征，做了很多改进优化）。

总结

本文并没有很全面的详述mysql中的所有知识点，但不影响我们查漏，这些都了解了吗？

innodbB+ 树存储
叶子存树枝索引
普通索引和主键树
支持ac和id
事务成功或失败
undolog来帮忙
主键逆操作链回滚
并发query要隔离
隔离级别很复杂
完全隔离串行化
读未提交和已提交
RR 可重复读更隔离
怎么支持？
MVCC多版本来控制
事务ID是自增
ReadView记录当前活跃事务
忽略活跃事务的修改
修改记录还加锁
innodb行锁是单位
事务结束释放锁
其实事务修改是当前读
而不再是可重复读
这会存在另一个问题
如果只加行锁会幻读
next-key锁来帮忙
执行器控制引擎
两阶段提交来一致
redolog和binlog常交互

本文转载自: 掘金

开发者博客 – 和开发相关的这里全都有