(五)MySQL索引应用篇：建立索引的正确姿势与使用索引的最佳指南！

引言

本文为社区首发签约文章，14天内制止转载，14天后未获授权制止转载，侵权必究！

数据库索引，肯定是MySQL的核心功用之一，假如没有索引机制的数据库，那数据的检索功率肯定是令人无法接受的，究竟没有索引的表数据，就如同一个一般的文本文件存储在磁盘中。在《索引上篇》中，我们关于MySQL供给的索引机制，从引进，到创立、运用、分类、办理….等进行了全面论述，信任经过上一篇的解说后，我们对MySQL索引机制树立了系统化的认知，而本篇则会以上篇为根底，对索引机制进一步加深把握。

不过在上篇中虽对数据库索引机制有了完善认知，但还不行，由于上篇仅是单纯的认知阶段，能否真实的在实践项目中运用好索引机制，还需求具有丰富的经验以及一些准则与方法论，比方下述一些关于索引的问题：

• 索引尽管能给MySQL检索数据的功率带来质的腾跃，但参加索引未带来新问题吗？
• 既然索引能够进步查询功用，那是不是为表中每个字段树立索引，功用会更好？
• 一张数据表中，那些类型的字段不合适树立索引呢？又是由于什么原因呢？
• 表中会存在许多的字段，但其间那些字段树立索引才干够最大的功用收益呢？
• MySQL供给的索引品种也不少，一个字段上树立什么类型的索引才最好呢？
• 当表中存在多个索引时，一条查询SQL有多条路径可走，此刻走哪条索引最好？
• .......

关于这些问题，假如仅靠上篇索引的知识，信任是很难答复具体的，那在本篇中，则要点解说索引应用相关的方法方法，例如各索引好坏剖析、树立索引的准则、运用索引的指南以及索引失效与索引优化等内容。

一、MySQL各索引的好坏剖析

首先来聊聊索引机制带来的利害关系，有句古话曾说过：“凡事有利必有弊”，而MySQL的索引机制也不破例，引进索引机制后，能够给数据库带来的优势很显着：

• ①整个数据库中，数据表的查询速度直线进步，数据量越大时效果越显着。
• ②经过创立仅有索引，能够保证数据表中的数据仅有性，无需额定树立仅有束缚。
• ③在运用分组和排序时，相同能够显著削减SQL查询的分组和排序的时刻。
• ④连表查询时，依据主外键字段上树立索引，能够带来十分显着的功用进步。
• ⑤索引默许是B+Tree有序结构，依据索引字段做规模查询时，功率会显着进步。
• ⑥从MySQL全体架构而言，削减了查询SQL的履行时刻，进步了数据库全体吞吐量。

看着上面一条又一条的优点，似乎感觉索引优点很大啊，关于这点的确毋庸置疑，但只需优点吗？No，一起也会带来一系列坏处，如：

• ①树立索引会生成本地磁盘文件，需求额定的空间存储索引数据，磁盘占用率会变高。
• ②写入数据时，需求额定保护索引结构，增、删、改数据时，都需求额定操作索引。
• ③写入数据时保护索引需求额定的时刻开支，履行写SQL时功率会下降，功用会下降。

当然，但对数据库全体来说，索引带来的优势会大于下风。不过也正由于索引存在坏处，因而索引不是越多越好，合理树立索引才是最佳挑选。

在上篇聊过，MySQL的索引也会分为多品种型，每个类型的索引多多少少都存在一些坏处，接下来聊聊其他类型的索引。

1.1、主键索引存在的圈套

信任我们数据库的表中，主键一般都是运用自增ID，但这是为什么呢？有人或许会答复自增ID不会重复，保证了主键仅有性。这样也的确没错，但不会重复的又不仅仅只需自增ID，比方我运用随机的UUID也不会重复，为何不运用UUID呢？这是由于索引存在一个圈套！

众所周知，一张表中大大都状况下，会将主键索引以聚簇的方法存在磁盘中，上篇文章也聊到过，聚簇索引在存储数据时，表数据和索引数据是一起寄存的。一起，MySQL默许的索引结构是B+Tree，也就代表着索引节点的数据是有序的。

此刻结合上面给出的一些信息，主键索引是聚簇索引，表数据和索引数据在一块、索引结构是有序的，那再反推前面给出的疑问，为何不运用UUID呢？由于UUID是无序的，假如运用UUID作为主键，那么每逢刺进一条新数据，都有或许破坏原本的树结构，如下：

