高性能MySQL学习一(逻辑架构、并发控制、事务、四种隔离级别、多版本并发控制MVCC)

本文链接：https://blog.youkuaiyun.com/yyyxxxs/article/details/98200449

1、MySQL三层逻辑架构
在这里插入图片描述

第一层负责连接管理、授权认证、安全性等。
每个客户端的连接都对应着服务器上的一个线程。服务器上维护了一个线程池，避免为每个连接都创建销毁一个线程。当客户端连接到MySQL服务器时，服务器对其进行认证。可以通过用户名和密码的方式进行认证，也可以通过SSL证书进行认证。登录认证通过后，服务器还会验证该客户端是否有执行某个查询的权限。
第二层负责解析查询(编译SQL)，并对其进行优化(如调整表的读取顺序，选择合适的索引等)。对于SELECT语句，在解析查询前，服务器会先检查查询缓存，如果能在其中找到对应的查询结果，则无需再进行查询解析、优化等过程，直接返回查询结果。存储过程、触发器、视图等都在这一层实现。
第三层是存储引擎，存储引擎负责在MySQL中存储数据、提取数据、开启一个事务等等。存储引擎通过API与上层进行通信，这些API屏蔽了不同存储引擎之间的差异，使得这些差异对上层查询过程透明。存储引擎不会去解析SQL（InnoDB除外，它会解析外键定义），不同存储引擎也不会相互通信，而只是简单地响应上层服务器的请求。

2、并发控制
（1）读写锁
读锁：也叫共享锁，相互不阻塞，多个客户在同一时刻同时读取同一资源，互不干扰。
写锁：也叫排他锁，一个写锁会阻塞其他写锁和读锁。
（2）锁粒度
为了提高共享资源的并发性，我们可以让锁定对象更有选择性，尽量只锁定需要修改的部分，不锁定其他资源。资源一定时，锁定的数据量越小，系统的并发性越高。
加锁需要消耗资源，所以说，所谓锁策略就是在锁的开销和数据的安全性间寻求平衡。
（3）表锁和行级锁
表锁：MySQL中最基本的锁策略，开销最小。会锁定整张表，用户在对表进行写操作前要获得写锁，阻塞其他用户对表的所有读写操作。只有没有写锁时，其他读取用户才能获得读锁，读锁之间互不阻塞。
行级锁：最大程度地支持并发处理，但也带来了最大的锁开销。

3、事务
事务是一组原子性的 SQL 查询, 或者说是一个独立的工作单元. 在事务内的语句, 要么全部执行成功, 要么全部执行失败。

我们通过一个例子来理解一下事务的四个特征。现在我们从id为10233276的用户的checking账户转移200元到其savings账户，我们使用START TRANSACTION开始一个事务，使用COMMIT进行提交。
在这里插入图片描述
事务具有以下四个特征（ACID）：

原子性(Atomicity)：事务作为一个整体被执行, 包含在其中的对数据库的操作要么全部执行成功, 要么全部失败回滚. 对于一个事务来说, 不可能只执行其中一部分操作, 这就是事务的原子性。
一致性(Consistency)：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。在上面的例子中，假如在3、4之间系统崩溃，那么对于用户账户也不会亏损，因为事务没有最终提交，所以其所做的修改不会记录到数据库。
隔离性(Isolation)：多个事务并发执行时, 通常来说，一个事务所做的修改在没有最终提交之前，对其他的事务是不可见的。从上面的例子来说，假设该事务执行完3，这时另一个账户汇总程序开始运行，它所看到的该用户账户并没有少200元。
持久性(Durability):：已被提交的事务对数据库的修改应该永久保存在数据库中。

4、隔离级别
先列个表看一下：

	脏读	不可重复读	幻读
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable read	×	×	√
Serializable	×	×	×

√：可能出现 ×：不会出现
（1）未提交读（Read uncommitted）
一个事务中的修改，即使没有提交，其他事务也是可见的。这也就意味着，一个事务可以读取未被提交的数据，这被称为脏读。
（2）提交读（Read committed）
一个事务从开始到提交之前，所做的修改对其他事务来说都是不可见的。这个级别有时也叫不可重复读，因为可能我们两次执行了相同的操作，但看到了不一样的结果。
（3）可重复读（Repeatable read）
解决了脏读和不可重复读的问题，但不能避免另一个问题，幻读。所谓幻读就是指当一个事务在读取某个范围之间的记录时，另一个事务在该范围内插入了新记录，当之前的事务再次读取该范围的记录时，会产生幻行。
（4）可串行化（Serializable）
强制事务串行执行，避免了幻读问题。会在读取的每行数据上都加锁，所以可能导致大量的超时和锁争用问题。

5、死锁
简单来说，就是两个及以上的事务在同一资源上相互占用，它们自身锁定了部分资源，同时请求对方锁定的资源，由此，造成了死循环。
为了解除死锁，需要外部因素的介入。为解决该问题，数据库系统实现了死锁检测和死锁超时机制。如InnoDB存储引擎，检测到死锁的循环依赖时就立即返回一个错误，这是一种很有效的解决方式。还有一种方式就是，当查询时间达到锁等待超时的设定时放弃锁请求。
死锁发生后，需要部分或完全回滚其中一个事务来打破死锁。InnoDB选择将持有最少行级排他锁的事务进行回滚，这是一种比较好的方式。

6、多版本并发控制MVCC
我们可以认为多版本并发控制MVCC是行级锁的一个变种，但它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制不同，但大都实现了非阻塞的读操作，写操作也值锁定必要的行。
MVCC的实现，是通过保存数据在某个时间点的快照实现的。这包含了两个意思：
（1）一个事务不管执行多长时间，看到的数据都是一致的；
（2）事务开始的时间不同，则在同一时刻针对同一张表，它们看到的数据可能不同。

通过InnoDB，了解一下MVCC是怎么实现的。
InnoDB通过在每行记录后面保存两个隐藏的列来实现MVCC，一个列保存行创建时间，一个列保存行过期时间。这个时间并不是实际时间，而是系统版本号。每开始一个新事务，系统版本号都会递增，开始时刻的系统版本号就作为事务版本号。在可重复读隔离级别下 ,MVCC具体操作如下：
（１）SELECT：