深入理解MySQL索引底层数据结构

本文共3806字,阅读本文大概需要8~13分钟

什么是索引

索引是帮助MySQL高效获取数据的排好序数据结构
若没有使用索引的话,查找数据的时候会从上到下一条一条的向下查找,直到找到数据为止,索引的目的是为了提高查找速度的。

索引的数据结构

二叉树


如图所示,若采用二叉树,的确能提高查找的速度。对于 col2 列的 89 来说,使用索引之前需要 6 次查找,而使用二叉树做索引之后,只需要 2 次查找即可
但是,对于 col1 列来说,查找对应的那条记录同样需要 6 次。这是为什么?对于二叉树来说,若采用递增的列作为索引的话,二叉树会退化成链表

红黑树


本质上还是一个二叉树,叫做二叉平衡树。平衡树在插入和删除的时候,会通过旋转操作将树的左右节点达到平衡
红黑树规则定义:

  • 任何一个节点都有颜色,红色或黑色
  • 根节点是黑色的
  • 父子节点之间不能出现两个连续的红节点
  • 任何一个根节点,遍历到它的子孙节点,所经过的黑节点树必须相同
  • 空节点被认为是黑色的
    若采用红黑树作为索引的数据结构,当数据量特别大的时候,黑红树的高度就特别大了。假如有 500w 条数据,那么红黑树的高度可能达到几十甚至更多,要经过大量的磁盘 I/O 操作,性能太差了,若要解决这一问题,就要减少 I/O 的次数

B树(B-Tree) 和 B+树(B+Tree)的选择

B-Tree

  • 叶节点具有相同的深度,叶节点的指针为空
  • 所有索引元素不重复
  • 节点中的数据索引从左到右递增排列

B+Tree

  • 非叶子节点不存储data,只存储索引(冗余),可以放更多的索引
  • 叶子节点包含所有索引字段
  • 叶子节点用指针连接,提高区间访问的性能

对比B-Tree和B+Tree

  • B-Tree的所有节点都存储了 data 元素,B+Tree 的非叶子节点不存储 data 元素,则 B+Tree 的一个非叶子节点可以存储更多的索引
  • B+Tree在叶子节点之间增加了指针,对于范围查找来说,有很好的支持

Hash

  • 对索引的key进行一次hash计算就可以定位出数据存储的位置
  • 很多时候Hash索引要比B+ 树索引更高效
  • 仅能满足 “=”,“IN”,不支持范围查询
  • hash冲突问题

mysql索引在实际中的使用

MySQL在实际中表的索引存储可以选择 Hash 或 B+Tree

  • 索引的数据结构和具体存储引擎的实现有关,mysql中使用较多的索引有hash索引,B+树索引,innodb的索引实现为B+树memory存储引擎为hash索引。
  • B+树是一个平衡的多叉树,从根节点到每个叶子节点的高度差值不超过1,而且同层级的二节点间有指针相关连接,在B+树上的常规检索,从根节点到叶子节点的搜索效率基本相当,不会出现大幅波动,而且基于索引的顺序扫描时,也可以利用双向指针快速左右移动,效率非常高。因为,B+树索引被广泛应用于数据库、文件系统等场景。
  • 哈希索引就是采用一定的哈希算法,把键值换算成新的哈希值,检索时不需要类似B+树那样从根节点到叶子节点逐级查找,只需一次哈希算法即可立刻定位到相应的位置,速度非常快。
  • 如果是等值查询,那么哈希索引明显有绝对优势,因为只需要经过一次算法即可找到相应的键值,前提是键值都是唯一的。如果键值不是唯一的,就需要先找到该键所在位置,然后再根据链表往后扫描,知道找到对应的数据
  • 如果是范围查询检索,这时候哈徐索引就毫无用武之地了,因为原先是有序的键值,经过哈希算法后,有可能变成不连续的了,就没办法再利用索引完成范围查询检索
  • 哈希索引也没办法利用索引完成排序,以及like这样的部分模糊查询
  • 哈希索引也不支持多列联合索引的最左匹配规则
  • B+树索引的关键字检索效率比较平均,不像B树那样波动大,在有大量重复键值情况下,哈希索引的效率也是极低的,因此存在哈希碰撞问题。

MySQL存储引擎的实现

我们所说的存储引擎,是对于数据表的存储引擎,在同一个数据库中,不同的表可以有不同的存储引擎

MyISAM存储引擎的实现

MyISAM索引文件和数据文件是分离的(非聚集)

组成,MyISAM存储引擎由3部分组成

  • *.frm文件:存储的是表的结构
  • *.MYD文件:存储的是表的数据
  • *.MYI文件:存储的是表中的索引数据

InnoDB存储引擎的实现

InnoDB存储引擎索引文件和数据文件是合一的(聚集);

  • 表数据文件本身就是按B+Tree组织的一个索引结构文件
  • 聚集索引-叶节点包含了完整的数据记录

组成,InnoDB存储引擎由两部分组成

  • *.frm文件:存储的是表的结构
  • *.ibd文件:存储的是索引和数据

问题

