读《MySQL是怎样运行的》笔记记录

---

前言

深入学习一门技术，亦可以达到面试等级，最好的方法无不是在现有的知识基础上，再去阅读相关书籍，到达更深层的理解。本文内容，便是在以前学习MySQL的基础上，再去读《MySQL是怎样运行的》一书，并通过阅读本书，做的笔记记录。（注意本文只是针对自己在现有的基础上，对自己内容知识的补充，如果想详细了解内容，建议还是自己看书）

第1章：初始MySQL

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

TCP/IP

MySQL采用TCP作为服务器和客户端之间的网络通信协议。并且MySQL服务器会默认监听3306端口。如果3306端口被占用或者想自定义监听的端口，在启动服务器程序时，输入如下命令即可
这样就把监听端口改成了3307

mysqld -P3307

命名管道和内存共享

如果是在Windows电脑下，并且是在自己电脑上联系使用MySQL，那么可以考虑在客户端进程和服务器进程之间使用命名管道或共享内存进行通信。不过需要参数设置。
对于使用命名管道进行进程通信：需要在启动服务器程序的命令中加上–enable-named-pipe参数，然后在启动客户端程序的命令中加上–pipe或者–protocol=pipe参数。
对于使用共享内存进行进程通信：需要在启动服务器程序的命令中加上–shared-memory参数。也可以在启动客户端程序的命令中加上–protocol=memory参数来显示指定使用共享内存进行通信。

服务器处理客户端请求

客户端向服务器发送请求（sql语句）的执行过程：
以比较复杂的查询请求为例：

服务器在处理客户端请求时，大概需要部分：连接管理、解析与优化、存储引擎。

连接管理

每当有一个客户端进程连接到服务器进程时，服务器进程都会创建一个线程专门处理与这个客户端的交互；当该客户端退出时会与服务器断开连接，服务器并不会立即把与该客户端交互的线程销毁，而是把它缓存起来，在另一个新客户端再进行连接时，把这个缓存的线程分配给该新客户端。这样就不用频繁地创建和销毁线程，从而节省开销。

解析与优化

查询缓存

MySQL服务器程序处理查询请求的过程中会把刚刚处理过的查询请求和结果缓存起来。如果下一次有同样的请求过来，直接从缓存中查找结果就好了，就不用再去底层的表中查找了，这个查询缓存可以在不同的客户端之间共享。但如果这两个查询请求有任何字符上的不同（例如，空格、注释、大小写），都会导致缓存不会命中。并且如果查询请求中包含某些系统函数、用户自定义变量和函数、系统表，则这个请求就不会被缓存。
对于缓存失效问题： MySQL的缓存系统会检测涉及的每张表，只要该表的结构或者数据被修改，比如对该表使用了insert、update、delete、truncate table、alter table、drop table或者drop database语句，则与该表有关的所有查询缓存都将变为无效并从查询缓存中删除。
系统性能： 查询缓存有时可以提升性能，但不得不因维护这块缓存而造成一些开销。比如每次都要去查询缓存中检索，查询请求处理完后需要更新询缓存，需要维护该查询缓存对应的内存区域等。从MySQL 5.7.20开始，不推荐使用查询缓存，在MySQL 8.0直接将其删除。

语法解析

客户端程序发送过来的请求只是一段文本，所以MySQL服务器程序首先要对这段文本进行分析，判断请求的语法是否正确，然后从文本中将要查询的表、各种查询条件都提取出来放到MySQL服务器内部使用的一些数据结构上。

查询优化

MySQL的优化程序会对我们的语句做一些优化，如外连接转换为内连接、表达式简化、子查询转为连接等一堆东西。

存储引擎

MySQL服务器把数据的存储和提取操作都封装到了一个名为存储引擎的模块中。在物理上如何表示记录，怎么从表中读取数据，以及怎么把数据写入具体的物理存储器上，都是存储引擎负责的事情。

第2章：MySQL的启动配置

这章主要讲述关于MySQL的配置，感觉没什么可以做笔记的，毕竟在配置中哪里遇到问题，就在网上搜哪里就行了啊

第3章：字符集和比较规则

字符集简介

计算机存储字符串实际上存储的是二进制，需要建立字符与二进制数据的映射关系。将字符映射成二进制数据的过程叫做编码，将二进制数据映射到字符的过程叫做解码。

一些重要的字符集

ASCII字符集：共收录128个字符，使用一个字节来进行编码。
GB2312字符集：收录了汉字等一些字母，同时又兼任ASCII字符集。在编码中，如果该字符在ASCII字符集中，则采用一字节编码，否则采用两字节编码。
GBK字符集：只是对GB2312在收录范围上进行了扩充。
UTF-8字符集：兼任ASCII字符集。采用变长编码方式，编码一个字符时需要使用1~4字节。

MySQL中支持的字符集和比较规则

在MySQL中，字符集表示一个字符所用的最大字节长度
在MySQL的设计中：
utf8mb3：“阉割”过的UTF-8字符集，使用1~3字节表示字符。
在MySQL中utf8是utf8mb3的别名，所以在MySQL中提到utf8时，就意味着使用1~3字节来表示一个字符。

utf8mb4:正宗的UTF-8字符集，使用1~4字节表示字符。
比如：存储一些emoji表情，使用4字节编码一个字符，就使用utf8mb4。

字符集的查看

请看下图MySQL中常见的字符集：
Maxlen表示这种字符最多需要几个字节来表示一个字符

比较规则的查看

通过命令：

show collation [like 匹配模式]

通过命令查看utf8字符集的比较规则

规则命名的含义：
1.比较规则的名称以与其关联的字符集的名称开头；比如上面的查询结果，比较规则的名称都是以utf8开头。
2.后面紧跟着该比较规则所应应用的语言。比如，utf8_polish_ci表示波兰语的比较规则；utf8_general_ci是一种通用的比较规则
3.名称后缀意味着该比较规则是否区分语言中的重音、大小写等。如下图：

比如比较规则utf8_general_ci是以ci结尾的，说明不区分大小写。

字符集和比较规则的应用

各级别的字符集和比较规则

MySQL的4个级别的字符集和比较规则：服务器级别、数据库级别、表级别、列级别
服务器级别：
提供了两个系统变量来表示服务器级别的字符集和比较规则。
如图所示：

