mysql原理（mysql是怎样运行的）

一、MySQL的客户端／服务器架构：

1、mysql客户端服务端场景：

a.启动MySQL服务器程序
b.启动MySQL客户端程序并连接到服务器程序。
c.在客户端程序中输入一些命令语句作为请求发送到服务器程序，服务器程序收到这些请求后，会根据请求的内容来操作具体的数据并向客户端返回操作结果

2、mysql进程：

我们的MySQL服务器程序和客户端程序本质上都算是计算机上的一个进程，这个代表着MySQL服务器程序的进程也被称为MySQL数据库实例，简称数据库实例；
我们启动的MySQL服务器进程的默认名称为mysqld， 而我们常用的MySQL客户端进程的默认名称为mysql

3、启动mysql服务器程序：

（1）mysqld:

mysqld这个可执行文件就代表着MySQL服务器程序，运行这个可执行文件就可以直接启动一个服务器进程。但这个命令不常用

（2）mysqld_safe:

mysqld_safe是一个启动脚本，它会间接的调用mysqld，而且还顺便启动了另外一个监控进程，这个监控进程在服务器进程挂了的时候，可以帮助重启它。另外，使用mysqld_safe启动服务器程序时，它会将服务器程序的出错信息和其他诊断信息重定向到某个文件中，产生出错日志，这样可以方便我们找出发生错误的原因

（3）mysql.server:

mysql.server也是一个启动脚本，它会间接的调用mysqld_safe，在调用mysql.server时在后边指定start参数就可以启动服务器程序了，例如：
mysql.server start
需要注意的是，这个 mysql.server 文件其实是一个链接文件，它的实际文件是 …/support-files/mysql.server。我使用的macOS操作系统会帮我们在bin目录下自动创建一个指向实际文件的链接文件

（4）mysqld_multi：

一台计算机上也可以运行多个服务器实例，也就是运行多个MySQL服务器进程。mysql_multi可执行文件可以对每一个服务器进程的启动或停止进行监控

4、启动MySQL客户端程序：

启动方法：mysql -h主机名 -u用户名 -p密码
-h 表示服务器进程所在计算机的域名或者IP地址，如果服务器进程就运行在本机的话，可以省略这个参数，或者填localhost或者127.0.0.1。也可以写作 --host=主机名的形式。
-u 表示用户名。也可以写作 --user=用户名的形式。
-p 表示密码。也可以写作 --password=密码的形式。
例如：mysql -hlocalhost -uroot -p123456

5、客户端与服务器连接的过程：

本质上是一个进程间通信的过程！MySQL支持下边三种客户端进程和服务器进程的通信方式：

（1）TCP/IP:

MySQL采用TCP作为服务器和客户端之间的网络通信协议;
在网络环境下，每台计算机都有一个唯一的IP地址，如果某个进程有需要采用TCP协议进行网络通信方面的需求，可以向操作系统申请一个端口号，这是一个整数值，它的取值范围是0~65535。这样在网络中的其他进程就可以通过IP地址 + 端口号的方式来与这个进程连接，这样进程之间就可以通过网络进行通信了;
MySQL服务器会默认监听 3306 端口
在启动服务器程序的命令行里添加-P参数来明确指定一下端口号，
例如：mysqld -P3307

（2）命名管道和共享内存：

针对Windows用户，客户端进程和服务器进程之间可以考虑使用命名管道或共享内存进行通信；
在启动服务器程序和客户端程序时添加一些参数：

I. 使用命名管道来进行进程间通信：

需要在启动服务器程序的命令中加上–enable-named-pipe参数，然后在启动客户端程序的命令中加入–pipe或者–protocol=pipe参数。

II. 使用共享内存来进行进程间通信:

需要在启动服务器程序的命令中加上–shared-memory参数，在成功启动服务器后，共享内存便成为本地客户端程序的默认连接方式，不过我们也可以在启动客户端程序的命令中加入–protocol=memory参数来显式的指定使用共享内存进行通信。

不过需要注意的是，使用共享内存的方式进行通信的服务器进程和客户端进程必须在同一台Windows主机中

（3）Unix域套接字文件：

服务器进程和客户端进程都运行在同一台操作系统为类Unix的机器上的话，我们可以使用Unix域套接字文件来进行进程间通信；
在启动客户端程序的时候指定的主机名为localhost，或者指定了–protocol=socket的启动参数，那服务器程序和客户端程序之间就可以通过Unix域套接字文件来进行通信；
MySQL服务器程序默认监听的Unix域套接字文件路径为/tmp/mysql.sock

6、服务器处理客户端请求

处理流程：客户端进程向服务器进程发送一段文本（MySQL语句），服务器进程处理后再向客户端进程发送一段文本（处理结果）

（1）连接管理:

客户端进程可以采用我们上边介绍的TCP/IP、命名管道或共享内存、Unix域套接字这几种方式之一来与服务器进程建立连接，每当有一个客户端进程连接到服务器进程时，服务器进程都会创建一个线程来专门处理与这个客户端的交互，当该客户端退出时会与服务器断开连接，服务器并不会立即把与该客户端交互的线程销毁掉，而是把它缓存起来，在另一个新的客户端再进行连接时，把这个缓存的线程分配给该新客户端。这样就起到了不频繁创建和销毁线程的效果，从而节省开销。从这一点大家也能看出，MySQL服务器会为每一个连接进来的客户端分配一个线程，但是线程分配的太多了会严重影响系统性能，所以我们也需要限制一下可以同时连接到服务器的客户端数量；
在客户端程序发起连接的时候，需要携带主机信息、用户名、密码，服务器程序会对客户端程序提供的这些信息进行认证，如果认证失败，服务器程序会拒绝连接。另外，如果客户端程序和服务器程序不运行在一台计算机上，我们还可以采用使用了SSL（安全套接字）的网络连接进行通信，来保证数据传输的安全性。

当连接建立后，与该客户端关联的服务器线程会一直等待客户端发送过来的请求，MySQL服务器接收到的请求只是一个文本消息，该文本消息还要经过各种处理

（2）解析与优化:

MySQL服务器已经获得了文本形式的请求，后面还需要分别是查询缓存、语法解析和查询优化；

I. 查询缓存：

如果客户端A刚刚查询了一个语句，而客户端B之后发送了同样的查询请求，那么客户端B的这次查询就可以直接使用查询缓存中的数据；如果两个查询请求在任何字符上的不同（例如：空格、注释、大小写），都会导致缓存不会命中。另外，如果查询请求中包含某些系统函数、用户自定义变量和函数、一些系统表，如 mysql 、information_schema、 performance_schema 数据库中的表，那这个请求就不会被缓存；
MySQL的缓存系统会监测涉及到的每张表，只要该表的结构或者数据被修改，如对该表使用了INSERT、 UPDATE、DELETE、TRUNCATE TABLE、ALTER TABLE、DROP TABLE或 DROP DATABASE语句，那使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除；
虽然查询缓存有时可以提升系统性能，但也不得不因维护这块缓存而造成一些开销，比如每次都要去查询缓存中检索，查询请求处理完需要更新查询缓存，维护该查询缓存对应的内存区域。从MySQL 5.7.20开始，不推荐使用查询缓存，并在MySQL 8.0中删除

II. 语法解析：

如果查询缓存没有命中，接下来就需要进入正式的查询阶段了。因为客户端程序发送过来的请求只是一段文本而已，所以MySQL服务器程序首先要对这段文本做分析，判断请求的语法是否正确，然后从文本中将要查询的表、各种查询条件都提取出来放到MySQL服务器内部使用的一些数据结构上来；

III. 查询优化：

MySQL的优化程序会对我们的语句做一些优化，如外连接转换为内连接、表达式简化、子查询转为连接吧啦吧啦的一堆东西。优化的结果就是生成一个执行计划，这个执行计划表明了应该使用哪些索引进行查询，表之间的连接顺序是啥样的。我们可以使用EXPLAIN语句来查看某个语句的执行计划

（3）存储引擎:

MySQL服务器把数据的存储和提取操作都封装到了一个叫存储引擎的模块里；
连接管理、查询缓存、语法解析、查询优化这些并不涉及真实数据存储的功能划分为MySQL server的功能，把真实存取数据的功能划分为存储引擎的功能；
在MySQL server完成了查询优化后，只需按照生成的执行计划调用底层存储引擎提供的API，获取到数据后返回给客户端

I. 常用存储引擎.

II. 基本操作：

查看当前服务器程序支持的存储引擎：SHOW ENGINES;
创建表时指定存储引擎：mysql> CREATE TABLE engine_demo_table(
-> i int
-> ) ENGINE = MyISAM;
修改表的存储引擎： ALTER TABLE 表名 ENGINE = 存储引擎名称

二、启动选项和配置文件

1、在命令行使用选项：

（1）使用规则：

在命令行中指定启动选项时需要在选项名前加上–前缀；如果选项名是由多个单词构成的，它们之间可以由短划线-连接起来，也可以使用下划线_连接起来
例：
启动服务器程序的命令行里添加skip-networking启动选项：mysqld --skip-networking
表的默认存储引擎改为MyISAM：mysqld --default-storage-engine=MyISAM

（2）通用格式：

–启动选项1[=值1] --启动选项2[=值2] … --启动选项n[=值n]

a. 我们可以将各个启动选项写到一行中，各个启动选项之间使用空白字符隔开，在每一个启动选项名称前边添加–
b. 选项名、=、选项值之间不可以有空白字符
c. 每个MySQL程序都有许多不同的选项。大多数程序提供了一个–help选项，你可以查看该程序支持的全部启动选项以及它们的默认值

（3）选项的长形式与短形式：

2、配置文件中使用选项：

（1）背景：

在命令行中设置启动选项只对当次启动生效；把需要设置的启动选项都写在这个配置文件中，每次启动服务器的时候都从这个文件里加载相应的启动选项；
注意： 推荐使用配置文件的方式来设置启动选项

（2）特点：

在配置文件中指定启动选项的语法类似于命令行语法，但是配置文件中只能使用长形式的选项。在配置文件中指定的启动选项不允许加–前缀，并且每行只指定一个选项，而且=周围可以有空白字符（命令行中选项名、=、选项值之间不允许有空白字符）。另外，在配置文件中，我们可以使用#来添加注释，从#出现直到行尾的内容都属于注释内容，读取配置文件时会忽略这些注释内容

（3）注意点：

如果我们在多个配置文件中设置了相同的启动选项，那以最后一个配置文件中的为准；

3、命令行和配置文件中启动选项的区别：

（1）区分：

在命令行上指定的绝大部分启动选项都可以放到配置文件中，但是有一些选项是专门为命令行设计的；
比方说defaults-extra-file、defaults-file这样的选项本身就是为了指定配置文件路径的，再放在配置文件中使用就没啥意义了

（2）优先级：

如果同一个启动选项既出现在命令行中，又出现在配置文件中，那么以命令行中的启动选项为准

4、系统变量：

（1）系统变量简介：

MySQL服务器程序运行过程中会用到许多影响程序行为的变量，它们被称为MySQL系统变量；
比如：允许同时连入的客户端数量用系统变量max_connections表示，表的默认存储引擎用系统变量default_storage_engine表示，查询缓存的大小用系统变量query_cache_size表示

（2）特点：

每个系统变量都有一个默认值，我们可以使用命令行或者配置文件中的选项在启动服务器时改变一些系统变量的值。大多数的系统变量的值也可以在程序运行过程中修改，而无需停止并重新启动它

（3）查看系统变量：

SHOW VARIABLES [LIKE 匹配的模式];

（4）设置系统变量：

a. 通过启动选项设置:

通过命令行添加启动选项和通过配置文件添加启动选项

b. 服务器程序运行过程中设置:

对于大部分系统变量来说，它们的值可以在服务器程序运行过程中进行动态修改而无需停止并重启服务器。
不过系统变量有作用范围之分: 设置不同作用范围的系统变量和查看不同作用范围的系统变量

c.作用范围分为两种：

GLOBAL：全局变量，影响服务器的整体操作。
SESSION：会话变量，影响某个客户端连接的操作。（注：SESSION有个别名叫LOCAL）

通过启动选项设置的系统变量的作用范围都是GLOBAL的，也就是对所有客户端都有效的；
如果在设置系统变量的语句中省略了作用范围，默认的作用范围就是SESSION；
SHOW VARIABLES语句默认查看的是SESSION作用范围的系统变量

d. 注意点：

并不是所有系统变量都具有GLOBAL和SESSION的作用范围；
有些系统变量是只读的，并不能设置值

5、启动选项和系统变量的区别

（1）定义：

启动选项是在程序启动时我们程序员传递的一些参数，而系统变量是影响服务器程序运行行为的变量

（2）关系：

a. 大部分的系统变量都可以被当作启动选项传入。
b. 有些系统变量是在程序运行过程中自动生成的，是不可以当作启动选项来设置，比如auto_increment_offset、character_set_client
c. 有些启动选项也不是系统变量，比如defaults-file

6、状态变量：

（1）定义：

为了让我们更好的了解服务器程序的运行情况，MySQL服务器程序中维护了好多关于程序运行状态的变量，它们被称为状态变量

（2）特点：

由于状态变量是用来显示服务器程序运行状况的，所以它们的值只能由服务器程序自己来设置，我们程序员是不能设置的

与系统变量类似，状态变量也有GLOBAL和SESSION两个作用范围的，所以查看状态变量的语句可以这么写：
SHOW [GLOBAL|SESSION] STATUS [LIKE 匹配的模式];

三、字符集和比较规则

1、字符集

（1）编码和解码：

将一个字符映射成一个二进制数据的过程也叫做编码，将一个二进制数据映射到一个字符的过程叫做解码；
在计算机中只能存储二进制数据，需要建立字符与二进制数据的映射关系

（2）规则：

抽象出一个字符集的概念来描述某个字符范围的编码规则；
比如：‘bA’ -> 0000001000000011 (十六进制：0x0203)

2、比较规则：

（1）简单规则：

我们确定了xiaohaizi字符集表示字符的范围以及编码规则后，怎么比较两个字符的大小呢？最容易想到的就是直接比较这两个字符对应的二进制编码的大小；
这种简单的比较规则也可以被称为二进制比较规则

（2）同一种字符集可以有多种比较规则

3、一些重要字符集：

（1）ASCII字符集：

共收录128个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符。由于总共才128个字符，所以可以使用1个字节来进行编码；
比如：‘M’ -> 01001101（十六进制：0x4D，十进制：77）

（2）ISO 8859-1字符集：

共收录256个字符，是在ASCII字符集的基础上又扩充了128个西欧常用字符(包括德法两国的字母)，也可以使用1个字节来进行编码。这个字符集也有一个别名latin1

（3）GB2312字符集：

a. 收录了汉字以及拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母。其中收录汉字6763个，其他文字符号682个。同时这种字符集又兼容ASCII字符集；
b. 特点：如果该字符在ASCII字符集中，则采用1字节编码；否则采用2字节编码
c. 这种表示一个字符需要的字节数可能不同的编码方式称为变长编码方式

（4）GBK字符集：

GBK字符集只是在收录字符范围上对GB2312字符集作了扩充，编码方式上兼容GB2312

（5）utf8字符集:

收录地球上能想到的所有字符，而且还在不断扩充。这种字符集兼容ASCII字符集，采用变长编码方式，编码一个字符需要使用1～4个字节；
utf8只是Unicode字符集的一种编码方案，Unicode字符集可以采用utf8、utf16、utf32这几种编码方案，utf8使用1～4个字节编码一个字符，utf16使用2个或4个字节编码一个字符，utf32使用4个字节编码一个字符

4、MySQL中支持的字符集和排序规则：

（1）MySQL中的utf8和utf8mb4：

utf8字符集表示一个字符需要使用1～4个字节，但是我们常用的一些字符使用1～3个字节就可以表示；
utf8mb3(等价于utf8)：阉割过的utf8字符集，只使用1～3个字节表示字符
utf8mb4：正宗的utf8字符集，使用1～4个字节表示字符（存储一些emoji表情，可以使用utf8mb4）

（2）mysql支持的字符集：

查看： SHOW (CHARACTER SET|CHARSET) [LIKE 匹配的模式];

Default collation列表示这种字符集中一种默认的比较规则；
最后一列Maxlen，它代表该种字符集表示一个字符最多需要几个字节；

（3）比较规则：

a. 查看MySQL中支持的比较规则：SHOW COLLATION [LIKE 匹配的模式];
b. 一种字符集可能对应着若干种比较规则，utf8字符集下的比较规则：

c. 命令规则规律：
I. 比较规则名称以与其关联的字符集的名称开头。如上图的查询结果的比较规则名称都是以utf8开头的；
II. 后边紧跟着该比较规则主要作用于哪种语言，比如utf8_polish_ci表示以波兰语的规则比较，utf8_spanish_ci是以西班牙语的规则比较，utf8_general_ci是一种通用的比较规则；
III. 名称后缀意味着该比较规则是否区分语言中的重音、大小写

IIII. 每种字符集对应若干种比较规则，每种字符集都有一种默认的比较规则，SHOW COLLATION的返回结果中的Default列的值为YES的就是该字符集的默认比较规则，比方说utf8字符集默认的比较规则就是utf8_general_ci

5、mysql的各级别的字符集和比较规则：

（1）服务器级别：

MySQL提供了两个系统变量来表示服务器级别的字符集和比较规则：
a. character_set_server 服务器级别的字符集
b. collation_server 服务器级别的比较规则
可以在启动服务器程序时通过启动选项或者在服务器程序运行过程中使用SET语句修改这两个变量的值

（2）数据库级别:

在创建和修改数据库的时候可以指定该数据库的字符集和比较规则;
CREATE DATABASE 数据库名
[[DEFAULT] CHARACTER SET 字符集名称]
[[DEFAULT] COLLATE 比较规则名称];

