mysql是怎样运行的-从根儿上理解mysql学习笔记(三)

说明

本文是《MySQL是怎样运行的-从根儿上理解MySQL》的学习笔记，文中的图全部来自于这本书，强烈建议买一本看看，对MySQL理解会特别深入，非常感谢作者"小孩子4919"。

第十一章 连接

连接简介

连接的本质

为了叙述方便，这里新建两个表并向其插入记录

CREATE TABLE t1 (m1 int, n1 char(1));
CREATE TABLE t2 (m2 int, n2 char(1));
INSERT INTO t1 VALUES(1, 'a'), (2, 'b'), (3, 'c');
INSERT INTO t2 VALUES(2, 'b'), (3, 'c'), (4, 'd');
SELECT * FROM t1, t2; # Q1

从本质上来说，连接就是把各个表中的记录都取出来进行依次匹配，并把匹配后的组合发送给客户端。t1，t2两个表连接的语句是Q1，连接的过程如图11-1所示。

图11-1 t1和t2两个表的连接过程

这个过程看起来就像是把t1表中的记录和t2表中的记录连起来组成一个更大的记录，所以这个查询过程称为连接查询。如果连接查询的结果集中包含连接表中所有的记录，那么这个结果集称为笛卡尔积。

连接过程简介

这里看一条携带过滤条件的连接查询大致过程

SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd'; #Q2

这个查询中指明了3个过滤条件

• t1.m1 > 1;
• t1.m1 = t2.m2;
• t2.n2 < ‘d’;

连接查询的大致过程如下：

• 步骤一：首先确定第一个需要查询的表，这个表称为驱动表；
  如何在单表中执行查询语句在第十章已经介绍过了，只需要选取代价最小的那种访问方式去执行查询就好了。这里假设使用t1为驱动表，查找条件为t1.m1 > 1，查找方式为all，结果是有两条，如图11-2所示。
  

图11-2 查询过程

• 步骤二：对于步骤一中每获取到一条记录，都需要到t2表中查找匹配的记录；
  步骤一从驱动表中获取了2条记录，也就意味着需要查询2次t2表，此时t1.m1 = t2.m2就派上用场了，查询过程如图11-3所示

图11-3 整个连接查询执行过程 这里需要强调一下，并不是将所有满足条件的驱动表记录先查询出来放到一个地方，然后再去被驱动表中查询，而是每获得一条驱动表记录，就立即到被驱动表中寻找匹配的记录。

内连接和外连接

• 内连接
  对于内连接的两个表，若驱动表中的记录在被驱动表中找不到匹配的记录，则该记录不会加入到最后的结果集。
• 外连接
  对于外连接的两个表，即使驱动表中的记录在被驱动表中找不到匹配的记录，也仍然需要加入到结果集中。
  • 左(外)连接：选取左侧表为驱动表
  • 右(外)连接：选取右侧表为驱动表

连接的原理

嵌套循环连接

对于对个表的连接过程用伪代码表示是：

for each row in t1 satisfying conditions about t1:
	for each row in t2 satisfying conditions about t2:
		for each row in t3 satisfying conditions about t3:
			send to client

这个过程就像是一个嵌套的循环，所以这种连接执行方式称为嵌套循环连接。

使用索引加快连接速度

嵌套循环连接是最简单最笨拙的连接方式如果访问被驱动表的方式都是全表扫描，那么性能将急剧下降。对于Q2查询来说，可以在t2表的m2和n2上建立索引。

• 在m2上建立索引。针对m2列的条件是等值查找，比如t2.m2=2、t2.m3=3，所以可以用到ref访问方法，之后再回表判断t2.n2<'d'条件是否成立；
• 在n2上建立索引，涉及的条件是t2.n2<'d'，可用到range访问方法，之后再回表判断t2.m2=2、t2.m3=3是否成立。
  假设m2和n2列上都存在索引，那么就需要从这两个里面挑一个代价更低的索引来查询t2表。

第十二章 基于成本的优化

查询成本

上文提到MySQL在执行一个查询时可以有不同的执行方案，它会选择其中成本最低的那种方案真正执行查询，MySQL执行成本是由两个方面组成的。

