1 MySQL逻辑架构

MySQL逻辑架构图

MySQL Server层（服务器层）

MySQL中不涉及真实数据存取的功能、跨存储引擎实现的功能在MySQL Server这一层，如存储过程、触发器、视图等
连接管理

每个客户端都连接会在MySQL服务器中拥有一个线程，这个连接的查询都只会在这个单独的线程中进行
MySQL支持线程池，可以用池中的少量线程来服务大量的连接
客户端连接到服务器时，服务器还需要对其进行各种认证
MySQL服务器接收到的请求只是一个文本信息，该文本信息还要经过后续各种处理

查询缓存

对于SELECT语句来说，在解析之前，服务器会先去检查查询缓存，如果能在其中找到对应的查询，服务器就不必再执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集
缓存虽然能够提升数据库的查询性能，但是缓存同时也带来了额外的开销，每次查询后都要做一次缓存操作，失效后还要销毁。 因此，开启查询缓存要谨慎，尤其对于写密集的应用来说更是如此。
MySQL 8.0版本后已经移除查询缓存，因为这个功能不太实用

解析器

MySQL会在MySQL Server层使用解析器解析SQL，而存储引擎不会去解析SQL
解析器在解析SQL时会创建一个解析树

优化器

解析器在解析SQL后，优化器会对解析器产生的解析树进行各种优化，因为由我们写的SQL语句生成的解析树执行起来效率不可能不高
优化方式包括重写查询、决定表的读取顺序、选择合适的索引等

执行器

服务器通过执行器提供的API与存储引擎进行通信，这些API屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明
这些API的实现由各存储引擎实现

存储引擎

MySQL支持多种存储引擎，每种存储引擎都有它的优劣

2 并发控制

MySQL中的并发控制
- 只要有多个查询需要在同一时刻修改数据，都会产生并发控制的问题
- MySQL在两个层面进行并发控制：服务器层和存储引擎层
- 在数据库中，除传统的计算资源（如 CPU、RAM、I/O 等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。
  1 锁的分类
根据数据操作的类型，可以将MySQL中的锁分为
- 读锁

读锁是共享的，或者说是不堵塞的，多个线程在同一时刻可以共同读取同一个资源

写锁

写锁则是排他的，一个写锁会阻塞其他的写锁和读锁

锁的策略概述
- 提高共享资源并发度的方式就是让锁定对象更有选择性，即尽量只锁定需要修改的部分数据，而不是所有的资源。更理想的方式是，只对会修改的数据进行精确的锁定

在给定的资源上，锁定的数据量越少，则系统的并发程度越高，只要相互之间不发生冲突

虽然锁的粒度越细，系统的并发程度越高，但是加锁也是需要消耗资源的，如果系统花费大量时间来管理锁，那么系统的性能将会受影响
在锁的开销和系统的并发度之间寻求平衡，称为锁的策略。MySQL存储引擎实现了自己的锁策略和锁粒度。将锁粒度固定在某个级别，可以为某些特定的应用场景提供更好的性能，但同时却会失去对另外一些应用场景的良好支持
MySQL支持多种存储引擎，因此不需要单一的通用解决方案

两种最重要的锁策略
1. 表锁（table lock）
2. 行锁（row lock）

表锁
- 表锁是MySQL中最基本的锁策略，其特点是开销小
- 表锁会锁定整张表，一个用户在对表进行写操作（增删改）前，需要先获得表锁
- 表锁的写锁比读锁有更高的优先级
- 尽管存储引擎可以管理自己的表锁，MySQL服务器还是会使用各种有效的表锁来实现不同的目的，例如服务器会为诸如ALTER TABLE之类的SQL语句使用表锁，而忽略存储引擎的锁机制
行锁
- 行锁可以最大程度地支持并发处理，同时也带来了最大的锁开销
- InnoDB和XtraDB等存储引擎中实现了行锁，但是服务器层没有实现行锁
- 使用行锁时可能出现死锁

3 事务

事务概述
- 事务就是一组原子性的SQL查询
- 如果事务中有任何一条语句无法执行，那么所有的语句都不会执行
- 事务内的语句要么全部执行，要么全部执行失败
自动提交

