B+树索引的使用

索引的代价

空间复杂度
索引就是一个又一个的数据页组成的B+树，单个数据页默认占用的空间是16kb，用户记录越多，索引文件空间占用就越多。不同的索引有自己的索引文件，太多的索引会造成空间的膨胀。
时间复杂度
我们对于用户记录的增删改操作，Innodb 都会去维护创建的索引的B+树组织结构，可能会涉及到过多的页分裂，页面回收等操作。过多的索引创建会加重维护的负担，且一般来说，查询优化器都会进行成本分析，选择一个合适的索引进行查询操作，索引太多的话，也会增加这种成本分析的负担。

区间扫描与边界条件

从一条简单的SQL语句说起：

select * from table where id >= 2 and id <= 100;

这里的id字段是主键，也就是默认建立了聚簇索引。

这段SQL语句的边界条件就是：id >= 2 and id <= 100，对应的扫描区间就是：[2,100]。

根据 MySQL 中 B+树的特点，我们只需要找到id值等于2的那条记录，然后沿着记录形成的单向链表一直向后，直到id值 > 100就停止扫描。

那全表扫描的区间呢？

很显然，(-∞,+∞)。

再来看一条SQL语句：

select * from table where field_2 in (1008,3333) or (field_2 >= 2 and field_2 <= 100);

假设filed_2都创建了索引。

扫描区间：[1008,1008]、[3333,3333]、[2,100]

精准的查询条件，可以写成闭区间的形式。

当然，并不是所有的搜索条件都可以成为边界条件，使用区间的范围扫描了：

select * from table where index_1 > 'a' and index_2 < 'z' and common_field = 'abc';

这里规定，以后的文章中，index开头代表索引字段，common开头的是非索引字段，id表示主键。

我们都知道，一般情况下，只会选择一个索引，也就是在某棵B+树上进行范围性的查找操作。那么上述这条 SQL 语句，会怎么选择呢？

这里我们暂时不知道，但是我们可以来讨论一下，不同的选择对应的查询过程是怎样的：

选择index_1
扫描区间：('a',+∞)，搜集到符合条件的二级索引对应的主键，然后回表查询，再过滤出index_2 < 'z' and common_field = 'abc' 的记录
选择index_2
扫描区间：(-∞,'z')，搜集到符合条件的二级索引对应的主键，然后回表查询，再过滤出index_1 > 'a' and common_field = 'abc'的记录

无论选择谁，都要进行回表操作，再按照剩余的查询条件，过滤数据集，得到最终的结果集。

通过上述例子，我们可以得出查询的精髓：通过搜索条件，找到对应的查询区间。

这里列举一些容易忽略的注意点：

!=
比如说!= x，那它的查询区间就是(-∞,x)和(x,+∞)。
in
比如说x in (3,4)，相当于x = 3 or x = 4，就是个单点扫描区间：[3,3]和[4,4]
like
比如说like 'a%'，这个的区间是什么呢？首字母['a','b')。
我们都说like叫做前缀匹配，是因为在形成索引的时候，索引列存储的是字符串的话，会进行字符串的比较：从左向右的挨个比较每个字符的大小（按照对应的比较规则来），当前字符相同，则比较下一个字符。所以前缀匹配是在有序的范围内查找，而后缀则不行。

扫描区间提取

接下来的部分，将介绍怎么在复杂的and和or搜索条件中提取出正确的扫描区间：

2、有的搜索条件不能生成合适的扫描区间

select * from table where index_1 > 100 and common_filed = 'abc';

and取交集，index_1扫描区间：(100,+∞)；common_filed扫描区间：(-∞,+∞)，结果区间：(100,+∞)。

select * from table where index_1 > 100 or common_filed = 'abc';

or取并集，很明显了，(-∞,+∞)。

实际上，非聚簇索引还要进行回表操作（因为这里是*），所以这里最好不要走索引。

3、从复杂的搜索条件中找出扫描区间

select 
* 
from table 
where 
(index_1 > 'xyz' and index_2 = 748)
or
(index_1 < 'abc' and index_1 > 'lmn')
or
(index_1 like '%suf' and index_1 > 'zzz' and (index_2 < 8000 or common_field = 'abc') );

一步步来，我们遵循以下原则：

分析搜索条件中用到的条件字段都有哪些？
idnex_1、index_2、common_field

对于可能用到的索引，分析它们的扫描区间

假设使用index_1进行查询

简化SQL语句：

select 
* 
from table 
where 
(index_1 > 'xyz' and true)
or
(index_1 < 'abc' and index_1 > 'lmn')
or
(true and index_1 > 'zzz' and (true or true) );

为什么？

走谁的索引，只会根据谁的条件进行范围筛选，其余的条件是在回表之后的数据集中筛选。

继续简化：

select 
* 
from table 
where 
(index_1 > 'xyz')
or
(index_1 < 'abc' and index_1 > 'lmn') // 很明显为false
or
(index_1 > 'zzz');

继续：

select 
* 
from table 
where 
(index_1 > 'xyz')
or
(index_1 > 'zzz');

or取并集，最终的扫描区间是：('xyz',+∞)。

假设使用index_2进行查询
这里直接写简化后的结果，步骤同上：
```
select 
* 
from table 
where 
true
```
什么意思？全表扫描，再进行回表过滤呗！

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

要想不让进去，就得抓住一点，联合索引组织顺序：前一个索引值相同时，才会按照后一个索引值进行排列。