ALTER DATABASE 数据库名
[[DEFAULT] CHARACTER SET 字符集名称]
[[DEFAULT] COLLATE 比较规则名称];

a. character_set_database 当前数据库的字符集
b. collation_database 当前数据库的比较规则
注意：character_set_database 和 collation_database 这两个系统变量是只读的，我们不能通过修改这两个变量的值而改变当前数据库的字符集和比较规则

（3）表级别：

在创建和修改表的时候指定表的字符集和比较规则；
CREATE TABLE 表名 (列的信息)
[[DEFAULT] CHARACTER SET 字符集名称]
[COLLATE 比较规则名称]]

ALTER TABLE 表名
[[DEFAULT] CHARACTER SET 字符集名称]
[COLLATE 比较规则名称]

（4）列级别：

对于存储字符串的列，同一个表中的不同的列也可以有不同的字符集和比较规则；
在创建和修改列定义的时候可以指定该列的字符集和比较规则：
CREATE TABLE 表名(
列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],
其他列…
);

ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];

（5）修改字符集或仅修改比较规则：

只修改字符集，则比较规则将变为修改后的字符集默认的比较规则。
只修改比较规则，则字符集将变为修改后的比较规则对应的字符集。

（6）各级别字符集和比较规则小结：

a. 如果创建或修改列时没有显式的指定字符集和比较规则，则该列默认用表的字符集和比较规则
b. 如果创建或修改表时没有显式的指定字符集和比较规则，则该表默认用数据库的字符集和比较规则
c. 如果创建或修改数据库时没有显式的指定字符集和比较规则，则该数据库默认用服务器的字符集和比较规则

6、客户端和服务器通信中的字符集：

（1）编码和解码使用的字符集不一致:

一个字节串，如果你使用不同字符集去解码这个字节串，最后得到的结果可能乱码

（2）字符集转换：

如果接收0xE68891这个字节串的程序按照utf8字符集进行解码，然后又把它按照gbk字符集进行编码，最后编码后的字节串就是0xCED2，我们把这个过程称为字符集的转换，也就是字符串’我’从utf8字符集转换为gbk字符集

（3）MySQL中字符集的转换：

从客户端发往服务器的请求本质上就是一个字符串，服务器向客户端返回的结果本质上也是一个字符串，而字符串其实是使用某种字符集编码的二进制数据。这个字符串可不是使用一种字符集的编码方式一条道走到黑的，从发送请求到返回结果这个过程中伴随着多次字符集的转换，在这个过程中会用到3个系统变量：
character_set_client 服务器解码请求时使用的字符集
character_set_connection 服务器处理请求时会把请求字符串从character_set_client转为character_set_connection
character_set_results 服务器向客户端返回数据时使用的字符集

（4）在请求从发送到结果返回过程中字符集的变化事例：

a. 客户端发送请求所使用的字符集:

一般情况下客户端所使用的字符集和当前操作系统一致，不同操作系统使用的字符集可能不一样：类Unix系统使用的是utf8，Windows使用的是gbk；
字符’我’在发送给服务器的请求中的字节形式就是：0xE68891

b. 服务器接收到客户端发送来的请求其实是一串二进制的字节:

它会认为这串字节采用的字符集是character_set_client，然后把这串字节转换为character_set_connection字符集编码的字符;
比如我的计算机上character_set_client的值是utf8，首先会按照utf8字符集对字节串0xE68891进行解码，得到的字符串就是’我’，然后按照character_set_connection代表的字符集，也就是gbk进行编码，得到的结果就是字节串0xCED2

c. 查找：

因为表t的列col采用的是gbk字符集，与character_set_connection一致，所以直接到列中找字节值为0xCED2的记录，最后找到了一条记录;

d. 解析：

上一步骤找到的记录中的col列其实是一个字节串0xCED2，col列是采用gbk进行编码的，所以首先会将这个字节串使用gbk进行解码，得到字符串’我’，然后再把这个字符串使用character_set_results代表的字符集，也就是utf8进行编码，得到了新的字节串：0xE68891，然后发送给客户端

e. 返回：

由于客户端是用的字符集是utf8，所以可以顺利的将0xE68891解释成字符我，从而显示到我们的显示器上，所以我们人类也读懂了返回的结果

以上步骤图解：

注意点：

I. 服务器认为客户端发送过来的请求是用character_set_client编码的，假设你的客户端采用的字符集和 character_set_client 不一样的话，这就会出现意想不到的情况；
II. 服务器将把得到的结果集使用character_set_results编码后发送给客户端，假设你的客户端采用的字符集和 character_set_results 不一样的话，这就可能会出现客户端无法解码结果集的情况，结果就是在你的屏幕上出现乱码。比如我的客户端使用的是utf8字符集，如果把系统变量character_set_results的值设置为ascii的话，可能会产生乱码；
III. character_set_connection只是服务器在将请求的字节串从character_set_client转换为character_set_connection时使用，它是什么其实没多重要，但是一定要注意，该字符集包含的字符范围一定涵盖请求中的字符，要不然会导致有的字符无法使用character_set_connection代表的字符集进行编码。比如你把character_set_client设置为utf8，把character_set_connection设置成ascii，那么此时你如果从客户端发送一个汉字到服务器，那么服务器无法使用ascii字符集来编码这个汉字，就会向用户发出一个警告

总结：

我们通常都把 character_set_client 、character_set_connection、character_set_results 这三个系统变量设置成和客户端使用的字符集一致的情况，这样减少了很多无谓的字符集转换，比如：
SET NAMES 字符集名;

7、比较规则的应用：

比较规则的作用通常体现比较字符串大小的表达式以及对某个字符串列进行排序中，所以有时候也称为排序规则；
在对字符串做比较或者对某个字符串列做排序操作时没有得到想象中的结果，需要思考一下是不是比较规则的问题

8、字符串和规则总结：

（1）字符集指的是某个字符范围的编码规则
（2）比较规则是针对某个字符集中的字符比较大小的一种规则。
（3）在MySQL中，一个字符集可以有若干种比较规则，其中有一个默认的比较规则，一个比较规则必须对应一个字符集
（4）查看MySQL中查看支持的字符集和比较规则的语句如下：
SHOW (CHARACTER SET|CHARSET) [LIKE 匹配的模式];
SHOW COLLATION [LIKE 匹配的模式];
（5）MySQL有四个级别的字符集和比较规则：
服务器级别、数据库级别、表级别、列级别
（6）发送请求到接收请求：

（7）比较规则的作用通常体现比较字符串大小的表达式以及对某个字符串列进行排序中

四、InnoDB记录存储结构

1、innodb简介：

（1）存储位置：

innoDB是一个将表中的数据存储到磁盘上的存储引擎，所以即使关机后重启我们的数据还是存在的。而真正处理数据的过程是发生在内存中的，所以需要把磁盘中的数据加载到内存中，如果是处理写入或修改请求的话，还需要把内存中的内容刷新到磁盘上

（2）存储方式：

InnoDB采取的方式是：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为 16 KB。也就是在一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中

2、InnoDB行格式：

（1）定义：

以记录为单位来向表中插入数据的，这些记录在磁盘上的存放方式也被称为行格式或者记录格式

（2）指定行格式的语法：

CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称
ALTER TABLE 表名 ROW_FORMAT=行格式名称

（3）4种不同类型的行格式：

Compact、Redundant、Dynamic和Compressed行格式

（4）CPMPACT格式：

a. 一条完整的记录其实可以被分为记录的额外信息和记录的真实数据两大部分

b. 记录的额外信息：

这部分信息是服务器为了描述这条记录而不得不额外添加的一些信息，这些额外信息分为3类，分别是变⻓字段⻓度列表、NULL值列表 和记录头信息；

I.变⻓字段⻓度列表:

拥有这些数据类型的列称为变⻓字段（比如VARCHAR(M)、VARBINARY(M)、各种TEXT类型，各种BLOB类型），变⻓字段中存储 多少字节的数据是不固定的，所以我们在存储真实数据的时候需要顺 便把这些数据占用的字节数也存起来，这样才不至于把MySQL服务器 搞懵，所以这些变⻓字段占用的存储空间分为两部分（真正的数据内容 和占用的字节数）
在Compact行格式中，把所有变⻓字段的真实数据占用的字节⻓度 都存放在记录的开头部位，从而形成一个变⻓字段⻓度列表，各变⻓字段数据占用的字节数按照列的顺序逆序存放

总结：
如果该可变字段允许存储的最大字节数(M×W)超 过255字节并且真实存储的字节数(L)超过127字节，则使用2个字 节，否则使用1个字节；

变⻓字段⻓度列表中只存储值为 非NULL 的列内容占用的⻓度，值为 NULL 的列的⻓度是不储存的，并不是所有记录都有这个 变⻓字段⻓度列表 部分，比方说表中所 有的列都不是变⻓的数据类型的话，这一部分就不需要有

II.NULL值列表:

Compact行格式把这些值 为NULL的列统一管理起来，存储到NULL值列表中;

处理过程:

1. 首先统计表中允许存储NULL的列有哪些;

2. 如果表中没有允许存储 NULL 的列，则 NULL值列表 也不存在 了，否则将每个允许存储NULL的列对应一个二进制位，二进制 位按照列的顺序逆序排列:
   二进制位的值为1时，代表该列的值为NULL;
   二进制位的值为0时，代表该列的值不为NULL

3. MySQL规定NULL值列表必须用整数个字节的位表示，如果使用 的二进制位个数不是整数个字节，则在字节的高位补0

示例：

c. 记录头信息：

用于描述记录 的记录头信息，它是由固定的5个字节组成。5个字节也就是40个二 进制位，不同的位代表不同的意思

示例：

d.记录的真实数据:

记录的真实数据除了我们自己定义的列的数据以外，MySQL会为 每个记录默认的添加一些列(也称为隐藏列)

I.主要包括：

II.InnoDB表对主键的生成策略:

优先使用用户自定义 主键作为主键，如果用户没有定义主键，则选取一个Unique键作为 主键，如果表中连Unique键都没有定义的话，则InnoDB会为表默 认添加一个名为row_id的隐藏列作为主键。所以我们从上表中可以 看出:InnoDB存储引擎会为每条记录都添加 transaction_id 和 roll_pointer 这两个列，但是 row_id 是可选的(在没有自定义主 键以及Unique键的情况下才会添加该列)

III. CHAR(M)列的存储格式

record_format_demo表的c1、c2、c4列的类型 是VARCHAR(10)，而c3列的类型是CHAR(10)，我们说在Compact 行格式下只会把变⻓类型的列的⻓度逆序存到变⻓字段⻓度列表中：

这只是因为我们的record_format_demo表采用的是ascii字 符集，这个字符集是一个定⻓字符集，也就是说表示一个字符采用固 定的一个字节，如果采用变⻓的字符集(也就是表示一个字符需要的 字节数不确定，比如gbk表示一个字符要1~2个字节、utf8表示一 个字符要1~3个字节等)的话，c3列的⻓度也会被存储到变⻓字段 ⻓度列表中；

修改该列字符集后记录的变⻓字段⻓度列表也发生了变化：

总结：
对于 CHAR(M) 类型的列来说，当列采用的是定⻓字符集时，该列占用的字节数不会被加到变⻓字段⻓度列表，而如果采用变⻓字符集时，该列占用的字节数也会被加到变⻓字段⻓度列表；

变⻓字符集的CHAR(M)类型的列要求至少 占用M个字节，而VARCHAR(M)却没有这个要求

（5）Redundant行格式：

a. Compact行格式的开头是变⻓字段⻓度列表， 而Redundant行格式的开头是字段⻓度偏移列表，与变⻓字段 ⻓度列表有两处不同：

I.没有了变⻓两个字，意味着Redundant行格式会把该条 记录中所有列(包括隐藏列)的⻓度信息都按照逆序存储 到字段⻓度偏移列表

II. 多了个偏移两个字，这意味着计算列值⻓度的方式不像 Compact行格式那么直观，它是采用两个相邻数值的差 值来计算各个列值的⻓度

III. 因为Redundant行格式并没有NULL值列表，所以需要别的方 式来存储字段的NULL值

IV: Redundant行格式的记录头信息占用6字节，48个二进制位

b. CHAR(M)列的存储格式:

Compact行格式在CHAR(M)类型的列中存储数据的时候还 挺麻烦，分变⻓字符集和定⻓字符集的情况，而在Redundant行格 式中十分干脆，不管该列使用的字符集是啥，只要是使用CHAR(M) 类型，占用的真实数据空间就是该字符集表示一个字符最多需要的字 节数和M的乘积。比方说使用utf8字符集的CHAM(10)类型的列占用 的真实数据空间始终为30个字节，使用gbk字符集的CHAM(10)类型 的列占用的真实数据空间始终为20个字节。由此可以看出来，使 用Redundant行格式的CHAR(M)类型的列是不会产生碎片的

（6）Dynamic和Compressed行格式：

这俩 行格式和Compact行格式挺像，只不过在处理行溢出数据时有点儿 分歧，它们不会在记录的真实数据处存储字段真实数据的前768个字 节，而是把所有的字节都存储到其他⻚面中，只在记录的真实数据处 存储其他⻚面的地址：

Compressed行格式和Dynamic不同的一点是，Compressed行格 式会采用压缩算法对⻚面进行压缩，以节省空间；

3、行格式总结：

（1）⻚是MySQL中磁盘和内存交互的基本单位，也是MySQL是管理 存储空间的基本单位。

（2）指定和修改行格式的语法：

CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名 称
ALTER TABLE 表名 ROW_FORMAT=行格式名称

（3）InnoDB目前定义了4中行格式：

Dynamic和Compressed行格式：
这两种行格式类似于COMPACT行格式，只不过在处理行 溢出数据时有点儿分歧，它们不会在记录的真实数据处存 储字符串的前768个字节，而是把所有的字节都存储到其 他⻚面中，只在记录的真实数据处存储其他⻚面的地址。
另外，Compressed行格式会采用压缩算法对⻚面进行压 缩。

（4）行溢出：

一个⻚一般是16KB，当记录中的数据太多，当前⻚放不下的时候，会把多余的数据存储到其他⻚中，这种现象称为行溢出

五、InnoDB 数据⻚结构

1、索引(INDEX)⻚(数据页):

存放我们表中记录的那种类型的⻚

2、数据页结构示意图：

（1）示意图：

（2）结构解释：

一个InnoDB数据⻚的存储空间大致被划分成了7 个部分，有的部分占用的字节数是确定的，有的部分占用的字节数是 不确定的

（3）记录在页中的存储：

在⻚的7个组成部分中，我们自己存储的记录会按照我们指定的行格 式存储到User Records部分。但是在一开始生成⻚的时候，其实 并没有User Records这个部分，每当我们插入一条记录，都会从 Free Space部分，也就是尚未使用的存储空间中申请一个记录大小 的空间划分到User Records部分，当Free Space部分的空间全 部被User Records部分替代掉之后，也就意味着这个⻚使用完 了，如果还有新的记录插入的话，就需要去申请新的⻚了：

3、记录头信息：

（1）记录头结构：

上一节compact行格式示例：

简化后：

（2）分析实例：

I.新建一张表：

CREATE TABLE page_demo( c1 INT,
c2 INT,
c3 VARCHAR(10000),
PRIMARY KEY (c1)
) CHARSET=ascii ROW_FORMAT=Compact;

II.插入数据：

INSERT INTO page_demo VALUES(1, 100, ‘aaaa’), (2, 200, ‘bbbb’), (3, 300, ‘cccc’), (4, 400, ‘dddd’);

III.User Records部分结构表示：

IV: 分析records部分各个参数：

a. delete_mask：

这个属性标记着当前记录是否被删除，占用1个二进制位，值 为0的时候代表记录并没有被删除，为1的时候代表记录被删除掉了；被删除的记录还在⻚中，这些被删除的记录之所以不立即 从磁盘上移除，是因为移除它们之后把其他的记录在磁盘上重 新排列需要性能消耗，所以只是打一个删除标记而已，所有被 删除掉的记录都会组成一个所谓的垃圾链表，在这个链表中的 记录占用的空间称之为所谓的可重用空间，之后如果有新记录 插入到表中的话，可能把这些被删除的记录占用的存储空间覆盖掉

b. min_rec_mask：

B+树的每层非叶子节点中的最小记录都会添加该标记

c. n_owned：

表示当前记录拥有的记录数

d. heap_no：

表示当前记录在本⻚中的位置，我们插入的4条记录在本⻚中的位置分别是:2、3、4、5；InnoDB会自动给 每个⻚里边儿加了两个记录，由于这两个记录并不是我们自己 插入的，所以有时候也称为伪记录或者虚拟记录，这两个伪记录一个代表最小记录，一个代表最大记录（利用主键比较大小），这两条记录的构造十分简单，都是由5字节大小的记录头 信息和8字节大小的一个固定的部分组成的：

由于这两条记录不是我们自己定义的记录，所以它们并不存放 在⻚的User Records部分，他们被单独放在一个称 为Infimum + Supremum的部分：

最小记录和最大记录的heap_no值分 别是0和1，也就是说它们的位置最靠前

e. record_type:

这个属性表示当前记录的类型，一共有4种类型的记录，0表示 普通记录，1表示B+树非叶节点记录，2表示最小记录，3表示最大记录; 我们自己插入的记录就 是普通记录，它们的record_type值都是0，而最小记录和最 大记录的record_type值分别为2和3

f. next_record:

它表示从当前记录的真实数据到下一条记 录的真实数据的地址偏移量；比方说第一条记录的 next_record值为32，意味着从第一条记录的真实数据的地 址处向后找32个字节便是下一条记录的真实数据（其实就是链表，可以通过一 条记录找到它的下一条记录）

