Mysql 梳理

Mysql 是一种被普遍使用的开源关系型数据库。下面是对 Mysql 一些知识点的整理。

存储引擎

引擎（Engine）是机器发动机的核心，而数据库存储引擎便是数据库的底层软件组织。数据库使用数据存储引擎实现存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，MySql 的核心就是插件式存储引擎，可以支持多种不同的数据引擎。目前常见的存储引擎及对比如下：

以下是查看 Mysql 数据库存储引擎的几个常用命令。

mysql> show engines; # 查看 MySQL 提供的所有存储引擎

mysql> show variables like '%storage_engine%'; # 查看 MySQL 当前默认的存储引擎

mysql> show table status like "table_name"; # 查看表的存储引擎

MyISAM vs InnoDB

这是最常用也是总放在一起对比的两个存储引擎，上面的图标也基本列出了两者的差异。MyISAM 是 v5.5 之前 MySQL 的默认数据库引擎，而 InnoDB 则是后续版本的默认引擎，也就是所谓的事务性数据库引擎。两者的区别大概总结一下：

是否支持行级锁 ，MyISAM 只有表级锁 (table-level locking)，而 InnoDB 支持行级锁 (row-level locking) 和表级锁，默认为行级锁。
是否支持事务 ，MyISAM 强调的是性能，但是不提供事务支持。InnoDB 提供事务支持、外键等高级数据库功能。具有事务 (commit)、回滚 (rollback) 和崩溃修复能力 (crash recovery capabilities) 的事务安全 (transaction-safe (ACID compliant)) 型表。
是否支持外键 ，MyISAM 不支持，InnoDB 支持。
是否支持 MVCC，仅 InnoDB 支持。应对高并发事务, MVCC 比单纯的加锁更高效，MVCC 只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作；MVCC 可以使用乐观 (optimistic) 锁和悲观 (pessimistic) 锁来实现；各数据库中 MVCC 实现并不统一。

至于两个存储引擎怎么选择的问题，闭着眼睛用 InnoDB 就完事了，不需要纠结。

MVCC(Multiversion Concurrency Control)

多版本控制: 指的是一种提高并发的技术。最早的数据库系统，只有读读之间可以并发，读写、写读、写写都要阻塞。引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了 InnoDB 的并发度。在内部实现中，与 Postgres 在数据行上实现多版本不同，InnoDB 是在 undolog 中实现的，通过 undolog 可以找回数据的历史版本。找回的数据历史版本可以提供给用户读 (按照隔离级别的定义，有些读请求只能看到比较老的数据版本)，也可以在回滚的时候覆盖数据页上的数据。在 InnoDB 内部中，会记录一个全局的活跃读写事务数组，其主要用来判断事务的可见性。

可以认为 MVCC 是行级锁的一个变种, 但是它在很多情况下避免了加锁操作, 因此开销更低。虽然实现机制有所不同, 但大都实现了非阻塞的读操作，写操作也只锁定必要的行。
MVCC 可以使用乐观 (optimistic) 锁和悲观 (pessimistic) 锁来实现;
应对高并发事务, MVCC 比单纯的加锁更高效;
MVCC 只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作;
InnoDB 的 MVCC 是通过在每行记录后面保存隐藏的列来实现的

将会在事务的章节中详细讲解。

索引

从数据结构角度讲，MySQL 索引主要有 B+Tree 索引和哈希索引。

哈希索引 ，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；
B+Tree 索引，大部分场景建议选择 B+Tree 索引。

MySQL 的 B+Tree 索引还可以分为聚集索引和非聚集索引。

聚集索引（聚簇索引 / clustered index），表数据按照索引的顺序来存储的，也就是说索引项的顺序与表中记录的物理顺序一致。对于聚集索引，叶子结点即存储了真实的数据行，不再有另外单独的数据页。在一张表上最多只能创建一个聚集索引，因为真实数据的物理顺序只能有一种。
- 每个 InnoDB 表都有且仅有一个聚簇索引。如果你为表定义了一个主键，MySQL 将使用主键作为聚簇索引。如果你不为表指定一个主键，MySQL 将第一个组成列都 not null 的唯一索引作为聚簇索引。如果 InnoBD 表没有主键且没有适合的唯一索引，将自动创建一个隐藏的名字为 “GEN_CLUST_INDEX” 的聚簇索引。
- 聚簇索引是物理索引，数据表就是按索引顺序存储的，物理上是连续的。
- 主索引文件和数据文件为同一份文件
非聚集索引（非聚簇索引 / 辅助索引 / 二级索引 / secondary index），指聚簇索引之外的所有其它的索引。
- 非聚簇所以可以有多个，而且只能由用户自己添加，InnoDB 默认并不会创建任何非聚簇索引。
- 对于非聚簇索引存储来说，主键 B + 树在叶子节点存储指向真正数据行的指针，而非主键。

索引这块后面也将单独章节进行介绍，敬请期待。

事务

事务是逻辑上的一组操作，要么都执行，要么都不执行。下面来看看事物的四大特性 (ACID)：

原子性（Atomicity），事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
一致性（Consistency），执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
隔离性（Isolation），并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
持久性（Durability，一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该有任何影响。

并发事务带来的问题

多个事务并发运行，即多个用户对同一数据进行操作，则可能会导致以下的问题：

脏读（Dirty read）- 读取了未提交数据

一个事务访问数据并做了修改，但还没有提交到数据库中，另外一个事务此时可以读取这个未被提交的数据，读到的这个数据是 “脏数据”，因为这个数据还没提交有可能会被回滚，那后面依据 “脏数据” 所做的操作就可能是不正确的。

丢失修改（Lost to modify）- 多事务同时修改同一份数据

A 事务读取一个数据时，B 事务也访问了该数据，那么 A 事务中修改了这个数据后，B 事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。

不可重复读（Unrepeatableread）- 一个事务内重复读取某个数据得到不同的值

在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

幻读（Phantom read）- 一个事务内重复读取得到的记录数量不一致

幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复读和幻读区别：
不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了。

事务的隔离级别

为了针对具体的使用场景解决上面并发事务导致的一些问题，SQL 标准定义了四个事务隔离级别

READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。我们可以通过 SELECT @@tx_isolation; 命令来查看

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

需要注意的是 InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）已经可以保证事务的隔离性要求，即达到了 SQL 标准的 SERIALIZABLE(可串行化) 隔离级别。 这时因为它使用了 Next-Key Lock 锁算法可以避免幻读的产生。另外隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是 READ-COMMITTED(读取提交内容)，但 InnoDB 存储引擎默认使用 REPEAaTABLE-READ（可重读）并不会有任何性能损失。

一条 SQL 语句在 MySQL 中如何执行的

以查询为例

客户端发送一个查询给服务器
服务器先检查查询缓存，如果命中，则直接返回缓存中的结果。如果没有没有命中，则进入下一阶段（解析器）。
由解析器检查 sql 语法是否正确，然后由预处理器检查 sql 中的表和字段是否存在，最后由查询优器生成执行计划。 这一步很耗资源。
mysql 根据优化器生成的执行计划，调用存储引擎的 API 来执行查询。
将结果返回给客户端。

注意查询缓存从 8.0 开始已被废弃，官方建议把缓存放到客户端，低版本的使用中一般情况下也不需要开启。详细原因可以参考 MySQL 8.0: Retiring Support for the Query Cache

Mysql 知识点