通过命令查看：

show variables like 'character_set_server';
show variables like 'collation_server';

数据库级别：
创建和修改数据库时可以指定数据库的字符集和比较规则：

create database 数据库名
		[[default] character set 字符集名称]
		[[default] collate  比较规则名称]
alter database  数据库名
		[[default] character set 字符集名称]
		[[default] collate 比较规则名称];

如果查看当前数据库使用的字符集和比较规则，可以查看两个系统变量：

注意不能通过修改这两个变量的值来改变当前数据库的字符集和比较规则。

表级别：
同样也可以在创建和修改表的时候指定表的字符集和比较规则。

列级别：
对于存储字符串的列，同一个表中不同的列也可以有不同的字符集和比较规则，可以在创建和修改列的时候指定该列的字符集和比较规则。语法如下：

create table 表名(
	列名  字符串类型  [character set 字符集名称] [collate 比较规则名称],
	其他列…
	);
alter table 表名 modify 列名  字符串类型 [character set 字符集名称] [collate 比较规则名称];

注意：在修改列的字符集时需要注意 ，如果列中存储的数据不能用修改后的字符集进行表示，则会发送错误 。比如，列最初使用的字符集是utf8，列中存储了一些汉字，现在把列的字符集转换为ascii就会出错，因为ascii字符集并不能表示汉字字符。

仅修改字符集或仅修改比较规则：
由于字符集和比较规则之间相互关联，因此如果只修改字符集，比较规则也会跟着变化；如果只修改比较规则，字符集也会跟着变化。具体规则如下：
只修改字符集，则比较规则将变为修改后的字符集默认的比较规则；
只修改比较规则，则字符集将变为修改后的比较规则对应的字符集

各级别字符集和比较规则小结：
如果创建或修改列时没有显式指定字符集和比较规则，则该列默认使用表的字符集和比较规则；
如果创建表时没有显式指定字符集和比较规则，则该表默认使用数据库的字符集和比较规则；
如果创建数据库时没有显式指定字符集和比较规则，则该数据库默认使用服务器的字符集和比较规则；
知道了字符集和比较规则，可以清楚的知道存储数据所占用的存储空间的大小。

客户端和服务器通信过程中使用的字符集

1.编码和解码使用的字符集不一致
字符串在计算机上体现就是一个字节序列。如果使用不同的字符集去解码这个字节序列，可能会出现乱码。
比如用utf-8编码，用gbk解码可能会出现乱码的错误。

2.字符集转换的概念
比如通过utf-8发来的数据，用utf-8解码，然后用按照gbk编码。把这个字符串从utf-8字符集转换为gbk字符集的过程就叫做字符集的转换。

3.MySQL中的字符集转换过程
MySQL客户端与服务器进行通信的过程中事先规定好的数据格式称为MySQL通信协议。

客户端发送请求：
一般情况下，客户端编码请求字符串使用的字符集与操作系统当前使用的字符集一致。
在启动客户端程序时，可以通过命令来选择编码格式：

mysql --default-character-set=utf8

此时客户端将会以utf-8字符集对请求的字符串进行编码

服务器接收请求：
每个客户端与服务器建立连接后，服务器都会为该客户端维护一个单独的character_set_client变量（session级别），并且服务器把接收到的字节序看做是使用系统变量character_set_client代表的字符集进行编码的字节序列。
客户端在编码请求字符串时实际使用的字符集与服务器在收到一个字节序列后认为该字节序列所采用的的编码字符集，是两个独立的字符集。

服务器处理请求：
服务器会将请求的字节序列当作采用character_set_client对应的字符集进行编码的字节序列，但在真正处理请求时又会将其转换为使用session级别的系统变量character_set_connection对应的字符集进行编码的字节序列。
这样做的作用：
看下面的SQL语句

select 'a' = 'A';

我们从结果中可以看到，这两个字符是相等的（因为我们此时的character_set_connection是utf8，对应的比较规则是utf8_general_ci不区分大小写）

我们将其比较规则修改一下，再进行比较。

set collation_connection=utf8_bin;

这个时候我们再看，这两个字符便不相等了，我们修改的是character_set_connection的比较规则，发现它起作用了！
与character_set_connection配套的还有个系统变量collation_connection这个系统变量表示这些字符串应该应用使用哪种比较规则。
另一种情况：请求中的字符串和某个列进行比较
看代码，创建个表

mysql> create table temp(
    -> c varchar(10)
    -> )engine=InnoDB charset=gbk;

mysql> insert into temp (c) values('我');

我们创建了如下表，只有一行数据。

接下来，我们要执行一条语句（铺垫一下，此时我们的character_set_connection为utf8，比较规则collation_connection为utf8_bin，而对应的列，我们在建表时已经规定它的编码方式了，为gbk和gbk_chinses_ci）

select * from temp where c = '我';

服务器在进行字节序列处理的时候，根据的是character_set_connection的字符集进行编码，也就是utf8，比较规则collation_connection为utf8_bin，而此时语句中我的编码便与之对应
另一方面，表的列中的我，采用的则是gbk编码和gbk_chinses_ci比较规则，那么这种情况下，该如何比较呢？
我们执行看看结果！

在这种情况下啊，列的字符集和排列规则的优先级更高。因此需要将请求中的字符串 ‘我’ 先从gbk字符集转换为utf-8字符集，然后再使用列c的比较规则utf8_general_ci进行比较。

服务器生成响应：
服务器将数据发送给客户端的编码格式，依据character_set_results

看下这三个系统变量的作用

这三个都是session级别。每个客户端与服务器建立连接后，服务器都会为这个连接维护这3个变量。
在服务器收到客户端连接时，服务器会将character_set_client 、character_set_connection和character_set_results这三个系统变量的值初始化为客户端的默认字符集。
如图：

比较规则的应用

比较规则通常用来比较字符串的大小以及对某些字符串进行排序

第4章： InnoDB记录存储结构

MySQL服务器中负责对表中的数据进行读取和写入工作的部分是存储引擎，真实的数据在不同存储引擎中的存放格式一般也是不同的。InnoDB是MySQL默认的存储引擎，也是我们最常用的存储引擎。

InnoDB页简介

