MySQL高级十：索引

索引

导读

1. 核心概念：索引本质上是一种数据结构
2. B+树：索引方式的一种，是数据在磁盘上真实的存储方式
3. 在navicat或终端上看到的数据表的格式，只是人机交互的一种人性化展示。

一、概述

1. 定义

   索引是存储引擎用于快速找到数据记录的一种数据结构。

   也可理解为是“满足特定查找算法，排好序的数据结构”，这样就可以在数据结构的基础上实现高级查找算法。

2. 作用（重点理解）

   索引是在存储引擎中实现的，数量和长度由具体的存储引擎定义。

   索引好比课本的目录，存储引擎进行数据查找时，首先查看SQL条件是否命中某条索引，符合则通过索引查找相关数据，如果不符合则需全表扫描。

3. 目的

   减少与磁盘IO的次数，因为每次IO操作的耗时是内存层面算法上查询耗时的几十甚至上百倍

4. 数据存储结构的演变

   

5. 优点

   （1）类似大学图书馆建书目索引，提高数据检索的效率，降低 数据库的IO成本 ，这也是创建索引最主 要的原因。

   （2）通过创建唯一索引，可以保证数据库表中每一行 数据的唯一性 。

   （3）在实现数据的 参考完整性方面，可以 加速表和表之间的连接 。换句话说，对于有依赖关系的子表和父表联合查询时， 可以提高查询速度。

   （4）在使用分组和排序子句进行数据查询时，可以显著 减少查询中分组和排序的时间，降低了CPU的消。

6. 缺点

   （1）创建索引和维护索引要 耗费时间 ，并 且随着数据量的增加，所耗费的时间也会增加。

   （2）索引需要占 磁盘空间 ，除了数据表占数据空间之 外，每一个索引还要占一定的物理空间， 存储在磁盘上 ，如果有大量的索引，索引文件就可能比数据文 件更快达到最大文件尺寸。

   （3）虽然索引大大提高了查询速度，同时却会 降低更新表的速度 。当对表 中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

7. 小技巧

   针对索引的缺点，为了减弱频繁增删改操作带来的索引耗时，可以先删除表中的索引，待操作完成后，再重新创建索引

二、索引的由来

因为索引是在存储引擎中实现的，MySQL5.7后，主要使用InnoDB存储引擎，故接下来主要研究InnoDB中的索引

未使用索引之前

1. 在一个页中（数据量少）

   ① 以主键为搜索条件

   ​ 一般主键是自增长（或有序的），可通过二分法快速定位到对应的槽，再遍历该操作对应分组中快速找到指定的记录。

   ② 以其他列作为搜索条件

   ​ 一般非主键都是无序的，只能依次遍历单链表中的每条记录

2. 在多个页中（数据量巨大）

   不论是通过主键还是其它列，由于无法快速定位到记录所在的页，所以只能从第一页，沿着双向列表一个一个的往下找，也可以说就是单纯的遍历，效率更低。并且还要将页从磁盘上读取到内存中，更是超级耗时。

   为了解决该弊端，索引应用而生。

构建索引

1. 行格式

   CREATE TABLE index_demo (
       c1 INT,
       c2 INT,
       c3 CHAR(1),
       PRIMARY KEY(c1)
   ) ROW_FORMAT = Compact
   1
   2
   3
   4
   5
   6

   compact行格式的简化模型

   

   record_type ：记录头信息的一项属性，表示记录的类型， 0 表示普通记录、 1 表示目录项记录、 2 表示最小记 录、 3 表示最大记录。

   next_record ：记录头信息的一项属性，表示下一条地址相对于本条记录的地址偏移量，我们用 箭头来表明下一条记录是谁。

   各个列的值 ：这里只记录在 index_demo 表中的三个列，分别是 c1 、 c2 和 c3

   其他信息 ：除了上述3种信息以外的所有信息，包括其他隐藏列的值以及记录的额外信息。

2. 页

   数据表中的记录是放在页中的，一个页(16kB)中存放的数据量是有限的，故一个数据表中的记录会被放置在多个不同的页中。

   页的基本模型如下

   

3. 目录项

   因为各个页中的记录并没有规 律，我们并不知道我们的搜索条件匹配哪些页中的记录，所以不得不依次遍历所有的数据页。为了快速定位到需要查找的记录在哪些数据页 中，我们可以为数据页建 立一个目录 。

   建立该目录需要两个步骤：

   ① 满足条件：下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。

   ② 给所有页都分别建立一个目录项

   这些目录项共同组成了目录。

   

