SQL中的where条件，在数据库中提取与应用浅析

一、初始条件

create table t1 (a int primary key, b int, c int, d int, e varchar(20));create index idx_t1_bcd on t1(b, c, d);insert into t1 values (4,3,1,1,’d’);insert into t1 values (1,1,1,1,’a’);insert into t1 values (8,8,8,8,’h’):insert into t1 values (2,2,2,2,’b’);insert into t1 values (5,2,3,5,’e’);insert into t1 values (3,3,2,2,’c’);insert into t1 values (7,4,5,5,’g’);insert into t1 values (6,6,4,4,’f’);

简单分析一下上图：
1、idx_t1_bcd索引上有[b,c,d]三个字段 (注意：若是InnoDB类的聚簇索引表，idx_t1_bcd上还会包括主键a字段)，不包括[a,e]字段。

2、idx_t1_bcd索引，首先按照b字段排序，b字段相同，则按照c字段排序，以此类推。记录在索引中按照[b,c,d]排序，但是在堆表上是乱序的，不按照任何字段排序。

二、where 提取规则

1、Index Key

用于确定SQL查询在索引中的连续范围(起始范围+结束范围)的查询条件，被称之为Index Key。由于一个范围，至少包含一个起始与一个终止，因此Index Key也被拆分为Index First Key和Index Last Key，分别用于定位索引查找的起始，以及索引查询的终止条件。

Index First Key：用于确定索引查询的起始范围

提取规则：

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

2. 若存在并且条件是 >，则将对应的条件加入Index First Key中，同时终止Index First Key的提取；若不存在，同样终止Index First Key的提取。

针对上面的SQL，提取出来的Index First Key为(b >= 2, c > 1)。由于c的条件为 >，提取结束，不包括d。

Index Last Key：用于确定索引查询的终止范围

提取规则：

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

2. 若存在并且条件是 <，则将条件加入到Index Last Key中，同时终止提取；若不存在，同样终止Index Last Key的提取。

针对上面的SQL，应用这个提取规则，提取出来的Index Last Key为(b < 8)，由于是 < 符号，因此提取b之后结束。

在完成Index Key的提取之后，我们根据where条件固定了索引的查询范围，但是此范围中的项，并不都是满足查询条件的项。在上面的SQL用例中，(3,1,1)，(6,4,4)均属于范围中，但是又均不满足SQL的查询条件。

2、Index Filter

Index Filter的提取规则：

1. 同样从索引列的第一列开始，检查其在where条件中是否存在，若存在并且where条件仅为 =，则跳过第一列继续检查索引下一列，下一索引列采取与索引第一列同样的提取规则；若where条件为 >=、>、<、<= 其中的几种，则跳过索引第一列，将其余where条件中索引相关列全部加入到Index Filter之中；

2. 若索引第一列的where条件包含 =、>=、>、<、<= 之外的条件，则将此条件以及其余where条件中索引相关列全部加入到Index Filter之中；

3. 若第一列不包含查询条件，则将所有索引相关条件均加入到Index Filter之中。

针对上面的用例SQL，索引第一列只包含 >=、< 两个条件，因此第一列可跳过，将余下的c、d两列加入到Index Filter中。

因此获得的Index Filter为 c > 1 and d != 4 。

3、Table Filter

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

此时获得的Table Filter就为 e != ‘a’。

三、总结

SQL语句中的where条件，使用以上的提取规则，最终都会被提取到Index Key (First Key & Last Key)，Index Filter与Table Filter之中。

Index First Key，只是用来定位索引的起始范围，因此只在索引第一次Search Path(沿着索引B+树的根节点一直遍历，到索引正确的叶节点位置)时使用，一次判断即可；

Index Last Key，用来定位索引的终止范围，因此对于起始范围之后读到的每一条索引记录，均需要判断是否已经超过了Index Last Key的范围，若超过，则当前查询结束；

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

Table Filter，则是最后一道where条件的防线，用于过滤通过前面索引的层层考验的记录，此时的记录已经满足了Index First Key与Index Last Key构成的范围，并且满足Index Filter的条件，回表读取了完整的记录，判断完整记录是否满足Table Filter中的查询条件，同样的，若不满足，跳过当前记录，继续读取索引的下一条记录，若满足，则返回记录，此记录满足了where的所有条件，可以返回给前端用户。