InnoDB是一个将表中的数据存储到磁盘上的存储引擎，但真正处理数据的过程发送在内存中，所以需要把磁盘中的数据加载到内存中。InnoDB采取的方式是，将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位。InnoDB中的页大小一般为16KB，就是说一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。（在服务器运行过程中不可以更改页面大小）

InnoDB行格式

以记录为单位向表中插入数据，这些记录在磁盘上的存放形式也被称为行格式或者记录格式。
InnoDB存储引擎一共4种不同类型的行格式：COMPACT 、REDUNDANT、 DYNAMIC、 COMPRESSED
创建或修改表的语句中指定记录所使用的行格式：

create table 表名 (列的信息) row_format=行格式名称;
alter table 表名 row_format=行格式名称;

下面来介绍下这四种行格式

COMPACT 行格式

看下行的结构：

一条完整的记录被分为记录的额外信息和记录的真实数据。

1.记录的额外信息

• 变长字段长度列表：所有变长字段的真实数据占用的字节数都存放在记录的开头位置，从而形成一个变长字段长度列表**（逆序存放，并且只存储值为非NULL的列的内容长度，不存储值为NULL的列的内容长度）**
  对于变长字段长度列表中的字节数是用1字节还是用2字节表示，InnoDB的判断规则：
  假设某个字符集中最多需要W字节来表示一个字符（比如，utf8字符集中的W是3，ASCII字符集中的W就是1）；对于变长类型把varchar（M），这种类型表示能存储最多M个字符**（注意是字符不是字节），所以这种类型表示的字符串最多占用的字节数是MW**。假设该变长字段实际存储的字节数是L。那么确定是1字节还是2字节表示的规则是：
  **如果MW小于等于255，那么使用1字节来表示真实数据占用的字节数。**
  如果M*W大于255，并且L小于等于127，那么使用1字节来表示。
  如果M*W大于255，并且L大于127，那么使用2字节来表示。

NULL值列表的优化：
一条记录某些列可能存储NULL值，如果把这些NULL值都放到记录的真实数据中存储会很占地方，所以COMPACT行格式把一条记录中值为NULL的列同一管理起来。

2.记录的真实数据
MySQL除了自己定义列数据外，还会为每个记录默认地添加一些列（也称为隐藏列）。
具体如下图：

InnoDB表的主键生成策略： 优先使用用户自定义的主键作为主键；如果用户没有定义主键，则选取一个不允许存储NULL值的UNIQUE键作为主键；如果表中连不允许存储NULL值的UNIQUE键都没有定义，则InnoDB会为表默认添加一个名为row_id的隐藏列作为主键。
InnoDB会为每条记录都添加trx_id和roll_pointer这两个列，但是row_id是可选的（在没有自定义主键以及不允许存储NULL值的UNIQUE键的情况下才会添加该列）

3. char（M）列的存储格式
   对于**char（M）类型列，当列采用的是定长编码的字符集时，该列占用的字节数 被加到变长字段长度列表；而如果采用变长编码的字符集时（如utf8），该列占用的字节数就会被加到变长字段长度列表。并且采用变长编码字符集的char（M）**类型的列要求至少占用M字节（真实数据占不够，就用空格填充），比如类型为char（10）的列（utf8编码），该列存储的数据占用的字节长度范围就是10~30字节，即使是空字符串也会占用10字节。 （目的是：在将来更新该列时，在新值的字节长度大于旧值的字节长度但不大于10个字节时，可以在该记录处直接更新，而不是在存储空间中再重新分配一个新的记录空间，导致原有的记录空间成为所为的碎片化）

PEDUNDANT行格式

REDUNDANT行格式如下图：

1.字段长度偏移列表
REDUNDANT行格式会把该记录中所有的列（包括隐藏列） 的长度信息都按照逆序存储到长度偏移列表中，；并且它是采用两个相邻偏移量的差值来计算各个列值的长度

2.REDUNDANT行格式中NULL值的处理
将列对应的偏移量值的第一个比特位作为是否为NULL的依据

3.CHAR(M)列的存储格式
对于REDUNDANT行格式，不管该列使用的字符集是啥，只要使用CHAR（M）类型，该列的真实数据占用的存储空间大小就是该字符集表示一个字符最多需要的字节数和M乘积。比如，使用utf8字符集的char（10）类型的列，其真实数据占用的存储空间大小始终为30字节。

溢出列

在COMPACT和REDUNDANT行格式中，对于占用存储空间非常多的列，在记录的真实数据处只会存储该列的一部分数据，而把剩余的数据分散存储在几个其他的页中，然后在记录的真实数据处用20字节存储指向这些页的地址（这20字节还包括分散在其他页面中的数据所占用的字节数），从而可以找到剩余数据所在的页，如下图：

对于COMPACT和REDUNDANT行格式，如果某一列中的数据非常多，则在本记录的真实数据处只会存储该列前768字节的数据以及一个指向其他页的地址，然后把剩下的数据存放到其他页中。如下图：

如果一条记录的某个列中存储的数据占用的字节数非常多时，该列就可能成为溢出列。

DYNAMIC行格式和COMPRESSED行格式

这两个行格式不会在记录的真实数据处存储该溢出列真实数据的前768字节，而是把该列的所有真实数据都存储到溢出页中，只在记录真实数据处存储20字节大小的指向溢出页的地址。如下图所示：

第5章：InnoDB数据页结构

不同类型页简介

InnoDB为了不同目的而设计了多种不同的类型的页，比如存放表空间头部信息的页、存放Change Buffer信息页、存放INODE的信息页、存放undo日志信息的页，存放表中记录的那种类型页（索引页 或者成为数据页）

数据页的结构

数据页代表的这块16KB大小的存储空间可以划分为多个部分，如下图：

可以看下每个部分存储什么内容：

记录在页中的存储

自己存储的记录会按照指定的行格式存储到User Records部分。（一开始生成页的时候，其实并没有User Records部分）。插入流程如下：

记录头信息

我们首先看下记录头主要包含了那些信息

• deleted_flag
  这个属性用来标记当前记录是否被删除，占用1比特。
  值为1时表示记录被删除了，但还真实的在磁盘上，只打一个删除标记（不在磁盘移除它们的原因是，如果移除还需要在磁盘上重新排列其他的记录，这会带来性能的消耗），放在一个垃圾链表中。

