MySQL高级学习（五）-MySQL学习补充

一、MySQL 的日志

在任何一款数据库中，都会有各种各样的日志，记录着数据库工作的方方面面，以帮助数据库管理员追踪数据库曾经发生过的各种时间。

在 MySQL 中，有 4 种不同的日志，分别是错误日志、二进制日志（BINLOG 日志）、查询日志和慢查询日志，这些日志记录着数据库在不同方面的踪迹

1.1、错误日志

错误日志是 MySQL 中最重要的日志之一，它记录了当 mysqld （MySQL 服务器）启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。

• 当数据库出现任何故障导致无法正常使用时，可以首先查看此日志。

• 可以使用 –log-error[=file_name] 选项来指定 mysqld 保存错误日志文件的位置；如果没有指定 file_name 值，那么 mysqld 使用错误日志名 host_name.err（host_name 为主机名）并默认在参数 DATADIR （数据目录）指定的目录中写入日志文件。

1.2、二进制日志

1、概述

二进制日志记录了所有的 DDL （数据定义语言）语句和 DML（数据操纵语言）语句，但是不包括数据查询语言。

• 语句以 事件 的形式保存，它描述了数据的更改过程
• 二进制日志对灾难时的数据回复和主从赋值起着极其重要的作用。

binlog 是 MySQL 的逻辑日志，并且由 Server 层进行记录，使用任何存储引擎的 MySQL 数据库都会记录 binlog 日志

2、日志的位置和格式

• binlog 位置

1. 当使用 --log-bin[=file_name] 选项启动时，mysqld 将包含所有更新数据的 SQL 命令写入日志文件。
2. 如果没有给出 file_name 的值，那么日志默认名为主机名 + -bin ，如果给出了这个文件名，但是没有包含路径，那么文件会默认写入参数 DATADIR （数据目录）指定的目录

• binlog 的格式

binlog 日志有三种存储格式，分别为 statement 、 row 和 mixed ，在 MySQL 5.7 后，默认值是 Row ，日志格式可以通过 binlog-format 参数指定

3、mysqlbinlog 工具

• 由于服务器生成的二进制日志文件以二进制格式保存，所以如果想要检查这些文件的文本格式，那么就会用到 mysqlbinlog 日志管理工具。
• 具体用法如下

shell> mysqlbinlog [options] log-files1 log-files2...

• 其中 options 有许多选项，常用的如下：

1. -d，–database=name：指定数据库名称，只列出指定的数据库相关操作。
2. -o，–offset=#：忽略掉日志中的前 n 行命令
3. -r，–result-file=name：将输出的文本格式日志输出到指定文件
4. -s，–short-form：显示简单格式，忽略一些信息
5. –set-charset=char-name：在输出为文本格式时，在文件第一行加上 char-name ，这个选项在某些情况下装载数据时非常有用。
6. –start-datetime = name -stop-datatime=name：指定日期间隔内的所有日志
7. –start-position = # –stop-position = #：指定位置间隔内的所有日志。

4、日志的读取

由于 binlog 以二进制方式存储，不能直接读取，需要用 mysqlbinlog 工具来查看，语法如下：

shell> mysqlbinlog log-file

5、binlog 的刷盘时机

• 对于 InnoDB 存储引擎而言，只有在事务提交时才会记录 binlog ，此时记录还在内存中；
• MySQL 使用 sync_binlog 参数控制 binlog 的刷盘时机，取值范围是 0 - N：

1. 0：不去强制要求，由系统自行判断何时写入磁盘
2. 1：每次 commit 的时候都要将 binlog 写入到磁盘
3. N：每 N 个事务，才会将 binlog 写入到磁盘中。

从上面可以看出，sync_binlog 最安全的设置是 1 ，这也是 MySQL 5.7 后的默认值。

但是在某种情况下也可以适当调大，牺牲一定的一致性来获取更好的数据库性能。

1.3、查询日志

• 查询日志记录了客户端的所有语句，而 binlog 不包含只查询数据的语句。

• 查询日志记录的格式是纯文本，因此可以直接进行读取。

• 对于访问频繁的系统，此日志对系统性能的影响较大，建议在一般情况下关闭

1.4、慢查询日志

1、概述

慢查询日志记录了包含所有执行时间超过参数 long_query_time （单位：秒）所设置值的 SQL 查询语句的日志

注意：获取表锁定的事件不算作执行时间

2、文件位置及格式

当使用 --log-slow-queries[=file_name] 选项启动 mysqld 时，慢查询日志开始被记录。和前面几个日志一样，如果没有给定 file_name 的值，日志将写入参数 DATADIR 指定的路径下，默认文件名为 host_name-slow.log

3、日志的读取

• 和错误日志、查询日志一样，慢查询日志记录的格式也是纯文本，可以被直接读取。
• 查询一下参数 long_query_time 的值

mysql> show variables like '%long';

• 设置 long_query_time 参数的值

mysql> set long_query_value = 2;

• 慢查询日志对于我们发现应用中有性能的 SQL 很有帮助，建议正常情况下，打开此日治并经常查看分析。

二、InnoDB 的事务日志

2.1、概述

• InnoDB 是一个支持事务的存储引擎，它利用回滚日志（redo log）和重做日志（undo log）实现事务，并实现 MVCC（多版本并发控制）

• 需要注意的是，undo log 不是 redo log 的逆向过程，它们两个都算是用来恢复的日志。

1. redo log 通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样，它用来恢复提交后的物理数据页（只能恢复到最后一次提交的位置）

如果数据发生了丢失，数据库可以根据 redo log 进行数据恢复。

2. undo log 通常是逻辑日志，用于回滚行记录到某个版本，当我们对记录做了变更操作时就会产生 undo 记录。

如何理解逻辑日志这个概念？

可以认为，当我们 delete 一条记录时，undo log 中会记录一条对应的 insert 记录，反之亦然，而当我们 update 一条记录时，undo log 中会记录一条与其相反的 update 记录。

2.2、redo log 和 binlog 的区别

redo log 不是二进制日志，虽然二进制日志中也记录了 InnoDB 表中的许多操作，也能实现重做的功能，但它们之间有很大差别。

• binlog 是由 MySQL 的 Server 层产生的，不管使用什么存储引擎，对数据库的修改都会产生二进制日志记录；而 redo log 是 InnoDB 存储引擎产生的，只记录使用了 InnoDB 引擎的表的修改。
• binlog 是逻辑日志，而 redo log 是物理日志
• binlog 在事务提交时一次性将缓存写入日志文件中（对于非事务表的操作，则是每次执行语句成功后就直接写入）。而后者是在对数据修改前就将缓存中的 redo log 进行写入，然后才对数据进行修改操作；

redo log 保证在发出事务提交指令时，先将缓存中的 redo log 写入磁盘，写入完成后再执行提交动作。

• redo log 在事务执行过程中会不断地写入，而 binlog 是在事务最终提交前写入。
• redo log 是循环写的，因为它地固定空间会被用完；binlog 是可以追加写的。

2.3、redo log

1、redo log 的组成

• redo log 包括两部分：一是内存中的日志缓冲（redo log buffer），这一部分的数据是易丢失的；二是磁盘上的重做日志文件（redo log file），这部分日志是持久化的。

InnoDB 引擎对数据的更新，是先将更新记录写入到 redo log buffer 中，然后再更新数据，在缓冲中的数据会在系统空闲或者是按照设定的更新策略再将缓冲的内容刷新到磁盘中。

这就是所谓的预写式技术（Write Ahead Logging），这种技术可以大大降低 I / O 操作的频率，提升数据刷新的效率。

2、持久化的实现方式

• 在概念上，InnoDB 使用 force log at commit 机制实现事务的持久性，即在事务提交时，必须先将该事务的所有事务日志写入到磁盘上的 redo log file 和 undo log file 中进行持久化

每次将 log buffer 中的日志写入到日志文件的过程中都会调用一次操作系统的 fsync 操作。这是因为 MySQL 是工作在 用户空间上的，故 log buffer 处于用户空间的内存中，写入操作需要经过操作系统内核空间的 os buffer ，调用 fsync() 的作用就是将 os buffer 中的日志刷到磁盘的日志文件中。

• MySQL 支持用户自定义在 commit 时如何将 log buffer 中的日志刷到 log file 中。可以通过控制变量 innodb_flush_log_at_trx_commit 的值来决定，该值有 3 种值：0、1、2，默认为 1

这个值仅仅控制 commit 动作是否刷新 log buffer 到磁盘

1. 当设置为 0 时，事务提交时不会将 log buffer 中的日志写入到 os buffer，而是每秒写入 os buffer 并调用 fsync() 写入到磁盘文件中。

也就是说，当 innodb_flush_log_at_trx_commit 值为 0 时，如果数据库系统崩溃，那么可能丢失一秒钟的数据。

