本文主要是对 MySQL 知识点的一些总结和 FAQ ,笔者已经尽力在组织体系了,但很难做到严谨,权当个备忘吧。

1. MySQL 的架构体系

MySQL 主要分三层:客户端、服务端和存储引擎。看如下图

mysql_architecture

客户端:这部分负责的功能是向 MySQL Server 发送请求的,比如查询、更新以及其它命令。客户端具体的表现形式包括 MySQL 自带的控制台、各种可视化工具以及 JDBC 代码等等。

服务端:即 MySQL Server,每次我们使用 MySQL 的时候都需要启动这个进程。这部分包括许多组件:连接器、分析器、优化器、执行器和缓存。

  • 连接器:负责 Server 与客户端的连接,包括登录时的权限验证。可以用show processlist命令查看连接个数。
  • 分析器:对 SQL 进行词法分析和语法分析,并根据 SQL 生成抽象语法树(Abstract Syntax Tree. AST)
  • 优化器:对请求语句做一些优化,规定执行流程
  • 执行器:SQL 语句的真正执行组件
  • 缓存:这个组件其实在 MySQL8 以后已经移除了,主要原因是缓存命中率太低。

存储引擎:存储引擎本质其实是存储文件和组织文件的方式。不同的存储引擎的文件有不同的格式,比如 InnoDB 的数据文件是 .ibd 后缀的;MyISAM 的文件后缀是 .MYD 和 .MYI,分别对应数据和索引。除了我们最常提到的 InnoDB 和 MyISAM,还有很多其它存储引擎,并且存储引擎是以插件形式存在的,每个人都可以开发自己存储引擎。我们可以使用show engines命令来查看 MySQL 自带的存储引擎。

mysql> show engines;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

另外需要特别提到的是:存储引擎是表级别的设置,即我们可以对同一个库里面的表格设置不同的存储引擎。不同的存储引擎在事务索引以及方面都有很大区别,因此引擎的选择对于 SQL 的执行效率有很大影响。

2. MySQL 的事务

2.1 为什么需要事务?

专业一些的说法是:保证数据的一致性。不过这个解释太抽象了,还是用转账场景来举例比较浅显易懂。

在一次转账业务中,我们一共需要执行三个操作:

  1. A 账户查询余额,结果为 X 元
  2. A 账户转出金额 Y 元(Y \le X)
  3. B 账户转入金额 Y 元

如果系统在第二步之后中断了又没有进行任何补救的话,那 A 账户就会平白无故损失 Y 元,为了保证两个账户金额不产生问题,我们需要这个转账操作构成一个不可分割的操作集合,要么全部成功要么全部失败。

2.2 事务的实现原理

看上面的表格我们可以知道,其实支持事务的只有 InnoDB 一个存储引擎,因此这节中的讨论都是 InnoDB 相关的。

我们都知道事务有ACID四大特性。但实际上,持久性的含义就是原子性、隔离性和持久性的组合。因此,只要实现了原子性、隔离性和持久性,我们也就能做到一致性。

这里就需要提到三个概念:undo logredo log。

在 MySQL 中,有很多锁的概念,比如:读锁(共享锁)写锁(排他锁,独占锁)间隙锁意向锁

跟锁一样,MySQL 中也有很多日志概念:binlog慢查询日志undo/redo log等。这里面 binlog 和慢查询是属于 MySQL Server 的,而 undo/redo log 是属于 InnoDB 的,也是 InnoDB 实现事务的方式。

2.2.1 原子性的实现

原子性的含义是:事务中的操作,要么全部成功要么全部失败。全部成功的情况无须赘述,我们需要处理的就是有部分操作失败的情况。说到这里,很多同学就会立马想到——回滚(Rollback)。那么回滚操作又是如何实现的呢?

这里就用到了我们刚才提到的 undo log。undo log 的思路也非常简单,就是记录一下操作的反向操作。比如操作一条 Insert,undo log 记录一条 delete;操作一条 update,undo log 记录一条相反的 update。

如果事务中有操作失败了,那么就可以根据 undo log 去恢复出事务开始之前的数据状态。

2.2.3 隔离性的实现

隔离性的含义是:事务和事务之间相互独立,不会影响。如果只从简单概念上来看待隔离性,那其实我们只需要在被操作的数据上加排他锁就可以了,这样可以保证某个事务在执行过程中,不受其它事务的影响。

在实际数据库的实现中,这种粗暴的加锁方式使得效率变得很低。因此各个数据库都对隔离性的限制有所降低,也就出现了隔离级别的概念。具体隔离级别的内容下节会重点讲,这里先架空讨论一下隔离级别的实现。

