<<Mysql是怎样运行的>>小记-2

第十章:单表访问方法#

Mysql Server中又有一个叫优化器的模块,在Mysql服务端对一条查询语句进行语法解析之后,会将其再交给优化器来进行优化,在优化后会获得一个执行计划.
这个执行计划中表明了应该使用哪些索引查询,还有表之间的连接顺序等等.
最后会按照该执行计划中的步骤调用存储引擎提供的接口来真正的进行查询.

访问方法的概念#

我们写的SQL查询语句只是告诉了MYSQL要查询出的数据要符合哪些规则,并不是限制了Mysql查询数据的方式.
同一个SQL查询语句,可能有多种获取到目标数据的方法,我们称这些方法为访问方法或是访问类型.虽然结果一样,但是不同的访问方法执行起来效率可能差距甚大.

接下来就来介绍一下各种访问方法

const#

const访问方法指直接通过聚簇索引或是唯一二级索引直接定位到一条数据,意为常数级别,代价可以忽略不计.
不过该方法需要聚簇索引的主键值和唯一二级索引的主键值与某想查询的常数相等才能有效.如果某个唯一索引主键是多个列,需要该多个列都有指定的值才能有效.
如果是为NULL的情况下则const访问方法无效,因为可能查询到多条数据.

ref#

当使用某个二级索引列与常数进行等值比较,ref访问方法就会有效.
select * from t where index='111'
搜索条件为二级索引列与常数进行等值比较,并且形成的扫描区间为单点区间,就比如[111,111].这种访问方法称为ref.
我们要注意:

  • 使用二级索引来进行查询,并不是查询完所有的数据再进行统一回表,而是查询到一条就进行一次回表.
  • 如果采用比较的列可以为NULL, 并且我们即使使用唯一索引来进行查询,使用 is NULL 作为条件,最多也只能使用ref访问方法,因为可能查询到多条数据
  • 对于联合索引来说,只要用于比较的是从左到右连续的索引列与常数比较,就可以使用ref访问方法.

ref_or_null#

当使用某二级索引查询索引列与某常数相同的同时,还查询该索引列值为NULL的时候,使用的就是ref_or_null访问方法
select * from t where index='111' and index is NULL
扫描区间为[NULL,NULL]和[111,111]

range#

之前的访问方法都是索引列与某常数值等值时有效的.
而range是:当使用某个索引执行查询时,形成的扫描区间为若干个单点扫描区或是范围扫描区,则为为range访问方法.
select * from t where index='111' OR (index>=30 AND index <=50)
注意:扫描区间只有一个单点扫描区或是扫描区为(-∞,+∞)时都不为range访问方法.

index#

当查询的列都包含在某一索引中充当索引列,并且条件也在该索引列中,就会使用index访问方法
select part1,part2,part3 from t where part2='abc'
就比如这个查询语句,我们假设这个表有一个联合索引(part1,part2,part3).
虽然条件中的列并不是连续的,无法利用索引快速查询.
这个时候由于查询的列都在索引列中,所以不需要进行回表操作,而查询条件又在该索引列中.
所以只需要遍历该联合索引的全部记录,而后对条件进行判断即可.我们称这种访问方法为index

因为联合索引列中的记录大小比聚簇索引的小,所以这种方式成本会小很多.

注意:如果全表扫描的语句使用了ORDER BY 主键的语句,该语句也会被认为是index访问方法.

all#

直接通过聚簇索引对全表的记录进行扫描,这种我们成为all访问方法.

注意事项#

重温二级索引和回表#

我们前面有说过,一般用索引来减少扫描区间,只会使用一个索引.
比如:select * from t where key1=100 and key2>50
这条语句有列中有俩个索引,各自形成的扫描区间是:[100,100]和(50,+∞)
我们一般认为ref访问方法比range访问方法成本更低,不过也有额外情况,就比如ref访问方法与那个常量等值的记录数非常多.
所以优化器会访问表中的少量数据,来计算这俩个扫描区间包含几条记录,再通过一定算法来计算使用这俩个扫描区间执行查询时的成本,最后选择成本最小的那个扫描区间所对应的索引来查询.
如果这个查询语句我们使用key1所对应的索引来查询:

  • 先去key1索引树中寻找到第一条key1=100的记录
  • 对这条记录进行回表,看他是否满足key2>50的条件,如果满足就将其发送给客户端,否则就跳过
  • 然后根据这条记录的单向链表向后寻找下一条二级索引记录,直到不满足key1=100的条件.

MRR(多范围读取):我们现在说的是每读取到一条二级索引记录都会对其进行回表操作,但在二级索引中的主键值是无序的,每次都要回到聚簇索引进行查询,这造成了许多随机IO,设计者提出了一个MRR的优化方式,读取二级索引记录时,并不进行立即回表,而是先读取一部分,而后对这些二级索引记录的主键进行排序,再进行回表.这样就节省了IO资源.不过这个优化的启用条件比较苛刻.

索引合并#

一般情况下Mysql只会为单个索引生成扫描区间.
但是存在一些特殊情况,Mysql可以为多个索引生成扫描区间.
我们称这种使用多个索引来完成一次查询的执行方法为:index merge 索引合并.

Intersection 索引合并#

当一个查询语句条件中的多个索引所形成的扫描区间都是按照主键值来排序的,并且条件之间有and关系,就可以使用Intersection索引合并查询.
即对俩个索引查询出的符合各自扫描区间的结果交集返回给客户端.
比如:
select * from single_table where key1='a' and key2='b';
这条查询语句的俩个条件所对应的索引形成的扫描区间为[a,a]和[b,b]
并且在这些扫描区间的内的记录都是按照主键值来排序.

  • 如果这个表的记录只使用key1或者key2索引来进行查询会怎么查询呢? 会在对应的二级索引中对对应的扫描区间进行扫描,而后对符合该索引列对应条件的记录进行回表,再对聚簇索引中的完整记录判断其他条件.

我们假设这个表的记录有:

id key1 key2
1 a a
2 a b
3 c b
4 a b

如果在这条查询语句中出现了索引合并,顺序会是:

  • 首先根据key1索引寻找key1='a'的第一条记录,寻找到了id为1的记录.
  • 随后去key2索引去寻找第一条符合key2='b'的第一条记录,寻找到了id为2的记录.
  • 将这俩条记录进行比较,发现key1的记录比key2的记录主键值要小,所以id值为1的记录不符合条件,丢弃.
  • 再根据key1去寻找下一条记录,寻找到了id值为2的,这个时候再与key2寻找到的id值为2的记录比较主键值.发现主键值一样,故这条记录符合条件,进行回表,将该条记录返回给客户端
  • 再根据key1寻找下一条记录,寻找到了id为4的记录. 再使用key2寻找记录,寻找到了id为3的记录,进行比较,发现key1的记录大于key2的,说明key2的记录不符合条件,丢弃.
  • 再次根据key2寻找下一条记录,这次寻找到了id值为4的,和key1这次寻找的进行比较,相同,回表并且返回给客户端.
  • 二级索引的叶子结点遍历完成.

这次我们根据索引合并获取到了id值为2和4的记录.
从上面的步骤我们可以知道,这种合并方式依赖与在扫描区间内的记录都要按照主键排序,否则不能使用Intersection 索引合并.
比如我们这条SQL:select * from table where key1>'a' and key2='b';
这种情况也不能使用Intersection 索引合并,因为a的扫描区间是[a,+∞),这个区间内的记录并不完全按照主键排序,key1值为a的记录的主键值可能会比key1值为b的主键值要大.在依次去取二级索引中的记录时,主键并不连续,不能进行筛选.

聚簇索引比较特殊,即使聚簇索引形成的扫描区间不是单点扫描区间,但是聚簇索引本身就是根据主键值来排序的,比如select * from table where id>'a' and key1='b';这种情况也是能利用到Intersection 索引合并的
某个索引列是联合索引的第一列也不能利用Intersection 索引合并,因为联合索引在前一列相同的情况下,是根据后一个索引列的值来排序的,并不按照主键排序.

Union 索引合并#

当一个查询语句条件中的多个索引所形成的扫描区间都是按照主键值来排序的,并且条件之间有or关系,就可以使用Union 索引合并查询.
即对俩个索引查询出的符合各自扫描区间的结果并集并且去重返回给客户端.
比如select * from t where key1='a' or key2='b'
这个查询语句我们能走key1或是key2的索引吗?
我们可以发现这个条件使用单个索引形成的扫描区间是(-∞,+∞),并且每条记录都要进行回表,查看是否满足另一个条件.这样还不如去聚簇索引去全表扫描!
根据这种情况,我们就可以使用俩个索引来进行查询.
在key1索引中扫描[a,a]中的记录,同时在key2索引中扫描[b,b]中的记录.
然后根据二级索引中的id值进行去重,再根据去重后的数据进行回表操作.

为什么都要求扫描区间中的记录都按照主键排序呢?
主要有俩点:
1 俩个有序列表进行去重和交集操作比无序列表更加简单
2 最后得到的结果如果是按照主键排序的有序记录,去聚簇索引中执行回表操作就会是连续IO,能够减少随机IO操作,这样提高了效率.

在一个查询语句中可以同时出现Intersection索引合并和Union索引合并:
比如
select * from t where (key_part1='a' and key_part2='b' and key_part3='c' ) or (key1='a' And key3='b');
这条语句我们可以先根据key1='a' and key3='b' 符合Intersection索引合并的条件,我们根据这俩个索引查询出的记录并取交集,获取(key1='a' And key3='b')这俩个条件的结果集
而后(key_part1='a' and key_part2='b' and key_part3='c' )是一个联合索引,形成的扫描区间内记录按照主键排序,和(key1='a' And key3='b')条件形成的or关系,再使用联合索引的查询结果和(key1='a' And key3='b')的结果集进行并集并去重,回表返回给客户端.

