Innodb 单表索引查询和连接查询效率分析

一、MySQL查询访问方法

mysql执行查询语句的方式叫做访问方法或访问类型，这些访问类型具体为 const、ref、range、index、all等。

同一个查询语句可以使用多种不同的访问方法来执行，虽然最后的查询结果都是一样的，但是花费的时间成本可能差距甚大。

 

下面对每种访问类型一一说明，假设有一个表如下图：

在介绍每种访问类型时需要用到B+树图示，下面所有的B+树都是简化后的，只展示了叶子节点的图。

 

  • const

通过主键或者唯一二级索引列与常数值进行等值比较来定位一条记录的访问方式就是const。

凡是单点查询（x = 'xxx'）只命中一条记录才算是const类型。

使用const访问类型，MySQL 会直接在聚簇索引中利用主键值定位对应的用户记录。

 

例1：通过主键定位一条记录

SELECT * from single_table WHERE id = 1438;

 

例2：通过非主键的唯一键定位一条记录

SELECT * from single_table WHERE key2= 3841;

通过唯一键key2定位一条记录，需要一次回表，比主键慢一点点，但也很快。

const 访问类型之所以（比ref访问类型）快是因为主键和唯一键的单值查询只会命中1条记录，最多只会发生1次回表，而二级索引的单值查询可能会命中多条，从而引发多次回表。

如果主键或者唯一二 索引的索引列由多个列构成，则只有在索引列中的每 个列都与常数都进行等值比较时才算使用了 const访问方法。

对于唯一二级索引列来说 在查询列为 NULL 值时，不算是 const 而算是 ref，因为它可能命中多条null的记录。

SELECT * from single_table WHERE key2 is null;

 

  • ref

通过非唯一二级索引列与常数值进行等值（单点查询）比较来定位记录的访问方式就是ref。

例3：通过普通索引key1定位一个单值

SELECT * from single_table WHERE key1 = 'abc';

注意：

  1. 采用二级索引来执行查询时， 其实每获取到一条二级索引记录就会立刻对其进行回表操作，而不是将所有二级索引记录的主键值都收集起来后再统一执行回表操作。而且就算是收集起来再回表也不是总共只回一次表，依然是有几个id就回几次表。

    ref 比 const 的性能差在可能需要多次回表。

  2. 二级索引列允许存储 NULL 值时，无论是普通的二级索引 ，还是唯一二级索引 ，在执行 "key IS NULL" 形式的搜索 时，最多只能使用 ref 访问方法 而不能使用 const 访问方法。

  3. 对于联合索引而言，只要满足最左前缀的单值查询才算是 ref 类型的查询。例如：

select * from single_table where key_part1='god like';
select * from single_table where key_part1='god like' and key_part2='legend';
select * from single_table where key_part1='god like' and key_part2='legend' and key_part3='penta kill';

和ref 类似的还有一种 ref_or_null 的访问类型，表示单点查询不仅要找到某个常数值，还要找到null。

 

例4：

SELECT * from single_table WHERE key1 = 'abc' or key1 is null;

值为 NULL 的记录会被放在索引最边的页。

 

  • range

使用索引执行查询时， 扫描区间为若干个单点扫描区间 或者 范围扫描区间 的访问方法称为 range。

例5：range查询

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

扫描区间 就是 [1438， 1438] 、[6328， 6328] 和 [38， 79]；

 

  • index

如果一个范围（非单点）查询的where没命中索引，而且无需回表（这要求搜索的列全在索引列范围内），那么就是index访问方式。这种方式又叫做覆盖索引。

例6：覆盖索引

SELECT key_part1, key_part2, key_part3, id from single_table 
WHERE key_part2 = ' abc';

由于 where 条件不遵循最左前缀原则，因此 where key_part2 = 'abc' 并没有用到索引。但由于select 的字段全部都是idx_key_part这个索引所涵盖的字段，因此算用到了覆盖索引。

另外当通过全表扫描查询时 如果添加 order by id 语句，那么该语句在执行时也会被 认定为使用的是 index 访问方法。

 

*问题1：为什么同样是扫描全部记录，但二级索引的index访问方式的性能比 all 访问方式性能高？

因为二级索引的叶子节点只存了索引字段，没有存记录上的其他列，这意味着二级索引的叶子节点能存储的记录数量比主键索引的叶子节点多。

