2-3-2-3、索引在查询中的作用_查询索引作用在哪列-CSDN博客

本文链接：https://blog.csdn.net/weixin_41381248/article/details/128921262

一个索引就是一个 B+树，索引让查询可以快速定位和扫描到需要的数据记录上，加快查询的速度
一个 select 查询语句在执行过程中一般最多能使用一个二级索引，即使在 where 条件中用了多个二级索引

扫描区间

对于某个查询来说，最简单粗暴的执行方案就是扫描表中的所有记录，判断每一条记录是否符合搜索条件。如果符合，就将其发送到客户端，否则就跳过该记录。这就是全表扫描
对于使用 InnoDB 存储引擎的表来说，全表扫描意味着从聚簇索引第一个叶子节点的第一条记录开始，沿着记录所在的单向链表向后扫描，直到最后一个叶子节点的最后一条记录。虽然全表扫描是一种很笨的执行方案，但却是一种万能的执行方案，所有的查询都可以使用这种方案来执行，只是效率不高
有了索引，利用 B+树查找索引列值等于某个值的记录，这样可以明显减少需要扫描的记录数量。由于 B+树叶子节点中的记录是按照索引列值由小到大的顺序排序的，所以即使只扫描某个区间或者某些区间中的记录也可以明显减少需要扫描的记录数量。比如下面这个查询语句:

SELECT * FROM order_exp WHERE id >= 3 AND id<= 99;

这个语句其实是想查找 id 值在[3,99]区间中的所有聚簇索引记录。我们可以通过聚簇索引对应的 B+树快速地定位到 id 值为 3 的那条聚簇索引记录，然后沿着记录所在的单向链表向后扫描,直到某条聚簇索引记录的 id 值不在[3,99]区间中为止
与全表扫描相比，扫描 id 值在[3,99]区间中的记录已经很大程度地减少了需要扫描的记录数量，所以提升了查询效率。其实所谓的全表扫描，我们可以理解为扫描的区间是[负无穷，正无穷]或者[第一条记录，最后一条记录]
比如下面这条sql：

SELECT * FROM order_exp WHERE id in(3,9) OR (id>=23 AND id<= 99);

它拥有三个扫描区间，两个单独扫描区间[3,3]、[9,9]，一个范围扫描区间[23,99]
特别注意：扫描区间建立在索引生效的前提下，同一条sql中存在多个索引还会导致回表问题的发生

范围区间扫描

对于 B+树索引来说，只要索引列和常数使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=（不等于也可以写成<>）或者 LIKE 操作符连接起来，就可以产生一个区间

IN 操作符的效果和若干个等值匹配操作符=之间用OR连接起来是一样的，也就是说会产生多个单点区间，比如下边这两个语句的效果是一样的：

SELECT * FROM order_exp WHERE insert_time IN (2021-03-22 18:23:42, yyyy);
SELECT * FROM order_exp WHERE insert_time= 2021-03-22 18:23:42 OR insert_time = yyyy;

!=产生的扫描区间一般有两个：

SELECT * FROM order_exp WHERE order_no != 'DD00_9S';

此时使用 idx_expire_time 执行查询时对应的扫描区间就是[第一条记录, ‘DD00_9S’]和[ ‘DD00_9S’,最后一条记录]

LIKE 操作符比较特殊，只有在匹配完整的字符串或者匹配字符串前缀时才产生合适的扫描区间

对于某个索引列来说，字符串前缀相同的记录在由记录组成的单向链表中肯定是相邻的。比如我们有一个搜索条件是 note LIKE’ b%‘，对于二级索引 idx_note 来说，所有字符串前缀为’b’的二级索引记录肯定是相邻的。这也就意味着我们只要定位到 idx_note 值的字符串前缀为’b’的第一条记录，就可以沿着记录所在的单向链表向后扫描，直到某条二级索引记录的字符串前缀不为 a 为止

很显然，note LIKE’ b%’ 形成的扫描区间相当于[‘b’, ‘c’)

扫描区间合并

有时候每个搜索条件都可以使用到某个索引，比如下边这个查询语句：

SELECT * FROM order_exp WHERE order_no > 'DD00_6S' AND order_no > 'DD00_9S';

这个查询中的搜索条件都可以使用到 idx_order_no，也就是说每个搜索条件都对应着一个 idx_order_no 的范围区间。这两个小的搜索条件使用 AND 连接起来，也就是要取两个范围区间的交集，两者交集当然就是 order_no > ‘DD00_9S’ 了，也就是说上边这个查询使用 idx_order_no 的范围区间就是(‘DD00_9S’, 最后一条记录)
使用 OR 将多个搜索条件连接在一起的情况：