• I/O成本：当查询表中记录时，需要先把数据或者索引加载到内存中，然后进行操作，从磁盘到内存加载过程损耗的时间称为I/O成本。
• CPU成本：读取记录以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称为CPU成本。
  页是磁盘和内存进行交互的基本单位，InnoDB规定：读取一个页花费的成本默认是1.0；读取一条记录以及检测一条记录是否符合搜索条件的成本默认是0.2。

单表查询成本

为了介绍单表查询成本，这里还是新建一个表

CREATE TABLE single_table(
id INT NOT NULL AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
KEY idx_key1(key1),
UNIQUE KEY uk_key2(key2),
KEY idx_key3(key3),
KEY idx_key_part(key_part1, key_part2, key_part3)
)Engine=InnoDB CHARSET=utf8;

假设向表中插入10000条记录，除id列其余的列都插入随机值。

基于成本优化的步骤

图12-2 B+树索引结构 下面以一个具体的查询实例进行分析

SELECT * FROM single_table WHERE
key1 IN ('a', 'b', 'c') AND 
key2 > 10 AND key2 < 1000 AND
key3 > key2 AND
key_part1 LIKE '%hello%' AND
common_field = '123';

• 根据搜索条件，找出所有可能使用的索引
  分析查询语句及建立的索引，可以使用的索引是：

  • key1 IN (‘a’, ‘b’, ‘c’)：可以使用二级索引idx_key1
  • key2 > 10 AND key2 < 1000：可以使用二级索引uk_key2

  其他搜索条件均无法使用索引加速查找过程。

• 计算全表扫描的代价
  成本=I/O成本+CPU成本，计算全表扫描的成本需要知道两个信息

  • 聚簇索引占用的页面数
  • 该表中的记录数

  MySQL为每个表都维护了一系列统计信息，这些统计信息将在第十三章介绍，使用SHOW TABLE STATUS语句可以查看表的统计信息，这里只列出比较重要的几个字段，查询的结果如下

  SHOW TABLE STATUS LIKE 'single_table'\G
  *****************************1. row *********************************
  Name: single_table
  Rows: 9693
  Data_length: 1589248
  

  • Rows
    表示表中记录数，对于InnoDB引擎来说，这是个估计值，上文说到表中一共插入了10000条数据，这里估计有9693。
  • Data_length
    表示表占用的存储空间字节数，对于InnoDB来说 $$Data\_length=聚簇索引的页面数量\ast 每个页面的大小$$由于页面大小默认为16KB，因此聚簇索引页面的数量为$1589248/16/1024=97$。

  现在可以计算全表扫描的代价了。

  • I/O成本
    $$97\ast 1.0+1.1=98.1$$其中97指聚簇索引占用的页面数，1.0指加载一个页面的成本常数，后面的1.1是一个微调值。
  • CPU成本
    $$9693\ast 0.2+1.0=1939.6$$9693指统计数据中表的记录数，0.2指的是访问一条记录所需要的成本常数，后面的1.0是一个微调值。
  • 总成本$$98.1+1939.6=2037.7$$

  综上所述，全表扫描的代价是2037.7