Sort-Union 索引合并#

Union索引合并的条件比较苛刻,所以mysql还有一种sort-union索引合并.
这种方式会将二级索引查询出符合条件的记录进行排序,再将多个二级索引的排序后的结果进行取并集并去重,回表,再返回给客户端.
select * from t where key1<'a' or key2>'z';
这条语句,Sort-Union会先根据key1<'a'条件从key1的二级索引树取出对应符合条件的记录,然后再将这些记录根据主键排序.
然后再从key2>'z'这个条件从key2索引中寻找到符合条件的记录,进行排序.
随后就和union索引合并操作一样了.
比单纯的union索引合并多了一步排序的操作.

为什么只有Sort-Union没有Sort-Intersection?
我们可以设想一下,为什么需要有Intersection? Intersection的作用是根据多个搜索条件的各自的索引来快速过滤掉不符合索引列条件的记录,从而减少大量二级记录回表的成本.
而要是Intersection之前还需要进行大量数据的排序,可能成本比回表更高.
而Union需要的是从各个索引中寻找到符合各自条件的记录,每个索引列对应的记录数相对情况来说比较少,排序成本不会很大.

第十一章:连接的原理#

我们在业务之中常常需要一次性将多个表的记录关联在一起查询获得.
我们这章就来更加的了解连接.

连接的过程简介#

我们在连接中可以通过一些条件来过滤查询的数据.
一般这些条件分成:

  • 涉及多张表的查询条件,如t1的id字段等于t2的feign_id字段.
  • 涉及到单张表的查询条件.只针对一张表的数据所进行的判断条件.

我们可以先根据下条sql来熟悉一下连接的流程:

select * From t1,t2 where t1.c1>1 and t1.c2=t2.c2 and t2.c1<2;

  • 首先先确认第一张进行查询的表,称这个表为驱动表,我们这里选择t1
  • 然后先使用涉及到单表的查询条件,去选择查询该表成本最小的访问方法,即使用t1.c1>1这个条件去寻找.我们假设这张表没有建立索引,所以使用的是all访问方法.
  • 当在t1中每查询到一条记录,都会去t2表中查询到其匹配的记录,这个时候我们就能使用到涉及到多表的条件了.将会在t2的单表查询中使用到涉及到t1表的条件进行查询.

一般一个俩表的连接查询中,驱动表只需要访问一次,而被驱动表需要访问多次.
并不是先将所有满足条件的驱动表记录查询出来,放在一个地方,再去查询被驱动表,而是每获取一条驱动表记录就立刻去被驱动表中寻找匹配的记录.

内连接和外连接#

我们上面举例的sql就是一条内连接查询sql.

那么为什么会有内连接和外连接的区分呢?
我们可以想象一下,上面这条内连接的sql如果在被驱动表中没有找到和对应驱动表记录关联的记录.那么就不会将记录返回给客户端.也就是内连接只查询驱动表在被驱动表中有匹配的记录.
但是我们实际业务中呢?有时候还需要即使被驱动表中没有与驱动表中对应记录匹配的记录,也要将其查询出来,而被驱动表相关联的字段填充为NULL.
这个时候我们就需要某些东西来控制是否要返回这些数据.
而MYSQL提供了俩种连接方式来控制.即内连接或外连接

  • 内连接的写法有:
select 查询字段 from1,表2 [where 过滤条件] ;
select 查询字段 from1 inner join2 [where 过滤条件];

当然 inner join也可以将条件放在on子句后,不过内连接将条件放在on后和where后效果都一样.

  • 外连接分成左外连接(left join) 和 右外连接(right join)
    写法:
select  查询字段 from t1 left join t2 on 连接条件 [where 普通过滤条件];
select  查询字段 from t1 right join t2 on 连接条件 [where 普通过滤条件];

外连接通过紧跟在join语句后的on子句中的条件来寻找关联的记录.
如果外连接在被驱动表中无法寻找到与on后面的过滤条件匹配的记录的话,就会将被驱动表的相关的查询字段填充为NULL返回给客户端.
而如果是where中的条件,则只要寻找不到其条件匹配的记录就不会加入到结果集,即不会返回给客户端该条记录.

连接的原理#

嵌套循环连接#

嵌套连接查询是:
先查询根据驱动表有关的过滤条件(根据条件选择成本最小的访问方法)去查询驱动表中的数据,而后再对驱动表查询的结果集中的每一条记录分别去被驱动表中查询匹配的记录
在这个连接查询中,多个表的连接就像一个嵌套循环查询一样,驱动表是外循环,而被驱动表是内循环.
这种连接查询方式驱动表只会访问一次,而被驱动表会访问驱动表的结果集大小的次数.
每查询到一条驱动表的记录都会立即去被驱动表中查询对应匹配的记录.如果符合条件就把组合后的记录返回给客户端.

使用索引加快连接速度#

我们在连接查询的时候,本质就是对驱动表的结果去被驱动表中根据条件再次查询匹配的数据,查询被驱动表就相当于一个单表查询,那么对于这个查询我们同样能通过建立索引来极大的提高查询速度.
我们在连接查询效率低下的时候,可以通过分析连接语句来进行建立对应查询成本低的索引来提高查询速度.
当然,为了利用这些索引和减少回表次数,尽量不要使用*来作为查询列表.

基于块的嵌套循环连接#

当连接的表中的数据量非常大的话,而被驱动表每次都需要进行全表扫描的话,那查询量不是非常大吗?驱动表有多少条数据就得全表扫描被驱动表多少次.

这些全表扫描中很多次可能都是对同一批数据的重复扫描.需要重复将被驱动表的数据加载到内存中.
我们想象一下这种情况:
该连接查询语句,被驱动表中无法使用索引来减少扫描区间,需要使用all访问方法全表扫描,而被驱动表的数据量十分大,内存并存放不了被驱动表的所有数据,可能会出现在查询该表的后半部分数据时,内存不够,只能将内存中存储的前半部分数据释放掉,然后再将后半部分数据加载到内存中.这时在驱动表查询第二条记录时,再去被驱动表的寻找匹配的记录时,又会出现这种情况,内存不足,释放掉已装载到内存的数据,再次加载.
出现这种情况会造成大量的将被驱动表重复数据从磁盘加载到内存中.

所以Mysql的设计者就提出了一个概念:Join Buffer(连接缓冲区).
当连接查询的时候,将驱动表结果集中的一些数据放置到该缓冲区中,而后去被驱动表中查询匹配的数据时,对Join Buffer中的多条驱动表中的数据进行匹配.由于这些操作都是在内存中进行的,能够显著的减少被驱动表的I/O代价,减少重复将被驱动表中的数据加载到磁盘的次数.

如果Join Buffer的空间足够大,可以装下驱动表中的所有数据,就可以一次性将驱动表的数据存放下来,就总共只需要一次被驱动表的全表扫描就可以查询到该次连接查询的结果了.

Join Buffer 并不会把驱动表中的所有记录存放进去,而是只会在查询列表和过滤条件中的列才会存放下来,所以我们尽量不要使用*作为查询列,这样能使Join Buffer能够放下更多的记录.

所以我们常说在连接查询中使用小表驱动大表,我认为一方面:相同表的数据在磁盘上分布连续的概率比较大,这样在查询大量被驱动表数据的时候尽量能够减少随机IO
另一方面:Join Buffer中能够尽量存下大部分或是全部驱动表的符合条件的数据,可以显著减少被驱动表的查询次数.

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

一条查询语句的执行成本由俩个部分组成:

  • I/O成本:InnoDB,MyIsAM存储引擎主要将数据和索引存储在磁盘上,而处理数据都是将数据从磁盘加载到内存中,这个加载过程损耗的时间我们就称之为I/O成本.
  • CPU成本:而读取记录或者检测记录是否满足对应的搜索条件,对结果集进行排序等这些操作所损耗的时间成本称为CPU成本.

MySQL的设计者规定:读取一个页面花费的成本默认是1.0,而读取以及检测一条记录是否符合搜索条件的成本是0.2(即使只是读取一条记录并不检测是否符合条件也是0.2)

单表查询的成本#

基于成本的优化步骤#

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

单单这样说可能印象不是很深刻,并且其中的一些细节不够明朗,就比如这些代价的计算方式是怎么样的?是根据什么条件计算的?
我们可以通过一个例子来提高这些印象:

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'
  1. 根据搜索条件找出所有可能使用索引: 这个sql语句中,key1,key2可以创建出对应的扫描区间,而key3比较的条件是另一个列的值,key_part1所使用的like比较以'%'开头无法利用索引来减少扫描区间,而common_field没有对应的索引,所以这三个列不属于可能使用的索引.所以key1和key2列对应的索引是可能使用的索引,设计者称这些索引为possible keys.

  2. 计算全表扫描的代价: 因为全表扫描需要将表中的所有记录都加载到内存中,并对这些信息根据条件进行判断,所以计算全表扫描时的代价需要有的信息:聚簇索引所占有的页面数目、该表中的记录数.
    那么这俩个信息从哪获取呢?
    设计者为每个表都维护了一系列统计数据,我们可以使用SHOW TABLE STATUS LIKE '表名'来查看对应表的统计信息

    其中Rows和Data_Length是我们暂时比较关心的,Rows:表示表中的记录总数,不过对于InnoDB来说该值只是一个估计值.Data_Length:表示表中占用的存储空间的字节数.对于InnoDB来说该值相当于聚簇索引占用的存储空间大小,我们可以根据这个值来除以页面大小得到页面数.
    我们假设single_table的Data_length的值为1589248,则推出该聚簇索引的页面数量为:1589248/16/1024=97.而Rows为9693
    我们就可以根据这些信息来计算IO成本和CPU成本.
    IO成本:971.0+1.1=98.1 (页面数读取一个页面的花费+微调值1.1)
    CPU成本:96930.2+1.0=1939.6 (记录条数读取一个记录并且比较的花费+微调值1.0)
    总成本:98.1+1939.6=2037.7