在MySQL中，如果不是显式地开启一个事务，则每个查询都被当做一个事务并且采用自动提交模式

1 事务的特性

事务具有四大特性，称为事务的ACID特性

原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）

原子性

对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性

一致性

一致性是指事务必须确保数据库从一个一致性状态转换到另一个一致性状态，即一个事务执行之前和执行之后数据库都必须处于一致性状态
所谓的一致性状态指的是当前的状态满足数据库预定的约束（也可以理解为满足现实世界中的约束）

隔离性

通常来说，一个事务所做的修改在最终提交之前，对其他事务是不可见的
上面所说的特性只是在通常情况下，具体情况还需要根据隔离级别来确定
隔离性实际上是要保证其他事务的执行不会影响到本次事务的执行

持久性

持久性是指一旦事务提交，则其所做的修改就会永久保存到数据库中

2 事务与性能

使用事务会牺牲性能
- 保证事务的ACID特性，需要数据库做更多额外的工作
- 用户可以根据业务是否需要事务处理来选择合适的存储引擎。对于一些不需要事务的查询类应用，选择一个非事务型的存储引擎可以获得更高的性能
- 即使存储引擎不支持事务，也可以通过LOCK TABLES语句来为应用提供一定程度的保护
LOCK TABLES和UNLOCK TABLES不能代替事务
- LOCK TABLES和UNLOCK TABLES是在服务器层实现的，和存储引擎无关
- 这两个语句有自己的用途，但并不能代替事务处理，如果需要用到事务，还是应该选择事务型存储引擎

3 事务的隔离级别

隔离级别概述
- SQL标准定义了四种隔离级别，每一种隔离级别都规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。
- 四种隔离级别的隔离程度依次升高，较高的隔离级别包含较低的隔离级别的隔离功能
- 较低级别的隔离通常可以执行更高的并发，系统的开销也更低
四种隔离级别
1. READ UNCOMMITTED（RU，未提交读）
- 在该隔离级别，事务中的修改，即使没有提交，对其他事务也都是可见的
- 事务可以读取其他事务未提交的数据，也被称为脏读（Dirty Read）
- 该隔离级别可能会导致很多问题，但是性能却不比其他隔离级别好太多，因此除非真的有必要的理由，否则不使用该隔离级别
1. READ COMMITTED（RC，提交读/不可重复读）
- 在该隔离级别，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的
- 除了MySQL外，大多数数据库系统的默认隔离级别就是该级别
- 该级别也被称为不可重复读（nonrepeatable read），因为在同一个事务内执行两次同样的查询，仍然可能会得到不一样的结果，因为可能读到其他事务已经提交的修改
- 不可重复读和脏读的区别
  - 脏读是一个事务读取了另一个事务未提交的脏数据
  - 不可重复读则是一个事务读取了另一个事务提交的数据
1. REPEATABLE READ（RR，可重复读）
- 在该隔离级别，一个事务内部多次读取同样记录的结果是一致的（或是读取到自己的修改），不会受到其他事务的影响，无论其他事务是否提交
- 该隔离级别无法解决幻读（Phantom Read）问题，幻读指的是事务在读取某个范围内的记录时，有线程在该范围内插入了新的记录，之后事务再次读取该范围的记录时，会产生幻行（Phantom Row）
- 不可重复读和幻读的区别
  - 不可重复读的原因是其他线程对行的修改，事务多次读取一条记录会发现其中某些列的值被修改
  - 幻读的原因是其他线程对行新增或者删除，事务读取范围记录时会发现记录的数量增多或减少
- 可重复读是MySQL默认的事务隔离级别，InnoDB和XtraDB引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）实现这一隔离级别
1. SERIALIZABLE（可串行化）
- 该隔离级别是最高的隔离级别，其通过强制事务串行执行来避免幻读问题
- 具体来说，该隔离级别会在读取的每一行数据上加锁，所以可能导致大量的超时和锁争用问题
- 只有在非常需要确保数据一致性并且可以接收事务没有并发的情况下，才考虑采用该级别
隔离级别表 | 隔离级别 | 可能脏读 | 不可重复度 | 可能幻读 | 加锁读 | | —- | —- | —- | —- | —- | | READ UNCOMMITTED | √ | √ | √ | × | | READ COMMITTED | × | √ | √ | × | | REPEATABLE READ | × | × | √ | × | | SERIALIZABLE | × | × | × | √ |