为什么建议InnoDB表必须建主键,并且推荐使用整型的自增主键?

  • 因为 MySQL对于 InnoDB 表设计的就是按照 B+Tree 组织存储数据的,若没有主键就没有办法去存储数据了;但是在平时我们建表的时候没有指定主键也是可以建成功的,这是因为 MySQL 会自动生成一个6字节的 rowid 作为数据的唯一标识;
  • 若使用的 UUID 作为主键,在查找的时候需要去比较大小,字符串UUID比较的效率肯定低于数据的比较;在进行比较的时候会把数据拿到内存空间中做比较,UUID为字符串占用的内存空间就会较多;
  • 若是递增的,则插入的数据直接向后排,这个节点满了,直接新增一个节点就好了;若不是递增的,有个节点存储满了(5, 9),但是新插入了一个数据(7)在这个节数据的中间,则需要将这个节点先分裂,再平衡去满足 B+Tree 的结构;

为什么非主键索引结构叶子节点存储的是主键值?(一致性和节省存储空间)

因为在插入数据之前先要维护一下索引,然后再将数据插入进去;若主键索引和非主键索引的叶子节点都存储具体的数据,则一个 insert 语句插入成功的判断就是向主键索引中插入成功且向非主键索引中也插入成功,这样就造成了事务的问题,事务是很耗性能的;当然,主键索引和非主键索引的叶子节点都存储具体数据,会造成数据的同样的数据存储了几份,就造成了空间的浪费;

mysql聚簇和非聚簇索引的区别是什么?

mysql的索引类型跟存储引擎是相关的,innodb存储引擎数据文件跟索引文件全部放在ibd文件中,而myisam的数据文件放在myd文件中,索引放在myi文件中,其实区分聚簇索引和非聚簇索引非常简单,只要判断数据跟索引是否存储在一起就可以了。

innodb存储引擎在进行数据插入的时候,数据必须要跟索引放在一起,如果有主键就使用主键,没有主键就使用唯一键,没有唯一键就使用6字节的rowid,因此跟数据绑定在一起的就是聚簇索引,而为了避免数据冗余存储,其他的索引的叶子节点中存储的都是聚簇索引的key值,因此innodb中既有聚簇索引也有非聚簇索引,而myisam中只有非聚簇索引。

简述MyISAM和InnoDB的区别

MyISAM

  • 不⽀持事务,但是每次查询都是原⼦的;
  • ⽀持表级锁,即每次操作是对整个表加锁;
  • 存储表的总⾏数;
  • ⼀个MYISAM表有三个⽂件:索引⽂件、表结构⽂件、数据⽂件; - 采⽤⾮聚集索引,索引⽂件的数据域存储指向数据⽂件的指针。辅索引与主索引基本⼀致,但是辅 索引不⽤保证唯⼀性。

InnoDB

  • ⽀持ACID的事务,⽀持事务的四种隔离级别;
  • ⽀持⾏级锁及外键约束:因此可以⽀持写并发;
  • 不存储总⾏数;
  • ⼀个InnoDb引擎存储在⼀个⽂件空间(共享表空间,表⼤⼩不受操作系统控制,⼀个表可能分布在 多个⽂件⾥),也有可能为多个(设置为独⽴表空,表⼤⼩受操作系统⽂件⼤⼩限制,⼀般为 2G),受操作系统⽂件⼤⼩的限制;
  • 主键索引采⽤聚集索引(索引的数据域存储数据⽂件本身),辅索引的数据域存储主键的值;因此 从辅索引查找数据,需要先通过辅索引找到主键值,再访问辅索引;最好使⽤⾃增主键,防⽌插⼊ 数据时,为维持B+树结构,⽂件的⼤调整。

索引的简单使用

  • 在使用非主键索引查找的时候,先从非主键索引的树中查询到对应的主键值,然后使用主键值去到主键索引的树中去查找;
  • 对于非主键单值索引,若索引字段的值为 null,则它的数据不会放到非叶子节点上,是放在叶子节点的链表的最前面的;(强烈不建议字段设置为null)

联合索引的底层存储结构


如图,若联合索引从左到右由字段a,b,c组成
联合索引在存数据或者进行比较的时候,先比较联合索引最前面的字段,若最前面的字段值一样,那么再比较第二个字段的值,以此类推
联合索引的索引字段中有一个值为null,则将其放在叶子节点的最前面,可以认为null值是最小的

估计一张表可以存储多少条数据

  • 查看mysql的页大小
mysql> SHOW GLOBAL STATUS like 'Innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)
  • mysql的页大小默认为16KB,这个数据是经过多次验证了的,不建议修改。
  • 树的高度为2,为3时可以存储多少条数据
分析:
    先看非叶子节点,假设主键ID为bigint类型,那么长度则为8B,指针大小为6B,则一页(非叶子节点)可以存储16KB/(8B+6B)约为 1170个索引(索引+指针)。
    对于叶子节点,假设每条数据大小1KB,则可以存储16条数据
    对于树的高度为2,那么可以存储 1170 * 16 = 18720 条数据
    对于树的高度为3,那么可以存储 1170 * 1170 * 16 = 21902400 条数据(千万级别)
posted @   小羊abc  阅读(265)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性
点击右上角即可分享
微信分享提示