换句话说，相同记录数下，一个表的二级索引的叶子节点页数肯定比主键索引的叶子节点页数少很多。

所以二级索引扫描全部记录 执行的磁盘IO次数 比 在主键索引扫描花的IO次数少。

 

*问题2：为什么有时候优化器选择全表扫描也不用二级索引+回表？

当搜索得到的结果范围很大的情况下，从二级索引叶子节点查到的每一个主键值都要回表，此时优化器宁愿全表扫描也不会使用索引+回表的查询方式。

后者会有2个坏处：

1、一次回表会在主键索引（从根节点到叶子节点的过程）发生多次磁盘IO。

2、这些磁盘IO都是随机IO。

全表扫描则是直接从叶子节点沿着链表指针往后遍历，不用每次都从根节点到叶子节点，相比于回表节省了多次IO次数，而且沿着链表指针的磁盘IO会有很多次顺序IO（也会有随机IO），速度会比回表的随机IO快。

 

  • all

对主键索引全表扫描的访问方式。

注意：为了避免二级索引回表发生多次随机IO，mysql提出 MRR （多范围读取）的优化措施，该优化的做法是先读取一部分二级索引记录，将其主键排好序进行统一回表，这样可以节省一些IO次数（原理是利用相邻的ID可能位于同一个页中，这样一来多个ID统一回表就不会发生重复的磁盘IO）。

 

二、索引合并

如果一个sql的where条件涉及2个独立的普通索引字段，mysql一般只会使用一个索引。但使用 索引合并 技术可以同时使用多个索引B+树。

  • intersection 索引合并

intersection索引合并是指如果where条件涉及多个索引，并且多个条件间的关系为 AND，此时可以同时根据这些条件查询多个索引B+树，并对得到的id取交集回表。

例7：intersection索引合并使用多个索引

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

该SQL的查询方案有4种：

1. 使用 key1 索引；

2. 使用key3索引；

3. 全表扫描；

4. 同时使用key1 和 key3 索引（索引合并）。

 

使用索引合并的情况下，innodb会在 key1索引 的B+树查找 ['a', 'a']区间的记录对应的id，也会在 key3索引 的B+树查找 ['b', 'b']区间的记录对应的id。

再将这个结果集的id求交集得到同时满足 key1='a' 和 key3='b'的id，再根据这些id回表。

 

使用索引合并有1个条件，它要求从二级索引获取到的二级索引记录都是按照主键记录排好序的，这意味着两个索引字段只能单点查询不能范围查询。

这主要出于2方面考虑：

1、从两个有序集合取交集比从两个无需集合取交集容易的多（假设两个集合长度是m和n，如果两个集合都是有序的，那么取交集的复杂度为O(n+m)，如果是无序的则复杂度为O((m+n)logm) 或 O((m+n)logn) 或 O(mlogm + nlogn)））

这里顺便说一下取交集算法。

有序集合取交集：

使用双指针i，j，比较 arr1[i] 和 arr2[j]。

如果 arr1[i] > arr2[j]，则 arr2[j]不可能是结果集的元素，所以指针往后移，j++;

如果 arr1[i] < arr2[j]，则 arr1[i]不可能是结果集的元素，i++;

如果 arr1[i] == arr2[j]，则arr1[i]和arr2[j]就是结果集的元素，i++ ，j++；

 

复杂度为O(n+m)。

 

无序集合取交集：

如果对两个集合分别排序再求交集，则复杂度为 O(mlogm + nlogn);

如果对集合m排序，再遍历集合n，让n的每个元素对集合m二分查找，则复杂度为 O((m+n)logm);

如果对集合n排序，再遍历集合m，让m的每个元素对集合n二分查找，则复杂度为 O((m+n)logn);

因此无序集合取交集用那种方式取决于 集合m和集合n的长度谁长。

 

2、如果从二级索引获得的回表id是有序的，那么回表操作就不是单纯的随机IO，而可能包含顺序IO从而提高效率。

例8：不符合intersection索引合并的SQL

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

这个例子不符合使用索引合并的条件，因为 key1 > 'a' 的情况下不能保证记录中的id是有序的。

SELECT * FROM single_table where key1 > ' a ' AND key_part1 = ' b';