注意：
下一条记录指得并不是按照我们插入顺序的下一条记录， 而是按照主键值由小到大的顺序的下一条记录；Infimum记录(也就是最小记录) 的下一条记录就本⻚中主 键值最小的用户记录，而本⻚中主键值最大的用户记录的下一 条记录就是 Supremum记录(也就是最大记录)，如图所示：

我们的记录按照主键从小到大的顺序形成 了一个单链表，最大记录的next_record的值为0，这也就是 说最大记录是没有下一条记录了，它是这个单链表中的最后一 个节点。如果从中删除掉一条记录，这个链表也是会跟着变化的，如果删除第二条记录，会变为：

第2条记录并没有从存储空间中移除，而是把该条记录的 delete_mask值设置为1；第2条记录的next_record值变为了0，意味着该记录没 有下一条记录了；第1条记录的next_record指向了第3条记录；最大记录的n_owned值从5 变成了4

小结：
不论我们怎么对⻚中的记录做增删改操作，InnoDB始终 会维护一条记录的单链表，链表中的各个节点是按照主键值由 小到大的顺序连接起来的；
当数据⻚中存在多条被删除掉的记录时，这些记录的next_record 属性将会把这些被删除掉的记录组成一个垃圾链表，以备之后重用 这部分存储空间。

4、Page Directory(⻚目录)：

（1）目录设计：

将所有正常的记录(包括最大和最小记录，不包括标记为已删除的记录)划分为几个组；
每个组的最后一条记录(也就是组内最大的那条记录)的头信 息中的n_owned属性表示该记录拥有多少条记录，也就是该组内共有几条记录；
将每个组的最后一条记录的地址偏移量单独提取出来按顺序存 储到靠近⻚的尾部的地方，这个地方就是所谓的Page Directory，也就是⻚目录；⻚面目录中的这些地址偏移量被称为槽，所以这个⻚面目录就是由槽组成的；

（2）分析：

InnoDB会把它们分成两组，第一组中只有一个最小记录，第二组中是剩余的5条记录：

现在⻚目录部分中有两个槽，也就意味着我们的记录被分成了 两个组，槽0中的值是112，代表最大记录的地址偏移量(就是 从⻚面的0字节开始数，数112个字节);槽1中的值是99，代表最小记录的地址偏移量；

最小和最大记录的头信息中的n_owned属性：
最小记录的n_owned值为1，这就代表着以最小记录结尾 的这个分组中只有1条记录，也就是最小记录本身；
最大记录的n_owned值为5，这就代表着以最大记录结尾 的这个分组中只有5条记录，包括最大记录本身还有我们 自己插入的4条记录

直观展示：

（3）分组规定：

对于最小记录所在的分组只能有 1 条记录，最大记录所在的分组拥 有的记录条数只能在 1-8条之间，剩下的分组中记录的条数范围只 能在是 4~8 条之间

分组步骤：
I. 初始情况下一个数据⻚里只有最小记录和最大记录两条记录， 它们分属于两个分组；
II. 之后每插入一条记录，都会从⻚目录中找到主键值比本记录的 主键值大并且差值最小的槽，然后把该槽对应的记录的 n_owned值加1，表示本组内又添加了一条记录，直到该组中 的记录数等于8个；
III. 在一个组中的记录数等于8个后再插入一条记录时，会将组中 的记录拆分成两个组，一个组中4条记录，另一个5条记录。这 个过程会在⻚目录中新增一个槽来记录这个新增分组中最大的 那条记录的偏移量

插入16条记录后：

各个槽代表的记录的主键值都是 从小到大排序的，所以我们可以使用所谓的二分法来进行快速查找。 4个槽的编号分别是:0、1、2、3、4，所以初始情况下最低的槽就 是low=0，最高的槽就是high=4

（4）在一个数据⻚中查找指定主键值的记录的过程:

通过二分法确定该记录所在的槽；
通过记录的next_record属性遍历该槽所在的组中的各个记录

5、Page Header(⻚面头部)：

（1）概念：

设计InnoDB的大叔们为了能得到一个数据⻚中存储的记录的状态信 息，比如本⻚中已经存储了多少条记录，第一条记录的地址是什么，⻚目录中存储了多少个槽等等，特意在⻚中定义了一个叫Page Header的部分，它是⻚结构的第二部分，这个部分占用固定的56个字节，专⻔存储各种状态信息

（2）具体字节：

6、File Header(文件头部)：

（1）概念：

File Header针对各种类型的⻚都通用，也就是说不同类型的⻚都 会以File Header作为第一个组成部分，它描述了一些针对各种⻚ 都通用的一些信息，比方说这个⻚的编号是多少，它的上一个⻚、下 一个⻚是谁， 这个部分占用固定的38个字节

（2）组成部分：

重要部分：

I. FIL_PAGE_SPACE_OR_CHKSUM：

这个代表当前⻚面的校验和(checksum)，校验和就是对于一个很⻓很⻓的字节串来说，我们会通过某种算法来 计算一个比较短的值来代表这个很⻓的字节串，这个比较短的 值就称为校验和；这样在比较两个很⻓的字节串之前先比较这 两个⻓字节串的校验和，如果校验和都不一样两个⻓字节串肯 定是不同的，所以省去了直接比较两个比较⻓的字节串的时间 损耗

II. FIL_PAGE_OFFSET:

每一个⻚都有一个单独的⻚号，就跟你的身份证号码一 样，InnoDB通过⻚号来可以唯一定位一个⻚

III. FIL_PAGE_TYPE:

这个代表当前⻚的类型

IV. FIL_PAGE_PREV和FIL_PAGE_NEXT

FIL_PAGE_PREV和 FIL_PAGE_NEXT就分别代表本⻚的上一个和下一个⻚的⻚ 号。这样通过建立一个双向链表把许许多多的⻚就都串联起来 了，而无需这些⻚在物理上真正连着；并不是 所有类型的⻚都有上一个和下一个⻚的属性，数据⻚(也就是类型为FIL_PAGE_INDEX的⻚)是有这 两个属性的，所以所有的数据⻚其实是一个双链表：

7、File Trailer：

（1）概念：

为 了检测一个⻚是否完整(也就是在同步的时候有没有发生只同步一半 的尴尬情况)，设计InnoDB的大叔们在每个⻚的尾部都加了一 个File Trailer部分，这个部分由8个字节组成；与FILE Header类似，都是所有类型的⻚通用
的。

（2）组成：

I. 前4个字节代表⻚的校验和:

这个部分是和File Header中的校验和相对应的。每当一个 ⻚面在内存中修改了，在同步之前就要把它的校验和算出来， 因为File Header在⻚面的前边，所以校验和会被首先同步 到磁盘，当完全写完时，校验和也会被写到⻚的尾部，如果完 全同步成功，则⻚的首部和尾部的校验和应该是一致的。如果 写了一半儿断电了，那么在File Header中的校验和就代表 着已经修改过的⻚，而在File Trialer中的校验和代表着原 先的⻚，二者不同则意味着同步中间出了错

II. 后4个字节代表⻚面被最后修改时对应的日志序列位置(LSN):

这个部分也是为了校验⻚的完整性的

8、总结：

（1） InnoDB为了不同的目的而设计了不同类型的⻚，我们把用于存 放记录的⻚叫做数据⻚

（2）一个数据⻚可以被大致划分为7个部分：

File Header，表示⻚的一些通用信息，占固定的38字 节。
Page Header，表示数据⻚专有的一些信息，占固定的 56个字节。
Infimum + Supremum，两个虚拟的伪记录，分别表示 ⻚中的最小和最大记录，占固定的26个字节。
User Records:真实存储我们插入的记录的部分，大 小不固定。
Free Space:⻚中尚未使用的部分，大小不确定。 Page Directory:⻚中的某些记录相对位置，也就是 各个槽在⻚面中的地址偏移量，大小不固定，插入的记录 越多，这个部分占用的空间越多。
File Trailer:用于检验⻚是否完整的部分，占用固 定的8个字节

（3）每个记录的头信息中都有一个next_record属性，从而使⻚ 中的所有记录串联成一个单链表

（4）InnoDB会为把⻚中的记录划分为若干个组，每个组的最后一 个记录的地址偏移量作为一个槽，存放在Page Directory 中，所以在一个⻚中根据主键查找记录是非常快：

通过二分法确定该记录所在的槽；
通过记录的next_record属性遍历该槽所在的组中的各个 记录

（5）每个数据⻚的File Header部分都有上一个和下一个⻚的编 号，所以所有的数据⻚会组成一个双链表

（6）为保证从内存中同步到磁盘的⻚的完整性，在⻚的首部和尾部 都会存储⻚中数据的校验和和⻚面最后修改时对应的LSN值， 如果首部和尾部的校验和和LSN值校验不成功的话，就说明同 步过程出现了问题

六、B+树索引：

1、InnoDB数据⻚格式：

各个数 据⻚可以组成一个双向链表，而每个数据⻚中的记录会按照主键值从 小到大的顺序组成一个单向链表，每个数据⻚都会为存储在它里边儿 的记录生成一个⻚目录，在通过主键查找某条记录的时候可以在⻚目 录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的 记录即可快速找到指定的记录

其中⻚a、⻚b、⻚c … ⻚n 这些⻚可以不在物理结构上相连，只要 通过双向链表相关联即可

2、在一个⻚中查找数据方法：

（1）以主键为搜索条件：

这个查找过程我们已经很熟悉了，可以在⻚目录中使用二分法 快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可 快速找到指定的记录

（2）以其他列作为搜索条件：

对非主键列的查找的过程可就不这么幸运了，因为在数据⻚中 并没有对非主键列建立所谓的⻚目录，所以我们无法通过二分 法快速定位相应的槽。这种情况下只能从最小记录开始依次遍 历单链表中的每条记录，然后对比每条记录是不是符合搜索条 件。很显然，这种查找的效率是非常低的

3、在很多⻚中查找：

在很多⻚中查找记录的话可以分为两个步骤:
（1）定位到记录所在的⻚
（2）从所在的⻚内中查找相应的记录
在没有索引的情况下，不论是根据主键列或者其他列的值进行查找，
由于我们并不能快速的定位到记录所在的⻚，所以只能从第一个⻚沿 着双向链表一直往下找因为要遍历所有的数据⻚，所以这种方式显然是超级耗时的

4、索引：

（1）新建一个示例表：

CREATE TABLE index_demo( c1 INT,
c2 INT,
c3 CHAR(1),
PRIMARY KEY(c1)
) ROW_FORMAT = Compact;

（2）对应行格式示意图为：

record_type:记录头信息的一项属性，表示记录的类型，0 表示普通记录、2表示最小记录、3表示最大记录、1是目录项记录
next_record:记录头信息的一项属性，表示下一条地址相对于本条记录的地址偏移量
各个列的值:这里只记录在index_demo表中的三个列，分别是c1、c2和c3。
其他信息:除了上述3种信息以外的所有信息，包括其他隐藏列的值以及记录的额外信息

（3）把多条记录放到⻚里边的示意图：

（4）索引方案：

为快速定位记录所在的数据⻚而建立一个别的目录，需要满足条件：

I. 下一个数据⻚中用户记录的主键值必须大于上一个⻚中用户记 录的主键值

在对⻚中的记录进行增删改操作的过程中，我 们必须通过一些诸如记录移动的操作来始终保证这个状态一直 成立:下一个数据⻚中用户记录的主键值必须大于上一个⻚中 用户记录的主键值。这个过程我们也可以称为⻚分裂

II. 给所有的⻚建立一个目录项（索引）:
每个⻚对应一个目录项，每个目录项包括两个部分:
⻚的用户记录中最小的主键值，我们用key来表示
⻚号，我们用page_no表示

以⻚28为例，它对应目录项2，这个目录项中包含着该⻚的⻚ 号28以及该⻚中用户记录的最小主键值5。我们只需要把几个 目录项在物理存储器上连续存储，比如把他们放到一个数组 里，就可以实现根据主键值快速查找某条记录的功能了。比方 说我们想找主键值为20的记录，具体查找过程分两步:

1. 先从目录项中根据二分法快速确定出主键值为20的记录 在目录项3中(因为 12 < 20 < 209)，它对应的⻚是 ⻚9。
2. 再根据前边说的在⻚中查找记录的方式去⻚9中定位具体 的记录。

（5）InnoDB中的索引方案：

I. 复用 了之前存储用户记录的数据⻚来存储目录项，为了和用户记录做一下 区分，我们把这些用来表示目录项的记录称为目录项记录（record_type=1）
下图新分配了一个编号为30的⻚来专⻔存储目 录项记录

II. 目录项记录和普通的用户记录的不同点:
目录项记录的record_type值是1，而普通用户记录的 record_type值是0；
目录项记录只有主键值和⻚的编号两个列，而普通的用户记录 的列是用户自己定义的，可能包含很多列，另外还有InnoDB 自己添加的隐藏列；
记录头信息中的min_rec_mask的属性，只有在存储目录项记录的⻚中的主 键值最小的目录项记录的min_rec_mask值为1，其他别的记 录的min_rec_mask值都是0

III.升级结构（B+树）：
实际用户记录其实都存放在 B+树的最底层的节点上，这些节点也被称为叶子节点或叶节点，其 余用来存放目录项的节点称为非叶子节点或者内节点，其中B+树最 上边的那个节点也称为根节点

5、聚簇索引：

（1）聚簇索引特点：

B+树本身就是一个目录，或者说本身就是一个索引
它有两个特点：
I. 使用记录主键值的大小进行记录和⻚的排序:
⻚内的记录是按照主键的大小顺序排成一个单向链表;
各个存放用户记录的⻚也是根据⻚中用户记录的主键大小顺序排成一个双向链表;
存放目录项记录的⻚分为不同的层次，在同一层次中的⻚也是根据⻚中目录项记录的主键大小顺序排成一个双向链表

II. B+树的叶子节点存储的是完整的用户记录:
完整的用户记录，就是指这个记录中存储了所有列的值 (包括隐藏列)

（2）索引结构：

在InnoDB存储引擎中，聚簇索引就是数据的存储方式 (所有的用户记录都存储在了叶子节点)，也就是所谓的索引即数 据，数据即索引

6、二级索引：

（1）来源：

需要根据其他列搜索，不同的B+树中的数据采用不同的排序 规则。比方说我们用c2列的大小作为数据⻚、⻚中记录的排序规 则，再建一棵B+树

（2）二级索引与聚簇索引不同点:

I. 使用记录c2列的大小进行记录和⻚的排序:
⻚内的记录是按照c2列的大小顺序排成一个单向链表
各个存放用户记录的⻚也是根据⻚中记录的c2列大小顺
序排成一个双向链表
存放目录项记录的⻚分为不同的层次，在同一层次中的⻚ 也是根据⻚中目录项记录的c2列大小顺序排成一个双向 链表

II. B+树的叶子节点存储的并不是完整的用户记录，而只是c2列 +主键这两个列的值

III. 目录项记录中不再是主键+⻚号的搭配，而变成了c2列+⻚号的搭配

IV. B+树的叶子节点中的记录只存储了c2和c1(也就是主键)两个列，所以我们必须再根据主键值去聚簇索引中再查 找一遍完整的用户记录；这个过程也被称为回表。也就是根据c2列的值查询一条完整的用户 记录需要使用到2棵B+树

7、联合索引：

（1）概念：

同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，

（2）示例：

比方说我们想让B+树按照c2和c3列的大小进行排序（先把各个记录和⻚按照c2列进行排序，在记录的c2列相同的情况下，采用c3列进行排序）

每条目录项记录都由c2、c3、⻚号这三个部分组成，各条记录 先按照c2列的值进行排序，如果记录的c2列相同，则按照c3 列的值进行排序；
B+树叶子节点处的用户记录由c2、c3和主键c1列组成

8、其他注意：

（1）我们需要保证在B+树的 同一层内节点的目录项记录除⻚号这个字段以外是唯一的；
二级索引的内节点的目录项记录的内容实际上是由三个部分构成的:
索引列的值、主键值、⻚号

我们把主键值也添加到二级索引内节点中的目录项记录了，这 样就能保证B+树每一层节点中各条目录项记录除⻚号这个字段外是 唯一的

9、MyISAM中的索引方案：

（1）结构：

MyISAM的索引方案虽然也使用树形结 构，但是却将索引和数据分开存储：
将表中的记录按照记录的插入顺序单独存储在一个文件中，称 之为数据文件。这个文件并不划分为若干个数据⻚，有多少记 录就往这个文件中塞多少记录就成了。我们可以通过行号而快 速访问到一条记录

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

（2）使用MyISAM存储引擎的表会把索引信息另外存储到一个称为 索引文件的另一个文件中；
MyISAM会单独为表的主键创建一 个索引，只不过在索引的叶子节点中存储的不是完整的用户记 录，而是主键值 + 行号的组合。也就是先通过索引找到对应 的行号，再通过行号去找对应的记录；
在InnoDB存储引擎中，我 们只需要根据主键值对聚簇索引进行一次查找就能找到对应的 记录，而在MyISAM中却需要进行一次回表操作，意味 着MyISAM中建立的索引相当于全部都是二级索引；

如果有需要的话，我们也可以对其它的列分别建立索引或者建 立联合索引，原理和InnoDB中的索引差不多，不过在叶子节 点处存储的是相应的列 + 行号。这些索引也全部都是二级索 引。

10、总结

（1）对于InnoDB存储引擎来说，在单个⻚中查找某条记录分为两种情况：
以主键为搜索条件，可以使用Page Directory通过二 分法快速定位相应的用户记录
以其他列为搜索条件，需要按照记录组成的单链表依次遍 历各条记录

（2）没有索引的情况下，不论是以主键还是其他列作为搜索条件， 只能沿着⻚的双链表从左到右依次遍历各个⻚