• min_rec_flag
  B+树每层非叶子节点中的最小

• heap_no（在分配之后就不会发生改动了，即使之后删除l堆的某条记录，这条被删除记录的heap_no值也仍然保持不变）
  把一条记录（这条记录的deleted_flag可以为1）在堆（把记录一条一条无间排列的结构）中的相对位置称之为heap_no。在页面前边的记录heap_no相对较小，在页面后边的记录heap_no相对较大，每新申请一条记录的存储空间时，该条记录比物理位置在它前边的那条记录的heap_no值大于1。
  虚拟记录：一条代表页面中的最小记录（Infimum记录），另外一条代表页面中最大记录（Supremum记录），分别占据了heap_no的0和1，说明它们在页面中的相对位置最靠前。
  注意：比较记录的大小就是比较主键的大小
  虽然Infimum记录和Supremum记录没有主键值，但是任何用户记录都比Infimum记录大，任何用户记录都比Supremum记录小

• record_type
  这个属性表示当前记录的类型
  一共4种类型的记录，0表示普通记录，1表示B+树非叶节点的目录项记录，2表示Infimum记录，3表示Supremum记录

• next_record
  表示从当前记录的真实数据到下一条记录的真实数据的距离。
  比如，第1条记录的next_record值为32，意味着从第1条记录的真实数据的地址处向后找32字节便是下一条记录的真实数据。（下一条记录指的并不是插入顺序中的下一条记录，而是按照主键值由小到大的顺序排列的下一条记录）
  各条记录按照主键从小到大的顺序形成一个单向链表。
  next_record指针指向记录头信息和真实数据之间的位置的原因？原因是这个位置刚刚好，向左读取就是记录头信息，向右读取就是真实数据，并且还可以使记录中位置靠前的字段和它们对应的字段长度信息在内存中的距离更近，提高高速缓存的命中率。
  当数据页存在多条被删除的记录时，可以使用这些记录的next_record属性将这些被删除的记录组成一个垃圾链表，以备之后重用这部分存储空间

Page Directory（页目录）

分组中记录的条数规定：对于Infimum记录所在的分组只能有1条记录，Supremum记录所在的分组拥有的记录条数只能在1~8条之间，剩下的分组中记录的条数范围只能是4 ~8条之间。

在一个数据页中查找指定主键值的记录时，过程分为两步：
1.通过二分法确定该记录所在分组对应的槽，然后找到该槽所在分组中主键值最小的那条记录。
2.通过记录的next_record属性遍历该槽所在的组中的各个记录。

File Trailer（文件尾部）

为了检测一个页是否完整（也就是在刷新时有没有发生只刷新了一部分的尴尬情况），设计了一个File Trailer部分。

第6章：B+树索引

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

索引

创建索引时，如果添加元素，对页中记录进行增删改操作，可能会做一些诸如记录移动的操作来始终保证这个状态一直成立：下一个数据页中用户记录的键值必须大于上一个页用户记录的主键值。（页分裂）

B+树都不会超过4层

1.聚簇索引
B+树特点：

• 记录主键值的大小进行记录和页的排序
  页（包括叶子节点和内节点）内的记录按照主键的大小顺序排成一个单向链表。
  各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
  同一层级的页也排成一个双向链表。
• B+树的叶子节点存储的是完整的用户记录（包括隐藏列）

聚簇索引只能在搜索条件是主键值时才能发挥作用。

2. 二级索引（辅助索引）

以其他列作为搜索条件
也创建个B+树，但是该B+树叶子节点存储的并不是完成的用户记录，而是该索引列+主键这两个列的值。
通过该B+树的叶子节点处定位到第一条符合条件的那条用户记录，然后需要根据该记录中的主键信息到聚簇索引中查找到完整的用户记录。这个通过携带主键信息到聚簇索引中重新定位完整的用户记录的过程成为回表
然后再返回到这棵B+树的叶子节点处，找到刚才定位到的符合条件的那条用户记录，并沿着记录组成的单向链表向后继续搜索其他也满足的记录，每找到一条就继续回表操作，重复这个操作，直到下一条记录不满足为止。

3. 联合索引
   本质也是二级索引，先按其中一个排序，这个相同再按另一个排序。

InnoDB中B+树索引的注意事项

1.根页面万年不动
一个B+树索引的根节点自创建之日便不会再移动（也就是页号不再改变）

2.内节点中目录项记录的唯一性
对与像二级索引那样，索引不唯一，那在插入数据时，如果出现了重复，那该放入那页时懵了。因此二级索引的内节点的目录项内容由三部分构成：
索引列的值、 主键值、 页号

所以上面那个问题，就可以先比较该索引，如果相同，再比较主键值

3.一个页面至少容纳2条记录
避免B+树的层级增长得过高

MyISAM中的索引解决方案

MyISAM的索引方案是将索引和数据分开存储的。
1.将表中的记录按照记录的插入顺序单独存储在一个文件中（称之为数据文件）
2.把索引信息单独存储到另外一个文件（索引文件）
叶子节点存储的不是完整的用户记录，而是主键值与行号的组合。二级索引

MyISAM行格式有定长格式、变长格式、变长记录格式等。
对于变长格式，MyISAM会直接在索引叶子节点处存储该条记录在数据文件中的地址偏移量。由此可见，MyISAM的回表操作是十分快速的，，因为它是拿着地址偏移量直接到文件中取数据。

MySQL中创建和删除索引的语句

InnoDB和MyISAM会自动为主键或者带有UNIQUE属性的列建立索引。

第7章：B+树索引的使用

索引的代价

• 空间上的代价
  每建立一个索引，都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页。

• 时间上的代价

存储引擎需要额外的时间进行页面分裂、页面回收等操作，以维护节点和记录的排序。并且在执行查询语句前，首先生成执行计划，在生成执行计划时需要计算使用不同索引执行查询时所需的成本，最后选取成本最低的那个索引执行查询

应用B+树索引

扫描区间和边界条件

