【MySQL】深入学习B+索引的使用

缺乏、安全感 2024-03-29 15:55 64阅读 0赞

#### 文章目录 ####

*  1. 前言
 *  2. 索引的代价
 *   *  2.1 空间上的代价
     *  2.2 时间上的代价
 *  3. B+树索引的使用
 *   *  3.1 提取正确的扫描区间
     *   *  3.1.1 所有搜索条件都可以生成合适的扫描区间的情况
         *  3.1.2 有的搜索搜索条件不能生成合适的扫描区间
         *  3.1.3 从复杂的搜索条件中找出扫描区间
         *  3.1.4 使用联合索引执行查询时对应的扫描区间
     *  3.2 索引用于排序
     *   *  3.2.1 使用联合索引进行排序的注意事项
         *  3.2.2 不能使用索引进行排序的几种情况
     *  3.3 索引用于分组
     *  3.4 回表的代价
     *  3.5 更好地创建和使用索引
     *  3.6 为列前缀建立索引
     *  3.7 覆盖索引
     *  3.8 新插入记录时主键大小对效率的影响

> 本文参考：[《MySQL是怎样运行的：从根儿上理解 MySQL》——作者：小孩子4919][MySQL_ MySQL_4919]

## 1. 前言 ##

学习数据库的同学应该都知道，索引是可以提高MySQL的查询效率的，但是吧，这个索引可不能乱加，如果加了一些没用的索引，不仅会浪费了空间并且还会浪费了时间。因为索引本身就是一个B+树，MySQL在进行增删改查的时候，都需要维护所有的B+索引树，因此可能会带有空间和时间上的损耗。

--------------------

## 2. 索引的代价 ##

前言这里提到过一下，索引的代价，无非就是下面两种：

1.  **空间上的代价**
2.  **时间上的代价**

--------------------

### 2.1 空间上的代价 ###

每一个索引，其实本质就是一个B+索引树。

而每一个B+树的每一个节点都是一个数据页。每一个数据页都会默认占用16KB的存储空间。

假如该B+索引树非常大的话，那这颗树就由很多数据页组成，因此会占用很大的内存空间。

--------------------

### 2.2 时间上的代价 ###

作为一名CRUD工程师，我们的工作无非就是对数据库的记录进行CRUD。

**每当我们对表中的记录进行CRUD的时候，都需要修改每一个B+树索引。**

而CRUD的操作，就可能会对节点和记录的排序造成破坏。

> ***为什么说是可能呢？***
> 
> 就聚簇索引来说，**它是使用主键值的大小进行记录和页排序的。**
> 
> 比如现在有主键值为【1，5，6】的三条记录。
> 
> **如果主键值不是递增的话**，比如插入一条主键值为2的记录，那么在聚簇索引中就需要插入到1的后面，就会对节点和记录的排序造成破坏。
> 
> **但是如果主键值是递增的话**，插入的主键值就应该为7，插入到6的后面，就不会对节点和记录的排序造成破坏

并且，需要注意的是，在执行查询语句前，会先生成一个执行计划，**一条查询语句在执行过程中最多使用一个二级索引（当然也有例外）**。

在生成执行计划的时候，需要分析使用不同索引执行查询时所需的成本，最后选取成本最低的那个索引执行。

**但是如果索引太多，就可能导致成本分析过程耗时过长，从而影响查询语句执行效率。**

--------------------

## 3. B+树索引的使用 ##

这里先准备一个表

CREATE TABLE single_table(
    	id INT NOT NULL AUTO_INCREMENT,
        key1 VARCHAR(100),
        key2 INT,
        key3 VARCHAR(100),
        key_part1 VARCHAR(100),
        key_part2 VARCHAR(100),
        key_part3 VARCHAR(100),
        common_field VARCHAR(100),
        PRIMARY KEY(id),
        KEY idx_key1(key1),
        UNIQUE KEY uk_key2(key2),
        KEY idx_key3(key3)
        KEY idx_key_part(key_part1, key_part2, key_part3)
    )Engine=InnoDB CHARSET=utf8

接着，需要认清两个定义：**扫描区间和边界条件**。

接下面这条SQL语句来说

SELECT * FROM single_table WHERE id >= 2 AND id <= 100

这个语句是想查询id值在\[2，100\]区间中所有聚簇索引记录。可以通过聚簇索引对应的B+树快速定位id为2的那条记录。然后沿着记录所在的单向链往后扫描，直到id不在\[2，100\]区间中为止。