这些微调值是直接写入到代码中的固定值,我们没必要太过纠结,可能是设计者根据经验来对结果进行微调的常量.
虽然我们在使用聚簇索引查询的时候可能并不会使用到所有的页面,比如其他内结点所在的页面,直接根据所占用的所有页面来计算确实有点简单直接粗暴了.

  1. 计算使用不同索引执行查询的代价: 这一步要根据前获取的查询可能使用到的索引来计算使用不同索引进行查询的成本.
    Mysql的查询优化器会先去分析唯一二级索引的成本,然后再去计算普通二级索引的成本.
    计算key2索引
    key2能够形成有效扫描区间的条件是:key2 > 10 AND key2 < 1000,其扫描区间为(10,1000)
    使用该二级索引进行查询主要需要先去二级索引树中查询对应扫描区间内的记录,然后再去聚簇索引回表查询出完整的记录判断其他条件.
    设计者计算普通索引成本计算依赖于俩个方面的数据: 范围区间数量 需要回表的记录数
  • 范围区间数量: 我们这里key2索引只形成了一个扫描区间(10,1000),数量为1
    访问这个扫描区间付出的IO成本为1.0*1=1

  • 需要回表的记录数: 我们可以直接使用左区间key2>10去二级索引中寻找到索引中最左边的那条记录,我们称这条记录为区间最左记录,这个时候我们再使用key2<1000来寻找第一条符合该条件的记录,称为区间最右记录
    这个时候我们已经获得了区间最左记录和最右记录,该怎么去计算需要回表的记录数呢?
    我们会从区间最左记录从右遍历10个页面的记录进行计数,如果遍历到区间最右记录就停止遍历,所以如果区间最左记录和最右记录只相差十个页面或是十个页面之内,获得的记录数是精确的.那如果大于10个页面呢?那就会去计算这10个页面平均的记录数量是多少,然后去计算区间最左记录和最右记录之间的页面数,使用 页面数*平均记录数 来获得需要回表的记录数
    那么这样我们又需要去获取对应的页面数了,这又该怎么获取呢?
    我们可以利用B+树索引的结构来获取这个页面数,我们想要获取的是区间最左记录叶子节点和区间最右记录的叶子节点之间的页面数,我们知道,叶子节点的上一层非叶子节点存储着叶子节点页面的信息,所以我们可以寻找非叶子节点最左记录所在的页面记录和非叶子节点最右记录所在的页面记录之间的记录数目,这就是这俩个记录之间的页面数量.
    那如果页面数量太多,使最左记录和最右记录所在的页面并不在上一层非叶子节点的同一个页面呢?那我们可以继续递归到再上一层节点.一般B+树的层级不会很高,所以这个过程消耗不会很高.

  • 我们根据上面所描述的算法,得知(10,1000)区间内的二级索引记录有95条.
    这个时候估算CPU成本为:950.2+0.1=19.01 (记录数读取一条记录的成本+微调值)
    而回表所造成的损耗,设计者直接认为每次回表都有直接从磁盘上读取一个页面的成本,为1.0,而这里二级索引记录有95条,这个回表造成的IO损耗为951.0=95
    现在我们可以进行总成本的计算了:
    IO成本: 1+95=96 (范围区间的数量+回表记录数量)
    CPU成本: 19.01+95
    0.2=38.01 (符合条件的二级索引记录读取成本+其对应的聚簇索引记录进行读取并判断是否符合条件的成本)
    总成本:96+38.01=134.01 (IO成本+CPU成本)

  • 而如果使用key1索引来进行查询,形成的扫描区间为([a,a],[b,b],[c,c]),假设其扫描区间的记录数依次为:35,44,39
    总共记录数为118条
    其成本为:
    IO成本:3+1181=121
    CPU成本:118
    0.2+0.01+118*0.2=47.21
    总成本:121+47.21=168.21

不过我们要注意到,像IN这样的操作符可能会形成十分多的扫描区间,那难道要对每个扫描区间进行一个个去判断其记录数吗?

在索引计算的时候,也是有可能会用到索引合并的,不过我们这个例子里面就不展开讲述了.

4.对比这些查询方案的代价,找出成本最低的方案:我们将我们前面计算的成本进行一个比较计算
全表扫描:2037.7 key2:134.01 key1:168.21
key2<key1<全表扫描
故这次查询使用key2索引来进行查询

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

我们上面提出过疑问,IN操作符可能会产生许多的单点扫描区间,我们上面的例子中只形成了三个区间还好,但要是一个sql中IN中塞了几百个几千个条件呢?这个时候再去对应的索引中对这些扫描区间一个个取出数据进行分析计算就成本很大了.
我们前面直接访问索引对应B+树进行分析的方式称为:index dive

而我们将要介绍通过索引统计数据进行分析计算的方式:

当什么时候会使用index dive?什么时候会通过索引统计数据来进行分析呢?#

mysql中有一个系统变量eq_range_index_dive_limit用于判断分析的方式,如果in中的参数小于该值则会使用index dive.大于等于就会通过索引的统计数据来分析.
我们可以通过show VARIABLE like '%dive%'来查看该系统变量的值

那么是从哪获取到对应的索引统计数据呢?#

我们可以通过show index form 表名;来获取到索引相关的一些数据,其中包括一些统计数据.

我们这重点关注的是Cardinality这个字段的值,这个字段的意思是该索引值不重复的值个数,比如如果为1,那说明该表中该索引的值都是重复的,而为10,说明该索引字段的值有10种.

不过要注意,在InnoDB中Cardinality统计值为估计值,并不是准确的.

一个值出现的次数≈该表的记录条数/该索引值不重复的值个数
该表的记录条数可以通过SHOW TABLE STATUS;来获取.
我们假设single_table的rows为9693,而key1的Cardinality为968,那我们估算其一个值的出现次数为:9693/968≈10条
要是IN条件中有2000个条件,则为2000*10=20000条,我们则假设需要回表的记录条数为20000条.

这种分析方式的缺点就是结果不够准确,可能和真实结果差别很大,可能会出现某个区间的记录数非常非常多,或者非常非常少.
这个时候对该区间的估算就差别很大了.会造成过多或者过少的回表.

连接查询的成本#

condition filtering#

俩表连接查询的成本=单次访问驱动表的代价*多次访问被驱动表的代价.
而访问被驱动表的次数主要取决于单次访问驱动表后获取的符合条件的记录条数.
我们称这个记录条数为扇出(fanout).

如果扇出越小,则访问被驱动表的次数也就越少,则连接查询的成本也就越低.

而在这个过程中,查询驱动表的结果集并不是所有都在索引内,无法通过索引来得知结果集大小.这种情况设计者通过condition filtering来估计判断扇出的大小(据说是通过启发性原则来得知).这个计算不确定因素较多,可能通过索引,也可能通过索引统计数据等等,整个评估分析过程比较复杂,所以就暂时不展开说了.

比如:
select * from s1,s2 where s1.key2>10 and s1.key2<1000 and s1.common_filed='zzz';
这种情况我们可以知道s1.key2>10 and s1.key2<1000所形成的区间记录条数,但是无法知道这写结果中满足 s1.common_filed='zzz'条件的记录条数.就需要去猜(10,1000)区间内符合s1.common_filed='zzz'条件的记录有多少条.

俩表连接的成本分析#

连接查询总成本=驱动表连接查询成本+驱动表扇出*被驱动表查询成本
在进行外连接查询的时候,驱动表和被驱动表已确定,则成本分析只要先分析驱动表的成本,然后再去分析被驱动表的成本即可,但是如果是内连接查询,驱动表和被驱动表是不确定的,所以查询优化器会去分析在哪张表为驱动表的情况下查询成本最低.

连接查询优化的主要目标是:减少驱动表的扇出、减少多次访问被驱动表的成本.

我们在编写连接查询语句的时候尽量在连接列上建立索引,这样就可以使用ref访问方法来进行匹配了.

多表连接的成本分析#

当有许多张表进行连接查询的时候,会有许多种连接顺序.
连接顺序种类=连接表个数!

那难道我们要将这n!个顺序一个个去计算分析其成本吗?
那太耗费时间了,所以设计者提出了一些规则来避免过多表连接时的成本分析的损耗,来提早结束分析过程.

  • 提早某个顺序的成本评估
    在计算各自连接顺序之前,mysql会维护一个全局变量来存储当前成本最小的连接顺序,如果现在连接顺序计算的成本已经大于最小成本的连接顺序时,则直接不继续进行该连接顺序的计算.
    比如A B C三表的连接,成本最低的是ABC连接顺序,成本为10,而在计算BCA连接顺序时,BC俩表的连接成本为15,大于10,这个连接顺序就没必要继续计算下去了,肯定会比ABC大.
  • 系统变量 optimizer_search_depth
    该系统变量控制分析成本的表的数量,如果连接查询中表的数量小于这个数值,则会穷举每一个连接顺序,但要是大于这个值,则只会取该值数量的表进行分析.
  • 启发式规则 optimizer_prune_level
    如果在计算的时候发现某些连接顺序不满足某些启发式规则(根据经验总结的规则)直接不进行分析.用optimizer_prune_level该系统变量来控制是否启用这些规则.

调节成本常数#

我们前面说的读取一个页面的成本为1.0,而读取一条记录进行检测的成本为0.2
难道这些成本都是写死在代码里的吗?不能灵活的去配置让我们根据环境来获得权重不一样的查询吗?

