跳至主要內容

《MySQL是怎样运行的：从根上理解MySQL》

Jayczee2025年2月8日大约 67 分钟学习笔记MySQL

感谢原书作者

感谢原书作者的总结，原书地址：原书地址

免费分享出来的Github Page：Github Page

有机会还是付费支持一下原作者，此处仅总结个人学习到的知识点，不展开说明。

第一章 重新认识MYSQL

  1. MySQL是C/S架构的，默认使用TCP协议通信。
  2. mysqld是服务端程序，可用于直接启动一个mysqld进程。
  3. mysqld_safe是一个脚本，用于启动mysqld的同时启动一个守护进程，当mysqld挂掉时重启，还会将错误信息日志等重定向到别的文件中方便调查。
  4. mysql.server间接调用mysql_safe，用于启动和停止MySQL服务器的脚本。
  5. mysql用于启动客户端程序与服务端进行通信，启动参数可使用-h指定服务器地址，-u指定用户，-p指定密码。
  6. mysqld启动时使用-P指定监听端口，同样客户端mysql启动时也可以使用-P指定服务端端口。
  7. 服务端处理客户端查询请求大致分为连接管理、解析与优化、存储引擎三个部分，如下图:
MySQL客户端处理客户端请求
MySQL客户端处理客户端请求
  1. 服务端与客户端建立连接后，服务端会创建一个线程处理该客户端的请求。当连接断开后，该线程会放入缓存，避免频繁创建与销毁线程带来的开销。
  2. 连接创建后，需要经过解析与优化，分别是查询缓存、语法解析、查询优化。
  3. 查询缓存只有查询语句SQL与先前的SQL一模一样时才有机会命中缓存，任何的不同都不会命中。
  4. 包含某些系统函数（比如NOW）、用户自定义变量与函数以及系统表的SQL的查询结果不会被缓存。
  5. 如果某表被更新，那么相关的缓存全都会失效。
  6. 如果缓存未命中，则进入语法解析阶段。
  7. 语法解析完成后进入查询优化，最终会生成一个执行计划。可以使用EXPLAIN命令查看。截止到目前为止尚未访问真实表。
  8. 查询缓存、语法解析、查询优化划分为Mysql Server的功能，而真实存取数据的功能划分为存储引擎的功能。各种存储引擎向Mysql Server层提供统一的调用接口（存储引擎API）。
  9. Mysql Server完成查询优化后，根据生成的执行计划调用存储引擎API，直接返回数据。
  10. Mysql存储引擎包含：
存储引擎描述
ARCHIVE用于数据存档（行被插入后不能再修改）
BLACKHOLE丢弃写操作，读操作会返回空内容
CSV在存储数据时，以逗号分隔各个数据项
FEDERATED用来访问远程表
InnoDB具备外键支持功能的事务存储引擎
MEMORY置于内存的表
MERGE用来管理多个MyISAM表构成的表集合
MyISAM主要的非事务处理存储引擎
NDBMySQL集群专用存储引擎

最常用的是InnoDB和MyISAM。

  1. 存储引擎是负责对表中的数据进行提取和写入的，可以为不同的表设置不同的存储引擎。

第二章 启动选项和系统变量

  1. mysqld --skip-networking 禁止客户端TCP/IP通信。

  2. mysqld --default-storage-engine=MyISAM 设置默认存储引擎，未指定时默认为InnoDB。

  3. 启动选项有长形式和短形式，例如--host等价于-h，使用短形式时选项名和选项值之间可以没有空格，-p除外。

  4. 推荐使用配置文件来设置启动选项。

  5. 配置文件内容格式如下：

    [server]
(具体的启动选项...)

[mysqld]
(具体的启动选项...)

[mysqld_safe]
(具体的启动选项...)

[client]
(具体的启动选项...)

[mysql]
(具体的启动选项...)

[mysqladmin]
(具体的启动选项...)

    填写格式如下：

    [server]
option1            # 这是option1，该选项不需要选项值
option2 = value2   # 这是option2，该选项需要选项值

    也可以使用类命令行形式:

    --option1 --option2=value2

  6. 启动命令会读取多个组的配置，具体如下：

启动命令类别能读取的组
mysqld启动服务器[mysqld]、[server]
mysqld_safe启动服务器[mysqld]、[server]、[mysqld_safe]
mysql.server启动服务器[mysqld]、[server]、[mysql.server]
mysql启动客户端[mysql]、[client]
mysqladmin启动客户端[mysqladmin]、[client]
mysqldump启动客户端[mysqldump]、[client]

  1. 如果想指定mysql.server程序的启动参数，则必须将它们放在配置文件中，而不是放在命令行中。mysql.server仅支持start和stop作为命令行参数。

  2. 选项组的名称后加上特定的MySQL版本号，比如对于[mysqld]选项组来说，可以定义一个[mysqld-5.7]的选项组，它的含义和[mysqld]一样，但只有版本号为5.7的mysqld程序才能使用这个选项组中的选项。

  3. 如果在多个配置文件中设置了相同的启动选项，则以最后一个配置文件中的为准。需要参考配置文件优先级。

  4. 在统一配置文件中，如果读取到的多个组包含同一启动项，将以最后一个出现的组中的启动选项为准。

  5. 如果不希望mysql搜索默认路径的配置文件，则可以使用--defaults-file指定配置文件，没有则报错。

  6. 同一个启动选项既出现在命令行中，又出现在配置文件中，则以命令行中的启动选项为准。

  7. MySQL系统变量拥有作用范围GLOBAL和SESSION，即对全部客户端生效或对当前客户端生效。大部分系统变量可以在运行时使用SET命令动态修改。例如：

    SET SESSION default_storage_engine = MyISAM;

不指定作用范围时，默认为SESSION。

  1. SHOW VARIABLES查看的是SESSION范围的系统变量。可以通过SHOW GLOBAL VARIABLES查看GLOBAL范围的系统变量。
  2. 如果某个客户端改变了某个系统变量在GLOBAL作用范围的值，并不会影响该系统变量在当前已经连接的客户端作用范围为SESSION的值，只会影响后续连入的客户端在作用范围为SESSION的值。
  3. 并不是所有系统变量都具有GLOBAL和SESSION的作用范围。一些系统变量只具有GLOBAL作用范围，比如max_connections；一些系统变量只具有SESSION作用范围，比如insert_id；一些系统变量的值既具有GLOBAL作用范围，也具有SESSION作用范围，比如default_storage_engine；有些系统变量是只读的，并不能设置值，比如version。

第三章 字符集和比较规则

  1. 同一种字符集可以有多种比较规则。

  2. MySQL中的utf8是utf8mb3的缩写，utf8mb3是utf8字符集的阉割版，使用13个字节表示字符，**utf8mb4**才是正常的utf8字符集，使用14个字节表示字符。

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

  4. MySQL有4个级别的字符集和比较规则，分别是服务器级别、数据库级别、表级别与列级别。

  5. 创建数据库时若不指定数据库字符集和比较规则，则使用服务器级别的值。

  6. 单独修改字符集或比较规则时，只修改字符集，则比较规则将变为修改后的字符集默认的比较规则；只修改比较规则，则字符集将变为修改后的比较规则对应的字符集。

  7. MySQL中字符的转换依靠服务器解码时的字符集character_set_client、服务器将请求转换成目标字符集character_set_connection以及服务器返回结果的字符集character_set_results三个系统变量。过程如下：

    MySQL字符集转换
    MySQL字符集转换

  8. 可以使用SET NAMES 字符集名将上述三个系统变量一口气改为客户端字符集，这样可以省去转换过程。

第四章 InnoDB记录结构

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

  2. 记录在磁盘上的存放方式也被称为行格式或记录格式，分别有Compact、Redundant、Dynamic和Compressed行格式。

  3. Compact格式结构如下：

    Compact 行格式结构
    Compact 行格式结构

    变长字段长度列表：变长字段即MySQL中VARCHAR(M)、VARBINARY(M)、各种TEXT类型、各种BLOB类型等长度不固定的类型的字段。变长字段中存储多少字节的数据是不固定的，所以在存储真实数据时需要顺便把这些数据占用的字节数也存起来。

    在Compact行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表，各变长字段数据占用的字节数按照列的顺序逆序存放。

  4. Compact行真实占用字节长度 = 字符长度 * 相应字符编码一个字符占用的字节数。

  5. 变长字段长度列表中可能会用1个或2个字节来表示某个变长字段真实长度。若可变字段最大长度M<=255，则用1个字节表示（11111111）；若理论最大长度大于255，则看实际占用长度L，若L<=127则用1个字节，否则用2个字节。此处用127进行分割是因为有些字符可能需要两个字节来表示。判断一个字符是一个字节表示还是两个字节表示通过首位标志位是0是1来判断，0则为单字节字符，1则表示当前字节为整个字符的一半。

  6. 并不是所有记录都有变长字段长度列表，变长字段长度列表仅存储非NULL值列的可变字段长度，若所有列都不是可变字段长度列或所有可变字段长度列都为NULL，则该部分就不需要。

  7. 如果没有允许NULL值的列，那么NULL值列表也不存在。

  8. 如果存在允许NULL值的列，每一列在NULL值列表中对应一个二进制位，也是逆序排序，若为0则该列为非NULL值，若为1则该列为NULL值。

  9. MySQL规定NULL值列表必须用整数个字节表示，一个字节有8位，可以记录8列，若允许NULL值的列不足8列，则高位补0。

    NULL值列表高位补零
    NULL值列表高位补零

  10. 记录头由5个固定字节组成，即40位，代表着不同的含义：