• 计算使用不同索引执行查询的代价

  • 使用uk_key2执行查询的成本分析

    • 扫描区间数量
      查询优化器粗暴地认为读取索引的一个扫描区间的I/O成本与读取一个页面的I/O成本是相同的。本例中uk_key2扫描区间只有一个$(10,1000)$，所以访问这个扫描区间二级索引的I/O成本是$1\ast 1.0=1.0$

    • 需要回表的记录数
      先找到左边界记录(也就是key2=10)所在的页b，再找到右边界记录(也就是key2=1000)所在的页c，现在的问题就是页b到页c之间一共有多少条记录，这个问题就转化为求它们父页面a对应目录项记录之间隔着几条记录。如图12-2，如果页b和页c之间的页面太多了，以至于页b父节点和页c父节点不是同一个节点，这种情况就继续递归，使用这种方法可以大致计算出需要回表的记录数。回到上述的实际例子，根据上述算法测得uk_key2在区间(10, 1000)中大约有95条记录。读取这95条二级索引记录需要付出的CPU成本就是$95\ast 0.2+0.01=19.01$。其中95是需要读取的二级索引记录条数，0.2是读取一条记录的成本常数，0.01是微调值。

    • 执行回表操作
      MySQL认为每次回表操作都相当于访问一个页面，也就是说二级索引扫描区间有多少条记录，就需要进行多少次回表操作，也就是需要进行多少次页面的I/O操作。前面在使用uk_key2二级索引执行查询时，预计有95条二级索引记录需要回表，因此回表的代价是$95\ast 1.0=95.0$

    • 回表之后得到完整用户记录，再检测其他搜索条件是否成立
      这里的代价就是判断条件是否满足，CPU成本为$95\ast 0.2=19.0$，所以使用uk_key2执行查询成本为

      • I/O成本：$1.0+95\ast 1.0=96.0$(扫描区间数量+预估二级索引记录条数)
      • CPU成本：$95\ast 0.2+0.01+95\ast 0.2=38.01$(读取二级索引记录的成本+读取并检测回表之后进行判断的成本)

      综上所述，使用uk_key2查询的总成本是$96.0+38.01=134.01$

  • 使用idx_key1执行查询的代价
    使用idx_key1索引执行查询的成本和uk_key2分析完全一样，这里不详述其过程，只列举其结果。

    • 扫描区间数量
      这里有三个单点扫描区间，分别是(‘a’, ‘a’)，(‘b’, ‘b’)， (‘c’, ‘c’)。I/O成本是$3\ast 1.0=3$
    • 需要回表的记录数
      每个单点扫描区间需要回表的记录数和上文相同，结果为$35+44+39=118$。读取记录的CPU成本是$118\ast 0.2=23.6$。
      综上所述，一共需要花费的CPU成本是$3.0+118\ast 1.0+118\ast 0.2+0.01+118\ast 0.2=168.21$

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

  • 全表扫描成本是2037.7
  • 使用uk_key2成本是134.01
  • 使用idx_key1索引的成本是168.21

  所以选择uk_key2来执行查询。

第十三章 InnoDB统计数据是如何生成的

每个表都有名为innodb_index_stats和innodb_table_stats的2个统计表，这两个统计表都位于mysql系统数据库下面，作用分别是存储索引的统计数据和存储表的统计数据。

innodb_table_stats

innodb_table_stats表的各个列含义如下表，表的主键是(database_name, table_name)。

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

innodb_index_stats

innodb_index_stats表的各个列含义如下，表的主键是(database_name, table_name, index_name, stat_name)，其中stat_name是指统计项名称。

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

第十五章 EXPLAIN详解

MySQL插叙优化器基于成本和规则对一条查询语句进行优化后，会生成一个执行计划，这个执行计划展示了接下来执行查询的具体方式，MySQL提供了EXPLAIN语句可以查看某个查询语句的具体执行计划。
为了方便叙述，这里还是给出single_table表的结构信息

CREATE TABLE single_table(
id INT NOT NULL AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
KEY idx_key1(key1),
UNIQUE KEY uk_key2(key2),
KEY idx_key3(key3),
KEY idx_key_part(key_part1, key_part2, key_part3)
)Engine=InnoDB CHARSET=utf8;

假设仍然有两个和single_table表的构造一模一样的表:s1和s2表，而且这两个表里各有10000条记录，除id外都插入随机值。
下面看一个具体的查询执行计划

EXPLAIN SELECT * FROM s1 INNER JOIN s1;

表15-101 EXPLAIN语句各个查询字段展示

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	s1	NULL	ALL	NULL	NULL	NULL	NULL	9688	100.00	NULL
1	SIMPLE	s2	NULL	ALL	NULL	NULL	NULL	NULL	9954	100.00	Using join buffer(Block Nested Loop)

各个列的作用如下表15-102

表15-102 EXPLAIN语句各个查询字段含义

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

执行计划输出中各列详解

table

MySQL规定，EXPLAIN语句输出的每条记录都对应某个单表的访问方法，该条记录的table列代表该表的表名，表15-101给出了内连接查询的结果，一共需要查询两张表，分别是s1和s2。

id