当然不是,这些信息被存储到mysql数据库(一个系统数据库)中的server_cost和engine_cost表中.
如果一个操作的成本与其存储引擎没关系,是在server层操作的,则其成本存储在server_cost.
而一个操作是与存储引擎层相关的,则其成本存储在engine_cost.

我们可以通过sql语句直接去更新该表中的值,然后使用FLUSH OPTIMIZER_COSTS来更新各操作对应的成本.

第十三章 InnoDB统计数据的收集#

统计数据的存储方式#

InnoDB存储数据的方式主要有:存储在磁盘上 存储在内存中
存储在磁盘上即使服务重启,但是其统计数据依旧存在,但是如果选择存储在内存中,这些统计数据就会清除,并且在适当的场景下重新收集.
服务端可以通过innodb_stats_persistent这个系统变量来控制存储方式
以表为维度可以在创建和修改表的时候指定STATS_PERSISTENT属性指定标统计数据存储方式

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

实际上不同表存储在磁盘上的统计数据都是存储到了mysql系统数据库下的innodb_index_stats表 和 innodb_table_stats表下.

  • innodb_index_stats:关于索引的统计数据,其记录对应着一个索引的统计数据;
  • innodb_table_stats:关于表的统计数据,其记录对应着一个表的统计数据;

innodb_table_stats结构#

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

我们接下来来说下其中几个字段的收集方式

  • n_rows:先按照一定算法选择几个页面来统计每个页面中包含的记录数量,然后计算其中平均的记录数量,最后再将叶子结点的数量乘以平均记录数量,就是n_rows的值了.所以这个值只是一个估计值.我们可以通过innodb_stats_persistent_sample_pages这个系统变量来改变其取样的页面数量,该变量数量越大,则n_rows值越准确.

以表的维度是 STATS_SAMPLE_PAGES 属性

  • cluster_index_size和sum_of_other_index_sizes:
    cluster_index_size是表中索引占用的页面数量
    sum_of_other_index_sizes是表中其他索引占用的页面数量
    统计一个索引占用的页面数量,我们可以利用什么?我们可以想到之前介绍的段所对应的INODE Entry结构,Innode Entry结构中存储了段所对应的区和零碎的页,我们可以统计索引对应段的零碎的页面,然后再去统计其三个区链表,统计段所占用的区的数量.
    步骤:
  1. 从数据字典(系统表SYS_INDEXES)中寻找到该索引对应的根页面位置.
  2. 从根页面的PageHeader中寻找到叶子节点和非叶子节点的Segment Header.
  3. 通过非叶子节点和叶子节点的Segment Header寻找到段对应的InnoDB Entry.InnoDB Entry结构中存储了该段所存储的零碎的页面和区链表的基结点.
  4. 我们可以通过链表基结点遍历去得知该段占用了多少个区.
  5. 每个区占用64个页面,我们只要将其段对应的区链表 (FREE_LIST 和 NOT_NULL_LIST 和 FULL_LIST) 中的区数量乘以64,然后再加上占用零碎页面的数量,就是该段的页面数量合了
  6. 如法炮制,对一个索引的叶子段和非叶子段都计算其段页面数量合,就可以计算出来该索引的页面数量总和了.

接下来的事情就很简单了,将其他索引也按照这个方式来计算,将合相加就是sum_of_other_index_sizes的值了.

因为当索引占用一定数据量(32个页面后)就会以区为分配单位,所以这俩个统计数据的值会大于真实占用页面数.

innoDB_index_stats结构#

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

该表中的记录是先按照table_name划分,再按照index_name划分,最后按照stat_name划分,即(table_name,index_name,stat_name)划分一条数据.
其具体的统计项:

  • n_leaf_pages 该索引的叶子结点实际占用多少页面
  • size 该索引共占用多少页面(包括已经分配但是还未使用的空白页面)
  • n_diff_pfxNN 表示对应索引不重复的值有多少.后面的NN是为了应对联合索引的情况,表示索引中前N个列组合的不重复值情况,比如n_diff_pfx01就是最左列不重复值的情况,n_diff_pfx02就是从左数第一个和第二个组合起来不重复值的情况.

n_diff_pfxNN这个属性要注意,当为普通二级索引时,他还会计算索引列+主键值的不重复情况,但是是唯一二级索引时,他就不会计算和主键值组合的不重复情况了.
n_diff_pfxNN进行计算的时候需要对某些叶子节点页面进行采样计算,计算的数目为innodb_stats_persistent_sample_pages * 索引列数目

定期更新统计数据#

  • innodb_stats_auto_recalc(默认值ON) 当表内的记录变动超过10%,并且该系统变量值为ON,则会异步重新计算一次统计数据.表纬度的属性为:STATS_AUTO_RECALC
  • 手动调用 ANALYZE TABLE 表名;我们可以通过该命令来直接更新统计信息.
  • 我们可以直接对innodb_index_stats表 和 innodb_table_stats表进行增删改来更新信息

基于内存的非永久性统计数据#

系统变量innodb_stats_persistent值为OFF时或是表属性STATS_PERSISTENT为0时,则统计信息基于内存存储.
该存储方式页面采样数量由innodb_stats_transient_sample_pages来控制
基于内存会在关闭服务时以及执行某些操作后清除,在下次访问表的时候进行重新计算.
使用较少.

innodb_stats_method#

我们在server层查询优化时,计算查询方案的成本之中,索引列不重复数量值十分重要,涉及到了我们该选择哪个方案进行查询,但是NULL值比较特殊.有许多分歧,这个系统变量就是为了解决这个问题

innodb_stats_method这个系统变量主要决定统计某个索引列中不重复值的数量时,对NULL值该如何对待

  • nulls_equal 认为NULL值都是相等的,这是默认值
  • nulls_unequal 认为NULL值都是不相等的
  • nulls_ignored 直接忽略NULL值

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

Mysql的设计者会依据一些规则,将一些效率低的sql语句转换成可以高效执行的形式,我们称这个过程为查询重写.

我们现在来介绍一下这些优化的规则:

条件化简#

我们编写的查询语句中的搜索条件有时候会比较复杂或是无法高效的执行,Mysql优化器会帮助我们对这些搜索条件的表达式进行简化.

移除不必要的括号#

表达式中有无用的括号会被优化器给去除:
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

常量传递#

有时的表达式中是某个列和常量比较,而另一个列又和该列比较,优化器会自动帮助我们优化
比如 a=5 and b > a
这种情况下优化器就会自动优化为: a=5 and b>5
但是要注意,如果是OR操作符就不能进行这样的优化
比如 a=5 or b > a
这种情况下并不是只筛选满足a=5时,b列又大于a列值的情况,还有a!=5时,b列大于a列值时候的情况,如果使用OR进行优化的话会变成 a=5 or b>5
这样就不符合原本的语义了.

移除没用的条件#

Mysql优化器还会为我们自动移除掉永远为True或者False的表达式.
比如 (a>1 and b=b) or (a=6 or 5!=5)
这种情况下 b=b 永远为真 而5!=5永远为假
就会优化为:(a>1 and true) or (a=6 or false)
再次进行简化就会变成 : a>1 or a=6

表达式计算#

如果表达式之中只包括常量,该表达式的值就会先进行计算,而后再进行查询.
比如: a=5+1
就会先优化为 a=6
但是列不是以单独的形式,而是作为某个计算式中的一元或是函数的参数,参与了某个运算等等情况,mysql优化器并不会帮助优化,并且即使该列是索引列也无法利用索引.

HAVING子句和WHERE子句的合并#

如果查询语句中没有GROUP BY 子句 或是 SUM, MAX 这样的聚集函数,则查询优化器会将HAVING子句和WHERE子句进行合并.

常量表检测#

Mysql的设计者觉得有俩种查询的消耗非常小,能够被称为常量级别的,查询成本少到可以忽略.

  • 查询的表只有一条或者没有记录.但是InnoDB存储引擎表的统计数据是估计的,所以这种情况只对MyIsam或Memory存储引擎生效.
  • 使用主键索引和唯一二级索引的等值匹配查询.
    设计者称这种查询对应的表为常量表
    在对常量表的查询时,查询优化器会先对常量表进行查询,然后将与常量表有关的条件使用常数进行替换
    比如:
select * from t1 inner join t2
on t1.c1 = t2.c2
where t1.id = 1;

我们假设t1的主键是id,那么对t1表以id为条件的查询就是对于常量表的查询,查询优化器会先将t1对应的条件查询(常量表查询出来的一定会是一条记录或是没有记录),然后将该表中的相关的条件进行替换

select t1记录的各个字段,t2.* from t1 inner join t2
on t1.c1列的常量值 = t2.c2;

外连接消除#

我们知道,内连接和外连接的区别就是是否当连接的其他表中没有与ON子句中符合条件的记录时,内连接不会将其记录添加到结果集,而外连接会将其他表中没有寻找到匹配记录的字段填充NULL并添加到结果集.

在查询优化部分也有区别,内连接的驱动表不确定,所以可以通过计算不同连接顺序的成本来选择合适的驱动表和被驱动表.而外连接呢?驱动表和被驱动表是固定的,所以不能通过更改驱动表来进行成本更低的查询.

但是我们有没有办法能够让外连接在某些情况下也能享受到能够选择不同的驱动表来减少查询成本的优化吗?
答案是有的,外连接查询的结果中的被驱动表中有列不能为NULL值,比如NOT NULL,或者和某值做比较等情况.
这种情况下的外连接查询,即使使用的是外连接,但是where子句中对被驱动表的记录进行了约束,不能为NULL值,故被驱动表没有了匹配的数据也无法添加到结果集,这样的外连接查询和内连接查询等值了.
符合这种规则的语句,mysql就能够通过评估表的不同连接顺序的成本来选择成本最低的连接顺序.

