MySQL中B+树索引的应用场景大全

深碍√TFBOYSˉ_ 2022-10-10 12:58 177阅读 0赞

### 文章目录 ###

*  一、本文所用到的数据表
 *  二、全值匹配
 *  三、最左前缀原则
 *  四、匹配列的前缀(比如like 'a%')
 *  五、匹配列的中间字符或者后缀(比如like '%a%'，like '%com')
 *  六、匹配范围查找，确定扫描区间和边界
 *  七、IS NULL、IS NOT NULL、!=、BETWEEN、IN等等不能用索引吗？必须辟谣！
 *  八、隐式转换导致全表扫描不走索引(这个坑容易忽视)
 *  九、使用联合索引的场景
 *  十、索引条件下推（Index Condition Pushdown，简称ICP）
 *   *  1.索引下推表象理解
     *  2.索引下推在存储引擎层和server层深入分析
     *  3.范围查找也会使用到Using index condition，需要注意！
 *  十一、索引用于排序(explain分析案例，精华！！)
 *   *  1.在使用联合索引时需要注意，order by子句后面的列的顺序也必须按照索引的顺序来。
     *  2.当记录数很多却不用limit限制查询返回的记录数时，极有可能不走索引
     *  3.asc、desc混用无法完全利用索引，除非你的数据库是MySQL 8.0+
     *  4.排序中不能包含非同一索引的列，否则不走索引
     *  5.排序列是某个联合索引的索引列，但是这些排序列在联合索引中并不连续，则不走索引
     *  6.排序列是索引列，但是使用了函数，则不走索引
 *  十二、索引用于分组
 *  十三、如何更好的创建和使用索引
 *   *  1.只为用于搜索、排序或分组的列创建索引
     *  2.考虑索引列中不重复值的个数
     *  3.索引列的类型尽量小
     *  4.为索引字符串值的前缀建立索引
     *  5.让索引在where子句的一侧单独出现
     *  6.保证主键的插入顺序
     *  7.如有必要，尽量考虑使用索引覆盖
     *  8.避免创建冗余重复的索引

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

# 一、本文所用到的数据表 #

本篇是讲`B+`树的应用场景，也就是我们平时在写`sql`语句时需要思考的问题，这里重点总结一下

首先列出建表语句，后面例子均在此表基础上举例说明。

