5.MySQL索引

小新1号
132 阅读14分钟

一、索引分类

1.数据结构角度

  • B+树索引
  • Hash索引
  • Full-Text全文索引
  • R-Tree索引

2.物理存储角度

  • 聚集索引：聚集索引就是按照每张表主键构造一棵B+树，叶子节点存放的是整张表行记录数据（每张表只能有一个聚集索引）

image.png

  • 非聚集索引：索引和数据分开维护，叶子节点没有包含完整数据行记录（存储的是聚集索引id即主键ID或数据磁盘地址）

image.png 3.逻辑角度

  • 主键索引：主键索引是一种特殊的唯一索引，不允许有空
  • 普通索引（单列索引）：每个索引只包含单个列，一个表可以有多个单列索引
  • 多列索引（复合索引、联合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引的第一个字段，索引才会被使用。复合索引遵循最左前缀规则
  • 唯一索引或者非唯一索引

二、MySQL索引结构

首先索引是在存储引擎层面实现的，而不是server层。不是所有的存储引擎都支持所有的索引类型。

2.1 B+Tree索引

MyISAM和InnoDB存储引擎，都使用B+Tree的数据结构，它相对于B-Tree结构，所有数据存存放在叶子结点。且把叶子节点通过指针连接到一起，形成了一条数据链表，以加快相邻数据的检索效率。

2.1.1 二叉搜索树

二叉搜索树特点：

  • 所有非叶子节点至多拥有两个子节点
  • 所有节点存储一个关键字
  • 非叶子节点左指针指向小于其关键字的子树，右指针指向大于其关键字的子树

以下都是二叉搜索树： image.png

如果要找到65，左边的二叉树需要扫描3层（3次IO），而右边需要6层

2.1.1 B-Tree（B树）

概念：B-Tree即B树，B即Balanced平衡的意思，B树是一种多路搜索树。

B树是为磁盘等外存储设备设计的一种平衡查找树。系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。

InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每页大小为16KB，可通过参数innodb_page_size将页大小设置为4k\8k\16k，在MySQL中可通过如下命令查看页大小：show variables like 'innodb_page_size';

而系统一个磁盘块的存储空间往往没有这么大，因为InnoDB每次申请磁盘空间时都会是连续读取若干连续磁盘块达到页大小。InnoDB在把磁盘数据读入到磁盘时会以页为基本单位，查询数据时如果一个页中每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

B树结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述B-Tree，首先定义一条记录为一个二元组[key, data]，key为记录的键值，对应表中的主键值，data为一行记录中除外键外的数据。

一棵m阶的B树满足下列条件：

  1. 树中每个节点至多有m个孩子
  2. 除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子
  3. 若根节点不是叶子结点，则至少有2个孩子
  4. 所有叶子节点都在同一层，且不包含其它关键字信息
  5. 每个非终端节点包含n个关键字信息（PO,P1,...Pn）
  6. 关键字的个数n满足：ceil(m/2)-1<= n <= m-1
  7. ki(i=1,...n)为关键字，且关键字升序排序
  8. Pi(i=1,...n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)

B-Tree中的每个节点根据实际情况可以包含大量关键字信息和分支。

image.png

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17-35，P3指针指向的子树数据范围为大于35

模拟查找关键字29的过程：

  1. 根据根节点找到磁盘块1，读入内存[磁盘I/O操作第一次]
  2. 比较关键字29在区间（17,35），找到磁盘块1的指针P2.
  3. 根据P2指针找到磁盘块3，读入内存。[磁盘I/O操作第二次]
  4. 比较关键字29在区间（26,30），找到磁盘块3的指针P2
  5. 根据P2指针找到磁盘块8，读入内存[磁盘I/O操作第三次]
  6. 在磁盘块8中的关键字列表中找到关键字29

2.1.2 B+Tree(B+树)

概念：B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构

从上节B-Tree结构图中可看到每个节点中不仅包含数据的key值，还有data值。而每个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储key的数量很小，当存储数据量很大时同样会导致B-Tree深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值消息，这样可以大大加大每个节点存储的key数量降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：

  1. 非叶子节点只存储键值信息
  2. 所有叶子节点间都有一个链指针
  3. 数据记录都存放在叶子节点中