而外连接消除这个规则:如果where条件中有包含被驱动表的列不为NULL的条件
我们称这种情况是空值拒绝(reject-NULL)

子查询优化#

子查询是一种能够出现在外层查询语句中不同地方的查询语句,比如 SELECT、FROM、WHERE、ON、ORDER BY、GROUP BY等子句中。

子查询语法#

按照子查询的返回结果集区分子查询:#

  • 标量子查询:只返回一个单一的值的子查询就是标量子查询,该子查询可以作为表达式的一部分或者一个值在外层查询来使用。
  • 行子查询:只返回一条记录的子查询,不过该方式和标量子查询的区别就是可以包含多个列。
  • 列子查询:只返回一个列但是有多行数据的子查询。该方式和标量子查询的区别就是会有多条记录。
  • 表子查询:可能会返回多条多个列的记录。查询结果形成一个表。

按照子查询与外层查询的关系区分:#

  • 不相关子查询:子查询的查询不依赖于外层查询,可以单独查询出结果集。
    比如:
    select * from t where key1 in (select key1 from t where id=1);
  • 相关子查询:子查询的查询依赖于外层查询
    比如:
    select * from t where t2_id in (select id from t2 where t2.t_id=t.id)
    这个子查询中就需要依赖于t1表的id列,所以是个相关子查询.

子查询在布尔表达式中的使用#

我们一般把子查询放在布尔表达式中来协助外层查询,比如在WHERE和ON子句中.

  • 使用">,<,<=,>=,=,!="等操作符来进行判断,作为布尔表达式的操作符
    我们称这些操作符为 comparisor_operator
    操作数 comparisor_operator (子查询)
    这里的子查询只能是标量子查询或者行子查询,即只能有一行数据,否则无法进行具体比较.
    那么如果有需要一个操作数和子查询中的多行数据进行比较该怎么办呢?后面有办法.

  • 使用"[NOT] IN / SOME / ANY / ALL"子查询
    使用这些操作符进行子查询,就不限制对应子查询的返回结果集类型了,只要和操作数所对应即可

    • NOT IN 和 IN: 具体使用方式为 操作数 NOT IN / IN (子查询)
      这种方式判断操作数是否在对应子查询的结果集中.IN操作符如果存在则为True,否则则为False,NOT IN相反
    • SOME 和 ANY : 操作数 comparisor_operator SOME / ANY (子查询)
      这俩个操作符需要和comparisor_operator进行配合使用,只要子查询的结果集中有一个记录符合操作数与comparisor_operator的比较,那么该表达式为True,否则为False.
      SOME与ANY作用一样,只是写法不同.
    • ALL : 操作数 comparisor_operator ALL (子查询)
      这个操作符的意思为:该操作数需要满足与子查询结果集都符合comparisor_operator的比较,则该表达式为True,否则为False
  • Exist 操作符
    [NOT] Exist (子查询)
    有时候我们只需要在乎该子查询是否结果集是否有记录,而不用去进行比较或者要求子查询中的数据的具体细节.就可以使用Exist操作符,当对应子查询的结果集中有记录,则该表达式为True,否则为False.而NOT Exist相反.

子查询使用注意事项#

  • SELECT子句中的select语句必须是标量子查询或者行子查询
  • 如果不能保证子查询获得的结果集为标量或者行子查询可以通过Limit 1来限制数量.
  • 对于 IN / SOME / ANY / ALL 操作符来说,子查询中不允许使用Limit子句来限制数量.同样也不支持ORDER 、DISTINCT 、没有聚集函数和HAVING的GROUP子句 .出现这种情况会在查询优化器部分直接优化掉.
  • 不允许在增删改语句中同时使用子查询,但是可以更改一下写法来避免.
    比如:delete from payment where id = (select max(id) from payment);
    我们想要在删除语句中先通过子查询查询到id最大的记录,然后进行删除.
    但是这样是会报错
    SQL 错误 [1093] [HY000]: You can't specify target table 'payment' for update in FROM clause
    因为MYSQL是不允许在一条语句中增删改某个表的记录时,同时还对这张表进行子查询.
    但是我们可以修改为:
    delete from payment where id in (select t.id from(select max(id) id from payment) t);
    将子查询的结果再通过中间表查询一遍就可以使用了,不过这种方式不太建议,提高了sql阅读成本.我们可以分成俩次sql操作来完成.

要注意,似乎是只有在mysql才会报这个错,oracle等数据库能够在更新数据时候使用子查询.

子查询在MYSQL中是怎么样执行的#

标量子查询和行子查询的查询流程#

这俩种只查询一行记录的查询方式,根据是否和外层查询是否相关来决定查询流程

  • 不相关子查询:会先查询子查询,然后将子查询的结果作为外部条件的参数,然后再进行外部查询
  • 相关子查询: 会先根据外部查询进行查询,当外部查询每寻找到一条满足外部查询的条件,再将该记录的值带入到子查询中进行查询,查看子查询的结果集是否满足子查询的表达式,即外层查询的where子句后结果为True,如果满足则加入结果集,否则丢弃.而后外部查询继续查询下一条记录,以此往复来获取最后的结果集.

IN子查询的优化#

IN子查询是我们在实际工作中最经常使用的子查询方式,所以Mysql设计者为优化IN子查询采取了许多手段.
我们接下来就来介绍一下IN子查询的优化手段

物化表

我们可以先来看一个普通的不相关子查询语句:
select * from t1 where id in (select t2.t1_id from t2)
按照我们上面所说的查询方式,将这外层查询和子查询当做俩个普通的单表查询来查询,先查询子查询,而后将子查询的结果集替换到外层查询的条件中.
但是我们可以设想一下,如果子查询查询出来的结果集就几条记录,那对外层来说对于每条记录对只有数个结果集来进行判断的成本并不高
那要是子查询的结果集非常大呢?
甚至内存也存不下来,并且因为结果集太大,对于外层查询来说,对于每条记录都要判断对应对应条件是否与结果集中的某个记录相等,这样成本就很大了.

那设计者怎么样去解决这个问题呢?Mysql的设计者提供了一个物化表的方式.
物化表:将不相关子查询的结果集写入到一个临时表中,而不是直接作为外层查询的参数

  • 临时表的列就是该子查询结果集的列

  • 临时表中的数据会被去重

  • 临时表的建立有俩种类型:

    • 基于内存的MEMORY存储引擎的临时表,同时该表使用Hash作为索引
    • 基于磁盘的存储引擎来保存结果集的记录,改为使用B+树作为索引
      当子查询结果集小于系统变量tmp_table_size或是max_heap_table_size时,采取内存的临时表,否则使用基于磁盘的临时表.
  • 当子查询的结果集存储到临时表后,就可以通过Hash索引或是B+树来快速判断对应记录是否存在在该表中,速度就变得非常快了.
    我们称这个将不相关子查询结果集存储到临时表的过程为物化(materialize).对应的临时表为物化表.

这里建立的索引我觉得是对临时表中所有列建立的索引,因为在IN条件中,外部查询的比较列是和子查询查询的列一一对应的,并且还可以通过这个唯一索引的过程来去重数据,这样使用外部条件就可以高效的利用所有的列来索引寻找是否有存在该记录了.

物化表转连接

当将IN对应的子查询转换成另一个临时表后,我们再来看该临时表和外部查询之间的关系是怎么样的?
当外部子查询对应的列在该临时表中寻找到相等的记录,如果寻找到了则加入到结果集中,否则则丢弃.
有没有发现?这关系就和我们的多表进行内连接一样,都是在多个表中寻找匹配的记录,寻找到就加入到结果集,否则就丢弃.
例如:

select t1.* 
  from t1
  wehre t1.key1 in (select t2.key1 from t2 where t2.id>10);

就等同于

select t1.* 
  from t1 inner join material_table
  where t1.key1=material_table.key1

material_table 指执行select t2.key1 from t2 where t2.id>10子查询之后的结果集,所以不需要再转换成内连接后的语句中添加子查询中的条件判断了

那么我们的IN子查询在物化之后,是否可以将其IN操作转换成内连接?这样就可以享受到内连接在查询优化阶段的通过计算不同连接顺序的成本,来选择成本更低的连接顺序来执行查询了.

将子查询转换成半连接

我们上面说过,进行不相关子查询的时候可以将子查询的结果集进行物化,放入临时表内,而后使用该临时表和外层查询进行内连接来进行查询.
那么不相关的子查询我们该怎么样优化呢?是否也可以通过转换为连接的方式来提高其查询效率呢?或是不进行物化操作可以直接把子查询转换成连接查询呢?

我们可以把目光看向刚刚的子查询语句:

select t1.* 
  from t1
  wehre t1.key1 in (select t2.key1 from t2 where t2.id>10);

如果不进行物化操作直接转换成连接操作就好像是:

select t1.* 
  from t1 inner join t2 
  on t1.key1=t2.key1
  wehre t2.id>10;

但是仔细一想,不对,如果直接转为这样的连接查询语句,那么要是t2中有多条符合id>10并且key1值和t1表中某条记录一致的记录,那样不是t1的同一条记录可能被返回多次吗?
我们想要的是这样的结果吗?
显然并不是,我们希望符合条件的t1记录只要有一条即可,只要关心对应的记录是否在t2有匹配的,有则返回,就转而t1的下一条记录,而不是会多次匹配并且加入到结果集.
所以Mysql的设计者提出了一种连接方式半连接(semi-join)

对于这种连接方式,连接表只需要关心在被连接表中是否有与其匹配的,并不关心其匹配条数,最后加入到结果集的也只有第一张表的记录.
上面的语句会变成这样:

select t1.* 
  from t1 semi join t2 
  on t1.key1=t2.key1
  where t2.id>10;

该种半连接查询方式只支持Mysql内部使用,并不支持我们客户端采用该方式去使用.

当然,我们只是提出了概念:SEMI_JOIN(半连接)可以关心被连接表中是否有匹配的数据,而不需要去关心匹配的条数,也不用返回被连接表的数据,那么我们该怎么实现它呢?
Mysql的设计者也使用了好几种办法来实现,根据不同情况来采取不同的方式,我们接下来就来介绍一下:

  1. Table pullout(子查询中的表上拉)
    当子查询中的查询列中只有主键或是唯一索引(即能保证其子查询的结果集都为唯一值).这样就不会担心有连接表记录和多个被连接表记录匹配了.
    就直接将子查询上拉到外层查询中.
    比如:
    select t1.* from t1 where t1.id in (select t2.id from t2 where t2.colum='a')
    就会变成:
    select t1.* from t1 inner join t2 on t1.id=t2.id**where **t2.colum='a'
    将子查询的条件上拉到外层查询的条件中,而子查询的列合并到from子句中.

  2. Duplicate Weedout(重复值消除)
    如果子查询的列可能会重复值,那么为了防止连接表的记录被多次加入到结果集,我们这里可以创建一个只存放连接表其主键列的临时表.
    当每次连接表中记录和被连接表中匹配了一次,将其记录加入到结果集前,先放入到临时表之中,如果临时表新增成功了就说明该记录不是重复的,可以加入到结果集之中,如果新增失败就说明该记录已经被新增到结果集之中过了.

  3. LooseScan(松散扫描)
    如果子查询可以使用到索引,并且子查询的列刚好也是索引内的,那么子查询的结果集就可以根据该索引进行分组排序.分为多个不同的组(但是组内的一个或多个记录的值相等).
    这个时候我们可以将子查询的结果集作为连接驱动表,只需要将其中每一个分组的第一个记录,去匹配连接表中的记录.
    如果能在外层查询的表中寻找到对应匹配的记录则加入到结果集中,否则丢弃,再使用下一个分组的第一条记录继续匹配.
    不会有一条记录能够同时与多个分组的的记录相等,所以这样最后得到的结果也是不重复的.

  • 条件:子查询可以利用到索引,并且可以利用索引将子查询的查询列进行分组.
    而后使用子查询的结构集作为驱动表,只使用子查询中每一个分组的第一条记录与外层查询的表进行匹配
    因为外层查询的表只会有一个值(不会与子查询的多个分组的记录同时相等),所以也不会被多次添加到结果集中.
  1. FirstMatch(首次匹配)
    这就和我们上面设想的相关子查询的查询方法一样, 先执行外层查询,当外层查询中查询到一条记录时,再进行子查询,如果子查询中可以找到一条匹配的记录,则把对应外层查询的记录加入到结果集中并且停止继续寻找更多匹配的记录.
    而后再对外层查询寻找到的下一条记录进行这样的操作.

对于相关子查询可以将他先转换成半连接语句的查询方式,然后根据不同的情况选择不同的半连接实现策略方式.
比如:

select * 
from s1
where key1 in (select common_field from s2 where s1.key3=s2.key3);

可以方便转换成半连接的查询方式

select s1.*
from s1 inner join on s1.key1=s2.common_field and s1.key3 = s2.key3;

然后再使用以上的TablePullOut、DuplicateWeedout、LooseScan、FirstMatch等半连接的查询策略.

半连接的适用条件
  • 该子查询必须是与IN操作符组成的布尔表达式,并且存在于外层查询的Where语句或是On子句
  • 外层查询可以有其他条件,可是必须与该IN子查询使用AND相连
  • 子查询必须是个单一的查询,子查询不能是使用UNION连接起来的多个查询
  • 该子查询不能包含GROUP BY,HAVING语句或是聚集函数
  • 不能使用NOT IN
    当子查询不能进行转换成连接查询后,Mysql还是有办法来优化它们:
  • 将不相关的子查询转换成物化表,而后再参与查询

这个和上面的物化半连接方式并不一样,这并不会将对应的语句转换成连接语句,只是会针对外层查询的某条记录去寻找物化表中是否有其匹配的记录.

  • 无论子查询是否相关都将IN子查询转换为EXISTS子查询.
    为什么要转换成EXISTS子查询呢?因为转换成Exists子查询有时候可能可以利用的到子查询查询列的索引.
    比如
select * 
from s1
where s1.key1 in(s2.key1 from s2 where s2.key2>10);

这个IN的子查询是利用不到索引的,但是转换成Exists子查询:

select * 
from s1
where Exists(s2.key1 from s2 where s2.key2>10 and s2.key1=s1.key1);

这样Exists就可以利用的到s2的key1索引

当子查询不符合转换成半连接的条件就会使用上述这俩种方式来进行执行

ANY/ALL子查询的优化

大多数时候ANY/ALL子查询都能通过转换成MAX(列)函数或是MIN(列)来进行执行
比如<ANY(SELECT inner_expr ..)转换成<(SELECT MAX(inner_expr)..)

[NOT]EXISTS子查询的执行

如果该子查询是不相关子查询,则就先将该子查询执行,而后将结果(True或是False)替换到该子查询的位置进行重写.
如果是相关子查询,则只能先执行外层查询,而后将外层查询的记录填充条件进行子查询,如果子查询有存在记录则将外层查询的记录加入到结果集,然后继续外层查询的查找,当寻找到一条符合条件的记录时,继续子查询,周而复始,这就和我们上面认为的相关子查询步骤一样,不过这个过程可能能利用到索引来帮助查询.

派生表的优化

如果子查询存在于FROM子句中,我们则称该子查询为派生表
对于派生表主要有俩种执行策略:

  • 将派生表进行物化:先将派生表的结果集写入到一个临时表中,然后将该临时表当做普通表进行使用,不过使用执行策略,设计者使用了一种名为延迟物化(懒加载)的策略,当只有使用到这个派生表的时候才会真正去物化派生表,而不是执行查询前.
  • 将派生表与外层查询进行合并:当派生表符合一些条件的时候,会将派生表和外层查询进行合并,将派生表的的表合并到外层查询的FROM子句中,将条件合并到外层表的WHERE子句中.
    比如:
SELECT * FROM (
  SELECT * FROM s1 WHERE key1='a'
) AS derived_s1 Inner JOIN s2
ON derived_s1.key1=s2.key1
where s2.key2=1;

就会合并变为:

SELECT * FROM s1 INNER JOIN s2
  ON s1.key1=s2.key1
  WHERE s1.key1 = 'a' AND s2.key2=1;

如果派生表子查询有以下问题,则派生表子查询不能和外层查询进行合并:

  1. 聚集函数
  2. DISTINCT
  3. GROUPBY
  4. HAVING
  5. LIMIT
  6. UNION 或者 UNION ALL
  7. 派生表的子查询的SELECT子句中有另一个子查询

第十五章 EXPLAIN详解#

EXPLAIN 能够输出查询语句的具体执行情况

我们可以看到对应的输出有许多的信息,我们接下来就来介绍下有用的信息.

EXPLAIN列组成#

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

各列详解#

table#

即使我们一个查询语句中查询了多张表的信息,但是实际上最后还是需要对每一张表进行单表访问的,而EXPLAIN输出的信息每一条就对应某一个单表访问,而table就是描述该单表访问的表名

这是个单表查询,且只有一个select关键字,所以只有一条记录

而这是个连接查询,查询了俩个表的数据,第二条记录的table值就是ar(即attendance_record表)

id#

当查询语句中每出现一个select关键字,则在explain返回值中都会给其分配一个id.
那么什么时候一个查询语句会出现多个select关键字呢?
有俩种情况:

  1. 子查询

  2. 使用UNION将多个查询语句的结果集进行拼接

    我们可以观察到上面的结果都有俩个id值.那么第三条记录是什么?因为UNION需要对结果进行去重,而这里采用的是临时表的方式去重,所以这里有一个table为<union1,2>的记录

虽然连接查询在from子句中也查询了多个表的数据,但是只有一个select关键字,故explain返回的多条记录也只有一个id值.

如果EXPLAIN子查询返回的结果没有多个id值,可能是被优化为了连接查询

select_type#

每一个select关键字都有一个对应的id,而每一个id都对应着一个select_type.
select_type字段说明了该查询在整个查询中所充当的角色.
那么select_type字段的取值有哪些呢?

  • SIMPLE: 当一个查询不包含子查询或是使用union拼接时,该查询的select_type值就为SIMPLE

  • PRIMARY: 对应包含子查询或者UNION的大查询来说,位于最左边的查询就是PRIMARY

  • UNION: 当出现UNION查询后,除了最左边的查询,其他查询的select_type值即位UNION

  • UNION RESULT: UNION查询之后可能会通过临时表进行结果去重,对于该临时表的查询的select_type值就为UNION_RESULT

  • SUBQUERY: 如果一个子查询无法转换成semi-join的形式(半连接查询),则该子查询的select_type值为:SUBQUERY

    因为外层查询中使用了or连接符,所以无法优化为semi-join.SUBQUERY类型的查询结果会被物化,只会执行一次.

  • DEPENDENT SUBQUERY:如果对应的子查询无法优化为semi-join形式,并且该子查询还是相关子查询,则该子查询的select_type为DEPENDENT SUBQUERY

    因为相关子查询需要外层查询的记录来补充条件,所以该种类型的子查询可能会被执行多次.

  • DEPENDENT UNION: 当一个子查询里使用UNION,并且该子查询内的多个查询都依赖于外层查询,则除了位于最左边的子查询,其他子查询的select_type值都为DEPENDENT UNION

  • DERIVED: 对于派生表中的查询采用物化的方式执行时,则该派生表对应的子查询的select_type为:DERIVED

  • MATERIALIZED: 对于子查询采取先物化,后与外层查询进行连接查询.该子查询的select_type字段值为MATERIALIZED

  • UNCHANGEABLE SUBQUERY 和 UNCHANGEABLE UNION:不常使用到就先忽略了