SELECT * FROM order_exp WHERE order_no > 'DD00_6S' OR order_no > 'DD00_9S';

OR 意味着需要取各个范围区间的并集，所以上边这个查询使用 idx_expire_time 的范围区间就是( ‘DD00_6S’ ,最后一条记录)

搜索条件无法使用索引的情况分析

比如下边这个查询：

SELECT * FROM order_exp WHERE expire_time> '2021-03-22 18:35:09' AND order_note = 'abc';

这个查询语句中能利用的索引只有 idx_expire_time 一个，而 idx_expire_time 这个二级索引的记录中又不包含 order_note 这个字段，所以在使用二级索引 idx_expire_time 定位记录的阶段用不到 order_note = 'abc’这个条件，这个条件是在回表获取了完整的用户记录后才使用的，而范围区间是为了到索引中取记录中提出的概念，所以在确定范围区间的时候不需要考虑 order_note = 'abc’这个条件
上边的查询中用不到 idx_expire_time 的搜索条件化简之后就是这样：

SELECT * FROM order_exp WHERE expire_time> '2021-03-22 18:35:09';

也就是说最上边那个查询使用 idx_expire_time 的范围区间就是：(‘2021-03-22 18:35:09’,最后一条记录)
使用 OR 的情况：

SELECT * FROM order_exp WHERE expire_time> '2021-03-22 18:35:09' OR order_note = 'abc';

这条语句在搜索时可以化简为：

SELECT * FROM order_exp;

如果我们使用 idx_expire_time 执行查询的话，对应的范围区间就是[第一条记录,最后一条记录]，也就是需要将全部二级索引的记录进行回表，这个代价肯定比直接全表扫描都大了。也就是说一个使用到索引的搜索条件和没有使用该索引的搜索条件使用 OR 连接起来后是无法使用该索引的

idx_expire_time 这个二级索引的记录中不包含 order_note 这个字段，那就说，即使二级索引 idx_expire_time 中找到了满足 expire_time> '2021-03-22 18:35:09’的记录，是无法判定 order_note 是否满足 order_note = 'abc’的，又因为是 OR 条件，所以必须要在主键索引中从第一条记录到最后一条记录逐条判定 order_note 是否等于 ‘abc’

复杂搜索条件下找出范围匹配的区间

以下面sql为例说明：

SELECT * FROM order_exp WHERE (order_no > 'DD00_9S' AND expire_time = '2021-03-22 18:35:09' ) OR (order_no < 'DD00_6S' AND order_no > 'DD00_9S') OR (order_no LIKE '%0S' AND order_no > 'DD00_12S' AND (expire_time < '2021-03-22 18:28:28' OR order_note = 'abc'));

这个查询的搜索条件涉及到了 order_no、expire_time、order_note 这 3 个列，然后 order_no 列有二级索引 idx_order_no，expire_time 列有二级索引 idx_expire_time

使用 idx_order_no 执行查询

需要把那些用不到该索引的搜索条件暂时移除掉。上边的查询中除了有关 expire_time 和 order_note 列不能使用到 idx_order_no 索引外，order_no LIKE '%0S’也使用不到索引
如果条件太复杂，看着演化怕出错，可以把所有用不到的搜索条件视为 True 来进行中间替换，所以把这些搜索条件替换为 True 之后的样子就是这样：

(order_no > 'DD00_9S' AND TRUE ) OR (order_no < 'DD00_6S' AND order_no > 'DD00_9S') OR (TRUE AND order_no > 'DD00_12S' AND (TRUE OR TRUE))

化简处理：

(order_no > 'DD00_9S') OR (order_no < 'DD00_6S' AND order_no > 'DD00_9S') OR (order_no > 'DD00_12S')

因为符合 order_no < ‘DD00_6S’ AND order_no > 'DD00_9S’永远为 FALSE，所以上边的搜索条件可以被写成这样：

(order_no > 'DD00_9S') OR (order_no > 'DD00_12S')

很明显，两者使用 OR 操作符连接起来的，意味着要取并集，所以最终的结果化简的到的区间就是：

order_no > 'DD00_9S'

也就是说：上边那个复杂搜索条件的查询语句如果使用 idx_order_no 索引执行查询的话，需要把满足 order_no > 'DD00_9S’的二级索引记录都取出来，然后拿着这些记录的 id 再进行回表，得到完整的用户记录之后再使用其他的搜索条件进行过滤。记住，如果使用 idx_order_no 索引执行查询，不代表 MySQL 一定会使用，因为 MySQL 需要做整体评估，才能确定是否使用这个索引还是别的索引，或者是干脆全表扫描

使用 idx_expire_time 执行查询

