1、什么是MySQL索引？

数据库在未添加索引进行查询的时候默认是进行全文搜索，也就是说有多少数据就进行多少次查询，然后找到相应的数据就把它们放到结果集中，直到全文扫描完毕。索引是帮助MySQL高效获取数据的数据结构，通俗点的说，数据库索引好比是一本书的目录，可以直接根据页码找到对应的内容，目的就是为了加快数据库的查询速度。

优点：

• 大大提高数据查询速度。
• 可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。
• 通过索引列对数据进行排序，降低数据的排序成本降低了CPU的消耗。
• 被索引的列会自动进行排序，包括【单例索引】和【组合索引】，只是组合索引的排序需要复杂一些。
• 如果按照索引列的顺序进行排序，对order不用语句来说，效率就会提高很多。

缺点：

• 索引会占据磁盘空间。
• 索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改查操作，MySQL不仅要保存数据，还要保存或者更新对应的索引文件。
• 维护索引需要消耗数据库资源。

索引的分类

• 主键索引：设定为主键后，数据库自动建立索引，主键索引列值不能为空（Null）且只允许创建一个，主键索引的关键字为Primary。

• 唯一索引：索引列的值必须唯一，但允许有空值（Null），但只允许有一个空值（Null），一张表中可以存在多个唯一索引，唯一索引的关键字是Unique。

• 单列索引：针对一列数据建立的索引，包含（主键索引，唯一索引）

• 复合索引：一个索引可以包含多个列，多个列共同构成一个复合索引。

• 全文索引：主要用于查询文本中的关键字，全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找允许在这些索引列中插入重复值和空值。全文索引仅允许在Char、VarChar、Text 上创建。

• 空间索引：是对空间数据类型的字段建立的索引，空间索引的列必须是Not Null，只有MyISAM支持该索引。

索引存储结构

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的，但根据存储引擎的不同，索引的存储方式也不相同，比如：

• 【MYISAM】:存储在单独的索引文件中。
• 【InnoDB】:索引与数据一起存储在数据文件当中。

MySQL的索引数据结构最常使用的是B+Tree，但对于主要的两种存储引擎的实现方式是不同的，不同之处在于：

【InnoDB】 存储引擎：B + 树索引的叶子节点保存数据本身，如下图：

InnoDB 存储引擎根据索引类型不同，分为聚簇索引和二级索引。对主键字段建立的索引叫做聚簇索引（上图就是聚簇索引），对普通字段建立的索引叫做二级索引（也称为聚簇索引，下图就是二级索引）。 它们区别在于，聚簇索引的叶子节点存放的是实际数据，所有完整的用户数据都存放在聚簇索引的叶子节点，而二>级索引的叶子节点存放的是主键值，而不是实际数据。如果将 name 字段设置为普通索引，那么这个二级索引长下图这样，叶子节点仅存放主键值。

例如：

--使用主键索引id进行查询的时候，由于叶子节点存储的是行数据，因此只需要遍历一次索引树，就可以将结果直接返回。
select * from t_user where id = 1;//id为主键索引

-- 第一次遍历索引树通过name找到叶子节点存储的id，再通过id遍历索引树，通过id找到对应的数据，该过程称为回表。
select * from t_user where name="林某";// name为非聚簇索引，也称二级索引。

-- 如果我们使用非聚簇索引进行查询的时候，要查询的数据在叶子节点，那么只需要在「二级索引」的 B+ 树找到>对应的叶子节点，然后读取要查询的数据，这个过程叫做覆盖索引。例如：
select id from t_user where name="林某";// name 字段为非聚簇索引，也称二级索引。

在 MySQL 的 InnoDB 引擎中，每个表都有一个聚簇索引，(只有叶子节点才会存储数据，非叶子节点只存储键值key；叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。)构建，这种叶子节点直接存储数据，只需要遍历一次索引树，这个索引我们称为“聚簇索引”，一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。InnoDB创建索引的具体规则如下：

• 在创建表时，定义主键，InnoDB会自动将主键索引用作聚簇索引。
• 如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。
• 如果以上两个都没有，InnoDB会自动使用一个长度为6字节的ROWID字段来构建聚簇索引，该ROWID字段会在插入新的行记录时自动递增。

聚簇索引和非聚簇索引最大的区别在于叶子节点存储的数据不同，聚簇索引叶子节点存储的是行数据，因此通过聚簇索引可以直接找到真正的行数据；而非聚簇索引叶子节点存储的是主键信息，使用非聚簇索引还需要回表查询，因此我们可以得出聚簇索引和非聚簇索引的区别主要有以下几个：

• 聚簇索引叶子节点存储的是行数据；而非聚簇索引叶子节点存储的是聚簇索引（通常是主键 ID）。
• 聚簇索引查询效率更高，而非聚簇索引需要进行回表查询，因此性能不如聚簇索引。
• 聚簇索引一般为主键索引，而主键一个表中只能有一个，因此聚簇索引一个表中也只能有一个，而非聚簇索引则没有数量上的限制。

【MyISAM】 存储引擎：B + 树索引的叶子节点保存数据的物理地址，如下图：