在存在隔离级别的情况下,undo log 又出场了。我们提到过,undo log 是记录反向操作的一个日志。实际上,undo log 在记录操作的时候,同时记录了一个版本号,因此我们可以知道某个数据在这个事务中各个时间点的变化情况。这样,不同的隔离级别就可以读取到同一个数据的不同状态,这也被称为多版本并发控制(Multi-Version Concurrency Control, MVCC)

2.2.2 持久性的实现

持久性的含义是:数据的改变会写入到磁盘中。这点也很好理解,如果我们对数据的改变操作都没有写入磁盘的话,如果突然断电的话这些操作也就消失了。但现在有一个问题,在每次事务修改数据之后,如果立即将修改写入磁盘,在本身表内数据量很大的情况下,就会产生大量磁盘 IO,严重影响效率。

InnoDB 就采取了一个折衷的办法——redo log

redo log 的思路就是,在事务提交之前,将此次事务的修改先写入磁盘,相比于直接修改数据文件,这种写入成本很低。如果事务提交之后发生了断电,就可以根据 redo log 中的内容,将丢失的数据重新写入磁盘。这个操作也称之为前滚(rollforward)。

2.2.3 日志写入磁盘的过程

undo/redo log 写入磁盘过程可以分为三个阶段:

  1. 写入 log buffer
  2. 写入 os buffer
  3. 写入磁盘

如下图表示:

log_write_to_disk

这里面 log buffer 和 os buffer 是在内存之中,如果断电则数据会丢失。另外,os buffer 写入磁盘的频率大约为 1s 一次,或者显式调用 fsync()也可以触发写入磁盘。MySQL 提供了innodb_flush_log_at_trx_commit来控制这个过程,我们看下面图片:

innodb_flush_log_at_trx_commit

如图所示,innodb_flush_log_at_trx_commit 的值有三个:0、1、2,分别代表 commit 后 log 写入的状态。其中,1 选项代表日志会直接刷入磁盘,安全性最高,效率最低,也是 InnoDB 的默认配置。0 和 2 效率上差别不太大,区别在于 log buffer 只存在于 innodb 中,因此停掉 MySQL Server 的话,这部分数据会消失,因此 2 有更好的安全性。

2.2.4 redo log 是物理日志 undo log 是逻辑日志

首先我们需要知道一个知识点:MySQL 读取硬盘数据到内存时,是以为单位的。这里先不展开页的概念,可以简单理解为一块连续的数据,大小一般为 16k。

redo log 的操作其实就是记录了某个页的最终数据状态,在恢复数据时,将这个页的内容直接覆盖到磁盘上,就完成了恢复。因此称 redo log 为物理日志。

另一方面,undo log 记录的是某行数据的反向操作,在恢复数据时,是将记录好的操作一条条执行,最终完成恢复。因此称 undo log 为逻辑日志。

2.3 事务的隔离级别

数据库基于效率原因,放宽了隔离性的要求,因此产生了隔离级别。相应地,不同的隔离级别会导致不同的数据安全性问题。具体如下表格:

隔离级别 脏读 不可重复读 幻读
读未提交 Read Uncommitted
读已提交 Read Commited ×
可重复读 Repeatable Read × ×
串行化 Serializable × × ×

下面逐个解释一下这些问题的具体意义。

  • 脏读:某个事务读取到了其它事务未提交的数据,未提交的数据也叫脏数据。
  • 不可重复读:某个事务先查询了某条数据,另一个事务对这条数据做了修改并提交,原事务再次查询这条数据的时候,发现数据变化了。
  • 幻读:某事务查询多条数据,另一个事务插入了一条新数据并提交,原事务再查询,没有发现新插入的数据(可重复读),但执行同样的插入时却会受到影响(比如主键重复)。

2.3 MySQL 中的锁

前一节中提到,MySQL 中有很多锁的概念,但是粗略一点来看,读锁写锁是最关键的两种。不同的存储引擎也有不同的锁类型。

2.3.1 MyISAM 的锁

在 MyISAM 中,读操作会给表格加上读锁,这个锁不会阻塞其它获取读锁的操作,但会阻塞获取写锁的。另外,MyISAM 写锁也被称为独占锁,锁的范围也是整个表格,因此 MyISAM 的写入操作效率很低适合大量读少量写的场景。

2.3.2 InnoDB 的锁

在 InnoDB 中,读锁被称为共享锁,写锁被称为排他锁,同时都支持行级锁和表级锁。同样地,读锁不会阻塞读锁,但阻塞写锁,而写锁会阻塞所有其它锁,排他的意义就在于此。

默认情况下,写操作会加排他锁,而读操作不加锁,这也被称为一致性非锁定读

