MySQL索引数据结构
索引是帮助MySQL高效获取数据的 排好序的 数据结构
数据结构演示地址:https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
1.二叉树
左边节点小于右边节点,主键自增时变成链表结构
2.红黑树(平衡二叉树)
数据量很大时,这颗树会异常庞大(树高问题)
3.B Tree
- 所有索引元素不重复
- 节点中的索引从左往右递增
- 叶子节点之间的没有指针,区别于B+树
- data存储的是数据对应的磁盘地址, k-v结构
4.B+Tree(B Tree变种,MySQL使用的索引结构)
- 非叶子节点不存储data,只存储索引(冗余),可以放更多的索引
- 叶子节点包含所有索引字段
- 叶子节点用双向指针连接,提高区间访问的性能
计算3层高的B+Tree能存储多少数据结构:
假设是索引是BigInt类型的数据,BigInt 占 8个字节 ,同时还是用6个字节存储了它指向的数据的物理地址;MySQL在使用innodb引擎的时候页大小默认是16K,假设树高为3,第一层即可以存储 16KB * 1024 / (8B + 6B) = 1170(只存索引),第二层也是1170*1170 (第二层不是叶子结点,只存索引,不存储数据),第三层,存储数据,一般情况下一行数据的大小肯定不会超过1KB,就按照1KB,
3层高的B+Tree , 存储BitInt可以存储 1170 * 1170 * 16 = 2千1 百万
5.Hash表
- 对索引的key进行一次hash计算就可以定位出数据存储的位置
- 很多时候Hash索引要比B+ 树索引更高效
- 仅能满足 “=”,“IN”,不支持范围查询
- hash冲突问题
6.MyISAM索引文件和数据文件是分离的(非聚集)
MyISAM存储引擎的索引文件 MYI 和数据文件 MYD 是分离的(非聚集),这就是非聚簇索引的含义
如下图可以看出MyISAM的索引文件仅仅保存数据记录的地址。
在MyISAM中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复。
7.InnoDB索引实现(聚集)
- 表数据文件本身就是按B+Tree组织的一个索引结构文件,ibd文件就是数据和索引,这两个存储在一个文件中
- 主键索引:叶子节点包含了完整的数据记录
- 辅助索引(二级索引):data存储的是相应记录主键的值而不是地址
问题
1、为什么建议InnoDB表必须建主键,并且推荐使用整型的自增主键?
因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。
2、为什么非主键索引结构叶子节点存储的是主键值?(一致性和节省存储空间)