B+树相对于B树的优点：

  1. B+树磁盘读写代价更低
  2. B+树数据存储在叶子几点，将导致B+树层高小于B树的层高，也就是说B+树平均IO次数会小于B树
  3. B+树查询效率更加稳定
  4. B+树更擅长范围查询
  5. B+树占用内存空间小

将上节中B-Tree优化，由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示： image.png

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

B+Tree性质：

  1. 通过以上分析，我们可以知道IO次数取决于B+树的高度h,假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=log(m+1)N，当数据量N一定情况下，m越大，h越小；而m = 磁盘块的大小/数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的。 如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，及索引字段要尽量小，比如int占4字节，要比bigint 8字节少一半，这也是为什么B+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。
  2. 当B+树的数据项是复合的数据结构，比如（name,age,sex）时，b+树是按照从左到右的顺序来建立搜索树的。比如当（张三，20，F）这样的数据来检索的时候，B+树会优先比较name来确定下一步的搜索方向。如果name相同再一次比较age和sex，最后得到检索的数据；但当（20，F）这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点了。因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当（张三，F）这样的数据来检索时，B+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了，这个是非常重要的性质，即索引的最左匹配特性。

MyISAM主键索引和辅助索引结构

概念：MyISAM引擎的索引文件和数据文件是分离的。MyISAM引擎索引结构的叶子节点的数据域，存放的并不是实际的数据记录，而是数据记录的地址。

非聚簇索引：索引文件和数据文件分离，这样的索引称为非聚簇索引

image.png

MyISAM中，索引（含叶子节点）存放在单独的.myi文件中，叶子节点存放的是数据的物理地址偏移量（通过偏移量访问就是随机访问，速度很快）。

索引流程：

  1. 先从索引文件中查找到索引节点，从中拿到数据的文件指针
  2. 再到数据文件中通过文件指针定位具体数据。

InnoDB主键索引与辅助索引结构

概念：InnoDB引擎索引结构的叶子节点的数据域，存放的就是实际的数据记录（对于主索引，此处会存放表中所有的数据记录；对于辅助索引此处会引用主键，检索的时候通过主键到主键索引中找到对应数据行），或者说，InnoDB数据文件本身就是主键索引文件，这样的索引称为聚簇索引，一个表只能有一个聚簇索引。

主键索引： InnoDB索引是聚集索引，它的索引和数据是存瑞同一个.idb文件中的额，因此他的索引结构是在同一个树节点中同时存放索引和数据，如下图中最底层的叶子节点有三行数据，对应于数据表中的id、stu_id、name数据项。

image.png

InnoDB中，索引分叶子节点和非叶子节点，非叶子节点就像新华字典的目录，单独存放在索引段中，叶子节点则是顺序排列的，在数据段中。InnoDB的数据文件可以按照表来切分（开启innodb_file_per_table），切分后存放在xxx.ibd中

辅助索引： 示例以学生表中name列建立辅助索引，它的索引结构跟主键索引结构有深大差别，在最底层的叶子节点有两行数据，第一行字符串是辅助索引，按照ASCLL码进行排序，第二行整数是主键的值。

image.png 索引流程：

  1. 在辅助索引上检索name，到达其叶子节点获取对应的主键
  2. 使用主键在主索引上进行对应检索操作

这个过程就是所谓的回表查询

InnoDB索引结构注意事项:

  1. 数据文件本身就是索引文件
  2. 表数据文件本身就是按B+Tree组织的一个索引结构文件
  3. 聚集索引中叶节点包含了完整的数据记录
  4. InnoDB表必须要有主键，并且推荐使用整形自增主键

2.1.3 Hash索引

概念：哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只要一次哈希算法即可立刻定位到相应位置，速度非常快。Memory存储引擎使用Hash索引

image.png Hash索引仅仅能满足=、In<=>查询，不能使用范围查询