比方上图中的灰色节点，是一条新刺进的数据，此刻经过核算后，应该排第二个位置，那就代表着后边的三个节点需求移动，然后给灰色节点挪出一个位置存储，然后保证索引的有序性。

这儿仅仅伪逻辑，目的是用于举例演示，实践上B+树索引结构不长这样，在《索引原理篇》会重新说一下这个点的。

由于主键索引是聚簇索引，因而上述事例中，当后续节点需求移动时，也就代表着还需求移动表数据，假如是偶然需求移动还行，但假如主键字段值无序，那代表着简直每次刺进都有或许导致树结构要调整。

但运用自增ID就不会有这个问题，一切新刺进的数据都会放到终究。

因而我们数据表的主键，最好选用带次序性的值，不然有或许掉入主键索引的“圈套”中。

1.2、联合索引存在的对立

为了多条件查询时的功率更高，一般都会一起对多个字段树立联合索引，但之前也聊到过，联合索引存在一个丧命的问题，比方在用户表中，经过id、name、age三个字段树立一个联合索引，此刻来了一条查询SQL，如下：

SELECT * FROM `zz_user` WHERE name = "竹子" AND age = "18";

而这条SQL句子是无法运用联合索引的，为什么呢？由于查询条件中，未包括联合索引的第一个字段，想要运用联合索引，那么查询条件中必须包括索引的第一个字段，如下：

SELECT * FROM `zz_user` WHERE name = "竹子" AND id = 6;

上面这条SQL才是能射中多列索引的句子，因而在树立索引时也需求考虑这个问题，保证树立出的联合索引能够射中率够高。

1.3、前缀索引存在的坏处

前缀索引的特点是言简意赅，我们能够运用一个字段的前N个字符创立索引，以这种方法创立的索引也被称之为前缀索引，相较于运用一个完好字段创立索引，前缀索引能够愈加节约存储空间，当数据越多时，带来的优势越显着。

不过前缀索引尽管带来了节约空间的优点，但也正由于其索引节点中，未存储一个字段的完好值，所以MySQL也无法经过前缀索引来完结ORDER BY、GROUP BY等分组排序作业，一起也无法完结掩盖扫描等操作。

1.4、全文索引存在的硬伤

之前做含糊查询时，通常都会运用like%语法，不过这种方法尽管能够完成效果，但随着表越来越大，数据越来越多时，其功用会呈现显着下降，而全文索引的推出则能够完美处理该问题，能够运用全文索引替代like%语法完成含糊查询，它的功用会比like%快上N倍。

全文索引尽管能够完成含糊查询，但也存在一系列硬伤，一起来看看。

①由于全文索引是依据分词完成的，所以对一个字段树立全文索引后，MySQL会对该字段做分词处理，这些分词成果也会被存储在全文索引中，因而全文索引的文件会额定的大！

②由于全文索引对每个字段值都会做分词，因而当修改字段值后，分词是需求时刻的，所以修改字段数据后不会立马主动更新全文索引，此刻需求我们写存储进程，并调用它手动更新全文索引中的数据。

③除开上述两点外，全文索引最大的硬伤在于对中文支撑不行友好，类似于英文能够直接经过符号、空格来分词，但中文呢？一个词语来形容便是博大精深，无法精准的对一段文字做分词，因而全文索引在检索中文时，存在少许精准度问题。

因而假如你项目规模较大，通常再引进ElasticSearch、Solr、MeiliSearch等搜索引擎是一个更佳的挑选。

1.5、仅有索引存在的快慢问题

仅有索引有个很大的优点，便是查询数据时会比一般索引功率更高，由于依据一般索引的字段查询数据，例如：

SELECT * FROM TABLE_XX WHERE COLUMN_XX = "XX";

假定COLUMN_XX字段上树立了一个一般索引，此刻依据这个字段查询数据时，当查询到一条COLUMN_XX = "XX"的数据后，此刻会持续走完好个索引树，由于或许会存在多条字段值相同的数据。

但假如COLUMN_XX字段上树立的是仅有索引，当找到一条数据后就会立马停下检索，因而自身树立仅有索引的字段值就具有仅有性。

因而仅有索引查询数据时，会比一般索引快上一截，但刺进数据时就不同了，由于要保证数据不重复，所以刺进前会检查一遍表中是否存在相同的数据。但一般索引则不需求考虑这个问题，因而一般索引的数据刺进会快一些。

1.6、哈希索引的丧命问题

哈希索引，也便是数据结构为Hash类型的索引，不过估计我们接触的比较少，究竟创立索引时都默许用的B+树结构。但要比起查询速度，哈希索引肯定是MySQL中当之无愧的魁首！由于选用哈希结构的索引，会以哈希表的方法存储索引字段值，当依据该字段查询数据时，只需求经过一次哈希核算就可获取到数据。

