MySQL索引底层数据结构与算法--笔记

索引

索引是帮助SQL高效获取数据的排好序的数据结构

索引结构

• 二叉树

  无法解决树平衡的问题

• 红黑树

  解决二叉树的平衡问题，每个节点存储的元素有限，当数据量特别大的时候，树的深度不能控制，导致IO次数不可控

• HASH

  通过算法将HASH值转化为文件存储的地址，查询时做HASH计算就能查询到数据，但是HASH不能解决范围查询

• B-Tree

  • B-Tree有度(Degree)的概念，即节点存储数据的个数，当 度存储的元素/度的大小 > 15/16 时，当前度进行分裂
    • 度的大小不会很大，否则导致树没有深度，也会导致查询时，一次IO不能将节点的数据全部获取到，需要多次IO才能获取整个节点的数据；内存与硬盘交互时，有页的概念，页的大小为4KB，每次从硬盘读取页的整数倍；一般节点的大小设置会根据一次IO获取多少页来确定，
  • 叶节点具有相同的深度
  • 叶节点的指针为空
  • 节点中的数据key从左到右递增排序

• B+Tree(B-Tree变种)

  • 非叶子节点不存储 数据，只存储索引，可以让度存储更多的索引
  • 叶子节点存储数据(MYISAM引擎叶子节点存储指针，INNODB存储整条数据)
  • 叶子节点间有箭头(引用)，顺序访问指针，提高区间访问的性能

B-Tree与B+Tree的主要区别

• 每个节点都存储数据
• 叶子节点之间没有箭头(引用)

B+Tree索引

• 一般使用磁盘I/O次数评价索引结构的优劣
• 预读：磁盘一般会顺序向后读取一定长度的数据(页的整数倍)，放入内存
• 局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用
• 节点大小设为等于一个页，每次新建节点直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，就实现了一个节点的载入只需要一次I/O
• 度一般会超过100，因此树的高度非常小(一般为3到5之间)

MyISAM引擎(非聚簇)

存储引擎是表级别的，一张表的存储引擎是MyISAM，该表会有三个文件：

文件一：表名_yisam.frm ----表结构

文件二：表名_myisam.MYD ----数据

文件三：表名_myisam.MYI ----索引

• MyISAM引擎的索引文件和数据文件是分离的
• MyISAM引擎的主键索引与非主键索引没有差别，叶子节点都是存储指针

INNODB引擎(聚簇)

表的储存引擎INNODB，只有两个文件：

文件一：表名_innodb_lock.frm ----表结构

文件二：表名_innodb_lock.ibd ----索引+数据

• 数据文件本身就是索引文件
• 表数据文件本身就是按B+Tree组织的一个索引结构文件
• 聚簇索引–叶子节点包含了完整的数据
• INNODB引擎的 主键索引 与 非主键索引是有区别的，因为主键索引中叶子节点中已经存储了完整的数据，非主键索引中叶子节点存储的是主键

innodb什么一定要有主键？并且推荐使用整型的自增主键？

• innodb存储引擎的数据结构必须需要一个主键才可以组织起来

• 如果在创建innodb表中没有设置主键，会自动找表中唯一值的列作为主键，若没有唯一值的列，则创建一个整型主键

• innodb存储引擎的数据结构是有序的，整型比其他类型更容易比较，且整形占用的空间比较小

为什么非主键索引没有存储完整的数据？

• 若非主键索引中存储了完整的数据，当新增/更新时，要保证数据的一致性；同时非主键索引中存储主键值比存储完整数据更节省空间

联合索引的底层存储结构是什么样子的？

• 节点的每个key存储联合索引所有字段的值，从第一个开始匹配，后面依次

组合索引匹配原则？

• 最左匹配