（3）InnoDB存储引擎的索引是一棵B+树，完整的用户记录都存储 在B+树第0层的叶子节点，其他层次的节点都属于内节点，内 节点里存储的是目录项记录。InnoDB的索引分为两大种:

聚簇索引（以主键值的大小为⻚和记录的排序规则，在叶子节点处存 储的记录包含了表中所有的列）
二级索引（以自定义的列的大小为⻚和记录的排序规则，在叶子节点 处存储的记录内容是列 + 主键）

（4）MyISAM存储引擎的数据和索引分开存储，这种存储引擎的索 引全部都是二级索引，在叶子节点处存储的是列 + ⻚号

七、B+树索引的使用：

1、索引的代价：

（1）时间上：

每次对表中的数据进行增、删、改操作时，都需要去修改各 个B+树索引。而且我们讲过，B+树每层节点都是按照索引列的 值从小到大的顺序排序而组成了双向链表。不论是叶子节点中 的记录，还是内节点中的记录(也就是不论是用户记录还是目 录项记录)都是按照索引列的值从小到大的顺序而形成了一个 单向链表。而增、删、改操作可能会对节点和记录的排序造成 破坏，所以存储引擎需要额外的时间进行一些记录移位，⻚面 分裂、⻚面回收啥的操作来维护好节点和记录的排序。如果我 们建了许多索引，每个索引对应的B+树都要进行相关的维护操作；
一个表上索引建的越多，就会占用越多的存储空间，在增删 改记录的时候性能就越差

（2）空间上：

每建立一个索引都为要它建立一棵B+树， 每一棵B+树的每一个节点都是一个数据⻚，一个⻚默认会占 用16KB的存储空间，一棵很大的B+树由许多数据⻚组成，那 可是很大的一片存储空间

2、B+树索引适用的条件：

（1）联合索引：

CREATE TABLE person_info(
id INT NOT NULL auto_increment,
name VARCHAR(100) NOT NULL,
birthday DATE NOT NULL,
phone_number CHAR(11) NOT NULL,
country varchar(100) NOT NULL,
PRIMARY KEY (id),
KEY idx_name_birthday_phone_number (name,
birthday, phone_number) );

这 个idx_name_birthday_phone_number索引对应的B+树中⻚面 和记录的排序方式就是这样的:
先按照name列的值进行排序。 如果name列的值相同，则按照birthday列的值进行排序。 如果birthday列的值也相同，则按照phone_number的值进
行排序

（2）全值匹配：

如果我们的搜索条件中的列和索引列一致的话，这种情况就称为全值匹配

SELECT * FROM person_info WHERE name = ‘Ashburn’ AND birthday = ‘1990-09-27’ AND phone_number = ‘15123983239’;

按照顺序，三个列索引都能用到

（顺序换后也能用到，查询优化器会分析这些搜索条件并且按照可以使用的索引中列的顺序来决定先使用哪个搜索条件，后使用哪个搜索条件）

（3）匹配左边的列

在我们的搜索语句中也可以不用包含全部联合索引中的列，只包含左边的或者包含多个左边的列就行
SELECT * FROM person_info WHERE name = ‘Ashburn’ AND birthday = ‘1990-09-27’;

（4）匹配列前缀：

为某个列建立索引的意思其实就是在对应的B+树的记 录中使用该列的值进行排序；
字符串排序的本质就是比较哪个字符串大一点儿，哪个字符串小一 点，比较字符串大小就用到了该列的字符集和比较规则；
一般的比较规则都 是逐个比较字符的大小，也就是说我们比较两个字符串的大小（先按照字符串的第一个字符进行排序，如果第一个字符相同再按照第二个字符进行排序，如果第二个字符相同再按照第三个字符进行排序，依此类推）
也就是说这些字符串的前n个字符，也就是前缀都是排好序的，所以 对于字符串类型的索引列来说，我们只匹配它的前缀也是可以快速定 位记录的

注意：如果只给出后缀或者中间的某个字符串，MySQL就无法快速定位记录位置了，因为字符串中间有’%s’的字符 串并没有排好序，所以只能全表扫描了,如果需要匹配后缀为 .com ,可以考虑把表中的数据全部逆序存储一下，然后 WHERE url LIKE ‘moc%’。可以使用索引

（5）匹配范围值

所有记录都是按照索引列的值从小到大的顺序排好序的，方便我们查找索引列的值在某个范围内的记录，比如：
SELECT * FROM person_info WHERE name > ‘Asa’ AND name < ‘Barlow’;

注意：
如果对多个列同时进 行范围查找的话，只有对索引最左边的那个列进行范围查找的时候才 能用到B+树索引；

SELECT * FROM person_info WHERE name > ‘Asa’ AND name < ‘Barlow’ AND birthday > ‘1980-01-01’;

只能用到name列索引，而用不到birthday列的部分，因为只 有name值相同的情况下才能用birthday列的值进行排序，而这个 查询中通过name进行范围查找的记录中可能并不是按照birthday 列进行排序的，所以在搜索条件中继续以birthday列进行查找时是 用不到这个B+树索引的

（6）精确匹配某一列并范围匹配另外一列：

对于同一个联合索引来说，虽然对多个列都进行范围查找时只能用到 最左边那个索引列，但是如果左边的列是精确查找，则右边的列可以 进行范围查找，比如：
ELECT * FROM person_info WHERE name = ‘Ashburn’ AND birthday > ‘1980-01-01’ AND birthday < ‘2000- 12-31’ AND phone_number > ‘15100000000’;
可以用到name列和birthday列索引

（7）用于排序

在写查询语句的时候经常需要对查询出来的记录通过ORDER BY 子句按照某种规则进行排序；
在MySQL中，把这种在内存中或者磁盘上进行排序的方式统称为文件 排序（filesort），但是如果ORDER BY子句里使用到了我们的索引列，就有 可能省去在内存或文件中排序的步骤，比如：
SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10;

（8）使用联合索引进行排序注意事项：

ORDER BY的子句后边的列的顺序 也必须按照索引列的顺序给出，颠倒顺序就不能使用索引

（9）不可以使用索引进行排序的几种情况：

a. ASC、DESC混用：
对于使用联合索引进行排序的场景，我们要求各个排序列的排序顺序 是一致的，也就是要么各个列都是ASC规则排序，要么都是DESC规则排序;

使用联合索引的各个排序列的排序顺序必须是一致的

b. WHERE子句中出现非排序使用到的索引列:

如果WHERE子句中出现了非排序使用到的索引列，那么排序依然是
使用不到索引的，比如：
SELECT * FROM person_info WHERE country = ‘China’ ORDER BY name LIMIT 10;

c. 排序列包含非同一个索引的列:
有时候用来排序的多个列不是一个索引里的，这种情况也不能使用索 引进行排序，比如：
SELECT * FROM person_info ORDER BY name, country LIMIT 10;

d. 排序列使用了复杂的表达式:

要想使用索引进行排序操作，必须保证索引列是以单独列的形式出 现，而不是修饰过的形式,比如：
SELECT * FROM person_info ORDER BY UPPER(name) LIMIT 10;

（10）用于分组：

有时候我们为了方便统计表中的一些信息，会把表中的记录按照某些 列进行分组；比如：
SELECT name, birthday, phone_number, COUNT(*) FROM person_info GROUP BY name, birthday, phone_number

如果没有索引的话，这个分组过程 全部需要在内存里实现，而如果有了索引的话，恰巧这个分组顺序又 和我们的B+树中的索引列的顺序是一致的，而我们的B+树索引又是 按照索引列排好序的，可以直接使用B+树索引进 行分组

3、回表的代价

（1）示例：

SELECT * FROM person_info WHERE name > ‘Asa’ AND name < ‘Barlow’;

（2）顺序IO：

由于索引idx_name_birthday_phone_number对应的B+树中的 记录首先会按照name列的值进行排序，所以值在Asa~Barlow之间 的记录在磁盘中的存储是相连的，集中分布在一个或几个数据⻚中， 我们可以很快的把这些连着的记录从磁盘中读出来，这种读取方式我 们也可以称为顺序I/O

（3）随机IO：

根据二级索引获取到的记录的id字段的值可能并不相连，而在聚簇索引中记录是根据id(也就是主键)的顺序 排列的，所以根据这些并不连续的id值到聚簇索引中访问完整的用 户记录可能分布在不同的数据⻚中，这样读取完整的用户记录可能要 访问更多的数据⻚，这种读取方式我们也可以称为随机I/O

（4）比较总结：

顺序I/O比随机I/O的性能高很多；
会使用到两个B+树索引，一个二级索引，一个聚簇索引；
访问二级索引使用顺序I/O，访问聚簇索引使用随机I/O
需要回表的记录越多，使用二级索引的性能就越低，甚至让某些查询 宁愿使用全表扫描也不使用二级索引

（5）扫描方式选择：

什么时候采用全表扫描的方式，什么使用采用二级索引 + 回表的 方式去执行查询；
这个就是查询优化器做的工作，查询优化器会事先对表中的记录计算一些统计数据，然后再利用这些统计数 据根据查询的条件来计算一下需要回表的记录数，需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用二级索引 + 回表 的方式；
一般情况下，限制查询获取较少的记录数会让优化器更 倾向于选择使用二级索引 + 回表的方式进行查询，因为回表的记录 越少，性能提升就越高

4、索引覆盖：

（1）背景：

为了彻底告别回表操作带来的性能损耗，我们建议:最好在查询列表 里只包含索引列；比如：
SELECT name, birthday, phone_number FROM person_info WHERE name > ‘Asa’ AND name < ‘Barlow’

（2）概念：

这种只需要 用到索引的查询方式称为索引覆盖

5、如何挑选索引：

（1）只为用于搜索、排序或分组的列创建索引：

只为出现在WHERE子句中的列、连接子句中的连接列，或 者出现在ORDER BY或GROUP BY子句中的列创建索引。而出现在查 询列表中的列就没必要建立索引了

（2）考虑列的基数：

列的基数指的是某一列中不重复数据的个数；
记录行数一定的情况下，列的基数越大，该 列中的值越分散，列的基数越小，该列中的值越集中；
列的基数 指标非常重要，直接影响我们是否能有效的利用索引；

最好为那些列的基数大的列建立索引，为基数太小列的 建立索引效果可能不好

（3）索引列的类型尽量小：

该类型表示的数 据范围的大小。能表示的整数范围当然也是依次递增，如果我们想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量 让索引列使用较小的类型，比如我们能使用INT就不要使 用BIGINT，能使用MEDIUMINT就不要使用INT；原因在于：

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

（4）索引字符串值的前缀：

假设我们的字符串很⻓，那存储一个字符串就需要占用很大的 存储空间。在我们需要为这个字符串列建立索引时，会有问题：
B+树索引中的记录需要把该列的完整字符串存储起来，而且字 符串越⻓，在索引中占用的存储空间越大；
如果B+树索引中索引列存储的字符串很⻓，那在做字符串比较 时会占用更多的时间

解决：
索引的 设计者提出了个方案 — 只对字符串的前几个字符进行索引也就是 说在二级索引的记录中只保留字符串前几个字符

在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值，再 对比就好了。这样只在B+树中存储字符串的前几个字符的编码，既 节约空间，又减少了字符串的比较时间，还大概能解决排序的问题

比如：
CREATE TABLE person_info(
name VARCHAR(100) NOT NULL,
birthday DATE NOT NULL,
phone_number CHAR(11) NOT NULL,
country varchar(100) NOT NULL,
KEY idx_name_birthday_phone_number (name(10),
birthday, phone_number) );

name(10)就表示在建立的B+树索引中只保留记录的前10个字符的 编码，这种只索引字符串值的前缀的策略是我们非常鼓励的，尤其是 在字符串类型能存储的字符比较多的时候

（5）索引列前缀对排序的影响：

如果使用了索引列前缀，比方说前边只把name列的前10个字符放到 了二级索引中
SELECT * FROM person_info ORDER BY name LIMIT 10;

因为二级索引中不包含完整的name列信息，所以无法对前十个字符 相同，后边的字符不同的记录进行排序，也就是使用索引列前缀的方 式无法支持使用索引排序

（6）让索引列在比较表达式中单独出现：

如果索引列在比较表达式中不是以单独列的形式出现，而是以某个表达式，或者函数调用形式出现的话，是用不到索引的
WHERE my_col * 2 < 4改为WHERE my_col < 4/2

6、主键插入顺序：

（1）问题：

如果我们插入的记录的主键值是依 次增大的话，那我们每插满一个数据⻚就换到下一个数据⻚继续插， 而如果我们插入的主键值忽大忽小的话，这就比较麻烦，带来性能损耗

（2）解决：

最好让插入的记录的主键值依次递增， 这样就不会发生这样的性能损耗了。建议让主键具 有AUTO_INCREMENT，让存储引擎自己为表生成主键，而不是我们手动插入

7、总结：

（1）B+树索引在空间和时间上都有代价

（2）B+树索引适用于下边这些情况:

全值匹配
 匹配左边的列
 匹配范围值
 精确匹配某一列并范围匹配另外一列
 用于排序

用于分组

（3）在使用索引时需要注意下边这些事项:

只为用于搜索、排序或分组的列创建索引 为列的基数大的列创建索引
索引列的类型尽量小
可以只对字符串值的前缀建立索引 只有索引列在比较表达式中单独出现才可以适用索引 为了尽可能少的让聚簇索引发生⻚面分裂和记录移位的情 况，建议让主键拥有AUTO_INCREMENT属性。 定位并删除表中的重复和冗余索引 尽量适用覆盖索引进行查询，避免回表带来的性能损耗

八、MySQL 的数据目录：

1、概念：

