深入索引的各种执行细节

索引在查询过程中的作用

1. 一个索引就是一个 B+树，索引让我们的查询可以快速定位和扫描到我们 需要的数据记录上，加快查询的速度。
2. 一个 select 查询语句在执行过程中一般最多能使用一个二级索引，即使在 where 条件中用了多个二级索引。（比较苛刻条件下会出现索引合并）

扫描区间

全表扫描

对于某个查询来说，最简单粗暴的执行方案就是扫描表中的所有记录，判断 每一条记录是否符合搜索条件。如果符合，就将其发送到客户端，否则就跳过该录。这就是全表扫描。

对于使用 InnoDB 存储引擎的表来说，全表扫描意味着从聚簇索引第一个叶子节点的第一条记录开始，沿着记录所在的单向链表向后扫描，直到最后一个叶子节点的最后一条记录。虽然全表扫描是一种很笨的执行方案，但却是一种万能的执行方案，所有的查询都可以使用这种方案来执行，只是效率不高。（可能也比某些普通索引快）

索引扫描

我们有了索引，利用 B+树查找索引列值等于某个值的记录，这样可以明显减少需要扫描的记录数量。由于B+树叶子节点中的记录是按照索引列值由小到大的顺序排序的，所以即使只扫描某个区间或者某些区间中的记录也可以明显减少需要扫描的记录数量。

扫描区间判定实战

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

首先这个是根据id查询，也就是说我们不需要回表操作。这也是最理想状态下的范围查询。

这个语句其实是想查找 id 值在[3,99]区间中的所有聚簇索引记录。我们可以通过聚簇索引对应的 B+树快速地定位到 id 值为 3 的那条聚簇索引记录，然后沿着记录所在的单向链表向后扫描,直到某条聚簇索引记录的 id 值不在[3,99]区间 中为止。

与全表扫描相比，扫描 id 值在[3,99]区间中的记录已经很大程度地减少了需要扫描的记录数量，所以提升了查询效率。其实所谓的全表扫描，我们可以理解 为扫描的区间是[负无穷，正无穷]或者[第一条记录，最后一条记录]。

给我们的启发便是，在业务代码中尽量用id进行查询。此时select *效率也不差。

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

共存在三个扫描区间，两个单独扫描区间[3,3]、[9,9]，一个范围扫描区间[23,99]

SELECT * FROM order_exp WHERE order_no <'DD00_10S' AND expire_time> '2021-03-22 18:28:28' AND order_note > '7 排';

首先，先说明一下这条sql的执行场景。

order_no 和 expire_time 都有索引，order_note 没有索引，那会有两个扫描区间吗？

并不会，请记住，一个 Select 查询语句在执行过程中一般 最多能使用一个二级索引。

那么具体选择哪个索引进行执行呢？这个要交给我们的优化器去进行判定。无论用哪个索引执行查询，都需要获取到索引中的记录后，进行回表，获取 到完整的用户记录后再根据判定条件判断这条记录是否满足 SQL 语句的要求。

不同操作符的索引的范围

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

IN操作符

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'

依旧会走order_no的索引。此时使用 idx_expire_time 执行查询时对应的扫描区间就是[第一条记录 , 'DD00_9S'）和（ 'DD00_9S',最后一条记录]（相当于仅仅把这条去掉了）。

为什么like操作符要尽量把%写到后面（b%）

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

很显然，note LIKE' b%' 形成的扫描区间相当于['b', 'c')。 不过在日常的工作中，一个查询的 WHERE 子句可能有很多个小的搜索条件， 这些搜索条件需要使用 AND 或者 OR 操作符连接起来，我们来看看怎么从由 AND 或 OR 组成的复杂搜索条件中提取出正确的范围区间。

范围索引重叠下and与or连接的区别

and取交集

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

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取并集

再看一下使用 OR 将多个搜索条件连接在一起的情况：

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

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

为什么有索引，却用不上

索引范围and非索引条件查询

比如下面这个查询：

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

这个例子中的expire_time是索引，而order_note不是索引。

这个查询语句中能利用的索引只有 expire_time一个，而 expire_time 这个二级索引的记录中又不包含 order_note 这个字段，所以在使用二级索引 idx_expire_time 定位记录的阶段用不到 order_note = 'abc'这个条件。

但是，由于order_note这个条件用不上，因此我们只能通过 expire_time 这个条件做出一次筛查。之后要把所有数据全部回表，判断 order_note 是否='abc'。如果成立，则取出整行数据。

那么，此时我们就想到了更好的解决方案，可以创建一个 expire_time 与 order_note 的联合索引。这样就可以在联合索引中将查询范围再次缩小，以减小回表成本。（发现一般这种范围查询和条件查询组合成的联合索引对效率优化都不小。因为范围查询一般数据量查出来的都比较大）。

索引范围or非索引条件查询（失效）

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

查询条件变成这样时，优化器不会选择任何一个条件执行，为什么呢？因为or语句取的是并集。我们并不能保证第一个范围查询中能包含所有order_note = "abc" 的情况（注意。如果查询条件相同的情况下使用or查询会取并集。失效发生在不同的查询条件中使用or）。

因此即时走索引，也需要全表扫描order_note的值。因此不如直接全局扫描。

联合索引的查询扫描区间

如图，假设我们有以下联合索引：

我们可以把这个联合索引当做加了三种索引：

1. insert_time
2. insert_time ，order_status
3. insert_time ，order_status ，expire_time

必须严格按照上述三种索引的顺序才可以，有任何不同都会导致使用失败。

采用的排序规则如下所示：

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

分析是否走索引实战

情况1

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

可以单独使用联合索引的insert_time 索引

情况2

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

可以使用insert_time ，order_status 这个联合索引。

情况3

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 ，order_status ，expire_time 这个联合索引

情况4

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

可以单独使用联合索引的insert_time 索引

情况5

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

可以使用insert_time ，order_status 这个联合索引。

情况6

SELECT * FROM order_exp WHERE order_status = 1;

无法使用索引

情况7

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

跳过了中间的索引，无法使用索引

情况8

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

可以使用insert_time ，order_status 这个联合索引。

简单了解 MyISAM 中的索引

MyISAM 存储引擎中的索引方案。我们知道 InnoDB 中聚簇索引对应的 B+树的叶子节点中已经把所有完整的用户记录都包含了，而 MyISAM 的 索引方案虽然也使用树形结构，但是却将索引和数据分开存储的。

使用 MyISAM 存储引擎的表会把索引信息另外存储到一个称为索引文件的另 一个文件中。MyISAM 会单独为表的主键创建一个索引，只不过在索引的叶子节 点中存储的不是完整的用户记录，而是主键值＋行号的组合。也就是先通过索引找到对应的行号，再通过行号去找对应的记录。

这一点和 InnoDB 是完全不相同的，在 InnoDB 存储引擎中，我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在 MyISAM 中即使是聚集索引，也要进行一次回表操作，意味着 MyISAM 中建立的索引相当于全部都是二级索引!

我们也可以对其它的列分别建立索引或者建立联合索引， 原理和 InnoDB 中的索引差不多，不过在叶子节点处存储的是相应的列＋行号。 这些索引也全部都是二级索引。