本文已参与「新人创作礼」活动，一起开启掘金创作之路。

MySQL逻辑架构

在这里插入图片描述

图片来源：《高性能MySQL》

第一层做一下安全认证，授权处理之类的，大多数C/S工具都会有这样的结构。
第二层是大多数MySQL核心功能的所在，包括查询解析，分析，优化，以及所有的内置函数（例如日期，时间，加密函数），所有的跨存储引擎的功能也都在这一层实现（例如存储过程，触发器，视图）
第三层是存储引擎层，mysql有不同的存储引擎，服务器通过API和存储引擎通信，这样就屏蔽了不同存储引擎之间的差异。存储引擎API包含几十个底层函数，用于执行诸如‘开始一个事务’或者‘根据主键提取一行记录’等操作，存储引擎不会解析SQL，不同存储引擎之间也不会相互通信，而只是简单的响应上层服务器的请求。

因此一个SQL的执行过程如下：

连接器，进行身份认证，权限认证。权限认证对于一个连接来说是终身的，一旦认证完毕，即使管理后续修改了这个用户的权限也不会影响，除非断开重连。
查询缓存：执行之前，MySQL会先进行缓存查询，缓存以KV方式存储在内存中，Key是SQL，Value是结果集，如果命中就直接返回，没有命中就执行后续操作，最后把结果缓存起来。在MySQL8.0，缓存功能被舍弃，这是因为官方认为实际需要用的场景较少，对于常更新数据，缓存有效数据很短，而对于冷数据，缓存又显得没有必要。而对于经常读取的数据，大部分情况开发人员会自己在应用层缓存。
分析器：对SQL进行分析，分析SQL要干嘛
优化器：对SQL进行优化，选择最优的执行方案
执行器：校验权限，然后调用存储引擎的API去进行处理。

MySQL的缓冲池

MySQL数据是要落盘的，而磁盘的读写操作相较于内存慢的多，因此为了避免每次都操作磁盘，MySQL设计了基于内存的缓冲池来提高执行效率。缓冲池在存储引擎层，属于存储引擎的实现。

一条数据执行时，如果是读操作，要查找的数据所在的数据页在内存中时，则将结果返回，否则会先将其所在的数据页加载到内存中，然后再返回结果。如果是写操作，要修改的数据所在的数据页在内存中时，则修改后返回对应的结果（后续再刷入磁盘中），否则会先将其所在的数据页加载到内存中，再进行修改。

基于内存的操作会让执行效率大大提升，因此为了尽可能的让数据所在的数据页都在内存中，MySQL具有预读的特性，也就是说当读取一个数据页时，会将相邻的数个数据页也提前读入内存，因为大多数数据的获取都是相邻的。而不把所有的数据页都放入缓冲池一是因为内存的易失性，二是缓冲池大小有限制。

因为缓冲池大小的限制，缓冲池会采用LRU算法来淘汰不常用的数据页以避免溢出。

如果使用传统的LRU算法：维护一个链表，如果一个数据页被访问，就放到链表头部，如果这个数据页是新数据页，也同步抛弃链表尾部的是数据保证不溢出。这种算法对于MySQL的实现机制来说有两个隐患：预读失效和缓冲池污染。

预读失效：因为MySQL的预读机制，可能会一次加载多个数据页，而这些数据页并不一定是全都有用的，如果此时将这些数据页都放到链表头部，淘汰已有的尾部，那就可能出现无用的数据页被放入缓冲池，而常用的数据被移除。这就属于预读失效。

缓冲池污染：如果执行一条需要全表扫描或者扫描了大量数据的SQL，那么缓冲池会加载大量的数据页，如果采用传统LRU算法，会使得常用数据页都被替换出去，导致MySQL性能下降。这就是缓冲池污染。

为了应对预读失效和缓冲池污染，MySQL采用了冷热数据分离的方式，即数据页被读取后，先放到冷数据区域的头部，如果1S后缓冲页再次被访问，才将其移入热数据区域头部。1S的延迟是为了尽量保证这个数据页不是一个临时读取。另外，对于热数据区域来说，可能内部的数据页会被频繁读取，因此为了避免每次读取都维护链表，同时又能保证LRU算法的诉求，MySQL对热数据区的维护做了优化，只有后3/4的数据页被访问时才移动到头部，前1/4的数据页被访问了也不会移动。

存储引擎

文件系统中，MySQL将每一个数据库（也可以称为schema）保存为数据目录下的一个子目录，创建表时会在子目录下创建一个与表名相同的.frm文件保存表的定义。数据库中元数据大小写敏感性与平台相关，如window平台大小不敏感，那么元数据也不敏感。

可以用SHOW TABLE STATUS命令查看表信息，会包含占据空间大小，类型，时间等各种各样的数据。可以用ALTER TABLE tableName ENGINE = InnoDB修改存储引擎。