2. 当设置为 1 时，事务的每次提交都会将 log buffer 中的日志写入到 os buffer 并调用 fsync() 将内容刷到磁盘文件中。

这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都要写入磁盘，所以效率较低

3. 当设置为 2 时，事务的每次提交都仅写入到 os buffer ，然后是每秒调用 fsync() 将 os buffer 中的内容写入到磁盘文件中。

如果只是 MySQL 数据库挂了，那么由于文件系统没有问题，所以对应的事务数据不会丢失。只有在数据库所在的主机操作系统损坏或者突然断电的情况下，数据库的事务数据可能丢失一秒的 事务数据。

• 在主从复制结构中，要保证事务的持久性和一致性，需要对日志相关变量设置如下：

1. 如果开启了 binlog ，那么需要设置 sync_binlog = 1 ，每提交一次事务就将事务同步写到磁盘中
2. 设置 innodb_flush_log_at_trx_commit = 1 ，每提交一次事务就将 redo log 和 undo log 缓冲中的数据写到磁盘中。

3、为什么有了 binlog 还需要 redo log ？

redo log 是保证 MySQL crash-safe 的重要因素，在 MySQL 崩溃恢复后，主要依靠 redo log 和 binlog 来对 MySQL 进行数据恢复，从而保持数据的完整和一致。

4、InnoDB 的两阶段提交

• 为了保证两份日志最终恢复到数据库的数据是一致的，所以采用两阶段提交的机制。

MySQL 中更新一条语句的流程，以蓝色表示执行器，白色表示存储引擎，假设我们现在要给 id 为 2 的员工涨 1000 工资

具体流程如下：

1. server 层中的执行器先找引擎取 id 为 2 这一行，id 是主键，如果这一行所在的数据页在内存中，那么直接返回，否则需要先从磁盘读入内存，然后再返回
2. 执行器拿到引擎给的行数据，对其工资 + 1000，再调用引擎接口写入这行新数据
3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后通知执行器执行完成，可以提交事务。
4. 执行器生成这个操作的 binlog ，并将 binlog 写入到磁盘中；
5. 执行器调用引擎的提交事务接口，引擎将刚刚写入的 redo log 改为 commit 状态，完成更新。

redo log 的写入分为两个阶段，分别是 prepare 和 commit ，这就是两阶段提交。

5、MySQL 异常重启后如何保证数据完整？

• 在上图时刻 A 中，也即是写入 redo log 并处于 prepare 阶段以后、写 binlog 之前，MySQL 发生了崩溃：此时由于 binlog 还没写，redo log 也还处于 prepare 状态，所以崩溃恢复后，这个事务会被回滚。这个时候由于 binlog 还没写，所以也不会传播到备库，数据一致。
• 在上图时刻 B 中，也就是写完 binlog 后发生 crash ，如果 redo log 里面的事务存在 commit 标识（事务是完整的），则直接提交；如果 redo log 里面的事务只有 prepare 没有 commit ，那么需要判断对应事务在 binlog 中是否存在并完整，完整则提交事务，否则回滚事务。

2.4、undo log

1、概述

undo log 是逻辑日志，只是将数据库逻辑地恢复到原来的样子；所有修改都被逻辑地取消了，但是数据结构和页本身在回滚之后可能大不相同。

• undo log 主要有两个作用：提供事务回滚和多版本并发控制 (MVCC)
• 当执行 rollback 或者数据库崩溃时，就可以从 undo log 中的逻辑记录中读取到对应的内容进行回滚，撤销未提交事务对数据库产生的影响。

有时候应用到行版本控制的时候，也是通过 undo log 来实现的：当读取的某一行被其他事务锁定时，它可以从 undo log 中分析出该行记录以前的数据是什么，从而提供该行版本信息，让用户实现非锁定一致性读取。

2、undo log 的存储方式

• InnoDB 存储引擎对 undo log 的管理采用段（segment）的方式，具体来说是一种名为回滚段（rollback segment）的数据结构

回滚段中有 1024 个 undo log segment ，以前旧版本只支持 1 个 rollback segment ，也就是说只能存储 1024 个 undo log segment ，MySQL 5.5 后，可以支持 128 个 rollback segment ，也就是说可以存储 128 * 1024 个 undo log segment

3、工作原理

• undo log 在事务开始前产生；事务在提交后，并不会立刻删除 undo log，InnoDB 会将该事务对应的 undo log 放入到删除列表中，后面会通过一个称为 purge thread 的后台线程进行回收处理。

• undo log 在 MySQL InnoDB 存储引擎中用于实现 MVCC ，在事务未提交之前，undo log 保存了未提交之前的版本数据，所以 undo log 中的数据可以作为数据旧版本快照提供给其他事务进行快照读

1. 事务 A 手动开启事务，执行更新操作，首先会将更新命中的数据先被分到 undo buffer 中。
2. 事务 B 手动开启事务，执行查询操作，此时会从 undo 日志中读取数据返回，进行快照读。

2.5、MySQL 脏读、幻读和不可重复读

1、脏读、不可重复读、幻读的区别和出现场景

• 脏读：事务读取到了其他事务还未提交的数据。
• 不可重复读：在同一次事务中前后查询不一致的问题（同一条数据前后不一致）
• 幻读：事务 A 先按照一定条件进行数据读取，期间事务 B 插入了相同搜索条件的新数据，事务 A 再按照原先条件进行搜索时，发现了事务 B 新插入的数据。（多了数据）

2、事务的隔离级别

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED	会发生	会发生	会发生
READ COMMITTED	不会发生	会发生	会发生
REPEATABLE READ	不会发生	不会发生	会发生（InnoDB 除外）
SERIALIZABLE	不会发生	不会发生	不会发生

3、数据库并发场景

• 读 - 读：不存在任何问题，也不需要并发控制
• 读 - 写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读、幻读、不可重复读
• 写 - 写：有线程安全问题，可能会存在更新丢失问题

三、MVCC

3.1、概述

• MVCC 全称 Multi-Version Concurrency Control ，即多版本并发控制，主要是为了提升数据库的并发性能，MyISAM 不支持事务，所以 MVCC 一般都是围绕 InnoDB 存储引擎展开的，MVCC 是基于数据版本对并发事务进行访问，作用于 RC 和 RR 两个隔离级别。
• 当同一行数据平行发生读写请求时，通常采用上锁的方式进行阻塞，而 MVCC 采用更好的方式去处理读 - 写请求，做到在发生读 - 写请求冲突时不用加锁。

这里的读指的是快照读，而不是当前读，当前读是一种加锁操作。

3.2、快照读和当前读

1、当前读

它读取的数据库记录，都是当前最新的版本，会对当前读取的数据进行加锁，防止其他事务修改数据。它是需要加悲观锁的一种操作

以下的操作都是当前读：

1. select lock in share mode（共享锁）
2. select for update（排他锁）
3. update（排他锁）
4. insert（排他锁）
5. delete（排他锁）
6. 串行化事务隔离级别

2、快照读

快照读的实现基于多版本并发控制，既然是多版本，那么快照读读取到的数据不一定是当前最新的版本，有可能是之前历史版本的数据。

一般来说，普通的不加锁的 select 操作都是快照读（事务级别不可是串行化）

3、MVCC 和快照读的关系

• MVCC 是一个抽象概念，即维持一个数据的多个版本，从而使得读 - 写操作没有冲突

在一个事务对数据进行写时，其他事务可以读取这条数据的另外版本，从而使同时进行的读写操作没有冲突。

• 快照读是实现 MVCC 的一种方案。

3.3、MVCC 的实现原理

MVCC 主要通过 版本链、undo log 和 Read View 来实现的。

1、版本链

我们数据库中的每行数据，除了我们肉眼看见的字段之外，还存在几个隐藏字段，它们分别是：

• trx_id （6 byte）

最近修改（修改 / 插入）事务 ID：记录修改 / 创建这条该数据行的事务 ID

• roll_pointer（7 byte）

回滚指针，它是实现版本 链 的关键，用于指向该数据行的上一个版本（该数据行上一个版本的记录存储在 rollback segment 中）

• row_id（6 byte）

隐含的自增 ID（主键）：如果数据表没有主键，那么 InnoDB 引擎会自动以 row_id 产生一个聚簇索引

• delete_flag

删除标志，记录被更新或删除时并不代表真的删除，而是删除标志改变了

比如说，在 Person 表中存在一条记录如上，其中 row_id 是 InnoDB 为没有设定主键的表生成的隐式主键；trx_id 代表将数据行修改为以上状态的事务的 id ；而回滚指针指向该数据行上一个版本的指针。