那些用不到该索引的搜索条件暂时使用 TRUE 条件替换掉，其中有关 order_no 和 order_note 的搜索条件都需要被替换掉，替换结果就是：

(TRUE AND expire_time = '2021-03-22 18:35:09' ) OR (TRUE AND TRUE) OR (TRUE AND TRUE AND (expire_time < '2021-03-22 18:28:28' OR TRUE))

化简处理：

根据布尔运算的规则，expire_time < ‘2021-03-22 18:28:28’ OR TRUE 的结果肯定是 TRUE

TRUE

这个结果也就意味着如果我们要使用 idx_expire_time 索引执行查询语句的话，需要扫描 idx_expire_time 二级索引的所有记录，然后再回表，效率上还不如全表扫描，因此一定不会使用 idx_expire_time 索引的

使用联合索引执行查询时对应的扫描区间

order_exp 表的 u_idx_day_status 联合索引为例：

联合索引的索引列包含多个列，B+树每一层页面以及每个页面中的记录采用的排序规则较为复杂，它采用的排序规则如下所示：

先按照 insert_time 列的值进行排序
在 insert_time 列的值相同的情况下，再按照 order_status 列的值进行排序
在 insert_time 和 order_status 列的值都相同的情况下，再按照 expire_time 列的值进行排序

对于下边这个查询 Q1 来说：

Q1：SELECT * FROM order_exp WHERE insert_time = '2021-03-22 18:34:55';

由于二级索引记录是先按照 insert_time 列的值进行排序的，所以所有符合 insert_time = '2021-03-22 18:34:55’条件的记录肯定是相邻的，我们可以定位到第一条符合 insert_time = '2021-03-22 18:34:55’条件的记录，然后沿着记录所在的单向链表向后扫描，直到某条记录不符合 insert_time = '2021-03-22 18:34:55’条件为止(当然，对于获取到的每一条二级索引记录都要执行回表操作)
也就是说，如果我们使用 u_idx_day_status 索引执行查询 Q1 时，对应的扫描区间就是[‘2021-03-22 18:34:55’, ‘2021-03-22 18:34:55’]，形成这个扫描区间的条件就是 insert_time = ‘2021-03-22 18:34:55’
对于下边这个查询 Q2 来说：

Q2：SELECT * FROM order_exp WHERE insert_time = '2021-03-22 18:34:55' AND order_status = 0;

由于二级索引记录是先按照 insert_time 列的值进行排序的；在 insert_time 列的值相等的情况下，再按照 order_status 列进行排序。所以符合 insert_time = ‘2021-03-22 18:34:55’ AND order_status = 0 条件的二级索引记录肯定是相邻的，我们可以定位到第一条符合 insert_time=‘2021-03-22 18:34:55’ AND order_status=0 条件的记录，然后沿着记录所在的链表向后扫描，直到某条记录不符合 insert_time='2021-03-22 18:34:55’条件或者 order_status=0 条件为止
也就是说，如果我们使用 u_idx_day_status 索引执行查询 Q2 时，可以形成扫描区间[(‘2021-03-22 18:34:55’, 0), (‘2021-03-22 18:34:55’, 0)]，形成这个扫描区间的条件就是 insert_time = ‘2021-03-22 18:34:55’ AND order_status = 0
对于下边这个查询 Q3 来说：

Q3：SELECT * FROM order_exp WHERE insert_time = '2021-03-22 18:34:55' AND order_status = 0 AND expire_time = '2021-03-22 18:35:13';

由于二级索引记录是先按照 insert_time 列的值进行排序的；在 insert_time 列的值相等的情况下，再按照 order_status 列进行排序；在 insert_time 和 order_status 列的值都相等的情况下，再按照 expire_time 列进行排序。所以符合 insert_time = ‘2021-03-22 18:34:55’ AND order_status = 0 AND expire_time = '2021-03-22 18:35:13’条件的二级索引记录肯定是相邻的，我们可以定位到第一条符合 insert_time=‘2021-03-22 18:34:55’ AND order_status=0 AND expire_time='2021-03-22 18:35:13’条件的记录，然后沿着记录所在的链表向后扫描，直到某条记录不符合 insert_time='2021-03-22 18:34:55’条件或者 order_status=0 条件或者 expire_time='2021-03-22 18:35:13’条件为止。如果我们使用 u_idx_day_status 索引执行查询 Q3 时，可以形成扫描区间[(‘2021-03-22 18:34:55’, 0, ‘2021-03-22 18:35:13’), (‘2021-03-22 18:34:55’, 0, ‘2021-03-22 18:35:13’)]，形成这个扫描区间的条件就是 insert_time = ‘2021-03-22 18:34:55’ AND order_status = 0 AND expire_time = ‘2021-03-22 18:35:13’
对于下边这个查询 Q4 来说：

