索引失效的原因是什么

共计 5706 个字符，预计需要花费 15 分钟才能阅读完成。

本篇内容主要讲解“索引失效的原因是什么”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让丸趣 TV 小编来带大家学习“索引失效的原因是什么”吧!

MySQL 数据是如何存储的?

聚集索引

我们先建如下的一张表

CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT  学号 , `name` varchar(10) NOT NULL COMMENT  学生姓名 , `age` int(11) NOT NULL COMMENT  学生年龄 , PRIMARY KEY (`id`), KEY `idx_name` (`name`) ) ENGINE=InnoDB;

插入如下 sql

insert into student (`name`, `age`) value(a , 10); insert into student (`name`, `age`) value(c , 12); insert into student (`name`, `age`) value(b , 9); insert into student (`name`, `age`) value(d , 15); insert into student (`name`, `age`) value(h , 17); insert into student (`name`, `age`) value(l , 13); insert into student (`name`, `age`) value(k , 12); insert into student (`name`, `age`) value(x , 9);

数据如下

图片 mysql 是按照页来存储数据的，每个页的大小为 16k。

在 MySQL 中可以通过执行如下语句，看到一个页的大小

show global status like  innodb_page_size

结果为 16384，即 16kb

在 InnoDB 存储引擎中，是以主键为索引来组织数据的。记录在页中按照主键从小到大的顺序以单链表的形式连接在一起。

可能有小伙伴会问，如果建表的时候，没有指定主键呢?

如果在创建表时没有显示的定义主键，则 InnoDB 存储引擎会按如下方式选择或创建主键。

首先判断表中是否有非空的唯一索引，如果有，则该列即为主键。如果有多个非空唯一索引时，InnoDB 存储引擎将选择建表时第一个定义的非空唯一索引作为主键

如果不符合上述条件，InnoDB 存储引擎自动创建一个 6 字节大小的指针作为索引

页和页之间以双链表的形式连接在一起。并且下一个数据页中用户记录的主键值必须大于上一个数据页中用户记录的主键值

假设一个页只能存放 3 条数据，则数据存储结构如下。

可以看到我们想查询一个数据或者插入一条数据的时候，需要从最开始的页开始，依次遍历每个页的链表，效率并不高。

我们可以给这页做一个目录，保存主键和页号的映射关系，根据二分法就能快速找到数据所在的页。但这样做的前提是这个映射关系需要保存到连续的空间，如数组。如果这样做会有如下几个问题

随着数据的增多，目录所需要的连续空间越来越大，并不现实

当有一个页的数据全被删除了，则相应的目录项也要删除，它后面的目录项都要向前移动，成本太高

我们可以把目录数据放在和用户数据类似的结构中，如下所示。目录项有 2 个列，主键和页号。

数据很多时，一个目录项肯定很多，毕竟一个页的大小为 16k，我们可以对数据建立多个目录项目，在目录项的基础上再建目录项，如下图所示

这其实就是一颗 B + 树，也是一个聚集索引，即数据和索引在一块。叶子节点保存所有的列值

以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17   亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问  3 次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。

非聚集索引

聚集索引和非聚集索引非常类似，区别如下

聚集索引叶子节点的值为所有的列值非聚集索引叶子节点的值为索引列 + 主键

当我们查询 name 为 h 的用户信息时 (学号，姓名，年龄)，因为 name 上建了索引，先从 name 非聚集索引上，找到对应的主键 id，然后根据主键 id 从聚集索引上找到对应的记录。

从非聚集索引上找到对应的主键值然后到聚集索引上查找对应记录的过程为回表

联合索引 / 索引覆盖

假设 teacher 表定义如下，在 name 和 age 列上建立联合索引

CREATE TABLE `teacher` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT  教师编号 , `name` varchar(10) NOT NULL COMMENT  教师姓名 , `age` int(11) NOT NULL COMMENT  教师年龄 , `ismale` tinyint(3) NOT NULL COMMENT  是否男性 , PRIMARY KEY (`id`), KEY `idx_name_age` (`name`, `age`) ) ENGINE=InnoDB;

插入如下 sql

insert into teacher (`name`, `age`, `ismale`) value(aa , 10, 1); insert into teacher (`name`, `age`, `ismale`) value(dd , 12, 0); insert into teacher (`name`, `age`, `ismale`) value(cb , 9, 1); insert into teacher (`name`, `age`, `ismale`) value(cb , 15, 1); insert into teacher (`name`, `age`, `ismale`) value(bc , 17, 0); insert into teacher (`name`, `age`, `ismale`) value(bb , 15, 1); insert into teacher (`name`, `age`, `ismale`) value(dd , 15, 1); insert into teacher (`name`, `age`, `ismale`) value(dd , 12, 0);

对 name 和 age 列建立联合索引

目录页由 name 列，age 列，页号这三部分组成。目录会先按照 name 列进行排序，当 name 列相同的时候才对 age 列进行排序。

数据页由 name 列，age 列，主键值这三部分组成。同样的，数据页会先按照 name 列进行排序，当 name 列相同的时候才对 age 列进行排序。

当执行如下语句的时候，会有回表的过程

select * from student where name =  aa

当执行如下语句的时候，没有回表的过程

select name, age from student where name =  aa