但哈希结构的丧命问题在于无序，也便是无法依据哈希索引的字段做排序、分组等作业。

因而假如你承认一个表中，不会做排序这类的作业，那能够适当选用哈希结构作为索引的数据结构，它会给你带来意想不到的功用收益~

二、树立索引的正确姿态

经过上述一系列剖析后，简略讲明了每种索引类型存在的缺点问题，但这跟我们本篇有啥关系呢？其实关系很大，由于只需当你了解了每种索引存在的下风，才干更好的考虑并设计出合理的索引，而不是一股脑的盲目创立索引。

那么在创立索引时，我们应当恪守那些原理准则，才干创立出合理的索引呢？

在实践项目场景中，当SQL查询功用较慢时，我们常常会有一个疑问：表中哪个字段树立一个索引能带来最大的功用收益呢？一般来说，判别字段是否要添加的索引的依据，是看这个字段是否被常常当做查询条件运用，但也不能光依靠这一个依据来判别，比方用户表中的性别字段，就会常常被用做查询条件，但假如对性别字段树立一个索引，那对查询的功用进步并不大，由于性别就两个值：男/女（不包括泰国在内），那对其树立索引，索引文件中就只会有两个索引节点，大致状况如下：

这种状况下，为性别树立一个索引，带来的功用收益明显不是太大。一起，上图中给出的事例，也不是索引真实的姿态，假如表中存在主键索引或聚簇索引，对其他字段树立的索引，都是次级索引，也被称为辅佐索引，其节点上的值，存储的并非一条完好的行数据，而是指向聚簇索引的索引字段值。

假如依据辅佐索引查询数据，终究数据会以何种方法被检索出来，这儿就牵扯到MySQL中的一个新概念，也便是SQL履行时的回表问题。

2.1、索引查询时的回表问题

什么叫做回表呢？意思便是指一条SQL句子在MySQL内部，要经过两次查询进程才干获取到数据。这是跟索引机制有关的，先来看看索引在MySQL内部真实的面貌：

在上图用户表中，依据ID字段先树立了一个主键索引，然后又依据name字段树立了一个一般索引，此刻MySQL默许会选用主键索引作为聚簇索引，将表数据和主键索引存在同一个文件中，也便是主键索引的每个索引节点，都直接对应着行数据。而依据name字段树立的索引，其索引节点寄存的则是指向聚簇索引的ID值。

在这种状况下，假定有一条下述SQL，其内部查询进程是啥样的呢？

SELECT * FROM `zz_user` WHERE name = "子竹";

首先会走name字段的索引，然后找到对应的ID值，然后再依据查询到的ID值，再走ID字段的主键索引，终究得到一整条行数据并回来。

在这个事例中，一条查询SQL经历了两次查询才获取到数据，这个进程则被称之为回表。

回表动作会导致额定的查询开支，因而尽量能够依据主键做查询，假如实在需求运用非主键字段查询，那么尽量要写明查询的成果字段，而并非运用*。

当然，实践状况中树立联合索引，运用索引掩盖特性，然后避免运用辅佐索引，这样也能够消除回表动作，但关于这点后边再聊，先来说说树立索引需求遵从的一些准则。

2.2、树立索引时需求恪守的准则

前面说过一点，当树立索引仅考虑一个字段是否被常常用于查询是不行的，往往一个合适的索引需求更为细致与长远的考虑，例如运用多个字段树立是否会更好？创立其他类型的索引功用是否会更佳？下面我们就一起来看看树立索引时，需求恪守的一些准则：

• ①常常频繁用作查询条件的字段应酌情考虑为其创立索引。
• ②表的主外键或连表字段，必须树立索引，由于能很大程度进步连表查询的功用。
• ③树立索引的字段，一般值的区分性要足够高，这样才干进步索引的检索功率。
• ④树立索引的字段，值不应该过长，假如较长的字段要树立索引，能够挑选前缀索引。
• ⑤树立联合索引，应当遵从最左前缀准则，将多个字段之间按优先级次序组合。
• ⑥常常依据规模取值、排序、分组的字段应树立索引，由于索引有序，能加速排序时刻。
• ⑦关于仅有索引，假如承认不会运用该字段排序，那能够将结构改为Hash结构。
• ⑧尽量运用联合索引替代单值索引，联合索引比多个单值索引查询功率要高。

一起，除开上述一些树立索引的准则外，在树立索引时还需有些留意点：

