我要评论mysql 索引是提升查询效率的核心机制 , 基于特定的数据结构(b+树,哈希等)对数据进行预排序和快速定位 , 避免全表扫描
一.索引使用哪种数据结构
1.哈希索引
- 基于哈希表 , 键值直接映射到存储位置 , 查询的时间复杂度为 o(1)
- 适合精度匹配(=) , 但不支持范围查询 , 排序 , 模糊查询
2.b+树索引(最常用)
2.1 b+树简介
b+树是一种经常用于数据库和文件系统等场合的平均查找树 , mysql 索引采用的数据结构 , 以 4 阶 b+树为例
在二叉搜索树的基础上可以有效控制树高 , 也就意味着在在相同数据量的情况下 可以有效减少 io 次数 , 从而提升效率
2.2 b+ 树特点
- 能够有效保持数据稳定有序 , 插入与修改有较稳定的时间复杂度
- 非叶子结点仅具有索引的功能 , 不存储数据 , 所有叶子结点保存真实数据
- 所有叶子结点构成一个有序链表 , 可以按照 key 排序的次序来依次遍历全部数据
2.3 b+树与 b 树的对比
- 叶子结点之间有一个相互连接的引用 , 使数据连续去 , 且相互链接 , 便于区间查找和搜索 , 并且在 mysql中使用的是双向链表
- 非叶子结点的值都包含在叶子节点中 , mysql 非叶子结点之保存了对叶子结点的引用 , 没有保存真实数据 , 所有的真实数据都保存在叶子结点中
- 对于 b+树而言 , 在相同树高的情况下 , 查找任意元素的时间复杂度都是一样的 , 性能均衡
2.4 b+树索引的使用
- 所有数据都保存在叶子结点中 , 非叶子结点仅保存索引键 , 形成有序链表 , 支持范围查找
- 适用于范围查找(>,<,between) , 排序(order by) , 多列联合查询
2.5 b+树在 mysql 索引中的应用
非叶子结点保存索引数据 , 叶子结点保存真实数据
二 . 索引中的页
1.索引与页的关系
mysql 索引(b+树)的每一层结点都是独立的页 , 通过页内指针和页间链表组织 , 实现高效数据查询
# b+ 树索引与页的对应关系
┌─────────────────────────────────────────────────────┐
│ 根节点页（16kb） - 非叶子节点 │
│ ┌───────────┬───────────┬───────────┬───────────┐ │
│ │ 键值:100 │ 键值:200 │ 键值:300 │ 键值:400 │ │
│ │ 指针:页2 │ 指针:页3 │ 指针:页4 │ 指针:页5 │ │
│ └───────────┴───────────┴───────────┴───────────┘ │
└─────────────────────────────────────────────────────┘
↓ ↓ ↓ ↓
┌───────────────┐ ┌───────────────┐ ┌───────────────┐ ┌───────────────┐
│ 子节点页2 │ │ 子节点页3 │ │ 子节点页4 │ │ 子节点页5 │
│ （非叶子节点） │ │ （非叶子节点） │ │ （非叶子节点） │ │ （非叶子节点） │
└───────────────┘ └───────────────┘ └───────────────┘ └───────────────┘
↓ ↓ ↓ ↓
┌───────────────┐ ┌───────────────┐ ┌───────────────┐ ┌───────────────┐
│ 叶子节点页10 │ │ 叶子节点页11 │ │ 叶子节点页12 │ │ 叶子节点页13 │
│ （聚簇索引： │ │ （聚簇索引： │ │ （聚簇索引： │ │ （聚簇索引： │
│ 存储完整数据） │ │ 存储完整数据） │ │ 存储完整数据） │ │ 存储完整数据） │
│ ┌─────────┐ │ │ ┌─────────┐ │ │ ┌─────────┐ │ │ ┌─────────┐ │
│ │ id:1-99 │ │ │ │id:100-199│ │ │ │id:200-299│ │ │ │id:300-399│ │
│ └─────────┘ │ │ └─────────┘ │ │ └─────────┘ │ │ └─────────┘ │
│ 前指针:null │ │ 前指针:页10 │ │ 前指针:页11 │ │ 前指针:页12 │
│ 后指针:页11 │ │ 后指针:页12 │ │ 后指针:页13 │ │ 后指针:null │
└───────────────┘ └───────────────┘ └───────────────┘ └───────────────┘2.为什么使用页 ?
在.ibj 文件中最重要的结构体就是页 , 页是内存与磁盘交互的最小单元 , 默认大小是 16 kb , 每次内存与磁盘交互至少读一页 , 所以在磁盘中每个页内部的地址都是连续的 , 之所 以这样做，是因为在使⽤数据的过程中，根据局部性原理，将来要使⽤的数据⼤概率与当前访问的 数据在空间上是临近的，所以⼀次从磁盘中读取⼀⻚的数据放⼊内存中，当下次查询的数据还在这 个⻚中时就可以从内存中直接读取，从⽽减少磁盘i/o提⾼性能
局部性原理： 是指程序在执⾏时呈现出局部性规律，在⼀段时间内，整个程序的执⾏仅限于程序中的某⼀部 分。相应地，执⾏所访问的存储空间也局限于某个内存区域，局部性通常有两种形式：时间局部 性和空间局部性。
时间局部性（temporallocality）：如果⼀个信息项正在被访问，那么在近期它很可能还会被再 次访问。
空间局部性（spatiallocality）：将来要⽤到的信息⼤概率与正在使⽤的信息在空间地址上是临 近的
- 每个页即使没有数据也会使用 16kb 空间 , 同时与索引的 b+树种的结点对应
3 索引结点与页之间的关系
- 每个 b+ 树结点都是一个独立的 16kb 页
- 非叶子结点页 : 仅存储 索引键值 和 子节点页的指针
- 叶子节点 : 聚簇索引种存储完整数据记录 , 二级索引中存储键值和主键值
4 数据页的基本结构
① 文件头部 file header(38 字节)
- 存储页的元数据 , 用于标识页的基本信息和关联关系
- 核心字段:
page number : 页的唯一编号 , 用于定位页在磁盘上的位置
prev page / next page : 上一页/下一页的编号 , 形成双向链表(方便范围查询)
page typr : 页的类型(索引页 , 数据页 , undo 日志页)
checksum : 页的校验和 , 用于验证数据的完整性
② 数据页头 page header(56 字节)
- 存储页的状态信息 , 描述页内部数据的组织情况
- 核心字段 :
page level : 当前页在 b+ 树种的层级
record count : 页内存储的用户记录数
free space offset : 空闲空间的起始偏移量(标识空闲空间的位置)
directory slot count : page directory 中的槽数量 (用于快速定位记录)
③infimum + supremum(最小/最大记录 , 固定存在)
- 两个虚拟的记录 , 不存储实际数据 , 用于定义页内记录的边界
④user records（用户记录）
- 存储实际的表数据或索引键值（聚簇索引页存完整数据，二级索引页存键值 + 主键）。
- 记录按 “行格式” 存储（如 compact、dynamic 格式），包含字段值、隐藏列（如主键 id、事务 id、回滚指针）。
⑤free space（空闲空间）
- 页内未使用的连续空间，呈动态变化。
插入记录时，从空闲空间分配内存；删除记录时，释放的空间会合并到空闲空间。 - 当空闲空间不足时，会触发页分裂（page split）。
⑥page directory（页面目录）
- 类似 “书籍目录”，用于快速定位页内记录，避免全页扫描。
存储部分记录的偏移量（按主键排序），每个偏移量称为一个 “槽”（slot）。 - 查找记录时，先通过二分法定位槽，再在槽对应的记录区间内遍历查找。
⑦file trailer（文件尾部，8 字节）
- 用于校验页的完整性，确保页在磁盘 i/o 过程中未损坏。
- 核心字段：
checksum：与 file header 中的校验和一致，用于比对验证。
lsn（log sequence number）：页的日志序列号，关联事务日志，确保数据持久化
5. 三层 b + 树的存储容量计算
三层 b + 树结构为 “根节点索引页 → 二级节点索引页 → 叶子节点数据页”因此总记录数为：根节点索引记录数 × 二级节点索引记录数 × 叶子节点数据记录数 = 1170 × 1170 × 16 = 21,902,400条
三层树高的 b + 树可存储约 2190 万条记录，且检索时只需三次 i/o（对应树的三层访问），体现了 b + 树在大规模数据存储与高效检索上的优势
三 . 索引的分类
1.按功能与约束划分
① 主键索引 ( primary key)
- 当在一个表中定义一个主键时 , 自动创建 , 唯一且非空 , 一个表只能有一个主键索引 , 索引值是主键的值 , innodb 中使用它为聚簇索引
② 普通索引 (index)
- 无任何约束 , 仅用于加速查询 , 允许列值重复 , 是最基础的索引类型
- 可能为多列创建组合索引，称为复合索引或组全索引
③ 唯一索引 (unique)
- 当在一个表上定义一个唯一键时 , 自动创建唯一索引
- 与普通索类似 , 区别是 唯一索引的列不允许有重复值
④ 联合索引(多列索引)
- 由多个列组合创建的索引 , 遵循'最左前缀匹配原则' : 查询序包含最左列才能命中索引
- 适用于多条件查询 , 需要合理安排排列顺序(区分高度的列放在左侧)
⑤ 全文索引(按数据结构划分)
- 基于文本列(char , varchar 或 text)上创建 , 以加快对这些类中包含的数据查询和 dml 操作
- 用于全文搜索 , 仅 mysqam 和 innodb 引擎支持
2.按存储方式划分 (innodb 引擎)
① 聚簇索引(clustered index)
- 主键索引默认时聚簇索引 , 如果没有为表定义主键 , inndb 使用第一个 唯一键和非空列作为聚集索引
- 如果表中没有主键或合适的唯一键索引 , innodb 会为新插入的行生成一个行号并用 6 字节 的 row_id 字段记录 , row_id 单调递增 , 并使用 row_id 作为索引(隐藏列)
索引与数据存储在一起 , 叶子结点即数据记录 , 仅主键索引是聚簇 索引
优势 查询主键时无需徽标 , 效率高 ; 劣势 主键更新会导致数据移动 , 影响性能
② 二级索引(secondary index)
- 聚集索引以外的索引称为 非聚集索引或而二级索引
- 二级索引中的每条记录都包含该行的主键列 , 以及二级索引指定的列
- innodb 使用这个主键值来搜索聚集索引中的行 , 这个过程称为回表查询
③ 索引覆盖
- 查询字段均在索引中无需回表的优化方式
四.使用索引
1.查看索引
① 方式 1 : show keys from 表名
② 方式 2 : show index from 表名
③ 方式 3 : 简要信息 : desc 表名
④ 查看索引信息
2.自动创建
- 当我们为一张表加主键约束 , 外键约束 , 唯一约束时 , mysql 回为对应的列自动创建一个索引
- 如果表不指定任何约束时 , mysql 会自动为每一列生成一个索引并用 row_id 进行标识
3.手动创建
① 主键索引
- 方式 1 : 创建表时创建主键
create table t_test_pk( id bigint primary key auto_increment, name varchar(20) );
- 方式 2 : 创建表时单独指定主键列
create table t_test_pk1( id bigint auto_increment, name varchar(20), primary key(id) );
- 方式 3 : 修改表中的列为主键索引
语法 : alter table 表名 [add | modify | drop] 要修改的内容
示例 : alter table t_test_pk2 modify id bigint auto_increment;
create table t_test_pk2( id bigint, name varchar(20) ); alter table t_test_pk2 add primary key(id);#为表中添加主键 , 并指定id
② 唯一索引
- 方式 1 : 创建表时创建唯一键
create table t_test_uk( id bigint primary key auto_increment, name varchar(20) unique );
- 方式 2 : 创建表时单独指定唯一列
create table t_test_uk1( id bigint primary key auto_increment, name varchar(20), unique (name) );
- 方式 3 : 修改表中的列为唯一索引
create table t_test_uk2( id bigint primary key auto_increment, name varchar(20) ); alter table t_test_uk2 add unique (name);
4.普通索引
按创建的时机分为两种
① 创建表时指定索引列
create table t_test_index( id bigint primary key auto_increment, name varchar(20) unique, sno varchar(10), 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(sno);
③ 单独创建索引并指定索引名
语法 : create index 索引名(一般为 index_ 表名 _ 列名) on 表名(列名);
create table t_test_index2( id bigint primary key auto_increment, name varchar(20), sno varchar(10) ); create index index_name on t_test_index2(sno);
5.创建复合索引
创建语法与创建普通索引相同 , 只不过指定多个列 , 列与列之间用逗号隔开
① 创建表时指定索引列
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);
6.删除索引
① 删除主键索引
语法 : alter table 表名 drop primary key;
注意 : 如果主键是自增列 , 需要先改为非自增 , 再删去主键
示例 :
alter table t_test_index6 modify id bigint; alter table t_test_index6 drop primary key;
② 其他索引
语法 : alter table 表名 drop index 索引名;
示例 :
alter table t_test_index6 drop index index_name;
7.创建索引注意事项
- 索引应该创建再高频查询的列上
- 索引需要占用额外的存储空间
- 对表进行插入,更新,删除操作时 , 同时也会修改索引 , 可能会影响性能
- 常见过多或不合理的索引会导致性能下降 , 需要谨慎选择和规划索引
8.如何查看自己写的 sql 走没走索引?
可以查看执行计划 , explain+查询语句
到此这篇关于mysql数据库页与索引示例详解的文章就介绍到这了,更多相关mysql页和索引内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论