4 多版本并发控制

多版本并发控制（Multiversion Concurrency Control，简称MVCC）概述
- MySQL的多数事务型引擎不但实现了简单的行级锁，基于提升并发性能的考虑，它们一般都同时实现了MVCC
- 可以认为MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低
- MVCC是一种并发控制的方法，其没有统一的实现机制
- MVCC在RR和RC两个隔离级别下工作
MVCC的实现
- MVCC是通过保存数据在某个时间点的快照来实现的
- 具体来说，InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间，这里存储的实际上不是实际的时间值，而是系统版本号
- 每开始一个新事务，系统版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较

MVCC在RR隔离级别下的具体操作

SELECT

InnoDB只查找行的系统版本号小于或等于事务系统版本号的数据行，这样可以确保事务读取到的行，要么是在事务开始前就已经存在的，要么是事务自身插入或修改过的

INSERT

InnoDB为新插入的每一行保存当前系统版本号作为行版本号

DELETE

InnoDB为删除的每一行保存当前系统版本号作为行删除表示

UPDATE

InnoDB为插入一行新纪录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标志

解决幻读

InnoDB通过MVCC配合间隙锁（next-key locking）策略来防止幻读的出现

3 事务日志

事务日志概述
- 事务日志可以提高事务的效率
- 目前大多数存储引擎都使用事务日志
事务日志的实现过程
- 使用事务日志，存储引擎在修改表的数据时只需要修改数据的内存拷贝，再把该修改行为记录记录到持久在硬盘上的事务日志中，这样就不用每次都将修改的数据本身持久到硬盘
- 事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头
- 事务日志持久以后，内存中被修改的数据在后台可以慢慢地刷回到磁盘
- 使用事务日志后，修改数据需要写两次磁盘
- 如果数据的修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，此时系统崩溃，存储引擎在重启时能够通过日志自动恢复这部分修改的数据

4 事务中混用存储引擎

MySQK服务器层不管理事务，事务是由存储引擎实现的，因此在同一个事务中使用多种存储引擎是不可靠的

事务中混用存储引擎的弊端
- 在事务中混合使用了事务型的表和非事务型的表（如InnoDB和MyISAM表），在正常提交的情况下不会有什么问题
- 如果混用存储引擎的事务需要回滚，则非事务型的表上的变更就无法撤销，这会导致数据库处于不一致的状态，事务的最终结果将无法确定
- 注意，在非事务型的表上执行事务相关操作时，MySQL不会发出提醒也不会报错

有时可能会在回滚时发出一个警告

4 InnoDB和MyISAM对比

默认存储引擎

InnoDB是现在MySQL的默认存储引擎
MyISAM是MySQL5.1及之前版本的默认存储引擎

支持的功能

InnoDB
- 支持事务
- 支持外键
- 支持崩溃后自动恢复
MyISAM
- 支持全文索引

支持的锁

InnoDB支持表锁和行级锁，还支持MVCC
MyISAM仅支持表锁

性能

大部分情况下InnoDB快于MyISAM

4 索引

1 索引概述

索引概述
- MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。
- 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
- 索引对于良好的性能非常关键。尤其是当表中的数据量越来越大时，索引对性能的影响愈发重要
- 索引是在存储引擎层面实现的
索引的工作原理
- 索引的工作原理类似于Map结构的工作原理，即给定key来快速找到value

在数据库中就是给定列值，通过索引快速找到包含该值的行

索引的键
- 索引可以建立在一列或者多列上，因此索引的键值可能由多个列的值组成
- 如果索引包含多个列，那么列的顺序十分重要，因为MySQL只能高效地使用索引的最左前缀列
实例
```
SELECT first_name
FROM actor
WHERE id = 5
```
对于上述查找，如果在id列上建有索引，则MySQL会利用该索引快速找到id为5的行

索引的示意图

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。
正常查询数据表中第二列（Col2）的数据需要O(N)的时间复杂度，即全表扫描

为了加快的查找，可以维护一个右边所示的二叉查找树（又称为二叉排序树），每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以利用二叉查找树快速获取到相应数据，此时时间复杂度为O(logN)

