Mysql读书笔记：MySQL实现架构、事务概述、锁机制

注：该内容主要是阅读《高性能MySQL（第三版）》的学习笔记，其中有些内容来源于互联网，仅用来学习。

1、MySQL服务器逻辑架构图

• 第一层：连接处理、认证、安全等

  最上层的服务器不是MySql所独有的，大多数基于网络的客户端/服务器工具或者服务都有类似的系统。比如链接处理，授权认证，安全等等。

  每个客户端连接都会在服务器进程中拥有一个线程，这个链接的查询只会在这个单独的线程中执行，该线程只能轮流在某个CPU核心或者CPU中运行。服务器会负责缓存线程，因此不需要为每一个新建的创建或者销毁线程。
当客户端（应用）连接到MySQL服务器时，服务器需要对其进行认证。认证基于用户名，原始主机信息和密码。如果使用了安全套接字(SSL)的方式连接，还可以使用X.509整数认证。一旦客户端连接成功，服务器会继续验证该客户端是否具有执行某个特定查询的权限。

• 第二层：查询解析、分析、优化、缓存及所有的内置函数等

  大多数的MySql的核心服务功能都在这一层，包括查询解析、分析、优化、缓存以及所有的内置函数（例如：日期，时间，数学和加密函数等）。所有跨存储引擎的功能都在这一层实现：存储过程，触发器，视图。

  MySQL会解析查询，并创建内部数据结构（解析树），然后对其进行各种优化，包括重写查询，决定表的读取顺序，以及选择合适的索引等。用户可以通过特殊的关键字提示（hint）优化器，影响他的决策过程。也可以请求优化器解释(explain)优化过程的各个因素，使用户可以知道服务器是如何进行优化决策的，并提供一个参考基准，便于用户重构查询和schema(对于MySQL下的schema，可以理解成就是存储数据的地方)、修改相关配置，使应用尽可能高效运行。

  优化器并不关心表使用的是什么存储引擎，但存储引擎对于优化查询是有影响的。优化器会起请求存储引擎提供容量或某个具体操作的开销信息，以及表数据的统计信息等。

  对于SELECT语句，在解析查询之前，服务器会先检查缓存(Query cache)，如果能够在其中找到对应的查询，服务器就不必在执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集。

• 第三层：存储引擎

  存储引擎负责MySql中的数据存储和提取。和GNU/Linux下的各种文件系统一样，每个存储引擎都有它的优势和劣势。服务器通过API和存储引擎进行通信，这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明。存储引擎API包含了几十个底层函数，用于执行诸如”开始一个事务“或者”根据主键提取一行记录“等操作。但存储引擎不会去解析SQL(InnoDB是一个例外，它会解析外键定义，因为MySQL服务器本身没有实现该功能)，不同存储引擎之间不会互相通信，而只是简单地响应上层服务器的请求。

2、并发控制

  在MySQL中，从两个层面讨论并发控制：服务器层和存储引擎层。

2.1、共享锁（shared lock）和排它锁（exclusive lock）

  共享锁和排它锁，又称为读锁（read lock）和写锁（write lock）。其中，读锁是共享的，相互不阻塞的，即多个客户在同一时刻可以同时读取同一个资源，互不干扰；写锁则是排他的，一个写锁会阻塞其他的写锁和读锁。

2.2、锁粒度

  加锁是需要消耗资源的，锁的各种操作，包括获取锁、检查锁是否解除、释放锁等，都会增加系统的开销，所以需要在锁的开销和数据的安全性之间寻求一种平衡，而这种平衡也会影响到了性能。而在MySQL中，不同的存储引擎都实现了自己的锁策略和锁粒度。

• 表锁（table lock）
  表锁是MySQL中最基本的锁策略，并且是开销最小的策略。这种锁会锁定整张表，即一个用户在对表进行写操作时前，需要获取写锁，这会阻塞其他用户对这张表的读写操作，只有没有写锁时，其他进行读取操作的用户才可以获取读锁，并进行操作。需要注意的是，MySQL服务器会为诸如ALTER TABLE之类的语句使用表锁，而忽略存储引擎的锁机制。同时，写锁的优先级高于读锁，所以一个写锁请求可能会被插入读锁队列的前面。

• 行级锁（row lock）
  行级锁可以最大程度地支持并发处理，但是同时也带来了很大的锁开销。MySQL服务器层没有实现行级锁，主要是由响应的存储引擎实现行级锁，比如：InnoDB。

3、事务

  事务就是一组原子性的SQL语句，或者说一个独立的工作单元。事务内的语句，要么全部执行，要么全部执行失败。

3.1、事务的ACID特性：

• 原子性（atomicity）
  一个事务要么全部提交成功，要么全部失败回滚，不能只执行其中的一部分操作，这就是事务的原子性。

• 一致性（consistency）
  事务的执行不能破坏数据库数据的完整性和一致性，一个事务在执行之前和执行之后，数据库都必须处于一致性状态。

• 隔离性（isolation）
  事务的隔离性是指在并发环境中，并发的事务时相互隔离的，一个事务的执行不能不被其他事务干扰。不同的事务并发操作相同的数据时，每个事务都有各自完成的数据空间，即一个事务内部的操作及使用的数据对其他并发事务时隔离的，并发执行的各个事务之间不能相互干扰。在标准SQL规范中，定义了4个事务隔离级别，不同的隔离级别对事务的处理不同，分别是：未授权读取，授权读取，可重复读取和串行化。

