MySQL-索引

索引是存储引擎用于从海量数据中快速找到数据记录的一种数据结构，MySQL在进行数据查找时，首先查看查询条件是否命中某条索引，符合则通过索引查找相关数据，如果不符合则需要全表扫描。

索引是在存储引擎中实现的，因此每种存储引擎的索引实现不一定完全相同。

优点：索引可以减少磁盘I/O的次数(把页面从磁盘加载到内存中耗时长)，加快查询速率。

缺点：创建索引和维护索引需要耗费时间，并且索引需要占用磁盘空间，因为它存储在磁盘上。

1. InnoDB中的索引

1.1 设计索引

采用B+树的格式，只有最底层的叶子结点存储的是一条条数据，其余的页存储的都是每页数据的目录信息。一个页默认16KB

1.2 索引分类

索引按照物理实现方式分：

1.2.1 聚簇索引

InnoDB中使用B+树这种数据结构设计的索引它是一个整体，这个整体就是聚簇索引。因此聚簇索引不单是一个索引类型，而是一种数据存储方式，用户所有记录都存储在叶子节点（这里的存储是从操作系统底层来说的，而不是用户看到的存放在表中），所以我们可以说数据即索引，索引即数据。在InnoDB中索引存放在表名.ibd中。

具有这两个特点的B+树称为聚簇索引：

页内的记录是按照主键的大小顺序排序成一个单向链表，各个存放用户记录的页也是根据页中用户记录的主键大小顺序排序成一个双向链表，存放目录向的页分为不同的层次，在同一层次的页也是根据页中目录项记录的主键大小排成一个双向链表。
B+树的叶子节点存储的是完整的（每列信息都存储了）用户记录。

InnoDB存储引擎会自动为我们创建聚簇索引。每个MySQL表只能有一个聚簇索引

设计索引那里的图片就是聚簇索引

目录项记录中是主键+页号

优点：

访问速度更快
对基于主键的排序查找和范围查找速度很快
减少io操作

缺点：

插入速度严重依赖于插入顺序，对于InnoDB，我们一般都会定义一个自增的ID列为主键。如果没有定义主键，InnodDB会选择一个非空的唯一索引代替，如果没有这样的索引，InnoDB会隐式的定义一个主键来作为聚簇索引。
更新主键的代价很高，因为要依次往后更新。对于InnoDB，我们一般定义主键不可更新
二级索引访问需要两次索引查找

非聚簇索引（二级索引 / 辅助索引）

“聚簇”表示数据行和相邻的键值聚簇的存储在一起

1.2.2 非聚簇索引(二级索引)

聚簇索引是以主键作为搜索条件，非聚簇索引是指定其他列做为搜索条件

目录项记录中是指定列+主键号+页号

数据项记录中是指定列+主键号

比如表中有c1 c2 c3列，c1是主键，但是我们想要以c2列作为搜索条件，就只能创建一个非聚簇索引，但是此时的命令是select * 即需要显示出所有字段，就需要一个回表的操作，找到c2后回聚簇索引中找完整的表记录。

1.2.3 联合索引

也是非聚簇索引，就是可以由多个列联合作为搜索条件。

目录项记录中是联合的列+页号

1.3 B+树页面特点

B+树根节点自被创建起，就永远不会移动
内节点（非叶子节点）中目录项记录需要唯一，因此二级索引都引用主键索引，即data域中存放主键
一个页面最少存储2条记录

1.4 B+树形成过程

每当为某个表创建一个B+树索引时，都会为这个索引创建一个根节点页面，最开始表中没有数据的时候，每个B+树对应的根节点中既没有用户记录，也没有目录项记录。

随后向表中插入用户记录时，先把用户记录存储到这个根节点中。

当根节点的可用空间用完时（一般一个页面只能存取有限的空间），如果想要继续插入记录，此时会将根节点中的所有记录赋值一份到一个新分配的页，然后对这个新页进行页分裂的操作，得到另一个新页，此时新插入的记录根据键值（聚簇索引中是主键值，非聚簇索引中是索引列的值）的大小由低到高分配到新页中，而根节点就变为存储目录项记录的页。

1.5 B+树查找行记录最多只需1~3次磁盘I/O

原因：