这个例子也不符合使用索引合并的条件。因为key_part1 是联合索引，所以 key_part1 = ' b' 的情况下不能保证记录中的id是有序的（因为联合索引 先根据key_part1排序，再根据key_part2排，再根据key_part3排，最后才是根据id排。key_part1 = ’b'的情况下， key_part2 的值可能是多个不同的值，key_part2有不同值的情况下，id字段就可能是无序的）。

使用索引合并的优点是可以通过求交集减少满足where条件的id的个数，从而较少回表次数，即减少在主键索引发生的磁盘IO次数。

缺点是需要在多个二级索引的B+树中查找，会增加在二级索引发生的磁盘IO次数。

是否使用索引合并需要衡量 减少的回表id的个数所减少的IO次数 带来的优化是否比得上 在二级索引增加的IO次数 带来的损耗。

所以如果两个集合求交集后的结果集没有比原集合少多少，那么索引合并的性能就会比较差。

 

  • Union索引合并

union索引合并是指如果where条件涉及多个索引，并且多个条件间的关系为 OR，此时可以同时根据这些条件查询多个索引B+树，并对得到的id取并集回表。

例9：union索引合并使用多个索引

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

此时单独用key1 或 key3 的任何一个索引都无法得到完整的目标记录，因为 key1 不等于 'a'的记录的 key3 也可能等于 'b'。

此时可以用全表扫描 或者 union索引合并这两种查询方案。

union 索引合并是同时使用 key1 和 key3 这两个索引得到两个id集合，再将两个id集合求并集，为结果集的这些id回表。

使用Union索引合并好处是通过求并集在二级索引回表前缩小搜索范围，减少了在主键索引的IO次数；坏处是要在每个索引发生多次随机IO。

衡量是否使用Union索引合并取决于在二级索引得到多个id集合后，求id集合的并集得到的结果集能缩小多少搜索范围。

 

例10：同时使用intersection索引合并和union索引合并

SELECT * FROH single_table WHERE 
(key_part1 = 'a'  AND key_part2 = 'b'  AND key_part3 = 'c') 
OR (key1 = ' a' AND key3 = 'b');

该例子可以通过 key1 和 key3 执行 intersection索引合并，得到的合并结果再与 key_part 这个联合索引执行Union索引合并。

Union索引合并的使用要求和Intersection索引合并一样，各个索引中扫描到的主键值得是有序的（即只能单点查询）。

 

  • Sort-Union索引合并

Union索引合并要求各个索引使用单点查询才能触发Union索引合并，但这个条件未免太苛刻。因此提出了Sort-Union索引合并，它可以允许各个索引使用范围查询的时候就触发union索引合并。

例11：sort-union索引合并

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

该例子不满足使用 Union 索引合并的条件，但可以使用Sort-Union索引合并来避免全表扫描。

Sort-Union索引合并过程如下

在 key1 索引中找到满足 key1<'a'的二级索引记录，将获得的id排序；

在 key3 索引中找到满足 key3>'z'的二级索引记录，将获得的id排序；

将在二级索引得到的两个主键集合求并集，再对结果集id进行回表。

Sort-Union的目的也是为了减少在主键索引的搜索范围。

 

  • 索引合并的使用场景

Union和Sort-Union适用于“从单个二级索引获取的记录数较少的场景”，即 key1 <'a' 和 key2 > 'z' 在各自的二级索引中命中的id数较少。

Intersection索引合并适用于“仅从单个二级索引获取的记录数太多，导致回表次数太多，因此需要使用多个二级索引”的场景。

注意：Mysql没有实现 Sort-Intersecton 索引合并，但是MariaDB却实现了Sort-Intersecton 。

凡是使用了索引合并的sql，其访问方式为：index_merge。

 

三、连接查询

  • 连接查询过程

我们先通过一个例子开始探究连接查询是怎么进行的。

如下所示有两个表：表t1和t2都没有建立任何索引。

 

现在我们执行一条关联查询：

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

查询过程如下：

  1. 确认第一个要查询的表，第一个要查询的表称为驱动表，假设以 t1 为驱动表。在驱动表中查询满足 m1 > 1的记录（有m1=2和m1=3这2条）；

  2. 根据从驱动表获取的每条记录，一次次的到 t2 表匹配，所谓的匹配是指符合t2搜索条件的匹配。t2就是被驱动表。由于 t1 得到 m1=2和m1=3这2条记录，因此需要在t2发生两次查询：

    第一次查询 t2.m2 = 2 and t2.n2 <'d'，第二次查询 t2.m2 = 3 and t2.n2 <'d'。

    而且每次在被驱动表t2发生的查询都是全表查询（因为没建立索引），即t2发生了2次全表扫描。