索引的存储位置

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。

索引的优势与劣势

优势

索引可以提高数据检索的效率

劣势

实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的。
虽然索引大大提高了查询效率，同时却也降低了更新表（INSERT、UPDATE、DELETE）的速度。

因为更新表时，MySQL不仅要更新数据，还要更新索引文件

2 索引的数据结构

MySQL支持以下4种数据结构的索引
1. B-Tree索引
- 平常所说的索引，如果没有特别指明都是指B-Tree索引
- 数据库中聚集索引、复合索引、前缀索引、唯一索引默认都是使用B-Tree索引
- B-Tree在不同的存储引擎上有各种变种实现，但这些实现仍然称为B-Tree
1. Hash索引
2. R-Tree索引
3. Full-Text索引
索引与数据库引擎
- 索引是在MySQL的存储引擎层中实现的，所以每种存储引擎支持的索引并不完全相同
- MyISAM、InnoDB、Memory三种存储引擎对各种索引类型支持表如下 | 索引 | InnoDB引擎 | MyISAM引擎 | Memory引擎 | | —- | —- | —- | —- | | B-Tree | √ | √ | √ | | Hash | × | × | √ | | R-Tree | × | √ | × | | Full-Text | √（5.6版本） | √ | × |

1 B-Tree索引

1 B-树

B-Tree（又称多路平衡搜索树）特性

一棵m叉B-Tree的特性如下

树中每个节点最多包含m个孩子
除根节点外，每个节点至少有ceil(m/2)个孩子
若根节点不是叶子节点，则至少有两个孩子
所有的叶子节点都在同一层
每个非根节点由n个key与n+1个指针（n+1度）组成，其中ceil(m/2) - 1 <= n <= m - 1

每个key对应两个指针，分别表示key的左子树和右子树

整个B-Tree中的key始终是有序的，即每个节点中的key都按照从小到大的顺序排列，每个key的左子树中的所有key都小于它，而右子树中的所有key都大于它

4叉B-Tree示意图

9a8a3f11fdbd45e4f7ef1d6de617d975_755840-20200609185347572-729860950.png

B-Tree的每个结点中存储了关键字（key）和关键字对应的数据（data）以及孩子结点的指针。
我们将一个key和其对应的data称为一个记录，但是对于B-Tree来说通常用key来代替键值对

B-Tree插入规则
- 在叶子节点中插入key
- 如果节点的关键字个数没有到达m-1个，那么直接插入即可
- 如果节点的关键字个数已经到达了m-1个，那么插入key后B树的性质显然无法满足，此时需要将该节点进行分裂。

分裂的规则是该节点分成两半（这也是为什么除根节点外其他节点key的数量大于等于ceil(m/2)- 1），将中间的关键字进行提升，加入到父节点中，但是这又可能存在父节点也满员的情况，则不得不向上进行回溯，甚至是要对根结点进行分裂，导致整棵树都加了一层

实例

有一颗5叉B-Tree，其非叶节点的key的数量经过公式（ceil(5/2) - 1 <= n <= 5 - 1）推导为2 <= n <= 4
现以向B树中插入 C N G A H E K Q M F W L T Z D P R X Y S 数据为例分析B树的构建过程（这些字母在B-Tree中按字典序排列）

向根节点插入前四个字母C、N、G、A，此时根节点有4个key和5个指针

向根节点插入H，此时根节点key数量大于4，中间元素G提升到“父节点”，两边的key分裂到两个节点

继续插入E、K、Q，其中E小于G，插入G的左节点，K和Q大于G，插入G的右节点

插入M，M大于G，插入G的右节点，此时右节点key的数量大于4，再次进行分裂，将M提升到父节点

插入F、W、L、T，不需要分裂

插入Z，中间元素T向上提升到父节点中

插入D，中间元素D向上提升到父节点中。然后插入P、R、X、Y不需要分裂

最后插入S，S应该插入T的左节点（M的右节点），而插入后该节点key数量大于4，中间元素Q向上提升到父节点，此时父节点的key数量大于4，再次进行分裂，将中间元素M向上提升

2 B+树

B+Tree为B-Tree的变种，B+Tree的特性为
1. n叉B+Tree最多含有n个key，而BTree最多含有n-1个key