记录头结构
记录头结构
名称大小（单位：bit）描述
预留位11没有使用
预留位21没有使用
delete_mask1标记该记录是否被删除
min_rec_mask1B+树的每层非叶子节点中的最小记录都会添加该标记
n_owned4表示当前记录拥有的记录数
heap_no13表示当前记录在记录堆的位置信息
record_type3表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录
next_record16表示下一条记录的相对位置
  1. MySQL每条记录除了自己定义的数据列之外，还会有一些隐藏列：DB_ROW_ID（唯一标识行id）、DB_TRX_ID（事务id）、DB_ROLL_PTR（回滚指针）。
  2. InnoDB表对主键的生成策略：优先使用用户自定义主键作为主键，如果用户没有定义主键，则选取一个Unique键作为主键，如果表中连Unique键都没有定义，InnoDB会为表默认添加一个名为DB_ROW_ID的隐藏列作为主键。因此，从上表中可以看出：InnoDB存储引擎会为每条记录添加DB_TRX_ID和DB_ROLL_PTR这两个列，但DB_ROW_ID是可选的（在没有自定义主键以及Unique键的情况下才会添加该列）。
  3. 在Compact行格式中，加不加入可变字段长度列表还要参考当前的字符集，例如char(n)类型的字段，若使用ascii字符集，则其为定长字段列，但如果使用utf8，则该列中的字符可能是1个字节也可能是3个字节表示的，此时需要将长度存储到可变长度字段列表中。
  4. Redundant格式行相比Compressed格式行，头部的可变字段长度列表变为字段长度偏移列表，用相邻两个数字的差值来表示各个字段（所有字段长度都会存储，包括隐藏列）的实际长度。
  5. Redundant格式行的记录头由6个字节组成，即48位：
名称大小（单位：bit）描述
预留位11没有使用
预留位21没有使用
delete_mask1标记该记录是否被删除
min_rec_mask1B+树的每层非叶子节点中的最小记录都会添加该标记
n_owned4表示当前记录拥有的记录数
heap_no13表示当前记录在页面堆的位置信息
n_field10表示记录中列的数量
1byte_offs_flag1标记字段长度偏移列表中每个列对应的偏移量是使用1字节还是2字节表示的
next_record16表示下一条记录的相对位置
  1. 字段长度偏移列表实质上是存储每个列中的值占用的空间在记录的真实数据处结束的位置，因此通过计算差值可以计算列长度。整条记录实际数据长度小于等于127（01111111），则用1个字节；大于127小于等于32767（0111111111111111），则用2个字节。超出之后存到溢出页，字段偏移长度表仅存储本页面上的偏移。
  2. 字段长度偏移列表无NULL值列表，列是否为NULL判断如下：当列是定长列时，将对应偏移量转为二进制，若第一位为1则该列值为NULL，其真实数据用0x00填充；若列为变长列，用偏移量减相邻值计算长度，若长度为0，则该列值为NULL。
  3. Redundant格式行中，char(n)类型列真实数据空间始终为字符集格式最大长度 * n，例如utf8的char(10)就始终为30。
  4. MySQL除了BLOB和TEXT，其他所有类型的一行（不包含记录头和隐藏列）总长不能超过65535个字节。
  5. 在Compact和Redundant行格式中，对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的一部分数据，把剩余的数据分散存储在几个其他的页中，然后记录的真实数据处用20个字节存储指向这些页的地址（当然这20个字节中还包括这些分散在其他页面中的数据的占用的字节数）。
  6. 一个页一般是16KB，当记录中的数据太多，当前页放不下的时候，会把多余的数据存储到其他页中，这种现象称为行溢出。
  7. Redundant行格式中，当某列数据非常长时，该行只会记录该列前768字节的数据，然后记录溢出页的地址。
Redundant存储行数据
Redundant存储行数据
Redundant行格式
Redundant行格式
  1. 不只是VARCHAR(M)类型的列，其他的TEXT、BLOB类型的列在存储数据非常多的时候也会发生行溢出。
  2. MySQL中要求一个页至少存储两行记录。
  3. MySQL 5.7、8默认行格式是Dynamic。
  4. Dynamic和Compressed行格式会把所有行溢出数据存储到其他页，原页面只记录页地址。Compressed会对页数据进行压缩。

第五章 InnoDB数据页结构

  1. 页是InnoDB管理存储空间的基本单位。

  2. MySQL中有许多不同类型的页。

    Index索引页结构
    Index索引页结构
名称中文名占用空间大小简单描述
File Header文件头部38字节页的一些通用信息
Page Header页面头部56字节数据页专有的一些信息
Infimum + Supremum最小记录和最大记录26字节两个虚拟的行记录
User Records用户记录不确定实际存储的行记录内容
Free Space空闲空间不确定页中尚未使用的空间
Page Directory页面目录不确定页中的某些记录的相对位置
File Trailer文件尾部8字节校验页是否完整

  1. 我们自己插入的记录会以我们设置好的行格式存储在User Records的部分。User Records起初不存在，每当插入数据时会从Free Space中申请。Free Space使用完即页使用完了。

    记录在Index索引页中的存储
    记录在Index索引页中的存储

  2. 当表有主键时，一行记录不会生成row_id，而是把原来row_id的位置存储主键那一列的数据。

  3. User Records中记录的存储格式如下（记录本身的信息进行了省略，只显示了记录头和数据部分）：

    记录在Index索引页中的存储 2
    记录在Index索引页中的存储 2

  4. 当记录被删除时，记录头中的delete_mask将会从0置1，但是实际上并不会从磁盘上立刻删除，而是生成了一个“垃圾链表”，垃圾链表上的记录构成一个可重用空间，当有新纪录进来时会将数据覆盖到这个可重用空间上。

  5. heap_no 记录位置从2开始。0和1由两条隐藏记录最小记录和最大记录占用。

  6. 最小记录和最大记录都由5B的记录头和8B的固定部分组成：

    最小记录和最大记录构成
    最小记录和最大记录构成

  7. record_type为记录的类型，0为普通记录，1为B+树非叶子节点记录，2为最小记录，3为最大记录。

  8. next_record为当前记录到下一条记录的偏移量。通过该值，从最小记录开始，经过用户记录，到最大记录结束，构成了一个单向链表。

记录链表
记录链表
  1. 删除记录的步骤：
  • 将被删除记录的delete_mask置为1
  • 将上一条记录的值的next_record值指向被删除记录的下一条记录
  • 将被删除记录的next_record置为0
  • 最大记录的n_owned值减1

可以参考单向链表删除节点的方法

  1. 不论我们怎么对页中的记录做增删改操作，InnoDB始终会维护一条记录的单链表，链表中的各个节点是按照主键值由小到大的顺序连接起来的。
  2. 把页中所有正常的记录划分为几个组，每个组中的最后一条记录的n_owned代表该记录拥有多少条记录，即该组拥有多少条记录。将每一组中最后一条记录的地址偏移量统一保存到页尾部的一个位置，这个偏移量被成为Slot槽，组成的部分即为页目录.
  3. 对于最小记录所在的分组只能有 1 条记录，最大记录所在的分组拥有的记录条数只能在 1~8 条之间，剩下的分组中记录的条数范围只能在是 4~8 条之间。
  4. 记录分组步骤如下：
  • 初始只有最小记录和最大记录两个分组。
  • 之后每插入一条记录，就会找到主键值大于要插入记录且差值最小的一个槽，将槽对应的n_owned记录加1，表示新增一条记录，直到该分组记录数量达到8。
  • 在分组记录数达到8后，再添加一条记录，会将该分组拆分为大小分别为4和5的两个分组。此时会新增一个槽用于记录新增分组最后一条记录（最大记录）的偏移量。
  1. 根据主键值查找记录时，会通过二分法（用的还是双指针）找到对应记录所在的槽，然后再通过next_record遍历这个槽来找到对应的记录。
  2. Page Header页头用于记录页面中的状态信息，固定56个字节：
名称占用空间大小描述
PAGE_N_DIR_SLOTS2字节在页目录中的槽数量
PAGE_HEAP_TOP2字节还未使用的空间最小地址，也就是说从该地址之后就是Free Space
PAGE_N_HEAP2字节本页中的记录的数量（包括最小和最大记录以及标记为删除的记录）
PAGE_FREE2字节第一个已经标记为删除的记录地址（各个已删除的记录通过next_record也会组成一个单链表，这个单链表中的记录可以被重新利用）
PAGE_GARBAGE2字节已删除记录占用的字节数
PAGE_LAST_INSERT2字节最后插入记录的位置
PAGE_DIRECTION2字节记录插入的方向
PAGE_N_DIRECTION2字节一个方向连续插入的记录数量
PAGE_N_RECS2字节该页中记录的数量（不包括最小和最大记录以及被标记为删除的记录）
PAGE_MAX_TRX_ID8字节修改当前页的最大事务ID，该值仅在二级索引中定义
PAGE_LEVEL2字节当前页在B+树中所处的层级
PAGE_INDEX_ID8字节索引ID，表示当前页属于哪个索引
PAGE_BTR_SEG_LEAF10字节B+树叶子段的头部信息，仅在B+树的Root页定义
PAGE_BTR_SEG_TOP10字节B+树非叶子段的头部信息，仅在B+树的Root页定义
  1. PAGE_DIRECTION用来表示最后一条记录插入的方向，若新的一条记录的主键值比上一条插入的记录大，则插入方向为右边，否则为左边。
  2. Page Header针对InnoDB的Index页记录各种信息，而File Header记录的是所有类型的页通用的信息，固定38个字节：