因此我们得到的结论是：驱动表只需查询1次，而被驱动表需要根据从驱动表筛选出来的记录进行多次查询。

注意：并不是先将满足 t1 条件的驱动表记录先收集起来再去被驱动表中查，而是每获得一条驱动表记录就立即到被驱动表查询并将查询到的结果记录立刻发送客户端，然后重复。这样做是为了节省内存（如果满足条件的驱动表记录很多，把他们收集起来就会浪费一大片内存空间）。

 

  • 内连接和外连接

连接查询可分为内连接和外连接，对于内连接的两个表，若驱动表中的记录在被驱动表找不到匹配的记录，则这些驱动表的记录 不会加入到最后的结果集。前面提到的连接都是内连接。

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

 

外连接可以细分为2种：

左外连接（left join）选取left join左侧的表为驱动表。

右外连接（right join）选取right join右侧的表为驱动表。

 

  • 区分过滤条件

连接查询有两种不同性质的过滤条件。

WHERE 子句中的过滤条件：

不论是内连接还是外连接 凡是不符 合 WHERE 子句中过滤条件的记录都不会被加入到最后的结果集。

 

ON 子句中的过滤条件：

对于外连接的驱动表中的记录来说，如果无法在被驱动表中找到匹配 ON 子句 中过滤条件的记录 那么该驱动表记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL 值填充。

 

ON 子句是专门为"外连接驱动表中的记录在被驱动表找不到匹配记录时 是否应该把该驱动表记录也加入结果集中"这个场景提出的。

所以，如果把 ON 子句放到内连接中， MySQL 会把它像 WHERE 子句一样对待。也就是说 内连接中的 ON 子句 和 WHERE 子句是等效的，在外连接中则是不等效的。

 

举个例子：

student 表只有一个主键索引 number，score有一个联合主键索引 number,subject。

 

执行

SELECT * FROM student, score WHERE student.number = score.number;

结果少了王五的记录，因为这是内连接。

 

  • 连接查询的原理

为什么有的连接查询快如闪电，有的慢如蜗牛。其实连接查询无非是以下3种套路。

  1. 嵌套循环连接

上面的例子t1和t2表的查询就是嵌套循环连接，也是最简单的连接方式。如果涉及到3个表的连接，则先把第1个表作为驱动表，第二个表作为被驱动表得到查询结果，再把第1、2个表连接查询的结果作为驱动表，第三个表作为被驱动表。

它的逻辑如下：

for each row in t1 satisfying conditions about t1{ 
  for each row in t2 satisfying condtions about t2 { 
    for each row in t3 satisfying conditions about t3 ( 
      send to client;
    }
  }
}

 

2. 使用索引加快连接速度

这其实是一种使用了索引的嵌套循环连接。不用索引，则每次在被驱动表的查询就是全表扫描。用了索引，每次在被驱动表的查询都会走索引。

还是之前的例子（t1作为驱动表）：

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

 

假设 t1.m1 > 1的记录有2条，那么该连接查询相当于对 t2 进行2次查询：

SELECT * from t2 WHERE t2. m2 =2  AND  t2.n2 < 'd';

SELECT * from t2 WHERE t2. m2 =3  AND  t2.n2 < 'd';

此时有2种加索引的方式：

a. 给 t2.m2 加索引，那么每次对t2的每次查询都是ref访问方式的查询（如果 m2 是t2的主键或者不允许存NULL的唯一键，那么这种访问方式在连接查询叫做 eq_ref）而不是全表扫描。

b. 给 t2.n2 加索引，那么每次对t2的每次查询都是range访问方式的查询而不是全表扫描。

 

3. 基于块的嵌套循环连接

该连接方式是对没有用到索引的嵌套循环连接的优化，减少重复磁盘IO。我们考虑一下下面的sql涉及到多少次IO：

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

假设 满足 m1 > 1有100个记录，t2的主键索引的叶子节点有1000个页，且t1和t2表没有任何索引。