MySQL5.1及之前的默认引擎是MyISAM，MySQL5.1之后默认的引擎是InnoDB。在引擎选择上，除非你要使用InnoDb没有的特性，且没有其它方式替代，否则都应该优先选择InnoDB引擎。因此就只看InnoDB引擎了。

InnoDB

MySQL5.5之前使用的是InnoDB，MySQL5.5使用的是InnoDB plugin.这是因为Oracle收购了Sun公司，将InnoDB变成自己的了，因此MySQL5.5之前的版本可能使用的是旧的InnoDB，可以换成InnoDB plugin，性能好很多哦。除这一段之外本文所有的InnoDB其实都指的是InnoDB plugin。

InnoDB支持事务，InnoDB采用MVCC支持高并发，而是实现了4个标准的隔离级别，默认的隔离级别是REPEATABLE_READ(可重复读),并且通过GAP锁也就是间隙锁（next-key locking）策略避免了幻读。即 InnoDB 存储引擎自己实现了行锁，通过 next-key 锁（记录锁和间隙锁的组合）来锁住记录本身和记录之间的“间隙”，防止其他事务在这个记录之间插入新的记录，从而避免了幻读。GAP锁在READ COMMITTED也存在，但是仅仅用做外键约束和重复键检查，而不避免幻读，这是因为幻读并不一定是坏的，有些场景确实需要这种读取最新数据的操作，而READ COMMITTED定义来就是支持这种场景的。

InnoDB表基于聚簇索引建立，聚簇索引对查询有很高的性能，不过他的二级索引必须包含主键列,因此如果一个表的索引很多的话可以适当的减小主键内容，这样能减少索引的大小。InnoDB的数据文件和索引文件是平台独立的，也就是说可以把Intel平台的文件复制到PowerPC平台继续使用。

InnoDB有很多优化，比如磁盘读取数据时的可预测性预读，能够自动创建hash索引以加速读操作的自适应哈希索引（adaptive hash index）,以及能够快速插入操作的插入缓冲区（insert buffer）等。后面细说。 InnoDB可以通过一些机制和工具实现真正的热备份，无需限制读写。MySQL其它存储引擎都不支持。

锁

锁级别

mysql默认的锁策略是表级锁，也是开销最小的策略。同时mysql一些存储引擎如InnoDB和XtraDB也实现了行级锁，最大程度的支持并发，但是相应的锁开销也最大。注意是存储引擎层实现的，上层服务器层完全不了解，它只是调API而已。如果采用了InnoDB引擎，那就是行级锁了。

死锁

事务带来更高的安全性的同时也带来了更高的开销。对于事务死锁，比如事务A要锁定行1,行2，事务B要锁定行2,行1，如果事务A先锁定1，事务B先锁定2，此时就会出现死锁，对于这种死锁数据库系统实现了各种死锁检测和死锁超时机制。InnoDB目前的处理方式是将持有最少行级排他锁的事务进行回滚。同时为了避免因为批量更新的顺序差异导致的死锁问题，我们在更新时，如果是根据ID进行更新，那么可以将ID都按照相同顺序来排列，这样就可以避免死锁的发生。而在涉及二级索引更新的语句中，可能发生二级索引和一级索引加锁顺序(如一个SQL先锁二级再锁一级，一个SQL先锁一级再锁二级)，此时就需要依据具体的业务场景通过减小锁粒度或者分布执行来解决死锁问题。

事务

事务的ACID

事务的ACID 4要素：原子性，一致性，隔离性，持久性。

Atomicity(原子性)：一个事务内的所有操作，要么全部完成，要么全部失败，不会结束在中间的某个环节。事务在执行过程中发生错误，会恢复到事务执行前的状态，就像这个事务从未执行过一样。
Consistency(一致性)：事务的执行不能破坏数据库的完整性，数据库只能从一个一致性状态转变到另一个一致性状态。写入的数据要符合预设的规则。包括但不限于主键唯一性，列完整性（类型长度符合要求），外键约束，用户自定义的完整性。不能说事务对数据库的修改只有一部分写入物理数据库，这种就属于一致性被破坏。
Isolation(隔离性)：隔离性是指事务之间相互干扰的程度，一个事务对另一个事务的干扰程度不能超过定义的隔离级别。
Durability(持久性)：事务执行完毕后，对数据的修改是永久的，即使数据库发生故障也不会丢失。

事务的隔离级别

隔离级别isolation：事务之间的隔离级别。数据更新时因为并发的问题会导致各种各样的问题：