只有顺序排列才能进入区间的考虑范围。

直接看SQL语句，

前置条件：index_1、index_2、index_3按照顺序建立了联合索引

select * from table where index_1 = 'a';

很简单，会找到第一个index_1列值为a的，沿着链表顺序，一直找到第一个index_1列的值不为a的。

select * from table where index_1 = 'a' and index_2 = 'b';

因为index_2是根据index_1排列的，在index_1值相等的情况下，index_2可以缩小区间范围。找到第一条符合条件的索引记录，一直往后找，直到index_1 != 'a' or index_2 != 'b'。

select * from table where index_1 = 'a' and index_2 = 'b' and index_3 = 'c';

同样的，三个字段都能为区间范围的缩小做出贡献，因为它们都是等值查找。

select * from table where index_1 < 'a';

不用多说。

select * from table where index_1 = 'a' and index_2 > 'a' and index_2 < 'd';

index_1是等值查询，index_2在这个基础上，是有序的，可以帮助缩小区间。

select * from table where index_2 = 'a';

这就很抱歉了，没有index_1打基础的话（也就是其一个参考字段），只能进行全表扫描，然后回表了。（杜绝）

select * from table where index_1 = 'a' and index_3 = 'c';

index_3依赖于index_2，所以在这里不能帮助缩小范围。但是，可以帮助减少回表的操作（判断indx_3是否等于'c'），这叫做索引下推（后面会说）。

select * from table where index_1 < 'a' and index_2 = 'a';

由于index_1不是等值查询，依赖它的index_2可能不连续，所以不会帮助缩小范围，有索引下推。

select * from table where index_1 <= 'b' and index_2 = 'a';

和前一个很像，但是这个的index_2可以帮助缩小区间范围，当index_1 = 'b'时，这时候就是等值查询了。同样有索引下推。

索引用于排序

一般对于数据的排序操作，都是加载到内存中进行的。如果数据文件太大的话，就得加载部分数据，排序后在磁盘中做临时存储。这种统称为文件排序。

索引本身就是排好序的，直接省略掉了排序的步骤，非常的高效。但使用的时候，也需要注意以下几点：

2、不可以使用索引进行排序的几种情况

（1）ASC、DESC 混用

上文有个联合索引排序的例子，如果是这样的 SQL 语句呢？

select * from table order by index_1 ASC,index_2 DESC limit 10;

很明显根据语义，我们取index_1第一条记录，也就是最小记录，记做minValue。我们需要一直向右查找，找出所有index_1 = minValue的记录，然后向左遍历，取10条记录。

问题在于，我们不知道到底有多少条记录的index_1 = minValue，只能一直向右寻找。这还不是最关键的，最关键的是，可能还不足10条，又要重复操作。

所以，这种情况下不会走联合索引。 （2）排序列包含非同一个索引列

select * from table order by index_1,index_2 limit 10;

index_1和index_2是二级索引，也就是说我们只能选择一棵B+树嘛。无论选择谁，与对方都没有关系，仅仅是自己有序而已。

（3）排序字段中的联合索引字段不连续

select * from table order by index_1,index_3,index_2 limit 10;

index_1、index_2、index_3按顺序创建了联合索引。

这个不用多说了吧。

（4）过滤字段与排序字段不同

select * from table where index_1 = 'a' order by index_2 limit 10;

这个不用多说了嘛。

（5）排序列名不纯粹，被修饰了

比如这个例子：

select * from table order by upper(index_1) limit 10;

回表这一操作经常出现在二级索引当中，需要根据主键id到聚簇索引中找到对应的用户记录。由于二级索引中有序的并不是主键id，而且数据页之间可能也并不相邻，所以在回表的过程中，可能会涉及到大量的磁盘 I/O ，影响查询性能（要把数据从磁盘加载到内存中）。当然，这个也不能否定索引带来的高效，我们可以对返回的记录数进行限制，少量的回表操作性价比还是很高的。无论怎样，还是要通过实际的测试还有查询优化器提供的信息来进行相应调整。

索引创建和使用的禅道

只为用于查询、排序或者分组的字段创建索引
考虑索引字段值的分布占比情况（性别很显然不适合嘛）
索引字段的值尽量小
无论是聚簇索引还是非聚簇索引，都会占用一定的空间，虽然相较于用户记录来说比较小，但是也不容忽视。较小的值类型可以节省空间，还可以加快比较速度。特别是聚簇索引需要注意这一点，因为其他索引都会中都会有对应主键值的副本
使用前缀索引
类似于邮箱这种，@前面的字符基本上就是唯一的，我们在建立索引的时候，就可以指定一下索引的长度。节约空间的同时也能加快匹配速度。但是，这种索引应用在排序操作上，因为它是不完整的。
优先进行覆盖索引的查询
由于索引中只存储了索引列的值和主键值，所以我们在结果返回的时候，最好也是只返回这索引字段的值和主键值，就不用进行回表查询了。但是这个还是要根据具体的业务来定的。
让索引列名保持纯粹
不要对其进行函数操作或者一些其他的运算操作，比如+ - * /：
```
select * from table where id / 2 > 2;
```
这样是会不走索引的，改成下面这样就行了：
```
select * from table where id > 2 * 2;
```
查询条件中，不要让索引字段参与运算。
主键保持递增性
中间的记录插入可能会造成页分裂，意味着性能的损耗
避免冗余和重复索引

总结

今天的内容主要是索引使用的一些“唠叨”。

2025/01/30

writeBy kaiven