• ❶值常常会增修改的字段，不合适树立索引，由于每次改变后需保护索引结构。
• ❷一个字段存在许多的重复值时，不合适树立索引，比方之前举例的性别字段。
• ❸索引不能参加核算，因而常常带函数查询的字段，并不合适树立索引。
• ❹一张表中的索引数量并不是越多越好，一般控制在3，最多不能超过5。
• ❺树立联合索引时，必定要考虑优先级，查询频率最高的字段应当放首位。
• ❻当表的数据较少，不应当树立索引，由于数据量不大时，保护索引反而开支更大。
• ❼索引的字段值无序时，不推荐树立索引，由于会形成页分裂，尤其是主键索引。

关于索引机制，在树立时应当参阅上述给出的意见，这每一条准则都是从实践经验中总结出来的，前面八条不必定要全面考虑，但后边七条留意点，必定要牢记，如若你的索引契合后边七条中的描绘，那必定要更改索引。

关于每一条主张是为什么，在后边的《索引原理篇》讲完之后我们就会彻底了解，这儿就不展开叙说了，接下来要点聊一下联合索引，以及它的最左前缀准则。

2.3、联合索引的最左前缀准则

首先在讲最左前缀准则之前，先看看上述给出的一条准则：

• ⑧尽量运用联合索引替代单值索引，联合索引比多个单值索引查询功率要高。

关于这一点是为什么呢？举个栗子了解，比方此刻依据X、Y、Z字段树立了一个联合索引，实践上也相当于树立了三个索引：X、X、Y、X、Y、Z，因而只需查询中运用了这三组字段，都能够让联合索引收效。

但如若查询中这三个字段不以AND方法呈现，而是单独作为查询条件呈现，那单值索引功用会好一些，但三个不同的索引，保护的代价也会高一些。

其实联合索引的最左前缀准则，道理很简略的，便是组成联合索引的多个列，越靠左面优先级越高，一起也只需SQL查询条件中，包括了最左的字段，才干运用联合索引，例如：

-- 依据上面的哪个X、Y、Z联合索引
SELECT * FROM tb WHERE Y = "..." AND Z = "...";

上面这条SQL就明显并不会运用联合索引，由于不契合最左前缀准则，最左侧的X字段未曾被运用。也正由于MySQL在运用联合索引时会遵从最左前缀准则，所以才在前面树立索引的主张中给出了一条：

• ❺树立联合索引时，必定要考虑优先级，查询频率最高的字段应当放首位。

由于将查询频率越高的字段放首位，就代表着查询时射中索引的几率越大。一起，MySQL的最左前缀准则，才匹配到规模查询时会停止匹配，比方>、<、between、like这类规模条件，并不会持续运用联合索引，举个栗子：

SELECT * FROM tb WHERE X="..." AND Y > "..." AND Z="...";

当履行时，尽管上述SQL运用到X、Y、Z作为查询条件，但由于Y字段是>规模查询，因而这儿只能运用X索引，而不能运用X、Y或X、Y、Z索引。

关于一条查询SQL是否用到了索引，或者一条查询SQL究竟用了那个索引，其实能够经过MySQL自带的explain东西剖析（后续解说）。

终究再来一个简略的栗子，加深一下关于联合索引的认知：

select * from user where name = '竹子';
select * from user where name = '竹子' and age = 18;
A. create index index_name on user(name);
   create index index_name on user(age);
B. create index index_name on user(name,age);

比方上述这个事例中，关于这两条SQL选第一种方法创立索引，仍是第二种呢？答案是B，由于两条sql彻底能够运用到第二个创立的联合索引。

select * from user where name = '竹子' and age = 18;
select * from user where  age = 18 and name = '竹子';

一起选B树立联合索引后，如上两条SQL都会运用到上面创立的联合索引，SQL是否走索引查询跟where后的条件次序无关，由于MySQL优化器会优化，对SQL查询条件进行重排序。

三、索引失效与运用索引的正确姿态

信任这一点我们看了有些懵，啥叫运用索引的正确姿态？索引不是MySQL履行SQL时主动挑选的吗？我们只能树立索引，怎样运用啊？其实这儿是指我们编写SQL时，要留意的点，究竟MySQL查询时究竟使不运用索引，这彻底取决于你编写的SQL。

但许多小伙伴在平常写SQL的时分，一般只追求完成事务功用，只需能够查询出相应的数据即可，压根不会过度考虑这条SQL应用到索引，那么这儿便是给出一些经验之谈，讲清楚几点写SQL时的方法论。