1. roll_pointer 配合 undo log ，指向该数据的上一个旧版本。
2. 每次对该数据进行一次改动时，都会记录一条 undo log ，每条 undo log 都会有一个 roll_pointer 属性（INSERT 操作对应的 undo log 没有该属性，因为新数据并没有更早的版本），我们可以通过回滚指针将不同版本的同一行数据连起来，串成一个链表。

3. 对该记录每次更新后，都会将更新前的记录放到一条 undo log 中，这算是该记录的一个旧版本，随着更新次数的增多，所有版本都会被 roll_pointer 属性连接成一个链表，这个链表就是版本链，链表头节点就是当前记录的最新版本。

每个版本中还包含生成该版本时对应的事务 id ，这个信息在根据 Read View 判断版本可见性时会用到。

2、undo log

undo log 主要用于记录数据被修改之前的日志，在表信息进行修改前，会先将数据拷贝到 undo log 中。

当事务进行回滚时，可以通过 undo log 的记录进行数据还原。

• undo log 的用途

1. 保证事务进行 rollback 时的原子性和一致性。
2. 用于 MVCC 快照读的数据，在 MVCC 中，通过读取 undo log 中的历史版本数据，可以实现不同事务版本号都有自己独立的快照数据版本。

• undo log 的分类

1. insert undo log

代表事务在 insert 新纪录时产生的 undo log ，只在事务回滚时需要，并且在事务提交后就可以立即被丢弃

2. update undo log （主要）

事务在进行 update / delete 时产生的 undo log；不仅在事务回滚时需要，在快照读时也需要。

不能随便删除，只有当快照读或事务回滚不再涉及该日志时，对应的日志才会被 purge 线程统一删除。

3、Read View

事务进行快照读操作时生成的读试图（Read View），在该事务执行快照读的那一刻，会生成数据库系统当前的一个快照

当每个事务开启时，都会被分配一个 ID ，这个 ID 是递增的，所以越是新的事务，ID 值就越大。

Read View 主要是用来做可见性判断的，即当我们某个事务执行快照读时，对该记录创建一个 Read View 读视图，用它作为条件来判断当前事务能够看到那个版本的数据，能看到的数据可能是当前最新的数据，也有可能是该行记录的 undo log 里面某个版本的数据。

• 我们可以将一个 Read View 类比为 Java 中的一个对象，它存在以下几个重要的属性

1. m_ids：表示在生成 Read View 的那一时刻，当前系统中活跃（未提交）的读写事务的 id 列表
2. min_trx_id：表示在生成 Read View 的那一时刻，当前系统活跃的读写事务中最小的事务 id，也就是 m_ids 中的最小值
3. max_trx_id：表示在生成 Read View 的那一时刻，应该分配给下一个事务的 id 值，也就是 m_ids 中的最大值 + 1，这个属性也称为预分配事务编号
4. creator_trx_id：表示生成该 Read View 的事务的事务 id

4、读已提交的数据提取

当隔离级别为 RC （读已提交）时，它会在每一次执行快照读时生成 Read View。

• 上述事务 4 进行了两次快照读，由于隔离级别为 RC ，所以在两次快照读时分别生成了两次快照

1. 生成第一个快照时，此时 trx_id 为 1 的事务已经结束，还有活跃中的事务仅有 trx_id 分别为 2，3，4 的事务，故 m_ids 列表为 {2，3，4}，同时可以得到最小活跃事务 id 为 2 ，预分配事务 id 为 5（4 + 1），创建当前 Read View 的事务为 4
2. 生成第二个快照时，此时 trx_id 为 1，2 的事务已经结束，还处于活跃状态的事务仅有 trx_id 分别为 3， 4 的事务，故 m_ids 列表为 {3,4}，同时可以得到最小活跃事务 id 为 3，预分配事务 id 为 5 ，创建当前 Read View 的事务为 4

• 版本链中数据的提取规则，将当前 Read View 中的信息代入到以下的数据访问规则中，当符合访问规则时，直接将对应数据返回

1. 如果被访问的 trx_id 与 Read View 中的 creator_trx_id 相同，那么说明当前事务在访问自己修改的记录，可见，直接返回数据即可

这种情况类似在事务中执行了 update 后立即又执行了一次 select ，相同事务内当然可以看到修改后的数据。

2. 如果被访问的 trx_id 小于 Read View 中的 min_trx_id 属性，那么证明该版本已经提交，可见，直接返回数据即可
3. 如果被访问的 trx_id 大于等于 Read View 中的 max_trx_id ，表示该版本在生成 Read View 时还未开启，不可见，直接返回
4. 如果被访问的 trx_id 满足 min_trx_id <= trx_id <= max_trx_id ，那么判断 trx_id 是否在 m_ids 中，如果在，那么说明生成 Read View 时，该版本事务尚未提交，所以该版本不可见；如果不存在，那么说明生成 Read View 时，该版本事务已经提交，可见，返回数据。

• 演示读已提交隔离级别的数据提取，此时版本链中的数据如下

• 生成第一个快照时，快照的信息如下

1. 判断版本链事务 id trx_id (3) 是否等于 creator_trx_id(4)，如果成立说明数据就是自己这个事务更改的，可以访问；

这里不成立，所以向下继续匹配条件

2. 判断版本链事务 id trx_id (3) 是否满足 trx_id < min_trx_id (2) ？ 如果成立说明 trx_id 对应的事务已经提交，可以访问当前 trx_id 对应事务的数据；

这里条件不成立，所以向下继续匹配条件

3. 判断版本链事务 id trx_id (3) 是否满足 trx_id > max_trx_id (5) ？如果成立说明该事务是在 Read View 生成后才开启的，不允许访问

这里条件不成立，所以继续向下匹配

4. 判断版本链事务 id trx_id (3) 是否满足 min_trx_id <= trx_id <= max_trx_id ，如果成立，那么需要进一步判断 trx_id 是否存在于 m_ids 中，如果存在，那么证明此时事务尚未提交，不可访问；如果不存在，那么证明此数据对应的事务已经提交了，可以访问

如果对于某一个 undo log 版本链中的元素，它的数据均不满足以上的条件，那么将顺着回滚指针往下找到更早的数据。然后继续带入到以上的数据中进行判断。

比如说，在 trx_id 为 3 的数据均不满足以上的条件后，我们按照回滚指针找到 trx_id 为 2 的那一个版本的数据，然后将这个数据代入 Read View 条件中进行匹配，最终我们会找到 trx_id 为 1 的数据，即 name 为 “张三” 的数据并返回。

• 生成第二个快照时，快照的信息如下

此时再将 undo log 版本链中的数据信息一一与快照信息进行对比，最终会将 trx_id 为 2 的数据返回，此时读取到 name 为 “张小三” 的数据

• 可以看到，在隔离级别为 RC 的情况下，由于每次快照读都会产生一个 Read View ，所以就会出现不可重复读现象。

5、可重复读的数据提取

与 RC 不同的是，在隔离级别为可重复读的情况下，仅在第一次执行快照读时生成 Read View ，后续快照读会复用第一次快照读生成的 Read View，但这种情况存在特例。

• 在隔离级别为 RR 时，由于多次快照读之间使用的 undo log 版本链一致、Read View 一致，故而两次快照读的结果也应该一致，从而解决了不可重复读的问题。
• 在隔离级别为 RR 时，InooDB 在绝大部分情况下可以避免幻读。

因为在 InnoDB 中，不是用锁，而是用 MVCC 来解决幻读问题。

1. 连续多次快照读，Read View 会产生复用，没有幻读问题
2. 如果两次快照读之间存在当前读，且当前读修改的数据覆盖到其他事务新增的信息时，那么会导致 Read View 重新生成，从而产生幻读。

四、MySQL 学习补充

4.1、存储引擎补充

• 为了管理方便，人们把 连接管理 、 查询缓存 、 语法解析 和 查询优化 这些并不涉及真实数据存储的功能划分为 MySQL server 的功能，把真实存取数据的功能划分为 存储引擎 的功能。
• 在 MySQL server 完成了查询优化后，只需按照生成的 执行计划 调用底层存储引擎提供的 API ，获取到数据后返回给客户端就可以了。
• MySQL 中提到了存储引擎的概念。简而言之，存储引擎就是表的类型 。
• 存储引擎以前也叫做表处理器，后来改名为 存储引擎，它的功能就是接收上层传下来的指令，然后对表中的数据进行提取或写入操作。
• 存储引擎的不同，决定了数据在磁盘中存储的文件格式的不同。

4.2、存储引擎介绍

二者不是替代关系。

1、InnoDB 引擎

支持外键、支持事务、支持行级锁的存储引擎

• MySQL 从 3.23.34a 开始就包含了 InnoDB 存储引擎，5.5 版本后就使用 InnoDB 作为默认的存储引擎。

• InnoDB 被设计用来处理大量的短期事务，可以确保事务的完整提交和回滚。