2.1.4 full-text全文索引

  • 全文索引也是MyISAM的一种特殊索引类型，主要用于全文索引，InnoDB从MYSQL5.6版本提供对全文索引的支持。

  • 它用于替代效率较低的LIKE模糊匹配操作，而且可以通过多字段组合的全文索引一次性全模糊匹配多个字段。

  • 同样使用B-Tree存放索引数据，但使用的是特定的算法，将字段数据分割后再进行索引（一般每4个字节一次分割），索引文件存储的是分割前的索引字符串集合，与分割后的索引信息，对应Btree结构的节点存储的是分割后的词信息以及它在分割前的索引字符串集合中的位置。

三、索引使用

3.1 索引使用场景

哪些情况需要创建索引？

  1. 主键自动建立唯一索引
  2. 频繁作为查询条件的字段
  3. 查询中与其他表关联的字段，外键关系建立索引
  4. 单键/组合索引选择问题，高并发下倾向创建组合索引
  5. 查询中排序的字段，排序字段通过索引访问大幅抬高排序速度
  6. 查询中统计或分组字段

哪些情况不要创建索引？

  1. 表记录太少
  2. 经常增删改的表
  3. 数据重复且分布均匀的表字段（包含太多重复数据字段，建索引没有太大意义）
  4. 频繁更新的字段不适合创建索引
  5. where条件用不到的字段不适合创建做引

3.2 最左匹配原则

3.2.1 定义

定义：最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询（>,<,between，like）就会停止匹配

例如:

where s_code=2的查询如果建立（sname,s_code）顺序的索引

  1. 匹配不到（sname,s_code）的索引的。
  2. sname = “派大星” and s_code = 2或者（s_code = 1 AND sname = “派大星”）就能匹配到，因为优化器会自动调整sname,s_code顺序
  3. sname=“派大星” and s_code > 1 and address = “上海”，address是不走索引的。因为s_code是范围查询，它之后的字段会停止匹配

索引失效场景：juejin.cn/post/716196…

3.2.2 原理

前提，如果创建b,c,d联合索引

  1. 创建索引树时，MySQL会先对联合索引最左边第一个字段b进行排序
  2. 在对b排序好的基础上，对c进行排序
  3. 在对c排序好的基础上，对d进行排序

最左匹配原则最重要的就是第一个字段。

3.3 回表查询

概念：先通过非聚集索引树定位到叶子节点中存储的聚集索引的值，再通过获取到的聚集索引值查询聚集索银树定位到对应行记录，这个过程需要扫描两棵索引B+树，性能较低

3.4 索引覆盖

3.4.1 定义

概念：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。换句话说就是查询列要被所建的索引覆盖。

3.4.2 示例分析

*create table user (*

*id int primary key,*

*name varchar(20),*

*sex varchar(5),*

*index(name)*

*)engine=innodb;*

第一个SQL： image.png

该SQL能够命中name索引，索引叶子节点存储了主键ID，通过name的索引树即可获取id和name，无需回表，符合索引覆盖效率较高。

第二个SQL: image.png

能够命中name索引，索引叶子节点存储了主键ID，但sex字段必须回表查询才能获取到，不符合索引覆盖，需要再次通过id值扫描聚集索引获取sex字段，效率会降低。

如果把单列索引(name)升级为联合索引(name,sex)就不同了

*create table user (*

*id int primary key,*

*name varchar(20),*

*sex varchar(5),*

*index(name, sex)*

*)engine=innodb;*

image.png 这样修改索引，两个SQL都能命中索引覆盖，无需回表。

3.4.3 优化场景

  1. 全表count查询优化

user表（PK id, name, sex）

image.png 执行查询select count(name) from user,全表扫描不能利用索引覆盖。如果在name字段添加索引，就能够利用索引覆盖提高查询效率

  1. 列表查询回表优化

select id, name, sex from user where name = 'allen'

这条SQL如果只在name字段建立索引的话也会触发回表查询。我们可以将单列索引(name)升级为联合索引(name, sex)可以避免回表

四、索引失效

image.png

参考：

avatar
文章
阅读
粉丝