MySQL索引

予风复笙歌*

已于 2024-05-24 11:02:40 修改

阅读量1k

点赞数 25

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

于 2024-05-24 10:59:39 首次发布

本文链接：https://blog.csdn.net/weixin_52620731/article/details/139168831

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

索引

是一种基于快速查询和检索数据的数据结构，本身可以看成一种排序好的数据结构，可以理解为目录，索引就是数据的目录

优点：

使用索引可以加快数据的检索速度
创建唯一索引，可以保证数据库表汇总每一行数据的唯一性

缺点：

创建索引和维护索引需要耗费许多时间，增删改数据的时候，如果数据有索引，索引也需要进行动态修改，会降低SQL执行效率
索引需要使用文件存储，也会耗费一定的空间

//创建索引
create index user_index on user(id);

//添加索引
alter table user add index user_index(id);

//创建唯一索引
create unique index user_index on user(id);

//删除索引
alter table user drop index user_index;

1. 分类

数据结构：B+ Tree索引、Hash索引、Full-text索引

物理存储：聚簇索引(索引结构和数据一起存放)、非聚簇索引(索引和数据分开存放)

字段特性：主键索引、唯一索引、普通索引、前缀索引

字段个数：单列索引、联合索引

在这里插入图片描述

1. 按字段特性

主键索引：加速查询 + 列值唯一（不可以有 NULL）+ 表中只有一个

建立在主键字段上的索引，一张表最多只有一个主键索引，且不允许有空值
```
CREATE TABLE table_name  (
  ....
  PRIMARY KEY (index_column_1) USING BTREE
);
```

唯一索引：加速查询 + 列值唯一（可以有 NULL）

建立在UNIQUE字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但允许有空值

//创建表时，创建唯一索引
CREATE TABLE table_name  (
  ....
  UNIQUE KEY(index_column_1,index_column_2,...) 
);

//建表后，创建唯一索引
CREATE UNIQUE INDEX index_name
ON table_name(index_column_1,index_column_2,...);

普通索引：仅加速查询。

建立在普通字段上的索引，不要求字段为主键，也不要求字段为UNIQUE

//建表时，创建普通索引
CREATE UNIQUE INDEX index_name
ON table_name(index_column_1,index_column_2,...); 

//建表后，创建普通索引
CREATE INDEX index_name
ON table_name(index_column_1,index_column_2,...);

前缀索引：

定义：对字符类型字段的前几个字符创建的索引。前缀索引可以建立在字段类型为char、varchar、binary、varbinary。

索引的选择性 = 不重复的索引值 / 数据表的记录总数

索引的选择性越高，查询的效率越高，当索引的选择性接近于0.031时，基本上可以被使用。

不能进行order by和group by，也无法使用前缀索引做覆盖扫描。

2. 按字段的个数

单列索引
联合索引

使用表中多个字段创建索引

最左匹配原则：

使用联合索引时，根据索引中的字段顺序，从左到右依次匹配查询条件中的字段。如果查询条件与索引中的最左侧字段相匹配，那么 MySQL 就会使用索引来过滤数据，这样可以提高查询效率。

最左匹配原则会一直向右匹配，直到遇到范围查询（如 >、<）为止。对于 >=、<=、BETWEEN 以及前缀匹配 LIKE 的范围查询，不会停止匹配。

在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据

3. 按物理存储分类

聚簇索引:的叶子节点存放的是实际数据，每一个叶子节点都包含了主键值、事务ID、用于事务和MVCC的回滚指针以及所有的剩余列。

二级索引:叶子节点存储的数据是主键的值，不是实际数据，也就是说，通过二级索引可以定位主键的位置，二级索引又称为辅助索引/非主键索引。

如果某个查询语句使用了二级索引，但是查询的数据不是主键值，这时在二级索引找到主键值后，需要去聚簇索引中获得数据行，这个过程就叫作回表，也就是说要查两个 B+ 树才能查到数据。不过，当查询的数据是主键值时，因为只在二级索引就能查询到，不用再去聚簇索引查，这个过程就叫作索引覆盖，也就是只需要查一个 B+ 树就能找到数据。

因为表的数据都是存放在聚簇索引的叶子节点里，所以 InnoDB 存储引擎一定会为表创建一个聚簇索引，且由于数据在物理上只会保存一份，所以聚簇索引只能有一个。由于一张表只能有一个聚簇索引，所以为实现非主键字段的快速搜索，使用二级索引，二级索引叶子节点存放主键值，不是实际数据。

聚簇索引和非聚簇索引的区别：