• 除了增加和查询外，还需要更新、删除操作时，那么应该优先选择 InnoDB 存储引擎。

• 除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑 InnoDB 存储引擎。

• 数据文件结构：

  • 表名.frm 存储表结构（MySQL 8.0 时，合并在 表名.ibd 中）
  • 表名.ibd 存储数据和索引

• 在之前的版本中，数据字典以元数据文件，非事务表等来存储。现在这些元数据文件被删除了，比如：.frm 、 .par 、 .trn 等都在 8.0 版本中都不存在了。

• 对比 MyISAM 存储引擎，InnoDB 写的处理效率要差一些，并且会占用更多的磁盘空间以保存数据和索引。

• MyISAM 只缓存索引，不缓存真实数据；InnoDB 不仅要缓存真实数据还要缓存索引，所以 InnoDB 对内存要求较高，而且内存大小对性能有决定性的影响

为什么说 InnoDB 的处理效率要差一点？因为 InnoDB 将数据和索引都放在一起，所以在加载时，需要加载的数据比 MyISAM 多（MyISAM 将数据与索引分开存储）

2、MyISAM 引擎

主要的非事务处理存储引擎

• MyISAM 提供了大量的特性，包括全文索引、压缩和空间函数等，但 MyISAM 不支持事务、行级锁和外键，另外一个致命的缺陷就是：MyISAM 在崩溃后无法安全恢复

• 它是 5.5 版本之前默认使用的存储引擎

• MyISAM 的优势是访问速度快，一些对事务的完整性要求不高，或者以 SELECT 、 INSERT 为主的场景下可以考虑使用 MyISAM

• MyISAM 针对数据统计有额外的常数存储，所以 select count(*) 的查询效率非常高

• 数据文件结构：

  • 表名.frm 存储表结构
  • 表名.myd 存储表数据（MyData）
  • 表名.myi 存储表索引（MyIndex）

• 应用场景：只读应用或者以读为主的业务。

3、Archive 引擎

用于数据存档

• archive 是存档的意思，它仅仅支持 插入 和 查询 两种功能
• 被插入的行数据不能再修改
• 拥有很好的压缩机制，使用 zlib 压缩库，在记录请求时实时地进行压缩，经常被用来作为仓库使用。
• 使用行级锁，它适合用于存储大量地独立的作为历史记录的数据，插入效率高，但查询效率不高。
• 同样数据量下，Archive 表比 MyISAM 表要小 75%，比 InnoDB 小 83%

4、Memory 引擎

将数据置于内存中的表

• Memory 采用的逻辑介质是内存，响应速度非常快，但是当 mysqld 守护进程崩溃时数据就会丢失。
• Memory 存储引擎要求存储的数据是长度不变的格式，比如说 Text 和 Blob 类型的数据就是不可用的（长度不固定）
• Memory 同时支持哈希索引和 B+ 树索引：
  • 哈希索引在进行等值查询时较快，但是对于范围查询要慢上许多
  • 默认使用哈希索引，其速度要比使用 B+ 树索引快
• Memory 表至少要比 MyISAM 表快上一个数量级。
• Memory 表的大小是受到限制的，主要取决于 max_rows 和 max_heap_table_size 两个参数，其中前者可以在创建表时指定，而后者默认为 16MB ，可以按需扩容。
• Memory 表的数据文件和索引文件分开存储。
• Memory 表的数据非常容易丢失，且生命周期较短，所以选择该引擎时需要特别小心
• 如果目标数据较小，而且需要非常频繁地进行访问，在这种场景下可以考虑使用 Memory 引擎

可以通过参数 max_heap_table_size 来控制 Memory 表的大小。

• 如果数据是临时的，而且必须立即可以得到，那么可以放在内存中
• 存储在 Memory 表中的数据的重要性和完整性不宜过高。

4.3、联合索引

• 在 MySQL 中，我们可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让 B+ 树按照 c2 和 c3 列的大小进行排序，这个包含两层含义：
  • 先把各个记录和页按照 c2 列进行排序
  • 在记录的 c2 列相同的情况下，采用 c3 列进行排序

我们需要注意以下几点：

• 每条目录项记录都由 c2、c3 和页号 这三部分组成，各条记录先按照 c2 列的值进行排序，如果记录的 c2 列相同，则按照 c3 列进行排序
• B+ 树的叶子节点处的用户记录由 c2、c3 和 主键 组成
• 联合索引本质上还是一个二级所以，和分别为 c2 和 c3 建立索引的表述是不同的，不同点如下：
  • 建立联合索引时，只会建立一棵 B+ 树
  • 分别为 c2 和 c3 建立索引时，会分别以 c2 和 c3 列的大小为排序规则建立两棵 B+ 树

4.4、InnoDB 的 B+ 树索引的注意事项

1、根页面位置万年不动

• 每当为某个表创建一个 B+ 树索引（聚簇索引不是人为创建的，默认就有）的时候，都会为这个索引创建一个 根节点 页面。

当表中不存在数据时，每个 B+ 树索引所对应的根节点中既没有用户记录，也没有目录项记录。

• 随后往表中插入用户记录时，先将用户记录存储到这个根节点中
• 当根节点中的可用空间用完时，此时再往表中插入记录时，会将根节点中所有的记录复制到一个新分配的页中（设这个新页为 页 a），然后对这个页进行页分裂的操作，得到一个新页（设为页 b）。这时新插入的记录根据键值（也就是聚簇索引中的主键值，二级索引中对应索引列的值）的大小就会被分配到页 a 或者 页 b 中，而根节点便升为存储目录项记录的页

一个 B+ 树索引的根节点自诞生之日起，就不会再发生移动，这也只要我们对某个表建立一个索引，那么它的根节点的页号便会被记录到某个地方，然后凡是 InnoDB 存储引擎需要用到这个索引时，就从固定的地方取出根节点的页号，从而来访问这个索引。

2、内节点中目录项记录的唯一性

• 对于二级索引的 B+ 树而言，它的非叶子节点除了记录索引列和页号外，还需要记录主键值
• 比如说，现在有一张表

c1	c2	c3
1	1	‘a’
3	1	‘b’
5	1	‘c’
7	1	‘d’

如果此时二级索引目录项（非叶子节点）中记录的内容只是索引列 + 页号 的搭配的话，那么为 c2 列建立索引后的 B+ 树应该长这样

• 如果此时我们希望插入一条新纪录，其中 c1 c2 c3 的值分别为 9 、 1 、 ‘c’ ，那么在修改 B+ 树时就遇到了一个问题，由于页 3 中只记录了 c2 列的值 + 页号，而页 3 中两个目录项记录对应的 c2 值都是 1 ，我们要插入的值也 为 1 ，那么我们这条新数据应该插入到页 4 还是页 5 ？

所以为了保证新插入的记录都可以找到自己在哪个页里，我们需要保证在 B+ 树的同一层内节点的目录项记录除页号这个字段以外是唯一的，所以对于二级索引的内节点的目录项记录的内容实际上由三部分组成：

• 索引列的值
• 主键值
• 页号

故二级索引的 B+ 树应该为

此时再插入记录（9，1，’c’）时，由于页 3 中存储的目录项由 c2 列 + 主键 + 页号 组成，那么我们可以将新插入记录的 c2 列与 页 3 中的各目录项进行对比，如果 c2 列的值相同，那么可以接着比较主键的值。
在本例中，最后新纪录应该被插入到页 5 中。

3、一个页最少可以存储两条数据

一个 B+ 树只需要很少的层级就可以轻松存储数亿条记录，而且查询速度相当不错！这是因为 B+ 树本质上就是一个大的多层级目录，每次经过一个目录就可以过滤掉非常多无效的子目录，直到最后访问到存储真实数据的目录。

如果一个大目录中只存放一个子目录是啥效果呢？这样会造成目录层级非常非常多，而且最后的那个存放真实数据的目录中也只能存放一条记录，这样得不偿失，所以 InnoDB 的一个数据页至少可以存放两条数据。

4.5、MyISAM 索引的原理

和 InnoDB 一样 ，MyISAM 也支持 B+ 树索引，但后者实现的原理与前者的略有不同，MyISAM 引擎使用 B+ 树索引时，在叶子节点的 data 域中存放的是数据记录的地址

1、MyISAM 索引的原理

• MyISAM 将数据与索引分开存储，它将表中的记录按照记录的插入顺序单独存储在一个文件中，称为数据文件。这个文件并不划分为若干个数据页，而是有多少记录就往文件中塞多少记录就完事了。

由于在插入数据时没有刻意地按照主键大小排序，所以我们并不能在这些数据上使用二分法进行查找。

• 使用 MyISAM 存储引擎地表会把索引信息另外放存储在另一个索引文件中。MyISAM 会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是主键值 + 数据记录地址的组合