其中，**我们把待扫描记录的区间称为扫描区间，把形成这个扫描区间的搜索条件称为这个扫描区间的边界。**

并且，**把只包含一个值的扫描区间称为单点扫描区间，把巴汗多个值的扫描区间称为范围扫描区间。**

但是，并不是所有搜索条件都可以成为边界条件。

SELECT * FROM single_table WHERE key1 < 'a' AND key3 > 'z' and common_field = 'abc'

在这个SQL语句中，如果使用`idx_key1`索引执行查询，那么扫描区间就是\[-∞，‘a’\]，形成该扫描区间的边界条件就是`key1<'a'`，而`key3>'z' and common_field = 'abc'` 就是普通的搜索条件，**这些普通搜索条件需要获取到`idx_key1`的二级索引记录后，再执行回表操作，在获取到完整的用户记录后才能判断是否成立。**

--------------------

### 3.1 提取正确的扫描区间 ###

#### 3.1.1 所有搜索条件都可以生成合适的扫描区间的情况 ####

**在使用某个索引执行查询的时候，有时每一个小的搜索条件都可以生成一个合适的扫描区间来减少需要扫描的记录数量。**

SELECT * FROM  single_table WHERE key2 > 100 AND key2 > 200

如果使用`idx_key2`执行查询，**两个小的搜索条件是使用AND操作符连接的，所以最终的扫描区间就是对这两个搜索条件的扫描区间取交集后的结果。**

![image-20230116135007230][]

**因此对应的扫描区间为(200，+∞)，边界条件就是key2>200**

但是如果AND操作符改为OR操作符。

SELECT * FROM  single_table WHERE key2 > 100 OR key2 > 200

OR也就是意味着取各个扫描区间的交集。

![image-20230116135323602][]

**对应的扫描区间就是(100，+∞)，形成的扫描区间的条件就是key2 > 100**

--------------------

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

**在使用某个索引执行询时， 某个小的搜索条不能生成合适的扫描区间来减少需要扫描记录数量。**

SELECT * FROM  single_table WHERE key2 > 100 AND common_field = ‘abc’

在使用`uk_key2`执行查询的时候，很显然搜索条件key>100可以形成扫描区间（100， +∞），但是`uk_key2`的二级索引不按照`common_field`进行排序。**所以单凭搜索条件`common_field = ‘abc’`并不能减少需要扫描的二级索引记录数量。也就是单凭单凭搜索条件`common_field = ‘abc’`的扫描区间为（-∞，+∞）。**

**于是，使用`uk_key2`执行查询是，扫描区间为（100，+ ∞），形成该扫描区间的条件为key2>100。**

将AND操作符改为OR操作符。

SELECT * FROM  single_table WHERE key2 > 100 OR common_field = ‘abc’

根据上面说的，`common_field`这个字段用不上，因此，将该索引条件改为TRUE

SELECT * FROM  single_table WHERE key2 > 100 OR TRUE

接着化简

SELECT * FROM  single_table WHERE TRUE

可见，对应的扫描区间就是（-∞，+∞)，也就是需要扫描`uk_key2`的所有二级索引记录，并且对于获取到的每一条二级索引记录都需要进行回表操作，这个代价肯定比执行全表扫描代价大。在这种情况下，我们是不考虑用`uk_key2`执行查询的。

--------------------

#### 3.1.3 从复杂的搜索条件中找出扫描区间 ####

在使用SQL中，肯定会用到一些复杂的SQL，那么想要定位出对应的扫描区间那该怎么办呢？

SELECT * FROM  single_table WHERE
    (key1 > 'xyz' AND key2 = 748) OR
    (key1 < 'abc' AND key1 > 'lmn') OR
    (key1 LIKE '%suf' AND key1 > 'zzz' AND (key2 < 8000 OR common_field = 'abc'));

像这种复杂SQL，我们需要按照以下套路分析一下

1.  首先查看 `WHERE`子句中的搜索条件都涉及了哪些列，以及我们为哪些列建立了索引这个查询语句的搜索条件涉及了 `key1`、`key2`、`common_field`这3个列，其中 `key1`列有普通的二级索引`idx_key1`，`key2`列有唯一二级索引`uk_key2`。
2.  对于那些可能用到的索引，分析它们的扫描区间。

