MySQL 存储引擎、事务、多版本并发控制（MVCC）、数据类型

MySQL 并不完美，但足够灵活适应高要求的环境，为了充分发挥其性能并顺利地利用，就必须理解其设计。

概念

MySQL 最重要最与众不同的特性就是它的存储引擎架构，这种架构将查询处理（Query Processing）及其他系统任务（Server Task）和数据的存储/提取相分离。MySQL 逻辑架构如下：

图片摘自知乎here

Server 层是 MySQL 的核心功能区，包括查询的解析、分析、优化、缓存以及所有的内置函数。解析查询会创建对应的解析树，然后对其各种优化，包括重写查询，决定表的读取顺序，以及选择合适的索引等。

存储引擎层负责数据的存储与提取：MySQL 的存储引擎 API 工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓中将编码过的列转换成行数据结构的操作代价是非常高的。

1.1 并发控制

无论何时，只要有多个查询需要同一时刻修改数据就会产生并发问题，MySQL 有两个层面的并发控制：服务器层和存储引擎层。途径都是通过锁来实现。

读写锁：也称为共享锁和排他锁。

锁粒度：让锁定对象更有选择性是提高共享资源并发性的有效方式。尽量只锁需要修改的部分数据片。任何时候，在给定的资源上，锁定的数据量越少，则系统的并发程度越高，但加锁也带来了资源消耗。所以出现了锁策略，目的是在锁开销和数据安全性之间寻求平衡。一般都是在表上施加行级锁（row-level lock），并以各种复杂方式来实现。

MySQL 提供了多种选择，每种 MySQL 存储引擎都可以实现自己的锁策略和锁粒度。将锁粒度固定到某个级别，可以为某些特定场景提供更好的性能，但也会失去另外一些场景的良好支持。MySQL 有两种锁策略：①表锁，最基本的锁策略也是开销最小的锁策略，是 MySQL 自己的锁策略。当一个用户对表进行写操作时，需要先获得写锁，这会阻塞其他用户对该表的所有读写操作，只有没有写锁时，其他读取的用户才能获得读锁，读锁之间是相互阻塞的。特定场景中，表锁也有良好性能。MySQL 也会强制某些操作使用表锁，如服务器会为 ALTER TABLE 之类的语句使用表锁，而忽略存储引擎的锁机制。②行级锁，可以最大程度的支持并发也带来了最大的锁开销。InnoDB 和 XtraDB 等实现了行级锁（行级锁只在存储引擎中实现，MySQL 服务器层没有实现，且服务器层完全不了解存储引擎中的锁实现）。

1.2 事务

一组原子性的 SQL 查询。符合 ACID 原则，A（Actomicity）要么成功要么失败、C（Consistency）从一致状态到另一个一致状态、I（Isolation）一个事务提交前对其他事务不可见和 D（Durability）一旦提交永久保存。

根据事务分为四种隔离级别：

Read Uncommitted（未提交读）：即使没有提交，对其他事务来说也是可见的，这就是脏读。
Read Committed(提交读)：大部分数据库使用，但 MySQL 不是。一个事务提交后，其他事务才可见，两次查询可能导致不一样。也叫不可重复读。
Repeatable Read（可重复读）：这个最厉害的就是做了数据快照，可以使同一事务多次读取的结果是一致的。没有解决幻读问题：指当某个事务在读取范围内的记录时，会发生幻行。InnoDB 和 XtraDB 通过多版本并发控制（MVCC）解决了幻读。所以 MySQL 使用的是这一级别。
Serializable（可串行化）：最高隔离级别，强制事务串行，避免了幻读。

MySQL 对于死锁的处理：死锁是多线程运行时常见问题，很多系统针对死锁实现了死锁检测和死锁超时机制。①越复杂的系统如 InnoDB 越能检测到死锁的循环，并立即返回一个错误。②查询的时间达到锁等待超时后主动放弃锁请求。InnoDB 采取的方式是：将持有最少行级排他锁的事务进行回滚。

事务日志：事务日志可以帮助提高事务的效率，使用事务日志（redo log），存储引擎在修改表的数据时只需修改其内存拷贝，之后再把该修改持久化道硬盘上的事务中（后追加）。事务日志持久后，内存中的被修改的数据在后台可以慢慢刷回到硬盘，所以修改数据需要写两次硬盘（事务日志和数据）。