（1）MySQL服务器程序在启动时会到文件系统的某个目录下加载一些文 件，之后在运行过程中产生的数据也都会存储到这个目录下的某些文 件中，这个目录就称为数据目录
（2）数据目录是用来存储MySQL在运行过程中产生的数据；
安装目录下非常重要的bin目录， 它里边存储了许多关于控制客户端程序和服务器程序的命令(许多可 执行文件，比如mysql，mysqld，mysqld_safe等等等等好几十个）

2、表在文件系统中的表示：

InnoDB和MyISAM这两种存储引擎都在数据目录下对应的数据 库子目录下创建了一个专⻔用于描述表结构的文件，文件名为：表名.frm

这个后缀名为.frm是以二进 制格式存储的，我们直接打开会是乱码的

（1）表结构的定义

（2）表中的数据

3、InnoDB是如何存储表数据的：

一个表空间或 者文件空间(英文名:table space或者file space)的概念， 这个表空间是一个抽象的概念，它可以对应文件系统上一个或多个真 实文件(不同表空间对应的文件数量可能不同)。每一个表空间可以 被划分为很多很多很多个⻚，我们的表数据就存放在某个表空间下的 某些⻚里

（1）系统表空间：

系统表空间可以对应文件系统上一个或多个实际的文件，InnoDB会在数据目录下创建一个名为ibdata1(在你 的数据目录下找找看有木有)、大小为12M的文件，这个文件就是对 应的系统表空间在文件系统上的表示；

注意：
在一个MySQL服务器中，系统表空间只有一 份。从MySQL5.5.7到MySQL5.6.6之间的各个版本中，我们表中的 数据都会被默认存储到这个 系统表空间

（2）独立表空间(file-per-table tablespace)

在MySQL5.6.6以及之后的版本中，InnoDB并不会默认的把各个表 的数据存储到系统表空间中，而是为每一个表建立一个独立表空间， 也就是说我们创建了多少个表，就有多少个独立表空间。使用独立表 空间来存储表数据的话，会在该表所属数据库对应的子目录下创建一 个表示该独立表空间的文件，文件名和表名相同，只不过添加了一 个.ibd的扩展名而已，
文件名：表名.ibd；

因此创建一个test表后会产生test.frm和test.ibd两个文件；
test.ibd文件就用来存储test表中的数据和索引

（3）其他类型的表空间

通用表空间(general tablespace)、undo表空间(undo tablespace)、临时表空间 (temporary tablespace)

4、MyISAM是如何存储表数据的：

（1）概念：

在文件系统中也是使用不同的文件来存储数据文件 和索引文件。而且和InnoDB不同的是，MyISAM并没有什么所谓的 表空间一说，表数据都存放到对应的数据库子目录下

（2）示例：

新建test表，会为test表创建这三个文件：test.frm、test.MYD和test.MYI
test.MYD代表表的数据文件，也就是我们插入的用户记 录;test.MYI代表表的索引文件，我们为该表创建的索引都会放到 这个文件中

5、其他的文件：

除了我们上边说的这些用户自己存储的数据以外，数据目录下还包括 为了更好运行程序的一些额外文件，主要包括这几种类型的文件：

（1）服务器进程文件：

每运行一个MySQL服务器程序，都意味着启动一个进
程。MySQL服务器会把自己的进程ID写入到一个文件中

（2）服务器日志文件：

在服务器运行过程中，会产生各种各样的日志，比如常规的查 询日志、错误日志、二进制日志、redo日志等各种日 志，这些日志各有各的用途

（3）默认/自动生成的SSL和RSA证书和密钥文件：

主要是为了客户端和服务器安全通信而创建的一些文件

6、MySQL系统数据库简介：

MySQL的几个系统数据库，这几个数据库包含了 MySQL服务器运行过程中所需的一些信息以及一些运行状态信息

（1）mysql：

这个数据库核心，它存储了MySQL的用户账户和权限信息， 一些存储过程、事件的定义信息，一些运行过程中产生的日志 信息，一些帮助信息以及时区信息等

（2）information_schema：

这个数据库保存着MySQL服务器维护的所有其他数据库的信 息，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索 引吧啦吧啦。这些信息并不是真实的用户数据，而是一些描述 性信息，有时候也称之为元数据

（3）performance_schema：

这个数据库里主要保存MySQL服务器运行过程中的一些状态信 息，算是对MySQL服务器的一个性能监控。包括统计最近执行 了哪些语句，在执行过程的每个阶段都话费了多⻓时间，内存 的使用情况等等信息

（4）sys：

这个数据库主要是通过视图的形式把information_schema 和performance_schema结合起来，让程序员可以更方便的 了解MySQL服务器的一些性能信息

7、总结：

（1）对于InnoDB、MyISAM这样的存储引擎会把数据存储到文件系 统上
（2）数据目录和安装目录是两个东⻄
（3）每个数据库都对应数据目录下的一个子目录
（4）表在文件系统上表示分两部分：
表结构的定义：不论是InnoDB还是MyISAM，都会在数据库子目录下创
建一个和表名同名的.frm文件
表中的数据：针对InnoDB和MyISAM对于表数据有不同的存储方式
（5）对于InnoDB存储引擎来说，使用表空间来存储表中的数据：

表空间分两种类型:
I. 系统表空间：默认情况下，InnoDB将所有的表数据都存储到这个系统 表空间内，它是一个抽象的概念，实际可以对应着文件系 统中若干个真实文件

II. 独立表空间:
如果有需要的话，可以为每个表分配独立的表空间，只需 要在启动服务器的时候将innodb_file_per_table参 数设置为1即可。每个表的独立表空间对应的文件系统中 的文件是在数据库子目录下的与表名同名的.ibd文件

九、单表访问方法：

1、概念：

把MySQL执行查询语句的方式称之为访问方法或者 访问类型

2、const：

通过主键或 者唯一二级索引列来定位一条记录的访问方法定义为:const
常数级别的，代价是可以忽略不计

对于唯一二级索引来说，查询该列为NULL值的情况比较特殊：
因为唯一二级索引列并不限制NULL值的数量，所以上述语句可能访 问到多条记录，也就是说上边这个语句不可以使用const访问方法来执行

3、ref：

（1）对某个普通的二级索引列与常数进行等值比较；

搜索条件为二级索引列与常数等值比较，采用二级索引来执 行查询的访问方法称为:ref

（2）二级索引列值为NULL的情况:

不论是普通的二级索引，还是唯一二级索引，它们的索引列对 包含NULL值的数量并不限制，所以我们采用key IS NULL这 种形式的搜索条件最多只能使用ref的访问方法，而不 是const的访问方法

（3）对于某个包含多个索引列的二级索引来说，只要是最左边的连 续索引列是与常数的等值比较就可能采用ref的访问方法，但是如果最左边的连续索引列并不全部是等值比较的话，它的 访问方法就不能称为ref了

4、ref_or_null：

有时候我们不仅想找出某个二级索引列的值等于某个常数的记录，还 想把该列的值为NULL的记录也找出来，比如：
SELECT * FROM single_demo WHERE key1 = ‘abc’ OR key1 IS NULL;

当使用二级索引而不是全表扫描的方式执行该查询时，这种类型的查 询使用的访问方法就称为ref_or_null

查询相当于先分别从idx_key1索引对应的B+树中 找出key1 IS NULL和key1 = 'abc’的两个连续的记录范围，然 后根据这些二级索引记录中的id值再回表查找完整的用户记录

5、range：

把这种利用索引进行范围匹配的访问方法称之 为:range

6、index：

直接遍历二级索引比直接遍历聚簇索引的成本要小很多，设 计MySQL的大叔就把这种采用遍历二级索引记录的执行方式称之 为:index

7、all：

直接扫描聚簇索引，使用全表扫描执行查询的方式称之为:all

8、eq_ref:

把在连接查询中对被驱动表 使用主键值或者唯一二级索引列的值进行等值查找的查询执行 方式称之为:eq_ref

十、连接join的原理：

1、join的概念：

（1）连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入 结果集并返回给用户
（2）在两表连接查询中，驱动 表只需要访问一次，被驱动表可能被访问多次
（3）内连接：
对于内连接的两个表，驱动表中的记录在被驱动表中找不到匹 配的记录，该记录不会加入到最后的结果集
（4）外连接：
对于外连接的两个表，驱动表中的记录即使在被驱动表中没有 匹配的记录，也仍然需要加入到结果集；
在MySQL中，根据选取驱动表的不同，外连接仍然可以细分为 2种:
左外连接（选取左侧的表为驱动表）
右外连接（选取右侧的表为驱动表）

WHERE子句中的过滤条件：
WHERE子句中的过滤条件就是我们平时⻅的那种，不论是内连 接还是外连接，凡是不符合WHERE子句中的过滤条件的记录都 不会被加入最后的结果集

ON子句中的过滤条件：
对于外连接的驱动表的记录来说，如果无法在被驱动表中找到 匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到 结果集中，对应的被驱动表记录的各个字段使用NULL值填充；这个ON子句是专⻔为外连接驱动表中的记录在被驱动表找不到匹配记录时应不应该把该记录加入结果集这个 场景下提出的，所以如果把ON子句放到内连接中，MySQL会把
它和WHERE子句一样对待，也就是说:内连接中的WHERE子句和ON子句是等价的

（5）我们都把只涉及单表的过滤条件放到WHERE子句中，把 涉及两表的过滤条件都放到ON子句中，我们也一般把放到ON子句中 的过滤条件也称之为连接条件

（6）左（右）连接：
SELECT * FROM t1 LEFT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];
对于LEFT JOIN类型的 连接来说，我们把放在左边的表称之为外表或者驱动表，右边的表称 之为内表或者被驱动表；

对于左(外)连接 和右(外)连接来说，必须使用ON子句来指出连接条件

（7）内连接：
内连接和外连接的根本区别就是在驱动表中的记录不符合ON子句中 的连接条件时不会把该记录加入到最后的结果集

SELECT * FROM t1 JOIN t2;
SELECT * FROM t1 INNER JOIN t2;
SELECT * FROM t1 CROSS JOIN t2;

注意：由于在内连接中ON子句和WHERE子句是等价的，所以内连接中不要求强制写明ON子句；
对于内连接来说，驱动表和被驱动表是可以互换的，并不会影响 最后的查询结果；
对于外连接来说，由于驱动表中的记录即使在 被驱动表中找不到符合ON子句连接条件的记录，所以此时驱动表和 被驱动表的关系就很重要了，也就是说左外连接和右外连接的驱动表 和被驱动表不能轻易互换

2、连接的原理：

（1）嵌套循环连接(Nested-Loop Join)：

这个过程就像是一个嵌套的循环，所以这种驱动表只访问一次，但被 驱动表却可能被多次访问，访问次数取决于对驱动表执行单表查询后 的结果集中的记录条数的连接执行方式称之为嵌套循环连接 (Nested-Loop Join)，这是最简单，也是最笨拙的一种连接查询算法

（2）使用索引加快连接速度：

查询t2表其实就相当于一次单表扫描，我们可以利用索引来加快查询速度

（3）基于块的嵌套循环连接(Block Nested-Loop Join)：

尽量减少访问 被驱动表的次数；
把被驱动表的记录加载到内 存的时候，一次性和多条驱动表中的记录做匹配，这样就可以大大减少重复从磁盘上加载被驱动表的代价了；

join buffer：
join buffer就是执行连接查询前 申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在 这个join buffer中，然后开始扫描被驱动表，每一条被驱动表的 记录一次性和join buffer中的多条驱动表记录做匹配，因为匹配 的过程都是在内存中完成的，所以这样可以显著减少被驱动表的I/O 代价

最好的情况是join buffer足够大，能容纳驱动表结果集中的所有 记录，这样只需要访问一次被驱动表就可以完成连接操作了。把这种加入了join buffer的嵌套循环连接算法称之 为基于块的嵌套连接(Block Nested-Loop Join)算法
这个join buffer的大小是可以通过启动参数或者系统变量 join_buffer_size进行配置，默认大小为262144字节(也就 是256KB)，最小可以设置为128字节；

驱动表的记录并不是所有列都会被放到join buffer中，只有查询列表中的列和过滤条件中的列才会被放到join buffer中

十一、基于成本的优化

1、MYSQL查询成本：

（1）I/O成本：

我们的表经常使用的MyISAM、InnoDB存储引擎都是将数据和 索引都存储到磁盘上的，当我们想查询表中的记录时，需要先 把数据或者索引加载到内存中然后再操作。这个从磁盘到内存 这个加载的过程损耗的时间称之为I/O成本

（2）CPU成本：

读取以及检测记录是否满足对应的搜索条件、对结果集进行排
序等这些操作损耗的时间称之为CPU成本

2、执行计划：

在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行 该语句所有可能使用的方案，对比之后找出成本最低的方案，这个成 本最低的方案就是所谓的执行计划，之后才会调用存储引擎提供的接口真正的执行查询

（1）根据搜索条件，找出所有可能使用的索引：

设 计MySQL的大叔把一个查询中可能使用到的索引称之为possible keys

（2）计算全表扫描的代价：

对于InnoDB存储引擎来说，全表扫描的意思就是把聚簇索引中的记 录都依次和给定的搜索条件做一下比较，把符合搜索条件的记录加入 到结果集，所以需要将聚簇索引对应的⻚面加载到内存中，然后再检 测记录是否符合搜索条件；

查询成本=I/O成本+CPU成本，所 以计算全表扫描的代价需要两个信息:
聚簇索引占用的⻚面数和该表中的记录数

（3）计算使用不同索引执行查询的代价

（4）对比各种执行方案的代价，找出成本最低的那一个

3、基于索引统计数据的成本计算：

（1）设计MySQL的大叔把这种通过直接 访问索引对应的B+树来计算某个范围区间对应的索引记录条数的方式称之为index dive；

index dive就是直接利用索引对应的B+树来计算某个范围区间对应的记录 条数。

（2）当IN语句中的参数个数大于或等于系统变量 eq_range_index_dive_limit的值的话，就不会使用index dive的方式计算各个单点区间对应的索引记录条数，而是使用索引统计数据
（3）索引统计数据指的是这两个值:
使用SHOW TABLE STATUS展示出的Rows值，也就是一个表 中有多少条记录；
使用SHOW INDEX语句展示出的Cardinality属性

结合上一个Rows统计数据，我们可以针对索引列，计算出平均
一个值重复多少次：一个值的重复次数 ≈ Rows ÷ Cardinality

4、连接查询的成本：

（1）Condition filtering介绍：

MySQL中连接查询采用的是嵌套循环连接算法，驱动 表会被访问一次，被驱动表可能会被访问多次，所以对于两表连接查询来说，
它的查询成本由下边两个部分构成：单次查询驱动表的成本和多次查询被驱动表的成本

（2）在这两种情况下计算驱动表扇出值时需 要靠猜（condition filtering）:

如果使用的是全表扫描的方式执行的单表查询，那么计算驱动 表扇出时需要猜满足搜索条件的记录到底有多少条；

如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时 候需要猜满足除使用到对应索引的搜索条件外的其他搜索条件 的记录有多少条

（3）两表连接的成本分析：

连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单 次访问被驱动表的成本

（4）优化重点其实是下边这两个部分：

尽量减少驱动表的扇出；
对被驱动表的访问成本尽量低

小结：我们需要 尽量在被驱动表的连接列上建立索引，这样就可以使用ref访 问方法来降低访问被驱动表的成本了。如果可以，被驱动表的 连接列最好是该表的主键或者唯一二级索引列，这样就可以把 访问被驱动表的成本降到更低了

十二、InnoDB 统计数据是如何收集的：

1、两种不同的统计数据存储方式：

（1）InnoDB提供了两种存储统计数据的方式:

永久性的统计数据：这种统计数据存储在磁盘上，也就是服务器重启之后这些统计 数据还在；

非永久性的统计数据：这种统计数据存储在内存中，当服务器关闭时这些统计数据就都被清除掉了，等到服务器重启之后，在某些适当的场景 下才会重新收集这些统计数据

（2）innodb_stats_persistent来控制到底采用哪种方式去存储统计数据：

在MySQL 5.6.6之前，innodb_stats_persistent的值默认是OFF，也就是说InnoDB的统计数据默认是存储到内存的，之后的版本中innodb_stats_persistent的值默认是ON，也就是统 计数据默认被存储到磁盘中

InnoDB默认是以表为单位来收集和存储统计数据的，也就是说 我们可以把某些表的统计数据(以及该表的索引统计数据)存储在磁 盘上，把另一些表的统计数据存储在内存中

2、基于磁盘的永久性统计数据：

（1）当我们选择把某个表以及该表索引的统计数据存放到磁盘上时，实际 上是把这些统计数据存储到了两个表里:
innodb_index_stats 和 innodb_table_stats

两个表都位于mysql系统数据库下边，innodb_table_stats存储了关于表的统计数据，每一条记录对应着一个表的统计数据；
innodb_index_stats存储了关于索引的统计数据，每一条 记录对应着一个索引的一个统计项的统计数据

3、总结：

（1）InnoDB以表为单位来收集统计数据，这些统计数据可以是基 于磁盘的永久性统计数据，也可以是基于内存的非永久性统计 数据

（2）innodb_stats_persistent控制着使用永久性统计数据还 是非永久性统计数 据;innodb_stats_persistent_sample_pages控制着 永久性统计数据的采样⻚面数 量;innodb_stats_transient_sample_pages控制着非 永久性统计数据的采样⻚面数 量;innodb_stats_auto_recalc控制着是否自动重新计算 统计数据。

（3）我们可以针对某个具体的表，在创建和修改表时通过指定 STATS_PERSISTENT、STATS_AUTO_RECALC、STATS_SAMPLE_PA 的值来控制相关统计数据属性

（4）innodb_stats_method决定着在统计某个索引列不重复值的 数量时如何对待NULL值

十三、基于规则的优化

1、查询重写：

我们无法避免某些同学写一些执行起来十分耗费性能的语 句。即使是这样，设计MySQL的大叔还是依据一些规则，竭尽全力的 把这个很糟糕的语句转换成某种可以比较高效执行的形式，这个过程 也可以被称作查询重写

2、条件化简：

（1）移除不必要的括号
（2）常量传递
（3）等值传递
（4）移除没用的条件
（5）表达式计算（最好让索引列
以单独的形式出现在表达式中）
（6）HAVING子句和WHERE子句的合并
（7）常量表检测
（8）外连接消除
（9）空值拒绝：在外连接查询中，指定的WHERE子句中包含被驱动表中的 列不为NULL值的条件称之为空值拒绝；在被驱动表的WHERE子句符合空值拒绝的条件后，外连接和内连接 可以相互转换。这种转换带来的好处就是查询优化器可以通过评估表 的不同连接顺序的成本，选出成本最低的那种连接顺序来执行查询

3、子查询优化

十四、explain详解：

1、概念：

（1）EXPLAIN语句来帮助我们查看某个查询语句的具体执行计划

（2）EXPLAIN语句输出的各个列：

id：在一个大的查询语句中每个SELECT关键字都对应 一个唯一的id；
select_type：SELECT关键字对应的那个查询的类型；
table：表名；
partitions：匹配的分区信息；
type：针对单表的访问方法；
possible_keys：可能用到的索引；
key：实际上使用的索引；
key_len：实际使用到的索引⻓度；
ref：当使用索引列等值查询时，与索引列进行等值匹配 的对象信息；
rows：预估的需要读取的记录条数；
filtered：某个表经过搜索条件过滤后剩余记录条数的百分比；
Extra：一些额外的信息

2、各列详解：

（1）table：

EXPLAIN 语句输出的每条记录都对应着某个单表的访问方法，该条记录的 table列代表着该表的表名

（2）id：

在连接查询的执行计划中，每个表都会对应一条记录，这些记录的id列 的值是相同的，出现在前边的表表示驱动表，出现在后边的表表示被 驱动表；

对于包含子查询的查询语句来说，就可能涉及多个SELECT关键字， 所以在包含子查询的查询语句的执行计划中，每个SELECT关键字都 会对应一个唯一的id值；

查询优化器可能对涉及子查询的查询语 句进行重写，从而转换为连接查询

（3）select_type：

SIMPLE：查询语句中不包含UNION或者子查询的查询都算作是SIMPLE 类型；

PRIMARY：对于包含UNION、UNION ALL或者子查询的大查询来说，它是 由几个小查询组成的，其中最左边的那个查询的 select_type值就是PRIMARY；

UNION：对于包含UNION或者UNION ALL的大查询来说，它是由几个小 查询组成的，其中除了最左边的那个小查询以外，其余的小查 询的select_type值就是UNION

UNION RESULT：MySQL选择使用临时表来完成UNION查询的去重工作，针对该 临时表的查询的select_type就是UNION RESULT

SUBQUERY：如果包含子查询的查询语句不能够转为对应的semi-join的形 式，并且该子查询是不相关子查询，并且查询优化器决定采用 将该子查询物化的方案来执行该子查询时，该子查询的第一 个SELECT关键字代表的那个查询的select_type就 是SUBQUERY；

DEPENDENT SUBQUERY：如果包含子查询的查询语句不能够转为对应的semi-join的形 式，并且该子查询是相关子查询，则该子查询的第一 个SELECT关键字代表的那个查询的select_type就 是DEPENDENT SUBQUERY（select_type为DEPENDENT SUBQUERY的 查询可能会被执行多次）

DEPENDENT UNION：在包含UNION或者UNION ALL的大查询中，如果各个小查询都 依赖于外层查询的话，那除了最左边的那个小查询之外，其余 的小查询的select_type的值就是DEPENDENT UNION

DERIVED：对于采用物化的方式执行的包含派生表的查询，该派生表对应 的子查询的select_type就是DERIVED

MATERIALIZED：当查询优化器在执行包含子查询的语句时，选择将子查询物化 之后与外层查询进行连接查询时，该子查询对应的 select_type属性就是MATERIALIZED