> **假使用`idx_key1`执行查询**

首先，先把不能不能形成合适的扫描区间的搜索条件展示移除，也就是将它们替换成TRUE

化简后

SELECT * FROM  single_table WHERE
    (key1 > 'xyz') OR
    (key1 < 'abc' AND key1 > 'lmn') OR
    (key1 > 'zzz' );

> ***咦，为什么`key1 LIKE '%suf'`也不能形成合适的搜索条件呢？***
> 
> 这是因为LIKE操作符号比较特殊，只有匹配完整的字符串或者匹配字符串前缀时才产生合适的扫描区间。

由于`key1 < 'abc' AND key1 > 'lmn'`永远为FALSE

继续化简可得

SELECT * FROM  single_table WHERE
    (key1 > 'xyz') OR
    (key1 > 'zzz' );

**由于两个搜索条件用OR连接，意味着取交集。**

最后化简可得

SELECT * FROM  single_table WHERE (key1 > 'xyz');

**因此扫描区间为（‘xyz’， +∞），边界条件为key1 > ‘xyz’。**

> ***假如使用`uk_key2`执行查询呢？***

化简可得

SELECT * FROM  single_table WHERE ( key2 = 748) OR TRUE

再化简

SELECT * FROM  single_table WHERE TRUE

**因此，扫描区间为（-∞，+∞）**

**也就是需要扫描`uk_key2`的全部二级索引记录，针对获取到的每一条二级索引记录还要进行回表操作。这不是得不偿失么!所以在这种博况下是不会使用 `uk_key2`索引的。**

--------------------

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

以`single_table`的`idx_key_part`联合索引为例，它的B+索引树如下图所示

![image-20230116143335335][]

比如执行下面的语句Q1

SELECT * FROM single_table WHERE key_part1 = 'a'

**二级索引记录是按照`key_part1`列排列的，所以符合`key_part1=‘a’`条件的所有记录肯定相连的。**

**因此可以先定位符合`key_part1=‘a’`的第一条记录，沿着这条记录往后扫描，直到某条记录不符合`key_part1=‘a’`为止。**

![image-20230116172754779][]

也就是扫描区间是\[‘a’，‘a’\]，形成这个扫描区间的边界条件就是`key_part1=‘a’`

对于下面的SQL语句Q2

SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b'

**二级索引记录是按照`key_part1`列排列的，在`key_part1`相同的情况下再按照`key_part2`进行排序，所以符合`key_part1 = 'a' AND key_part2 = 'b'`这个条件的结果再二级索引记录中肯定是相连的。**

**在Q2中，扫描区间为\[(‘a’，‘b’)，(‘a’，‘b’)\]，边界条件就是`key_part1 = 'a' AND key_part2 = 'b'`**

![0cc4b14f6bbcd8354d47b90913a373c0.png][]

**同理，对于下面SQL语句Q3来说规律是一样的，就不再多说了**

SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c'

对于Q4查询语句来说

SELECT * FROM single_table WHERE key_part1 < 'a'

**这个和Q1很像，二级索引记录是按照`key_part1`列排列的，所以符合`key_part1<‘a’`条件的所有记录肯定相连的。**

![image-20230116174124136][]

对于Q5查询语句来说

SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 > 'a' AND key_part3 < 'd'

**由于二级索引记录是先按照 `key_part1`列的值进行排序的，在 `key_part1`列的值相等的情况下再按照`key_part2`列进行排序。那么`key_part1 = 'a' AND key_part2 > 'a' AND key_part3 < 'd'`条件下的二级索引记录肯定是相邻的。**

![image-20230116174057321][]

对于查询语句Q6来说

SELECT * FROM single_table WHERE key_part2 = 'a'

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

对于查询语句Q7来说

SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part3 = 'c'

**由于二级索引记录是按照`key_part1`列的值排序的，但是在`key_part1`相同的情况下，并不是直接按照`key_part3`列进行排序的，也就是不能根据`key_part3`进一步减少需要扫描的记录数量。**

**所以对应的扫描空间是\[‘a’，‘a’\]，边界条件是`key_part1 = 'a'`，与`key_part3 = 'c'`无关。**

对于查询语句Q8来说，

SELECT * FROM single_table WHERE key_part1 < 'b' AND key_part2 = 'a'