CREATE TABLE demo_info(
        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=utf8mb4;

在这里你需要观察到的是哪些列加了索引就可以。

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

# 二、全值匹配 #

如果我们的搜索条件中的列和索引列对应的话（列的字段和个数要相同），这种情况就称为全值匹配

SELECT * FROM demo_info WHERE key_part_1 = 'a' AND key_part_2 = 'b' AND key_part_3 = 'c';

可以想象一下这个查询过程：

*  因为`B+`树的数据页和记录先是按照`key_part_1`列的值进行排序的，所以先可以很快定位`key_part_1`列的值是`'a'`的记录位置。
 *  在`key_part_1`列相同的记录里又是按照`key_part_2`列的值进行排序的，所以在`key_part_1`列的值是`'a'`的记录里又可以快速定位`key_part_2`列的值是`'b'`的记录。
 *  如果`key_part_1`和`key_part_2`列的值都是相同的情况下，那记录是按照`key_part_3`列的值排序的，所以联合索引中的三个列都可能被用到。

有的同学也许有个疑问，`WHERE`子句中的几个搜索条件的顺序对查询结果有啥影响么？也就是说如果我们调换`key_part_1`、`key_part_2`、`key_part_3`这几个搜索列的顺序对查询的执行过程有影响么？比方说写成下边这样

SELECT * FROM demo_info WHERE key_part_2 = 'b' AND key_part_3 = 'c' AND key_part_1 = 'a';

答案是没有影响。**只要你把联合索引的每个索引列都用到了，随意更换顺序是没有影响的，查询优化器会自动调整。** 但是如果没有全部用到，那么就必须按照最左前缀原则使用。

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

# 三、最左前缀原则 #

在我们的搜索语句中也可以不用包含全部联合索引中的列，只包含左边的就行，比方说下边的查询语句：

SELECT * FROM demo_info WHERE key_part_1 = 'a';

或者包含多个左边的列也行

SELECT * FROM demo_info WHERE key_part_1 = 'a' AND key_part_2 = 'b';

为什么搜索条件中必须出现左边的列才可以使用到这个`B+`树索引呢？

因为`B+`树的数据页和记录先是按照`key_part_1` 列的值排序的，在`key_part_1` 列的值相同的情况下才使用`key_part_2`列进行排序，也就是说`key_part_1` 列的值不同的记录中，`key_part_2` 的值可能是无序的。根据`key_part_2` 直接去无序查找全表，`innodb`肯定不会那么笨啊。

来张图，举个例

![20210628165253805.png][]

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

# 四、匹配列的前缀(比如like ‘a%’) #

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

比较字符串的大小其实就相当于依次比较每个字符的大小。字符串的比较过程如下

1.  先比较字符串的第一个字符，第一个字符小的那个字符串就比较小。
2.  如果两个字符串的第一个字符相同，再比较第二个字符，第二个字符比较小那个字符串就比较小，以此类推。

如果这个列是索引列，那么字符串前缀相同的记录在单链表中肯定是相邻的。比如搜索条件为`key1 LIKE 'a%'`，对于非聚集索引来说，所有字符串前缀为`'a'`的记录肯定是相邻的。所以我们只要沿着单链表往后扫描即可，直到字符串前缀不为`'a'`为止。  
所以，`key1 LIKE 'a%'`的扫描区间相当于`['a', 'b')`

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

# 五、匹配列的中间字符或者后缀(比如like ‘%a%’，like ‘%com’) #

如果查询中间包含的某个字符串，比如

SELECT * FROM demo_info WHERE name LIKE '%a%';

`MySQL`就无法快速定位记录位置了，因为字符串中间有`'a'`的字符串并没有排好序，所以只能全表扫描了。

如果查询后缀包含某个字符串，你会怎么做呢？

假设有个索引列`url`，想查询以`com`为后缀的网址的话可以这样写查询条件，`WHERE url LIKE '%com'`，但是这样的话无法使用该`url`列的索引。

我们可以**把后缀查询改写成前缀查询**，不过我们就得把表中的数据全部逆序存储一下，这样再查找以`com`为后缀的网址时搜索条件便可以这么写：`WHERE url LIKE 'moc%'`，这样就可以用到索引了。这样即使是千万上亿级别的数据量，也可以快速查找而不是全表扫描。如果要查看正确的`url`，只需要将此逆序一下就可以了。

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

# 六、匹配范围查找，确定扫描区间和边界 #

为了避免往上翻，这里再次列出建表语句

并不是所有的搜索条件都可以成为边界条件的，如下

select * from demo_info where key1 < 'a' and key3 > 'z' and common_field = 'abc'

`key1`和`key3`但是单独的索引列，不是联合索引。

如果使用`idx_key1`执行查询，那么相应的扫描区间是`(-∞, 'a')`，非聚集索引的叶子结点上只有`key1`和`id`，所以`key3 > 'z' and common_field = 'abc'`就是普通的搜索条件，这些普通的搜索条件是回表获得完整的用户记录后才判断它们是否成立。

如果使用`idx_key3`执行查询，那么相应的扫描区间是`('z', +∞)`，而`key1 < 'a' and common_field = 'abc'`就是普通的搜索条件，这些普通的搜索条件是回表获得完整的用户记录后才判断它们是否成立。

注意：对于`B+`树索引来说，只要索引列和常数使用了`=`、`<=>`、`IN`、`NOT IN`、`IS NULL`、`IS NOT NULL`、`>`、`<`、`>=`、`<=`、`BETWEEN`、`!=`（也就是`<>`）或者`LIKE`操作符连接起来，就可以产生所谓的扫描区间。

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

# 七、IS NULL、IS NOT NULL、!=、BETWEEN、IN等等不能用索引吗？必须辟谣！ #

**IS NULL的例子**

explain select * from demo_info where key1 is null

![20210629181210782.png][]

虽然没有`key1`为`null`的记录，但还是走了索引。

**IS NOT NULL的例子**

explain select * from demo_info where key1 is not null limit 5;

![20210629181545234.png][]

因为这里所有记录的`key1`都不为`null`，为了避免全表扫描，我这里限制一下返回结果集数量。因为所有的结果都满足`is not null`，所有记录都会回表，那么优化器会选择全表扫描，而不是多此一举走非聚集索引+回表的方式。

**!= 的例子**

explain select * from demo_info where key1 != 'a' limit 5;

![20210629182008944.png][]

这里也走了索引，限制结果集的理由同上一个例子。

**between的例子**

explain select * from demo_info where key1 between 'a' and 'd' limit 5;

![20210629182645376.png][]

走索引的理由同上。

多的例子就不举了，否则比较冗余，直接上结论。

结论：对于`B+`树索引来说，只要索引列使用了`=`、`<=>`、`IN`、`NOT IN`、`IS NULL`、`IS NOT NULL`、`>`、`<`、`>=`、`<=`、`BETWEEN`、`!=`（也就是`<>`）或者`LIKE`（只能是`'a%'`前缀字符形式）操作符连接起来，就可以使用到索引，如果你发现没走索引，请检查自己的结果集是否过多，限制一下结果集数量。

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

# 八、隐式转换导致全表扫描不走索引(这个坑容易忽视) #

这也是开发中经常可能遇到的坑。  
比如，你明明想利用索引查询`key1='1'`的记录

explain select * from demo_info where key1 = '1';

![20210629095449274.png][]

结果！你却写成了`key1 = 1`;这个`1`可是`number`类型，不是`varchar`了，当字段类型和查询条件数据类型不一致的时候，如果没有函数转换，则会隐式转换，如果不能隐式转换则报错。这里`varchar`的`'1'`可以顺利转换`number`类型的`1`，结果转换了类型，所以用不到索引。

explain select * from demo_info where key1 = 1;

![20210629095738450.png][]

注意: `Server`层包括连接器、查询缓存（`MySQL 8.0`剔除）、分析器、优化器、执行器等，涵盖`MySQL`的大多数核心服务功能，以及**所有的内置函数（如日期、时间、数学和加密函数等）**，所有跨存储引擎的功能都在这一层实现。  
  而非聚集索引的查询和回表是在存储引擎层，如果要用函数判断，必须等到把完整记录返回给`Server`层，这里隐式转换用到函数就在`Server`层，在`Server`层就用不到索引了，所以是全表扫描。

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

# 九、使用联合索引的场景 #

我们前面说了，有联合索引`KEY idx_key_part(key_part1, key_part2, key_part3)`  
**eg1：**

select * from demo_info where key_part1 = 'a' and key_part2 = 'b';

对于这个联合索引，先按照`key_part1`排序，在`key_part1`列的值相等的情况下再按照`key_part2`列进行排序，所以符合`key_part1 = 'a' and key_part2 = 'b'`条件的非聚集索引记录一定是相邻的。

我们可以定位到符合`key_part1 = 'a' and key_part2 = 'b'`条件的第一条记录，然后回表，接着定位满足`key_part1 = 'a' and key_part2 = 'b'`的第二条记录，然后回表，就这样沿着记录所在的单链表往后扫描，直到不满足`key_part1 = 'a'` 或者`key_part2 = 'b'`条件为止。（每找到一条满足条件的记录都会进行回表操作）

> 如果你了解`MRR`，并对这里产生了疑问，可以见这里[MRR的说明][MRR]，我们讨论问题一般都会忽略`MRR`

**eg2：**

select * from demo_info where key_part1 = 'a' and key_part2 = 'b' and key_part3 = 'c';

与上面类似，先按照`key_part1`排序，在`key_part1` 的值相等的情况下再按照`key_part2`排序，在`key_part1`和`key_part2`的值都相等的情况下，再按照`key_part3`排序。所以符合`key_part1 = 'a' and key_part2 = 'b' and key_part3 = 'c'`的非聚集索引的记录一定是相邻的。（如果有满足的多条记录）

定位到满足`key_part1 = 'a' and key_part2 = 'b' and key_part3 = 'c'`的第一条记录后，然后进行回表，接着沿着单链表往后扫描，直到找到不满足`key_part1 = 'a'` 或者`key_part2 = 'b'` 或者`key_part3 = 'c'`的记录为止。（每找到一条满足条件的记录都会进行回表操作）

**eg3:**

select * from demo_info where key_part2 = 'a';

非聚集索引不是按照`key_part2`的值进行排序的。无法通过`key_part2 = 'a'`这个条件来减少扫描的记录数量，只能全表扫描，不会使用 `idx_key_part`这个联合索引。

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

# 十、索引条件下推（Index Condition Pushdown，简称ICP） #

## 1.索引下推表象理解 ##

上面说到，有联合索引`KEY idx_key_part(key_part1, key_part2, key_part3)`

select * from demo_info where key_part1 = 'a' and key_part3 = 'c'

页中的记录如下  
![20210626113943427.png][]

**先来简单说明一下，不在`server`层和存储引擎层分析，后面会分析。**

由于非聚集索引`idx_key_part`是先按照`key_part1`来排序的，但是仅仅在`key_part1`相同的情况下，`key_part3`却可能是无序的，如上图。这样的话，这个联合索引就只能使用到`key_part1`这个索引列了，在`['a', 'a']`区间内的所有非聚集索引的记录进行依次扫描。

这里例子需要注意！有人说是先找到`key_part1 = 'a'`的第一条记录，然后回表获得完整的用户记录之后，接着判断`key_part3='c'`是否成立。每找到一条满足`key_part1 = 'a'`条件的记录都会进行回表操作，回表后再判断`key_part3='c'`是否成立。**其实不对！**

在使用`idx_key_part`联合索引执行查询时，虽然不能直接用到`key_part3`，但是还是**包含**`key_part3`列的。因此每当从`idx_key_part`索引的扫描区间`['a', 'a']`中获取到一条非聚集索引记录时，我们可以先判断这条二级索引记录是否符合`key_part3='c'`条件。如果符合该条件再执行回表操作，不符合就不回表，然后跳到下一条非聚集索引记录继续上述判断。**这样可能减少因回表操作而带来的性能损耗，这种优化方式称为索引条件下推（Index Condition Pushdown，简称ICP）**。

虽然只能用到联合索引的一部分，利用后面的条件可以判断是否继续回表，从而加快查找速度。索引条件下推的特性是在`MySQL 5.6`中引入的，**该特性是默认开启**的。

## 2.索引下推在存储引擎层和server层深入分析 ##

前面说到，有联合索引`KEY idx_key_part(key_part1, key_part2, key_part3)`和普通索引`KEY idx_key1 (key1)`

explain select * from demo_info where key_part1 = 'a' and key_part3 = 'c' and key1 < 'b';

执行计划如下

![20210630155803518.png][]

`MySQL`分为`server`层和存储引擎层，`server`层和存储引擎层的交互是以记录为单位的。

1.  `server`层第一次开始执行查询，把条件`key_part1 = 'a'`交给存储引擎，让存储引擎定位符合条件的第一条记录
2.  存储引擎在非聚集索引`idx_key_part`中定位`key_part1 = 'a'`的第一条记录，我们看到`explain`语句的输出结果的`Extra`列有一个`Using index condition`的提示，这表明会将有关`idx_key_part`非聚集索引的查询条件放在存储引擎层判断，这个特性就是所谓的索引条件下推。很显然这里的`ICP`条件就是`key_part3 = 'c'`。`ICP`条件筛选后得到一条非聚集索引记录，根据这条记录的主键`id`去回表，把回表得到的这条完整的用户记录返回给`server`层

> 注意：筛选到一条非聚集索引记录后就去回表，而不是把所有满足条件的非聚集索引记录都拿到后去回表

1.  我们的执行计划输出的`Extra`列有一个`Using Where`的提示，意味着`server`层在接收到存储引擎层返回的一条记录之后，接着就要判断其余的`where`条件是否成立（就是再判断一下`key1 < 'b'`是否成立）。如果成立的话，就直接发送给客户端，否则就跳过该条记录。

> 发现一条记录符合条件就发送给客户端，客户端在接收完全部的记录之后再展示！

1.  接着`server`层向存储引擎层要求继续读刚才那条记录的下一条记录。
2.  每条记录的头信息中都有`next_record`的这个属性，所以可以快速定位到下一条记录的位置，然后继续判断`ICP`条件，接着回表，存储引擎把下一条完整的记录取出后就将其返回给`server`层。
3.  然后重复第`3`步的过程，直到存储引擎层遇到了不符合`key_part1 = 'a'`的记录，然后向`server`层返回了读取完毕的信息，这时`server`层将结束查询。

## 3.范围查找也会使用到Using index condition，需要注意！ ##

explain select * from demo_info where key_part1 <= 'a' limit 1;

![在这里插入图片描述][20210630165212179.png]  
我们可以看到这里的`Extra`有`Using index condition`。

但是将范围查询改为等值查询后，结果就变了

explain select * from demo_info where key_part1 = 'a' limit 1

![在这里插入图片描述][20210630165359554.png]

因为在`InnoDB`存储引擎层有特殊的处理方案，是不作为`ICP`条件进行处理的，所以不会用到`Using index condition`。

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

# 十一、索引用于排序(explain分析案例，精华！！) #

## 1.在使用联合索引时需要注意，order by子句后面的列的顺序也必须按照索引的顺序来。 ##

对于联合索引`KEY idx_key_part(key_part1, key_part2, key_part3)`，如下查询是用不到索引的

# 用不到联合索引的例子
    explain select * from demo_info where key_part3 <= 'b' order by key_part1, key_part2;

![20210626142424798.png][]

不管是`where`子句还是`order by`子句，想要用到索引，就得按照规则来，如下

explain select * from demo_info where key_part1 = 'a' and key_part2 = 'b' order by key_part3 limit 10;

![20210626142314172.png][]

## 2.当记录数很多却不用limit限制查询返回的记录数时，极有可能不走索引 ##

当我使用联合索引的条件去进行`order by`排序，结果却没有走索引，而是全表扫描

explain select * from demo_info order by key_part1, key_part2;

![20210626215814865.png][]

当我限制只返回一条记录的时候，一下就走到了联合索引

explain select * from demo_info order by key_part1, key_part2 limit 1;

![20210626215940224.png][]

其实这是查询优化器搞的鬼。查询优化器会事先针对表中的记录计算一些统计数据，然后利用这些统计数据，或者访问表中少量记录来计算需要执行回表的记录数，**如果需要回表的记录越多，查询优化器就越倾向于全表扫描，反之则使用非聚集索引+回表的方式。**

当你使用`limit`子句限制查询返回的记录数时，会让查询优化器更倾向于选择使用非聚集索引+回表的方式进行查询，因为回表的记录越少，性能提升越高。

注意：当你`order by` 主键`id`的时候，一定是走索引的，而且是走的`PRIMARY`索引，因为在聚集索引上就不存在回表了，不会使用全表扫描。

## 3.asc、desc混用无法完全利用索引，除非你的数据库是MySQL 8.0+ ##

首先说下`asc`和`desc`排序

`asc`升序排列大家知道，沿着页中单链表遍历即可。

`desc`降序排列就要注意了，你需要知道页中有`Infimum`\+`Supremum`记录，可以先简单理解为头指针和尾指针。首先从头指针处遍历到最后一条用户记录，接着从头指针遍历到倒数第二条记录，从头指针遍历到倒数第三条记录…

![20210626160141481.png][]

这样就能获取到倒序排列的结果集了，很显然，`asc`升序排列获取结果集要快于`desc`降序排列。

实际上，一个页中的记录分了很多组，页中有一块`Page Directory`的空间存放了叫"槽"的东西，槽中存放着每个分组内最后一条记录在页面中的地址偏移量。只要找到了最后一条记录，然后找到分组的第一条记录(上一个槽的下一条记录)，就可以在这个分组内小范围的遍历获取倒序的结果。这可比从页中第一条记录开始遍历获取倒序结果好的多，大大减少遍历时间。

如果非要`asc`和`desc`混合排序，我们来分析一下

explain select * from demo_info where key_part1 = 'a' order by key_part2, key_part3 desc;

按照`key_part2`升序排列，`key_part2`相同的情况下就按`key_part3`降序排列。

![20210626180846242.png][]

`MySQL 8.0`以前无法直接利用索引进行`asc`和`desc`混合排序，所以过程如下

1.  先筛选到满足`key_part1 = 'a'`的记录
2.  再按照单链表取出`key_part2`最小的记录(假设为`'b'`，有多条满足`key_part2 = 'b'`的记录)
3.  `key_part3`无法直接利用索引完成，将`key_part2 = 'b'`的记录取出进行一次文件排序，即无需全部回表获得完整记录后再排序，直接在非聚集索引进行文件排序（也称为外部排序，一般是归并排序）
4.  依次循环`1~3`过程，直到找到不满足`key_part1 = 'a'`的记录为止，最后回表取出的结果集就是有序的完整的用户记录。

![20210626181608228.png][]

`MySQL 8.0` 引入了`Descending Index`的特性，允许利用索引直接`asc`和`desc`混合排序。

但是这里用到的联合索引却是升序的`KEY idx_key_part(key_part1, key_part2, key_part3)`，`Using filesort`是因为这里要取出第二次按照`key_part3`降序排列，**索引声明是升序的，降序排列一定提示`Using filesort`**。当拿到`key_part2`相等的记录时，还要按照`key_part3`降序排一次（在满足条件的记录分组内小范围遍历获取倒序结果）

如果在`MySQL 8.0+`将`key_part3`为`desc`，这里索引改为`KEY idx_key_part(key_part1, key_part2, key_part3 desc)`，再次执行

explain select * from demo_info where key_part1 = 'a' order by key_part2, key_part3 desc;

![20210626191545113.png][]

结果是`asc`和`desc`完美的利用索引进行混合排序。而在`MySQL8.0`以下，索引声明`desc`是直接被忽略的。

## 4.排序中不能包含非同一索引的列，否则不走索引 ##

排序中用来排序的多个列不是同一索引是不能使用索引排序的。

前面说过，`key1`和`key2`不是联合索引  
`KEY idx_key1 (key1)`  
`UNIQUE KEY uk_key2 (key2)`

explain select * from demo_info order by key1, key2 LIMIT 1;

![20210627135843129.png][]

## 5.排序列是某个联合索引的索引列，但是这些排序列在联合索引中并不连续，则不走索引 ##

explain select * from demo_info order by key_part1, key_part3 LIMIT 1;

![20210627135843129.png][]

如果你的排序条件换成连续的，马上就能用到索引了

explain select * from demo_info order by key_part1, key_part2 LIMIT 1;

![20210627155054466.png][]

## 6.排序列是索引列，但是使用了函数，则不走索引 ##

explain select * from demo_info order by UPPER(key1) limit 1;

![20210627135843129.png][]

因为`key1`列是以`UPPER(key1)`函数调用的形式出现在`order by`子句中，所以不能使用`idx_key1`执行上述查询。

索引的查询和回表是在存储引擎层，如果要用函数判断，必须等到把完整记录返回给`Server`层，在`Server`层就用不到索引了，所以是全表扫描。

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

# 十二、索引用于分组 #

有时候我们为了方便统计表中的一些信息，会把表中的记录按照某些列进行分组。比如下边这个分组查询：

explain select key_part1, key_part2, key_part3, count(*) from demo_info group by key_part1, key_part2, key_part3;

![20210627161335341.png][]

这个查询语句相当于做了`3`次分组操作：

1.  先把记录按照`key_part1`值进行分组，所有`key_part1`值相同的所有记录划分为一组。
2.  将每个`key_part1`值相同的分组里的记录再按照`key_part2`的值进行分组，将`key_part2`值相同的记录放到一个小分组里，看起来就像在一个大分组里又细分了好多小分组。
3.  再将上一步中产生的小分组按照`key_part3`的值分成更小的分组，所以整体上看起来就像是先把记录分成一个大分组，然后把大分组分成若干个小分组，最后把若干个小分组再细分成更多的小小分组。
4.  针对那些小小分组进行统计，上面这个查询语句就是统计每个小小分组包含的记录条数。

如果没有`idx_key_part`索引，就得建立一个用于统计的临时表，在扫描聚集索引的记录时将统计的中间结果填入这个临时表。当记录扫描完毕后，再把临时表中的结果作为结果集发送给客户端。

如果有了索引`idx_key_part`，恰巧这个分组顺序又与`idx_key_part`的索引列的顺序是一致的，而`idx_key_part`的非聚集索引记录又是按照索引列的值排好序的，所以可以直接使用索引进行分组，不用再建立临时表了。

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

# 十三、如何更好的创建和使用索引 #

## 1.只为用于搜索、排序或分组的列创建索引 ##

只为出现在`where`子句中的列、连接子句中的连接列，或者出现在`order by`或`group by`子句中的列创建索引。而出现在`select`查询列表中的列就没必要建立索引了：

select common_field, key_part3 from demo_info where key1 = 'a';

像查询列表中的`common_field`、`key_part3`这两个列就没必要建立索引，我们只需要为出现在`where`子句中的`key1` 列创建索引就可以了。

## 2.考虑索引列中不重复值的个数 ##

比方说某个列包含值`2, 5, 8, 2, 5, 8, 2, 5, 8`，虽然有`9`条记录，但不重复值的个数是`3`个。也就是说，在记录行数一定的情况下，不重复值的个数越大，该列中的值越分散，不重复值的个数越小，该列中的值越集中。这个不重复值的个数指标非常重要，**不重复值的个数越多，可以称为区分度高或者筛选性好，区分度或者筛选性就是不重复值的个数与总个数的比值。** 这直接影响我们是否能有效的利用索引。

假设某个列不重复值的个数为`1`，也就是所有记录在该列中的值都一样，那为该列建立索引是没有用的，不管查什么都需要回表。 而且如果某个建立了非聚集索引的列的重复值特别多，那么使用这个非聚集索引查出的记录回表的次数越多，性能损耗越大，查询优化器可能就不会走这个非聚集索引了，改变为全表扫描。

所以结论就是：最好为不重复值的个数多的列建立索引，区分度低说明该列包含过多重复值，那么在非聚集索引+回表的方式执行查询时，就有可能执行太多回表操作，导致查询优化器选择全表扫描。

## 3.索引列的类型尽量小 ##

在定义表结构的时候，要显式的指定列的类型，以整数类型为例，有`TINYINT`、`MEDIUMINT`、`INT`、`BIGINT`这么几种，它们占用的存储空间依次递增，能表示的整数范围依次递增。如果我们想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如我们能使用`INT`就不要使用`BIGINT`，能使用`MEDIUMINT`就不要使用`INT`。因为数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以存放更多的记录，磁盘`I/O`带来的性能损耗就越小（一次页面`I/O`能将更多记录加载到内存中），读写效率就越高。

**这个建议对于表的主键来说更加适用**，因为不仅是聚集索引中会存储主键值，其他所有的非聚集索引的节点处都会存储一份记录的主键值，如果主键使用更小的数据类型，也就意味着能节省更多的存储空间。

## 4.为索引字符串值的前缀建立索引 ##

假设我们的字符串很长，那存储一个字符串就需要占用很大的存储空间。在需要为这个字符串列建立索引时，那就意味着在对应的`B+`树中的记录中，需要把该列的完整字符串存储起来，字符串越长，在索引中占用的存储空间越大。

索引列的字符串前缀其实也是排好序的，所以索引的设计者提出了个方案 — 只对字符串的前几个字符放到索引中，也就是说在非聚集索引的记录中只保留字符串前几个字符。如下：

#先删除原有索引
     alter table demo_info drop index idx_key1;
     #创建新的索引,保留前10个字符
     alter table demo_info add index idx_key1(key1(10));

然后再执行下面查询语句

select * from demo_info where key1 = 'abcdefghijklmnop';

由于在`idx_key1`的非聚集索引中只保留字符串的前`10`个字符，所以我们只能定位到前缀为‘`abcdefghij`’的非聚集索引记录。这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值，再对比就好了。

当列中存储的字符串包含的字符数比较多时，这种为列前缀建立索引的方法可以明显减少索引大小。

不过，**在只对列前缀建立索引的情况下，就不能使用索引来完成排序需求了。**

select * from demo_info order by key1 LIMIT 10;

因为非聚集索引中不包含完整的`key1` 列信息，所以无法对`key1`列前`10`个字符相同但其余字符不同的记录进行排序，也就是使用索引列前缀的方式无法支持使用索引排序，只能全表扫描+文件排序的方式来执行了。

## 5.让索引在where子句的一侧单独出现 ##

上面`demo_info`表给出了`key2`的信息

....
    key2 INT,
    ...
    UNIQUE KEY uk_key2 (key2),
    ...

表中有一个整数列`key2`，这个列建立了唯一索引。下边的两个`where`子句虽然语义是一致的，但是在效率上却有很大差别：

explain select * from demo_info where key2 < 4 - 1;

![20210628153633703.png][]

但是表达式调整之后

explain select * from demo_info where key2 + 1 < 4;

![20210628153908228.png][]

后者`key2`列并不是以单独出现的，而是以`key2 + 1`这样的表达式的形式出现的，存储引擎会依次遍历所有的记录，所以这种情况下是使用不到`key2`列建立的`B+`树索引的。

所以结论就是：如果`where`子句中表达式的一侧的索引不是单独的形式出现，而是经过计算或者函数调用形式出现的话，是用不到索引的。

注意：索引树有`key2`的值，而不是`key2 + 1`的值，像这种计算不会在存储引擎层完成，而是在`server`层。

## 6.保证主键的插入顺序 ##

对于一个使用`InnoDB`存储引擎的表来说，在我们没有显式的创建索引时，表中的数据实际上都是存储在聚集索引的叶子节点的。而记录又是存储在数据页中的，数据页和记录是按照记录主键值从小到大的顺序进行排序，所以如果我们插入的记录的主键值是依次增大的话，那我们每插满一个数据页就换到下一个数据页继续插入，如果新插入的主键值忽大忽小，这就比较麻烦了

![20210628160010513.png][]

如果此时再插入一条主键值为`9`的记录，那它插入的位置就如下图：

![20210628160027555.png][]

可这个数据页已经满了啊，新纪录该插入到哪里呢？我们需要**把当前页面分裂成两个页面，把本页中的一些记录移动到新创建的页中**。页面分裂和记录移位有一定的性能损耗。如果想尽量避免这样无谓的性能损耗，最好让插入的记录的主键值依次递增，让主键具有`AUTO_INCREMENT`属性，`MySQL`会自动为新插入的记录生成递增的主键，这样能够避免因数据插入导致的记录不必要的移动损耗。

## 7.如有必要，尽量考虑使用索引覆盖 ##

我个人在某业务场景中，需要查询根据会话`id`(`session_id`)和场景`id`(`scene_id`)去查询有没有这条记录，从而继续下一步插入或者更新操作，但是`session_id`对应多个`scene_id`，所以`session_id`无法设置唯一索引。就不能使用存在则更新，不存在则插入的语法`insert into 表名(字段1,字段2,...) values(值1, 值2,...) on duplicate key update 字段1=values(字段1), 字段2=values(字段2)`

所以，我只查一查这条记录有没有，再去判断插入还是更新。建立联合索引`idx_sessionid_sceneid(session_id, scene_id)`

只需要如下

select id from 表名 where session_id = '1212213' and scene_id = 'scene123';

这里只查询`id`，因为这样可以利用到索引覆盖。联合索引`idx_sessionid_sceneid`生成的非聚集索引不仅有`session_id`和`scene_id`， 还有主键`id`，这样就省去了回表的性能损耗。

前面`demo_info`表中有`KEY idx_key1 (key1)`

select key1 from demo_info order by key1;

前面说过，需要回表的记录越多，非聚集索引查询的性能越低，从而导致查询优化器选择全表扫描，前面采用的是指定`limit`子句来限制查询返回的记录数，让查询优化器倾向于选择非聚集索引+回表的方式查询，而不是全表扫描。

这里虽然没有`limit`子句，但是由于可以采用索引覆盖，查询优化器会直接在非聚集索引进行排序，不需要回表操作。

在实际业务中，如果也无需要查询索引列以外的列，那还是以业务为重，不要为了炫耀自己的索引玩的溜而乱用。最好仅把业务需要的索引列放在查询列表，而不是`select *`。

## 8.避免创建冗余重复的索引 ##

前面说过，我们有联合索引`KEY idx_key_part(key_part1, key_part2, key_part3)`。

如果你还单独建立一个`key_part1`索引，那就是多此一举，因为根据联合索引的最左前缀原则就已经可以用到`key_part1`索引了，索引的建立和维护也是需要时间和空间的，这种不必要的开销应该避免。

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

欢迎一键三连~  
  
有问题请留言，大家一起探讨学习  
  
\----------------------Talk is cheap, show me the code-----------------------

[20210628165253805.png]: /images/20221005/6f5096fe5e154dfb8c57eb25cf1580e4.png
[20210629181210782.png]: /images/20221005/1dd51a3387ec4dcdada07e4dbcb1dbf5.png
[20210629181545234.png]: /images/20221005/e0ad85cf63a8448abd4f2feb3ccc4c33.png
[20210629182008944.png]: /images/20221005/f5acf9899c4f49b4867efacc59a77cfb.png
[20210629182645376.png]: /images/20221005/8498ff3acb374fc388d6ab1feaea9f3e.png
[20210629095449274.png]: /images/20221005/cd9db17d528e40bd99a1ecae4214a487.png
[20210629095738450.png]: /images/20221005/c7649f57cb3241e8920527b602a0871e.png
[MRR]: https://blog.csdn.net/qq_34115899/article/details/118004118#t7
[20210626113943427.png]: /images/20221005/83f68c44e4ce4233bf21b75614ccda2b.png
[20210630155803518.png]: /images/20221005/2b3b1c02ae8147ae8dd21100d9d627cc.png
[20210630165212179.png]: /images/20221005/b830a4a840db4163b583c8f1a2cbd1be.png
[20210630165359554.png]: /images/20221005/530d3522b06e4299a295735e7062ac7e.png
[20210626142424798.png]: /images/20221005/6f1d2f8f64a345f49eb926a788d2f558.png
[20210626142314172.png]: /images/20221005/5e102568fe1e415b854a61eef5d8fbf1.png
[20210626215814865.png]: /images/20221005/f88feb61a6724a92804d07eac3670178.png
[20210626215940224.png]: /images/20221005/1a022f6cb1854d9185b64f08532a2e9b.png
[20210626160141481.png]: /images/20221005/e8ebfa583a5041fa904218d8f44dd7b7.png
[20210626180846242.png]: /images/20221005/a01c188cf38e4c5eb0e0e1751441c742.png
[20210626181608228.png]: /images/20221005/4076ea13e7ae4d5fa8b97e6987f7bbea.png
[20210626191545113.png]: /images/20221005/f41619f229a44022b3a8134a90bb8697.png
[20210627135843129.png]: /images/20221005/933c943bf2b04089bef23505b75041af.png
[20210627155054466.png]: /images/20221005/531621e3add948b693c99e7ad09a6c38.png
[20210627161335341.png]: /images/20221005/bede2bd13add465cb2406613c08c3637.png
[20210628153633703.png]: /images/20221005/9cbe97dcae804355b6719b240a05ba18.png
[20210628153908228.png]: /images/20221005/58eb567e5e274e83841f8d05d2e39d8a.png
[20210628160010513.png]: /images/20221005/d2291288b32b484ba59d993434dd7d4a.png
[20210628160027555.png]: /images/20221005/1eae275f37834d8db207035b8e5689b6.png