InnoDB存储引擎中页的大小默认为16KB，一般表的主键类型为int占4个字节，指针类型一般也占4-8个字节，假设忽略其他列的类型大小，则一个也可以存放16KB/(8B+8B)=1000条用户记录，所以如果是一个三层的B+树可以存放1000×1000×1000条用户记录。但实际情况是每个页面不是全部都是用户记录，可能填不满或者是有其他内容，所以B+树高度一般就是2到4层，但是因为InnoDB存储引擎默认将根节点常驻内存，所以不需要进行页面置换，所以查找某一行记录需要进行磁盘I/O的次数最多为1到3次。

1.6 B+树比B树的好处

B+树优于B树的根本原因就是B+树只在叶子节点存储数据项记录，非叶子节点仅用于索引，而B树在非叶子节点既保存索引，也存储数据项记录，叶子节点只保存数据记录。

B+树的磁盘读写代价更低

B树的非叶子节点中也存储了部分用户数据记录，需要占用一定的页面空间，因此非叶子节点就存储不了太多目录项记录，因此到叶子节点那一层的指针就少，树就高了。而B+树将数据项记录都存放在叶子节点，这样相对来说，上面页面中可以容纳的目录项个数就多，树就低了，因此需要的磁盘I/O就少了。
B+树的查询效率更加稳定

B+树只需要查到最后一层的某些页面，然后逐个遍历(记录与记录之间是单向链表的形式，页面和页面之间是双向链表的形式)，而B树中非叶子节点中也可能存在数据项，最差的情况可能需要将整个B树的节点都遍历一遍。

1.7 Hash索引和B+树索引的区别

Memory存储引擎默认采用Hash索引，InnoDB存储引擎默认采用B+树索引。

Hash索引不能进行范围查找，只能精确查找，B+树可以。因为Hash索引指向的数据是无序的，而B+树的每个叶子节点中都是一个个有序的链表。
Hash索引不支持ORDER BY 排序，因为Hash索引指向的数据是无序的
Hash索引不支持联合索引的最左侧原则(联合索引的部分索引无法使用)，因为Hash索引在计算Hash值的时候是将索引键合并后再一起计算hash值，而不会针对每个索引都计算Hash值。

2.MyISAM中的索引

都是非聚簇索引，因为将索引和数据分开存储。索引的叶子节点存储的是主键值+数据记录地址

底层也是使用的B+树

3.索引的声明和使用

索引的分类：

按照功能逻辑上分有：
- 普通索引
  
  不附加任何约束条件，只创建索引，就是为了提高查询效率
- 唯一索引
  
  使用UNIQUE参数可以设置索引为唯一性索引，在创建唯一索引时限制该索引的值必须是唯一的，但允许有空值，一个表里可以有多个唯一索引。
- 主键索引
  
  特殊的唯一索引，在唯一索引的基础上增加了不为空的约束，即NOT NULL+UNIQUE，一个表中最多有一个主键索引
- 全文索引
  
  使用参数FULLTEXT设置索引为全文索引，查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度
按照物理实现方式分：
- 聚簇索引(其实就是主键索引)
- 非聚簇索引
按照作用字段个数分：
- 单列索引
  
  在表的单个字段上创建索引，单列索引只根据该字段进行索引，一个表可以有多个单列索引。单列所以可以是普通索引/唯一所以/全文索引
- 联合索引
  
  即多列索引，在表的多个字段组合上创建一个索引，该索引指向创建时对应的多个字段，可以使用这几个字段一起查询，查询中至少使用这些字段的第一个字段，索引才会生效。使用组合索引遵循最左前缀集合。
  
  先根据第一个字段进行查询，如果第一个字段相同，再根据第二个字段，如果第二个也相同，再根据第三个字段

4.索引的创建

4.1 隐式创建索引

在声明有主键约束，唯一性约束，外键约束的字段上会自动添加相关索引

将表中一字段设置为主键

mysql> create table tb_user5(
    -> tb_id int primary key,
    -> tb_name varchar(20)
    -> );

外键、唯一性约束

mysql> create table tb_user6(
    -> tb_id int primary key,
    -> tb_name varchar(20) unique,
    -> tb5_id int,
    -> constraint tb6_tb5_id foreign key(tb5_id) references tb_user5(tb_id)
    -> );
Query OK, 0 rows affected (0.03 sec)