**由于二级索引记录是按照`key_part1`列的值排序的，所以`key_part1<'b'`条件的二级索引记录肯定是相邻的。但是对于`key_part1<'b'`二级索引记录，并不是按照`key_part2`进行排序的，所以不能根据`key_part2`减少需要扫描的记录数量**

对于查询语句Q9来说

SELECT * FROM single_table WHERE key_part1 <= 'b' AND key_part2 = 'a'

**很Q8很像，对于`key_part1 < 'b`的二级索引记录并不是按照`key_part2`进行排序的。但是对于`key_part1 = 'b'`的索引记录来说，是按照`key_part2`进行排序的，`key_part2 = ‘a’`条件可以减少二级索引记录范围**

![image-20230116175447504][]

--------------------

### 3.2 索引用于排序 ###

**一般情况下，需要将记录加载到内存中，然后再使用一些排序算法在内存中对这些记录进行排序。**

但是有时候可能太大以至于无法在内存中进行排序，此时就需要暂时借助磁盘空间来存放中间结果，在排序操作完成后再把排序结果集返回客户端。

--------------------

#### 3.2.1 使用联合索引进行排序的注意事项 ####

1.  **ORDER BY子句后面的列的顺序也必须按照索引列的顺序给出。**
    
    1.  因为如果颠倒顺序就不能使用索引，原因和联合索引中页面和记录的排序规则是固定的。
    2.  仅对联合索引的索引列中左边连续的列进行排列也是可以利用B+索引的。
2.  **当联合索引的索引列左边连续的列为常量时，也可以使用联合索引对右边的列进行排序**

--------------------

#### 3.2.2 不能使用索引进行排序的几种情况 ####

1.  **ASC、DESC混用**
    
    1.  使用索引进行排序的场景，要求各个排序列的规则是一直的，要么都是升序，要么都是降序
    2.  因为混用的华需要较为复杂的算法从索引中读取记录，不能高效使用索引。
    3.  **注意，这里说的是不能高效使用，也就是用索引也能实现，只是效率慢而已。**
2.  **排序列包含非同一个索引的列**
    
    1.  有时用来排序的多个列不是同一个索引中的，这种情况也不能使用索引进行排序。
3.  **排序列是某个联合索引的索引列，但是这些排序列在联合索引中并不连续**
4.  **用来形成扫描区间的索引列与排序列不同**
    
    1.  也就是这种情况
        
            SELECT * FROM single_table WHERE key1 = 'a' ORDER BY key2 LIMIT 10
    2.  用`key1 = 'a'`形成扫描区间，也就是用`idx_key1`执行该查询时，进扫描key1值为‘a’的二级索引记录即可。此时无法使用`uk_key2`
5.  **排序列不是以单独列名的形式出现在 ORDER BY 子句中**
    
    1.  **必须保证索引列是以单独列名的形式出现！！！**
    2.  像这种就不行了
        
            SELECT * FROM single_table ORDER BY UOOER(key1) LIMIT 10

--------------------

### 3.3 索引用于分组 ###

像这样这样的查询语句

SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key1 = 'a' GROUP BY key_part1, key_part2, key_part3

如果没有 `idx_key_part`索引，就得建立一个用于统计的临时表，在扫描聚簇索引的记录时将统计的中间结果填入这个临时表。当扫描完记录后，再把临时表中的结果作为结果集发送给客户端。如果有了索引`idx_key_part`，恰巧这个分组顺序又与`idx_key_part`的索引列的顺序是一致的，而 `idx_key_part`的二级索引记录又是按照索引列的值排好序的，这就正好了。所以可以直接使用`idx_key_part`索引进行分组，而不用再建立临时表了。

--------------------

### 3.4 回表的代价 ###

1.  **造成大量随机的I/O**
    
    1.  **`idx_key1`在扫描某一个区间中二级索引所在的页面号回尽可能相邻，尽管不相邻，起码一个页面可以存放多条记录，也就是执行完一次I/O后，就可以把很多二级索引记录从磁盘加载到内存。**
    2.  但是在扫描该区间中的二级索引记录对应的id值是毫无规律的。
    3.  **每一次读取二级索引记录都需要根据二级索引记录对应id进行回表。**
    4.  如果对应的聚簇索引记录所在页面不在内存中，就需要将该页面从硬盘加载到内存。
    5.  由于要读取很多id值并不连续的聚簇索引记录，而且这些聚簇索引分布在不同数据页，因此数据页页号也毫无规律