更新丢失.多个事务对同一条数据操作。在事务A未提交时,事务B进行操作,此时后提交的事务就会覆盖掉先提交的事务所做的更新,造成了更新丢失。如银行账户.用户A账户中有10元钱。同时受到用户B转账的10元和用户C转账的20元。此时如果我们使用sql.1{UPDATE USERACCOUNT SET ACCOUNT = 20 WHERE USERNAME = A}与sql.2{UPDATE USERACCOUNT SET ACCOUNT = 30 WHERE USERNAME = A}.此时无论A先提交还是B先提交都会导致先提交的那个事务所做的更改被覆盖。事实上，很多数据库都会默认在底层加锁来避免这种现象发生。
脏读。一个事务查询到另一个事务未提交的数据，如果再查询之后另一个事务的更新操作被回滚就会出现脏读现象。如银行账户，用户A想买了价值1000元的商品。此时用户A余额为900元。于是就用户B给用户A转账100元。在转账事务未提交的时间，用户A看到自己账户以及变为1000元就去付款。此时B给A的转账事务被回滚。那么A付款时就会出现余额不足的清况。脏读是非常危险的。因为其读取到一个未提交的数据。一旦这个未提交的数据被回滚。那么以此查询为基础的之后任何操作在链式反应下全部都会得到期望之外的结果。
不可重复读。一个事务对同一条数据的两次查询返回了不同的结果。同样以账户为例。在一个事务T里面，用户A第一次查询账户余额为1000元。查询之后用户B转账给用户A100元。此时在事务T里面再次查询用户A的余额得到为1100元。在同一个事务里面对同一条数据的两次查询获得了不同的结果就产生了不可重复读。
幻读。在同一个事务中，条件相同的两次查询得到不同的结果。比如说在事务T中。查询所有姓A的客户的数量，获得结果为999位，此时又有一位A姓客户注册了，那么在事务T中再次查询姓A的客户的数量时获得结果为1000.此时便出现了幻读现象。

不可重复读和幻读都必须是在同一个事务中内的。不同的是不可重复读是对同一条数据的查询。幻读是相同条件下的一批数据的查询。

MySQL提供了4种事务隔离级别

READ_UNCOMMITTED：读未提交。此种隔离级别下未提交的的数据可以被查询到。会出现脏读，不可重复读，幻读。
READ_COMMITTED：读已提交。此种隔离级别下未提交的数据将不可被查询到。只有已提交的数据才可查询到。此种隔离级别不会出现脏读，但是会出现不可重复读和幻读。
REPEATABLE_READ：可重复读。此种隔离级别下不会出现脏读和不可重复读，但是会出现幻读。
SERIALIZABLE：串行化。最高的隔离级别。所有的事务串行执行，不允许并行。此种情况下脏读，不可重复读，幻读都不会出现。

隔离级别的提升势必会带来性能的下降。因为要花费更多的资源到事务隔离上。

事务中混合使用存储引擎

事务仅在支持事务的引擎上才有用，但是事务由存储引擎实现，所以在同一个事务中使用多种存储引擎是不可靠的。如果事务中涉及了事务型表（如InnoDB）和非事务型表（如MyISAM表），正常提交情况下是没有问题的，但是在异常情况下，事务型表可以回滚，非事务型却无法执行回滚操作，破坏事务的原子性。因此如果在非事务表上产生了回滚操作，MySQL会发出一个警告：某些非事务型的表上的变更不能被回滚。正常提交下则不会有任何提示或报错。

mysql目前支持事务的引擎有InnoDB和NDB Cluster。还有一些第三方的引擎如XtraDB和PBXT。

MySQL默认采用自动提交的事务策略，可以通过AUTOCOMMIT变量控制启动和禁用。这个对于非事务引擎是没用的，毕竟非事务引擎都没事务。如MyISAM表。

事务所加的锁属于隐式加锁，InnoDB可以通过特定的SQL语句进行显示锁定。显示锁定的使用要慎重，尽量避免使用，因为严重影响性能，而且一般你都没有InnoDB自带的行级锁做的更好。

主从同步

MySQL实现主从同步是通过binlog（备份日志）和 relay-log（中继日志）实现的，主节点将binlog信息发送给从节点，从节点将数据写到 relay-log（中继日志），然后根据 relay-log（中继日志）来进行同步。

MySQL的主从同步有四种：

同步复制：主节点写好binlog日志就发送同步信息给从节点，commit后接到从节点的ACK才继续操作。从节点commit后才ACK。
异步复制：默认方式，主节点写好binlog日志就放同步信息给从节点，然后就不管了。这种主节点宕机可能导致数据不同步，DBA人工查看binlog日志确定position来完成同步。MySQL5.6.5 提供了GTID来进行同步定位，理念就是为每个事务都生成一个ID并记录当前执行位置以进行定位。
半同步复制：主节点写好binlog日志就发送同步信息给从节点，commit后接到所有从节点的ACK才继续操作。从节点写好binlog就ACK。
增强半同步复制：主节点写好binlog日志就发送同步信息给从节点，接到任意一个从节点的ACK后才进行commit。从节点写好binlog就ACK。

开发成长之旅 [持续更新中...]
欢迎关注…

44：MySQL架构基础与事务简介