MySQL索引详解 - 指南

索引

​ MySQL的索引是⼀种数据结构，它可以帮助数据库⾼效地查询、更新数据表中的数据。索引通过 ⼀定的规则排列数据表中的记录，使得对表的查询可以通过对索引的搜索来加快速度。 （不同的数据结构都有自己实现的规则，不同的规则导致不同的数据结构的效率不同，最终时间复杂度和空间复杂度不同）

​ MySQL 索引类似于书籍的⽬录，通过指向数据⾏的位置，可以快速定位和访问表中的数据，⽐如 汉语字典的⽬录（索引）⻚，我们可以按笔画、偏旁部⾸、拼⾳等排序的⽬录（索引）快速查找到需要的字。

为什么要使用索引？

​ 使⽤索引的⽬的只有⼀个，就是提升数据检索的效率，在应⽤程序的运⾏过程中，查 询操作的频率远远⾼于增删改的频率。

索引应该选择哪种数据结构

HASH

时间复杂度：O(1)，查询的速度非常快，但MySQL没有选择HASH做诶索引的默认数据结构，主要原因是HASH不支持范围查找

二叉搜索树

⼆叉搜索树的中序遍历是⼀个有序数组，支持范围查找，但有⼏个问题导致它不适合⽤作索引的数据结构

  1. 最坏情况下时间复杂度为O(N)
  2. 节点个数过多⽆法保证树⾼

AVL和红⿊树，虽然是平衡或者近似平衡，但是毕竟是⼆叉结构在检索数据时，每次访问某个节点的⼦节点时都会发⽣⼀次磁盘IO，⽽在整个数据库系统中，IO是 性能的瓶颈，减少IO次数可以有效的提升性能

N叉树

为了解决树⾼的问题，可以使⽤N叉树

在这里插入图片描述

在数据量相同的情况下，N叉树的曙光号可以得到有效的控制，也就是意味着在相同的数据量的情况下可以减少IO的次数，从而提升效率。

B+树

B+树是⼀种经常⽤于数据库和⽂件系统等场合的平衡查找树，MySQL索引采⽤的数据结构，以4阶 B+树为例

在这里插入图片描述

B+树的特点：

  • 能够保持数据稳定有序，插⼊与修改有较稳定的时间复杂度

  • ⾮叶⼦节点仅具有索引作⽤，不存储数据，所有叶子节点保真实数据

  • 所有叶⼦节点构成⼀个有序链表，可以按照key排序的次序依次遍历全部数据

B+树与B树的对比：

  • 叶⼦节点中的数据是连续的，且相互链接，便于区间查找和搜索。

    在MySQL中在组织叶子结点的时候使用的是双向列表

  • ⾮叶⼦节点的值都包含在叶⼦节点中

    MySQL的非叶子节点只保存了对子节点的引用，而所有保存真实的数据都保存在叶子节点中

  • 对于B+树⽽⾔，在相同树⾼的情况下，查找任⼀元素的时间复杂度都⼀样，性能均衡。

MySQL中的页

为什么要使用页

在 .ibd ⽂件中最重要的结构体就是Page(⻚)，⻚是内存与磁盘交互的最⼩单元，默认⼤⼩为 16KB，每次内存与磁盘的交互⾄少读取⼀⻚，所以在磁盘中每个⻚内部的地址都是连续的，之所以这样做，是因为在使⽤数据的过程中，根据局部性原理，将来要使⽤的数据⼤概率与当前访问的 数据在空间上是临近的，所以⼀次从磁盘中读取⼀⻚的数据放⼊内存中，当下次查询的数据还在这 个⻚中时就可以从内存中直接读取，从⽽减少磁盘I/O提⾼性能

局部性原理：

是指程序在执⾏时呈现出局部性规律，在⼀段时间内，整个程序的执⾏仅限于程序中的某⼀部 分。相应地，执⾏所访问的存储空间也局限于某个内存区域，局部性通常有两种形式：时间局部 性和空间局部性。

时间局部性（Temporal Locality）：如果⼀个信息项正在被访问，那么在近期它很可能还会被再 次访问。

空间局部性（Spatial Locality）：将来要⽤到的信息⼤概率与正在使⽤的信息在空间地址上是临 近的。

