likes
comments
collection
share

神奇的 SQL，你们的知识盲区：WHERE条件的提取与应用SQL 语句中的 WHERE 条件，最终都会被提取到 Inde

作者站长头像
站长
· 阅读数 60

开心一刻

某天，一个粉丝咨询了我一个问题粉丝：为什么中国人结婚非要选一个好日子呢 ？我：嗯 ？ 那肯定啊，结完婚之后你还能有好日子吗 ？ 粉丝：那结婚时所说的白头到老是真的吗 ？ 我：这哪能是真的，你看现在，头发还没白就秃了 粉丝：那女生的公主病是怎么回事 ？ 我：原因很简单，不是长得丑就是穷 粉丝：那又漂亮又有钱的呢 ? 我：别逗了，那不是公主病，那是真公主 ！ 粉丝：那你的是有公主病，还是真公主 ？ 我：别闹了，我的在硬盘里

神奇的 SQL，你们的知识盲区：WHERE条件的提取与应用SQL 语句中的 WHERE 条件，最终都会被提取到 Inde

基础回顾

正式开讲之前，我们先来回顾一些基础知识

  1. SQL 执行流程

    这可是八股文，我相信你们都会

    神奇的 SQL，你们的知识盲区：WHERE条件的提取与应用SQL 语句中的 WHERE 条件，最终都会被提取到 Inde

    这是 MySQL 数据库中 SQL 的执行流程，其他数据库应该类似

    MySQL 8 取消了查询缓存！

  2. 数据组织

    关系型数据库中，数据组织涉及到两个最基本的结构：表与索引

    表中存储的是完整数据记录，分为堆表和聚簇索引表；堆表中所有的记录无序存储，聚簇索引表中所有的记录则是按照记录主键进行排序存储；索引中存储的是完整记录的一个子集，用于加速记录的查询速度，索引的组织形式，一般均为 B+ 树结构；MySQL 的 InnoDB 采用的是聚簇索引表，数据记录和索引是一起存储的，类似如下

    神奇的 SQL，你们的知识盲区：WHERE条件的提取与应用SQL 语句中的 WHERE 条件，最终都会被提取到 Inde

    InnoDB 二级索引（非聚簇索引）的结构与聚集索引的结构基本相同，只是叶子节点有些许差别，二级索引的叶子节点存的是 索引值 + 主键值，而聚簇索引的叶子节点存的是 索引值 + 完整的数据记录，所以通过二级索引查找的过程是先找到该索引值对应的聚集索引的值，然后再通过该聚簇索引值到聚簇索引树上查找对应的完整数据记录，这个过程称为 回表！当然也有不需要回表的情况，这里就不展开了

    Oracle、DB2、PostgreSQL，MySQL 的 MyISAM 引擎，采用的是堆表形式来存储数据，索引和数据是分开存储的，类似如下

    神奇的 SQL，你们的知识盲区：WHERE条件的提取与应用SQL 语句中的 WHERE 条件，最终都会被提取到 Inde

    堆表结构中的聚簇索引和二级索引基本就没什么区别了，可以简单的认为聚簇索引的结构和二级索引中的唯一索引的结构是一样的

其实表结构采用何种形式并不重要，因为下面讲的内容在任何表结构中均适用

WHERE 条件提取

对该标题，你们是不是有很多疑问

  1. 提取什么
  2. 为什么要提取
  3. 提取到哪里去

总结下就是：MySQL 如何解析 WHERE 条件

我们还是结合案例来看，建表 tbl_test 并初始化数据

create table tbl_test (a int primary key, b int, c int, d int, e varchar(50));
create index idx_bcd on tbl_test(b, c, d);
insert into tbl_test values (4,3,1,1,'a');
insert into tbl_test values (1,1,1,2,'d');
insert into tbl_test values (8,8,7,8,'h');
insert into tbl_test values (2,2,1,2,'g');
insert into tbl_test values (5,2,2,5,'e');
insert into tbl_test values (3,3,2,1,'c');
insert into tbl_test values (7,4,0,5,'b');
insert into tbl_test values (6,5,2,4,'f');

假设数据结构是堆表形式，那么 idx_bcd 索引的结构大致如下