名称占用空间大小描述
FIL_PAGE_SPACE_OR_CHKSUM4字节页的校验和（checksum值）
FIL_PAGE_OFFSET4字节页号
FIL_PAGE_PREV4字节上一个页的页号
FIL_PAGE_NEXT4字节下一个页的页号
FIL_PAGE_LSN8字节页面被最后修改时对应的日志序列位置（英文名是：Log Sequence Number）
FIL_PAGE_TYPE2字节该页的类型
FIL_PAGE_FILE_FLUSH_LSN8字节仅在系统表空间的一个页中定义，代表文件至少被刷新到了对应的LSN值
FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID4字节页属于哪个表空间
  1. 每一个页都有单独、唯一的一个页号。
  2. 页类型如下：
类型名称十六进制描述
FIL_PAGE_TYPE_ALLOCATED0x0000最新分配，还没使用
FIL_PAGE_UNDO_LOG0x0002Undo日志页
FIL_PAGE_INODE0x0003段信息节点
FIL_PAGE_IBUF_FREE_LIST0x0004Insert Buffer空闲列表
FIL_PAGE_IBUF_BITMAP0x0005Insert Buffer位图
FIL_PAGE_TYPE_SYS0x0006系统页
FIL_PAGE_TYPE_TRX_SYS0x0007事务系统数据
FIL_PAGE_TYPE_FSP_HDR0x0008表空间头部信息
FIL_PAGE_TYPE_XDES0x0009扩展描述页
FIL_PAGE_TYPE_BLOB0x000ABLOB页
FIL_PAGE_INDEX0x45BF索引页，也就是我们所说的数据页
  1. 并非所有类型的页都有FIL_PAGE_PREV和FIL_PAGE_NECT属性。
  2. File Trailer固定由8个字节组成，前4个字节代表页的校验和，后4个字节代表页面被最后修改时对应的日志序列位置（LSN）。
  3. File Trailer所有类型的页通用。

第六章 B+树索引

  1. 在无索引且以非主键列为条件的查找中，查询过程是从第一页按顺序进行，效率较低。

  2. 新插入数据分配的页号不一定与上一条数据分配的页号连续，页与页之间以双向链表的形式连接，因此在物理存储上不一定连续。

  3. 记录头信息中的 record_type 为 1 的是目录项记录。

  4. 目录项记录仅包含主键值和页的编号两个列，而普通的用户记录的列是用户自定义的，可能包含多个列，此外还有 InnoDB 自己添加的隐藏列。

  5. 只有在存储目录项记录的页中，主键值最小的目录项记录的 min_rec_mask 值为 1，其他记录的 min_rec_mask 值均为 0。

  6. 存储目录项记录的页和存储用户记录的页是相互独立的，不会混合存储两种记录。

    目录项记录结构
    目录项记录结构
    B+树结构
    B+树结构

    实际用户记录存放在 B+ 树的最底层节点上，这些节点称为叶子节点或叶节点，其余用来存放目录项的节点称为非叶子节点或内节点，其中 B+ 树最上面的节点称为根节点。

  7. 拥有以下特性的 B+ 树称为 聚簇索引：

    • 使用记录主键值的大小进行记录和页的排序，包括三个方面的含义：

      • 页内的记录按照主键的大小顺序排成一个单向链表。
      • 各个存放用户记录的页根据页中用户记录的主键大小顺序排成一个双向链表。
      • 存放目录项记录的页分为不同的层次，在同一层次中的页也根据页中目录项记录的主键大小顺序排成一个双向链表。

    • B+ 树的叶子节点存储完整的用户记录。

    这种聚簇索引不需要在 MySQL 语句中显式使用 INDEX 语句去创建（后面会介绍索引相关的语句），InnoDB 存储引擎会自动为我们创建聚簇索引。另一个有趣的点是，在 InnoDB 存储引擎中，聚簇索引就是数据的存储方式（所有用户记录都存储在了叶子节点），也就是所谓的“索引即数据，数据即索引”。

  8. 聚簇索引只有在搜索条件为主键时才有效。

  9. 二级索引 / 辅助索引的特点如下：

    • 使用记录 c2 列的大小进行记录和页的排序，包括三个方面的含义：

      • 页内的记录按照 c2 列的大小顺序排成一个单向链表。
      • 各个存放用户记录的页根据页中记录的 c2 列大小顺序排成一个双向链表。
      • 存放目录项记录的页分为不同的层次，在同一层次中的页也根据页中目录项记录的 c2 列大小顺序排成一个双向链表。

    • B+ 树的叶子节点存储的不是完整的用户记录，而仅包含 c2 列和主键这两个列的值。

    • 目录项记录中不再是主键 + 页号的搭配，而变成了 c2 列 + 页号的搭配。

  10. 通过二级索引查找目标记录的步骤如下：

    • 确定目录项记录页
    • 通过目录项记录页确定用户记录真实所在的页
    • 在真实存储用户记录的页中定位到具体的记录
    • 由于 B+ 树的叶子节点中的记录只存储了 c2 和 c1（即主键）两个列，因此必须根据主键值再去聚簇索引中查找完整的用户记录。

    这个过程称为 回表。

  11. 联合索引是以多列的值的大小排序建立 B+ 树，本质上也是二级索引，区别在于只会生成一棵 B+ 树。

  12. 一个 B+ 树索引的根节点自诞生之日起，便不会再移动。最开始表只有一个节点，不断向其中添加记录，超出大小上限时，其数据会先分配到一个新的页，然后该新页进行 页分裂，分出另一个新页，将数据按相关索引列值大小排序分配到不同的页上，原来的节点升级为这两个页的根节点。此后每当 InnoDB 需要用到该索引时，会从固定的节点中取出该索引。

  13. 为了让新插入记录能找到自己在那个页里，需要保证在 B+ 树的同一层内节点的目录项记录除页号字段外是唯一的。因此，二级索引的内节点的目录项记录实际上由三个部分构成：

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

    此时列值相同时通过比较主键值进行排序。

  14. 在 MyISAM 中，索引和用户记录完全独立存储。先将用户记录存储到一张列表中，然后建立一张主键 -> 行号的索引，通过索引找到行号，再根据行号找到实际记录，因此 MyISAM 中的索引全都是二级索引。

  15. MyISAM 中对其他列建立索引的原理与 InnoDB 中类似，在叶子节点处存储的是相应列值 + 行号。

第七章 B+树索引的使用

  1. 匹配左边的列：命中多列的联合索引必须从左边开始，可以只查询部分列来命中部分索引。由于 InnoDB B+树的目录项记录是多列组成的单向链表，因此只能先匹配前面的列。

  2. 匹配列前缀：在匹配左边的列的前提下，搜索索引列时使用条件 'str%'，也能快速定位记录。

  3. 匹配范围值：B+树记录按照索引列值的大小进行排序，因此在使用范围搜索条件（如 >、< 等）时，也能快速定位记录。然而，对于多个索引列进行范围查找时，只有最左边的列可以利用 B+树索引快速定位记录。

  4. 范围查找的优先级：如果对多个索引列进行范围查找，而左边的列已经通过精确查找（已使用 B+树索引），那么下面第一个范围查找的列将使用 B+树索引。

  5. 使用索引列排序：查询语句中若使用索引列进行排序，可以省去文件排序的过程。在使用联合索引时，也需遵循前述规则。

  6. 不能使用联合索引进行排序的情况：

    • ORDER BY 中的字段 ASC 和 DESC 混用，排序方向不一致
    • WHERE 子句中使用了非排序列
    • 排序中包含非同一个索引的列
    • 排序中使用了复杂的表达式（只能使用简单的列排序，不能使用函数处理）

  7. 使用 GROUP BY：当 GROUP BY 中的列恰巧与索引一致时，可以使用 B+树进行分组，而非默认的在内存中分组。

  8. 回表记录的影响：需要回表的记录越多，使用二级索引的性能就越低。

  9. 全表查询的倾向：需要回表的记录数越多，MySQL 越倾向于使用全表查询。

  10. 尽量避免回表：若查询列表中只包含索引列，则可以避免回表操作带来的性能损耗。

  11. 索引创建：尽量只为用于分组、排序或搜索的列创建索引。

  12. 基数考虑：考虑列的基数（同一列中不同值的数量），为基数大的列建立索引。

  13. 索引列类型：索引列的类型尽量小。数据类型越小，查询时的比较速度就越快；数据类型越小，一个页存储的记录就越多，从而减少磁盘 I/O 带来的性能损耗。

  14. 字符串列索引：尽量只对字符串列的前面部分字符进行索引，尤其是字符串列存储值较大的时候。但这样会使得该列排序无法使用索引排序，只能使用文件排序。

  15. 索引列的独立性：让索引列在比较表达式中单独出现。例如：

    SELECT * FROM table WHERE age < 20 / 2

    而不是：

    SELECT * FROM table WHERE age * 2 < 20

    这样才能命中索引。

  16. 避免冗余索引：避免冗余索引和重复索引的出现。

