MySQL 正确使用索引-KeepStudy

MySQL 正确使用索引

对于如何使用索引才是正确的呢？总结如下：
- ①查询SQL中尽量不要使用OR关键字，可以使用多SQL或子查询代替。
- ②模糊查询尽量不要以%开头，如果实在要实现这个功能可以建立全文索引。
- ③编写SQL时一定要注意字段的数据类型，否则MySQL的隐式转换会导致索引失效。
- ④一定不要在编写SQL时让索引字段执行计算工作，尽量将计算工作放在客户端中完成。
- ⑤对于索引字段尽量不要使用计算类函数，一定要使用时请记得将函数计算放在=后面。
- ⑥多条件的查询SQL一定要使用联合索引中的第一个字段，否则会打破最左匹配原则。
- ⑦对于需要对比多个字段的查询业务时，可以拆分为连表查询，使用临时表代替。
- ⑧在SQL中不要使用反范围性的查询条件，大部分反范围性、不等性查询都会让索引失效。

实际上无非就是根据前面给出的索引失效情况，尽量让自己编写的SQL不会导致索引失效即可，写出来的SQL能走索引查询，那就能在很大程度上提升数据检索的效率。

### 索引覆盖
由于表中只能存在一个聚簇索引，一般都为主键索引，而建立的其他索引都为辅助索引，包括联合索引也例外，最终索引节点上存储的都是指向主键索引的值，拿用户表为例：
```sql
SELECT * FROM `zz_users` WHERE `user_name`="竹子" AND `user_sex`="男";
```
虽然这条SQL会走联合索引查询，但是基于联合索引查询出来的值仅是一个指向主键索引的ID，然后会拿着这个ID再去主键索引中查一遍，这个过程之前聊过，被称为回表过程。

那么回表问题无法解决吗？必须得经过两次查询才能得到数据吗？答案并非如此。

比如假设此时只需要user_name、user_sex、password这三个字段的信息，此时SQL语句可以更改为如下情况：
```sql
SELECT `user_name`,`user_sex`,`password`
FROM `zz_users` 
WHERE `user_name` = "竹子" AND `user_sex` = "男";
```
此时将SQL更改为查询所需的列后，就不会发生回表现象，Why？再这里很多小伙伴可能会疑惑，这是什么道理啊？因为此时所需的user_name、user_sex、password三个字段数据，在联合索引中完全包含，因此可以直接通过联合索引获取到数据。

但如果查询时用*，因为联合索引中不具备完整的一行数据，只能再次转向聚簇索引中获取完整的行数据，因此到这里大家应该也明白了为什么查询数据时，不能用*的原因，这是因为会导致索引覆盖失效，造成回表问题。

### 索引下推
索引下推是MySQL5.6版本以后引入的一种优化机制，在MySQL5.6版本之后是默认开启的，可以通过命令set optimizer_switch='index_condition_pushdown=off|on';命令来手动管理。

以之前的用户表为例，首先为了更加直观的讲清楚索引下推，因此先再向用户表中增加一条数据：
```sql
{
    ["熊猫","女","6666"] : 1,
    ["竹子","男","1234"] : 2,
    ["子竹","男","4321"] : 3,
    ["1111","男","4321"] : 4,
    ["竹竹","女","8888"] : 5
}
```
然后再来看一条SQL语句：
```sql
SELECT * FROM `zz_users` WHERE `user_name` LIKE "竹%" AND `user_sex`="男";
```
这条SQL会使用联合索引吗？答案是会的，但只能部分使用。由于前面使用的是模糊查询，但%在结尾，因此可以使用竹这个字作为条件在联合索引中查询，整个查询过程如下：

- ①利用联合索引中的user_name字段找出「竹子、竹竹」两个索引节点。
- ②返回索引节点存储的值「2、5」给Server层，然后去逐一做回表扫描。
- ③在Server层中根据user_sex="男"这个条件逐条判断，最终筛选到「竹子」这条数据。

有人或许会疑惑，为什么user_sex="男"这个条件不在联合索引中处理呢？因为前面是模糊查询，所以拼接起来是这样的：竹x男，由于这个x是未知的，因此无法根据最左前缀原则去匹配数据，最终这里只能使用联合索引中user_name字段的一部分，后续的user_sex="男"还需要回到Server层处理。

>那什么又叫做索引下推呢？也就是将Server层筛选数据的工作，下推到引擎层处理。

以前面的案例来讲解，MySQL5.6加入索引下推机制后，其执行过程是什么样子的呢？

- ①利用联合索引中的user_name字段找出「竹子、竹竹」两个索引节点。
- ②根据user_sex="男"这个条件在索引节点中逐个判断，从而得到「竹子」这个节点。
- ③最终将「竹子」这个节点对应的「2」返回给Server层，然后聚簇索引中回表拿数据。

相较于没有索引下推之前，原本需要做「2、5」两次回表查询，但在拥有索引下推之后，仅需做「2」一次回表查询。

### MRR(Multi-Range Read)机制
Multi-Range Read简称为MRR机制，这也是和索引下推一同在MySQL5.6版本中引入的性能优化措施，那什么叫做MRR优化呢？