其实索引自身是一把双刃剑，用的好能够给我们带来与众不同的查询功率，用的欠好则反而会带来额定的磁盘占用及写入操作时的保护开支。因而我们必定要切记，既然挑选建了索引，那必定要运用它，不然还不如爽性别建，既能节约磁盘空间，又能进步写入功率。

3.1、索引失效的那些事儿

想要用好索引，那必定要先搞清楚那些状况会导致索引失效，弄了解这些事项之后，在写SQL的时分故意避开，那你写出来的SQL十有八九是会用到索引的，那么在数据库中那些状况下会导致索引失效呢？下面一起来聊一聊，但单纯的讲概念会有种坐而论道的感觉，因而下面简略的举个事例，然后来阐明索引失效的一些状况。

SELECT * FROM `zz_users`;
+---------+-----------+----------+----------+---------------------+
| user_id | user_name | user_sex | password | register_time       |
+---------+-----------+----------+----------+---------------------+
|       1 | 熊猫      | 女       | 6666     | 2022-08-14 15:22:01 |
|       2 | 竹子      | 男       | 1234     | 2022-09-14 16:17:44 |
|       3 | 子竹      | 男       | 4321     | 2022-09-16 07:42:21 |
+---------+-----------+----------+----------+---------------------+
ALTER TABLE `zz_users` ADD PRIMARY KEY `p_user_id`(`user_id`);
ALTER TABLE `zz_users` ADD KEY `unite_index`(`user_name`,`user_sex`,`password`);

此刻对这张用户表，分别创立两个索引，第一个是依据user_id创立的主键索引，第二个是运用user_name、user_sex、password三个字段创立的联合索引。

但想要检查一条SQL是否运用了索引，需求用到一个自带的剖析东西ExPlain，下面简略介绍一下。

3.1.1、履行剖析东西 – ExPlain

这儿就对explain东西做一个简略介绍，后续《SQL优化篇》会具体解说这个东西，先来看看这个东西/指令的效果，当在一条SQL前加上explain指令，履行这条SQL后会列出一切的履行方案：

EXPLAIN SELECT * FROM `zz_users`;
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | zz_users | ALL  | NULL          | NULL | NULL    | NULL |    3 |       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+

• id：这是履行计划的ID值，这个值越大，表明履行的优先级越高。
• select_type：当时查询句子的类型，有如下几个值：
  • simple：简略查询。
  • primary：杂乱查询的外层查询。
  • subquery：包括在查询句子中的子查询。
  • derived：包括在FROM中的子查询。
• table：表明当时这个履行计划是依据那张表履行的。
• type：当时履行计划查询的类型，有几种状况：
  • all：表明走了全表查询，未射中索引或索引失效。
  • system：表明要查询的表中仅有一条数据。
  • const：表明当时SQL句子的查询条件中，能够射中索引查询。
  • range：表明当时查询操作是查某个区间。
  • eq_ref：表明现在在做多表相关查询。
  • ref：表明现在运用了一般索引查询。
  • index：表明现在SQL运用了辅佐索引查询。
• possible_keys：履行SQL时，优化器或许会挑选的索引（终究履行不必定用）。
• key：查询句子履行时，用到的索引名字。
• key_len：这儿表明索引字段运用的字节数。
• ref：这儿显现运用了那种查询的类型。
• rows：当时查询句子或许会扫描多少行数据才干检索出成果。
• Extra：这儿是记录着额定的一些索引运用信息，有几种状态：
  • using index：表明现在运用了掩盖索引查询（稍后讲）。
  • using where：表明运用了where子句查询，通常表明没运用索引。
  • using index condition：表明查询条件运用到了联合索引的前面几个字段。
  • using temporary：表明运用了暂时表处理查询成果。
  • using filesort：表明以索引字段之外的方法进行排序，功率较低。
  • select tables optimized away：表明在索引字段上运用了聚合函数。

关于上述这么多的字段，其完成在不需求彻底弄懂，本文只需求记住里边的type字段即可，all表明走全表扫描，const、ref...表明经过索引查询。

下面一起来聊一聊索引失效的一些场景。

3.1.2、查询中带有OR会导致索引失效

EXPLAIN SELECT * FROM `zz_users` WHERE user_id = 1 OR user_name = "熊猫";

例如上述这条SQL，其间既包括了主键索引的字段，又包括了联合索引的第一个字段，按理来说是会走索引查询的对吗？但看看履行成果：

从成果中可看到type=ALL，明显并未运用索引来查询，也就代表着，尽管一切查询条件都包括了索引字段，但由于运用了OR，终究导致索引失效。