4. 补充说明

   链表关系：各数据页的编号（地址）是不连续的，故相邻两个页之间需使用双向链表建立联系。

   页分裂 ：因增删改操作，会导致记录存储位置从当前页转移到另一个页，这种记录移动的过程称为页分裂

5. 上述过程构建好的这个目录结构就是索引

三、B+树

1. 目录页

   将目录项组合在一起，就可以组成目录页

   

2. 更高级的目录页

   当目录页多于一个时，就会生成一个存储目录页的目录页，也可称为更高级的目录页

   

3. B+树的模型

   上述模型的抽象结构就是B+树。

   

4. B+树层级

   一般，B+树的层级不会超过4层，且B+树越低，IO的次数就会越少。

   数据页的大小默认是16kB，假设每个数据页中存放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 条记录，即1000亿条记录。

   ​ 已经是相当多的记 录了！！！

四、索引的基本概念

索引按照物理实现方式，可分为2种：聚簇索引和非聚簇索引。

1. 聚簇索引

   定义：针对主键构成的，且具备如下四种特性的索引，就称为聚簇索引。

   本质：并不是一种单独的索引类型，而只是一种数据存储方式。由于所有的用户记录都存储在叶子节点，也称为 索引即数据，数据即索引。（如InnoDB存储引擎格式下的数据表的.ibd文件中，就是索引和数据存 储在一起）

   特性：
   ① 页内 的记录是按照主键的大小顺序排成一个 单向链表 。

   ​ ② 各个存放 用户记录的页与页之间也是根据页中用户记录的主键大小顺序排成一个 双向链表 。

   ​ ③ 存放 目录项记录的页 分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键 大小顺序排成一个 双向链表 。

   ​ ④ 叶子节点中存储的是完整的用户记录

   优点：
   ① 数据访问更快 ，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快

   ② 聚簇索引对于主键的 排序查找 和 范围查找 速度非常快

   ③ 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多 个数据块中提取数据，所以 节省了大量的io操作 。

   缺点：
   ① 插入速度严重依赖于插入顺序 ，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影 响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键

   ② 更新主键的代价很高 ，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为 不可更新

   限制：
   ① 在MySQL数据库中，只有InnoDB存储引擎支持聚簇索引

   ​② 数据的物理存储排序方式只能有一种，所以每个数据表只能有一个聚簇索引

   ​③ 如果没有定义主键，InnoDB会自动选择非空的唯一索引代替

   ​④ 为了充分发挥聚簇索引的特性，故数据表的主键尽量选用有序的id

   注意：
   聚簇索引无需我们在MySQL语句中显示的使用INDEX语句去创建，InnoDB存储引擎会自动的为我们创建聚簇索引

2. 非聚簇索引

   定义：非主键构成的索引，称为非聚簇索引，别称二级索引、辅助索引

   作用：解决以非主键字段作为搜索条件的查找问题，避免了需要遍历记录的费时操作

   特性：
   ① 非聚簇索引的叶子节点中，列上只有该非主键字段和主键字段两个值，

   ② 一个数据表只有一个聚簇索引，但可以有多个非聚簇索引

   回表：
   根据搜索条件选择对应的非聚簇索引进行查询，然后得到对应的记录主键，再根据主键值使用聚簇索引进行一次查询，从而最终获取到完整的记录，这个过程叫做回表。

   注意：
   非聚簇索引中，叶子节点上不存储完整的记录，就是为了避免数据大量冗余。

3. 联合索引

   定义：使用了两个或多个字段作为排序规则，本质上还是非聚簇索引。

   特性：先按照c1进行排序，c1相同的情况下按照c2进行排序，以此类推。

五、InnoDB下的B+树索引的注意事项

1. 根页面位置万年不动

   ① 创建表的时候，就会选择表的存储引擎，默认是InnoDB，故会为B+树（索引）创建一个根节点页面。

   ② 当向表中插入数据时，首先存储到根节点页面。

   ③ 当根节点页面中的记录数已满，就会将根节点中的所有记录复制到一个新的页中，随着数据页的增多，根节点页面就会升级为目录页 …

   所以一个B+数索引的根节点自诞生之日起，就不会再移动。

2. 内节点中目录项记录的唯一性

   内节点：就是指非叶子节点，即目录页。为了保证其中目录项的唯一性，目录页的各条记录中也会存储主键

六、MyISAM

1. 使用的索引仍然是类B+树模型

   

2. 与B+树索引的区别

   在叶子节点中，存储的是主键+记录的存储地址

3. 与InnoDB的区别

   InnoDB：

   数据与索引存放在.bd文件中，

   MyISAM:

   数据存放在.MYD文件中，索引存放在.MYI文件中