第八章 MySQL的数据目录

  1. MySQL服务器程序在启动时会到文件系统的某个目录下加载一些文件，之后在运行过程中产生的数据也都会存储到这个目录下的某些文件中，这个目录就称为 数据目录。
  2. 每个数据库都对应数据目录下的一个子目录(文件夹)，除了系统数据库 information_schema。
  3. 页被划分于表空间中，表空间对应文件系统上的一个或多个文件。
  4. 表空间分为系统表空间、独立表空间等多种类型。
  5. 每个MySQL只会有一个系统表空间。
  6. 在MySQL 5.6.6 以及之后的版本中，InnoDB并不会默认的把各个表的数据存储到系统表空间中，而是为每一个表建立一个独立表空间，也就是说我们创建了多少个表，就有多少个独立表空间。使用独立表空间来存储表数据的话，会在该表所属数据库对应的子目录下创建一个表示该独立表空间的文件，文件名和表名相同，只不过添加了一个 .ibd 的扩展名而已。
  7. 在MySQL 8.0 及以上版本中，.frm 文件已被替换为数据字典，表结构信息存储在系统表中，而不是以文件形式存在。
  8. MyISAM没有表空间，表数据都存放在对应的数据库子目录下。 .MYD 代表表的数据文件，.MYI 代表表的索引文件。
  9. 存储视图的时候是不需要存储真实的数据的，只需要把它的结构存储起来就行了。和表一样，描述视图结构的文件也会被存储到所属数据库对应的子目录下面，只会存储一个视图名 .frm 的文件。但在 MySQL 8 之后页直接存在了数据字典里。
  10. MySQL会把数据库名和表名中所有除数字和拉丁字母以外的所有字符在文件名里都映射成 @+编码值的形式作为文件名。
  11. 系统数据库 mysql：存储了 MySQL 的用户账户和权限信息，一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。
  12. 系统数据库 information_schema：保存着 MySQL 服务器维护的所有其他数据库的信息，比如表、视图、触发器、列、索引等等。这些信息并不是真实的用户数据，而是一些描述性信息，有时候也称之为元数据。
  13. 系统数据库 performance_schema：保存着 MySQL 状态信息。
  14. 系统数据库 sys：通过视图把 information_schema 和 performance_schema结合起来，方便程序员查询信息。

第九章 InnoDB的表空间

  1. 对于 16KB 的页而言，每 64 页分为一个区（extent，共 1MB），每 256 个区分为一组（共 256MB）。

  2. 表空间中第一个组的头三个页的类型是固定的，分别为：

    • FSP_HDR（Flash Storage Protocol Header）：用于记录表空间的一些属性和本组所有的区，即（extent0~extent255）。一个表空间只有一个 FSP_HDR 类型的页。
    • IBUF_BITMAP（Input Buffer Bitmap）：存储本组所有的区的所有页关于 INSERT BUFFER 的信息。
    • INODE（Index Node 索引节点）：类型的页存储了许多称为 INODE 的数据结构。

  3. 其余组头两个页的类型是固定的，分别为：

    • XEDS：extent descriptor，用来登记本组 256 个区的属性。
    • IBUF_BITMAP。

  4. 为什么要引入区？如果仅有页的概念，并不影响存储引擎的运行。但是在数据量及其庞大时，已知数据都存储在聚簇索引和二级索引中，索引中的每一层都是一个双向链表，范围查询时我们定位数据只需要从找到最左边数据所在页和最右边数据所在页，遍历其中记录即可，但是这两个页物理位置可能相距非常远，此时的 IO 为随机 IO，相比顺序 IO 速度非常慢，所以需要引入区，区即为物理位置上 1MB 的连续空间，连续的 64 个页。为了尽可能地消除随机 IO。

  5. InnoDB 中把所有非叶子节点放到一个区，把叶子节点放到另外一个区。存放所有非叶子节点的区的结合为一个段，存放叶子结点的区的集合为另一个段。一个索引会生成两个段，一个叶子节点段，一个非叶子节点段。

  6. 但是记录并不是一开始就存储在段中的，零碎的记录先存入碎片区，碎片区不属于任何一个段，其直属于表空间。碎片区中的页可能不只存储了一个区的数据，而是多个区的数据。

  7. 区分为四种状态（类型，State）：FREE, FREE_FRAG, FULL_FRAG, FSEG（Fragmented Segment）。前三个状态时区直属于表空间，最后一个状态时区才属于某一个段。

  8. 为了方便管理区（不在段中的），引入 XDES Entry（Extent Descriptor Entry），结构如下：

    XDES Entry结构
    XDES Entry结构
    • Segment ID：表示当前区所在的段，前提是该区已经被分配给某个段了，否则没有意义。
    • List Node：指向前一个和后一个 XDES Entry。
    • State：区的状态。
    • Page State Bitmap：描述页的状态，总共 16 字节，128 位，一个区中 64 个页，每个页用两位来描述，第一位描述该页是否空闲，第二位还没用。

  9. 当某个段数据较少时，向该段中插入一条数据。MySQL 先会寻找该段中是否存在 FREE_FRAG 碎片区，若有则取其零碎页将数据插入其中。若没有，则向表空间申请一个 FREE 状态的区，然后从该区中取零碎页将数据插入，后续插入的数据都会插入该区，后续不同段使用零碎页都会从该区中取 FULL_FRAG。

  10. 通过 XDES Entry 中的 List Node，将 Free 状态的 XDES Entry 连接起来，形成 Free链表；FREE_FRAG 状态的 Entry 连接起来形成 FREE_FRAG链表；FULL_FRAG 的 Entry 连接起来形成 FULL_FRAG 链表。每当需要取 FREE_FRAG 状态的区时，就取 FREE_FRAG 链表的头结点，若链表无节点，就从 FREE 链表中取一个节点，插入数据后节点转移到 FREE_FRAG 链表。

  11. 当段中的数据已经占满 32 个零碎页时，就会直接申请完整的区来插入数据了。

  12. 同一个段中，根据区的状态，将其 XDES Entry 连接成不同的链表：

    • FREE：同一个段中，所有页都是空闲的区会被加入该列表。
    • NOT_FULL：同一个段中，仍有空闲页的区会被加入该列表。
    • FULL：同一个段中，没有空闲页的区会被加入该列表。

  13. 每个索引都有两个段，每个段都会维护上述三个链表。

  14. 用于定位上述链表的结构称为 List Base Node，链表基节点，结构如下：

List Base Node结构
List Base Node结构
  • List Length 表明该链表一共有多少节点。
  • First Node Page Number 和 First Node Offset 表明该链表的头节点在表空间中的位置。
  • Last Node Page Number 和 Last Node Offset 表明该链表的尾节点在表空间中的位置。

一般某个链表对应的 List Base Node 结构放置在表空间中固定的位置。
15. 正如有 XDES Entry 描述区，描述段也有对应的结构，称为 INODE Entry，结构如下：

INODE Entry结构
INODE Entry结构
  • Segment ID：所描述的段的编号。
  • NOT_FULL_N_USED：对应段的 NOT_FULL 链表已经使用了多少页，下次使用 NOT_FULL 可以直接依靠该值定位到，而不需要从头遍历节点。
  • 3个 List Base Node：分别为 FREE、NOT_FULL、FULL 链表的链表基节点。
  • Magic Number：表示该 INODE Entry 是否已经初始化，若值为 97937874 则表示已经初始化，否则没有。
  • Fragment Array Entry：段是零散页和一些完整区的集合。每个 Fragment Array Entry 对应着一个零散页，每个四字节，表示页号。
  1. 第一个组中的第一个页，即表空间中的第一个页，类型为 FSP_HDR 的页的结构如下：
FSP_HDR类型页结构
FSP_HDR类型页结构
名称中文名占用空间大小简单描述
File Header文件头部38字节页的一些通用信息
File Space Header表空间头部112字节表空间的一些整体属性信息
XDES Entry区描述信息10240字节存储本组 256 个区对应的属性信息
Empty Space尚未使用空间5986字节用于页结构的填充，没什么实际意义
File Trailer文件尾部8字节校验页是否完整
  1. File Space Header 结构如下：