因此需要 t2 发生100次全表扫描，每次全表扫描会发生1000次磁盘IO（这里不考虑 buffer pool的缓存。就算考虑缓存，buffer pool 也没办法把所有页都缓存，所以每次扫描到 t2 后面的页，t2前面的页可能就失效了，等到第二次扫描 t2的时候，又要从磁盘读取），那么总共会发生 100 * 1000次磁盘IO。

 

为了减少重复的磁盘IO，我们可以将驱动表的查询结果放到一个叫做 Join Buffer（连接缓冲区）的内存空间缓存起来，只在t2发生一轮全表扫描，就对 join buffer 中的所有记录的连接字段进行比较。

 

例如 满足 t1.m1 > 1 有100个记录，join buffer只能缓存 50条，需要进行两次t2的全表扫描，第一次从t2全表扫描过程中，t2的第一个叶子页读到内存，将 join buffer 中的 50个 m1字段和第一个叶子节点的m2字段一个个比对（这里用不了二分查找，因为m2不是索引字段），再将 50 个 m1 和第二个叶子页的m2一个个比对，依次类推。

这样只需要发生 2*1000 = 2000次磁盘IO。

加入了 join Buffer 的嵌套循环 连接算法称为基于块的嵌套循环连接 (Block Nested-Loop join）算法。

join buffer默认大小 256K，用到了索引的连接查询是不会用到 join buffer 的。

join buffer中并不会存放驱动表记录的所有列，只有查询列表(select 的字段)中t1的列 和where条件中t1的列才会被放到 Join Buffer 中。这也再次提醒我们，最好不要把*作为查询 列表，这样可以让join buffer 放更多记录。

 

三、计算成本

  • 什么是执行sql的成本

MySQL在执行一个查询时可以有不同的执行方案，mysql的优化器会选择其中成本最低的那种方案执行。

那么“成本”具体指什么？包括2方面：

IO 成本：把sql涉及的所有页从磁盘到内存的加载过程花费的时间称为 IO 成本，包括IO次数和每IO的时间（即随机IO还是顺序IO，后者的每次IO比前者快很多）。

CPU成本： 页读取到内存后，从页中（包括叶子页和非叶子页）筛选记录（页内的二分查找或直接遍历）、搜索条件比对、排序和分组等操作损耗的时间称为 CPU 成本。CPU成本是发生在内存中的。

设计 MySQL 的大叔规定读取一个页面花费的成本默认是1.0；内存中读取以及检测一条记录是否符合搜索条件的成 本默认是 0.2。1 和 0.2 这种数字叫做成本常数。成本常数可以通过修改mysql配置来修改。

优化器计算sql成本的时候只会粗略的计算。可以简单的认为：

IO成本 = 本次sql读取的总页数 * 1  
CPU成本 = 本次sql读取到内存的所有叶子页的记录数总和 * 0.2。

 

  • 计算成本的过程

例12：计算SQL成本

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

假设 key1和key3是普通索引，key2是唯一索引，key_part1是联合索引的第一个列，common_field不是索引。

mysql的优化器会按照如下步骤计算执行成本。

1、根据搜索条件，找出所有可能使用的索引（也就是 explain 命令执行结果中的 possible keys列）。

本例子中 key1 和 key2是可能用到的索引。

key3 > key2 这个搜索条件的索引列由于没有与常数进 比较 因此不能产生合适的扫 锚区间，不能用到key3索引。

key_part1 的like不是以字符串开头的通配符匹配，用不到索引。

2、计算全表扫描的代价

全表扫描查询成本 = IO 成本 + CPU 成本 = 磁盘IO读取的页数 * 1.0 + 扫描的记录数 * 0.2

假设 single_table 的总页数为 97 页，9693条记录，那么全表扫描查询成本为：

（97 * 1.0  + 1.1） + （9693*0.2 + 1.0）= 2037.7

这里多出来的 1.1 和 1.0是微调值，可以不用理会。

上图所示，我们可以用 show table status like '表名' 得知表的总字节数（Data_length)和行数(Rows)。

页数 = Data_length / 1024 / 16 = 97页。

对于Innodb而言，Data_length 是聚簇索引占的存储空间，对于MyISAM，Data_length 是数据文件（MYD）的大小。

注意：全表扫描的页数其实只读取了所有叶子页和从根节点到最左侧叶子页之间的几个非叶子页。但设计 MySQL 的大叔在计算全表扫描成本时直接使用聚簇索引占用 的所有页数作为计算 IO 成本的依据。

 