神奇的 SQL，你们的知识盲区：WHERE条件的提取与应用SQL 语句中的 WHERE 条件，最终都会被提取到 Inde

组合索引 idx_bcd 上有 b,c,d 三个字段，不包括 a,e 字段，它是先按照 b 字段排序，b 字段相同，则按照 c 字段排序，以此类推

针对上表，我们分析如下 SQL

select * from tbl_test where b >= 2 and b < 7 and c > 0 and d != 2 and e != 'a';

此 SQL 中 WHERE 条件用到了 b,c,d,e 四个字段，而索引 idx_bcd 刚好是建立在 b,c,d 三个字段上，那么走 idx_bcd 索引进行条件过滤应该能提高查询效率，既然走 idx_bcd 索引进行条件过滤，那么我们来思考下以下三个关键问题

  1. 上述 SQL，覆盖了 idx_bcd 索引的哪个范围 ？

    起始点由 b >= 2, c > 0 决定，所以 2,1,2 是第一个需要检查的索引项；终止点由 b < 7 决定，所以 8,7,8 是第一个不需要检查的索引项，8,7,8 后面的也无需检索

  2. 范围确定后，SQL 中还有哪些条件可以使用 idx_bcd 索引来过滤 ？

    上面我们已经确认了范围 2,1,2 ~ 8,7,8，那么在这个范围内的每一个索引项是不是都满足 WHERE 条件了 ？ 很显然不是， 4,0,5 不满足 c > 0， 2,1,2 不满足 d != 2；所以 c,d 列的 WHERE 条件可以通过索引 idx_bcd 来过滤

  3. 当 idx_bcd 索引物尽其用后，还有哪些条件是无法通过 idx_bcd 索引过滤的 ？

    这个很明显， e != 'a' 无法在索引 idx_bcd 上进行过滤，因为索引并未包含 e 列，e 列只在堆表上存在，所以需要将已经满足索引查询条件的记录回表，取出对应的完整数据记录，然后看该数据记录中 e 列值是否满足 e != 'a' 条件

你们可能觉得上述 WHERE 条件的抽取具有特殊性，不具普遍性，那么我们就抽象出一套放置于所有 SQL 语句皆准的 WHERE 查询条件的提取规则

Index Key (First Key & Last Key)，Index Filter，Table Filter

我们仔细往下看

  1. Index Key

    用于确定 SQL 查询在索引中的连续范围（起始点 + 终止点）的查询条件，被称之为 Index Key

    由于一个范围，至少包含一个起始条件与一个终止条件，因此 Index Key 也被拆分为 Index First Key 和 Index Last Key，分别用于定位索引查找的起始点和终止点

    • Index First Key

      用于确定索引查询范围的起始点

      提取规则：从索引的第一个键值开始，检查其在 WHERE 条件中是否存在，若存在并且条件是 =、>=，则将对应的条件加入 Index First Key 之中，继续读取索引的下一个键值，使用同样的提取规则；若存在并且条件是 >，则将对应的条件加入 Index First Key 中，同时终止 Index First Key 的提取；若不存在，同样终止 Index First Key 的提取

      回到案例 SQL

      select * from tbl_test where b >= 2 and b < 7 and c > 0 and d != 2 and e != 'a';

      应用这个提取规则，提取出来的 Index First Key 是 b >= 2, c > 0，由于 c 的条件为 >，提取结束

    • Index Last Key

      用于确定索引查询范围的终止点，与 Index First Key 正好相反

      提取规则：从索引的第一个键值开始，检查其在 WHERE 条件中是否存在，若存在并且条件是 =、<=，则将对应条件加入到 Index Last Key 中，继续提取索引的下一个键值，使用同样的提取规则；若存在并且条件是 < ，则将条件加入到 Index Last Key 中，同时终止提取；若不存在，同样终止Index Last Key 的提取

      回到案例 SQL

      select * from tbl_test where b >= 2 and b < 7 and c > 0 and d != 2 and e != 'a';

      应用这个提取规则，提取出来的 Index Last Key 是 b < 7 ，由于是 < 符号，提取结束

  2. Index Filter

    在完成 Index Key 的提取之后，我们根据 WHERE 条件固定了索引的查询范围，那么是不是在范围内的每一个索引项都满足 WHERE 条件了 ？ 很明显 4,0,5 ， 2,1,2 均属于范围中，但是又均不满足 SQL 的查询条件，所以 Index Filter 用于索引范围确定后，确定 SQL 中还有哪些条件可以使用索引来过滤

    提取规则：从索引列的第一列开始，检查其在 WHERE 条件中是否存在，若存在并且 WHERE 条件仅为 =，则跳过第一列继续检查索引下一列，下一索引列采取与索引第一列同样的提取规则；若 WHERE 条件为 >=、>、<、<= 其中的几种，则跳过索引第一列，将其余 WHERE 条件中索引相关列全部加入到 Index Filter 之中；若索引第一列的 WHERE 条件包含 =、>=、>、<、<= 之外的条件，则将此条件以及其余 WHERE 条件中索引相关列全部加入到 Index Filter 之中；若第一列不包含查询条件，则将所有索引相关条件均加入到 Index Filter 之中

    回到案例 SQL

    select * from tbl_test where b >= 2 and b < 7 and c > 0 and d != 2 and e != 'a';

    应用这个提取规则，提取出来的 Index Filter 是 c > 0 and d != 2，因为索引第一列只包含 >=、< 两个条件，因此第一列跳过，将余下的 c、d 两列加入到 Index Filter 中，提取结束

  3. Table Filter

    这个就比较简单了，WHERE 中不能被索引过滤的条件都归为此中

    提取规则：所有不属于索引列的查询条件，均归为 Table Filter 之中

    回到案例 SQL

    select * from tbl_test where b >= 2 and b < 7 and c > 0 and d != 2 and e != 'a';

    应用这个提取规则，那么 Table Filter 就是 e != 'a'

