InnoDB的查询成本计算

light0x00
344 阅读7分钟

对于同一条查询语句,内部可以有多种查询方案供选择

create table my_table(
	id int not null auto_increment,
	key1 int,
	unique key idx_key1(key1)
	)

select * from single_table where key1>10 and key1<10000;

对于以上语句,可以选择使用key1的索引,或全表扫描. 而不同的选择,成本却可能大相径庭,如果使用索引,那么需要考虑区间(1,10000)内记录数量,如果很多,则需要大量回表,由于二级索引并不按id排序(只有在索引列相等时才会按id大小排序),将产生大量随机IO,此时用索引的成本将高于直接全表扫描的成本. 另一方面,如果区间内记录很少,那么使用索引就可以获得更好的性能. mysql优化器所要做的,就是计算不同选择的成本,比较以得到最优解.

成本的定价

对于InnoDB,mysql规定:

  • 加载一个索引页的IO成本默认为1
  • 读取一条记录的CPU成本默认为0.2
  • 一次区间扫描的成本默认为1,无论加载了多少个页

成本的来源

按查询方法的不同,成本的来源也自然不同,对于唯一索引的等值条件(其查询方法为const),成本可忽略不计; 只有在非唯一索引的单点和范围区间扫描时(即ref,range),由于存在“用了索引还不如全表扫描”的情况,所以才有必要去计算使用和不用两种情况的成本,以选取最佳的方案.

全表扫描的成本来源于如下2个阶段:

  1. 从最左叶子页一直扫到最右的IO成本

    成本为叶子页的数量*1

  2. 读取每一条记录并判断是否符合条件的CPU成本

    成本为表记录数量*0.2

使用二级索引的成本来源于如下4个阶段:

  1. 在二级索引中区间扫描时,加载索引页到内存的IO成本

    扫描一次的成本为1

  2. 在二级索引中读取区间内的记录的CPU成本

    成本为区间的记录数量*0.2

  3. 回表时加载索引页到内存的IO成本

    成本为区间的记录数量*1

  4. 回表时在叶子页上读取记录的CPU成本

    成本为区间的记录数量*0.2

    其中前两个阶段就是二级索引做区间扫描的成本,在B+树中,从根结点查到叶结点,再从叶结点开始区间扫描叶子页的记录,自然需要加载索引页到内存,产生IO成本,以及扫描叶子页内的记录并读取出符合条件的记录,产生CPU成本.

    后两个阶段的是在二级索引区间扫描符合条件的记录时,逐个回表的成本,这其中包含从聚簇索引的根节点开始查到叶结点,并扫描叶子页记录产生的IO成本,和读取出记录的CPU成本.

成本的计算

全表扫描的成本 = 叶子页的数量*1 + 记录总数*0.2,其中,”叶子页的数量“和”记录总数“都可通过统计数据中(直接或间接)得到.

mysql为每个表维护了一些统计信息,可通过Show table status like 'my_table'查看

二级索引的成本 = 1+区间记录数量*0.2+区间记录数量*1+区间记录数量*0.2,可见,确定“区间的记录数量”,是衡量二级索引成本的决定性因素,得到区间记录数量的方式有两种: index dive或基于索引统计数据.

使用Index dive确定区间数量

其原意是指,下潜到索引内去计算成本. 因此,这种方式计算成本时,是需要读取索引页的,也就是说计算成本算法本身会带来一定的成本.

其思路为,

  1. 先找出范围的上界和下界,然后计算之间相隔的页的数量 Npage_distance
  2. 然后从上界开始向右读10个页,算得每个页的记录的数量平均值 Nrecord_avg
  3. 最终,区间记录的数量=相隔页的数量*每个页的记录的平均值
                                       +-----------+
                                       |  8     15 |
                     +-----------------+-----------+------------------+
                     |                                                |
               +-----+-----+                                   +------+----+
               |   2 5 8   |                                   |  11 15    |
      +--------+-----+-----+--------+                     +----+-----------+-+
      |              |              |                     |                  |
+-----+-----+  +-----+-----+  +-----+-----+         +-----+-----+      +-----+-----+
|  1   2    |  |   3    5  |  |   6   8   |         |   9    11 |      |   13  15  |
+-----------+  +-----------+  +-----------+         +-----------+      +-----------+

select * from my_table where key1>1 and key1<13

以以上索引为例

  1. 先计算Npage_distance

    1. 对于条件key1>1 and key1<13,上界1所在节点对应的父节点(记为节点A)的键值是2,右边有键值5和8各对应一个页,而下届并在其中,这意味上界和下界之间至少相隔了2个页. 继续向上到达根节点,对应根节点的键值8.
    2. 下界13对应的父节点(记为节点B)中键值是15,左边还有一个11也对应一个页,而下接并不在其中,所以又找了中间相隔的1个页. 继续向上到达根节点,节点B对应15.
    3. 在根节点中,8和15之间并没有键值,所以最终算得区间的上界和下界相隔的页数量为3个(2 + 1)

  2. 下一步就是计算 Nrecord_avg,从最左叶子页向右遍历10个页,注意由于例子中索引的叶子页较少,所以直接从上界扫描到了下界的页,不过明白其中的道理就行. 这样我们可一算的得 Nrecord_avg = 10/5 =2

  3. 最后,Npage_distance * Nrecord_avg = 2 * 3 = 6