tips：在 InnoDB 存储引擎中，.ibd文件是独立表空间文件的核心载体

每⼀个⻚中即使没有数据也会使⽤ 16KB 的存储空间，同时与索引的B+树中的节点对应，查看⻚的⼤⼩，可以通过系统变量 innodb_page_size 查看

在MySQL中有多种不同类型的⻚，最常⽤的就是⽤来存储数据和索引的"索引⻚"，也叫做"数据 ⻚"，但不论哪种类型的⻚都会包含⻚头(File Header)和⻚尾(File Trailer)，⻚的主体信息使⽤数 据"⾏"进⾏填充，数据⻚的基本结构如下图所⽰：

在这里插入图片描述

页文件头和页文件尾

在这里插入图片描述

页头和页尾包含了当前页文件的主要信息，通过上一页页号和下一页页号，通过这两个属性可以把页与页之间连接起来，形成一个双向链表。（通过页号和页大小，就可以计算出下一页和上一页在磁盘上的偏移量）

页主体

⻚主体部分是保存真实数据的主要区域，每当创建⼀个新⻚，都会⾃动分配两个⾏，⼀个是⻚内最小行 Infimun ，另⼀个是⻚内最⼤⾏ Supremun ，这两个⾏并不存储任何真实信息，⽽是做为 数据⾏链表的头和尾，第⼀个数据⾏有⼀个记录下⼀⾏的地址偏移量的区域 **next_record** 将页内所有数据⾏组成了⼀个单向链表，此时新⻚的结构如下所⽰：

在这里插入图片描述

（橘色部分为行信息区，淡黄色部分是数据区）

当向⼀个新⻚插⼊数据时，将 Infimun 连接第⼀个数据⾏，最后⼀⾏真实数据⾏连接 Supremun ，这样数据⾏就构建成了⼀个单向链表，更多的⾏数据插⼊后，会按照主键从⼩到⼤ 的顺序进⾏链接，如下图所⽰

在这里插入图片描述

页中的数据行是一个单链表

页目录

  • 当按照主键或索引查找某条数据时，最简单的方式是从头行**infimun**开始，沿链表顺序逐个对比查询，但一个页有16KB，通常有数百条数据，为了提高查询效率，InnoDB采用二分查找来解决效率问题
  • 具体的实现方式是，在每一个页中加入一个结构页目录Page Directory，将页内所有行进行分组，约定头行单独为一组，其他每个组最多8条数据，而最大行一定在最后一组。分完组后会把每个组最后一行的地址，按主键从小到大顺序记录在页目录中的槽中（每个槽都对应了一个分组），一旦分组中的数据行数量超过上限8个时，就会分裂出一个新的分组。
  • 后续在查询某⾏时，就可以通过⼆分查找，先找到对应的槽，然后在槽内最多8个数据⾏中进⾏遍 历即可，从⽽⼤幅提⾼了查询效率，这时⼀个⻚的核⼼结构就完成了
  • 例如要查找主键为6的⾏，先⽐对槽中记录的主键值，定位到最后⼀个槽2，再从最后⼀个槽中的第 ⼀条记录遍历，第⼆条记录就是我们要查询的⽬标⾏。

在这里插入图片描述

数据页头

数据页头记录了当前页保存数据相关的信息，如下图所⽰

在这里插入图片描述

B+在MySQL索引中的应用

⾮叶⼦节点保存索引数据，叶⼦节点保存真实数据，如下图所⽰

在这里插入图片描述

以查找id为5的记录，完整的检索过程如下：

  1. ⾸先判断B+树的根节点中的索引记录，此时5 < 7，应访问左孩⼦节点，找到索引⻚2
  2. 在索引 比特就业课 ⻚2中判断id的⼤⼩，找到与5相等的记录，命中，加载对应的数据页

索引分类

主键索引

  • 当在一个表上定义一个主键 PRIMARY KEY 时，自动创建索引，索引的值是主键列的值，InnoDB 使用它作为聚集索引。
  • 推荐为每个表定义一个主键。如果没有逻辑上唯一且非空的列或列集可以使用主键，则添加一个自增列。