4.2 显式创建索引

创建普通索引

mysql> create table tb_user7(
    -> tb_id int,
    -> tb_name varchar(20),
    -> index idx_tb_id(tb_id)
    -> );

创建唯一索引

mysql> create table tb_user8(
    -> tb_id int,
    -> tb_name varchar(20),
    -> unique index uk_idx_tb_id(tb_id)
    -> );

相对应字段也会有唯一性约束主键索引

主键索引

就是创建主键的时候会自动生成索引

联合索引

多个字段一起作为索引

1 2	`mysql> create table tb_user9( tb_id int, tb_name varchar(20),index mul_idx_tb_id(tb_id,tb_name) ); Query OK, 0 rows affected (0.03 sec)`

4.6 表已经创建成功后添加索引

alter table … add …

1
2
3

mysql> alter table tb_user7 add index idx_id(tb_id); //普通索引
mysql> alter table tb_user7 add unique index uk_idx_id(tb_id); //唯一索引
mysql> alter table tb_user7 add index mul_idx_id(tb_id,tb_name);//联合索引

create index …. on ….

1
2
3

mysql> create index idx_id on tb_user7(tb_id);
mysql> create unique index idx_id on tb_user7(tb_id);
mysql> create index mul_idx_id on tb_user7(tb_id,tb_name);

5.删除索引

方式一：

1	`alter table 表名 drop index 索引名;`

添加auto_increment约束字段的唯一索引不能被删除

方式二：

1	`drop index 索引名 on 表名;`

6.MySQL8.0新特性【降序索引】

mysql> create table tb_user8(
    -> a int,
    -> b int,
    -> index idx_a_b(a asc,b desc)
    -> );

Collation那一列中A表示升序，D表示降序

7.MySQL8.0新特性【隐藏索引】

如果将一个索引删除需要消耗系统过多的资源的时候，就不适合将这个索引删除，因此有隐藏索引，先将待删除的索引设置为隐藏索引后，这个索引就不起作用了，看看系统是否受到很大的影响，没有太大影响就可以将它删除。

创建表时，添加隐藏索引

mysql> create table tb_user7(
    -> tb_id int,
    -> tb_name varchar(20),
    -> index idx_tb_id(tb_id) invisiable //不可见
    -> );

表已经创建，添加隐藏索引

1	`mysql> alter table tb_user7 add index idx_id(tb_id) invisiable;`

修改索引的可见性

1 2	`//将索引修改为不可见 mysql> alter table tb_user7 alter index idx_id(tb_id) invisiable;`

当索引被隐藏时，它的内容仍然和正常索引一样实时更新，只是不能用

8.适合创建索引的情况

字段的数值有唯一性的限制
频繁作为where查询条件的字段
经常group by和order by的列 (本来就需要排序或者分组)

如果group by和order by 都有，创建一个联合索引，先写group by
update、delete的where条件列的查询的字段
DISTINCT字段需要创建索引

DISTINCT的作用是对字段进行去重，如果创建了索引，相同的列都是在一起的，去重就更快
多表JOIN连接时创建索引
使用列的类型更小的字段创建索引

一个页能容纳更多的记录
使用字符串前缀创建索引

如果一个字符串要设置索引，由于字符串很长，就可以截取字段前面一部分内容创建索引，这就叫前缀索引，没有必要对全字创建索引

create table shop(address varchar(120) not null);

alter table shop add index(address(12));
区分度高的列适合作为索引
使用最频繁的列适合放到联合索引的左侧
在多个字段都要创建索引时，联合索引优于单列索引

9. 不适合创建索引的情况

数据量小的表最好不要使用索引
在where中使用不到的字段不要设置索引
有大量重复数据的列不要建立索引

需要多次访问很多页
不要对经常更新的表创建过多索引
不建议用无序的值作为索引
最好删除不再使用或者很少使用的索引
不要定义冗余或者重复的索引

索引是一把双刃剑，可以提高查询效率，但也会降低插入和更新的速度并占用磁盘空间。

MySQL

#Linux #数据库高级

MySQL-索引

https://vickkkyz.fun/2022/03/25/计算机/mysql/索引/

作者

Vickkkyz

发布于

2022年3月25日

许可协议

MySQL-范式上一篇

每天一个小技巧~ 下一篇