3.1.3、含糊查询中like以%最初导致索引失效

众所周知，运用like关键字做含糊查询时，是能够运用索引的，那来看看下述这条SQL：

EXPLAIN SELECT * FROM `zz_users` WHERE user_name LIKE "%熊";

在这条SQL中以联合索引中的第一个字段作为了查询条件，此刻会运用索引吗？看看成果：

成果中显现仍旧走了全表扫描，并未运用索引，但like不以%最初，实践上是不会导致索引失效的，例如：

在这儿以%结束，其实能够运用联合索引来检索数据，并不会导致索引失效。

3.1.4、字符类型查询时不带引号导致索引失效

-- 先刺进一条user_name = 1111 的数据
INSERT INTO `zz_users` VALUES(4,"1111","男","4321","2022-09-17 23:48:29");
EXPLAIN SELECT * FROM `zz_users` WHERE user_name = 111;

上述这条SQL按理来说是没有半点问题的，现在是契合联合索引的最左匹配准则的，但来看看成果：

从成果中很显着的能够看出，由于user_name是字符串类型的，因而查询时没带引号，居然直接未运用索引，导致了索引失效（上面也放了比照图，我们能够仔细看看差异）。

3.1.5、索引字段参加核算导致索引失效

EXPLAIN SELECT * FROM `zz_users` WHERE user_id - 1 = 1;

上面这条SQL看着估计有些懵，但实践上很简略，便是查询ID=2的数据，理论上由于查询条件中运用了主键字段，应该会运用主键索引，但成果呢？

由于索引字段参加了核算，所以此刻又导致了索引失效，因而我们要切记，千万不要让索引字段在SQL中参加核算，也包括运用一些聚合函数时也会导致索引失效，其根本原因就在于索引字段参加了核算导致的。

这儿的运算也包括+、-、*、/、!.....等一系列涉及字段核算的逻辑。

3.1.6、字段被用于函数核算导致索引失效

EXPLAIN SELECT * FROM `zz_users` WHERE SUBSTRING(user_name,0,1) = "竹子";

上述中，我们运用SUBSTRING函数对user_name字段进行了截取，然后再用于条件查询，此刻看看履行成果：

很明显，并未运用索引查询，这也是意料之中的作业，究竟这一条和3.1.5的原因大致相同，索引字段参加核算导致失效。

3.1.7、违背最左前缀准则导致索引失效

EXPLAIN SELECT * FROM `zz_users` WHERE `user_sex` = "男" AND `password` = "1234";

上述这条SQL中，明显用到了联合索引中的性别和暗码字段，此刻再看看成果：

由于违背了联合索引的最左前缀准则，由于没运用最左面的user_name字段，因而也导致索引失效，然后走了全表查询。

3.1.8、不同字段值比照导致索引失效

从一张表中查询出一些值，然后依据这些值去其他表中筛选数据，这个事务也是实践项目中较为常见的场景，下面为了简略完成，就简略用名字和性别模仿一下字段比照的场景：

EXPLAIN SELECT * FROM `zz_users` WHERE user_name = user_sex;

按理来说，由于user_name归于联合索引的第一个字段，所以上述这条SQL中规中矩，理论上会走索引的，但看看成果：

明显，这个场景也会导致索引无法运用，因而之后也要切记这点。

3.1.9、反向规模操作导致索引失效

一般来说，假如SQL归于正向规模查询，例如>、<、between、like、in...等操作时，索引是能够正常收效的，但假如SQL履行的是反向规模操作，例如NOT IN、NOT LIKE、IS NOT NULL、!=、<>...等操作时，就会呈现问题，例如：

EXPLAIN SELECT * FROM `zz_users` WHERE user_id NOT IN(1,2,3);

上述SQL的意思很简略，也便是查询user_id不是1,2,3的数据，这儿是依据主键索引字段user_id查询的，但会走索引吗？来看看成果：

成果也很显着，运用NOT关键字做反向规模查询时，并不会走索引，索引此刻失效了，可是做正向规模查询时，索引仍旧有用。

关于这一点，其实我们能够渐渐实验，并非一切的正向规模操作都会走索引，例如IS NULL就不会走，它的反向操作：IS NOT NULL相同不会走。

3.1.10、索引失效小结

在MySQL中还有一种特殊状况会导致索引失效，也便是当走索引扫描的行数超过表行数的30%时，MySQL会默许抛弃索引查询，转而运用全表扫描的方法检索数据，因而这种状况下走索引的次序磁盘IO，反而不必定有全表的随机磁盘IO快。