当然,你应该看到了,1和13之间实际隔了7个记录,这是因为1的右边还有一个键值2, 因此如果上界不在最右,或下界不在最左,就需要加上同页的记录,最终才能算得准确的区间内记录数量. (前面没说这一点,是因为不想因为这些“细枝末节”把问题搞复杂)

知道了区间内的记录数量,后面就很好办了,

  1. 扫描一个区间的IO成本 1
  2. 读取区间内记录的CPU成本 6*0.2
  3. 回表IO的成本 6*1
  4. 回表读取记录的成本 6*0.2

总计使用二级索引的成本为: 1+60.2+61+6*0.2=9.4

基于统计数据确定区间数量

当扫描区间很多时,比如有1w个单点扫描区间:

select * from my_table where key1 in (1,2,3,4,5,6,7,8,9....10000)

此时如果还使用index dive的话,就需要为每个单点区间执行一次共计一万次index dive,最后将每个单点区间的记录数量加总.由于每次index dive都需要读取少量的页,1万的话成本就很可观了,总不能,还没开始真正查询呢,就在计算成本的过程中耗费大量成本,因此mysql设定了一个系统变量 eq_range_index_dive_limit作为临界值,该值默认为200,如果单点区间数量不超过该值,那么使用index dive,否则使用统计数据来确定区间数量.

mysql 统计了每个表的记录总数,以及每个索引的不重复记录数量

show table status; //Rows为记录总数
show index from my_table; //Cardinality为索引的不重复记录数

键值的平均重复次数=记录总数 / 索引的不重复记录数,在算得平均重复次数后,即可算得 区间记录总数 = 单点区间数量 * 平均记录重复次数

假设二级索引的键值平均重复次数为2,那么1w的单点区间内包含2万个记录,其成本计算步骤如下:

  1. 扫描区间有一万个,所以在二级索引上的IO成本就是10000
  2. 1万个单点区间内包含2万条记录,读取成本为 20000*0.2
  3. 2万条记录回表的IO成本为: 20000*1
  4. 2万条记录回表的CPU成本为: 20000*0.2

Join的成本

select * from a inner join b on a.bid=b.id;
select * from a left/right outer join b on a.bid=b.id;

join分为内连接,外连接,内外连接有两点区别,一是驱动表的不同,内连接的驱动表由优化器根据成本计算结果而选择,外连接则显式指定了驱动表,对于a left join b,驱动表为a,对于a right join b,则驱动表为b; 二是当驱动表的扇出(fanout)记录在被驱动表中没有对应记录时,也即是on a.bid=b.id不满足时,那么是保留驱动表的记录在最终结果集里(被驱动表的字段值填NULL),还是排除.

以如下表数据为例

student

stu_nonamegender
001Jackmale
002Rosefemale
003Alicefemale
004Bobmale

score

stu_nosubjectscore
001C++90
002C++95
001MySql85
002MySql80
004MySql90
select * from student left join score on student.stu_no=score.stu_no where student.gender='male' and score.subject='MySql';

Join查询可以分解为若干条单表查询,对于以上join语句,其执行过程如下:

  1. 先找出和驱动表student相关的条件student.gender='male',从student表中筛选出2条记录(称为扇出),这一步骤相当于执行了 
    select * from student where student.gender='male'
  2. 遍历每一条扇出记录,
    1. 第一条扇出记录的stu_no是001,于是变量条件on student.stu_no=score.stu_no就可变为常量条件on score.stu_no=001,此时在score表中的查询就相当于:
    select * from score where score.stu_no=001 and score.subject='MySql'
    1. 第二条扇出记录的stu_no是004,于是变量条件on student.stu_no=score.stu_no就可变为常量条件on score.stu_no=002,此时在score表中的查询就相当于:
    select * from score where score.stu_no=004 and score.subject='MySql'

以上过程中,在驱动表执行了一次单表查询,在被驱动表执行了多次(取决于扇出记录的数量)单表查询,这一过程被称为Nested-Loop Join.

最终可得出结论: JOIN查询的成本 = 驱动表查询成本+扇出记录数量*被驱动表查询成本.

JOIN查询中,在驱动表和被驱动表的查询都是单表查询,成本如何计算已在前文详细分析过,这里不再赘述.

对于多个表Join的情况,比如A left join B left join C, 那么现以A为驱动表,得到第一次的查询扇出,然后每一条扇出都在B执行一次查询,查询结果作为第二次查询的扇出,然后再将第二次的每一条扇出都在C执行一次查询,以得到最终的结果.

更多的表JOIN也同理.

参考文献

  • 《MySql是怎样运行的》
avatar
文章
阅读
粉丝