partitions#

我们暂时都未采用分区,就不解释了,暂时这个列的值都会为null

type#

表示对某个表所进行查询的访问方法,我们接下来介绍一下该字段在各种情况下的各种值

  • system: 当对应的表只有一条数据,并且该表采用的存储引擎(MYISAM,MEMERORY)对于数据条数的估计是准确的.
  • const: 当对单表查询使用主键或是唯一二级索引时与常数进行等值匹配查询.
  • eq_ref: 当连接查询时,使用被驱动表的主键或是唯一二级索引进行等值匹配查询.
  • ref: 对某表使用普通的二级索引进行等值匹配的时候,可能是ref.
  • fulltext: 使用全文索引查询的时候
  • ref_or_null: 对某表使用普通的二级索引进行等值匹配的时候,并且该值可能为null值时,则为ref_or_null.
    比如:
  • index_merge: 当查询某表使用了索引合并(Intersection,UNION,SORT_UNION)等方式查询时.
  • unique_subquery: 当使用了子查询,并且该子查询被转换为EXSITS的方式,还能够利用主键来帮助查询.
  • index_subquery: 子查询被转换为EXSITS,并且能够利用普通的二级索引来帮助查询.
  • range: 当使用索引来查询某个区间的记录时.
  • index: 当我们可以使用索引覆盖,但还是需要全表扫描时.就比如有个联合索引(part1,part2,part3),而我们查询语句为:
select part1,part2,part3
from table
where part2=10;

虽然这个查询并不能利用该联合索引减少扫描范围,但是查询的列都包含在联合索引内,因为联合索引比聚簇索引所存放的字段少,所以将联合索引的数据页加载到内存的成本也比聚簇索引小,所以这个时候会使用该联合索引进行全表查询.

  • all: 在聚簇索引中全表扫描时

possible_keys和key#

possible_keys字段的值显示的是在该查询语句中对该表的查询可能使用到的索引.
key字段的值显示的是实际上查询使用到的索引

不过要注意,当type为index时,possible_keys的字段为空,但是key字段会显示实际上使用的索引.
并不是possible_keys中的索引越多越好,如果可能使用到的索引越多,则查询优化时,需要计算成本的索引也就越多,也就成本越大.

key_len#

表示在使用该索引进行查询时,该表中记录的该索引的最大字节长度.
该字段计算的组成部分有:

  • 如果索引字段可以为空,则+1字节
  • 如果索引字段是变长数据类型,则+2字节来存储变长列的实际长度.
  • 索引字段的占用字节数

比如我这有一个主键索引sno,数据类型为bigInt(64).
如果使用该索引进行查询,则ken_len值为:64/8=8

64/8是索引占用字节数 因为BIGINT不是变长数据类型,主键索引也不会为空,所以key_len值为8

比如我这有一个普通索引name,数据类型为varchar(64).
如果使用该索引进行查询,则ken_len值为:64*3+2+1=195

64*3是索引占用字节数 2是需要存储实际长度 1是因为该索引可以为NULL值

ref#

表示与查询所使用的索引列进行比较的是什么

  • const: 常数与对应索引列进行比较
  • 数据库名.表名.字段名: 对该表查询的索引使用对应数据库表的字段进行等值比较
  • func: 与对应索引进行等值匹配的是一个函数

rows#

表示查询将要扫描的记录预估条数.

filtered#

表示估计满足条件的数据条数的百分比.
如果该条查询语句使用索引来查询,那么filtered就表示满足除索引外的条件的记录所占预估百分比.

如果该条查询语句使用全表扫描进行查询,那么filtered就表示满足条件的记录所占预估百分比.

我们可以根据rows与filtered进行计算,来预估满足条件的记录条数.这在连接查询的时候比较重要,通过查看驱动表的rows与filtered值,可以计算出这次连接查询的扇出值,预估对被驱动表的查询次数.

Extra#

该字段用于说明一些查询的额外信息,我们可以通过这个字段来了解查询的一些细节.
我们接下来就来介绍下这个字段的各自情况:

  • No tables used: 在select语句中没有from语句
  • impossible where: 当where里的条件永远为False
  • no matching max/min row: 当查询字段中有聚集函数,但是没有满足条件的记录时
  • Using index: 当查询语句使用索引覆盖时
  • Using index condition: 当使用到索引下推时.

    比如我们有个联合索引(part0,part1,part2),而我们的查询语句中条件为:where part0='0' and part2='2'
    这个时候很明显我们的part2条件是不能利用索引的记录的顺序来进行帮助缩减扫描区间的(因为条件不是顺序不间断的,缺失了part1条件).
    但是我们发现part2这个条件在该索引列中,我们就不需要先进行回表,然后判断该索引记录是否满足条件,最后再加入结果集了.
    我们可以直接:在二级索引中扫描到记录->在二级索引中直接判断part2条件->符合则回表将记录加入到结果集,而不符合的话就直接丢弃即可,不需要进行回表.
    这样的话提早在二级索引扫描索引记录的阶段就可以减少一些已在索引中存在列的条件判断,减少了大量的回表次数.

  • Using Where:
    • 当查询该表时使用全表扫描,并且在条件中也有针对该表的条件时
    • 当查询该表时使用索引查询,但是条件中有该表索引之外列的条件.
  • Using join buffer(Block Nested Loop):当连接查询时,无法利用索引来查询被驱动表,mysql就会采取给被驱动表分配一片内存区域来加快查询.

    基于块的嵌套查询,将驱动表的数据存入到buffer中,再扫描被驱动表中的记录,将被驱动表的记录与驱动表的记录进行匹配,符合条件的加入结果集,而不需要每查到一条驱动表的记录就需要再查询一遍被驱动表.

  • Not Exsits: 当外连接查询语句,在where中被驱动表的列有IS NULL条件,并且该被驱动表的该字段有NOT NULL约束.
    就会为Not Exsits
  • Using inresection(...),Using union(...),Using sort_union(...): 当查询该表的时候使用了索引合并时,括号内的值为所合并的索引名.
  • Zero Limit: 当查询语句的Limit子句参数为0时,即不打算读取任何记录.
  • Using filesort: 当查询表的时候进行排序没有利用到索引,需要将对应的记录放在内存或是在磁盘上进行排序时.
  • Using Temporary: 当查询过程中Mysql使用临时表的方式来进行去重或是排序时

在我们使用GROUP BY,Distinct,Union等子句时,如果使用利用不到索引可能就会使用临时表的方式.

  • Start Temporary,End Temporary: 当查询的时候子查询转换为半连接使用Duplicate Weekout执行策略的时,驱动表的Explain记录会显示Start Temporary,被驱动表的Explain记录会显示End Temporary

Duplicate Weekout:将子查询转换为连接查询,将驱动表的查询结果加入到最后的结果集的时,先加入到以驱动表的主键为列的表,如果主键已存在则丢弃记录,否则加入.

  • LooseScan:在将子查询转换为半连接查询的时候采用的是LooseScan执行策略时.

LooseScan: 如果子查询可以利用到索引进行查询,就先执行子查询,将子查询的结果根据索引列进行分组,将子查询分组后的结果集与驱动表进行连接查询,只使用每个分组的第一个记录去进行匹配,如果匹配上了就加入结果集,否则不加入.
FirstMatch(tbl_name): 执行子查询时,采用FirstMatch策略时.

JSON格式的执行计划#

当我们想要查看某个执行计划的成本时,就可以在EXPLAIN 语句上加上FORMAT=JSON来查询.

Extented EXPLAIN#

再使用EXPLAIN查看了某个查询语句的查询计划后,我们可以使用SHOW WARNINGS来查看与这个查询执行计划有关的扩展信息.

第十六章 optimizer trace#

optimize trace可以让我们查看优化器生成执行计划的完整过程,如果我们想要去查看为什么优化器会去选择这样的执行方案,是如何去计算执行计划的成本进行比较的就可以使用这个命令

第十七章 Buffer Pool#

采用InnoDB存储引擎的表,数据是存储在磁盘上的,但是磁盘和CPU之间的速度差距太大,磁盘会拖累CPU的速度,所以这个时候,Buffer Pool就登场了,当InnoDB处理客户端的请求时,会将对应需要处理数据所在的数据页全部读取到Buffer Pool之中,并且在处理完毕后并不急着将该数据页从内存中释放,而是先缓存起来,这样的话当下次再需要这个数据页的数据时,就不需要再去磁盘中进行I/O读取了.

InnoDB的Buffer Pool#

在MySql服务器启动的时,它就会向操作系统申请一片连续的内存空间用于存放缓存数据页.这一片内存空间就被称之为Buffer Pool
innodb_buffer_pool_size该系统变量决定了该Buffer Pool的占用字节空间大小.

Buffer Pool内部组成#

Buffer Pool被分为若干个页面, 页面的大小和InnoDB存储引擎的数据页的大小一致(默认16KB).我们称在Buffer Pool中的数据页为缓冲页
而为了更好的管理这些缓冲页,设计者为每一个缓冲页分配了对应的控制块.控制块中存储了对应缓冲页的控制信息(表空间编号,页号,缓冲页在Buffer Pool的地址,链表节点信息等等).

BufferPool中的组成就类似于这样,控制块与缓冲页一一对应,并且控制块的大小大约为缓冲页的5%