• 在 MyISAM 中，索引文件仅仅保存数据记录的地址，它的主键索引和二级索引在结构上没有任何区别，只是主键索引要求 key 是唯一的，而二级索引的 key 可以重复。

• MyISAM 的索引方式都是非聚簇的，下面总结一下二者索引的区别：、

  • 在 InnoDB 引擎中，我们只需要根据主键值对 聚簇索引 进行一次查找就可以找到对应的记录，而在 MyISAM 中还需要进行一次回表，即 MyISAM 中的索引都是二级索引。
  • InnoDB 本身的数据文件就是索引文件，而 MyISAM 中索引和数据是分开存储的
  • InnoDB 的非聚簇索引的 data 记录的是相应记录主键的值，而 MyISAM 索引记录的是数据地址。换句话说，InnoDB 的所有非聚簇索引都引用主键作为 data 域
  • MyISAM 的回表操作是十分快的，因为是拿着地址偏移量直接到文件中取数据，而 InnoDB 是通过获取主键的值然后再去聚簇索引中查找记录。速度没前者快。
  • InnoDB 要求表必须有主键（MyISAM）可以没有，如果在 InnoDB 表中没有显式指定主键，那么会选择表中的唯一列作为主键，如果表没有指定主键，又没有唯一列， 那么 InnoDB 会生成一个隐含字段作为主键，这个字段长度为 6 byte ，类型为长整型

五、InnoDB 数据存储结构

5.1、数据库的存储结构 – 页

索引结构为我们提供了高效的索引方式，不过索引信息以及数据记录都是保存在文件上的，确切说是存储在页结构中。另一方面，索引是在存储引擎中实现的，MySQL 服务器上的存储引擎负责对表中数据的读取和写入工作。

不同存储引擎中存放的格式一般是不同的，甚至有的存储引擎如 Memory 都不用磁盘来存储数据。

1、磁盘与内存交互的基本单位 – 页

• InnoDB 将数据划分为若干个页，页的大小默认为 16KB
• 以页作为磁盘与内存之间交互的基本单位，也就是说一次最少从磁盘中读取 16KB 的内容到内存中，一次最少将内存中的 16 KB 内容刷新到磁盘中
• 在数据库中，不论是读一行，还是读多行，都是将这些行所在的页进行加载。也就是说，数据库管理存储空间的基本单位是页，数据库 I/O 操作的最小单位是页，一个页中可以存储多个行记录。

记录是按照行来存储的，但是数据库的读取并不以行为单位，否则一次读取（也就是一次 I / O 操作）只能处理一行数据，效率非常低。

2、页结构概述

• 页 a、页 b、页 c…页 n 这些页可以不在物理结构上相连，只要通过双向链表相关联即可。
• 每个数据页中的记录会按照主键值从小到达的顺序组成一个单向链表
• 每个数据页都会为存储在它里面的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。

3、页的上层结构

在数据库中，还存在着区（Extent）、段（Segment）和表空间（TableSpace）的概念。行、页、区、段和表空间的关系如下图所示

• 区是比页大一级的存储结构，在 InnoDB 中，一个区会分配 64 个连续的页。因为 InnoDB 中页的大小默认是 16KB，所以一个区的大小为 64 * 16 KB
• 段由一个或多个区组成，在段中不要求区与区之间是相邻的。段是数据库中的分配单位，不同类型的数据库对象以不同的段形式存在。

当我们创建数据库表、索引时，会响应地创建对应的段，比如说创建一个表时会创建一个表段，创建一个索引时会创建一个索引段。

表空间是一个逻辑容器，它存储的对象是段，在一个表空间中可以有一个或多个段，但是一个段只能属于一个表空间。数据库由一个或者多个表空间组成

表空间从管理上可以划分为：系统表空间、用户表空间、撤销表空间 和 临时表空间 等

5.2、页的内部结构

• 页如果按照类型划分的话，常见的有数据页（保存 B+ 树节点）、系统页、Undo 页 和 事务数据页等，其中数据页是最常用的页
• 数据页的 16 KB 大小的存储空间被划分为七个部分，分别是
  • 文件头（File Header）
  • 页头（Page Header）
  • 最大最小记录
  • 用户记录
  • 空闲空间（Free Space）
  • 页目录（Page Directory）
  • 文件尾（File Tailer）

如下图所示：

• 这七个部分作用分别如下：

名称	占用大小	说明
文件头	38 字节	用于描述页的信息
页头	56 字节	页的状态信息
最大最小记录	26 字节	两个虚拟的行记录
用户记录	不确定	用于存储行记录内容（如果是叶子节点，那么存储数据，如果是非叶子节点，那么存储目录项）
空闲空间	不确定	用于记录页中还没有被使用过的空间
页目录	不确定	存储用户记录的相对位置
文件尾	8 字节	用于校验页是否完整

我们可以将这七个结构划分为三部分

1、文件头和文件尾

首先是文件通用部分，也就是文件头和文件尾

• 文件头部信息

不同类型的页都会以 File Header 作为第一个组成部分，它描述了一些针对各种页都通用的一些信息，这一部分由以下内容组成

名称	占用空间	描述
FIL_PAGE_SPACE_OR_CHKSUM	4 byte	页的校验和
FIL_PAGE_OFFSET	4 byte	页号
FIL_PAGE_PREV	4 byte	上一个页的页号
FIL_PAGE_NEXT	4 byte	下一个页的页号
FIL_PAGE_LSN	8 byte	页面被最后修改时对应的日志序列位置（Log Sequence Number）
FIL_PAGE_TYPE	2 byte	该页的类型
FIL_PAGE_FILE_FLUSH_LSN	8 byte	仅在系统表空间的一个页中定义，代表文件至少被刷新到了对应 LSN 值
FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID	4 byte	页属于哪个表空间

1. 其中每一个页都有一个单独的页号，可以类比为人的身份证号码， InnoDB 可以通过页号唯一定位一个页
2. FIL_PAGE_TYPE 代表当前页的类型，InnoDB 为了不同的目的将页划分为不同的类型

用于存储数据的页的类型是 FIL_PAGE_INDEX ，也就是所谓的索引页

3. 数据页的链接实现

在文件头部内容中存在两个属性，即 FIL_PAGE_PREV 和 FIL_PAGE_NEXT ，如果数据分散到多个不连续的页中存储时，需要将这些页关联起来，而 FIL_PAGE_PREV 和 FIL_PAGE_NEXT 就分别代表本页的上一个和下一个页的页号。这样通过前驱指针与后继指针将许许多多的页串联成一个双向链表，保证这些页形成逻辑上的联系。

4. 校验页的完整性

InnoDB 存储引擎以页为单位把数据加载到内存中处理，如果该页中的数据在内存中被修改了，那么在修改后的某个时间需要将数据同步到磁盘中，但是在同步到一半时断电了，造成了页传输的不完整。

此时为了校验一个页是否完整，可以通过文件尾和文件头的校验和来判断，如果两个值不相等，那么证明该页的传输有问题，需要重新进行传输

在内存中对页数据进行修改后，需要重新计算它的校验和，然后同步时需要将校验和也同步回磁盘，如果内存中的数据完全被刷进磁盘，那么文件头和文件尾的校验和都会被刷新，此时表示同步成功，如果在刷盘的过程中断电崩溃，那么只有文件头的校验和被刷新，而文件尾的校验和还是上版本的数据。

5. 校验和

对于一个很长的字符串来说，我们会通过某种算法来计算一个较短的值来代表这个很长的字符串，这个较短的值就被称为校验和。

在比较两个很长的字符串时，先比较这两个字符串的校验和，如果校验和都不一样，那么证明这两个字符串一定不等，所以省去了比较两个长字符串的时间消耗。

• 文件尾部信息

1. 校验和

这个部分是为了与文件头中的校验和形成一一对应关系

2. 页面被最后修改时对应的日志序列位置

这个部分也是为了校验页面完整性的，与文件头的 LSN 形成一一对应。

2、记录

第二部分是记录部分，页的主要作用就是存储记录，所以最大最小记录和用户记录部分占了页结构的主要空间。

• 空闲空间（Free Space）

我们自己存储的记录会按照指定的行格式存储到 User Records 部分。但是在一开始生成页的时候并没有 User Records 部分，每当我们插入一条记录，都会从空闲空间（Free Space），也就是尚未使用的存储空间中申请一个记录大小的空间划分到 User Records 部分。

当空闲空间完全被 User Records 部分替代了后，也就意味着这个页使用完了，此时如果还有新纪录插入的话，就需要去申请新的页了。

• 用户记录（User Records）

User Records 中的这些记录按照指定的行格式一条一条地摆放在 User Records 部分，相互之间形成单链表

3、页目录和页面头部

• 为什么需要目录？