扫描区间：把待扫描记录的值所在的区间成为扫描区间
边界条件：形成扫描区间的搜索条件（如 id>20 and id <100）
单点扫描区间：只包含一个值的扫描区间
范围扫描区间：包含多个值的扫描区间
在有多个索引的情况下，在使用某个索引执行查询时，关键的问题就是通过搜索条件找出合适的扫描区间，然后再到对应的B+树中扫描区间中的第一条记录。
需要注意的是：

• IN操作符的语义与若干等值匹配操作符（=）之间用OR连接起来的语义是一样的，都会产生多个单点扫描区间。
• LIKE操作符比较特殊，只有在匹配完整的字符串或者匹配字符串前缀时才产生合适的扫描区间。

在执行一个查询语句时，首先需要找出所有可用的索引以及使用它们时对应的扫描区间。

1. 所有搜索条件都可以生成合适的扫描区间的情况
2. 有的搜索条件不能生成合适的扫描区间的情况
3. 从复杂的搜索条件中找出扫描区间
4. 使用联合索引执行查询时对应的扫描区间
   索引下推进行优化

索引用于排序

在MySQL中，这种在内存或者磁盘中进行排序的方式统称为文件排序。但是，如果order by子句中使用了索引，就有可能省去在内存或磁盘中的排序的步骤。

1. 使用联合索引进行排序时的注意事项
   order by子句后面的列的顺序也必须按照索引列的顺序给出，否则无法使用B+树索引。
   并且当联合索引的索引列左边连续的列为常量时，也可以使用联合索引对右边的列进行排序，如下面的SQL语句：

//key_part1 key_part2 key_part3为联合索引
select * from table where key_part1='a' and key_part2='b' order by key_part3 limit 10; 

2. 不可以使用索引进行排序的几种情况
   （1）asc desc混用
   对于联合索引，要求各个排序列的排序规则是一致的，要么都是按照asc（升序）规则，要么都是按照desc（降序）规则排序。
   对于降序规则排序的方法：
   在查找当前记录的上一条记录时，找到该记录所在组的第一条记录（一直根据记录的next_record属性找下一条记录，直到某条记录的头信息的n_owned属性值不为0，该记录就是本组中的“带头大哥”。然后再从页目录中找到“带头大哥”记录对应的槽的上一个槽，该槽对应记录的下一条记录就是本组中第一条记录），从第一条记录开始遍历该组中的记录，直到找到当前记录的前一条记录。
   对于order by子句中asc、desc混用的情况，MySQL 8.0引入了一种称为Descending Index的特性

（2）排序列包含非同一个索引的列
用来排序的多个列不是同一个索引中的，这种情况不能使用索引进行排序。

（3）排序列是某个联合索引的索引列，但是这些排序列在联合索引中并不连续
（4）用来形成扫描区间的索引列与排序列不同
（5）排序列不是以单独列名的形式出现在order by子句中
要想使用索引进行排序操作，必须保证索引列是以单独列名的形式（而不是修饰过的形式）出现。

索引用于分组

与使用B+树索引进行排序差不多，分组列的顺序也需要与索引列的顺序一致；也可以只使用索引列中左边连续的列进行分组。

回表的代价

InnoDB中的数据页会被存放到磁盘中的一个或者多个文件中，页面的页号对应着该页在磁盘文件中的偏移量。
一般情况下，可以给查询语句指定limit子句来限制查询返回的记录数，这可能会让查询优化器倾向于选择使用二级索引+回表的方式进行查询，原因是回表的记录越少，性能提升越高。

更好地创建和使用索引

（1）只为用于搜索、排序或分组的列创建索引
只为出现在where子句中的列、连接子句中的连接列，或者出现在order by或group by子句中的列创建索引
（2）考虑索引中不重复值的个数
在执行查询时，某个扫描区间中包含的二级索引记录数量越多，就会导致回表操作的代价越大。在为某个列创建索引时，需要考虑该列中不重复值的个数占全部记录条数的比例。如果比例太低，则说明该列包含过多重复值，那么在通过二级索引+回表的方式执行查询时，就有可能执行太多次回表操作。
（3）索引列的类型尽量小
数据类型越小，索引占用的存储空间就越小，在一个数据页就可以存放更多的记录，磁盘I/O带来的性能损耗也就越小（一次页面I/O可以将更多的记录加载到内存中），读写效率也就越高
（4）为列前缀建立索引
即只将字符串的前几个字符存放到索引中，也就是说在二级索引的记录中只保留字符串的前几个字符。但是，只为列前缀建立索引的方式无法支持使用索引进行排序的需求。
（5）覆盖索引
把索引中已经包含所有需求读取的列的查询方式（如二级索引中包含了主键）称为覆盖索引。排序操作也优先使用覆盖索引查询。
（6）让索引列以列名的形式在搜索条件中单独出现
如下SQL语句：

select * from table where key*2<4;

不会进行简化，而是会用全表扫描的方式进行

（6）新插入记录时主键大小对效率的影响
为了避免性能消耗，在插入主键时，最好让它一次递增
（7）冗余和重复索引

第8章：MySQL的数据目录

数据库和文件系统的关系

InnoDB、MyISAM这样的存储引擎都是把表存储在磁盘上，而操作系统又是使用文件系统来管理磁盘。所以，像InnoDB、MyISAM这样的存储引擎都是把数据存储在文件系统上。

MySQL数据目录

如何确定MySQL中的数据目录

可以通过命令查看：

show variables like 'datadir';

数据目录的结构

数据库在文件系统中的表示

每个数据库都对应数据目录下的一个子目录，或者说对应一个文件夹

表在文件系统中的表示

每个表的信息可以分为两种：

1. 表结构的定义
   描述表结构的文件 表名.frm
2. 表中的数据

• InnoDB是如何存储表数据的
  表空间：对应文件系统上一个或多个真实文件（不同表空间对应的文件数量可能不同）。每一个表空间可以被划分为很多个页，表数据就存放在某个表空间下的某些页中
  （1）系统表空间
  在一个MySQL服务器中，系统表空间只有一份。从MySQL 5.5.7到MySQL 5.6.5之间的各个版本中，表中的数据都会被默认存储到这个系统表空间。

（2）独立表空间
在使用独立表空间来存储表数据时，会在该表所属的数据库对应的子目录下创建一个表示该独立表空间的文件，其文件名和表名相同，如 表名.ibd

