Mysql索引和索引失效

Aimyon_36

已于 2024-04-08 11:16:45 修改

阅读量121

点赞数

分类专栏：数据库文章标签： mysql 数据库数据结构

于 2023-04-22 14:42:45 首次发布

本文链接：https://blog.csdn.net/m0_57697768/article/details/130302968

版权

数据库专栏收录该内容

15 篇文章 0 订阅

订阅专栏

mysql索引

一、什么是索引以及为什么使用索引
- 1.索引的特点
- 2.索引的设计
二、B+树
三、索引相关概念
四、索引的代价、创建索引的情况
五、什么情况下索引会失效

本文以Mysql为栗子，为什么使用索引。

一、什么是索引以及为什么使用索引

1.索引的特点

在这里插入图片描述
首先，数据页是在磁盘上保存物理记录的单位，和操作系统上的页概念类似。数据库的页（也就是块）是在磁盘上保存物理记录的单位，和操作系统上的页概念类似。

因为数据的页地址不可能都是连续的，因此在查询数据时，需要不断的在磁盘寻址，这样的操作是非常耗时的；因此我们使用一种数据结构，将这些数据存储的页地址保存起来，当我们需要查询这些数据时，只需要查看这种数据结构便能知道这些数据的存储地址，进而加快查询效率。

该数据结构还需要对我们想要查找的数据进行标识，这样才能知道什么数据范围存储在磁盘上的那块位置。

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。

索引的优点:

（1）类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本，这也是创建索引最主要的原因。
（2）通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
（3）在实现数据的参考完整性方面，可以加速表和表之间的连接。换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度。
（4）在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低了CPU的消耗。

索引的缺点:

（1）创建索引和维护索引要且随着数据量的增加，所耗费的时间也会增加。
（2）索引需要占耗费时间，并磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，存储在磁盘上，如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸。
（3）虽然索引大大提高了查询速度，同时却会降低更新表的速度。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

2.索引的设计

mysql> CREATE TABLE index_demo(    
			c1 INT,     
			c2 INT,
 			c3 CHAR(1),
 		PRIMARY KEY(c1)) 
 		ROW_FORMAT = Compact

新建一个index_demo 表，其有2个INT类型的列，1个CHAR(1)类型的列，而且我们规定了c1列为主键，这个表使用Compact 行格式来实际存储记录的。

这里简化了index_demo表的行格式示意图：
在这里插入图片描述

record_type ：记录头信息的一项属性，表示记录的类型，0表示普通记录、2表示最小记录、3表示最大记录、1表示目录项记录。
next_record ：记录头信息的一项属性，表示下一条地址相对于本条记录的地址偏移量，我们用箭头来表明下一条记录是谁。
各个列的值：这里只记录在index_demo 表中的三个列，分别是c1 、c2和c3。
其他信息：除了上述3种信息以外的所有信息，包括其他隐藏列的值以及记录的额外信息

将这些记录放到页里边的示意图:
在这里插入图片描述
了解了数据存放在页中的结构，接下来就能够为这些数据页构建目录了:

以页28为例，它对应目录项2，这个目录项中包含着该页的页号28 以及该页中用户记录的最小主键值5。
我们只需要把几个目录项在物理存储器上连续存储（比如：数组），就可以实现根据主键值快速查找某条记录的功能了。
比如：查找主键值为20的记录，具体查找过程分两步：

先从目录项中根据二分法快速确定出主键值为20的记录在(目录项3，目录项4)，它对应的页是页9 。
再根据前边说的在页中查找记录的方式去目录项3中（因为页9 中定位具体的记录。至此，针对数据页做的简易目录就搞定了。这个目录有一个别名，称为索引。

目录项记录和普通的用户记录:

目录项记录的record_type 值是1，普通用户记录的record_type 值是0。
目录项记录主键值和页的编号两个列，而普通的用户记录的列是用户自己定义的，可能包含很多列，另外一些数据库引擎自己添加的隐藏列。

两者用的是一样的数据页，都会为主键值生成Page Directory （页目录），从而在按照主键值进行查找时可以使用二分法来加快查询速度。

二分法查找方式:
现在以查找主键为20的记录为例，根据某个主键值去查找记录的步骤就可以大致拆分成下边两步：

先到存储目录项记录的页，通过二分法快速定位到对应目录项，因为12 < 20 < 209 ，所以定位到对应的记录所在的页就是页9。
再到存储用户记录的页9中根据二分法快速定位到主键值为20的用户记录。

至此，针对数据页做的简易目录就搞定了。这个目录有一个别名，称为索引。

二、B+树

紧接着上边，如果插入一条新的数据会发生什么？
在这里插入图片描述
从图中可以看出，我们插入了一条主键值为320的用户记录之后需要两个新的数据页：

1.为存储该用户记录而新生成了页31 。
2.因为原先存储目录项记录的页30的容量已满（我们前边假设只能存储4条目录项记录），所以不得不需要一个新的页32 来存放页31 对应的目录项。

现在因为存储目录项记录的页不止一个，所以如果我们想根据主键值查找一条用户记录大致需要3个步骤，以查找主键值为20的记录为例：