undo log：记录数据被修改之前的日志，用于回滚。

1.3 多版本并发控制（MVCC）

大多数事务型存储引擎都实现了 MVCC，而不是单纯地使用行级锁。也可以认为 MVCC 是行级锁的一个变种，但它在很多情况下避免了加锁操作，因此开销很低，且大都实现了非阻塞的读操作，写操 作也只锁定必要行。MVCC 只在 REPEATABLE 和 READ COMMITTED 两个隔离级别下工作。

MVCC：通过保存数据在某个时间节点的快照来实现的。也就是说，不管执行多长时间，每个事务看到的数据始终都是一致的（解决了脏读）。不同存储引擎的 MVCC 是不同的，分为：乐观（Optimistic）并发控制和悲观（Pessimistic）并发控制。

InnoDB 的 MVCC 是通过在每行记录后保存两个隐藏的列来实现的。一列保存了行的创建时间，一个保存行的过期时间（或删除时间），这个时间是系统版本号，每开始一个新的事务，系统版本号就会自动递增。

SELECT：InnoDB 只查找版本早于当前事务版本中的数据行。行的删除版本要么未定义，要么大于当前事务版本号，这样可以确保事务开始前未被删除。
INSERT：将当前版本号作为行版本号。
DELETE：将删除的每一行保存当前版本号作为删除标识。
UPDATE：新插入一条记录，保存当前版本号，同时保存当前系统版本号到之前行作为删除标识。

1.4 MySQL 的存储引擎

在文件系统中，MySQL 将每个数据库保存为数据目录下的一个子目录，创建表时，会在子目录下创建与表同名的 frm 文件。

InnoDB 时 MySQL 的默认事务型引擎，它被用来处理大量的（短期）事务，很少会滚，效率很高。InnoDB 特性：性能很好，自动奔溃恢复特性使得它在非事务型存储的需求中也很流行。

InnoDB 采用 MVCC 来支持高并发，并且实现了隔离级别，并通过**间隙锁（next-key locking）**策略防止幻读的出现。间隙锁使得 InnoDB 不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入（GAP LOCK + RECORD LOCK行记录锁）。InnoDB 间隙锁保证了高并发的安全：https://www.cnblogs.com/rainwang/p/5073068.html

InnoDB 的索引结构和 MySQL 其他引擎有很大的不同，聚簇索引（也就是主键索引）结构对主键查询有很高的性能，不过它（InnoDB）的二级索引（非主键索引）中必须包含主键列，所以如果主键列很大的话，其他的所有索引都会很大。

MySQL 的索引有B+树、哈希等，但是 InnoDB 引擎使用的是 B+ 树索引模型。InnoDB 中的索引有主键索引（PRIMARY）、普通索引（index）、唯一索引（UNIQUE）和组合索引。这些索引划分为两类：主键索引也被称为聚簇索引，其他的都叫非主键索引也称为二级索引。

主键索引的叶子结点包含了整行数据，而二级索引的叶子节点存储的是主键索引的值。所以，二级索引需要二次查找。

MySQL 索引最左前缀原则：带头大哥不能死，中间兄弟不能断。具体查看mysql 的覆盖索引

InnoDB 内部做了很多优化，包括从磁盘读取数据时采用的可预测性预读，能够自动在内存中创建 hash 索引加速读操作的自适应哈希索引，以及能加速插入操作的插入缓冲区（Insert Buffer）等。

1.5 Schema 与数据类型优化

这一章关注的 MySQL 数据库的设计，主要介绍 MySQL 数据库设计与其他关系型数据库管理系统的区别。

选择正确的数据类型对于获得高性能至关重要。以下是几个原则：

- 更小的通常更好，它们占用更少的磁盘、内存和 CPU 缓存，且CPU 处理周期也少。
- 简单就好，整数比字符串操作代价更低
- 尽量避免 NULL 

VARCHAR 和 CHAR 的区别和使用，略

日期与时间：DATETIME：保存大范围的值，从 1001-9999 年，精度秒，与时区无关，8字节存储。TIMESTAMP（时间戳）：保存从 1970.01.01-2038，精度秒，与时区有关，4 字节存储。默认 NOT NULL 且空间效率更高。