还有一点要牢记：关于索引是否会失效，实践上也跟索引的数据结构、MySQL的版别、存储引擎的不同有关，例如一条SQL句子在B+Tree索引中会导致索引失效，但在哈希索引中却不会（比方IS NULL/IS NOT NULL），这种状况在不同版别、不同引擎中都有或许会体现出来。

但到现在为止，大致上现已将MySQL中会导致索引失效的几种状况罗列阐明了，接下来一起看看运用索引的正确姿态！

3.2、运用索引的正确姿态

其实到这儿，关于怎样运用索引才是正确的呢？总结如下：

• ①查询SQL中尽量不要运用OR关键字，能够运用多SQL或子查询替代。
• ②含糊查询尽量不要以%最初，假如实在要完成这个功用能够树立全文索引。
• ③编写SQL时必定要留意字段的数据类型，不然MySQL的隐式转化会导致索引失效。
• ④必定不要在编写SQL时让索引字段履行核算作业，尽量将核算作业放在客户端中完结。
• ⑤关于索引字段尽量不要运用核算类函数，必定要运用时请记住将函数核算放在=后边。
• ⑥多条件的查询SQL必定要运用联合索引中的第一个字段，不然会打破最左匹配准则。
• ⑦关于需求比照多个字段的查询事务时，能够拆分为连表查询，运用暂时表替代。
• ⑧在SQL中不要运用反规模性的查询条件，大部分反规模性、不等性查询都会让索引失效。
• ⑨.......

实践上无非便是依据前面给出的索引失效状况，尽量让自己编写的SQL不会导致索引失效即可，写出来的SQL能走索引查询，那就能在很大程度上进步数据检索的功率。

不过这些也归于SQL优化的内容，因而更多、更具体的SQL编写准则，会在之后的《SQL调优篇》具体解说。

接下来再要点讲几个较重要的内容，既索引掩盖、索引下推、Multi-Range Read机制、索引跳动式扫描机制。

3.2.1、索引掩盖

在之前聊到过，由于表中只能存在一个聚簇索引，一般都为主键索引，而树立的其他索引都为辅佐索引，包括联合索引也破例，终究索引节点上存储的都是指向主键索引的值，拿前面的用户表为例：

SELECT * FROM `zz_users` WHERE `user_name`="竹子" AND `user_sex`="男";

尽管这条SQL会走联合索引查询，可是依据联合索引查询出来的值仅是一个指向主键索引的ID，然后会拿着这个ID再去主键索引中查一遍，这个进程之前聊过，被称为回表进程。

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

比方假定此刻只需求user_name、user_sex、password这三个字段的信息，此刻SQL句子能够更改为如下状况：

SELECT
    `user_name`,`user_sex`,`password`
FROM 
    `zz_users` 
WHERE 
    `user_name` = "竹子" AND `user_sex` = "男";

此刻将SQL更改为查询所需的列后，就不会产生回表现象，Why？再这儿许多小伙伴或许会疑问，这是什么道理啊？由于此刻所需的user_name、user_sex、password三个字段数据，在联合索引中彻底包括，因而能够直接经过联合索引获取到数据。

但假如查询时用*，由于联合索引中不具有完好的一行数据，只能再次转向聚簇索引中获取完好的行数据，因而到这儿我们应该也了解了为什么查询数据时，不能用*的原因，这是由于会导致索引掩盖失效，形成回表问题。

当然，再来提一点比较有意思的作业，先看SQL：

EXPLAIN SELECT
    `user_name`,`user_sex`
FROM 
    `zz_users`
WHERE 
    `password` = "1234" AND `user_sex` = "男";

比方上述这条SQL，明显是不契合联合索引的最左前缀匹配准则的，但来看看履行成果：

这个成果是不是很令你惊讶，经过EXPLAIN剖析的成果显现，这条SQL居然运用了索引，这是什么原因呢？也是由于索引掩盖。

一句话概述：便是要查询的列，在运用的索引中现已包括，被所运用的索引掩盖，这种状况称之为索引掩盖。

3.2.2、索引下推

索引下推是MySQL5.6版别今后引进的一种优化机制，仍是以之前的用户表为例，先来看一条SQL句子：

INSERT INTO `zz_users` VALUES(5,"竹竹","女","8888","2022-09-20 22:17:21");
SELECT * FROM `zz_users` WHERE `user_name` LIKE "竹%" AND `user_sex`="男";

首先为了愈加直观的讲清楚索引下推，因而先再向用户表中添加一条数据。然后再来看看后边的查询SQL，这条SQL会运用联合索引吗？答案是会的，但只能部分运用，由于联合索引的每个节点信息大致如下：