为什么不需要回表呢?

因为 idx_name_age 索引的叶子节点存的值为主键值，name 值和 age 值，所以从 idx_name_age 索引上就能获取到所需要的列值，不需要回表，即索引覆盖

仔细看一下联合索引这个图，你就基本上能明白为什么不满足最左前缀原则的索引会失效?

索引下推

当执行如下语句的时候

select * from student where name like  张 %  and age = 10 and ismale = 1;

在 5.6 版本之前的执行过程如下，先从 idx_name_age 索引上找到对应的主键值，然后回表找到对应的行，判断其他字段的值是否满足条件

在 5.6 引入了索引下推优化，可以在遍历索引的过程中，对索引中包含的字段做判断，直接过滤掉不满足条件的数据，减少回表次数，如下图

最左前缀原则

加速查询

主要针对组合索引，满足如下 2 个条件即可满足左前缀原则

需要查询的列和组合索引的列顺序一致

查询不要跨列

构造数据如下，其中在 name，address，country 上建了联合索引

CREATE TABLE `people` ( `name` varchar(50) NOT NULL, `address` varchar(50) NOT NULL, `country` varchar(50) NOT NULL, KEY `idx_name_addr_country` (`name`,`address`,`country`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

举几个例子，下面涉及到一些 explain 相关的知识，后面单开一篇长文来做介绍

例子一

explain select * from people where name =  jack and address =  beijing  and country =  china

type 为 ref，key_len 为 456=(50*3+2)*3，联合索引的所有列都使用了

例子二

explain select * from people where name =  jack

type 为 ref，key_len 为 152=50*3+2，联合索引只使用了 name 列

例子三

explain select * from people where address =  beijing

type 为 index，表明查询的时候对整个索引进行了扫描，并没有加速查找。

假设有如下的联合索引 key idx_a_b_c(a,b,c)

sql 是否使用索引 where a = x and b = x and c = x 是 where a = x and b = x 是，部分索引 where a = x 是，部分索引 where b = x 否，不包含最左列 namewhere b = x and c = x 否，不包含最左列 name

如果你仔细看了前面联合索引是如何存储的，那你一定能看懂是否使用索引的介绍

目录页是按照 a b c 列的顺序依次递增排序的。先按照 a 列排序，如果 a 列相同，再按照 b 列排序，如果 b 列相同，才按照 c 列排序

所以查询列值 a b c，则这个排序规则能用到，即会走索引。如果只查列值 b，并不能用到这个排序规则，所以得遍历所有的记录

加速排序

最左前缀原则不仅用在查询中，还能用在排序中。MySQL 中，有两种方式生成有序结果集：

通过有序索引顺序扫描直接返回有序数据

Filesort 排序，对返回的数据进行排序

因为索引的结构是 B + 树，索引中的数据是按照一定顺序进行排列的，所以在排序查询中如果能利用索引，就能避免额外的排序操作。EXPLAIN 分析查询时，Extra 显示为 Using  index。

所有不是通过索引直接返回排序结果的操作都是 Filesort 排序，也就是说进行了额外的排序操作。EXPLAIN 分析查询时，Extra 显示为 Using  filesort，当出现 Using filesort 时对性能损耗较大，所以要尽量避免 Using filesort

还是先举 2 个例子，然后总结

explain select * from people order by name

Extra 列只有 Using index，即根据索引顺序进行扫描

explain select * from people order by address

在这里插入图片描述

Extra 列有 Using filesort

总结：假如说有如下联合索引，key idx_a_b_c(a,b,c)

order by 能使用索引排序

order by a order by a,b order by a,b,c order by a desc, b desc, c desc where a = const order by b,c where a = const and b = const order by c where a = const and b   const order by b,c

order by 不能使用索引进行排序

order by b order by c order by b, c order by a asc, b desc, c desc // 排序不一致  where g = const order by b,c // 丢失 a 索引  where a = const order by c // 丢失 b 索引  where a = const order by a,d // d 不是索引的一部分  where a in (...) order by b,c // 范围查询 

这个原因就不用我解释了把，相信你一定看懂了

联合索引的好处

索引覆盖，减少了很多回表的操作，提高了查询的效率

索引下推，索引列越多，通过索引筛选出的数据越少。有 1000W 条数据的表，有如下 sql:select * from table where col1=1  and col2=2 and  col3=3, 假设假设每个条件可以筛选出 10% 的数据，如果只有单值索引，那么通过该索引能筛选出 1000W10%=100w 条数据，然后再回表从 100w 条数据中找到符合 col2=2  and col3= 3 的数据; 如果是联合索引，通过索引筛选出 1000w*10%*10% *10%=1w，效率提升可想而知!

索引为什么会失效?

当别人问我索引在什么条件下会失效时，我能背出一大堆规则

不要在索引列上进行运算或使用函数

前导模糊查询不会使用索引，例如 like % 李

负向条件索引不会使用索引，建议用 in。负向条件有：!=、、not in、not exists、not like 等

索引是按照一定规则排好序的，如果对索引列使用函数，或者 like % 李，具体的值都不知道，它怎么在 B + 树上加速查询?

到此，相信大家对“索引失效的原因是什么”有了更深的了解，不妨来实际操作一番吧！这里是丸趣 TV 网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！