普通索引

  • 最基本的索引类型，没有唯一性的限制。
  • 可能为多列创建组合索引，称为复合索引或组全索引

为了提高效率通常会为查询频率高并且重复度不高的列创建索引。创建索引之后都会生成一颗索引树，而索引树也是会占用磁盘空间的，所以创建索引时，要慎重考虑一下

唯一索引

  • 当在一个表上定义一个唯一键 UNQUE 时，自动创建唯一索引。
  • 与普通索引类似，但区别在于唯一索引的列不允许有重复值。

全文索引

  • 基于文本列 (CHAR、VARCHAR 或 TEXT 列) 上创建，以加快对这些列中包含的数据查询和 DML 操作
  • 用于全文搜索，仅 MyISAM 和 InnoDB 引擎支持。

聚集索引

  • 与主键索引是同义词

  • 如果没有为表定义 PRIMARY KEY，InnoDB 使用第一个UNIQUE 和 NOT NULL 的列作为聚集索引。（聚集索引可以标志数据行的唯一性）

  • 如果表中没有 PRIMARY KEY 或合适的 UNIQUE 索引，InnoDB 会为新插入的行生成一个行号并用 6 字节的 ROW_ID 字段记录，ROW_ID 单调递增，并使用 ROW_ID 做为索引。（ROW_ID 数据行中的一个隐藏列之一）

非聚集索引

  • 聚集索引以外的索引称为非聚集索引或二级索引
  • 二级索引中的每条记录都包含该行的主键列，以及二级索引指定的列。
  • InnoDB 使用这个主键值来搜索聚集索引中的行，这个过程称为回表查询

索引覆盖

  • 当一个 select 语句使用了普通索引且查询列表中的列刚好是创建普通索引时的所有或部分列，这时就可以直接返回数据，而不用回表查询，这样的现象称为索引覆盖

非聚集索引的查询过程

  1. 通过索引查到叶子结点中的索引记录
  2. 通过索引记录中的主键值，去主键索引树中找到相应的完整记录（回表查询）

示例：复合索引和索引失效

在student表中查询学生张三的信息,name和sn是复合索引

-- 通过索引查询的列sn，包含在索引中，不需要回表查询了，这种现象叫做索引覆盖
select sn from student where name='张三';
-- 当前的组合索引是通过学号来查询姓名索引，索引不会生效
select name from student where sn='100002';

因为创建索引时，name列在sn之前，那么使用的时候也要先使用name再使用sn。如果只使用sn列，那么索引就会失效，如果一定要使用su列值，可以为sn单独创建一个索引。

使用索引

自动创建

  • 当我们为⼀张表加主键约束(Primary key)，外键约束(Foreign Key)，唯⼀约束(Unique)时， MySQL会为对应的的列⾃动创建⼀个索引
  • 如果表不指定任何约束时，MySQL会⾃动为每⼀列⽣成⼀个索引并⽤**ROW_ID**进行标识

手动创建

主键索引

# ⽅式⼀，创建表时创建主键
create table t_test_pk)(
id bigint primary key auto_increment,
name varchar(20)
);
# ⽅式⼆，创建表时单独指定主键列
create table t_test_pk1(
id bigint auto_increment,
name varchar(20),
primary key(id)
);
# ⽅式三，修改表中的列为主键索引
create table t_test_pk2 (
id bigint,
name varchar(20)
);
alter table t_test_pk2 add primary key(id);
alter table t_test_pk2 modify id bigint auto_increment;

唯⼀索引

# ⽅式⼀，创建表时创建唯⼀键
create table t_test_uk(
id bigint primary key auto_increment,
name  varchar(20) unique
);
# ⽅式⼆，创建表时单独指定唯⼀列
create table t_test_uk1(
id bigint primary key auto_increment.
name varchar(20),
unique (name)
);
# ⽅式三，修改表中的列为唯⼀索引
create table t_test_uk2(
id bigint primary key auto_increment,
name varchar(20)
);
alter table t_test_uk2 add unique(name);

普通索引