3. 计算使用不同索引执行查询的成本

key1 和 key2是可能用到的索引。Mysql会分析单独使用key1和key2索引的成本以及使用索引合并的成本。

使用索引查询的总成本分为在二级索引上的成本（扫描区间的个数（IO成本） 和 回表记录数（CPU成本）） + 在聚簇索引上的成本（回表次数（IO成本） 和 叶子节点扫描的记录数（CPU成本））。

无论一个扫描区间在B+树上占用了多少个页，查询优化器粗暴地认为读取索引的 一个扫描区间的 IO 成本与读取一个页面的IO 成本是相同的。

另外mysql 在评估回表操作的 IO成本时很豪放：他们认为每次回表操作都相当于访问一个页面的耗时。

 

综上：

二级索引上的成本 = 扫描区间的个数 + 回表记录数 * 0.2

聚簇索引上的成本 = 回表记录数（一条记录就需要回表1次） + 扫描主键索引叶子节点页的记录数 *0.2

其中 扫描主键索引叶子节点页的记录数 可近似看做 回表记录数（但实际上前者肯定大于等于后者）。所以：

聚簇索引上的成本 = 回表记录数（一条记录就需要回表1次） + 回表记录数 *0.2

 

下面我们具体看一下使用key1 和 key2 的SQL成本如何计算：

a. 计算用唯一索引key2的SQL成本

key2条件是 key2 > 10 and key2 < 1000;

 

在二级索引的成本

key2 > 10 and key2 < 1000 这个条件只有一个区间：(10, 1000)，所以在二级索引的IO成本 = 1.0

回表记录数需要计算：平均一个叶子页有多少记录数 n，以及 key2 = 10 到key2=1000之间有多少个页 p，n * p就是回表记录数。为了计算 n 和 p，就需要真的从key2索引的B+树读取页了。

简单来说，计算两点之间的记录数，需要在二级索引从根节点往下找到区间左边界（10）的叶子节点以及它对应的记录，以及从根节点往下找到区间右边界（1000）的叶子节点以及它对应的记录（在二级索引定位一条记录的时间可以忽略不计），我们简称为b记录和c记录，他们所在的页叫做页b和页c。

如果b和c记录在同一个页中，则可以直接计算出b~c间的记录数，也就是需要回表的记录数。

如果b和c记录不在同一个页中，需要沿着 b记录向右读10个页从而计算出每个页的平均记录数 n（接近于顺序IO，因此速度很快）；

另外要从b记录和c记录所对应的（父节点）目录页a得到页b和页c之间的页数 p（而不用真的沿着叶子节点从页b读到页c来计算p）。

 

回表的记录数 = n * p。

需要知道，为了计算成本，mysql可能会真的到B+树读取页，但会把这个损耗控制到可以忽略的范围。

假设这里得到回表记录数是95，则CPU成本 = 95*0.2 = 19。

所以在二级索引的成本为 19+1=20；

 

在主键索引的成本

IO成本 = 回表记录数 = 95

CPU成本 = (10, 1000)之间的记录数 * 0.2 = 95 * 0.2 = 19

所以在主键索引的成本为 19+95=114；

综上，总成本 = 20 + 114 = 134。

 

b. 计算用普通索引key1的SQL成本

key1条件是 key1 in ('a', 'b', 'c')；是3个单点区间。

在二级索引的成本

扫描区间的数量为3个区间，需要回表的记录数假设为 35条a + 44条b + 39条c = 118。

在二级索引付出成本 = 3 + 118 * 0.2 = 26.6

 

在主键索引的成本

在主键索引付出成本 = 118 + 118 * 0.2 = 141.6

综上，总成本 = 26.6 + 141.6= 168.2。

 

问题：上例中是否可能使用索引合并（Index Merge）

由于是范围查询，不满足使用 Intersection 合并的条件，所以并不会使用索引合并。

 

  • index dive

在评估二级索引 + 回表方式的成本时，mysql直接访问索引对应的 B+ 树来计算某个扫描区间内对应的索引记录条数（回表记录数）的方式称为 index dive。

index dive 的具体过程是在二级索引中分别从根节点往下找到区间的最左边记录和最右边记录（例如 一个区间是 ['a', 'a']，那么需要找到a的最左记录 和 a的最右记录），然后再计算这两条记录之间的记录数，得到的结果就是回表记录数。