在页中，记录是以单链表的形式进行存储的。单向链表的特点是插入、删除非常方便，但是检索效率不高，最差的情况下需要遍历链表上的所有节点才能检索。

因此在页结构中设计了页目录这个模块，用于专门给记录做一个目录，通过二分查找法的方式进行检索，提高效率。

• 页面头部

为了能得到一个数据页中存储的记录的状态信息，比如说本页以及存储了多少条记录，第一条记录的地址是什么，特意在页中定义了一个页面头部，用于记录信息。

4、COMPACT 行格式

COMPACT 行格式由 变长字段长度列表 、 NULL 值列表 、 记录头信息 和 记录真实数据 组成

• 记录头信息

这些记录头信息中各个属性如下：

名称	大小（单位：bit）	描述
delete_mask	1	标记该记录是否被删除
min_rec_mask	1	B+ 树每层非叶子节点的最小记录都会添加该标记
n_owned	4	表示当前记录拥有的记录数
heap_no	13	表示当前记录在记录堆的位置信息
record_type	3	表示当前记录的类型，0 表示普通记录，1 表示 B+ 树种非叶子节点记录，2 表示最小记录，3 表示最大记录
next_record	16	表示下一条记录的相对位置

1. delete_mask （删除标记）

这个属性标记着当前记录是否被删除，占用 1 bit （值为 0 ，代表记录没有被删除；值为 1 ，代表记录被删除掉了）

被删除的记录为何还在页中存储？

这些被删除的记录之所以不立即从磁盘上移除，是因为移除它们后其他的记录在磁盘上需要进行重新排列，导致性能消耗。

所以只是打一个删除标记，所有被删除掉的记录会组成一个所谓的垃圾链表，在这个链表中的记录占用的空间被称之为可重用空间，之后如果有新纪录插入到表中的话，可能将这些被删除的记录占用的存储空间覆盖掉。

2. heap_no

这个属性表示当前记录在本页中的位置。

MySQL 会自动为每个页里加两条记录，这两条记录不是我们自己插入的，所以也被称为伪记录或者虚拟记录。

3. next_record

它表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量

六、优化相关

6.1、索引的设计原则

1、适合创建索引的 11 种情况

• 字段的数值有唯一性的限制

1. 索引本身可以起到约束的作用，比如说唯一索引和主键索引都是可以起到唯一性约束的。
2. 如果某个字段的值是唯一的，那么就可以直接为其创建一个唯一性索引，或者主键索引，这样可以更快速地通过该索引来确定某条记录。

比如说学生表中学号是具有唯一性的字段，为学号字段创建唯一性索引可以很快地确定某个学生的信息；

唯一索引对 insert 速度的损耗可以忽略，但是对查找速度的提高是明显的。

• 频繁作为 WHERE 查询条件的字段

如果某个字段在 SELECT 语句的 WHERE 条件中经常被使用到，那么就需要对这个字段创建索引。

在数据量大的情况下，创建普通索引就可以大幅度提高数据查询的效率。

• 经常用于 GROUP BY 和 ORDER BY 的列

索引就是让数据按照某些顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序时，就需要对分组或者排序的字段进行索引。如果待排序的列有多个，那么可以在这些列上创建组合索引

• UPDATE、 DELETE 的 WHERE 条件列

当我们对某条数据进行 UPDATE 或者 DELETE 操作时，可以针对 WHERE 条件列建立索引。

对数据按照某个条件进行查询后再进行 UPDATE 或者 DELETE 的操作，如果对 WHERE 字段创建索引，那么就能大幅提高效率。

原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或者删除。

如果进行更新时，更新的字段是非索引字段，那么提升的效率会更明显，这是因为非索引字段的更新不需要对索引进行维护

• DISTINCT 字段需要创建索引

有时候我们需要对某个字段进行去重，使用 DISTINCT ，那么对这个字段创建索引也会提升查询效率。

• 多表 JOIN 连接操作时，创建索引注意事项

1. 连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增加会非常快，严重影响查询效率
2. 对 WHERE 条件创建索引， 因为 WHERE 才是数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。
3. 对用于连接的字段创建索引，并且在多张表中该字段的类型必须一致

因为如果连接字段的类型不一致，那么在进行连接时会使用函数进行数据类型转换，也就不会使用索引。

• 使用列的类型小的创建索引

这里所说的类型大小指的就是该类型表示的数据范围的大小。

如果我们希望对某个整数列创建索引的话，那么在表示的整数范围允许的情况下，尽量让索引使用较小的类型，比如说能使用 INT 就别用 BIGINT ，能用 TINYINT 就别用 INT

1. 数据类型越小，在查询时进行的比较操作越快。
2. 数据类型越小，索引占用的存储空间就越少，在一个数据页中就可以放下更多的记录，从而减少磁盘 I/O 带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，加快读写效率。

这个建议对于表的主键来说更加实用，如果主键使用更小的数据类型，就可以节省更多的存储空间。

• 使用字符串前缀创建索引

1. 假设我们的字符串很长，那存储一个字符串就需要占用很大的存储空间。
2. 我们可以通过截取字段的前面一部分内容建立索引，这个索引称为前缀索引。

这样在查找记录时虽然不能精确地定位到记录的位置，但是能定位到响应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串，既节约空间，又减少了字符串的比较时间。

3. 索引的长度和区分度是一对矛盾体，可以使用 count(distinct left (列名, 索引长度)) / count(*) 的区分度来确定。

区分度越接近于 1 越好，而在区分度相同的条件下，索引长度越低越好。

• 区分度高（散列性高）的列适合创建索引

• 使用最频繁的列放到联合索引的左侧

• 在多个字段都要创建索引的情况下，联合索引高于单值索引

2、不适合创建索引的 7 种情况

• WHERE 中使用不到的字段不要设置索引

索引的价值是快速定位，如果起不到定位的字段通常不需要创建索引。

• 数据量小的表最好不要使用索引

表记录太小，是否创建索引对查询效率影响并不大。甚至说，查询花费的时间可能要比遍历索引的时间还要短，这种情况下，索引起不到优化效果。

• 有大量重复数据的列上不要创建索引

比如说学生表的性别字段上只有男与女两个不同值，因此没必要创建索引。如果创建索引，不但不会提高查询效率，反而会严重降低数据更新速度。

• 避免对经常更新的表创建过多的索引

1. 频繁更新的字段不一定要创建索引。因为更新数据时，也要更新索引，如果索引太多，那么在更新索引时也会造成负担，从而影响效率。
2. 避免对经常更新的表创建过多索引，同时索引中的列要尽可能少。此时虽然索引提高了查询速度，但是也增加了更新负担。

• 不建议使用无序的值作为索引

例如身份证、UUID（在索引比较时需要转换为 ASCII ，并且插入时可能造成页分裂）、MD5、HASH

• 删除不再使用或者很少使用的索引

• 不要定义冗余或者重复的索引

3、限制索引的数目

建议单张表的索引不超过 6 个

• 每个索引都需要占用磁盘空间，索引越多，需要占用的磁盘空间就越大。
• 索引会影响 INSERT、UPDATE、DELETE 等语句的性能，因为表中数据更改的同时，索引也可能进行相应的调整和更新，会造成负担
• 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成一个最好的执行计划，如果同时有很多个索引都可以用于查询，那么会增加 MySQL 优化器

6.2、JOIN 语句原理

• 对于内连接来说，查询优化器可以决定哪张表作为驱动表，哪张表作为被驱动表。

1. 对于内连接来说，如果表的连接条件中只有一个字段有索引，那么有索引字段所在的表会被作为被驱动表
2. 对于内连接来说，如果两个表的连接条件都存在索引，那么会选择小表作为驱动表（小表驱动大表）

• JOIN 方式连接多个表，本质就是各个表之间数据的循环匹配。

在 MySQL 5.5 之前，MySQL 只支持一种表间关联方式，那就是嵌套循环（Nested Loop Join）。如果关联表的数据量很大，那么 JOIN 关联的执行时间就会非常长。

在 MySQL 5.5 以后的版本中，MySQL 引入了 BNLJ （Block Nested Loop Join） 算法来优化嵌套执行

• 在连接查询中，驱动表和被驱动表不是根据 SQL 语句中表的出现顺序决定的，查询优化器可能会帮我们重新划定驱动表和被驱动表。

1、Simple Nested-Loop Join（简单嵌套循环连接）

这个算法十分简单，即从表 A 中取出一条数据 1 ，然后遍历表 B ，将匹配到的数据放到 result 中，以此类推，驱动表的每一条记录都需要与被驱动表的记录进行判断。

这种方式的效率是非常低的，如果 A 表中有 100 条，B 表中有 1000 条数据，那么扫描次数为 100000 次

2、Index Nested-Loop Join（索引嵌套循环连接）