• MyISAM是如何存储表数据的
  MyISAM中的索引相当于全部都是二级索引，该存储引擎的数据和索引是分开存放的

第9章：InnoDB的表空间

表空间中的每一个页都对应着一个页号，可以通过这个页号在表空间中快速定位到指定的页面

独立表空间结构

区的概念

对于16KB的页来说，连续的64个页就是一个区，也就是说一个区默认占用1MB空间大小。每256个区被划分成一组。

第一个组最开始的3个页面的类型是固定的：
FSP_HDR:这个类型的页面用来登记整个表空间的一些整体属性以及本组所有的区。
IBUF_BITMAP:这个类型的页面用来存储关于change buffer的一些信息。
INODE：这个类型的页面存储了许多成为INODE Entry的数据结构。
其余各组最开始的2个页面的类型是固定的。

段的概念

尽量让页面链表中相邻的页的物理位置也相邻，这样在扫描叶子节点中大量的记录时才可以使用顺序I/O。所以引入区的概念，一个区就是在物理位置上连续的64个页（区里页面的页号都是连续的）。
在表中的数据量很大时，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区为单位进行分配。
叶子节点有自己独有的区，非叶子节点也有自己独有的区。存放叶子节点的区的集合就算是一个段，存放非叶子节点的区的集合也算是一个段。一个索引会生成两个段：一个叶子节点段和一个非叶子节点段。
对于“以完整的区为单位分配给某个段时，对于数据量较小的表来说太浪费存储空间”这种情况，提出了碎片区的概念。
在一个碎片区中，并不是所有的页都是为了存储同一个段的数据而存在的，碎片区中的页可以用于不同的目的。碎片区直属于表空间，并不属于任何一个段。

• 在刚开始向表中插入数据时，段是从某个碎片区以单个页面为单位来分配存储空间的；
• 当某个段已经占用了32个碎片区页面之后，就会以完整的区为单位来分配存储空间（原先占用的碎片区页面并不会被复制到新申请的完整的区中）。

区的分类

空闲的区、有剩余空闲页面的碎片的区、没有剩余空闲页面的碎片的区、附属于某个段的区

处于FREE、FREE_FRAG、以及FULL_FRAG这3种状态的区都是独立的，算是直属于表空间；而处于FSEG状态的区是附属于某个段的。

为了管理这些区，设计XDES Entry的结构对应区的一些属性。

• Sagment ID（8字节）：每一个段都有一个唯一的编号，用ID表示。前提是该区已经被分配给某个段了
• List Node（12字节）：这个部分可以将若干个XDES Entry结构串连成一个链表。
  

如果我们想定位表空间内的某一个位置，只需指定页号以及该位置在指定页号中的页内偏移量即可。

1. XDES Entry链表
   把FREE、FREE_FRAG、FULL_FRAG状态的区分别建立链表

2. 链表基节点
   为了找到这些链表，设计了名为List Base Node（链表基节点）：包含了链表的头节点和尾节点、以及包含了多少个节点的信息。
   

段的结构

系统表空间

MySQL进程只有一个系统表空间，系统表空间中需要记录一些与整个系统相关的信息。
InnoDB数据字典
存储元数据的表

第10章：单表访问方法

访问方法的概念

把MySQL执行查询语句的方式称为访问方法或者访问类型。同一个查询语句可以使用多种不同的访问方法来执行。

const

通过主键或者唯一二级索引列来定位一条记录的访问方法定义为const（意识是常数级别的，代价是可以忽略的）。不过这种const只能在与一个常数进行等值比较时才有效。

ref

普通二级索引进行等值比较时，单点扫描称为ref。
在执行key is NULL只能是ref访问方法，而不能是const方法（因为NULL可能会出现很多条）

ref_or_null

扫描区间为单点扫描和[NULL,NULL]时称为ref_or_null（注意 值为NULL的记录会被放在索引的最左边）

range

对扫描区间为若干个单点扫描区间或者范围扫描区间的访问方法称为range

index

把这种扫描全部二级索引记录的访问方法称为index访问方法

all

全表扫描

注意事项

索引合并

把使用多个索引来完成一次查询的执行方法称为index merge（索引合并）
索引合并的方法有下面3种：

1. Intersection索引合并
   如下SQL语句：

//key1  key3均是二级索引
select * from table where key1='a' and key3='b';

执行方案：同时使用idx_key1和idx_key3进行单点扫描，然后在找出主键值相同的列，再回表。（交集）
要求获得的记录都是按照主键值排序的
如果读取的记录不是按照主键值排序的，则不可以使用Intersection索引，比如下面的SQL查询：

select * from table where key1>'a' and key='b';

2. Union索引合并（并集）
   如下SQL语句：

select * from table where key1='a'  or key3='b';

先分别单点扫描，然后根据主键值进行去重，再根据去重后的主键值，执行回表（要求必须是按主键值进行排序的）

3.Sort-Union索引合并

第11章：连接的原理

连接简介

连接就是把各个表中的记录都取出来进行依次匹配，并把匹配后的组合发送给客户端。笛卡尔积

连接过程简介

首先确定第一个需要查询的表（驱动表）进行单表查询；然后根据获取到的记录，都需要到另一张表中查找匹配记录。（注意：每获得一条驱动表记录，就立即到被驱动表中寻找匹配的记录）

内连接和外连接

内连接：
若驱动表中的记录在被驱动表中找不到匹配的记录，则该记录不会加入到最后的结果集。

外连接：
即使驱动表中的记录在被驱动表中没有匹配的记录，也仍然需要加入到结果集。
为了解决有时需要把匹配失败加入结果集，有时不要把匹配失败加入结果集，所以把过滤条件分为了两种：
（1）where子句中的过滤条件
不论是内连接还是外连接，凡是不符合where子句中过滤条件的记录都不会被加入到最后的结果集。
（2）on子句中的过滤条件
如果无法在被驱动表中找到匹配on子句中过滤条件的记录，那么该驱动表仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充。
（注意：on子句是专门为“外连接驱动表中的记录在被驱动表找不到匹配记录时是否应该把该驱动表记录加入结果集中”这个场景提出的）

连接的原理

嵌套循环连接

驱动表只访问一次，但被驱动表却可能访问多次，且访问次数取决于对驱动表执行单表查询后的结果集中有多少条记录；这种连接执行方式称为嵌套循环连接。