{
    ["熊猫","女","6666"] : 1,
    ["竹子","男","1234"] : 2,
    ["子竹","男","4321"] : 3,
    ["1111","男","4321"] : 4,
    ["竹竹","女","8888"] : 5
}

由于前面运用的是含糊查询，但%在结束，因而能够运用竹这个字作为条件在联合索引中查询，整个查询进程如下：

• ①运用联合索引中的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」一次回表查询。

索引下推在MySQL5.6版别之后是默许敞开的，能够经过指令set optimizer_switch='index_condition_pushdown=off|on';指令来手动办理。

3.2.3、MRR(Multi-Range Read)机制

Multi-Range Read简称为MRR机制，这也是和索引下推一同在MySQL5.6版别中引进的功用优化措施，那什么叫做MRR优化呢？

一般来说，在实践事务中我们应当尽量经过索引掩盖的特性，削减回表操作以下降IO次数，但在许多时分往往又不得不做回表才干查询到数据，但回表明显会导致产生许多磁盘IO，一起更严重的一点是：还会产生许多的离散IO，下面举个例子来了解。

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去聚簇/主键索引中回表查询数据。

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

能够经过上述这条指令敞开或封闭MRR机制，MySQL5.6及今后的版别是默许敞开的。

3.2.4、Index Skip Scan索引跳动式扫描

在讲联合索引时，我们提到过最左前缀匹配准则，也便是SQL的查询条件中必需要包括联合索引的第一个字段，这样才干射中联合索引查询，但实践上这条规则也并不是100%遵从的。由于在MySQL8.x版别中参加了一个新的优化机制，也便是索引跳动式扫描，这种机制使得我们即使查询条件中，没有运用联合索引的第一个字段，也仍旧能够运用联合索引，看起来就像跳过了联合索引中的第一个字段相同，这也是跳动扫描的称号由来。

但跳动扫描究竟是怎样完成的呢？上个栗子快速了解一下。

比方此刻经过(A、B、C)三个列树立了一个联合索引，此刻有如下一条SQL：

SELECT * FROM `tb_xx` WHERE B = `xxx` AND C = `xxx`;

按理来说，这条SQL既不契合最左前缀准则，也不具有运用索引掩盖的条件，因而肯定是不会走联合索引查询的，但考虑一个问题，这条SQL中都现已运用了联合索引中的两个字段，成果还不能运用索引，这似乎有点亏啊对不？因而MySQL8.x推出了跳动扫描机制，但跳动扫描并不是真实的“跳过了”第一个字段，而是优化器为你重构了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去重也无法触发…..，总归有许多约束条件，具体的能够参阅《MySQL官网8.0-跳动扫描》。

其实这个跳动性扫描机制，只需在仅有性较差的状况下，才干发挥出不错的效果，假如你联合索引的第一个字段，是一个值具有仅有性的字段，那去重一次再拼接，简直就等价于走一次全表。

终究，能够经过经过set @@optimizer_switch = 'skip_scan=off|on';指令来挑选敞开或封闭跳动式扫描机制。当然，该参数仅限MySQL8.0以上的版别，假如在此之下的版别暂时就不必考虑了。

四、索引应用篇总结

至此，MySQL索引应用篇，也便是索引中篇就结束了，信任我们仔细看完本篇之后，关于索引的把握性、熟练程度肯定会更上一层楼，由于本章中从索引的好坏剖析，到树立索引的准则、索引失效的情景、运用索引的正确姿态、MySQL关于索引的优化机制等各方面，对索引进行了进一步论述。

经历中、上两篇的论述后，关于MySQL索引这个大系统现已树立出了完好的认知，下一篇便是《索引原理篇》啦，在中、上两篇中抛出了许多疑问，都留在了索引原理篇中去剖析，由于只需当你真实搞懂了索引的底层完成，才干更好的了解一些前面给出的主张、定论及概念。

当然，假如你以为我的文章对你有帮助，那能够动动发财的小手，点上一个免费的小赞赞~，点赞量足够多可加速《索引原理篇》的解锁进度，更文速度彻底取决于诸位的点赞数量！当然，就算不给赞，《索引原理篇》也不会缺席噢！终究再给出两条关于索引的查询指令：

• show status like '%Handler_read%';检查当时会话的索引运用状况。
• show global status like 'Handler_read%';：查询全局索引运用状况。

这两条指令在之后的《SQL调优篇》中会用到的，这些指令中的信息关于调优会有不小的参阅价值，当然，关于具体的剖析在后续的华章中再具体解说咯！