UNCACHEABLE SUBQUERY：不常用

UNCACHEABLE UNION：不常用

（4）partitions：

分区，一般情况下我们的查询语句的执行计划的partitions列的 值都是NULL

（5）type：

执行计划的一条记录就代表着MySQL对某个表的执行查 询时的访问方法，其中的type列就表明了这个访问方法是个啥

system：当表中只有一条记录并且该表使用的存储引擎的统计数据是精 确的，比如MyISAM、Memory，那么对该表的访问方法就 是system

const：当我们根据主键或者唯一二级索引 列与常数进行等值匹配时，对单表的访问方法就是const

eq_ref：在连接查询时，如果被驱动表是通过主键或者唯一二级索引列 等值匹配的方式进行访问的(如果该主键或者唯一二级索引是 联合索引的话，所有的索引列都必须进行等值比较)，则对该 被驱动表的访问方法就是eq_ref

ref：当通过普通的二级索引列与常量进行等值匹配时来查询某个 表，那么对该表的访问方法就可能是ref

fulltext：全文索引

ref_or_null：当对普通二级索引进行等值匹配查询，该索引列的值也可以 是NULL值时，那么对该表的访问方法就可能 是ref_or_null

index_merge：一般情况下对于某个表的查询只能使用到一个索引，但我们唠 叨单表访问方法时特意强调了在某些场景下可以使 用Intersection、Union、Sort-Union这三种索引合并的 方式来执行查询，MySQL打算使用索引合并的方式来执行对s1表的查

unique_subquery：类似于两表连接中被驱动表的eq_ref访问方 法，unique_subquery是针对在一些包含IN子查询的查询语 句中，如果查询优化器决定将IN子查询转换为EXISTS子查 询，而且子查询可以使用到主键进行等值匹配的话，那么该子 查询执行计划的type列的值就是unique_subquery

index_subquery：index_subquery与unique_subquery类似，只不过访问子 查询中的表时使用的是普通的索引

range：如果使用索引获取某些范围区间的记录，那么就可能使用 到range访问方法

index：当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该 表的访问方法就是index

ALL：全表扫描

（6）possible_keys和key：

possible_keys列表示在某个 查询语句中，对某个表执行单表查询时可能用到的索引有哪些，key 列表示实际用到的索引有哪些

注意：possible_keys列中的值并不是越多越好， 可能使用的索引越多，查询优化器计算查询成本时就得花费更⻓时 间，所以如果可以的话，尽量删除那些用不到的索引

（7）key_len：

key_len列表示当优化器决定使用某个索引执行查询时，该索引记 录的最大⻓度；

由这三个部分构成的：
对于使用固定⻓度类型的索引列来说，它实际占用的存储空间 的最大⻓度就是该固定值，对于指定字符集的变⻓类型的索引 列来说，比如某个索引列的类型是VARCHAR(100)，使用的字 符集是utf8，那么该列实际占用的最大存储空间就是100 × 3 = 300个字节；

如果该索引列可以存储NULL值，则key_len比不可以存储 NULL值时多1个字节；

对于变⻓字段来说，都会有2个字节的空间来存储该变⻓列的 实际⻓度

（8）ref：

当使用索引列等值匹配的条件去执行查询时，也就是在访问方法 是const、eq_ref、ref、ref_or_null、unique_subquery、index_ 其中之一时，ref列展示的就是与索引列作等值匹配的东东是个啥， 比如只是一个常数或者是某个列

（9）rows：

如果查询优化器决定使用全表扫描的方式对某个表执行查询时，执行 计划的rows列就代表预计需要扫描的行数，如果使用索引来执行查 询时，执行计划的rows列就代表预计扫描的索引记录行数

（10）filtered：

执行计划的filtered列就代表查询优化器预测在这些条记录中，有 多少条记录满足其余的搜索条件；

对于单表查询来说，这个filtered列的值没什么意义，我们更关注 在连接查询中驱动表对应的执行计划记录的filtered值

（11）Extra：

extra列是用来说明一些额外信息的，我们可以通过这些 额外信息来更准确的理解MySQL到底将如何执行给定的查询语句；

No tables used：当查询语句的没有FROM子句时将会提示该额外信息

Impossible WHERE：查询语句的WHERE子句永远为FALSE时将会提示该额外信息

No matching min/max row：当查询列表处有MIN或者MAX聚集函数，但是并没有符 合WHERE子句中的搜索条件的记录时，将会提示该额外信息

Using index：当我们的查询列表以及搜索条件中只包含属于某个索引的列， 也就是在可以使用索引覆盖的情况下，在Extra列将会提示该 额外信息

Using index condition：有些搜索条件中虽然出现了索引列，但却不能使用到索引，如果在查询语句的执行过程中将要使用索引条件下推这个特 性，在Extra列中将会显示Using index condition

Using where：当我们使用全表扫描来执行对某个表的查询，并且该语句的 WHERE子句中有针对该表的搜索条件时，在Extra列中会提示 上述额外信息

Using join buffer (Block Nested Loop)：在连接查询执行过程过，当被驱动表不能有效的利用索引加快 访问速度，MySQL一般会为其分配一块名叫join buffer的 内存块来加快查询速度，也就是我们所讲的基于块的嵌套循环 算法

Not exists：当我们使用左(外)连接时，如果WHERE子句中包含要求被驱 动表的某个列等于NULL值的搜索条件，而且那个列又是不允许 存储NULL值的，那么在该表的执行计划的Extra列就会提 示Not exists额外信息

Using intersect(…)、Using union(…)和Using sort_union(…)：如果执行计划的Extra列出现了Using intersect(…)提 示，说明准备使用Intersect索引合并的方式执行查询，括号 中的…表示需要进行索引合并的索引名称;如果出现了 Using union(…)提示，说明准备使用Union索引合并的 方式执行查询;出现了Using sort_union(…)提示，说 明准备使用Sort-Union索引合并的方式执行查询

Zero limit：我们的LIMIT子句的参数为0时，表示压根儿不打算从表中
读出任何记录，将会提示该额外信息

Using filesort：有一些情况下对结果集中的记录进行排序是可以使用到索引 的，如果某个查询需要使用文件排序的方式执 行查询，就会在执行计划的Extra列中显示Using filesort 提示

Using temporary：如果查询中使用到了内部的临时 表，在执行计划的Extra列将会显示Using temporary提 示

Start temporary, End temporary：驱动表查 询执行计划的Extra列将显示Start temporary提示，被驱 动表查询执行计划的Extra列将显示End temporary提示

LooseScan：在将In子查询转为semi-join时，如果采用的是LooseScan 执行策略，则在驱动表执行计划的Extra列就是显 示LooseScan提示

FirstMatch(tbl_name)：在将In子查询转为semi-join时，如果采用的 是FirstMatch执行策略，则在被驱动表执行计划的Extra列 就是显示FirstMatch(tbl_name)提示

3、Json格式的执行计划：

上边介绍的EXPLAIN语句输出中缺少了一个衡量执行计划好坏 的重要属性 —— 成本。不过设计MySQL的大叔贴心的为我们提供了 一种查看某个执行计划花费的成本的方式；
在EXPLAIN单词和真正的查询语句中间加上FORMAT=JSON，我们就可以得到一个json格式的执行计划，里边儿包含该计划花费的成本

十五、InnoDB 的 Buffer Pool：

1、缓存的重要性：

即使我们只需要访问一个⻚的一条记录，那也需要先把整个⻚的数据加 载到内存中；
将整个⻚加载到内存中后就可以进行读写访问了，在进 行完读写访问之后并不着急把该⻚对应的内存空间释放掉，而是将其 缓存起来，这样将来有请求再次访问该⻚面时，就可以省去磁盘IO 的开销了

2、InnoDB的Buffer Pool：

（1）概念：

设计InnoDB的大叔为了缓存磁盘中的⻚，在MySQL服务器启动的时 候就向操作系统申请了一片连续的内存，他们给这片内存起了个名， 叫做Buffer Pool(中文名是缓冲池)；默认情况下Buffer Pool只有128M大小

可以在启动服务器的时候配 置innodb_buffer_pool_size参数的值，它表示Buffer Pool 的大小

（2）Buffer Pool内部组成：

Buffer Pool中默认的缓存⻚大小和在磁盘上默认的⻚大小是一样 的，都是16KB；

I. 控制块：

为了更好的管理这些在Buffer Pool中的缓存⻚， 设计InnoDB的大叔为每一个缓存⻚都创建了一些所谓的控制信息， 这些控制信息包括该⻚所属的表空间编号、⻚号、缓存⻚在Buffer Pool中的地址、链表节点信息、一些锁信息以及LSN信息；

每个缓存⻚对应的控制信息占用的内存大小是相同的，我们就把每个 ⻚对应的控制信息占用的一块内存称为一个控制块

II. 缓存⻚:

控制块和缓存 ⻚是一一对应的，它们都被存放到 Buffer Pool 中，其中控制块被存 放到 Buffer Pool 的前边，缓存⻚被存放到 Buffer Pool 后边；

整个Buffer Pool对应的内存空间看起来就是这样：

每 一个控制块都对应一个缓存⻚，那在分配足够多的控制块和缓存⻚ 后，可能剩余的那点儿空间不够一对控制块和缓存⻚的大小，用不到的那点儿内存空间就被称为碎片

3、free链表的管理：

（1）在某个地方记录一下Buffer Pool中哪些缓存⻚是可用的，这个时候缓存⻚对应的控制块就派上 大用场了，我们可以把所有空闲的缓存⻚对应的控制块作为一个节点 放到一个链表中，这个链表也可以被称作free链表(或者说空闲链表)，刚刚完成初始化的Buffer Pool中所有的缓存⻚都是空闲 的，所以每一个缓存⻚对应的控制块都会被加入到free链表中

（2）假设该Buffer Pool中可容纳的缓存⻚数量为n，那增加了free链表 的效果图就是这样的:

（3）每当需要从磁盘中加载一个 ⻚到Buffer Pool中时，就从free链表中取一个空闲的缓存⻚，并 且把该缓存⻚对应的控制块的信息填上(就是该⻚所在的表空间、⻚ 号之类的信息)，然后把该缓存⻚对应的free链表节点从链表中移 除，表示该缓存⻚已经被使用了

4、缓存⻚的哈希处理

哈希表，用表空间号 + ⻚号作为key，缓存⻚作为value创建 一个哈希表，在需要访问某个⻚的数据时，先从哈希表中根据表空间 号 + ⻚号看看有没有对应的缓存⻚，如果有，直接使用该缓存⻚就 好，如果没有，那就从free链表中选一个空闲的缓存⻚，然后把磁 盘中对应的⻚加载到该缓存⻚的位置

5、flush链表的管理：

（1）如果我们修改了Buffer Pool中某个缓存⻚的数据，那它就和磁盘 上的⻚不一致了，这样的缓存⻚也被称为脏⻚(英文名:dirty page)；

每次修改缓存⻚后，我们并不 着急立即把修改同步到磁盘上，而是在未来的某个时间点进行同步

（2）flush链表：
创建一个存储脏⻚的链表，凡是修改过的缓存⻚对 应的控制块都会作为一个节点加入到一个链表中，因为这个链表节点 对应的缓存⻚都是需要被刷新到磁盘上的，所以也叫flush链表

6、LRU链表的管理：

（1）缓存不够的窘境

（2）简单的LRU链表：

当Buffer Pool 中不再有空闲的缓存⻚时，就需要淘汰掉部分最近很少使用的缓存⻚；

创建一个链 表，由于这个链表是为了按照最近最少使用的原则去淘汰缓存⻚的， 所以这个链表可以被称为LRU链表(LRU的英文全称:Least Recently Used)

（3）处理：

当我们需要访问某个⻚时，可以这样处理LRU链表:
如果该⻚不在Buffer Pool中，在把该⻚从磁盘加载 到Buffer Pool中的缓存⻚时，就把该缓存⻚对应的控制块 作为节点塞到链表的头部；

如果该⻚已经缓存在Buffer Pool中，则直接把该⻚对应的 控制块移动到LRU链表的头部；

（4）总结：

只要我们使用到某个缓存⻚，就把该缓存⻚调整到LRU链 表的头部，这样LRU链表尾部就是最近最少使用的缓存⻚喽~ 所以 当Buffer Pool中的空闲缓存⻚使用完时，到LRU链表的尾部找些 缓存⻚淘汰

7、划分区域的LRU链表：

（1）InnoDB提供了一个看起来比较贴心的服务——预读 (英文名:read ahead)。

所谓预读，就是InnoDB认为执 行当前的请求可能之后会读取某些⻚面，就预先把它们加载 到Buffer Pool中，根据触发方式的不同，预读又可以细分 为下边两种：线性预读和随机预读；

（2）有的小伙伴可能会写一些需要扫描全表的查询语句：

每次执行都要把Buffer Pool中的缓存⻚换一次 血，这严重的影响到其他查询对 Buffer Pool的使用，从而 大大降低了缓存命中率

（3）总结两种：

加载到Buffer Pool中的⻚不一定被用到；
如果非常多的使用频率偏低的⻚被同时加载到Buffer Pool 时，可能会把那些使用频率非常高的⻚从Buffer Pool中淘 汰掉

（4）LRU链表按照一定比例分成两截：

一部分存储使用频率非常高的缓存⻚，所以这一部分链表也叫
做热数据，或者称young区域；
另一部分存储使用频率不是很高的缓存⻚，所以这一部分链表
也叫做冷数据，或者称old区域

我们是按照某个比例将LRU链表分成两半 的，不是某些节点固定是young区域的，某些节点固定是old区域 的，随着程序的运行，某个节点所属的区域也可能发生变化

（5）优化：

针对预读的⻚面可能不进行后续访情况的优化，当磁盘上的某个⻚面在初次加载到 Buffer Pool中的某个缓存⻚时，该缓存⻚对应的控制块会被放 到old区域的头部这样针对预读到Buffer Pool却不进行后续访问的⻚面就会被逐渐从old区域逐出，而不会影响young 区域中被使用比较频繁的缓存⻚；

针对全表扫描时，短时间内访问大量使用频率非常低的⻚面情 况的优化：我们规定，在对某个处在old区域的缓存⻚进行第一次访问时就在它 对应的控制块中记录下来这个访问时间，如果后续的访问时间 与第一次访问的时间在某个时间间隔内，那么该⻚面就不会被 从old区域移动到young区域的头部，否则将它移动到young区 域的头部

8、更进一步优化LRU链表

只有被访问的缓存⻚位于young区域的1/4的后边，才 会被移动到LRU链表头部，这样就可以降低调整LRU链表的频率，从 而提升性能(也就是说如果某个缓存⻚对应的节点在young区域的 1/4中，再次访问该缓存⻚时也不会将其移动到LRU链表头部)；尽量高效的提高 Buffer Pool 的缓存命中率

9、多个Buffer Pool实例：

（1）Buffer Pool本质是InnoDB向操作系统申请的一 块连续的内存空间，在多线程环境下，访问Buffer Pool中的各种 链表都需要加锁处理啥的，在Buffer Pool特别大而且多线程并发 访问特别高的情况下，单一的Buffer Pool可能会影响请求的处理 速度。所以在Buffer Pool特别大的时候，我们可以把它们拆分成 若干个小的Buffer Pool，每个Buffer Pool都称为一个实例，它们都是独立的，独立的去申请内存空间，独立的管理各种链表，独 立的吧啦吧啦，所以在多线程并发访问时并不会相互影响，从而提高 并发处理能力

（2）当innodb_buffer_pool_size的值小于1G的时候设置多个实例 是无效的，InnoDB会默认把innodb_buffer_pool_instances 的值 修改为1。而我们鼓励在Buffer Pool大小或等于1G的时候设置多 个Buffer Pool实例

10、总结：

（1）磁盘太慢，用内存作为缓存很有必要
（2）Buffer Pool本质上是InnoDB向操作系统申请的一段连续的 内存空间，可以通过innodb_buffer_pool_size来调整它 的大小
（3）Buffer Pool向操作系统申请的连续内存由控制块和缓存⻚ 组成，每个控制块和缓存⻚都是一一对应的，在填充足够多的 控制块和缓存⻚的组合后，Buffer Pool剩余的空间可能产 生不够填充一组控制块和缓存⻚，这部分空间不能被使用，也 被称为碎片
（4）InnoDB使用了许多链表来管理Buffer Pool
（5）free链表中每一个节点都代表一个空闲的缓存⻚，在将磁盘中 的⻚加载到Buffer Pool时，会从free链表中寻找空闲的缓 存⻚
（6）为了快速定位某个⻚是否被加载到Buffer Pool，使用表空 间号 + ⻚号作为key，缓存⻚作为value，建立哈希表
（7）在Buffer Pool中被修改的⻚称为脏⻚，脏⻚并不是立即刷 新，而是被加入到flush链表中，待之后的某个时刻同步到磁 盘上。
（8）LRU链表分为young和old两个区域，可以通过 innodb_old_blocks_pct来调节old区域所占的比例。首次 从磁盘上加载到Buffer Pool的⻚会被放到old区域的头部，在innodb_old_blocks_time间隔时间内访问该⻚不会把它 移动到young区域头部。在Buffer Pool没有可用的空闲缓 存⻚时，会首先淘汰掉old区域的一些⻚
（9）我们可以通过指定innodb_buffer_pool_instances来控 制Buffer Pool实例的个数，每个Buffer Pool实例中都有 各自独立的链表，互不干扰
（10）自MySQL 5.7.5版本之后，可以在服务器运行过程中调 整Buffer Pool大小。每个Buffer Pool实例由若干 个chunk组成，每个chunk的大小可以在服务器启动时通过启 动参数调整
（11）可以用下边的命令查看Buffer Pool的状态信息：
SHOW ENGINE INNODB STATUS\G

