索引的数据结构
概述
1、MySQL 对索引定义:索引(Index)是帮助 MySQL 高效获取数据的数据结构
2、本质:数据结构
(1)排好序的快速查找数据结构,满足特定查找算法
(2)这些数据结构以某种方式指向数据, 这样就可以在这些数据结构的基础上实现高级查找算法
3、优点
(1)提高数据检索的效率,降低数据库 I/O 成本
(2)通过创建唯一索引,可以保证数据库表中每一行数据的唯一性
(3)在实现数据的参考完整性方面,可以加速表和表之间的连接,对于有依赖关系的子表和父表联合查询时,可以提高查询速度
(4)在使用分组和排序子句进行数据查询时,可以显著减少查询中分组和排序的时间 ,降低 CPU 消耗
4、缺点
(1)创建索引、维护索引耗费时间 ,并且随着数据量的增加,所耗费的时间也会增加
(2)索引需要占用磁盘空间,如果有大量的索引,索引文件就可能比数据文件更快达到最大文件尺寸
(3)索引提高查询速度,同时降低更新表的速度
(4)当增加、删除、修改表中数据时,索引需要动态维护,降低数据维护速度
常见索引概念
1、按照物理实现方式分为 2 种
(1)聚簇(聚集)索引
(2)非聚簇(非聚集)索引 / 二级索引 / 辅助索引
聚簇索引
1、不是单独的索引类型,而是一种数据存储方式
(1)B+ 树的所有的完整用户记录,都记录在叶子节点
(2)完整用户记录:指这个记录中存储所有列的值,包括隐藏列
(3)聚簇:表示数据行与相邻键值聚簇存储
2、使用记录主键值的大小进行记录和页的排序
(1)页(包括叶子节点和内节点)内的记录按照主键的大小顺序排成一个单向链表,页内的记录被划分成若干个组,每个组中主键值最大的记录在页内的偏移量会被当作槽依次存放在页目录中,可以在页目录中通过二分法快速定位到主键列等于某个值的记录
(2)各个存放用户记录的页是根据页中用户记录的主键大小顺序排成一个双向链表
(3)存放目录项记录的页(B+ 树的非叶子节点)分为不同的层级,在同一层级中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表
(4)B+ 树的叶子节点存储的是完整的用户记录,即这个记录中存储所有列的值(包括隐藏列)
(5)B+ 树的非叶子节点存储主键 + 页号
3、不需要 INDEX 显式创建索引,InnoDB 自动创建聚簇索引
4、优点
(1)数据访问更快:因为聚簇索引将索引和数据保存在同一个 B+ 树中,因此从聚簇索引中获取数据比非聚簇索引更快
(2)聚簇索引对主键的排序查找、范围查找速度非常快
(3)按照聚簇索引排列顺序,查询显示一定范围数据时,由于数据都是紧密相连,数据库不用从多个数据块中提取数据,所以节省大量 I/O 操作
5、缺点
(1)插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能,因此,对于 InnoDB 表,一般都会定义一个自增 ID 列为主键
(2)更新主键代价很高 ,因为将会导致被更新的行移动,因此,对于 InnoDB 表,一般定义主键为不可更新
6、限制
(1)MySQL 只有 InnoDB 支持聚簇索引,MyISAM 不支持聚簇索引
(2)由于数据物理存储排序方式只能有一种,所以每个 MySQL 表只能有一个聚簇索引,一般情况下就是该表的主键
(3)如果没有定义主键,InnoDB 会选择 NOT NULL 的 UNIQUE 索引代替,如果没有这样的索引,Innodb 会隐式的定义一个主键来作为聚簇索引
(4)为了充分利用聚簇特性,所以 InnoDB 表的主键列尽量选用有序的顺序 id,而不建议用无序 id,比如 UUID、MD5、HASH、字符串列作为主键无法保证数据的顺序增长
(5)因为 B+ 树按照主键进行排序,只有在搜索条件是主键值时,聚簇索引才能发挥作用
非聚簇索引 / 二级索引 / 辅助索引
1、以一个非主键列,作为二级索引,创建新的 B+ 树
(1)B+ 树的叶子节点存储的并不是完整的用户记录,而是索引列(非主键列) + 主键列,由这两个列的值组成
(2)B+ 树的非叶子节点不再存储主键 + 页号,而是索引值(非主键列)+ 主键值 + 页号
2、回表:二级索引只能确定要查找记录的主键值,若想根据其他列的值查找到完整的用户记录,仍然需要到聚簇索引中再查一遍
(1)二级索引访问需要两次索引查找 ,第一次找到主键值,第二次根据主键值找到行数据
(2)不用回表,相当于每建立一棵 B+ 树都需要把所有完整用户记录再拷贝一遍,过于浪费存储空间
3、非聚簇索引的存在,不影响数据在聚簇索引中的组织,所以一张表可以有多个非聚簇索引
4、对于二级索引记录来说,是先按照二级索引列的值进行排序,在二级索引列值相同的情况下,再按照主键值进行排序
5、UNIQUE 二级索引的非叶子节点的目录项记录也会包含记录的主键值
联合索引
1、以多个非主键列的大小为排序规则,建立一个 B+ 树
(1)先把各个记录和页按照第一个非主键列进行排序
(2)在记录的第一个非主键列相同的情况下,采用第二个非主键列进行排序
(3)以此类推
2、本质仍是聚簇索引
聚簇索引、非聚簇索引的使用区别
1、聚簇索引的叶子节点存储数据记录;非聚簇索引的叶子节点存储数据位置
2、非聚簇索引不会影响数据表的物理存储顺序
3、一个表只能有一个聚簇索引,因为只能有一种排序存储的方式,但可以有多个非聚簇索引,即多个索引目录提供数据检索
4、使用聚簇索引时,数据的查询效率高,但若对数据进行插入、删除、更新等操作,效率比非聚簇索引低
InnoDB 索引
1、B+ 树:一个聚簇索引 + 多个非聚簇索引
2、B+ 树形成过程
(1)每当为某个表创建一个 B+ 树索引时,都会为这个索引创建一个根节点页面
(2)最开始表中没有数据时,每个 B+ 树索引对应的根节点中,既没有用户记录,也没有目录项记录
(3)随后向表中插入用户记录时,先把用户记录存储根节点中
(4)当根节点中的可用空间用完时,继续插入记录,此时会将根节点中的所有记录,复制到一个新分配的页(a),然后对这个新页进行页分裂的操作,得到另一个新页(b)
(5)这时新插入的记录根据键值的大小(聚族索引中的主键值,二级索引中对应的索引列的值),被分配到页 a 或页 b 中,而根节点便升级为存储目录项记录的页
(6)注意:一个 B+ 树索引的根节点创建后便不会再移动
2、内节点中目录项记录的唯一性
(1)为了让新插入记录能插入正确页中,需要保证在 B+ 树的同一层内节点,除页号这个字段以外,目录项记录是唯一的
(2)二级索引内节点的目录项记录,由三部分组成:索引列的值、主键值、页号
(3)把主键值添加到二级索引内节点中的目录项记录,保证 B+ 树每一层节点中各条目录项记录是唯一的,除页号这个字段外
3、一个页面最少存储 2 条记录
(1)一个 B+ 树只需要很少的层级,就可以轻松存储数亿条记录
(2)B+ 树本质是一个多层级目录,每经过一个目录时,都会过滤掉许多无效的子目录,直到最后访问到存储真实数据的目录
MyISAM 索引方案
索引 / 存储引擎 | MyISAM | InnoDB | Memory |
B 树索引 | 支持 | 支持 | 支持 |
1、实现原理不同
(1)InnoDB、MyISAM 默认 B 树索引
(2)Memory 默认 Hash 索引
2、MyISAM 使用 B+ 树作为索引结构,叶子节点 data 域存放数据记录的地址
MyISAM、InnoDB
1、MyISAM 索引方式都是非聚簇
(1)在 MyISAM 中却需要进行一次回表操作,即建立的索引相当于全部都是二级索引
(2)MyISAM 索引文件和数据文件是分离,索引文件仅保存数据记录的地址
(3)MyISAM 索引记录地址,从地址偏移量直接到文件中取数据,所以回表操作十分快速
(4)MyISAM 表可以没有主键
(5)将表中的记录按照记录的插入顺序单独存储在一个文件中(称之为数据文件)
(6)数据文件并不划分为若干个数据页,有多少记录就往这个文件中塞多少记录,可以通过行号快速访问到一条记录
(7)MyISAM 会为表的主键单独创建一个索引,只不过在索引的叶子节点中存储的不是完整的用户记录,而是主键值与行号的组合,即先通过索引找到对应的行号,再通过行号去找对应的记录
(8)如果有必要,也可以为其他列分别建立索引或者建立联合索引,其原理与 InnoDB 的索引差不多,只不过在叶子节点处存储的是索引列 + 行号,这些索引全部都是二级索引
2、InnoDB 包含 1 个聚簇索引
(1)在 InnoDB 存储引擎中,只需要根据主键值对(聚簇索引)进行一次查找就能找到对应的记录
(2)InnoDB 数据文件本身就是索引文件
(3)InnoDB 所有非聚簇索引 data 域存储相应记录主键的值
(4)InnoDB 通过获取主键之后再去聚簇索引里找记录,比不上直接用地址去访问
(5)InnoDB 要求表必须有主键,如果没有显式指定,则 MySQL 系统会自动选择一个可以非空,且唯一标识数据记录的列作为主键,如果不存在这种列,则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键,该字段长度为 6 个字节,类型为长整型
索引代价
1、空间代价
(1)每建立一个索引,就要建立一棵 B+ 树,每一棵 B+ 树的每一个节点都是一个数据页
(2)一个页默认占用 16KB 存储空间
2、时间代价
(1)B+ 树每层节点都是按照索引列的值,从小到大的顺序排序,页与页之间组成双向链表
(2)B+ 树的用户记录、目录项记录,按照索引列的值,从小到大的顺序排序,页内节点之间形成一个单向链表
(3)增、删、改操作可能会对节点和记录的排序造成破坏,所以存储引擎需要额外的时间进行一些记录移位,页面分裂、页面回收等操作来维护节点和记录的排序
Hash 结构
索引 / 存储引擎 | MyISAM | InnoDB | Memory |
HASH索引 | 不支持 | 不支持 | 支持 |
1、哈希碰撞
(1)在数据库中一般采用链接法解决
(2)将散列到同一槽位的元素放在一个链表中
2、时间复杂度
(1)哈希:O(1)
(2)树型:O(log2N)
3、索引结构设计为树型原因
(1)Hash 索引仅能满足 =、<>、IN 查询,如果进行范围查询,哈希型的索引,时间复杂度会退化为 O(n);而树型的仍保持为 O(log2N)
(2)Hash 索引中,数据的存储是没有顺序的,在 ORDER BY 情况下,使用 Hash 索引还需要对数据重新排序
(3)对于联合索引,Hash 值是将联合索引键合并后一起来计算的,无法对单独的一个键或者几个索引键进行查询
(4)对于等值查询,通常 Hash 索引的效率更高,若索引列的重复值如果很多,效率就会降低,因为遇到 Hash 冲突时,需要遍历桶中的行指针来进行比较,时间复杂度会退化为 O(n)
4、InnoDB 支持自适应 Hash 索引
(1)若某个数据被经常访问,满足一定条件时,则该数据页的地址存放到 Hash 表中
(2)方便根据 SQL 的查询条件加速定位到叶子节点
(3)当 B+ 树较深时,通过自适应 Hash 索引可以明显提高数据的检索效率
一个 M 阶 B 树(M > 2)有以下的特性
1、根节点的子节点树的范围:[2, M]
2、每个中间节点包含 k - 1 个元素和 k 个子节点,k 的取值范围为 [ceil(M / 2),M]
3、叶子节点包括 k - 1 个元素,k 的取值范围为 [ceil(M / 2),M]
4、假设中间节点的元素为:Key[1],Key[2],……,Key[k-1],且元素按照升序排序,即 Key[i] < Key[i+1]
(1)此时 k - 1 个元素相当于划分 k 个范围,即对应着 k 个指针,即为:P[1],P[2],……,P[k]
(2)其中 P[1] 指向元素小于 Key[1] 的子树,P[i] 指向元素属于(Key[i-1],Key[i])的子树,P[k] 指向关键字大于 Key[k-1] 的子树
5、所有叶子节点位于同一层
B+ 树
1、有 k 个子节点就有 k 个元素
2、非叶子节点的元素,同时存在子节点中,并且是在子节点中所有关键字的最大(或最小)
3、非叶子节点仅用于索引,不保存数据记录,跟记录有关的信息都放在叶子节点中
4、所有元素都在叶子节点,叶子节点构成一个有序链表,而且叶子节点本身按照元素的大小,从小到大顺序链接
索引 / 存储引擎 | MyISAM | InnoDB | Memory |
R 树索引 | 支持 | 支持 | 不支持 |
R 树索引
1、在 MySQL 很少使用,仅支持 geometry 数据类型
2、存储高维数据的平衡树
3、优势在于范围查找,解决高维空间搜索问题
事项
1、为了减少 I/O,索引树不会一次性加载
(1)数据库索引是存储在磁盘上的,如果数据量很大,必然导致索引的大小也会很大
(2)利用索引查询时,不可能将全部索引都加载进内存,只能逐一加载每一个磁盘页,因为磁盘页对应着索引树的节点
2、B+树 一般查找行记录,最多只需 1~3 次磁盘 I/O
(1)InnoDB 页大小为 16 KB,一般表的主键类型为 INT(占用 4 个字节)或 BIGINT(占用 8 个字节),指针类型一般为 4 或 8 个字节
(2)假设一个页(B+ 树中的一个节点)中大概存储 16KB / (8B + 8B) = 1K 个键值,估算 K 取值为 103,即一个深度为 3 的 B+ 树索引可以维护 103 * 103 * 103 = 10亿条记录,同时假定一个数据页也存储 103 条行记录数据
(3)实际情况中每个节点可能不能填满,因此在数据库中,B+ 树高度一般都在 2~4 层
(4)MySQL 的 InnoDB 在设计时,将根节点常驻内存,即查找某一键值的行记录时,最多只需要 1~3 次磁盘 I/O 操作
3、B+ 树比 B 树更适合实际应用中,操作系统的文件索引和数据库索引
(1)B+ 树的磁盘读写代价更低:B+ 树的内部结点并没有指向关键字具体信息的指针,因此其内部结点相对于 B 树更小,如果把所有同一内部结点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多,一次性读入内存中的需要查找的关键字也就越多,相对来说 I/O 读写次数也就降低
(2)B+ 树的查询效率更加稳定:由于非叶子节点并不是最终指向文件内容的节点,而只是叶子结点中关键字的索引,所有任何关键字的查找必须走一条从根结点到叶子结点的路,所有关键字查询的路径长度相同,导致每一个数据的查询效率相当
4、Hash 索引与 B+ 树索引的区别
(1)因为 Hash 索引指向的数据是无序的,而 B+ 树的叶子节点是个有序的链表:Hash 索引不能进行范围查询,而 B+ 树可以;Hash 索引不支持 ORDER BY 排序,而 B+ 树索引可以优化对该字段 ORDER BY 排序;Hash 索引无法进行模糊查询,而 B+ 树可以优化 LIKE 模糊查询
(2)Hash 索引不支持联合索引的最左侧原则(即联合索引的部分索引无法使用),而 B+ 树可以,对于联合索引来说,Hash 索引在计算 Hash 值时,是将索引键合并后再一起计算 Hash 值,不会针对每个索引单独计算 Hash 值,因此如果用到联合索引的一个或者几个索引时,联合索引无法被利用
(3)InnoDB 不支持哈希索引
5、针对 InnoDB、MyISAM 默认采用 B+ 树索引,无法使用 Hash 索引,InnoDB 不需要手动开启自适应 Hash,Memory、NDB 可以选择 Hash 索引
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战