使用索引加快连接速度

建立索引，减小扫描代价。
建议做好不要使用*作为查询列表，而是把真正用到的列作为查询列表。

基于块的嵌套循环连接

为了可以在把驱动表中记录加载到内存时，一次性地与驱动表中的多条记录进行匹配，大大减少重复从磁盘上加载被驱动表的代价。所以设计了Join Buffer（连接缓冲区）的概念

第12章：基于成本的优化

MySQL中执行成本是由两个方面组成的

• I/O成本：从磁盘到内存的加载过程损耗的时间称为I/O成本。
• CPU成本：读取记录以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称为CPU成本。

单表查询的成本

基于成本的优化步骤

1.根据搜索条件，找出所有可能使用的索引
2.计算全表扫描的代价
3.计算使用不同索引执行查询的代价
4.对比各种执行方案的代价，找出成本最大的那个方案

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

把通过直接访问索引对应的B+树来计算某个扫描区间内对应的索引记录条数的方式称为 index dive。

如果通过IN语句生成的单点扫描区间的数量小于200个，将使用index dive来计算各个单点扫描区间对应的记录条数；如果大于或等于200个，就不能使用index dive了，而是要使用索引统计数据来进行估算。（避免单点扫描个数较多，造成性能损失）

连接查询的成本

条件过滤

把查询驱动表后得到的记录条数称为驱动表的扇出。（驱动表的扇出值越小，对被驱动表的查询次数也就越小，连接查询的总成本也就越低）
下面两种情况下计算驱动表扇出值时，需要靠猜测：
（1）如果使用全表扫描的方式执行单表查询，那么计算驱动表扇出值时需要猜测满足全部搜索条件的记录到底有多少条
（2）如果使用索引来执行单表查询，那么计算驱动表扇出值时需要猜测除了满足形成索引扫描区间的搜索条件外，还满足其他搜索条件的记录有多少条。

两表连接的成本分析

在连接查询中“占大头”的其实是驱动表扇出数*单次访问被驱动表的成本，所以优化的重点就是这两点：
（1）尽量减少驱动表的扇出
（2）访问被驱动表的成本要尽量低
需要尽量在被驱动表的连接列上建立索引，这样就可以使用ref访问方法来降低被驱动表的访问成本了。如果可以，被驱动表的连接列最好是该表的主键或者唯一二级索引列，这样就可以把访问被驱动表的成本降至更低了。

第13章：InnoDB统计数据是如何收集的

统计数据的存储方式

永久性地存储统计数据：统计数据在磁盘上。
非永久性地存储统计数据：统计数据存储在内存上。

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

1. n_rows统计项的收集
   为什么是估值？ 计算方法：按照一定算法从聚簇索引中选取几个叶子节点页面，统计每个页面中包含的记录数量，然后计算一个页面中平均包含的记录数量，并将其乘以全部叶子节点的数量，结果就是该表的n_rows值。

第14章：基于规则的优化（内含子查询优化二三事）

条件化简

移除不必要的括号

如下面SQL语句：

select * from (t1,(t2,t3)) where t1.a=t2.a and t2.b=t3.b;

优化器会把语句中不必要的括号移除掉，移除后的效果如下所示：

select * from t1,t2,t3 where t1.a=t2.a  and t2.b=t3.b;

常量传递

当使用and操作符将这个表达式和其他涉及列a的表达式连接起来时，可以将其他表达式中a的值替换为常量，比如下面这个表达式：

a=5 and b>a

就可以被转换为：

a=5 and b>5;

移除没用的条件

对于一些明显的永远true或者false的表达式，优化器会移除掉它们。
如下面的表达式：

(a<1 and b=b) or (a=6 or 5!=5)

很明显，b=b这个表达式永远为true，5！=5这个表达式永远为false，所以简化后成了如下：

(a<1 and true) or (a=6 or false);

表达式计算

如果表达式只包含常量，它的值会被先计算出来，如 a=5+1，会被简化成a=6；
在搜索条件中，只有搜索列和常数使用某些运算符连接起来，才可能形成合适的范围区间来减少需要扫描的记录数量。所以，最好让索引列以单独的形式出现在搜索条件表达式中。

having子句和where子句的合并

如果查询语句中没有出现诸如sum、max这样的聚集函数以及group by子句，查询优化器就把having子句和where子句合并起来。

常量表检测

默认下面这两种类型的查询运行的特别快：

• 类型1：查询的表中一条记录都没有，或者只有一条记录。

• 类型2：使用主键等值匹配或者唯一二级索引列等值匹配作为搜索条件来查询某个表。
  默认这两种查询方式花费的时间特别少，把通过这两种方式查询的表称为常量表。
  查询优化器在分析一个查询语句时，首先执行常量表查询，然后把查询中涉及该表的条件全部替换成常数，最后再分析其余表的查询成本。

外连接消除

在外连接查询中，指定的where子句中包含被驱动表中的列不为NULL值的条件称为空值拒绝。在被驱动表的where子句符合空值拒绝的条件后，外连接和内连接可以相互转换。这种转换带来的好处就是优化器可以通过评估表的不同连接顺序的成本，选出成本最低的连接顺序来执行查询。

子查询优化

子查询语法注意事项

1.子查询必须用小括号括起来
2.在select子句中的子查询必须是标量子查询

子查询在MySQL中是怎么执行的

1. 标量子查询、行子查询的执行方式
2. in子查询优化
   （1）物化表的提出
   对于不相关的in子查询，比如下面这样：

select * from s1 where key1 in (select comm_filed from s2 where key='a');

对于这样，MySQL的处理策略是将子查询的结果集当作外层查询的参数，而是将该结果集写入一个临时表中。在将结果集写入临时表时，有两点需要注意（该临时表的列就是子查询结果集中的列；写入临时表的记录会被去重）。把那个存储子查询结果集的临时表称为物化表。物化表中的记录都建立了索引（基于内存的物化表有哈希索引，基于磁盘的物化表有B+树索引）

（2）物化表转连接
把子查询的结果集放到临时表中后，相当于两个表直接的操作，内连接。