File Space Header结构
File Space Header结构
名称占用空间大小描述
Space ID4字节表空间的 ID
Not Used4字节这 4 个字节未被使用，可以忽略
Size4字节当前表空间占有的页数
FREE Limit4字节尚未被初始化的最小页号，大于或等于这个页号的区对应的 XDES Entry 结构都没有被加入 FREE 链表
Space Flags4字节表空间的一些占用存储空间比较小的属性
FRAG_N_USED4字节FREE_FRAG 链表中已使用的页数量
List Base Node for FREE List16字节FREE 链表的基节点
List Base Node for FREE_FRAG List16字节FREE_FREG 链表的基节点
List Base Node for FULL_FRAG List16字节FULL_FREG 链表的基节点
Next Unused Segment ID8字节当前表空间中下一个未使用的 Segment ID
List Base Node for SEG_INODES_FULL List16字节SEG_INODES_FULL 链表的基节点
List Base Node for SEG_INODES_FREE List16字节SEG_INODES_FREE 链表的基节点

  • 其中对于 List Base Node for SEG_INODES_FULL List 和 List Base Node for SEG_INODES_FREE List：每个段对应的 INODE Entry 结构会集中存放到一个 类型为 INODE 的页 中，如果表空间中的段特别多，则会有多个 INODE Entry 结构，可能一个页放不下，这些 INODE 类型的页 会组成两种列表：

    • SEG_INODES_FULL 链表，该链表中的 INODE 类型的页都已经被 INODE Entry 结构填充满了，没空闲空间存放额外的 INODE Entry 了。

    • SEG_INODES_FREE 链表，该链表中的 INODE 类型的页都已经仍有空闲空间来存放 INODE Entry 结构。

  • 对于 FREE LIMIT，主要记录着从哪个页开始，其 XDES Entry 还未加入 FREE 链表。等什么时候空闲链表中的 XDES Entry 结构对应的区不够使了，再把之前没有加入 FREE 链表的空闲区对应的 XDES Entry 结构加入 FREE 链表，中心思想就是什么时候用到什么时候初始化，设计 InnoDB 的大佬采用的就是后者，他们为表空间定义了 FREE Limit 这个字段，在该字段表示的页号之前的区都被初始化了，之后的区尚未被初始化。

  • Next Unused Segment ID，即字面意思，下一个未使用的段 ID，每个表中每个索引对应着两个段，当创建新索引时，就是创建新的两个段，需要赋予每个段一个 ID，根据该值即可快速获得唯一 ID。

  1. XDES Entry 就存储在表空间中第一个页（FSP_HDR 类型页）中保存，之所以分散开市因为每个页空间有限。XDES Entry0 对应的就是区 extent0，XDES Entry255 对应的就是 extent255。
  2. 后续 XDES Entry 类型存储在每个组的第一个页（XDES 类型页）中，其结构和 FSP_HDR 非常相似。FSP_HDR 额外存储着表空间的一些属性。
  3. 与 FSP_HDR 类型的页对比，除了少了 File Space Header 部分之外，也就是除了少了记录表空间整体属性的部分之外，其余的部分是一样一样的。
XDES类型页结构
XDES类型页结构
  1. INODE 类型页用于存储 INODE Entry 结构，INODE 类型页结构如下：
INODE类型页结构
INODE类型页结构
名称中文名占用空间大小简单描述
File Header文件头部38字节页的一些通用信息
List Node for INODE Page List通用链表节点12字节存储上一个 INODE 页和下一个 INODE 页的指针
INODE Entry段描述信息16128字节
Empty Space尚未使用空间6字节用于页结构的填充，没什么实际意义
File Trailer文件尾部8字节校验页是否完整
  1. List Node for INODE Page List 为存储上一个 INODE 页和下一个 INODE 页的指针，与 File Page Header 中的 List Base Node for SEG_INODES_FULL List 与 List Base Node for SEG_INODES_FREE List 对应。
  2. 在 INDEX 类型页的 Page Header 中有以下两个属性：
名称占用空间大小描述
PAGE_BTR_SEG_LEAF10字节B+树叶子段的头部信息，仅在 B+树的根页定义
PAGE_BTR_SEG_TOP10字节B+树非叶子段的头部信息，仅在 B+树的根页定义

其对应的是一个名为 SEGMENT HEADER 的结构，其定义如下：

SEGMENT HEADER结构
SEGMENT HEADER结构
名称占用字节数描述
Space ID of the INODE Entry4INODE Entry 结构所在的表空间 ID
Page Number of the INODE Entry4INODE Entry 结构所在的页页号
Byte Offset of the INODE Ent2INODE Entry 结构在该页中的偏移量

PAGE_BTR_SEG_LEAF 记录着叶子节点段对应的 INODE Entry 结构的地址是哪个表空间的哪个页的哪个偏移量，PAGE_BTR_SEG_TOP 记录着非叶子节点段对应的 INODE Entry 结构的地址是哪个表空间的哪个页的哪个偏移量。这样子索引和其对应的段的关系就建立起来了。不过需要注意的一点是，因为一个索引只对应两个段，所以只需要在索引的根页中记录这两个结构即可。

  1. 系统表空间和独立表空间结构类似，只不过整个 MySQL 进程只有一个系统表空间，其表空间 ID 为 0，结构如下：
系统表空间结构
系统表空间结构
  1. MySQL 为了更好的管理用户数据而不得已引入的一些额外数据，这些数据也称为元数据。InnoDB 存储引擎特意定义了一些列的内部系统表（internal system table）来记录这些这些元数据:
表名描述
SYS_TABLES整个 InnoDB 存储引擎中所有的表的信息
SYS_COLUMNS整个 InnoDB 存储引擎中所有的列的信息
SYS_INDEXES整个 InnoDB 存储引擎中所有的索引的信息
| SYS_FIELDS          | 整个 InnoDB 存储引擎中所有的索引对应的列的信息 |
| SYS_FOREIGN         | 整个 InnoDB 存储引擎中所有的外键的信息        |
| SYS_FOREIGN_COLS    | 整个 InnoDB 存储引擎中所有的外键对应列的信息  |
| SYS_TABLESPACES     | 整个 InnoDB 存储引擎中所有的表空间信息        |
| SYS_DATAFILES       | 整个 InnoDB 存储引擎中所有的表空间对应文件系统的文件路径信息 |
| SYS_VIRTUAL         | 整个 InnoDB 存储引擎中所有的虚拟生成列的信息  |

这些系统表也被称为数据字典，它们都是以 B+树的形式保存在系统表空间的某些页中，其中 SYS_TABLES、SYS_COLUMNS、SYS_INDEXES、SYS_FIELDS 这四个表尤其重要，称之为基本系统表（basic system tables）。

26. SYS_TABLES 表结构如下：

| 列名     | 描述                                         |
|----------|----------------------------------------------|
| NAME     | 表的名称                                     |
| ID       | InnoDB 存储引擎中每个表都有一个唯一的 ID      |
| N_COLS   | 该表拥有列的个数                             |
| TYPE     | 表的类型，记录了一些文件格式、行格式、压缩等信息 |
| MIX_ID   | 已过时，忽略                                 |
| MIX_LEN  | 表的一些额外的属性                           |
| CLUSTER_ID| 未使用，忽略                                |
| SPACE    | 该表所属表空间的 ID                           |

其包含以 NAME 列为主键的聚簇索引以及以 ID 列建立的二级索引。

27. SYS_COLUMN 表结构如下：

| 列名     | 描述                                             |
|----------|--------------------------------------------------|
| TABLE_ID | 该列所属表对应的 ID                              |
| POS      | 该列在表中是第几列                              |
| NAME     | 该列的名称                                      |
| MTYPE    | main data type，主数据类型，即 INT、CHAR、VARCHAR、FLOAT、DOUBLE 等类型 |
| PRTYPE   | precise type，精确数据类型，修饰主数据类型的属性，如是否允许 NULL 值、是否允许负数等 |
| LEN      | 该列最多占用存储空间的字节数                    |
| PREC     | 该列的精度，通常未使用，默认值为 0               |

该表只包含以 (TABLE_ID, POS) 列为主键的聚簇索引。

28. SYS_INDEX 表结构如下：

| 列名               | 描述                                             |
|--------------------|--------------------------------------------------|
| TABLE_ID           | 该索引所属表对应的 ID                            |
| ID                 | InnoDB 存储引擎中每个索引都有一个唯一的 ID        |
| NAME               | 该索引的名称                                    |
| N_FIELDS           | 该索引包含列的个数                              |
| TYPE               | 该索引的类型，比如聚簇索引、唯一索引、修改缓冲区的索引、全文索引、普通的二级索引等 |
| SPACE              | 该索引根页所在的表空间 ID                        |
| PAGE_NO            | 该索引根页所在的页号                            |
| MERGE_THRESHOLD     | 如果页中的记录被删除到某个比例，就把该页和相邻页合并，这个值就是这个比例 |

该表只包含以 (TABLE_ID, ID) 列为主键的聚簇索引。

29. SYS_FIELDS 表结构如下：

| 列名       | 描述                                         |
|------------|----------------------------------------------|
| INDEX_ID   | 该索引列所属的索引的 ID                      |
| POS        | 该索引列在某个索引中是第几列                |
| COL_NAME   | 该索引列的名称                              |

该表只包含以 (INDEX_ID, POS) 列为主键的聚簇索引。

30. 只要有了上述 4 个基本系统表，也就意味着可以获取其他系统表以及用户定义的表的所有元数据。但这 4 个表的元数据就无法通过其他表查询，而是将其存储在数据字典的头部，用一个固定的页来记录这 4 个基本表的聚簇索引和二级索引的位置，即 Data Dictionary Header，结构如下：

![数据字典头部结构](/assets/images/note/mysql/m-25.png)