十六、事务

1、原子性(Atomicity)：

现实世界中转账操作是一个不可分割的操作，也就是说要么压根儿就 没转，要么转账成功，不能存在中间的状态，也就是转了一半的这种 情况。设计数据库的大叔们把这种要么全做，要么全不做的规则称之为原子性

2、隔离性(Isolation)：

对于现实世界中状态转换对应的某些数据库操作来说，不仅要保 证这些操作以原子性的方式执行完成，而且要保证其它的状态转换不 会影响到本次状态转换，这个规则被称之为隔离性

3、一致性(Consistency)：

如 果数据库中的数据全部符合现实世界中的约束(all defined rules)，我们说这些数据就是一致的，或者说符合一致性的

（1）数据库本身能为我们保证一部分一致性需求(就是数据库自身 可以保证一部分现实世界的约束永远有效)
（2）更多的一致性需求需要靠写业务代码的程序员自己保证

（3）数据库某些操作的原子性和隔离性都是保证一致性的一种手 段，在操作执行完成后保证符合所有既定的约束则是一种结果
（4）只要最后的结果符合所有现实世界中的约束，那么就是符合一致 性的

4、持久性(Durability)：

当现实世界的一个状态转换完成后，这个转换的结果将永久的保留， 这个规则被设计数据库的大叔们称为持久性；

持久性意味着该转换 对应的数据库操作所修改的数据都应该在磁盘上保留下来，不论之后 发生了什么事故，本次转换造成的影响都不应该被丢失掉

5、事务的概念：

（1）定义：

把需要保证原子性(Atomicity)、隔离性 (Isolation)、一致性(Consistency)和持久性 (Durability)的一个或多个数据库操作称之为一个事务

（2）事务状态：

活动的(active)：事务对应的数据库操作正在执行过程中时，我们就说该事务处在活动的状态；

部分提交的(partially committed)：当事务中的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并没有刷新到磁盘时，我们就说该事务处在 部分提交的状态；

失败的(failed)：当事务处在活动的或者部分提交的状态时，可能遇到了某些错 误(数据库自身的错误、操作系统错误或者直接断电等)而无 法继续执行，或者人为的停止当前事务的执行，我们就说该事 务处在失败的状态；

中止的(aborted)：如果事务执行了半截而变为失败的状态，比如我们前边唠叨的 狗哥向猫爷转账的事务，当狗哥账户的钱被扣除，但是猫爷账 户的钱没有增加时遇到了错误，从而当前事务处在了失败的状 态，那么就需要把已经修改的狗哥账户余额调整为未转账之前 的金额，换句话说，就是要撤销失败事务对当前数据库造成的 影响。书面一点的话，我们把这个撤销的过程称之为回滚。当 回滚操作执行完毕时，也就是数据库恢复到了执行事务之前的 状态，我们就说该事务处在了中止的状态；

提交的(committed)：当一个处在部分提交的状态的事务将修改过的数据都同步到磁盘上之后，我们就可以说该事务处在了提交的状态

如图，只有当事务处于提交的或者中止的状态 时，一个事务的生命周期才算是结束了。对于已经提交的事务来说， 该事务对数据库所做的修改将永久生效，对于处于中止状态的事务， 该事务对数据库所做的所有修改都会被回滚到没执行该事务之前的状 态。

6、MySQL中事务的语法：

（1）开启事务：

I. BEGIN [WORK];

BEGIN语句代表开启一个事务，后边的单词WORK可有可无。开 启事务后，就可以继续写若干条语句，这些语句都属于刚刚开 启的这个事务；

II. START TRANSACTION

START TRANSACTION语句和BEGIN语句有着相同的功效，都标志着开启一个事务；
比BEGIN语句牛逼一点儿的是，可以在START TRANSACTION语句后边跟随几个修饰符:
READ ONLY:标识当前事务是一个只读事务，也就是属 于该事务的数据库操作只能读取数据，而不能修改数据；
READ WRITE:标识当前事务是一个读写事务，也就是属 于该事务的数据库操作既可以读取数据，也可以修改数据；
WITH CONSISTENT SNAPSHOT: 启动一致性读

III. 如果我们不显式指定事务的访问模式，那么该事务的访问模式 就是读写模式

（2）提交事物：

开启事务之后就可以继续写需要放到该事务中的语句了，当最后一条 语句写完了之后，我们就可以提交该事务了，提交的语句也很简单：
COMMIT [WORK]

COMMIT语句就代表提交一个事务，后边的WORK可有可无

（3）手动中止事务：

如果我们写了几条语句之后发现上边的某条语句写错了，我们可以手 动的使用下边这个语句来将数据库恢复到事务执行之前的样子:
ROLLBACK [WORK]
ROLLBACK语句就代表中止并回滚一个事务；

ROLLBACK语句是我们程序员手动的去回滚事务 时才去使用的，如果事务在执行过程中遇到了某些错误而无法继续执 行的话，事务自身会自动的回滚

7、支持事务的存储引擎：

目前只有InnoDB 和NDB存储引擎支持

8、自动提交

（1）MySQL中有一个系统变量autocommit；
它的默认值为ON，也就是说默认情况下，如果我们不显式 的使用START TRANSACTION或者BEGIN语句开启一个事务，那么 每一条语句都算是一个独立的事务，这种特性称之为事务的自动提交

（2）如果我们想关闭这种自动提交的功能，可以使用下边两种方法 之一:
显式的的使用START TRANSACTION或者BEGIN语句开启一个事务。
这样在本次事务提交或者回滚前会暂时关闭掉自动提交的功能

把系统变量autocommit的值设置为OFF。这样的话，我们写入的多条语句就算是属于同一个事务了，直 到我们显式的写出COMMIT语句来把这个事务提交掉，或者显 式的写出ROLLBACK语句来把这个事务回滚掉

9、隐式提交

（1）概念：

当我们使用START TRANSACTION或者BEGIN语句开启了一个事 务，或者把系统变量autocommit的值设置为OFF时，事务就不会进 行自动提交，但是如果我们输入了某些语句之后就会悄悄的提交掉， 就像我们输入了COMMIT语句了一样，这种因为某些特殊的语句而导 致事务提交的情况称为隐式提交

（2）这些会导致事务隐式提交的语句包括:

I. 定义或修改数据库对象的数据定义语言(Data definition language，缩写为:DDL);

当我们使用CREATE、ALTER、DELETE等语句去 修改这些所谓的数据库对象时，就会隐式的提交前边语句所属 于的事务

II. 隐式使用或修改mysql数据库中的表:

当我们使用ALTER USER、CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD 等语句时也会隐式的提交前边语句所属于的事务

III. 事务控制或关于锁定的语句:

当我们在一个事务还没提交或者回滚时就又使用START TRANSACTION或者BEGIN语句开启了另一个事务时，会隐式 的提交上一个事务；

或者当前的autocommit系统变量的值为OFF，我们手动把它 调为ON时，也会隐式的提交前边语句所属的事务；

或者使用LOCK TABLES、UNLOCK TABLES等关于锁定的语句 也会隐式的提交前边语句所属的事务

IV. 加载数据的语句

比如我们使用LOAD DATA语句来批量往数据库中导入数据时， 也会隐式的提交前边语句所属的事务

V. 关于MySQL复制的一些语句:

使用START SLAVE、STOP SLAVE、RESET SLAVE、CHANGE MASTER TO等语句时也会隐式的提交前边 语句所属的事务

VI. 其它的一些语句:

使用ANALYZE TABLE、CACHE INDEX、CHECK TABLE、FLUSH、 LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE、RESET等语句也会隐式的提交前边 语句所属的事务

10、保存点：

（1）背景：

如果你开启了一个事务，并且已经敲了很多语句，忽然发现上一条语 句有点问题，你只好使用ROLLBACK语句来让数据库状态恢复到事务 执行之前的样子，然后一切从头再来

（2）保存点概念：

就是在事务对应的数据库语句中打几个 点，我们在调用ROLLBACK语句时可以指定会滚到哪个点，而不是回 到最初的原点

定义保存点的语法：
SAVEPOINT 保存点名称;

当我们想回滚到某个保存点时，可以使用下边这个语句：
ROLLBACK [WORK] TO [SAVEPOINT] 保存点名称;

如果我们想删除某个保存点，可以使用这个语句:
RELEASE SAVEPOINT 保存点名称;

十七、redo日志

1、概念：

redo日志（重做日志）：在事务提交时，把记录修改内容刷新到磁盘中，即使之后系统崩 溃了，重启之后只要按照记录修改内容所记录的步骤重新更新一下数据 ⻚，那么该事务对数据库中所做的修改又可以被恢复出来，也就意味 着满足持久性的要求。因为在系统奔溃重启时需要按照上述内容所记 录的步骤重新更新数据⻚，所以上述内容也被称之为重做日志

2、只将该事 务执行过程中产生的redo日志刷新到磁盘的好处：

（1）redo日志占用的空间非常小：

存储表空间ID、⻚号、偏移量以及需要更新的值所需的存储空 间是很小的

（2）redo日志是顺序写入磁盘的：

在执行事务的过程中，每执行一条语句，就可能产生若干 条redo日志，这些日志是按照产生的顺序写入磁盘的，也就是 使用顺序IO

3、redo日志格式：

redo日志本质上只是记录了一下事务对数据库做了哪些修改

（1）通用的结构:

type:该条redo日志的类型，redo日志设计了53种不同的类型；
space ID:表空间ID；
page number:⻚号；
data:该条redo日志的具体内容

（2）简单的redo日志类型：

redo 日志中只需要记录一下在某个⻚面的某个偏移量处修改了几个字节的 值，具体被修改的内容是啥就好，设计InnoDB的大叔把这种极其 简单的redo日志称之为物理日志，并且根据在⻚面中写入数据的多 少划分了几种不同的redo日志类型:
MLOG_1BYTE(type字段对应的十进制数字为1):表示在⻚ 面的某个偏移量处写入1个字节的redo日志类型。
MLOG_2BYTE(type字段对应的十进制数字为2):表示在⻚ 面的某个偏移量处写入2个字节的redo日志类型。
MLOG_4BYTE(type字段对应的十进制数字为4):表示在⻚ 面的某个偏移量处写入4个字节的redo日志类型。
MLOG_8BYTE(type字段对应的十进制数字为8):表示在⻚ 面的某个偏移量处写入8个字节的redo日志类型。
MLOG_WRITE_STRING(type字段对应的十进制数字 为30):表示在⻚面的某个偏移量处写入一串数据

MLOG_WRITE_STRING类型的redo日志表示写入一串数 据，但是因为不能确定写入的具体数据占用多少字节，所以需要在日 志结构中添加一个len字段:

（3）复杂一些的redo日志类型：

把一条记录插入到一个⻚面时需要更改的地方非常多

4、redo日志格式小结：

redo日志会把事务在执行过程中 对数据库所做的所有修改都记录下来，在之后系统奔溃重启后可以把 事务所做的任何修改都恢复出来

5、Mini-Transaction的概念：

把对底层⻚面中的一次原子访问的过程称之为一 个Mini-Transaction，简称mtr；

一个事务可以包含若干条语句，每一条语句其实是由若干个mtr组 成，每一个mtr又可以包含若干条redo日志

6、redo日志的写入过程：

（1）redo log block：

用来存储redo日志的⻚称 为block

真正的redo日志都是存储到占用496字节大小的log block body 中，图中的log block header和log block trailer存储的是 一些管理信息

（2）redo日志缓冲区：

设计InnoDB的大叔为了解决磁盘速度过慢的问题而 引入了Buffer Pool。同理，写入redo日志时也不能直接直接写到 磁盘上，实际上在服务器启动时就向操作系统申请了一大片称之 为redo log buffer的连续内存空间，翻译成中文就是redo日志 缓冲区，我们也可以简称为log buffer，这片内存空间被划分成若 干个连续的redo log block

（3）redo日志写入log buffer

向log buffer中写入redo日志的过程是顺序的，也就是先往前边 的block中写，当该block的空闲空间用完之后再往下一个block中 写；

提供了一个称之为buf_free的全局变量，该变量指明后续写 入的redo日志应该写入到log buffer中的哪个位置

7、redo日志文件：

（1）redo日志刷盘时机：

mtr运行过程中产生的一组redo日志在mtr结束时会被复 制到log buffer中，可是这些日志总在内存里呆着也不是个办法， 在一些情况下它们会被刷新到磁盘里：

I. log buffer空间不足时：

如果当前写入log buffer的redo日志量 已经占满了log buffer总容量的大约一半左右，就需要把这 些日志刷新到磁盘上

II. 事务提交时:

我们前边说过之所以使用redo日志主要是因为它占用的空间 少，还是顺序写，在事务提交时可以不把修改过的Buffer Pool⻚面刷新到磁盘，但是为了保证持久性，必须要把修改这 些⻚面对应的redo日志刷新到磁盘，Force Log at Commit

III. 后台线程不停的刷刷刷

后台有一个线程，大约每秒都会刷新一次log buffer中的 redo日志到磁盘

IV. 正常关闭服务器时

V. 做所谓的checkpoint时

（2）redo日志文件组

磁盘上的redo日志文件不只一个，而是 以一个日志文件组的形式出现的，这些文件以ib_logfile[数字] (数字可以是0、1、2…)的形式进行命名

总共的redo日志文件大小其实就是:innodb_log_file_size × innodb_log_files_in_group

（3）redo日志文件格式

log buffer本质上是一片连续的内存空间，被划分成 了若干个512字节大小的block。将log buffer中的redo日志刷新到 磁盘的本质就是把block的镜像写入日志文件中，所以redo日志文件 其实也是由若干个512字节大小的block组成；

redo日志文件组中的每个文件大小都一样，格式也一样：
前2048个字节，也就是前4个block是用来存储一些管理信息 的；
从第2048字节往后是用来存储log buffer中的block镜像 的

8、Log Sequeue Number：

设计InnoDB的大叔为记 录已经写入的redo日志量，设计了一个称之为Log Sequeue Number的全局变量，翻译过来就是:日志序列号，简称lsn，规定初始的lsn值 为8704

（1）每一组由mtr生成的redo日志都有一个 唯一的LSN值与其对应，LSN值越小，说明redo日志产生的越早
（2）redo日志是首先写到log buffer中，之后才会被刷新到磁盘上的 redo日志文件，设计InnoDB的大叔提出了一个称之 为buf_next_to_write的全局变量，标记当前log buffer中已 经有哪些日志被刷新到磁盘中了

（3）flush链表中的脏⻚按照修改发生的时间 顺序进行排序，也就是按照oldest_modification代表的LSN值进行 排序，被多次更新的⻚面不会重复插入到flush链表中，但是会更新 newest_modification属性的值。

（4）checkpoint：
redo 日志只是为了系统奔溃后恢复脏⻚用的，如果对应的脏⻚已经刷新到 了磁盘，也就是说即使现在系统奔溃，那么在重启后也用不着使用 redo日志恢复该⻚面了，所以该redo日志也就没有存在的必要了， 那么它占用的磁盘空间就可以被后续的redo日志所重用。也就是 说:判断某些redo日志占用的磁盘空间是否可以覆盖的依据就是它 对应的脏⻚是否已经刷新到磁盘里

9、崩溃恢复：

（1）确定恢复的起点
（2）确定恢复的终点
（3）怎么恢复

十八、undo日志

1、概念：

（1）回滚：

事务需要保证原子性，也就是事务中的操作要么全部完成，要么什么也不做。但是偏偏有时候事务执行到一半会出现一些情况：

情况一：事务执行过程中可能遇到各种错误，比如服务器本身的错误，操作系统错误，甚至是突然断电导致的错误。
情况二：程序员可以在事务执行过程中手动输入ROLLBACK语句结束当前的事务的执行。

这两种情况都会导致事务执行到一半就结束，但是事务执行过程中可能已经修改了很多东西，为了保证事务的原子性，我们需要把东西改回原先的样子，这个过程就称之为回滚

（2）undo日志：

设计数据库的大叔把这些为了回滚（指INSERT、DELETE、UPDATE）而记录的这些东东称之为撤销日志，英文名为undo log，我们也可以土洋结合，称之为undo日志

2、事务id：

（1）事务种类：

一个事务可以是一个只读事务，或者是一个读写事务：

START TRANSACTION READ ONLY语句开启一个只读事务，在只读事务中不可以对普通的表（其他事务也能访问到的表）进行增、删、改操作，但可以对临时表做增、删、改操作；

START TRANSACTION READ WRITE语句开启一个读写事务，或者使用BEGIN、START TRANSACTION语句开启的事务默认也算是读写事务，在读写事务中可以对表执行增删改查操作。

（2）给事务分配id的时机：

如果某个事务执行过程中对某个表执行了增、删、改操作，那么InnoDB存储引擎就会给它分配一个独一无二的事务id：

对于只读事务来说，只有在它第一次对某个用户创建的临时表执行增、删、改操作时才会为这个事务分配一个事务id，否则的话是不分配事务id的；

对于读写事务来说，只有在它第一次对某个表（包括用户创建的临时表）执行增、删、改操作时才会为这个事务分配一个事务id，否则的话也是不分配事务id的；

只有在事务对表中的记录做改动时才会为这个事务分配一个唯一的事务id

（3）事务id是怎么生成的：

事务id本质上就是一个数字，具体策略如下：
服务器会在内存中维护一个全局变量，每当需要为某个事务分配一个事务id时，就会把该变量的值当作事务id分配给该事务，并且把该变量自增1；
每当这个变量的值为256的倍数时，就会将该变量的值刷新到系统表空间的页号为5的页面中一个称之为Max Trx ID的属性处，这个属性占用8个字节的存储空间；
当系统下一次重新启动时，会将上边提到的Max Trx ID属性加载到内存中，将该值加上256之后赋值给我们前边提到的全局变量（因为在上次关机时该全局变量的值可能大于Max Trx ID属性值）。这样就可以保证整个系统中分配的事务id值是一个递增的数字。先被分配id的事务得到的是较小的事务id，后被分配id的事务得到的是较大的事务id