2、索引失效场景

首先创建表，此外，还创建了三个索引：

• id：数据库的主键
• idx_code_age_name：由code、age和name三个字段组成的联合索引。
• idx_height：普通索引

CREATE TABLE `user` (  
  `id` int NOT NULL AUTO_INCREMENT,  
  `code` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,  
  `age` int DEFAULT '0',  
  `name` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,  
  `height` int DEFAULT '0',  
  `address` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,  
  PRIMARY KEY (`id`),  
  KEY `idx_code_age_name` (`code`,`age`,`name`),  
  KEY `idx_height` (`height`)  
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

插入数据：

INSERT INTO sue.user (id, code, age, name, height) VALUES (1, '101', 21, '周星驰', 175,'香港'); 
INSERT INTO sue.user (id, code, age, name, height) VALUES (2, '102', 18, '周杰伦', 173,'台湾'); 
INSERT INTO sue.user (id, code, age, name, height) VALUES (3, '103', 23, '苏三', 174,'成都');

不满足最左匹配原则

生效场景

-- 需要注意的是，因为有查询优化器，所以 code 字段在 where 子句的顺序并不重要。
explain select * from user where code='101';
explain select * from user where code='101' and age=21;
explain select * from user where code='101' and age=21 and name='周星驰';

执行结果:

-- 特殊情况
explain select * from user where code = '101'  and name='周星驰';
-- 这种其实严格意义上来说是属于索引截断，不同版本处理方式也不一样。
-- `MySQL 5.5` 的话，前面 code 会走索引，在联合索引找到主键值后，开始回表，到主键索引读取数据行，然后再比对 `c` 字段的值。
-- `MySQL5.6` 之后，有一个索引下推功能，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

--大概原理是：截断的字段会被下推到存储引擎层进行条件判断（因为 c 字段的值是在 `(a, b, c)` 联合索引里的），然后过滤出符合条件的数据后再返回给 `Server` 层。由于在引擎层就过滤掉大量的数据，无需再回表读取数据来进行判断，减少回表次数，从而提升了性能。

--比如下面这条 `code = '101' and name='周星驰'` 语句，我们可以从执行计划中的 `Extra=Using index condition` 中看到使用了索引下推功能。

失效场景

explain select * from user where age=21;
explain select * from user where name='周星驰';
explain select * from user where name='周星驰';

执行结果:

原因：

为什么联合索引不遵循最左匹配原则就会失效？

因为在联合索引的情况下，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序。 也就是说，如果我们想使用联合索引中尽可能多的列，查询条件中的各个列必须是联合索引中从最左边开始连续的列。如果我们仅仅按照第二列搜索，肯定无法走索引。

索引列上进行计算

-- 由于id字段是主键，该sql语句用到了主键索引。
explain select * from user where id=1;

执行结果：

-- 对id列进行了计算
explain select * from user where id + 1 = 2;

执行结果：

但是，如果把查询语句的条件改成 where id = 10 - 1，这样就不是在索引字段进行表达式计算了，于是就可以走索引查询了。

原因

为什么对索引进行表达式计算，就无法走索引了呢？

因为索引保存的是索引字段的原始值，而不是 id + 1 表达式计算后的值，所以无法走索引，只能通过把索引字段的值都取出来，然后依次进行表达式的计算来进行条件判断，因此采用的就是全表扫描的方式。

对索引使用函数

有时候我们会用一些MySQL自带的函数来得到我们想要的结果，这时候要注意了，如果查询条件中对索引字段使用函数，就会导致索引失效。比如下面这条语句查询条件中对height字段使用了SUBSTR函数，执行计划中的 type=ALL，代表了全表扫描：

explain select * from user  where SUBSTR(height,1,2)=17;

执行结果:

为什么对索引使用函数，就无法走索引了呢？

因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了。 不过，从MySQL 8.0开始，索引特性增加了函数索引，即可以针对函数计算后的值建立一个索引，也就是说该索引的值是函数计算后的值，所以就可以通过扫描索引来查询数据。举个例子，我通过下面这条语句，对 length(name) 的计算结果建立一个名为 idx_name_length 的索引。

-- 创建函数索引
alter table user add key idx_name_length (SUBSTR(height,1,2));

-- 执行该SQL我们就会发现走了索引。
explain select * from user  where SUBSTR(height,1,2)=17;

字段类型不同

不失效场景

-- user表中code字段，它是varchar类型，就会走索引
explain select * from user where code="101";

执行结果：

-- user表中height是int类型，传参却是varchar类型，此时却走了索引
-- 因为mysq发现如果是int类型作为查询条件的话，就会自动将该字段的传参进行隐式替换
-- 将字符转为int
explain select * from user where height='175';

失效场景

-- user表中code字段，它是varchar类型，= 后面是int类型，此时索引失效
explain select * from user where code=101;

执行结果:

原因

为什么字符串类型的字段，传入了int类型的参数时索引会失效呢？

字符串'1'、' 1 '、'1a'都能转换成int类型的1，也就是说可能会出现多个字符串，对应一个int类型参数的情况。那么，mysql怎么知道该把int类型的1转换成哪种字符串，用哪个索引快速查值?

like左边包含%

生效场景

explain select * from user where code like '10%';

执行结果：

失效场景

explain select * from user where code like '%1';
explain select * from user where code like '%1%';

执行结果：

原因

当like语句中的%，出现在查询条件的左边时，索引为什么会失效？

因为索引 B + 树是按照「索引值」有序排列存储的，只能根据前缀进行比较。 举个例子，下面这张二级索引图，是以 name 字段有序排列存储的。 假设我们要查询 name 字段前缀为「林」的数据，也就是 name like '林%'，扫描索引的过程：

• 首节点查询比较：林这个字的拼音大小比首节点的第一个索引值中的陈字大，但是比首节点的第二个索引值中的周字小，所以选择去节点2继续查询；
• 节点 2 查询比较：节点2的第一个索引值中的陈字的拼音大小比林字小，所以继续看下一个索引值，发现节点2有与林字前缀匹配的索引值，于是就往叶子节点查询，即叶子节点4；
• 节点 4 查询比较：节点4的第一个索引值的前缀符合林字，于是就读取该行数据，接着继续往右匹配，直到匹配不到前缀为林的索引值。

如果使用 name like '%林' 方式来查询，因为查询的结果可能是「陈林、张林、周林」等之类的，所以不知道从哪个索引值开始比较，于是就只能通过全表扫描的方式来查询。

WHERE 子句中的 OR

生效场景

-- 由于对id和height本身就存在索引，因此索引不会失效
explain select * from user where id=1 or height='175';

执行结果:

失效场景

-- address未加索引
explain select * from user where id=1 or height='175' or address='成都';

执行结果：

in/not in/exists/not exists

in

explain select * from user where height in (173,174,175,176);

执行结果：

exists

explain select * from user t1here  exists (select 1 from user t2 where t2.height=173 and t1.id=t2.id)

执行结果：

not in

explain select * from user where height not in (173,174,175,176);

执行结果：

not exists

explain select * from user t1here  not exists (select 1 from user t2 where t2.height=173 and t1.id=t2.id)

执行结果：

ORDER BY

生效场景

满足最左匹配原则

-- order by后面的条件，也要遵循联合索引的最左匹配原则。具体有以下sql：
explain select * from user order by limit 100;
explain select * from user order by code,age limit 100;
explain select * from user order by code,age,name limit 100;

执行结果： 注意：除了遵循最左匹配原则之外，有个非常关键的地方是，后面还是加了limit关键字，如果不加它索引会失效。

配合where一起使用

-- order by还能配合where一起遵循最左匹配原则。
explain select * from user where code='101' order by age;

执行结果：

code是联合索引的第一个字段，在where中使用了，而age是联合索引的第二个字段，在order by中接着使用。假如中间断层了，sql语句变成这样，执行结果会是什么呢？

explain select * from user where code='101' order by name;

执行结果：

虽说name是联合索引的第三个字段，但根据最左匹配原则，该sql语句依然能走索引，因为最左边的第一个字段code，在where中使用了。只不过order by的时候，排序效率比较低，需要走一次filesort排序罢了。

相同的排序

order by后面如果包含了联合索引的多个排序字段，只要它们的排序规律是相同的（要么同时升序，要么同时降序），也可以走索引。

explain select * from user order by code desc,age desc limit 100;

执行结果： 该示例中order by后面的code和age字段都用了降序，所以依然走了索引。

两者都有

如果某个联合索引字段，在where和order by中都有，结果会怎么样？

explain select * from user where code='101' order by code, name;

执行结果：

code字段在where和order by中都有，对于这种情况，从图中的结果看出，还是能走了索引的。

失效场景

没加where或limit

--如果order by语句中没有加where或limit关键字，该sql语句将不会走索引。
explain select * from user order by code, name;

执行结果： 从图中看出索引真的失效了。

对不同的索引做order by

--对多个索引列进行order by，也会失效
explain select * from user order by code, height limit 100;

执行结果：

不满足最左匹配原则

-- 不满足最左匹配原则
explain select * from user order by name limit 100;

执行结果：

name字段是联合索引的第三个字段，从图中看出如果order by不满足最左匹配原则，确实不会走索引。

不同的排序

-- 多个索引列不相同的排序规则
explain select * from user order by code asc,age desc limit 100;

执行结果：

从图中看出，尽管order by后面的code和age字段遵循了最左匹配原则，但由于一个字段是用的升序，另一个字段用的降序，最终会导致索引失效。

总结

今天给大家介绍了 8 种会发生索引失效的情况：

模 - 当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；

型 - MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索引列会发生隐式类型转换，由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效。

数 - 当我们在查询条件中对索引列使用函数，就会导致索引失效。

空 - 索引不存储空值，如果不限制索引列是not null，数据库会认为索引列有可能存在空值，索引也不会按照索引进行计算。

运 - 当我们在查询条件中对索引列进行表达式计算，也是无法走索引的。

最 - 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。

快 - 如果数据库觉得全表扫描比索引查询更快的时候则不会去走索引。

OR - 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。