相应地,读操作也可以手动加上共享锁或排他锁。方式如下:

  • 加共享锁:SELECT * FROM tbl LOCK IN SHARE MODE;
  • 加排他锁:SELECT * FROM tbl FOR UPDATE;

给读操作加锁的方式,也称作一致性锁定读

InnoDB 加锁的对象是索引,因此,如果定位数据时不使用索引条件查询,则 InnoDB 会加表锁,如果使用索引查询,则加的是行锁

2.3.3 间隙锁

间隙锁是 InnoDB 在可重复读隔离级别下,防止幻读的一种手段。它加锁的数据是一些“不存在的”行。

例如我们有一个 test 表格,其中 age 为普通索引,下面为样例数据:

id age
5 5
10 10
15 15

如果我们执行:

步骤 Session A Session B
1 BEGIN;
2 SELECT * FROM test WHERE age = 11 FOR UPDATE;
3 INSERT INTO test VALUES (12, 12); # 会阻塞
4 COMMIT;

这里 InnoDB 其实是对 5 到 10 这中间的间隙加了锁。

这里只是介绍了间隙锁最简单的一种情况,具体还有很多细节(诸如开闭区间与范围查询),本文暂不做展开了。

2.3.4 意向锁

意向锁的含义比较简单,即我们对某行数据加锁的时候,会先对相应的表格申请一个意向锁,如果这个意向都无法达成的话(表明有其它事务占用该表),那我们后面的行锁也是一定加不上的,这样来提高一些加锁效率。

3. MySQL 索引

3.1 索引的数据结构

绝大部分 MySQL 的索引使用 B 树(B+ 树)来存储,空间类型索引使用 R 树,Memory 存储引擎使用 Hash 表,InnnoDB 的全文索引使用倒排索引。因此这里我们主要把握一下 B+ 树的情况。

那么为什么 MySQL 要选用 B+ 树来存储索引呢?我们从树本身这种数据结构开始讨论。

首先树里面我们最常见和熟悉的,应该是二叉树了,这种树每个节点最多有两个子树。如果我们人为对节点做一个排序规定的话,我们就得到了二叉搜索树。这样我们可以利用二分查找来确定元素,效率得到了提升。但是这种数据结构有个很大的问题,即如果数据的值只往一个方向增加的话,那我们的二叉搜索树就会只往一个方向增加节点,这时候树就退化成了链表,失去了二分查找的特性。

基于上面的问题,人类又发明了AVL 树,即二叉平衡树。这种树会在节点插入的时候,进行一些左旋或者右旋调整,以保证整个树的最大子树深度和最小子树深度之差不超过 1,即尽量保持平衡。这就解决了二叉搜索树的问题。但是这样虽然可以保证查询的效率不会下降,但大量的左旋右旋操作,使得插入效率又变得低下。

于是人们又发明了红黑树。红黑树也是平衡树,但并不严格要求深度差为 1,这样似乎兼顾了插入和查找的效率。但实际上,随着数据量的增大,红黑树的平衡程度仍然达不到要求,依然会发生倾斜的现象。

究其根本,是因为我们只基于二叉树来存储数据,一个 n 层深的二叉树,最多也就存储 2^n - 1 个数据。因此我们需要放宽子树数量的限制,于是我们有了B 树。B 树是一种多叉平衡树,并且一个节点内可以存储多个数据(由 B 树的度来决定),并且所有叶子节点的深度都是一样的。到此,我们几乎找到了适合存储 MySQL 数据的结构。

但是人们还并不满足于此。B 树的节点会存储数据和对应的索引,MySQL 会从磁盘读取一个节点(在 MySQL 中,这个节点就对应这个概念)的数据到内存中,由于 IO 的性能的限制,一次可以读取的数据量并不大,一个三层的 B 树,要经过三次 IO 的读取,大概只能找到几千条数据。于是人们对 B 树又进行了改造,既然节点的大小有限,那么就只存储索引,把数据都挪到叶子节点。这样一来,内部节点的数据范围就可以扩大百倍,三层的 B 树,可以容纳的数据量就变为了原来的万倍,到达了千万级的数据量。这就是B+ 树

当然 B+ 树还有一些其它的优化。B+ 树把所有叶子节点之间也做了一个链接,形成了一个链表。这样 B+ 树不仅可以从根节点进行随机查找,还可以通过链表进行范围查找。

总结一下,B 树和 B+ 树的区别在于:

  1. B 树的节点会存储数据,B+ 树只存储索引,因此 B+ 树可以容纳更多数据。
  2. B+ 树叶子节点间有链表结构,增加了一种查找方式。

最终,MySQL 使用了 B+ 树来存储索引数据。

3.2 索引的分类

MySQL 中,索引有如下几类:主键索引普通索引唯一索引全文索引组合索引