（4）trx_id隐藏列：

聚簇索引的记录除了会保存完整的用户数据以外，而且还会自动添加名为trx_id、roll_pointer的隐藏列，如果用户没有在表中定义主键以及UNIQUE键，还会自动添加一个名为row_id的隐藏列；

trx_id列：就是某个对这个聚簇索引记录做改动的语句所在的事务对应的事务id而已（此处的改动可以是INSERT、DELETE、UPDATE操作）

3、undo日志的格式：

（1）一个事务在执行过程中可能新增、删除、更新若干条记录，也就是说需要记录很多条对应的undo日志，这些undo日志会被从0开始编号，也就是说根据生成的顺序分别被称为第0号undo日志、第1号undo日志、…、第n号undo日志等，这个编号也被称之为undo no；

（2）这些undo日志是被记录到类型为FIL_PAGE_UNDO_LOG的页面中。这些页面可以从系统表空间中分配，也可以从一种专门存放undo日志的表空间，也就是所谓的undo tablespace中分配

4、INSERT操作对应的undo日志：

（1）这条记录被放到了一个数据页中。如果希望回滚这个插入操作，那么把这条记录删除就好了，也就是说在写对应的undo日志时，主要是把这条记录的主键信息记上，类型为TRX_UNDO_INSERT_REC的undo日志记录：
（2）undo no在一个事务中是从0开始递增的，也就是说只要事务没提交，每生成一条undo日志，那么该条日志的undo no就增1；

（3）如果记录中的主键只包含一个列，那么在类型TRX_UNDO_INSERT_REC的undo日志中只需要把该列占用的存储空间大小和真实值记录下来，如果记录中的主键包含多个列，那么每个列占用的存储空间大小和对应的真实值都需要记录下来；

（4）记录信息：因为记录的主键只包含一个id列，所以我们在对应的undo日志中只需要将待插入记录的id列占用的存储空间长度（id列的类型为INT，INT类型占用的存储空间长度为4个字节）和真实值记录下来

5、roll_pointer隐藏列的含义

本质上就是一个指向记录对应的undo日志的一个指针，roll_pointer本质就是一个指针，指向记录对应的undo日志

6、DELETE操作对应的undo日志：

（1）正常记录链表：

插入到页面中的记录会根据记录头信息中的next_record属性组成一个单向链表，我们把这个链表称之为正常记录链表

（2）垃圾链表：

被删除的记录其实也会根据记录头信息中的next_record属性组成一个链表，只不过这个链表中的记录占用的存储空间可以被重新利用，所以也称这个链表为垃圾链表
Page Header部分有一个称之为PAGE_FREE的属性，它指向由被删除记录组成的垃圾链表中的头节点

（3）删除的过程需要经历两个阶段：

I. 仅仅将记录的delete_mask标识位设置为1，其他的不做修改（其实会修改记录的trx_id、roll_pointer这些隐藏列的值）。设计InnoDB的大叔把这个阶段称之为delete mark。

正常记录链表中的最后一条记录的delete_mask值被设置为1，但是并没有被加入到垃圾链表。也就是此时记录处于一个中间状态，在删除语句所在的事务提交之前，被删除的记录一直都处于这种所谓的中间状态

II. 当该删除语句所在的事务提交之后，会有专门的线程后来真正的把记录删除掉。所谓真正的删除就是把该记录从正常记录链表中移除，并且加入到垃圾链表中，然后还要调整一些页面的其他信息，比如页面中的用户记录数量PAGE_N_RECS、上次插入记录的位置PAGE_LAST_INSERT、垃圾链表头节点的指针PAGE_FREE、页面中可重用的字节数量PAGE_GARBAGE、还有页目录的一些信息等等。设计InnoDB的大叔把这个阶段称之为purge

阶段二执行完了，这条记录就算是真正的被删除掉了。这条已删除记录占用的存储空间也可以被重新利用了

（4）设计InnoDB的大叔为此设计了一种称之为TRX_UNDO_DEL_MARK_REC类型的undo日志：

在对一条记录进行delete mark操作前，需要把该记录的旧的trx_id和roll_pointer隐藏列的值都给记到对应的undo日志中来，就是我们图中显示的old trx_id和old roll_pointer属性。这样有一个好处，那就是可以通过undo日志的old roll_pointer找到记录在修改之前对应的undo日志；

与类型为TRX_UNDO_INSERT_REC的undo日志不同，类型为TRX_UNDO_DEL_MARK_REC的undo日志还多了一个索引列各列信息的内容，也就是说如果某个列被包含在某个索引中，那么它的相关信息就应该被记录到这个索引列各列信息部分，所谓的相关信息包括该列在记录中的位置（用pos表示），该列占用的存储空间大小（用len表示），该列实际值（用value表示）。所以索引列各列信息存储的内容实质上就是<pos, len, value>的一个列表。这部分信息主要是用在事务提交后，对该中间状态记录做真正删除的阶段二，也就是purge阶段中使用的

7、UPDATE操作对应的undo日志

在执行UPDATE语句时，InnoDB对更新主键和不更新主键这两种情况有截然不同的处理方案。

（1）不更新主键的情况：

在不更新主键的情况下，又可以细分为被更新的列占用的存储空间不发生变化和发生变化的情况：

I.就地更新（in-place update）:

更新记录时，对于被更新的每个列来说，如果更新后的列和更新前的列占用的存储空间都一样大，那么就可以进行就地更新，也就是直接在原记录的基础上修改对应列的值。再次强调一边，是每个列在更新前后占用的存储空间一样大，有任何一个被更新的列更新前比更新后占用的存储空间大，或者更新前比更新后占用的存储空间小都不能进行就地更新

II. 先删除掉旧记录，再插入新记录:

在不更新主键的情况下，如果有任何一个被更新的列更新前和更新后占用的存储空间大小不一致，那么就需要先把这条旧的记录从聚簇索引页面中删除掉，然后再根据更新后列的值创建一条新的记录插入到页面中。

III. TRX_UNDO_UPD_EXIST_REC的undo日志:

针对UPDATE不更新主键的情况，设计InnoDB的大叔们设计了一种类型为TRX_UNDO_UPD_EXIST_REC的undo日志：
n_updated属性表示本条UPDATE语句执行后将有几个列被更新；
如果在UPDATE语句中更新的列包含索引列，那么也会添加索引列各列信息这个部分，否则的话是不会添加这个部分的

（2）更新主键的情况

在聚簇索引中，记录是按照主键值的大小连成了一个单向链表的，如果我们更新了某条记录的主键值，意味着这条记录在聚簇索引中的位置将会发生改变，比如你将记录的主键值从1更新为10000，如果还有非常多的记录的主键值分布在1 ~ 10000之间的话，那么这两条记录在聚簇索引中就有可能离得非常远，甚至中间隔了好多个页面。针对UPDATE语句中更新了记录主键值的这种情况，InnoDB在聚簇索引中分了两步处理：

I. 将旧记录进行delete mark操作:

也就是说在UPDATE语句所在的事务提交前，对旧记录只做一个delete mark操作，在事务提交后才由专门的线程做purge操作，把它加入到垃圾链表中。这里一定要和我们上边所说的在不更新记录主键值时，先真正删除旧记录，再插入新记录的方式区分开！

注意：之所以只对旧记录做delete mark操作，是因为别的事务同时也可能访问这条记录，如果把它真正的删除加入到垃圾链表后，别的事务就访问不到了。这个功能就是所谓的MVCC

II. 根据更新后各列的值创建一条新记录，并将其插入到聚簇索引中（需重新定位插入的位置）

由于更新后的记录主键值发生了改变，所以需要重新从聚簇索引中定位这条记录所在的位置，然后把它插进去；
针对UPDATE语句更新记录主键值的这种情况，在对该记录进行delete mark操作前，会记录一条类型为TRX_UNDO_DEL_MARK_REC的undo日志；之后插入新记录时，会记录一条类型为TRX_UNDO_INSERT_REC的undo日志，也就是说每对一条记录的主键值做改动时，会记录2条undo日志

8、通用链表结构：

在写入undo日志的过程中会使用到多个链表，很多链表都有同样的节点结构，为了更好的管理链表，设计InnoDB的大叔还提出了一个基节点的结构，里边存储了这个链表的头节点、尾节点以及链表长度信息

9、FIL_PAGE_UNDO_LOG页面

（1）FIL_PAGE_UNDO_LOG类型的页面是专门用来存储undo日志的：

File Header和File Trailer是各种页面都有的通用结构，Undo Page Header是Undo页面所特有的

（2）各个属性：

I. TRX_UNDO_PAGE_TYPE：

本页面准备存储什么种类的undo日志，分为两个大类：
TRX_UNDO_INSERT（使用十进制1表示）：类型为TRX_UNDO_INSERT_REC的undo日志属于此大类，一般由INSERT语句产生，或者在UPDATE语句中有更新主键的情况也会产生此类型的undo日志;
TRX_UNDO_UPDATE（使用十进制2表示），除了类型为TRX_UNDO_INSERT_REC的undo日志，其他类型的undo日志都属于这个大类，比如我们前边说的TRX_UNDO_DEL_MARK_REC、TRX_UNDO_UPD_EXIST_REC啥的，一般由DELETE、UPDATE语句产生的undo日志属于这个大类

II. TRX_UNDO_PAGE_START

表示在当前页面中是从什么位置开始存储undo日志的，或者说表示第一条undo日志在本页面中的起始偏移量

III. TRX_UNDO_PAGE_FREE

与上边的TRX_UNDO_PAGE_START对应，表示当前页面中存储的最后一条undo日志结束时的偏移量，或者说从这个位置开始，可以继续写入新的undo日志

IV. TRX_UNDO_PAGE_NODE

代表一个List Node结构

10、Undo页面链表

（1）单个事务中的Undo页面链表

I. 因为一个事务可能包含多个语句，而且一个语句可能对若干条记录进行改动，而对每条记录进行改动前，都需要记录1条或2条的undo日志，所以在一个事务执行过程中可能产生很多undo日志，这些日志可能一个页面放不下，需要放到多个页面中，这些页面通过TRX_UNDO_PAGE_NODE属性连成了链表；

II. 链表中的第一个Undo页面给标了出来，称它为first undo page，其余的Undo页面称之为normal undo page，这是因为在first undo page中除了记录Undo Page Header之外，还会记录其他的一些管理信息

III. 一个事务执行过程中就可能需要2个Undo页面的链表，一个称之为insert undo链表，另一个称之为update undo链表

IV. 规定对普通表和临时表的记录改动时产生的undo日志要分别记录，一个事务中最多有4个以Undo页面为节点组成的链表；

V. 并不是在事务一开始就会为这个事务分配这4个链表，具体分配策略如下(按需分配，啥时候需要啥时候再分配，不需要就不分配)：
刚刚开启事务时，一个Undo页面链表也不分配。
当事务执行过程中向普通表中插入记录或者执行更新记录主键的操作之后，就会为其分配一个普通表的insert undo链表
当事务执行过程中删除或者更新了普通表中的记录之后，就会为其分配一个普通表的update undo链表
当事务执行过程中向临时表中插入记录或者执行更新记录主键的操作之后，就会为其分配一个临时表的insert undo链表
当事务执行过程中删除或者更新了临时表中的记录之后，就会为其分配一个临时表的update undo链表

（2）多个事务中的Undo页面链表

为了尽可能提高undo日志的写入效率，不同事务执行过程中产生的undo日志需要被写入到不同的Undo页面链表中

11、undo日志具体写入过程：

（1）Undo Log Segment Header：

每一个Undo页面链表都对应着一个段，称之为Undo Log Segment

（2）Undo Log Header：

一个事务在向Undo页面中写入undo日志时的方式是十分简单暴力的，就是直接往里怼，写完一条紧接着写另一条，各条undo日志之间是亲密无间的。写完一个Undo页面后，再从段里申请一个新页面，然后把这个页面插入到Undo页面链表中，继续往这个新申请的页面中写；
在每写入一组undo日志时，都会在这组undo日志前先记录一下关于这个组的一些属性，设计InnoDB的大叔把存储这些属性的地方称之为Undo Log Header

（3）总结：

对于没有被重用的Undo页面链表来说，链表的第一个页面，也就是first undo page在真正写入undo日志前，会填充Undo Page Header、Undo Log Segment Header、Undo Log Header这3个部分，之后才开始正式写入undo日志。对于其他的页面来说，也就是normal undo page在真正写入undo日志前，只会填充Undo Page Header。链表的List Base Node存放到first undo page的Undo Log Segment Header部分，List Node信息存放到每一个Undo页面的undo Page Header部分

12、重用Undo页面：

在事务提交后在某些情况下重用该事务的Undo页面链表，一个Undo页面链表是否可以被重用的条件很简单：
（1）该链表中只包含一个Undo页面，只有在Undo页面链表中只包含一个Undo页面时，该链表才可以被下一个事务所重用
（2）该Undo页面已经使用的空间小于整个页面空间的3/4。

13、回滚段

（1）概念：

Rollback Segment Header的页面，在这个页面中存放了各个Undo页面链表的frist undo page的页号，他们把这些页号称之为undo slot

（2）每一个Rollback Segment Header页面都对应着一个段，这个段就称为Rollback Segment，翻译过来就是回滚段

十九、事务隔离级别和MVCC

1、事务并发执行遇到的问题：

（1）脏写（Dirty Write）：

如果一个事务修改了另一个未提交事务修改过的数据，那就意味着发生了脏写；

例：Session A和Session B各开启了一个事务，Session B中的事务先将number列为1的记录的name列更新为’关羽’，然后Session A中的事务接着又把这条number列为1的记录的name列更新为张飞。如果之后Session B中的事务进行了回滚，那么Session A中的更新也将不复存在，这种现象就称之为脏写

（2）脏读（Dirty Read）：

如果一个事务读到了另一个未提交事务修改过的数据，那就意味着发生了脏读；

例：Session A和Session B各开启了一个事务，Session B中的事务先将number列为1的记录的name列更新为’关羽’，然后Session A中的事务再去查询这条number为1的记录，如果du到列name的值为’关羽’，而Session B中的事务稍后进行了回滚，那么Session A中的事务相当于读到了一个不存在的数据，这种现象就称之为脏读

（3）不可重复读（Non-Repeatable Read）：

如果一个事务只能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值，那就意味着发生了不可重复读；

例：在Session B中提交了几个隐式事务（注意是隐式事务，意味着语句结束事务就提交了），这些事务都修改了number列为1的记录的列name的值，每次事务提交之后，如果Session A中的事务都可以查看到最新的值，这种现象也被称之为不可重复读

（4）幻读（Phantom）：

如果一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来，那就意味着发生了幻读；

例：Session A中的事务先根据条件number > 0这个条件查询表hero，得到了name列值为’刘备’的记录；之后Session B中提交了一个隐式事务，该事务向表hero中插入了一条新记录；之后Session A中的事务再根据相同的条件number > 0查询表hero，得到的结果集中包含Session B中的事务新插入的那条记录，这种现象也被称之为幻读

注意：如果Session B中是删除了一些符合number > 0的记录而不是插入新记录，那Session A中之后再根据number > 0的条件读取的记录变少了，这种现象算不算幻读呢？明确说一下，这种现象不属于幻读，幻读强调的是一个事务按照某个相同条件多次读取记录时，后读取时读到了之前没有读到的记录；幻读只是重点强调了读取到了之前读取没有获取到的记录。

2、SQL标准中的四种隔离级别：

问题按照严重性来排一下序：脏写 > 脏读 > 不可重复读 > 幻读；
设立一些隔离级别，隔离级别越低，越严重的问题就越可能发生
SQL标准，在标准中设立了4个隔离级别：

READ UNCOMMITTED：未提交读。

READ COMMITTED：已提交读。

REPEATABLE READ：可重复读。

SERIALIZABLE：可串行化。

SQL标准中规定，针对不同的隔离级别，并发事务可以发生不同严重程度的问题，具体情况如下：

3、MySQL中支持的四种隔离级别：

MySQL在REPEATABLE READ隔离级别下，是可以禁止幻读问题的发生的

（1）MySQL的默认隔离级别为REPEATABLE READ，我们可以手动修改一下事务的隔离级别

（2）如何设置事务的隔离级别：

可以通过下边的语句修改事务的隔离级别：
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;

其中的level可选值有4个：
level: {
REPEATABLE READ
| READ COMMITTED
| READ UNCOMMITTED
| SERIALIZABLE
}

（3）设置事务的隔离级别的语句中，在SET关键字后可以放置GLOBAL关键字、SESSION关键字或者什么都不放，这样会对不同范围的事务产生不同的影响：

使用GLOBAL关键字（在全局范围影响）：
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
只对执行完该语句之后产生的会话起作用，当前已经存在的会话无效。

使用SESSION关键字（在会话范围影响）：
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
对当前会话的所有后续的事务有效，该语句可以在已经开启的事务中间执行，但不会影响当前正在执行的事务，如果在事务之间执行，则对后续的事务有效

上述两个关键字都不用（只对执行语句后的下一个事务产生影响）：
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
只对当前会话中下一个即将开启的事务有效，下一个事务执行完后，后续事务将恢复到之前的隔离级别，该语句不能在已经开启的事务中间执行，会报错的

（4）想要查看当前会话默认的隔离级别

SHOW VARIABLES LIKE ‘transaction_isolation’;

5、MVCC原理：

（1）

持续更新中。。。1