优点：
- 查询速度快：定位到节点即可得到数据
- 对排序查找和范围查找优化
缺点：
- 依赖于有序的数据
- 更新代价大

4.按照数据结构分类

哈希索引

基于哈希表实现，只有精确匹配索引的所有列的查询才有效。只有Memory引擎支持哈希索引

存储过程：

存储引擎会对所有索引列计算一个哈希码
哈希索引将所有的哈希码存储在索引中
在哈希表中保存指向每个数据行的指针

特点：

哈希索引只包含哈希值和行指针，而不存储字段值。
哈希索引数据不按照索引值顺序存储，无法用于排序。
不支持部分索引列匹配查找
只支持等值比较查询
访问哈希索引数据很快，出现哈希冲突时，存储引擎遍历链表中所有的行指针
哈希冲突较多时，索引维护的代价会很高，需要遍历对应哈希值上的每一行，找到并删除对应行的引用，冲突越多，代价越大。

补充：

可以创建一个伪哈希索引，可以在查询where字句中手动指定使用的哈希函数。

eg: SELECT id FROM url WHERE url=“http://www.mysql.com” AND url_crc=CRC32(“http://www.mysql.com”)

查询时，MySQL会使用url_crc列的索引来完成查找。

不要使用SHA1()和MD5作为哈希函数，因为他们计算出来的哈希值是比较长的字符串，会浪费大量的空间。

CRC32()当数据表非常大时，会出现大量的哈希冲突。此时可以在where条件中代入哈希值和对应列值

B+树索引

2. 底层数据结构

1. Hash表

是键值对的集合，通过键即可快速取出对应的值。

散列表、哈希冲突(链式解决)

Hash索引不支持顺序和范围查询，对表中的数据进行排序或者范围查询

2. 二叉查找树(BST)

基于二叉树的数据结构，具有以下特点：

左子树 < 根节点 < 右节点
左右子树也分别为二叉查找树

但二叉树不平衡时，会退化成线性链表，时间复杂度由O(logn)变为O(n)

二叉查找树的性能非常依赖于平衡程度，导致不适合作为MySQL底层索引的数据结构。

3. 自平衡二叉查找树(AVL)

保证任何节点的左右子树高度之差不超过1，也可以称为高度平衡二叉树，查找、插入和删除时间复杂度都是O(logn)

通过旋转操作来保持平衡，包括了四种：LL、LR、RR、RL

但是AVL树需要旋转操作来保持平衡，并且在使用AVL树时，每个节点仅存储一个数据，需要查询的数据分布在多个节点上时，需要进行多次磁盘IO，但会很耗时。

4. 红黑树

也是一种自平衡的二叉查找树，通过在插入和删除节点时进行颜色变换和旋转操作，使得树始终保持平衡状态，特点：

左根右
根叶黑
不红红
黑路同

大致平衡，导致查询效率稍有下降。

5. B树&B+树

B树也称为B-树，全称为多路平衡查找树。
- B树索引查询的类型
  - 全值匹配：和索引中的所有列进行匹配
  - 匹配最左前缀：只使用索引的第一列
  - 匹配列前缀：只匹配某一列的值的开头部分
  - 匹配范围值
  - 精确匹配某一列并且范围匹配另外一列
  - 只访问索引的查询
B+ Tree是多叉树，叶子节点存放数据，非叶子节点只存放索引，每个节点里面的数据是按主键顺序存放的。

父节点的索引值都会出现在下层子节点的索引值中
特点
- 只有叶节点存放数据，非叶子节点存放目录项作为索引
- 非叶节点通过分层降低每一层的搜索量
- 所有节点按照索引键大小排序，构成双向链表，便于范围查询
叶节点会包含所有索引值信息，每一个叶子节点都有两个指针，分别指向上一个叶子节点和下一个叶子节点，形成双向链表。
- 根据主键索引查询数据
- 根据二级索引查询数据
  
  二级索引的叶子节点存放主键值，根据查询到的主键值再去查询实际数据回表。

B树和B+树的性能区别：

单点查询

进行单个索引查询时，平均时间会比B+树更快一点

B树所有节点即存放索引+记录，B+树只有叶子节点存放索引+记录，其他内节点只存放索引，而有些情况下用户记录的数据超过了索引数据，就意味着要花费更多的磁盘I/O操作次数来读取数据，导致效率降低。

在数据量相同的情况下，B+树的非叶子节点可以存放更多的索引，因此B+树比B树更矮胖，查询底层节点的磁盘I/O次数会更少。
插入和删除效率