碎片区域:在分配空间时,难免会发生剩下的空间不足够分配一个控制块+缓冲页的大小,这剩下的区域就只能变成碎片区了,我们可以在定义Buffer Pool大小时,先进行计算大小,来减少对应碎片区的大小.

Free链表的管理#

在MySql服务器启动后,会进行Buffer Pool区域的初始化,那么在MySql服务端使用一段时间后,Buffer Pool区域不断的有数据页被缓存。那么我们在想要进行缓存新的数据页的时候,该如何判断哪些缓冲页是还未使用的呢?难道我们要遍历所有的Buffer Pool中的数据页吗?这是不现实的。
我们可以思考一下,可以利用到之前的XDES Entry链表的思想,我们可以将Buffer Pool中的空闲数据页所对应的控制块串联在一起,由各个控制块的free_pre和free_next指针来指向上一个控制块和下一个控制块,形成一个Free链表。
并且我们还可以为Free链表创建一个对应的链表基节点(40字节大小),存储该链表的头结点、尾节点、结点数信息。
当我们要进行数据页缓冲进Buffer Pool时,就从该链表中取出一个空闲的缓冲页使用.将对应控制块填入数据页的信息(表空间、页号等信息),然后将该结点从Free链表中移除,代表该缓冲页已被使用。

缓存页的哈希处理#

我们在获取一个数据页的数据时,会先去Buffer Pool中寻找该数据页是否以被缓存到Buffer Pool,有的话则直接读取缓存中的,那么我们该如何判断一个数据页是否已经被读取到Buffer Pool中呢?
我们可以用表空间+页号唯一区分一个数据页,使用表空间+页号作为key,而缓冲页作为value创建一个哈希表。
用该哈希表来存放当前已被使用的缓存页情况。
当有读取数据页的请求时,先进行判断该数据页是否在哈希表中,有的话直接使用缓冲页即可,没有的话再去free链表上选择一个空闲的缓存页,然后再去磁盘上将对应的数据页缓存到Buffer Pool中即可。

flush链表#

我们有时会对数据库表中的数据进行更新操作,那么在使用Buffer Pool之后,数据更新操作是怎么做的呢?
我们可以先对Buffer Pool中的缓存页进行更新,那么什么时候对磁盘上的对应的数据页进行更新就值得思考了.
如果在修改数据后立即进行更新到磁盘上,那么频繁的磁盘IO会严重拖累程序的性能.
那么如果选择异步更新到磁盘上,我们该如何控制更新的时机?哪些数据页是脏页呢?一次更新到磁盘的数量?
我们需要再新建一个链表来管理这些脏页.这个链表被称为flush链表,它的结构和free链表类似:

LRU链表#

我们在使用Buffer Pool的时,Buffer Pool的空间总有使用完的时候,这个时候我们总要去淘汰掉一些页面.
那么淘汰掉哪些页面比较好呢?
我们使用Buffer Pool时,最注重的一点当然就是缓存命中率,对于我们来说缓存命中率越高,缓存的利用率就越高,需要去磁盘上进行IO的次数也就越少.
所以我们去淘汰页面的方式也就希望使用能够使缓存命中率更高的方式.淘汰使用次数少的缓存页,而保留使用次数多的缓存页.

简单的LRU链表#

根据淘汰最少使用的页面,而倾向于保留使用次数多的页面,我们可以想到什么数据结构?
我们可以使用LRU链表(Least Recently Used 最近最少使用)

  • 当读取数据页到Buffer Pool中或是使用了Buffer Pool中的数据页时,就将对应数据页的节点移动到LRU链表的头部
  • 这样的话使用次数少的节点就会慢慢向LRU链表尾部移动,当Buffer Pool空间不够的时候,我们直接淘汰链表尾部的缓存页即可.

划分区域的LRU链表#

但是单纯使用简单的LRU链表又会出现其他的问题:

  • InnoDB有一个功能,称为预读
    在满足一定条件下InnoDB会异步读取一整个区的数据页到Buffer Pool中.
    • 顺序预读:当顺序访问了某个区超过innodb_read_ahead_threshold系统变量值的页面数,就会将该区的所有页面异步读取到缓存中.
    • 随机预读:当缓存中已存在了某个区13个连续的页面,并且innodb_random_read_ahead系统变量为ON,就会将该区的所有页面异步读取到缓存中.
  • 当查询语句进行全表扫描的时候,读取大量数据页到缓存中.

这俩种情况都可能会导致:

  1. 缓存中出现大量使用率较低的数据页
  2. 这些新的页面将原本的使用率高的页面"挤出去"了,劣币驱除良币.

那么又该如何解决呢?
设计者想到了一个办法,将LRU链表分成俩个区域:

  • young区域:使用频率非常高的缓存页,我们称存放在这的数据叫做热数据
  • old区域:使用频率较低的缓存页,我们称存放在这的数据叫做冷数据


InnoDB根据系统变量innodb_old_blocks_pct来划分old区域在LRU链表中占有的比例.

根据这个划分了old区和young区的LRU链表后,我们就可以更改策略来优化上述的俩个问题了:

  • 根据预读的问题:当读取数据页到缓存的时候,在LRU链表中并不将其直接放到LRU链表首部,而是将其页面放在LRU链表old区域的首部,这样就不会影响到原有使用率较高的页面.如果这些页面后续没有使用也能够逐渐被淘汰.
  • 针对全表扫描的问题:全表扫描操作的特点是一次会扫描多个数据页的记录,但是频率不高,并且对于一个页面里的多条数据的读取速度是快速的(因为该页面已经被读取到了内存上)
    所以设计者提出了一个思想:第一次读取一个old区域的缓存页时,记录下其第一次访问时间,如果后续对这个页面的访问不超过一定时间,则不会将它移动到young区域,否则将它移动到young区域的头部.
    这样的话对于一个数据页的短时间内多次访问(对于一个数据页上的不同数据多次访问也被视为多次访问该数据页),并不会影响到young区域,而间隔一段时间后的再次访问会使该缓存页移动到young区域的头部.
    这个间隔时间通过系统变量innodb_old_blocks_time控制.

更进一步优化LRU链表#

当多次读取缓存中的数据页时,每次都要将其移动到链表首部,这样会不会有些繁琐了?命中率高的数据页可能也不会被Buffer Pool中淘汰,我们能不能让这些命中率高的缓存页尽量减少它移动到首部的次数?
这里设计者提出了一个优化策略:只有young区域的后1/4部分的缓存页再次被访问的时候才会移动到LRU链表的首部,而前面3/4的缓存页被命中后是不会移动到链表头部的.

前面随机预读的策略要求这13个页面位于young区域的前1/4部分.

其他的链表#

除了这些链表以外还有其他的链表来帮助更好的使用缓存页,这里就不多介绍了.

刷新脏页到磁盘#

InnoDB后台有专门的线程用来处理脏页刷新至磁盘的操作
这样不会对用户线程的处理造成影响.

后台线程主要以接下来几种方式将脏页刷入磁盘:

  • BUF_FLUSH_LRU: 从LRU链表的冷数据中刷新一部分页面到磁盘,扫描的页面数量可以通过innodb_lru_scan_depth系统变量来控制.后台线程会定时从LRU链表的尾部扫描一部分页面,如果从中发现脏页(控制块中存储了是否有被修改过的信息)则将它们刷新到磁盘.
  • BUF_FLUSH_LIST: 后台线程定时从flush链表中刷新一部分页面到磁盘,刷新的速率取决于当时系统是否繁忙,并且还有许多的系统变量可以来控制刷新的过程,这些系统变量就不细说了.
  • BUF_FLUSH_SINGLE_PAGE: 当用户线程想要将一个新的磁盘页加载到Buffer Pool之中时,结果Buffer Pool之中并没有空闲的缓存页,那么就会去LRU链表尾部去寻找是否有未修改的可以直接释放的缓存页,如果有则释放掉该页,没有的话就将尾部的一个脏页同步刷新到磁盘,这会拖慢处理用户请求的速度.

系统特别繁忙的时候也会出现用户线程从flush链表中刷新脏页的情况.

多个Buffer Pool实例#

在我们使用Buffer Pool的时候,对其各个链表的处理都需要加锁来进行操作.
但是当我们的数据库规模大了以后,设置了Buffer Pool的大小也非常大,对数据库的并发量高了以后,这个时候对单一的Buffer Pool的并发操作可能会导致请求的处理速度降低.
所以InnoDB支持多个Buffer Pool实例,不同的Buffer Pool实例是独立的,各自的操作不会影响到其他的Buffer Pool实例.
这样能够提高并发处理能力.
我们可以通过innodb_buffer_pool_instances系统变量来修改Buffer Pool实例的个数.

当然,这些实例的大小总和还是由innodb_buffer_pool_size控制的.

innodb_buffer_pool_chunk_size#

在Mysql 5.7.5的版本之前,Mysql是不支持动态更新Buffer Pool的大小的,但在之后的版本提供了在服务运行期间调整大小的功能.
但是并不是直接调整大小,而是提供了一个chunk单位,Buffer Pool的分配以chunk为单位来向操作系统进行申请一片连续的内存空间.
这样的话就避免了随意调整大小出现的Mysql需要向操作系统重新申请一片内存空间,然后再将旧内存空间的内容复制到新内存空间中.
而是可以根据chunk为单位来动态缩减.不需要再将原本的内容复制到新的内存空间中.

即使是同一个Buffer Pool,不同的chunk在内存空间上可能是不连续的.
innodb_buffer_pool_chunk_size:该系统变量可以控制每个chunk的大小(不过这个同样不能在运行时更改)

posted @   况况况  阅读(10)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
历史上的今天:
2021-11-06 2019年上半年下午第六题
点击右上角即可分享
微信分享提示
主题色彩