确定目录项记录页我们现在的存储目录项记录的页有两个，即页30 和范围是页32 ，又因为页30表示的目录项的主键值的[1, 320) ，页32表示的目录项的主键值不小于320 ，所以主键值为20的记录对应的目录项记录在页30 中。
通过目录项记录页确定用户记录真实所在的页。在一个存储目录项记录的页中通过主键值定位一条目录项记录的方式说过了。
在真实存储用户记录的页中定位到具体的记录。

如果长期以此，随着数据的增加，索引的效率将会越来越低下，所以需要为索引添加索引:
在这里插入图片描述如上图，我们生成了一个存储更高级目录项的页33 ，这个页中的两条记录分别代表页30和页32，如果用户记录的主键值在[1, 320) 之间，则到页30中查找更详细的目录项记录，如果主键值不小于320 的话，就到页32中查找更详细的目录项记录。

至此，艺术已成！ 我们用下图对其进行描述:
在这里插入图片描述
这种数据结构就叫做B+树

一个B+树的节点其实可以分成好多层，规定最下边的那层，也就是存放我们用户记录的那层为第0层，之后依次往上加。之前我们做了一个非常极端的假设：存放用户记录的页记录的页最多存放3条记录，存放目录项最多存放4条记录。其实真实环境中一个页存放的记录数量是非常大的，假设所有存放用户记录的叶子节点代表的数据页可以存放100条用户记录，所有存放目录项记录的内节点代表的数据页可以存放1000条目录项记录，那么：

如果B+树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放100 条记录。
如果B+树有2层，最多能存放1000×100=10,0000 条记录。
如果B+树有3层，最多能存放1000×1000×100=1,0000,0000 条记录。
如果B+树有4层，最多能存放1000×1000×1000×100=1000,0000,0000 条记录。

一般情况下，我们用到的B+树都不会超过4层，那我们通过主键值去查找某条记录最多只需要做4个页面内的查找（查找3个目录项页和一个用户记录页），又因为在每个页面内有所谓的Page Directory （页目录），所以在页面内也可以通过二分法实现快速定位记录。

三、索引相关概念

索引按照物理实现方式，索引可以分为 2 种：聚簇（聚集）和非聚簇（非聚集）索引。
我们也把非聚集索引称为二级索引或者辅助索引。

1.聚簇索引

特点：

使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：
页内的记录是按照主键的大小顺序排成一个各个存放单向链表。
用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。
B+树的叶子节点存储的是完整的用户记录。所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

聚簇索引采用将数据数据和数据页排序的方式进行存储，顺序数据能很好的契合二分法的使用。

优点：

数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
聚簇索引对于主键的排序查找和范围查找速度非常快，按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的io操作

缺点：

插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。
更新主键的代价很高，因为将会导致被更新的行移动。
二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

因此在使用innoDB作为引擎的Mysql中，一般定义一个自增的id列为主键，并且该主键设置为不可更新。

2.二级索引(辅助索引、非聚簇索引)

二级索引是指在聚簇索引上的非主键索引，它的叶子节点存储的是主键值，而不是整行数据。二级索引的查询速度比主键索引慢，因为它需要先查询二级索引，再根据二级索引的键值查询聚簇索引。
在这里插入图片描述
我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根据c2列的值查找到完整的用户记录的话，仍然需要到聚簇索引中再查一遍，这个过程称为回表。
也就是根据c2列的值查询一条完整的用户记录需要使用到2棵B+树

看到这的小伙伴可能会有疑问:

对于创建了索引的列，其列值都是不可重复的

为什么宁愿多做一次回表操作都不把数据存放到叶子节点中呢？

虽然直接把完整的用户记录放到叶子节点可以避免回表操作，但是这样会导致空间浪费。如果一个表中只有很少的一部分记录被经常查询，那么为了这些记录创建一个单独的聚集索引是没有意义的。此外，如果表中的数据经常发生变化，那么频繁地更新聚集索引也会增加磁盘I/O和CPU消耗。因此，使用二级索引可以在一定程度上提高查询效率和空间利用率。

二级索引适用于那种情况？

当查询语句中涉及到非主键列时，可以使用二级索引来提高查询性能。
当表中的数据量较大时，使用二级索引可以减少磁盘空间的占用。
当需要频繁更新表中的数据时，使用二级索引可以加快更新操作的速度。

3.联合索引

我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让B+树按照c2和c3列的大小进行排序，这个包含两层含义：

先把各个记录和页按照c2列进行排序。
在记录的c2列相同的情况下，采用c3列进行排序

注意一点，以c2和c3列的大小为排序规则建立的B+树称为联合索引，本质上也是一个二级索引。它的意思与分别为c2和c3列分别建立索引的表述是不同的，不同点如下：建立联合索引只会建立如上图一样的1棵B+树。为c2和c3列分别建立索引会分别以c2和c3列的大小为排序规则建立2棵B+树

四、索引的代价、创建索引的情况

索引是个好东西，可不能乱建，它在空间和时间上都会有消耗:

空间上的代价

每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，一棵很大的B+树由许多数据页组成，那就是很大的一片存储空间。

时间上的代价

B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单向链表。