由于B+树有冗余的节点，所以在删除节点的时候，不会发生复杂的变形，但是B树可能会涉及复杂的树的变形。
范围查询

B+树所有叶子节点之间进行链表连接，所以在找到一个时可以对链表进行遍历，节省查询时间

B树叶子节点之间是独立的，只能通过树的遍历来完成范围查询，先找到查找的下限，在对B树进行中序遍历，直到找到查找的上限。

补充：为什么使用B+树作为索引

数据持久化：数据(索引 + 记录)保存到磁盘上

可能存在的问题：

数据库的索引也是保存在磁盘上，当索引查找某行数据时，会先从磁盘中读取索引，通过索引找到某行数据，再读入内存，会发生多次磁盘的I/O。

支持范围查询

要满足的条件：

尽可能少的磁盘的I/O操作完成查询
可以高效的查询某一记录，也可以进行范围查询

为便利查找提出：二叉查找树，但由于会退回单链表，所以提出平衡二叉查找树。

二叉查找树：左子树 < 根节点 < 右子树

平衡二叉查找树：每个节点的左右子树的高度差不会超过1

但是随着插入元素的增多，树的高度会变大，但树的高度会导致磁盘I/O操作次数增多，影响整体数据查询效率。 每遍历一层树，会把当前树的节点读入内存。

提出B树，树的分叉由二叉变为多叉。

3. 索引优化

索引区分度：建立联合索引的时候，将区分度大的字段排在前面，区分度大的字段越有可能会被更多的SQL使用到。
索引下推：在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足的条件的记录，减少回表的次数。
前缀索引优化：某个字段中字符串的前几个字符建立索引
覆盖索引优化：可以直接从二级索引中查询到的记录
主键索引自增：插入一条新数据，是追加操作，不需要重新移动数据；非自增主键，由于插入主键的索引值都是随机的，可能需要移动其他数据来满足新数据的插入，会导致页分裂，造成大量内部碎片，导致索引结构不紧凑，从而影响查询效率。

主键字段长度尽可能小，可以保证二级索引的叶子节点越小，占用空间越小。
索引最少设置为NOT NULL：索引存在NULL值导致优化器做索引选择更复杂，难以优化；NULL虽然没有意义，但是会占用物理空间。

4. 何时建立索引

缺点：

占用物理空间、数量大，占用空间越大
创建索引和维护索引耗费时间，时间随数据量增加而增大
降低表的增删改效率，增删改索引，B+树为维护索引有序性，需要进行动态维护。

什么时候适合索引

字段有唯一性限制
经常用于where查询条件的字段
经常用于group by 和orded by的字段

什么时候不需要创建索引

字段中存在大量重复数据，不需要创建索引
表数据太少
经常更新的字段不需要创建索引

5. 索引数据结构

索引的数据结构与页几乎一样，大小也是16k。

索引页中记录的是页(数据页，索引页)的最小主键id和页号，且在索引页中增加了层级信息。

在这里插入图片描述

索引页(非叶子节点)：

内容包括了id和页号地址两部分：

id：对应页中记录的最小记录的id值
页号：地址是指向对应页的指针

在这里插入图片描述

MySQL单表不能超过2000W行：

非叶子节点内指向其他页的数量为x

叶子节点内能容纳的数据行数为y

B+树的层次为z

total = x^(z-1)*y

计算x:

整个页的大小是 16K, 剩下 15k 用于存数据，在索引页中主要记录的是主键与页号，主键假设是 Bigint (8 byte), 而页号也是固定的（4Byte）, 那么索引页中的一条数据也就是 12byte。所以 x=15*1024/12≈1280 行。

计算y:

叶子节点和非叶子节点的结构是一样的，能放数据的空间也是 15k。暂时按一条行数据 1k 来算，那一页就能存下 15 条，Y = 15*1024/1000 ≈15。
根据上述的公式，Total =x^(z-1) *y，已知 x=1280，y=15：

假设 B+ 树是两层，那就是 z = 2， Total = （1280 ^1 ）*15 = 19200
假设 B+ 树是三层，那就是 z = 3， Total = （1280 ^2） *15 = 24576000 （约 2.45kw）

在保持相同的层级下，行数据大小不同的情况下，也是不同的。2000W是一个推荐值，超过这个值则会导致B+树层级更高，影响查询性能。

6. 页的数据结构

InnoDB的数据按照数据页为单位读写，读取某一页的时候是将整个记录所在页从磁盘中读取出来。数据页的大小默认为16KB。

数据页中的记录按照主键顺序组成单向链表，为了方便遍历，存在一个页目录，用于记录索引。