Index Nested-Loop Join 的优化思路是为了减少内层表数据的匹配次数，所以要求被驱动表上必须有索引才行。

通过外层表匹配条件直接与内层表索引进行比较，避免与内层表的每条记录去进行比较，这样极大地减少了对内层表的匹配次数。

3、Block Nested-Loop Join

如果存在索引，那么会使用 Index 的方式进行 Join ，如果 Join 的列没有索引，被驱动表要扫描的次数太多了。

Simple Nested Loop Join IO 的次数非常多，所以为了减少被驱动表的 IO 次数，就出现了 Block Nested Loop Join。

与前者相比，Block Nested Loop Join 不再是逐条获取驱动表中的数据，而是一块一块的获取，它引入了 join buffer 缓冲区，将驱动表 join 相关的部分数据列缓存到 join buffer 中，然后全表扫描被驱动表，被驱动表的每一条记录一次性与 join buffer 中的所有驱动表记录进行匹配，将简单嵌套循环中的多次比较合并为一次，降低了被驱动表的访问频率。

• 这里缓存的不只是关联表的列， select 后面的列也会缓存起来。
• 在一个有 N 个 Join 关联的 SQL 中会分配 N - 1 个 join buffer。所以查询的时候尽量减少不必要的字段，可以让 join buffer 中存放更多的列。

4、Join 小结

• 整体效率比较：INLJ > BNLJ > SNLJ
• 永远用小结果集驱动大结果集
• 为驱动表匹配的条件字段添加索引
• 增大 join buffer size 的大小（一次缓存的数据越多，那么内层表的扫描次数就越少）
• 减少驱动表不必要的查询字段

5、Hash Join

从 MySQL 8.0.20 版本开始将废弃 BNLJ，因为从 8.0.18 版本开始就加入了 Hash Join

• Nested Loop：对于被连接的数据子集较小的情况，Nested Loop 是一个较好的选择
• Hash Join ：对于大数据集连接时的常见方式，优化器使用两个表中较小（相对较小）的表利用 Join Key 在内存中建立散列表，然后扫描较大的表并探测散列表，找出与 Hash 表匹配的行。
  • 这种方式适用于较小的表完全可以放到内存中的情况，这样总成本就是访问两个表的成本之和。
  • 在表很大不能完全放入内存的情况下，优化器会将其分割为若干个不同的分区，不能放入内存的部分就将该分区写入磁盘的临时段，此时要求有较大的临时段从而提高 IO 的性能。
  • 它能够很好地工作于没有索引的大表和并行查询的环境中，并提供最好的性能。Hash Join 只能应用于等值连接。

类别	Nested Loop	Hash Join
使用条件	任何条件	等值连接
相关资源	CPU、磁盘 IO	内存、临时空间
特点	当有高选择性索引或者限制性搜索时效率比较高，能够快速返回第一次的搜索结果	当缺乏索引或者索引条件模糊时，Hash Join 比 Nested Join 有效。在数据仓库环境下，如果表记录数多，效率高。
缺点	当索引丢失或者查询条件限制不够时，效率很低；当表记录数较多时，效率低。	建立哈希表需要大量内存。第一次的结果返回较慢，仅适用于等值连接。

6.3、SQL 优化

1、子查询优化

• 子查询执行效率不高的原因：

1. 执行子查询时，MySQL 需要为内层查询语句的查询结果建立一张临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销临时表。这样会消耗大量 CPU 和 IO 资源，产生大量的慢查询。
2. 子查询的结果集存储的临时表，无论是内存临时表还是物理临时表都不会存在索引，所以查询性能会受到影响。
3. 对于返回结果集较大的子查询，其对查询性能的影响也就越大。

在 MySQL 中，可以使用连接查询来代替子查询。连接查询不需要建立临时表，速度比子查询快，如果使用索引的话，性能就会更好。

2、排序优化

• 为什么要在 ORDER BY 字段上添加索引呢？

在 MySQL 中，支持两种排序方式，分别是 FileSort 和 Index 排序

1. Index 排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高
2. FileSort 排序则一般在内存中进行排序，占用 CPU 较多。如果待排序结果集较大，那么会产生临时文件 IO 到磁盘进行排序的情况，效率低

3、分页查询优化

一般分页查询时，通过创建覆盖索引能够比较好地提高性能，一个常见有非常头疼地问题就是 limit 2000000, 10 ，此时需要 MySQL 排序前 2000010 条数据，然后仅仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大。

• 优化思路一：

在索引上完成排序分页操作，最后根据主键关联回原表查询所需的其他列内容。

SELECT * FROM student as t , (SELECT id FROM student ORDER BY id LIMIT 2000000, 10) as a WHERE t.id = a.id;

• 优化思路二：

该方案适用于主键自增的表，可以把 LIMIT 查询转换为某个位置的查询。

SELECT * FROM student where id > 2000000 LIMIT 10

七、锁

事务的隔离性由锁来实现

7.1、概述

锁 是计算机协调多个进程或线程并发访问某一资源的机制，它由于保证这个资源在任何时刻最多只有一个线程访问，保证资源的一致性和完整性。

• 锁机制为实现 MySQL 的各个隔离级别提供了保证
• 锁冲突是影响数据库并发访问性能的一个重要因素

7.2、MySQL 并发事务访问相同的记录

1、读 - 读情况

读 - 读情况，即并发事务相机读取相同的记录。读取操作本身不会对记录产生任何影响，所以这种情况是允许的。

2、写 - 写情况

写 - 写情况，即并发事务相继对相同的记录做出改动。

• 在这种情况下会发生脏写的问题，任何一种隔离级别 都不允许这种问题的发生。

在多个未提交事务相继对一条记录做改动时，需要让它们排队执行，这个排队过程其实就是通过锁来执行的。

这个所谓的锁其实是一个内存中的结构，在事务执行前本来是没有所得，也就是说一开始没有锁结构与记录进行关联的，如图

当一个事务相对这条记录做改动时，首先会看看内存中有没有与这条记录关联的锁结构，如果没有，那么会创建一个锁结构与之关联。

• 我们来看看锁结构中两个比较重要的属性：

1. trx 信息：代表这个锁结构是由哪个事务生成的。
2. is_waiting：代表当前事务是否在等待

当事务 t1 改动这条记录后，就生成一个锁结构与之关联，因为之前没有其他事务对记录加锁，所以 is_waiting 为 false ，我们把这个场景称为获取锁成功，然后就可以继续执行操作。

在事务 t1 提交前，另一个事务 t2 也想对这个记录做改动，那么先看看有没有锁结构与这条记录相关联，发现有一个锁结构与之关联后，t2 也会生成一个锁结构与之关联，不过这个锁结构的 is_waiting 为 true 。表示当前事务需要等待，我们称这个现象为获取锁失败。

t1 提交事务后，会将该事务生成的锁结构释放，然后看看有没有其他事务在等待获取锁，如果有，那么会将对应事务生成的锁结构的 is_waiting 值为 false ，然后将该事务对应的线程唤醒，让它继续执行，此时 t2 就获取到锁了。

3、读 - 写或者写 - 读情况

这种情况下，可能会产生脏读、不可重复读 和 幻读 现象

7.3、锁的不同角度分类

7.4、读锁与写锁

对于数据库中并发事务的 读 - 读 情况并不会引起什么问题，对于 写 - 写 或 写 - 读 这些情况可能会引起一些问题，需要使用 MVCC 或者加锁的方式来解决。

由于既要允许读 - 读情况不会受到影响，又要使写 - 写、写 - 读情况中的操作相互阻塞，MySQL 实现了一个由两种类型的锁组成的所系统来解决。

这两类的锁通常被称为共享锁和排他锁，也就是读锁和写锁

• 读锁：也称为共享锁，英文用 S 表示。针对同一份数据，多个事务的读操作可以同时进行而不相互影响，不相互阻塞。
• 写锁：也称为排他锁，英文用 X 表示。当前写操作没有完成前，它会阻断其他写锁和读锁。这样就能确保在给定的时间内，只有一个事务能执行写入，并防止其他用户正在写入的同一资源

对于 InnoDB 存储引擎来说，读锁和写锁既可以加在表上，也可以加在行上。

	X 锁	S 锁
X 锁	不兼容	不兼容
S 锁	不兼容	兼容

• 锁定读

对于读行为（SELECT 语句），既可以加共享锁，也可以加排他锁。

1. 加共享锁

select ... lock in share mode;

2. 加排他锁

select ... for update;

• 写操作

平时用到的写操作无非是 DELETE 、 UPDATE 和 INSERT 三种

1. DELETE

对一条记录的 delete 操作其实是现在 B+ 树种定位到这条记录的位置，然后获取这条记录的 X 锁，再执行修改 delete mark 操作。

我们可以将这个定位待删除记录在 B+ 树中位置的过程看为一个获取 X 锁的锁定读。