每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。
增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收等操作来维护好节点和记录的排序。
如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，会给性能拖后腿

既然创建索引的代价如此之大，该如何利用好索引，什么情况下该创建索引？

字段的数值有唯一性的限制

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba）
即使唯一性的索引会让insert操作插入数据前检验数据唯一性，降低了执行速度，但是和提高的查找速度相比是可忽略不计的。

频繁作为 WHERE 查询条件的字段

如果某个字段在SELECT语句的 WHERE 条件中经常被使用到，那么为其创建索引，在执行查询时就能避免全表扫描。

经常 GROUP BY 和 ORDER BY 的列

索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，就需要对分组或者排序的字段进行索引。如果待排序的列有多个，那么可以在这些列上建立组合索引。

UPDATE、DELETE 的 WHERE 条件列

对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。

.DISTINCT 字段需要创建索引

有时候我们需要对某个字段进行去重，使用 DISTINCT，那么对这个字段创建索引，也会提升查询效率。索引会对数据按照某种顺序进行排序，所以在去重的时候也会快很多。

多表 JOIN 连接操作时，创建索引注意事项

首先，连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。
其次，对 WHERE 条件创建索引，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。
最后，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。比如 course_id 在student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。

使用列的类型小的创建索引
使用字符串前缀创建索引
区分度高(散列性高)的列适合作为索引
使用最频繁的列放到联合索引的左侧
在多个字段都要创建索引的情况下，联合索引优于单值索引

什么情况下不能创建索引？

在where中使用不到的字段，不要设置索引
数据量小的表最好不要使用索引

在数据表中的数据行数比较少的情况下，比如不到 1000 行，是不需要创建索引的。

有大量重复数据的列上不要建立索引

当数据重复度大，比如高于 10%的时候，也不需要对这个字段使用索引，数据重复度大，在索引中存储的相同主键值也会增多。

避免对经常更新的表创建过多的索引
不建议用无序的值作为索引

例如身份证、UUID(在索引比较时需要转为ASCII，并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等。

五、什么情况下索引会失效

最佳左前缀法则：索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

计算、函数、类型转换(自动或手动)导致索引失效

MySQL需要对查询条件进行解析和执行，而在执行过程中，MySQL会根据查询条件来选择使用哪个索引。如果查询条件中包含了计算、函数或类型转换等内容，那么因为索引保存的是原始值，而不是经过函数计算后的值，自然就没办法走索引了。

类型转换导致索引失效

MySQL在比较不同数据类型时会进行隐式类型转换(字符串类型在比较时使用的是ASCII码)，但这可能导致索引无法使用。

范围条件右边的列索引失效

MySQL在处理范围条件时，会将范围条件右边的列作为常量进行比较，而不会使用该列上的索引。因此，如果查询语句中的范围条件右边的列上有索引，那么这个索引就无法被使用，从而导致索引失效

例如:在name字段上创建索引
SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.classId>20 AND student.name = ‘abc’ ;
如果要让索引生效只需要将范围条件查询语句放在最后:
SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name =
‘abc’ AND student.classId>20 ;

不等于(!= 或者<>)索引失效

MySQL在处理不等于条件时，会将不等于条件右边的列作为常量进行比较，而不会使用该列上的索引。因此，如果查询语句中的不等于条件右边的列上有索引，那么这个索引就无法被使用，从而导致索引失效。

is null可以使用索引，is not null无法使用索引

在MySQL中，当一个列上存在NULL值时，该列上的索引会被标记为不可用状态，因此在使用索引进行查询时，MySQL会放弃使用索引进行优化。

like以通配符%开头索引失效

MySQL无法使用索引来查找以通配符开头的字符串。可以使用x%但不能使用%x，因为匹配是从左到右进行，对字符串进行匹配时第一个如果是%将无法匹配索引。

OR前后存在非索引的列，索引失效

如果查询语句中存在OR条件，并且OR条件前后存在非索引列，那么即使这些列上有索引，也无法使用索引进行查询优化。这是因为MySQL在处理查询语句时，需要先对查询条件进行解析和执行，而在执行过程中，MySQL会根据查询条件来选择使用哪个索引。如果查询条件中包含了OR条件，那么MySQL就需要同时考虑多个索引，而无法确定应该使用哪个索引，因此只能进行全表扫描。

口决:模型数空运最快

模：代表模糊查询的意思，我们在使用like关键字的时候，如果是%开头，那么索引就会失效。
型：代表数据类型，如果数据类型错误了，索引也会失效。
数：代表函数的意思，对索引的字段使用内部函数，索引也会失效，这种情况应该建立基于函数的索引。
空：代表英文的 null 的意思，索引不存储空值，如果不限制索引列是 Not Null，数据库会认为索引列有可能存在空值，所以也不会按照索引进行计算。
运：代表运算的意思，对索引列进行加、减、乘、除等运算，会导致索引失效。
最：代表最左原则的意思，在复合索引中，索引列的顺序非常重要，如果不是按照索引列最左列开始进行查找，则无法使用索引。
快：代表全表扫描更快的意思，如果数据库预计使用全表扫描比使用索引更快的话，就不会使用索引。