在连接查询执行计划中，每个表都会对应一条记录，这些记录的id列的知识相同的，出现在前面的表是驱动表，出现在后面的表示被驱动表，表15-101显示驱动表是s1，被驱动表是s2。
对于包含子查询的查询语句来说，可能涉及多个SELECT关键字，此时每个SELECT关键字都对应一个唯一的id值。

EXPLAIN SELECT * FROM s1 WHERE key1 IN(SELECT key1 FROM s2) OR key3 = 'a';

表15-103 连接子查询

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	s1	NULL	ALL	idx_key3	NULL	NULL	NULL	9688	100.00	Using where
2	SUBQUERY	s2	NULL	index	idx_key1	idx_key1	303	NULL	9954	100.00	Using index

从输出结果可以看到，s1表在外层查询中，外层查询有一个独立的SELECT关键字，所以第一条记录的id值就是1；s2表在子查询中，子查询中有一个独立的SELECT关键字，所以第二记录的id值就是2。

select_type

MySQL为每一个SELECT关键字代表的小查询都定义了一个名为select_type的属性，它的取值有

• SIMPLE
  查询语句中不包含UNION或子查询的查询都算作SIMPLE类型
• PRIMARY
  对于包含UNION、UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，其中最左边那个查询的select_type值就是PRIMARY
• UNION
  对于包含UNION、UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，其中最左边那个查询的select_type值就是PRIMARY，其余小查询的select_type值就是UNION
• UNION RESULT
  MySQL选择使用临时表来完成UNION查询去重工作，针对该临时表的查询的select_type就是UNION RESULT
• SUBQUERY
  如果包含子查询的查询语句不能够转化为对应的半连接形式，并且该子查询不是相关子查询，而且查询优化器决定采用该子查询无话的方案来执行该子查询是，该子查询的第一个SELECT关键字代表的那个查询select_type就是SUBQUERY

type

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

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

• const
  根据主键或者唯一二级索引列与常数进行等值匹配的访问结果

• eq_ref
  执行连接查询是，如果被驱动表是通过主键或者不允许存储NULL值的唯一二级索引列等值匹配的方式进行访问，则对该被驱动表的访问方法就是eq_ref

  EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
  

  该执行语句中，s1是驱动表，s2是被驱动表，s2的访问方法就是eq_ref，表明在访问s2表时，可以通过主键的等值匹配来访问。

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

• ref_or_null
  当通过普通二级索引列与常量或NULL进行等值匹配的方式来查询某个表时，对该表的访问方法就是ref_or_null

• index_merge
  一般情况下只会为单个索引生成扫描区间，但当使用索引合并的方式时，访问方式就是index_merge

• range
  如果使用索引获取某些单点扫描区间记录，就可能用到range访问方法。

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

• ALL
  全表扫描，访问方式就是ALL

possible_keys和key

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

key_len

key_len的值由以下三部分组成

1. 该列的实际数据最多占用的存储空间长度。比如对于INT类型的列来说，该列实际数据最多占用存储空间长度就是4；对于变长类型VARCHAR来说，实际数据最多占用的存储空间长度就是utf8字符集中表示一个字符最多占用的字节数乘以该类型 最多可以存储的字符数的积，也就是3*100=300字节。
2. 如果该列可以存储NULL的值，则key_len在1基础上+1
3. 对于使用变长类型的列来说，都会有2字节的空间来存储该变列的实际数据占用的存储空间长度，key_len在1的基础上+2

所以表15-103中id=2查询中key_len=303

EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 > 'b';

表15-104 使用多个索引key_len的值

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	s1	NULL	range	idx_key_part	idx_key_part	606	NULL	3	100.00	Using index conditon

表15-104展示了使用多个索引key_len的值的情况，key_len=606，说明该查询语句通过涉及key_part1和key_part2这两个列的 搜索条件来充当形成扫描区间的边界条件。

ref

当访问方法是const、eq_ref、ref、ref_or_null中其中的一个时，ref列展示的就是与索引列进行 等值匹配的项。

rows

执行计划的rows列表示该表的估计行数。

Extra

Extra列是用来说明一些额外的信息的，通过它可以更准确地理解MySQL到底如何执行给定的查询语句，它可能显示好几十个额外的信息，比较重要的有

• No tables used
  当查询语句中没有FROM子句时会提示该额外信息。