| 名称                        | 中文名               | 占用空间大小 | 简单描述                                         |
|-----------------------------|----------------------|--------------|--------------------------------------------------|
| File Header                 | 文件头部             | 38字节       | 页的一些通用信息                                 |
| Data Dictionary Header      | 数据字典头部信息     | 56字节       | 记录一些基本系统表的根页位置以及 InnoDB 存储引擎的一些全局信息 |
| Segment Header              | 段头部信息           | 10字节       | 记录本页所在段对应的 INODE Entry 位置信息         |
| Empty Space                 | 尚未使用空间         | 16272字节    | 用于页结构的填充，没什么实际意义                 |
| File Trailer                | 文件尾部             | 8字节        | 校验页是否完整                                   |

其中关于 Data Dictionary Header，

- **Max Row ID**：当一个表中没有主键和唯一索引时，该表就会生成一个隐藏的值唯一的 row_id 列，其最大值保存在该属性中，且所有拥有该列的表的 row_id 共享该属性。当向这类表中插入一条数据时，该值自增 1，新数据的 row_id 值即为自增后的新值。
- **Max Table ID**：InnoDB 存储引擎中，每当创建一个新表，该值自增 1，新表的值即为自增后的新值。
- **Max Index ID**：索引 ID，同上。
- **Max Space ID**：表空间 ID，同上。
- **Mix ID Low(Unused)**：未使用。
- **Root of SYS_TABLES clust index**：本字段代表 SYS_TABLES 表聚簇索引的根页的页号。
- **Root of SYS_TABLE_IDS sec index**：本字段代表 SYS_TABLES 表为 ID 列建立的二级索引的根页的页号。
- **Root of SYS_INDEXES clust index**：本字段代表 SYS_INDEXES 表聚簇索引的根页的页号。
- **Root of SYS_FIELDS clust index**：本字段代表 SYS_FIELDS 表聚簇索引的根页的页号。

31. 用户是不能直接访问 InnoDB 的这些内部系统表的，除非你直接去解析系统表空间对应文件系统上的文件。不过设计 InnoDB 的大佬考虑到查看这些表的内容可能有助于大家分析问题，所以在系统数据库 information_schema 中提供了一些以 innodb_sys 开头的表：