n叉B+Tree和BTree最多都包含n个指针，对于B+Tree来说一个key对应一个指针，而B-Tree的一个key对应两个指针

B+Tree所有的非叶子节点都可以看作是key的索引部分，实际的键值对都保存在叶子节点中
- 因此也将B+Tree的节点分为两类，分别是索引节点（非叶子节点）和叶子节点
- 索引节点中用于索引的键可以重复出现，且一定存在于对应的叶子节点中
- B+Tree的度也限制了叶子节点可以存储的键值对数量
B+Tree与B-Tree最大的不同是B+Tree内部节点不保存数据只用于索引，所有数据都保存在叶子节点中
B+Tree所有节点内部的key按key的大小排列
B+数的叶子节点有顺序访问的指针，也就是每个叶子节点均有一个指向相邻叶子节点的指针，并且当前叶子节点的key均小于下一个叶子节点的key
B+Tree的每次查询都要查询到叶子节点，而B-Tree不需要

3叉B+Tree示意图

MySQL使用的B-Tree索引的原因
- 由于索引通常很大，因此往往需要存放在硬盘中，而减少索引的查询次数也就减少了磁盘的I/O次数，这也是索引数据结构所追求的目标
- 对于B-Tree来说，其最大的特点就是“矮胖”，即每层节点数目非常多而层数非常少，这也就加快了检索的速度，减少了磁盘I/O次数
- 使用B-Tree而不是二叉排序树（如红黑树）的原因
  - 在大规模数据存储的时候，红黑树往往出现由于树的深度过大而造成磁盘IO读写过于频繁，进而导致效率低下的情况。
  - 为什么会出现这样的情况，我们知道要获取磁盘上数据，必须先通过磁盘移动臂移动到数据所在的柱面，然后找到指定盘面，接着旋转盘面找到数据所在的磁道，最后对数据进行读写。磁盘IO代价主要花费在查找所需的柱面上，树的深度过大会造成磁盘IO频繁读写。
  - 根据磁盘查找存取的次数往往由树的高度所决定，所以，只要我们通过某种较好的树结构减少树的结构尽量减少树的高度，B树可以有多个子女，从几十到上千，可以降低树的高度。
MySQL使用B+Tree索引的原因

MySQL中使用的实际上是B+Tree，B+Tree相比于B-Tree有以下优点

磁盘与内存的交互是以页为单位交互的，因此在构建B-Tree时尽量让每个节点的大小等于一个页的大小，这样读取一个节点只需要一次I/O操作。而B-Tree的每个节点都有data域，这无疑增加了节点的大小。而对于B+Tree来说，除了叶子节点外，所有节点只存储索引信息，因此一个节点可以存储更多索引信息，从而降低了树的高度，减少了磁盘I/O操作
B+Tree的所有数据都存储在叶子节点，因此可以为叶子节点添加顺序指针，这样可以方便快速地进行范围查找（一次查询一定范围内的多个数据）
B+Tree的查询效率更稳定

由于B+Tree的非叶节点并不是最终指向文件内容的节点，而只是叶子节点中关键字的索引。所以任何关键字的查找必须走一条从根节点到叶子节点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

3 可以使用B-Tree索引的查询类型

由于B-Tree索引的键值直接由索引列的值组成，因此B-Tree只适用于下述类型的查找

现假设在姓名、性别、出生年月三个列上建立B-Tree索引

全值匹配

全值匹配指的是查找条件和索引中的所有列匹配
如查找条件为查找姓名为Mike、性别男、出生与1997-7-15的人

匹配最左前缀

可以只匹配索引左侧的第一列或数列
如查找姓名为Mike的人或姓名为Mike、性别男的人

匹配列前缀

可以只匹配某一列的值的开头部分
如匹配姓崔的人，即匹配姓名列的开头部分

匹配范围值

B-Tree索引也可以用于范围查找
如查找姓名在Allen和Barry之间的人

精确匹配某些列并范围匹配另外一列

前面的列可以精确匹配，后面的列可以范围匹配

只访问索引的查询

B-Tree支持只访问索引而无需访问数据行的查询，因为索引中包含字段值

B-Tree索引不适用的查询类型