页目录创建过程：

将记录划分为几组，包括了最小记录和最大记录
每组记录的最后一条记录是组内最大的记录，且会保存当前组内的记录的数量
页目录存储每组记录的最后一条记录的偏移地址，每组的偏移量被称为槽，每个槽相当于指针指向了不同组的最后一个记录
页目录由多个槽组成，通过槽查找记录时，使用二分法来查询记录的分组

如何查找组内的最小值:通过查找上一组的最大值，再通过最大值来遍历目标值

在页的7个组成部分中，存储的记录会按照指定的行格式存储到User Records部分。User Records开始并没有，会从Free Space中申请一个记录大小空间划分到User Record部分。当所有的Free Space的控件被使用完时，意味着当前页使用完。

在这里插入图片描述

7. 索引失效

对索引使用左或者左右模糊匹配

like %xx 或者 like %xx%都会导致索引失效

索引B+树是按照索引值有序排列，只能根据前缀进行比较，当出现左匹配时，无法进行索引值的比较。
对索引使用函数

索引中保存的是索引字段的原始值，不是经过函数计算后的值，所以无法走索引查询

但是MySQL8.0开始，索引特性增加了函数索引，可以针对函数计算后的值建立一个索引，即索引值是函数计算后的值。
对索引进行表达式的计算

索引保存的是索引字段的原始值，不是表达式计算后的值
对索引隐式类型转换

MySQL在遇到字符串和数字进行比较的时候，会自动把字符串转换为数字，然后再进行比较。
- 索引字段是整型类型，查询条件输入的参数是字符串，不会导致索引失效
- 索引字段是字符串，查询条件输入的参数是整型，会导致索引失效。使用了函数进行转换
联合索引非最左匹配

组合索引：多个普通字段组合在一起创建的索引

举个例子：

创建了一个索引(a,b,c)，如果查询语句为：

where a = 1;

where a = 1 and b = 2 and c = 3;

where a = 1 and b = 2;

由于具有查询优化器，所以a字段在where字句的顺序并没有要求。

但查询条件若为：

where b = 2;

where c = 3;

where b = 2 and c = 3;

则无法匹配联合索引，联合索引会失效

查询条件若为：where a = 1 and c = 3会出现索引截断

索引下推：在存储引擎层进行索引遍历过程中，对索引包含的字段先做判断，过滤掉不满足条件的记录，再返回给Server层，减少回表次数。

联合索引中，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序。
where字句中的or

在where字句中，如果or前的条件列是索引列，or后面的条件列不是索引列，则会导致索引失效。

8. cout(*) 和count(1)的区别

哪种的count性能最好：

在这里插入图片描述
count()：

是一个聚合函数，函数的参数不仅可以是字段名，也可以是其他任意表达式，作用是统计符合查询条件的记录中，函数指定的参数不为NULL的记录有多少个

count(1):

统计表中有多少个记录，由于count函数的参数是1，不是字段，所以不需要读取记录中的字段值。

有二级索引的时候不遍历聚簇索引

count(主键字段)执行过程：

通过count函数统计有多少个记录时，MySQL的server层会维护一个名为count的变量。

server层会循环向InnoDB读取一条记录，count函数指定的参数不为NULL，则会将变量count+1，直到符合查询的所有记录被读完，则退出循环，最后将count变量的值发送给客户端。

当表中只有主键索引的时候，InnoDB循环遍历聚簇索引，将读取到的记录返回给server层，之后读取记录中的主键值，如果主键值不为NULL，则count变量+1。

表中有二级索引的时候，InnoDB循环遍历二级索引，因为二级索引记录比聚簇索引记录占更少的存储空间，遍历成本会比聚簇索引低。

count(*):

相当于count(0)，当使用count(*)时，会默认转换为count(0)，性能和count(1)操作基本没什么差距

count(字段)：

执行效率最差，由于字段不是索引，所以会采用全表扫描的方式来计数。

补充：

执行count(1)、count(*)、count(主键字段)时，尽可能建立二级索引，优化器会自动采用key_len最小的二级索引进行扫描，效率更高。
为什么通过遍历计数：

由于InnoDB存储引擎支持事务，所以同一时刻的多个查询，由于多版本并发控制，返回多少行的数据是不确定的，不能维护一个变量。
优化count(*):
1. 近似值
  
  使用show table status 或者 explain命令来进行估算
2. 额外表保存计数值
  
  可以将表的计数值保存到单独的一张计数表中，在修改表中记录数时，额外维护这个记录表。