怎么样，这些知识是不是触及到你们的知识盲区了？

神奇的 SQL，你们的知识盲区：WHERE条件的提取与应用SQL 语句中的 WHERE 条件，最终都会被提取到 Inde

但别慌，从此刻开始，WHERE 条件的提取就不再是你们的知识盲区了！

WHERE 条件应用

SQL 语句中的 WHERE 条件，最终都会被提取到 Index Key (Index First Key & Index Last Key)，Index Filter 与 Table Filter 之中，所以 WHERE 条件的应用，其实就是 Index Key (Index First Key & Index Last Key)，Index Filter 与 Table Filter 的应用

  1. Index Key

    • Index First Key

      只是用来定位索引的起始点，因此只在索引第一次 Search Path （沿着索引 B+ 树的根节点一直遍历，到索引正确的叶节点位置）时使用，只会判断一次

    • Index Last Key

      用来定位索引的终止点，因此对于起始点之后读到的每一条索引记录，均需要判断是否满足 Index Last Key，若不满足，则当前查询结束

  2. Index Filter

    用于过滤索引范围中不满足条件的索引项，因此对于索引范围中的每一条索引项，均需要与 Index Filter 进行匹对，若不满足 Index Filter 则直接丢弃，继续读取索引下一条记录

  3. Table Filter

    用于过滤不能被索引过滤的条件，此时的索引项已经满足了 Index First Key 与 Index Last Key 构成的范围，并且满足 Index Filter 的条件，但是索引项无法过滤 Table Filter 中的条件，所以回表读取完整的数据记录，判断完整记录是否满足 Table Filter 中的查询条件，若不满足，跳过当前记录，继续读取索引项的下一条索引项，若满足，则返回记录，此记录满足了 WHERE 的所有条件，可以返回给客户端

如果你们理解了 WHERE 条件的提取，那 WHERE 条件的应用就很容易理解了

总结

  1. SQL 语句中的 WHERE 条件，最终都会被提取到 Index Key (Index First Key & Index Last Key)，Index Filter 与 Table Filter
  2. WHERE 条件的过滤是 one by one 的方式进行的，联表查询其实也是 one by one 的方式进行的
  3. MySQL 5.6 中引入了 Index Condition Pushdown，究竟是 Push Down 了什么，从哪 Push Down 到哪 ，有什么用？我们后续再讲解
转载自:https://juejin.cn/post/7411745719227531314
评论
请登录