```sql
mysql> USE information_schema;
Database changed

mysql> SHOW TABLES LIKE 'innodb_sys%';
+--------------------------------------------+
| Tables_in_information_schema (innodb_sys%) |
+--------------------------------------------+
| INNODB_SYS_DATAFILES                       |
| INNODB_SYS_VIRTUAL                         |
| INNODB_SYS_INDEXES                         |
| INNODB_SYS_TABLES                          |
| INNODB_SYS_FIELDS                          |
| INNODB_SYS_TABLESPACES                     |
| INNODB_SYS_FOREIGN_COLS                    |
| INNODB_SYS_COLUMNS                         |
| INNODB_SYS_FOREIGN                         |
| INNODB_SYS_TABLESTATS                      |
+--------------------------------------------+
10 rows in set (0.00 sec)

在 information_schema 数据库中的这些以 INNODB_SYS 开头的表并不是真正的内部系统表（内部系统表就是我们上面介绍的以 SYS 开头的那些表），而是在存储引擎启动时读取这些以 SYS 开头的系统表，然后填充到这些以 INNODB_SYS 开头的表中。以 INNODB_SYS 开头的表和以 SYS 开头的表中的字段并不完全一样。

  1. 总结图
InnoDB表空间总结图
InnoDB表空间总结图

第十章 单表的访问方法

  1. MySQL执行查询语句的方式称为 访问方法 或 访问方式。

  2. const访问方法：通过主键或 唯一二级索引列 与常数的 等值比较 来定位一条记录，速度如同坐火箭般快速。因此，这种通过主键或唯一二级索引列来定位记录的访问方法被定义为：const，意即常数级别，代价可以忽略不计。多列索引需每一列都进行等值比较才能使用 const 访问方法。若列允许 NULL 值，则无法使用 const 访问方法，因为 NULL 值数量不确定且不唯一。

  3. ref访问方法：搜索条件为二级索引列与常数等值比较，采用二级索引来执行查询的访问方法。当二级索引列与常数等值匹配的条数较少，回表操作次数少时，优先使用索引而非全表扫描。二级索引列允许 NULL 值时，可采用 ref 访问方法。对于包含多个列的二级索引，只需最左边的连续索引列都进行常数等值比较，即可使用 ref 访问方法。

    以下可以使用 ref 访问方法：

    SELECT * FROM single_table WHERE key_part1 = 'god like';
SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary';
SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary' AND key_part3 = 'penta kill';

    以下不可以使用：

    SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 > 'legendary';

  4. ref_or_null访问方法：不仅想要找出索引列与常数等值的记录，也想找出索引列为 NULL 值的记录。

    SELECT * FROM single_demo WHERE key1 = 'abc' OR key1 IS NULL;

  5. range访问方法：

    SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);

    上述查询语句，索引列 key2 进行范围匹配时的查询使用 range 访问方法。

  6. index访问方法：

    SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';

    上述查询语句中，查询的三个字段恰巧包含在一个多列索引中，且查询条件也被包含在一个多列索引中，此时可直接遍历对应的二级索引，比遍历聚簇索引更为快速。该方法称为 index 访问方法。

  7. all访问方法：全表扫描。

  8. 索引合并：一般查询只会使用一个二级索引，当一次查询使用多个二级索引时，该情况称为索引合并。

  9. Intersection合并：将查询条件中的两个二级索引查找到的 ID 值取交集，然后再回表从聚簇索引中查询。

  10. 可能会使用 Intersection 合并的情况：

    • 二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。
    • 主键列可以是范围匹配。

    之所以在二级索引列都是等值匹配的情况下才可能使用 Intersection 索引合并，是因为只有在这种情况下，根据二级索引查询出的结果集是按照主键值排序的。Intersection 索引合并会把从多个二级索引中查询出的主键值求交集，如果从各个二级索引中查询得到的结果集本身已经按照主键排好序，那么求交集的过程就会变得轻松。优化器只有在单独根据搜索条件从某个二级索引中获取的记录数太多，导致回表开销过大，而通过 Intersection 索引合并后需要回表的记录数大幅减少时，才会使用 Intersection 索引合并。

  11. Intersection 是交集的意思，适用于使用不同索引的搜索条件之间使用 AND 连接的情况；Union 是并集的意思，适用于使用不同索引的搜索条件之间使用 OR 连接的情况。

  12. 可能会使用 Union 合并的情况：

    • 二级索引是等值匹配的情况，若是联合索引则联合索引中所有字段都是等值匹配的情况。
    • 主键列可以是范围匹配。
    • 满足使用 Interaction 合并的情况：即在一条 SQL 查询语句中，使用部分搜索条件用于 Interaction 合并得到主键集合，然后再用剩下的搜索条件使用 Union 合并获取主键集合。

  13. Sort Union合并：Union 合并要求二级索引时等值匹配的情况，例如下列语句不是等值匹配就不能使用 Union 合并：

    SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z';

    Sort Union 是先根据 key1 < 'a' 从二级索引中获取主键记录进行排序，再根据 key3 > 'z' 从二级索引中获取主键记录进行排序，两个主键记录排序后再进行 Union 合并。

    提示

    之所以存在 Sort Union 合并，也仅是因为从二级索引中匹配到的记录较少，此时添加一个排序操作也不费时，才会使用 Sort Union 合并。

    之所以没有 Sort Interaction 合并，是因为 Interaction 合并原理是从二级索引中获取的记录过多，才取交集减少二级索引匹配记录数，若还要增加排序操作，则与设计初衷相违背。

  14. 可以通过联合索引的方式避免索引合并，例如：

    SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';

    其之所以触发索引合并，是因为 key1 和 key3 是两棵单独的 B+ 树，可以直接将其创建为联合索引，避免索引合并。

第十一章 连接的原理

  1. 对于内连接的两个表，驱动表中的记录在被驱动表中找不到匹配的记录，该记录不会加入到最后的结果集。我们上面提到的连接都是所谓的内连接。

  2. 对于外连接的两个表，驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。

  3. 内连接中的 WHERE 子句和 ON 子句是等价的。由于在内连接中 ON 子句和 WHERE 子句是等价的，所以内连接中不要求强制写明 ON 子句。

  4. 内连接和外连接的根本区别在于：当驱动表中的记录不符合 ON 子句中的连接条件时，该记录不会被加入到最后的结果集。

  5. 对于内连接来说，驱动表和被驱动表是可以互换的，并不会影响最后的查询结果。而左外连接和右外连接的驱动表和被驱动表则不能轻易互换。

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

  7. 有时候连接查询的查询列表和过滤条件中可能只涉及被驱动表的部分列，而这些列都是某个索引的一部分。在这种情况下，即使不能使用 eq_ref、ref、ref_or_null 或者 range 这些访问方法执行对被驱动表的查询，也可以使用索引扫描，即 index 的访问方法来查询被驱动表。因此，建议在真实工作中最好不要使用 * 作为查询列表，最好将实际需要的列作为查询列表。

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

    嵌套循环连接
    嵌套循环连接

  9. 基于块的嵌套循环连接是指在内存中开辟一个 join buffer（默认大小256KB），将驱动表中的记录加载到 join buffer 中，然后访问被驱动表时，被驱动表的每一条记录一次性与驱动表的多条记录进行匹配，显著减少被驱动表从磁盘上 I/O 的代价。

  10. 驱动表的记录并不是所有列都会被放到 join buffer 中，只有查询列表中的列和过滤条件中的列才会被放到 join buffer 中。因此，再次提醒，最好不要将 * 作为查询列表。

第十二章 MySQL基于成本的优化

  1. 在 MySQL 中，一条查询语句的成本分为 IO 成本 和 CPU 成本。IO 成本指 MyISAM 和 InnoDB 将数据从磁盘加载到内存所耗费的时间；CPU 成本则指读取及检测记录是否满足搜索条件、对结果集排序等操作所耗费的时间。

  2. 对于 InnoDB 存储引擎，页是数据交互的基本单位。MySQL 规定读取一个页面的成本默认为 1.0，而读取及检测一条记录是否符合搜索条件的成本默认是 0.2。

  3. 在一条单表查询语句真正执行之前，MySQL 的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案，这个成本最低的方案即为所谓的 执行计划。过程总结如下：

    • 根据搜索条件，找出所有可能使用的索引。
    • 计算全表扫描的代价。
    • 计算使用不同索引执行查询的代价。
    • 对比各个方式，选出成本最低的一种。

  4. 计算全表扫描的代价时，由于查询成本 = IO 成本 + CPU 成本，所以只需知道相关表聚簇索引的页面数以及记录数。可以直接通过表的 统计信息 查看：

    mysql> SHOW TABLE STATUS LIKE 'single_table'\G
*************************** 1. row ***************************
           Name: single_table
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 9693
   Avg_row_length: 163
      Data_length: 1589248
 Max_data_length: 0
   Index_length: 2752512
      Data_free: 4194304
   Auto_increment: 10001
      Create_time: 2018-12-10 13:37:23
      Update_time: 2018-12-10 13:38:03
       Check_time: NULL
        Collation: utf8_general_ci
         Checksum: NULL
      Create_options:
            Comment:
1 row in set (0.01 sec)

    其中只需关注 Rows 记录数 和 Data_length 数据字节数。

  5. MySQL 计算使用索引查询的成本时，优先计算唯一二级索引的成本，再计算普通索引的成本。

  6. 对于 二级索引 + 回表 的查询方式，MySQL 计算成本依赖于 范围区间数量 和 需要回表的记录数。假设现有一个范围区间，其中包含的满足条件的回表记录数为 95 条。

    不论某个范围区间的二级索引占用了多少页面，查询优化器粗略认为 读取索引的一个范围区间的 I/O 成本 和 读取一个页面的成本 是相同的。因此，读取一个范围区间的成本就是 1.0。

    计算一个范围区间需要回表的记录数，先找到该区间最左记录，再找到最右记录。如果两条记录相隔不远（MySQL 5.7 是 10 个页内），则直接统计出精确记录数；若相隔较远，则从最左记录向右读 10 个页面，计算这 10 个页面的平均记录数，再乘以相隔总页面数，该方法称为 index dive。

    假设某索引在某区间总共有 95 条记录，则读取 95 条记录的 IO 总成本为：

    95 * 0.2 + 0.01 = 19.1。

    其中 95 是需要读取的二级索引记录条数，0.2 是读取一条记录的成本常数，0.01 是微调。

    然后再根据查到的二级索引记录条数计算回表的成本，MySQL 认为一次回表的成本就是一次页 IO 的成本，即默认的 1.0。因此，95 条二级索引记录回表查询成本为：

    95 * 1.0 = 95.0

    其中 95 是预计的二级索引记录数，1.0 是一个页面的 I/O 成本常数。

    回表操作后得到完整用户记录，再检测其他搜索条件是否成立。通过范围区间获取到二级索引记录共 95 条，也就对应着聚簇索引中 95 条完整的用户记录，读取并检测这些完整的用户记录是否符合其余的搜索条件的 CPU 成本如下：

    95 * 0.2 = 19.0;

    其中 95 是待检测记录的条数，0.2 是检测一条记录是否符合给定搜索条件的成本常数。

    最后，总 IO 成本为 95.0 + 1.0 = 96.0（95 条记录对应的 95 次回表查询成本以及一次从磁盘中读取范围区间的成本）。总 CPU 成本为 95 * 0.2 + 0.01 + 95 * 0.2 = 38.1（从内存中统计二级索引记录条数 + 微调 0.01 + 检测 95 条记录是否符合条件）。

    如果触发了索引合并，那么计算成本的方式会更加复杂，不展开讨论。

  7. 基于统计数据的成本计算：
    为了防止使用 IN 查询索引列造成的单点区间过多（index_col in ('','',''.....)）从而导致 index dive 性能开销过大，MySQL 维护了一个系统变量 eq_range_index_dive_limit。当查询的单点区间数量少于该值时，使用 index dive 方法确定记录数量，超过该值时使用估算法，具体如下：

    MySQL 为表中的每一个索引维护一份统计数据，查看某个表中索引的统计数据可以使用 SHOW INDEX FROM 表名 的语法。

    mysql> SHOW INDEX FROM single_table;
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| single_table |          0 | PRIMARY      |            1 | id          | A         |       9693  |     NULL | NULL   |      | BTREE      |         |               |
| single_table |          0 | idx_key2     |            1 | key2        | A         |       9693  |     NULL | NULL   | YES  | BTREE      |         |               |
| single_table |          1 | idx_key1     |            1 | key1        | A         |        968 |     NULL | NULL   | YES  | BTREE      |         |               |
| single_table |          1 | idx_key3     |            1 | key3        | A         |        799 |     NULL | NULL   | YES  | BTREE      |         |               |
| single_table |          1 | idx_key_part |            1 | key_part1   | A         |        9673 |     NULL | NULL   | YES  | BTREE      |         |               |
| single_table |          1 | idx_key_part |            2 | key_part2   | A         |        9999 |     NULL | NULL   | YES  | BTREE      |         |               |
| single_table |          1 | idx_key_part |            3 | key_part3   | A         |       10000 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.01 sec)

    关注 Cardinality 属性，Cardinality 直译为基数，表示索引列中不重复值的个数。例如，对于一万行记录的表，某个索引列的 Cardinality 属性是 10000，意味着该列中没有重复的值；若 Cardinality 属性是 1，则该列的值全部重复。需要注意的是，对于 InnoDB 存储引擎，使用 SHOW INDEX 语句展示的某个索引列的 Cardinality 属性是一个估计值，并非精确值。

    当 IN 语句中的参数个数大于或等于系统变量 eq_range_index_dive_limit 的值时，就不会使用 index dive 的方式计算各个单点区间对应的索引记录条数，而是使用索引统计数据。这里所指的索引统计数据包括：

    • 使用 SHOW TABLE STATUS 展示出的 Rows 值，即一个表中有多少条记录。
    • 使用 SHOW INDEX 语句展示出的 Cardinality 属性。

    已知：

    一个值的重复次数 ≈ Rows ÷ Cardinality

    假设算出一个值的重复次数是 10 次，那么 IN 中有 20000 个参数时，其对应的记录数就直接估算为 10 * 20000 = 200000。

  8. MySQL 中的连接查询方式是 嵌套循环连接算法，其驱动表会被访问一次，而被驱动表会被访问多次。因此，其成本可以分为 单次查询驱动表的成本 与 多次查询被驱动表的成本（具体查询视被驱动表的记录而定）。

  9. 将对驱动表进行查询后得到的记录条数称为 驱动表的扇出（英文名：fanout）。

  10. 以下情况下，计算扇出值需要猜测：

    • 如果使用全表扫描的方式执行单表查询，则计算驱动表扇出时需猜测满足搜索条件的记录数量。
    • 如果使用索引执行单表扫描，则计算驱动表扇出时需猜测满足除使用到对应索引的搜索条件外的其他搜索条件的记录数量。

    设计 MySQL 的大佬将这个猜测过程称为 condition filtering。此过程��能会使用索引，也可能使用统计数据，甚至可能是设计 MySQL 的大佬单纯的猜测，整个评估过程较复杂，不展开说明。

    提示

    在 MySQL 5.7 之前的版本中，查询优化器在计算驱动表扇出时，如果使用全表扫描，就直接使用表中记录的数量作为扇出值；如果使用索引，则直接使用满足范围条件的索引记录条数。MySQL 5.7 中引入了 condition filtering 功能，需猜测剩余搜索条件能过滤多少条记录，其实本质上是为了让成本估算更精确。所谓的纯粹猜测其实是很不严谨的，设计 MySQL 的大佬们称之为 启发式规则（heuristic）。

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

  12. 外连接在某些情况下可以优化为内连接。

  13. 内连接的驱动表和被驱动表可以互换，因此查询优化器会分别考虑以不同的表作为驱动表和被驱动表的成本，选择成本最优的情况执行。

  14. 当有 n 个表连接时，连接方式有 n! 种，若查询优化器评估每种连接，性能开销会非常大。因此采用以下方法优化：

    • 提前结束某种顺序的成本评估：当已计算出某种连接方式的最小成本时，例如 A->B->C 成本为 10.0，后续计算其他连接方式发现 B->C 成本已大于 0 时，就不会继续计算。
    • 系统变量 optimizer_search_depth：通过该值限制分析表的数量。
    • 使用启发式规则，不考虑参与连接的表的数量。即使有上述两条规则的限制，但分析多个表不同连接顺序成本花费的时间仍然较长，因此设计 MySQL 的大佬们提出了一些启发式规则（根据以往经验制定的规则），凡是不满足这些规则的连接顺序将不分析，这样可以极大减少需要分析的连接顺序数量，但也可能错失最优的执行计划。他们提供了一个系统变量 optimizer_prune_level 来控制是否使用这些启发式规则。

  15. 除了之前提到的两个成本常数读取记录 1.0 和检测记录 0.2 外，MySQL 还支持其他常数，存储在系统数据库 mysql 中。

  16. 在 server 层进行连接管理、查询缓存、语法解析、查询优化等操作，在存储引擎层执行具体的数据存取操作。也就是说，一条语句在 server 层中执行的成本与其操作的表使用的存储引擎无关，因此关于这些操作对应的成本常数存储在了 server_cost 表中，而依赖于存储引擎的一些操作对应的成本常数则存储在 engine_cost 表中。

  17. server_cost 表结构如下：

mysql> SELECT * FROM mysql.server_cost;
+------------------------------+------------+---------------------+---------+
| cost_name                    | cost_value | last_update         | comment |
+------------------------------+------------+---------------------+---------+
| disk_temptable_create_cost   |       NULL | 2018-01-20 12:03:21 | NULL    |
| disk_temptable_row_cost      |       NULL | 2018-01-20 12:03:21 | NULL    |
| key_compare_cost             |       NULL | 2018-01-20 12:03:21 | NULL    |
| memory_temptable_create_cost |       NULL | 2018-01-20 12:03:21 | NULL    |
| memory_temptable_row_cost    |       NULL | 2018-01-20 12:03:21 | NULL    |
| row_evaluate_cost            |       NULL | 2018-01-20 12:03:21 | NULL    |
+------------------------------+------------+---------------------+---------+
6 rows in set (0.05 sec)

各个成本常数定义如下：

成本常数名称默认值描述
disk_temptable_create_cost40.0创建基于磁盘的临时表的成本。增大此值会让优化器尽量少创建基于磁盘的临时表。
disk_temptable_row_cost1.0向基于磁盘的临时表写入或读取一条记录的成本。增大此值会让优化器尽量少创建基于磁盘的临时表。
key_compare_cost0.1两条记录做比较操作的成本，多用于排序操作。增大此值会提升 filesort 的成本，让优化器可能更倾向于使用索引完成排序而不是 filesort。
memory_temptable_create_cost2.0创建基于内存的临时表的成本。增大此值会让优化器尽量少创建基于内存的临时表。
memory_temptable_row_cost0.2向基于内存的临时表写入或读取一条记录的成本。增大此值会让优化器尽量少创建基于内存的临时表。
row_evaluate_cost0.2检测一条记录是否符合搜索条件的成本。增大此值可能让优化器更倾向于使用索引而不是直接全表扫描。
  1. engine_cost 表结构如下：
mysql> SELECT * FROM mysql.engine_cost;
+------------------------------+------------+---------------------+---------+
| cost_name                    | cost_value | last_update         | comment |
+------------------------------+------------+---------------------+---------+
| io_block_read_cost           | 1.0       | 2018-01-20 12:03:21 | NULL    |
| memory_block_read_cost       | 1.0       | 2018-01-20 12:03:21 | NULL    |
+------------------------------+------------+---------------------+---------+

各个成本常数的定义如下：

成本常数名称默认值描述
io_block_read_cost1.0从磁盘上读取一个块对应的成本。请注意使用的是块，而不是页这个词。对于 InnoDB 存储引擎来说，一个页就是一个块，而对于 MyISAM 存储引擎来说，默认是以 4096 字节作为一个块。增大此值会加重 I/O 成本，可能让优化器更倾向于选择使用索引执行查询而不是执行全表扫描。
memory_block_read_cost1.0与上一个参数类似，只不过衡量的是从内存中读取一个块对应的成本。

第十三章 InnoDB的统计数据

  1. InnoDB提供两种统计数据存储方式：永久性统计数据存储和非永久性统计数据存储。

  2. 自MySQL 5.6.6起，默认的存储方式为永久性，即将数据存储在磁盘上。

  3. InnoDB以表为单位收集统计数据，某些表的统计数据可以存储在磁盘上，而其他表的统计数据则存储在内存中。可以在创建表时指定统计数据的存储方式。

  4. 当选择将某个表的统计数据存储到磁盘上时，实际上是存储到了两张表中：

    mysql> SHOW TABLES FROM mysql LIKE 'innodb%';
+---------------------------+
| Tables_in_mysql (innodb%) |
+---------------------------+
| innodb_index_stats        |
| innodb_table_stats        |
+---------------------------+
2 rows in set (0.01 sec)
    • innodb_table_stats存储了关于表的统计数据，每一条记录对应着一个表的统计数据。
    • innodb_index_stats存储了关于索引的统计数据，每一条记录对应着一个索引的一个统计项的统计数据。

  5. innodb_table_stats表的字段如下：

    字段名描述
    database_name数据库名
    table_name表名
    last_update本条记录最后更新时间
    n_rows表中记录的条数
    clustered_index_size表的聚簇索引占用的页面数量
    sum_of_other_index_sizes表的其他索引占用的页面数量

  6. n_rows是通过一定算法选取表中几个叶子节点页面计算每个页面的主键数量的平均值，再乘以叶子节点数量得出，因此精确度有限。

  7. innodb_index_stats的字段如下：

    字段名描述
    database_name数据库名
    table_name表名
    index_name索引名
    last_update本条记录最后更新时间
    stat_name统计项的名称
    stat_value对应的统计项的值
    sample_size为生成统计数据而采样的页面数量
    stat_description对应的统计项的描述

  8. 统计数据的更新默认是自动开启的，用户也可以通过ANALYZE TABLE等语句手动更新。更新的过程是异步的。

  9. innodb_stats_method用于在统计某列不重复值的数量时对待NULL值的方式，分为以下三种：

    • nulls_equal：认为所有NULL值都是相等的。这个值也是innodb_stats_method的默认值。
    • nulls_unequal：认为所有NULL值都是不相等的。
    • nulls_ignored：直接将NULL值忽略掉。

第十四章 MySQL基于规则的优化

  1. 查询优化器是不会尝试对这些表达式进行化简的。

  2. 索引列和常数使用某些运算符才能使用到索引，所以尽量让索引列在搜索条件中单独出现。

  3. 如果查询语句中没有出现诸如SUM、MAX等聚合函数以及GROUP BY子句，优化器就把HAVING子句和WHERE子句合并起来。

  4. 常量表检测：当表中只有一条或没有数据，或者查询使用主键等值匹配或唯一二级索引列等值匹配的时候，涉及的表就称为常量表。例如：

    SELECT * FROM table1 INNER JOIN table2
    ON table1.column1 = table2.column2 
    WHERE table1.primary_key = 1;

    上述查询中，table1使用了主键等值匹配查询，所以在该查询中，table1相当于常量表。因此，优化器会先执行对于table1的查询，查询到结果后，将查到的值（常量）替换到上述查询中。以下为替换结果：

    SELECT table1表记录的各个字段的常量值, table2.* FROM table1 INNER JOIN table2 
    ON table1表column1列的常量值 = table2.column2;

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

  6. 对于包含不相关的标量子查询或者行子查询的查询语句来说，MySQL会分别独立地执行外层查询和子查询。

  7. 对于相关的标量子查询或者行子查询来说，比如下面这个查询：

    SELECT * FROM s1 WHERE 
    key1 = (SELECT common_field FROM s2 WHERE s1.key3 = s2.key3 LIMIT 1);

    先从外层查询中取一条记录，找出该记录中子查询涉及到的值，用子查询查看该记录是否符合条件，不符合条件就丢弃，每条记录都检查一遍。

  8. 在进行不相关子查询时，将子查询的数据保存到一个临时表中的行为称为物化，该表称为物化表，其具有哈希索引。当子查询的结果集达到一定大小时，临时表的数据就会转而存储到硬盘上，索引也从哈希索引变为B+树索引。

  9. 将不相关子查询转为物化表后，优化器还会和外层查询的表进行连接，通过基于成本的优化规则来决定连接顺序。

  10. 有不相关子查询的查询中的两个表还可以进行半连接（semi-join）：对于s1表的某条记录来说，我们只关心在s2表中是否存在与之匹配的记录，而不关心具体有多少条记录与之匹配，最终的结果集中只保留s1表的记录。

  11. 如果IN子查询符合转换为semi-join的条件，查询优化器会优先把该子查询转为semi-join，然后再考虑下面5种执行半连接的策略中哪种成本最低：

    • Table pullout
    • DuplicateWeedout
    • LooseScan
    • Materialization
    • FirstMatch
      选择成本最低的进行子查询。

  12. 如果IN子查询不符合转换为semi-join的条件，那么查询优化器会从下面两种策略中找出一种成本更低的方式执行子查询：

    • 先将子查询物化之后再执行查询
    • 执行IN to EXISTS转换

  13. 当子查询放在FROM之后，会产生派生表。对于派生表的查询优化有以下两种方式：

    • 物化
    • 通过连接消除派生表
      但是派生表的语句中有聚合函数时就不能合并。

第十五章 Explain详解（上）

浙ICP备2025150833
Copyright © 2025 Jayczee