• 持久性（durability）
  一旦事务提交，那么它对数据库中的对应数据的状态的变更就会永久保存到数据库中。–即使发生系统崩溃或机器宕机等故障，只要数据库能够重新启动，那么一定能够将其恢复到事务成功结束的状态。

3.2、隔离级别

  在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。较低级别的隔离通常可以执行更高的并发，系统的开销也更低。

注：MySQL中，不同存储引擎实现的隔离级别不尽相同。不同数据库产品，某些特性也会有一些不一样。

• 未提交读（Read Uncommitted）
  该隔离级别，事务中的修改，即是没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称为脏读（Dirty Read）。这种级别会出现很多问题，而且性能也没有提高太多，所以在实际应用中很少使用。其中，脏读的示例：事务A和事务B同时进行，事务A在整个执行阶段，会将某数据的值从1开始一直加到10，然后进行事务提交，此时，事务B能够看到这个数据项在事务A操作过程中的所有中间值（如1变成2，2变成3等），而对这一系列的中间值的读取就是未授权读取。

• 提交读（Read Committed）
  该隔离级别，一个事务开始时，只能“看见”已经提交的事务所做的修改，即一个事务从开始直到提交之前，所有的修改对其他事务都是不可见的。大多数数据库默认的隔离级别都是提交读，但是MySQL不是。这个级别有时候也叫不可重复读（nonrepeatable read），因为在同一个事务中，两次同样的查询，可能会得到不一样的结果，比如事务A和事务B同时进行，事务A进行+1操作，此时，事务B无法看到这个数据项在事务A操作过程中的所有中间值，只能看到最终的10。另外，如果说有一个事务C，和事务A进行非常类似的操作，只是事务C是将数据项从10加到20，此时事务B也同样可以读取到20，即允许不可重复读取。补充：事务B，在同一个事务中，获取的数据可能是不一致的，因为在B事务执行期间，数据可能被多次修改。

• 可重复读（Repeatable Read）
  该隔离级别解决了脏读问题，该级别保证了在同一个事务中多次读取同样记录的结果是一致的。但是该级别无法解决幻读问题（Phantom Read），即当某个事务在读取某个范围的记录时，另外一个事务又在该范围内插入了新的记录，之前的事务再次读取该范围的记录时，会产生幻行（Phantom Row）。MySQL默认采用了该隔离级别，其中InnoDB和XtraDB存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）解决了幻读的问题。

• 可串行化（Serializable）
  最高隔离级别。它通过强制事务串行执行，避免了前面说的幻读问题。该级别会在读取的每行数据上都加锁，所以会导致大量的超时和锁争用的问题，实际应用中很少使用。

3.3、自动提交

  MySQL默认采用自动提交（AutoCommit）模式，即如果不显式的开始一个事务，则每个查询都被当作一个事务执行提交。可以通过设置AutoCommit变量来启用或者禁用自动提交模式。

 &esmp;在执行数据定义语句（DDL）比如Alter Table，或者Lock tables等语句时，会在执行前强制执行Commit提交当前的活动事务。

3.4、事务中混用存储引擎

  在MySQL中，服务器层是不管理事务的，事务是由存储引擎实现的。所以在同一事务中，使用多种存储引擎是不可靠的。当在事务中混用了事务型和非事务型的表（比如InnoDB和MyISAM表），在正常提交的情况下是没有问题的，但是当事务需要回滚时，非事务型的表上的变更就无法撤销了，这个时候就会出现数据库中数据不一致的状态，而且数据库也不会有提醒和报错，只会在回滚的时候，发出一个警告：“某些非事务型的表变更不能被回滚”。

3.5、隐式和显式锁定

• 隐式锁定
  InnoDB采用的是两阶段锁定协议（two-phase locking protocol）。在事务执行过程中，随时可以执行锁定，锁只有在执行COMMIT或ROLLBACK时才会释放，并且所有的锁是在同一时刻被释放的。这里提到的都是隐式锁定。

• 显式锁定
  通过LOCK TABLES 和 UNLOCK TABLES语句实现锁定，这是在服务器层实现的，和存储引擎没有关系。除了事务中禁用了AUTOCOMMIT，可以使用LOCK TABLES之外，其他时候都不建议使用显式锁定，LOCK TABLES。

4、死锁

  死锁是指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时，就会产生死锁。多个事务锁定同一个资源时，也会产生死锁。

  为了解决死锁问题，数据库系统实现了各种死锁检测和死锁超时机制。InnoDB目前处理死锁的方法是：将持有最少行级排他锁的事务进行回滚，即死锁回滚算法。

  死锁的产生有两重原因：一种是真正的数据冲突，还有一种是存储引擎的实现方式导致的，因为锁的行为和顺序是和存储引擎相关的。

5、事务日志

  事务日志可以帮助提高事务的效率。使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不是每次都将修改的数据本身持久到磁盘。事务日志采用追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不是随机I/O需要在磁盘的很多地方移动磁头，所以采用事务日志的方式相对来说要快的多。事务日志持久以后，内存中被修改的数据在后台可以慢慢的刷回磁盘。目前大多数存储引擎都是这样实现的，我们通常称之为预写式日志（Write-Ahead Loggin），修改数据需要写两次磁盘。

  如果数据的修改已经记录到事务日志并持久化，但是数据本身没有写会磁盘，此时系统崩溃，存储引擎会在系统重启时自动恢复这部分修改的数据，具体方式根据存储引擎而定。