评估成本时所发生的index dive次数取决于区间的个数，例如 key2 > 10 and key2 <1000，只有一个区间，因此只会发生1次index dive。对于 key2 in ('a', 'b', 'c')，产生了3个区间：[a, a],[b, b],[c, c]，会发生3次 index dive。

索引条件中的扫描区间个数越多，优化器在评估成本时发生的IO次数越多。有零星几个单点扫描区间的话，损耗可以忽略不记。

但是很多人写sql的时候会在 in 子句塞很多值，如果in中有20000个值，就会发生20000次 index dive，这个成本甚至比直接扫描全表的成本大，而且这是在计算成本的阶段就已经有这么大的成本了，还不是在真正执行的阶段。

 

为了避免这个问题，mysql规定如果一个sql语句的条件区间超过系统变量 eq_range_index _ div_limit 规定的值（mysql 5.7 之前是10个区间，mysql 5.7及之后是200）就不进行index dive。而是直接根据系统表中的索引统计数据来计算成本，索引统计数据可以通过 “ SHOW INDEX FROM 表名 ”查询得到。

 

索引统计数据中有一个属性是索引的基数，也就是不重复值的数量，用表的行数 rows 除以基数 c 可以得到该字段的一个值平均会重复多少次。

in中的参数个数 * rows / c = 需要回表的记录数

使用这种评估SQL成本的方式，好处是不用访问B+树，节省了评估SQL成本所带来的的耗时，坏处是计算出来的成本误差可能很大。

 

四、连接查询的成本

由前面内容我们知道，连接查询本质上是用驱动表的条件对驱动表查询一次得到结果集A，再根据结果A在被驱动表查询多次的过程（嵌套循环连接）。

因此连接查询成本由两部分成：

单次查询驱动表的成本；

多次查询被驱动表的成本；

 

我们把查询驱动表后得到的记录条数称为驱动表的扇出，扇出值越小，被驱动表的查询次数也就越少。

连接查询总成本 = 单次访问驱动表的SQL成本 + 驱动表扇出值 × 单次访问被驱动表的成本。

其中 单次访问表的成本 在前面已经介绍过了，所以关键在于如何计算驱动表扇出值。

扇出值的计算有时候很容易，有时候很难

例如计算下面语句的扇出值：

SELECT * FROM s1 INNER JOIN s2 WHERE 
s1.key2 >10 AND 
s1.key2 < 1000;

那么满足 key2 > 10 且 key2 < 1000的记录数就是扇出值，而这个记录数可以在二级索引key2的B+树通过 index dive 操作得出。

又例如：

SELECT * FROM s1 INNER JOIN s2 WHERE 
s1.key2 >10 AND s1.key2 < 1000
AND s1.common_field = 'xyz';

你无法得出  key2 > 10 且 key2 < 1000 下，common_field = 'xyz'的记录数有多少，因为common_field在主键索引的叶子节点中，你需要遍历主键索引B+树的叶子节点才能知道答案，但在计算成本的阶段这么干未免小题大做，消耗过大了。

此时mysql需要更复杂的机制做大致的预估，这个预估过程我们不再详细介绍。

 

  • 多表连接的成本穷举分析

对于内连接，由于驱动表是不固定的，不同的表作为驱动表，查询成本也不同，也就是说优化器还需要考虑最优的表连接顺序。

所以优化器会计算出所有不同表作为驱动表的成本情况，这相当于排列组合，如果对4个表进行连接，会有 4*3*2*1种成本。

假设连接的排列组合顺序有很多，为了避免计算成本花的时间太长，mysql是不会无休止的计算下去，而是有一个连接数阈值，如果一个sql的连接表数超过了这个阈值，mysql只会对数量等于该阈值的表进行穷举分析。

 

此外，mysql还会在穷举过程中记录当前最小成本，例如如果ABC这种连接顺序下，它的成本是穷举到目前为止最小的成本：10。在计算下一种情况BCA的成本时，如果发现B和C的连接成本已经大于10，就不会再计算 result(BC) 与 A 的连接成本了。

posted on 2022-10-19 10:44  一只阿木木  阅读(484)  评论(0)  编辑  收藏  举报

Powered by: 博客园 Copyright © 2024 一只阿木木
Powered by .NET 9.0 on Kubernetes