# ⽅式⼀，创建表时指定索引列
create table t_test_index(
id bigint primary key auto_increment,
name varchar(20),
sno varchar(20),
index(sno)
);
# ⽅式⼆，修改表中的列为普通索引
create table t_test_index1 (
id bigint primary key auto_increment,
name varchar(20),
sno varchar(10)
);
alter table t_test_index1 add index(so);
# ⽅式三，单独创建索引并指定索引名
create table t_test_index1 (
id bigint primary key auto_increment,
name varchar(20),
sno varchar(10)
);
create index index_name on t_test_index2(sno);

创建复合索引

创建语法与创建普通索引相同，只不过指定多个列，列与列之间⽤逗号隔开

# ⽅式⼀，创建表时指定索引列
create table t_test_index4 (
id bigint primary key auto_increment,
name varchar(20),
sno varchar(10),
class_id bigint,
index (sno, class_id)
);
# ⽅式⼆，修改表中的列为复合索引
create table t_test_index5 (
id bigint primary key auto_increment,
name varchar(20),
sno varchar(10),
class_id bigint
);
alter table t_test_index5 add index (sno, class_id);
# ⽅式三，单独创建索引并指定索引名
create table t_test_index6 (
id bigint primary key auto_increment,
name varchar(20),
sno varchar(10),
class_id bigint
);
create index index_name on t_test_index6 (sno, class_id);

查看索引

# ⽅式⼀：show keys from 表名
show keys from t_test_index
# ⽅式⼆
show index from t_test_index;
# ⽅式三，简要信息：desc 表名;
desc t_test_index;

删除索引

主键索引

# 语法
alter table 表名 drop primary key;

⽰例：删除t_test_index6表中的主键

alter table t_test_index6 drop primary key;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
# 如查提⽰由于⾃增列的错误，先删除⾃增属性
alter table t_test_index6 modify id bigint;
# 重新删除主键
alter table t_test_index6 drop primary key;

其他索引

# 语法
alter table 表名 drop index 索引名；
# ⽰例，删除t_test_index6表中名为index_name的索引
alter table t_test_index6 drop index index_name;

创建索引的注意事项

  • 索引应该创建在⾼频查询的列上
  • 索引需要占用额外的存储空间
  • 对表进行插⼊、更新和删除操作时，同时也会修索引，可能会影响性能
  • 创建过多或不合理的索引会导致性能下降，需要谨慎选择和规划索引

怎么查自己写的SQL有没有走索引？

可以查看执行计划，explain

先为学生表创建一个索引（组合）

create index idx_student_sn_name on studrnt(sn,name);

1.不加条件，查询所有

explain select * from student；

在这里插入图片描述

2.使用主键查询

在这里插入图片描述

3.子表使用查询

在这里插入图片描述

type访问类型

从左至右，性能由差到好

  1. ALL: 扫描全表

  2. index: 扫描全部索引树

  3. range: 扫描部分索引，索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询

  4. ref: 使用非唯一索引或非唯一索引前缀进行的查找，不是主键或不是唯一索引（eq_ref 和 const 的区别：）

  5. eq_ref: 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

  6. const, system: 单表中最多有一个匹配行，查询起来非常迅速，例如根据主键或唯一索引查询。system 是 const 类型的特例，当查询的表只有一行的情况下， 使用 system。

  7. NULL: 不用访问表或者索引，直接就能得到结果，如:select 1;

4.使用普通索引

在这里插入图片描述

5.使用复合索引

在这里插入图片描述

查询的字段有部分不是索引中字段的时候会发生回表查询

在这里插入图片描述

创建索引时sn在name之前，在只使用name的时候索引idx_student_sn_name就会失效，此时Extra里面就有Using Where

在这里插入图片描述

Extra: 执行情况的说明和描述。包含不适合在其他列中显示但十分重要的额外信息。

  1. Using index: 表示使用索引，如果只有 Using index，说明他没有查询到数据表，只用索引表就完成了这个查询，这个叫覆盖索引。
  2. Using where: 表示条件查询，如果不读取表的所有数据，或不是仅仅通过索引就可以获取所有需要的数据，则会出现 Using where。

在这里插入图片描述

posted on 2025-11-21 14:44  ljbguanli  阅读(0)  评论(0)    收藏  举报

博客园  ©  2004-2025
浙公网安备 33010602011771号 浙ICP备2021040463号-3