• Impossible WHERE
  查询语句的WHERE子句永远为FALSE时提示该额外信息
• No matching min/max row
  查询列表中有MIN或者MAX聚集函数，但是并没有记录符合WHERE子句的搜索条件
• Using index
  使用覆盖索引执行查询时，Extra列会提示该额外信息
• Using filesort
  当对结果集中的记录进行排序时，会显示该信息。

第十七章 InnoDB的Buffer Pool

InnoDB在处理客户端请求是，如果需要访问某个页的数据，就会把完整的页中的数据加载到内存中，而且在读写访问之后并不着急把该也对应的内存空间释放掉，而是将其缓存起来。InnoDB为了缓存磁盘中的页，在MySQL服务器启动时就向操作系统申请了一片连续的内存，命名为Buffer Pool(缓冲池)。
Buffer Pool对应的一片连续的内存被划分为若干个页面，页面大小与InnoDB表空间使用 的页面大小一致，为了与磁盘中的页面区分开来，这里把Buffer Pool中的页称为缓冲页。为了更好地管理这些缓冲页，InnoDB为每一个缓冲页都创建了控制信息，这些控制信息包括页所属的表空间编号、页号、缓冲页在Buffer Pool中的地址、链表节点信息等。Buffer Pool的结构如图17-1所示。

图17-1 Buffer Pool对应的内存空间

free链表的管理

从磁盘读取一个页到Buffer Pool中时，该放到哪个缓冲页位置呢？怎么区分哪些缓冲页是空闲的，哪些缓冲页已经被使用了呢？InnoDB将所有空闲的缓冲页连接起来构成一个链表，称为free链表，初始化时，所有的缓冲页都在free链表中 ，如图17-2

图17-2 free链表效果图

表空间+页号可以定位一个页，所以，free链表再加上hashmap就可以方便判断一个页是否有对应的缓冲页。

flush链表的管理

前文说到，如果修改了Buffer Pool的某个缓冲页数据，它就与磁盘上的页数据不一致了，这样的缓冲页成为脏页，什么时候将脏页的数据刷新到磁盘对应的页呢？可以使用参数配置。
这里就有问题，如果不立即将修改刷新到磁盘，那之后再刷新是怎么知道Buffer Pool中哪些页是脏页呢？InnoDB将所有修改的页连接起来，构成flush链表，如图17-3所示。

图17-3 Buffer Pool中脏页数量为n时对应的flush链表

LRU链表的管理

简单的LRU链表淘汰会有问题，它存在以下较为尴尬的局面

1. InnoDB提供了一个比较贴心的服务——预读，就是InnoDB认为执行当前的请求时，可能会在后面读取某些页面，于是就预先把这部分页面加载到Buffer Pool中。
2. 进行全表扫描操作时，意味着需要访问聚簇索引所有的叶子节点对应的页，这是非常耗时的操作，而且这些页会把可能需要经常使用的页挤出链表。

由于存在这两种情况，InnoDB把LRU链表按一定比例分成两截：

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

如图17-4所示

图17-4 LRU链表示意图

有了被划分成young和old区域的LRU链表之后，InnoDB可以针对前文提到的两种情况进行优化了。

• 针对预读的页面可能不进行后续访问的优化
  InnoDB规定当磁盘某个页面在初次加载到Buffer Pool中某个缓冲页时，该页会被放到old区域的头部，这样，预读到Buffer Pool却不进行后续访问的页面会被逐渐从old区域移除；
• 针对全表扫描时，短时间内访问大量使用频率非常低的页面的优化
  在进行全表扫描时，每访问一条记录，就相当于访问了一次该记录所在的页面，如果单单使用上面的优化方法，那么该页面还是会被移到young区。解决的思路是，全表扫描执行频率非常低，而且在执行过程中，即使某个页面有很多条记录，尽管没读取一条记录都算访问一次该页面，但是读取一个页面所有记录花费的时间是非常少的。InnoDB规定，在对某个处于old区域的缓冲页进行第一次访问时，在对应控制块记录这个访问时间，如果后续的访问时间与第一次访问时间在某个时间间隔innodb_old_blocks_time内(InnoDB默认是1s)，就不将该页面从old移动到young区。