（3）将子查询转换为半连接
如上面的SQL语句；半连接概念。将s1表和s2表进行半连接的意思就是：对于s1表中的某条记录来说，我们只关心在s2表中是否存在与之匹配的记录，而不关心具体有多少条记录与之匹配，最终的结果集中只保留s1表的记录。

第15章：explain详解

MySQL提供了explain语句，可以查看某个查询语句的具体执行计划

执行计划输出中各个列详解

table

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

id

查询语句中每出现一个select关键字，就会为它分配一个唯一的id值。
在连接查询的执行计划中，每个表都会对应一条记录，这些记录的id列的值是相同的；出现在前面的表表示驱动表，出现在后面的表表示被驱动表

对于union， id会多个NULL，原因：union会把多个查询的结果集合并起来并对结果集中的记录进行去重。（会创建个临时表）

select_type

type

type列表明访问该表执行查询的方法是啥。

possible_keys 和key

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

第16章：optimizer trace的神奇功效

optimizer trace简介

optimizer trace功能可以让用户方便地查看优化器生成执行计划的整个过程

第17章：InnoDB的Buffer Pool

InnoDB的Buffer Pool

Buffer Pool的概念

为了缓存磁盘中的页，在MySQL服务器启动时就向操作系统申请了一片连续的内存-------Buffer Pool（缓冲池）。默认情况下，Buffer Pool只有128MB。

Buffer Pool内部组成

Buffer Pool对应的一片连续的内存被划分为若干个页面，页面大小与InnoDB表空间使用的页面大小一致，默认都是16KB，称为缓冲页。InnoDB为每一个缓冲页都创建了一些控制信息，每个缓冲页对应的控制信息占用的内存大小是相同的。把每个页对应的控制信息占用的一块内存称为一个控制块，控制块与缓冲页是一 一对应的。

free链表的管理

把所有空闲的缓冲页对应的控制块作为一个节点放到链表中，这个链表也可以称为free链表（或者说空闲链表）。
为了管理free链表，为这个链表定义了一个基节点，链表的基节点占用的内存空间并不包含在为Buffer Pool申请的一大片连续内存空间之内，而是一块单独申请的内存空间。

缓冲页的哈希处理

为了确定该页是否在缓冲区？
根据表空间号+页号来定位一个页，也就是相当于表空间号+页号是一个key（键），缓冲页控制块 就是对应的value（值）。通过哈希表来确定。
如果有直接使用该缓冲页就好，如果没有，就从free链表中选一个空闲的缓冲页，然后把磁盘中对应的页加载到该缓冲页的位置。

flush链表的管理

脏页：缓冲页的数据与磁盘页上的不一致。
每次修改缓冲页后，不立即把修改刷新到磁盘上，而是在未来的某个时间点进行刷新。
创建一个存储脏页的链表，凡是修改过的缓冲页对应的控制块都会作为一个节点加入到这个链表（flush链表）中。

LRU链表的管理

（1）缓冲区不够的窘境
free链表没有空闲的缓冲页，需要移除些缓冲页？
移除使用不频繁的
（2）简单的LRU链表
LRU链表：为了按照最近最少使用的原则去淘汰缓冲页的，所以这个链表可以被称为LRU链表。
对LRU链表的处理：

• 如果该页不在Buffer Pool中，在把该页从磁盘加载到Buffer Pool中的缓冲页时，就把该缓冲页对应的控制块作为节点塞到LRU链表的头部；
• 如果该页已经被加载到Buffer Pool中，则直接把该页对应的控制块移动到LRU链表的头部。

（3）划分区域的LRU链表
预读：InnoDB执行当前的请求时，可能会在后面读取某些页面，于是就预先把这些页面加载到Buffer Pool中。
可能降低Buffer Pool命中率的两种情况：
加载到Buffer Pool中的页不一定被用到； 如果有非常多的使用频率偏低的页被同时加载到Buffer Pool中，则可能会把那些使用频率非常高的页从Buffer Pool中淘汰掉。

为了解决这两种情况，InnoDB把LRU链表按照一定比例分成两截：

• 一部分存储使用频率非常高的缓冲页；这一部分链表也称为热数据，或者称为young区域；
• 另一部分存储使用频率不是很高的缓冲页；这一部分链表也称为冷数据，或者称为old区域。

InnoDB针对降低Buffer Pool命中率的情况进行优化：
针对预读的页面可能不进行后续访问的优化,当初次页面加载到Buffer Pool中，该缓冲页对应的控制块会放大old区域的头部，而不会影响young区域中使用比较频繁的缓冲页。
针对全表扫描时，短时间内访问大量使用频率非常低的页面的优化，在对某个处于old区域的缓冲页进行第一次访问时，就在它对应的控制块中记录下这个访问时间，如果后续的访问时间与第一次访问的时间在某个时间间隔内，那么该页面就不会从old区域移动到young区域的头部，否则将它移动到young区域的头部。

（4）更进一步优化LRU链表
只有被访问的缓冲页位于young区域1/4的后面时，才会被移动到LRU链表的头部，这样可以降低调整LRU链表的频率。

刷新脏页到磁盘

后台有专门的线程负责每隔一段时间就把脏数据刷新到磁盘，这样可以在不影响用户线程处理的正常的请求。刷新方式主要有下面两种：
（1）从LRU链表的冷数据中刷新一部分页面到磁盘。
（2）从flush链表中刷新一部分页面到磁盘

第18章：事务简介

事务的起源

原子性

隔离性

保证其他的状态转换不会影响到本次状态转换，这个规则称为隔离性。

一致性

数据库中的数据全部符合现实世界中的约束，就说这些数据是一致的，或者说符合一致性的。

持久性

事务的概念

把需要保证原子性、隔离性、一致性和持久性的一个或多个数据库操作称为事务。
事务的状态：活动的、部分提交的、失败的、中止的、提交的。

MySQL中事务的语法

如果不显示指定事务的访问模式，那么该事务的访问模式就是读写模式。
目前只有InnoDB和NDB存储引擎支持事务。

推荐一个零声学院免费公开课程，个人觉得老师讲得不错，
分享给大家：[Linux，Nginx，ZeroMQ，MySQL，Redis，
fastdfs，MongoDB，ZK，流媒体，CDN，P2P，K8S，Docker，
TCP/IP，协程，DPDK等技术内容，点击立即学习:服务器课程