2. UPDATE
   1. 未修改该记录的键值，并且被更新的列占用的存储空间在修改前后未发生改变；这种情况下则先在 B+ 树种定位该记录的位置，然后再获取一下记录的 X 锁，最后在原纪录的位置执行修改操作即可。
   2. 未修改该记录的键值，并且至少有一个被更新的列占用的存储空间在修改前后发生变化；这种情况则先在 B+ 树中定位该记录的位置，然后获取记录的 X 锁，将该记录彻底删除，最后再插入一条新纪录。
   3. 修改了该记录的键值；则相当于再原纪录做 DELETE 后再执行一次 INSERT 操作，加锁操作就按照 DELETE 和 INSERT 规则进行
3. INSERT

一般情况下，新插入一条记录的操作并不加锁，而是通过一种称为 隐式锁 的结构来保护这条新插入的数据在本事务提交前不被别的事务访问。

7.5、表级锁、页级锁和行锁

• 为了尽可能提高数据库的并发度，每次锁定的数据范围越小越好，理论上每次只锁定当前操作的数据的方案可以得到最大的并发，但是管理锁是非常消耗资源的事情。
• 所以数据库系统在高并发响应和系统平衡两方面进行平衡，也就产生了锁粒度的概念。

1、表锁

表锁会锁定整张表，是 MySQL 中最基本的所策略，它并不依赖于存储引擎，而且表锁是开销最小的策略，但同时也是并发性能最差的情况。

• 意向锁

InnoDB 支持多粒度锁，它允许行级锁和表级锁共存，而意向锁就是其中一种表锁

1. 意向锁的存在是为了协调行锁和表锁的关系，支持多粒度锁共存
2. 意向锁是一种不与行级锁冲突的表级锁
3. 它表示某个事务正在某些行持有了锁或者该事务准备去持有锁

意向锁分为意向共享锁和意向排他锁，意向锁主要解决如下问题：现在有两个事务，分别是 t1 和 t2，其中 t2 试图在该表级别上应用共享或排他锁，如果没有意向锁存在，那么 t2 就需要去检查各个页或行是否存在锁；如果存在意向锁，那么此时就会收到由 t1 控制的表级别意向锁的阻塞。t2 在锁定该表之前不必再去检查各个页或者行锁，而只需检查表上的意向锁。

4. 简单来说：就是给更大一级的空间示意里面是否已经加过锁。

如果我们给某一行数据添加了排他锁，那么数据库会自动给更大一级的空间，比如说数据页或者数据表加上意向锁，来告诉其他人这个数据页或者数据表已经有人加过排他锁了。这样当其他人想要获取数据表排他锁时，就只需要了解是否有人已经获取了这个数据表的意向排他锁即可。

5. 意向锁之间是兼容的，如下表

	意向共享锁	意向排他锁
意向共享锁	兼容	兼容
意向排他锁	兼容	兼容

6. 意向锁和普通锁的关系如下表

	意向共享锁	意向排他锁
共享锁	兼容	互斥
排他锁	互斥	互斥

7. 意向锁是表锁，不会与普通的行级锁（共享、排他）发生冲突，只会和普通的表级锁发生冲突（共享、排他）

• 自增锁

在使用 MySQL 的过程中，我们可以为表的某个列添加 AUTO_INCREMENT 属性，这意味着在插入语句中可以不需要为该列赋值。

插入数据总共由三种方式，分别为 简单插入 、 批量插入 和 混合模式插入

1. 简单插入

可以预先确定要插入的行数 的语句

2. 批量插入

事先不知道要插入的行数的语句，比如说 insert ... select,replace ... select 和 load data 语句，但不包含纯 insert ，InooDB 在每一处一行，都会为 AUTO_INCREMENT 列分配一个新的值

3. 混合模式插入

这些是简单插入语句但是指定部分新行的自动递增列，比如说 insert into teacher (id,name) values(1,'a'), (null, 'b')...

另一种混合类型插入是 insert ... on duplicate key update

对于上面数据插入的案例，MySQL 采用了自增锁的方式来实现，AUTO-INC 锁是当含有 AUTO_INCREMENT 列的表中插入记录时需要获取的一种特殊的表级锁，执行插入语句时就往表中添加一个 AUTO-INC 锁，然后为每条待插入记录的 AUTO_INCREMENT 的列分配递增的值，语句执行结束后再释放锁。

一个事务在持有 AUTO-INC 锁时，其他事务的插入都会被阻塞，这样可以保证一个语句中分配的递增值是连续的。

这样的并发潜力是很低下的，所以 InnoDB 提供了 innodb_autoinc_lock_mode 的不同取值来提供不同的锁定机制。

1. innodb_autoinc_lock_mode = 0（传统锁定模式）

2. innodb_autoinc_lock_mode = 1（连续锁定模式）

3. innodb_autoinc_lock_mode = 2（交错锁定模式）

• 元数据锁（MDL 锁）

它的作用是保证读写的正确性。

比如说，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程正在变更这个表的结构，那么查询线程拿到的结果可能与表结构对不上。

1. 当对一个表进行 CRUD 时，加 MDL 读锁；
2. 当对表做结构性变更时，加 MDL 写锁。

MDL 锁保证了 DML 和 DDL 操作之间的一致性问题，不需要显式使用

2、页锁

• 页锁就是在页的粒度上进行锁定，锁定粒度和并发度处于行锁和表锁之间，页锁也会出现死锁
• 每个层级的锁数量是有限制的，因为锁会占用内存空间，锁空间的大小是有限的。当某个层级的锁数量超过这个层级的阈值时，就会进行锁升级。

锁升级就是用更大粒度的锁代替多个小粒度的锁，这样做的好处是占用的锁空间降低，缺点是并发度也会随之下降。

3、行锁介绍

行锁，也称为记录锁，顾名思义，就是锁住某条记录。需要注意的是，MySQL 服务器层没有实现行锁机制，行级锁只在存储引擎层实现

• 优点：锁定粒度小，发生所冲突概率低，可以实现的并发度高。
• 缺点：对于锁的开销比较大，加锁比较慢，容易出现死锁

1. 记录锁

记录锁也就是仅仅把一条记录锁上，官方的类型名称为：LOCK_REC_NOT_GAP，记录锁仅仅对锁住的记录有作用，对周围其他的数据没有影响。

2. 间隙锁

MySQL 在可重复读隔离级别下可以解决幻读，解决方案有两种，一是 MVCC ，二是使用加锁方式解决。但是在加锁时有个问题需要解决，就是事务在第一次执行读操作时，那些幻影记录尚未存在，所以我们没办法给那些幻影 记录加上记录锁。

InnoDB 提出了一种名为间隙锁的锁，比如说，将 id 为 8 的记录加一个间隙锁的示意图如下：

图中 id 值为 8 的记录加了间隙锁，这意味着不允许别的事务在 id 为 8 的记录前边的间隙插入新纪录，也就是 id 列的值为（3，8）这个区间的新纪录是不允许立即插入的，只有这个拥有间隙锁的事务提交了之后，该区间内的新纪录才允许被插入。

间隙锁仅仅是为了防止插入幻影记录而提出的，虽然有共享间隙锁和排他间隙锁的说法，但是二者起到的作用是相同的。而且如果对一条记录加了间隙锁，并不会限制其他事务对这条记录加记录锁或者继续加间隙锁。

3. 临键锁（Next-key-lock）

有时候我们既想锁住某条记录，又想阻止其他事务在该记录前边的间隙插入新纪录，所以 InnoDB 就提出了一种称为临键锁的锁

临键锁的本质就是一个记录锁 + 间隙锁的合体。

4. 插入意向锁

我们说一个事务在插入一条记录时需要判断一下插入位置是否被其他事务加锁了，如果有的话，那么插入操作需要等待，直到拥有间隙锁的事务提交。

但是 InnoDB 规定事务在等待的时候也需要在内存中生成一个锁结构，表明该事务想在某个间隙中插入新纪录，但是现在在等待。

InnoDB 就将这种类型的锁命名为插入意向锁。这个锁用于表示插入意向，和多个事务在同意区间插入位置不同的多条数据时，事务之间不需要互相等待。

假设存在两条值分别为 4 和 7 的记录，两个不同的事务分别试图插入值为 5 和 6 的两条记录，每个事务在获取插入行上独占的锁前，都会获取 （4，7）之间的间隙锁，但由于数据之间并不冲突，所以两个事务之间不会产生冲突。

插入意向锁是一种特殊的间隙锁 – 间隙锁可以锁定开区间内的部分记录。

插入意向锁之间并不排斥，所以即使多个事务在同一区间插入多条记录，只要记录本身（主键、唯一索引）不冲突，那么事务之间就不会出现冲突。

插入意向锁是行锁。