一般来说，在实际业务中我们应当尽量通过索引覆盖的特性，减少回表操作以降低IO次数，但在很多时候往往又不得不做回表才能查询到数据，但回表显然会导致产生大量磁盘IO，同时更严重的一点是：还会产生大量的离散IO，下面举个例子来理解。

```sql
SELECT * FROM `zz_student_score` WHERE `score` BETWEEN 0 AND 59;
```

上述这条SQL所做的工作很简单，就是在学生成绩表中查询所有成绩未及格的学生信息，假设成绩字段上存在一个普通索引，那思考一下，这条SQL的执行流程是什么样的呢？

- ①先在成绩字段的索引上找到0分的节点，然后拿着ID去回表得到成绩零分的学生信息。
- ②再次回到成绩索引，继续找到所有1分的节点，继续回表得到1分的学生信息。
- ③再次回到成绩索引，继续找到所有2分的节点......
- ④周而复始，不断重复这个过程，直到将0~59分的所有学生信息全部拿到为止。

那此时假设此时成绩0~5分的表数据，位于磁盘空间的page_01页上，而成绩为5~10分的数据，位于磁盘空间的page_02页上，成绩为10~15分的数据，又位于磁盘空间的page_01页上。此时回表查询时就会导致在page_01、page_02两页空间上来回切换，但0~5、10~15分的数据完全可以合并，然后读一次page_01就可以了，既能减少IO次数，同时还避免了离散IO。

而MRR机制就主要是解决这个问题的，针对于辅助索引的回表查询，减少离散IO，并且将随机IO转换为顺序IO，从而提高查询效率。

那MRR机制具体是怎么做的呢？MRR机制中，对于辅助索引中查询出的ID，会将其放到缓冲区的read_rnd_buffer中，然后等全部的索引检索工作完成后，或者缓冲区中的数据达到read_rnd_buffer_size大小时，此时MySQL会对缓冲区中的数据排序，从而得到一个有序的ID集合：rest_sort，最终再根据顺序IO去聚簇/主键索引中回表查询数据。

```sql
SET @@optimizer_switch='mrr=on|off,mrr_cost_based=on|off';
```

可以通过上述这条命令开启或关闭MRR机制，MySQL5.6及以后的版本是默认开启的。

### Index Skip Scan索引跳跃式扫描
在讲联合索引时，咱们提到过最左前缀匹配原则，也就是SQL的查询条件中必须要包含联合索引的第一个字段，这样才能命中联合索引查询，但实际上这条规则也并不是100%遵循的。因为在MySQL8.x版本中加入了一个新的优化机制，也就是索引跳跃式扫描，这种机制使得咱们即使查询条件中，没有使用联合索引的第一个字段，也依旧可以使用联合索引，看起来就像跳过了联合索引中的第一个字段一样，这也是跳跃扫描的名称由来。

但跳跃扫描究竟是怎么实现的呢？上个栗子快速理解一下。

比如此时通过(A、B、C)三个列建立了一个联合索引，此时有如下一条SQL：
```sql
SELECT * FROM `tb_xx` WHERE B = `xxx` AND C = `xxx`;
```

按理来说，这条SQL既不符合最左前缀原则，也不具备使用索引覆盖的条件，因此绝对是不会走联合索引查询的，但思考一个问题，这条SQL中都已经使用了联合索引中的两个字段，结果还不能使用索引，这似乎有点亏啊对不？因此MySQL8.x推出了跳跃扫描机制，但跳跃扫描并不是真正的“跳过了”第一个字段，而是优化器为你重构了SQL，比如上述这条SQL则会重构成如下情况：
```sql
SELECT * FROM `tb_xx` WHERE B = `xxx` AND C = `xxx`
UNION ALL
SELECT * FROM `tb_xx` WHERE B = `xxx` AND C = `xxx` AND A = "yyy"
......
SELECT * FROM `tb_xx` WHERE B = `xxx` AND C = `xxx` AND A = "zzz";
```

其实也就是MySQL优化器会自动对联合索引中的第一个字段的值去重，然后基于去重后的值全部拼接起来查一遍，一句话来概述就是：虽然你没用第一个字段，但我给你加上去，今天这个联合索引你就得用，不用也得给我用。

当然，如果熟悉Oracle数据库的小伙伴应该知道，跳跃扫描机制在Oracle中早就有了，但为什么MySQL8.0版本才推出这个机制呢？还记得咱们在《MySQL架构篇》中的闲谈嘛？MySQL几经转手后，最终归到了Oracle旗下，因此跳跃扫描机制仅是Oracle公司：从Oracle搬到了“自己的MySQL”上而已。

但是跳跃扫描机制也有很多限制，比如多表联查时无法触发、SQL条件中有分组操作也无法触发、SQL中用了DISTINCT去重也无法触发.....，总之有很多限制条件。

其实这个跳跃性扫描机制，只有在唯一性较差的情况下，才能发挥出不错的效果，如果你联合索引的第一个字段，是一个值具备唯一性的字段，那去重一次再拼接，几乎就等价于走一次全表。

最后，可以通过通过```sql
set @@optimizer_switch = 'skip_scan=off|on';
```命令来选择开启或关闭跳跃式扫描机制。当然，该参数仅限MySQL8.0以上的版本，如果在此之下的版本暂时就不用考虑了。

顶部

MySQL索引

目录

相关推荐