3.2.1 主键索引

主键索引是 MySQL 组织数据的最基本方式。如果表格没有主键,则使用唯一键,如果还没有唯一键,MySQL 会生成一个 6 字节的 rowid 来作为主键。

3.2.2 普通索引

普通索引在 MySQL 中的组织形式也是 B+ 树,只不过叶子节点上存储的并不是数据本身,而是数据的主键。然后,再通过主键索引去查到相应的数据。这个过程也叫回表

但如果我们的想查询的字段就是主键本身的时候,就不需要进行回表查询,这个现象也叫覆盖索引。所以我们常说不要用*来代替被查询字段,就有这部分原因在内。

3.2.3 组合索引

组合索引其实就是对多个字段进行索引,其它部分与单字段的索引无异。但是组合索引有个很重要的概念叫做最左匹配原则。即组合索引的内的字段是有先后顺序的,MySQL 查询的时候会先从最左边开始匹配索引,如果匹配不上就会放弃使用索引。

举个例子来说,我们的 user 表格有如下四个字段:

id name age gender

我们对 name 和 age 做了组合索引(name, age)。

那么如下四个 SQL,哪个可以使用索引呢?

1. SELECT * FROM user WHERE name = 'gaga'
2. SELECT * FROM user WHERE age = 1
3. SELECT * FROM user WHERE name = 'gaga' AND age = 1
4. SELECT * FROM user WHERE age = 1 AND name = 'gaga'

1 和 3 显然是可以使用索引的,2 显然不能使用索引。比较特别的是 4,事实上,4 经过前面提到的SQL 优化器的调整,也是可以使用索引的。所以答案是 1、3 和 4。

另外,在组合索引中,还有一个概念叫索引下推。在 MySQL 5.6 以前,组合索引的查询方式为,先按 name 查出所有数据,再到 Server 层来筛选 age。而从 5.6 开始,在第一次筛选数据的时候,就会使用 name 和 age 两个字段,这样可以直接拉取更少的数据。

4. MySQL 的性能监控

既然要进行优化,首先我们就需要知道效率的瓶颈在哪里。MySQL 中有一些工具可以帮助我们了解 SQL 运行的执行效率。

4.1 执行计划

执行计划是对语句使用的,它可以模拟出执行语句的过程。使用方式是在语句之前加上 EXPLAIN 关键字。

EXPLAIN SELECT * FROM test;

返回结果如下:

+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | index | NULL          | idx  | 5       | NULL |    6 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

这里面最主要关注的一栏就是type。type 有很多可能的值,其中效率最差的是ALL值,我们在写 SQL 的时候一定要避免这个。

4.2 profiling

profiling 也是语句层面的,它可以给出语句执行时各个阶段所花费的时间。

首先我们需要开启 profiling

SET profiling = 1;

然后,在执行完查询语句后,执行如下命令:

SHOW profile for query 1;

其中的 1 表示语句的编号,也就是执行计划中的 id 值。得到输出如下:

+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000066 |
| Executing hook on transaction  | 0.000011 |
| starting                       | 0.000010 |
| checking permissions           | 0.000010 |
| Opening tables                 | 0.000029 |
| init                           | 0.000010 |
| System lock                    | 0.000011 |
| optimizing                     | 0.000007 |
| statistics                     | 0.000016 |
| preparing                      | 0.000012 |
| executing                      | 0.000005 |
| Sending data                   | 0.000055 |
| end                            | 0.000006 |
| query end                      | 0.000006 |
| waiting for handler commit     | 0.000010 |
| closing tables                 | 0.000008 |
| freeing items                  | 0.000012 |
| cleaning up                    | 0.000015 |
+--------------------------------+----------+
18 rows in set, 1 warning (0.00 sec)

我们就可以看到各个阶段语句执行所花费的时间,然后再针对进行优化。

不过官网上已经不推荐使用 profile 来进行监控,并表示会在将来移除这个命令。更推荐的是 performance_schema 库来进行查看。

4.3 performance_schema

performance_schema 是 MySQL 的一个默认库,里面有 80+ 张表格。这些表格从各种角度对 MySQL Server 的运行情况做了记录,从这些记录中我们就可以找到 Server 运行的瓶颈,并进行针对性优化。

5. 写在最后

MySQL 作为发展多年的数据库,内容实在是太庞杂,这上面每一点其实都可以单写一篇。说实在的这篇博文个人感觉完成得并不好,有很多必要的内容并没有在文章中体现,加上之后又怕篇幅过长难以阅读,或许将来会将这篇拆分为系列文章吧。另外感谢 mashibing.com 的视频教学与各种大神博客,给本文提供了素材,学到了很多 👍 。