> ***那么什么时候采用全表扫描，什么时候采用二级索引+回表呢？***
> 
> 这是查询优化器做的事情，**查询优化器会事先针对表中的记录计算一些统计数据**，然后再利用这些统计数据或者访问表中的少量记录来计算需要执行回表操作的记录数。
> 
> **如果需要执行回表操作的记录数越多，就越倾向于使用全表扫描**，否则使用二级索引+回表。
> 
> **但是如果给查询条件指定LIMIT子句来限制查询返回的记录数，这可能会让查询优化器更倾向于使用二级索引+回表方式进行查询。**
> 
> 原因是记录越少，性能越高。
> 
> **同时，对于结果需要进行排序的查询，如果再采用二级索引执行查询需要执行回表操作的记录特别多，也倾向于使用全表扫描+文件排序的方式执行查询。**

--------------------

### 3.5 更好地创建和使用索引 ###

1.  只为用于搜索、排序或分组的列创建索引
    
    1.  对于出现在查询列表的列就没有必要创建索引了
2.  考虑索引列中不重复值个数
    
    1.  我们在为某个列创建索引时，需要考虑该列中**不重复值的个数占全部记录条数的比例**。
    2.  **如果比例太低，则说明该列包含过多重复值，那么在通过二级索引+回表的方式执行查询时，就有可能执行太多次回表操作**
3.  索引列的类型尽量小
    
    1.  **如果想要对某个整数类型的列建立索引，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型**
    2.  这样索引占用的内存就越少，一个页就能放下更多的记录，磁盘I/O带来的性能损耗也越少

--------------------

### 3.6 为列前缀建立索引 ###

**为字符串所在的列建立索引时，也就意味着对应B+树中，需要将该列的完整字符串存储起来。**

字符串越长，占用的存储空间就越大。

**我们可以设置一个巧妙的方法，就是将字符串的前几个字符存放在所以索引中。**

当列中存储的字符串包含的字符较多时候，这种方法明显减少索引的大小。

**不过，这样做的话就不能使用该索引完成排序需求了！！！！！**

至于为什么，相信不说大家也知道。

--------------------

### 3.7 覆盖索引 ###

**把这种索引中已经包含所有需要读取的列的查询方式称为覆盖索引**。排序操作也优先使用覆盖索引进行查询，比如下面这个查询语句:

SELECT key1 FROM single_table  ORDER BY key1

虽然这个查询语句中没有 LIMIT 子句，但是由于可以采用覆盖索引，所以查询优化器会直接使用`id_key1`索引进行排序，而不需要执行回表操作。

--------------------

### 3.8 新插入记录时主键大小对效率的影响 ###

**如果新插入记录的主键值是依次增大的话，则每插满一个数据页就换到下一个数据页继续插入。**

但是如果主键值随机，这就难搞了

假设某个数据页存储的聚簇索引记录**已经满了**，它存储的主键值在1~100之间

![image-20230116190331622][]

再插入一条主键值为9的记录

**这就需要把当前页面分裂成两个页面**，把本页中的一些记录移动到新创建的页中。这意味着性能损耗！！所以，如果想尽量避免这种无谓的性能损耗，最好让插入记录的主键值依次递增。就像 `singletable`表的主键id列具有`AUTO_INCREMENT`属性那样，MySQL会自动为新插入的记录生成递增的主键值。

--------------------

[MySQL_ MySQL_4919]: https://book.douban.com/subject/35231266/
[image-20230116135007230]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/87c8982f04214ebfbc627652f6c5677d.png
[image-20230116135323602]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/fe1f7ca59ebd4f17b5b93a81f0442f8b.png
[image-20230116143335335]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/ec33e3ab187a4709be2ed974d442e8d7.png
[image-20230116172754779]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/998d0fea6abd458e8cfbc4ba94c0c83c.png
[0cc4b14f6bbcd8354d47b90913a373c0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/21ff458b8f75437fab6f15aff712f23d.png
[image-20230116174124136]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/ca96ad809f39452ca542f78479f3bfa1.png
[image-20230116174057321]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/8c18f783e38242d18d50c7a6b522de8b.png
[image-20230116175447504]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/6b13f208878040a6ba53b6446f396946.png
[image-20230116190331622]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/a9b55ead3b7a4c699f1f9d246b3aa74d.png