Q4：SELECT * FROM order_exp WHERE insert_time < '2021-03-22 18:34:55';

由于二级索引记录是先按照 insert_time 列的值进行排序的，所以所有符合 insert_time < '2021-03-22 18:34:55’条件的记录肯定是相邻的，我们可以定位到第一条符合 insert_time < '2021-03-22 18:34:55’条件的记录(其实就是 u_idx_day_status 索引第一个叶子节点的第一条记录)，然后沿着记录所在的链表向前扫描，直到某条记录不符合 insert_time < '2021-03-22 18:34:55’为止
也就是说，如果我们使用 u_idx_day_status 索引执行查询 Q4 时，可以形成扫描区间(第一条记录, ‘2021-03-22 18:34:55’)，形成这个扫描区间的条件就是 insert_time < ‘2021-03-22 18:34:55’
对于下边这个查询 Q5 来说：

Q5：SELECT * FROM order_exp WHERE insert_time = '2021-03-22 18:34:55' AND order_status >= 0;

由于二级索引记录是先按照 insert_time 列的值进行排序的；在 insert_time 列的值相等的情况下，再按照 order_status 列进行排序。也就是说在符合 insert_time = '2021-03-22 18:34:55’条件的二级索引记录中，是按照 order_status 列的值进行排序的，那么此时符合 insert_time = ‘2021-03-22 18:34:55’ AND order_status > =0 ;条件的二级索引记录肯定是相邻的。我们可以定位到第一条符合 insert_time = ‘2021-03-22 18:34:55’ AND order_status > =0 ;条件的记录，然后沿着记录所在的链表向后扫描，直到某条记录不符合 insert_time='2021-03-22 18:34:55’条件或者 order_status > =0 条件为止
也就是说，如果我们使用 u_idx_day_status 索引执行查询 Q5 时，可以形成扫描区间，条件就是 insert_time = ‘2021-03-22 18:34:55’ AND order_status > =0;
对于下边这个查询 Q6 来说：

Q6：SELECT * FROM order_exp WHERE order_status = 1;

由于二级索引记录不是直接按照 order_status 列的值排序的，所以符合 order_status = 1 的二级索引记录可能并不相邻，也就意味着我们不能通过这个order_status = 1 搜索条件来减少需要扫描的记录数量。在这种情况下，我们是不会使用 u_idx_day_status 索引执行查询的
对于下边这个查询 Q7 来说：

Q7：SELECT * FROM order_exp WHERE insert_time = '2021-03-22 18:34:55' AND expire_time = '2021-03-22 18:35:12';

由于二级索引记录是先按照 insert_time 列的值进行排序的，所以符合 insert_time = '2021-03-22 18:34:55’条件的二级索引记录肯定是相邻的，但是对于符合 insert_time = '2021-03-22 18:34:55’条件的二级索引记录来说，并不是直接按照 expire_time 列进行排序的，也就是说我们不能根据搜索条件 expire_time = '2021-03-22 18:35:12’来进一步减少需要扫描的记录数量。那么如果我们使用 u_idx_day_status 索引执行查询的话，可以定位到第一条符合 insert_time='2021-03-22 18:34:55’条件的记录，然后沿着记录所在的单向链表向后扫描，直到某条记录不符合 insert_time = '2021-03-22 18:34:55’条件为止。所以在使用 u_idx_day_status 索引执行查询 Q7 的过程中，对应的扫描区间其实是 [‘2021-03-22 18:34:55’, ‘2021-03-22 18:34:55’]，形成该扫描区间的搜索条件是 insert_time = ‘2021-03-22 18:34:55’，与 expire_time = '2021-03-22 18:35:12’无关
对于下边这个查询 Q8 来说：

Q8：SELECT * FROM order_exp WHERE insert_time < '2021-03-22 18:34:57' AND order_status = 1;

由于二级索引记录是先按照 insert_time 列的值进行排序的，所以符合 insert_time < '2021-03-22 18:34:57’条件的二级索引记录肯定是相邻的，但是对于符合 insert_time < '2021-03-22 18:34:57’条件的二级索引记录来说，并不是直接按照 order_status 列进行排序的，也就是说我们不能根据搜索条件 order_status = 0 来进一步减少需要扫描的记录数量。那么如果我们使用 u_idx_day_status 索引执行查询的话，可以定位到第一条符合 insert_time 的记录，其实就是 u_idx_day_status 索引第一个叶子节点的第一条记录，所以在使用 u_idx_day_status 索引执行查询 Q8 的过程中，对应的扫描区间其实是[第一条记录, ‘2021-03-22 18:34:57’)