每日一面 - 从 innodb 的索引结构分析,为什么索引的 key 长度不能太长?

本问题参考: https://www.zhihu.com/question/410506694/answer/1368215298 ,答案为个人原创

MySQL innoDB引擎索引基于 B+树,B+树有以下特点:

image
图片参考自:链接

  • 每个节点中子节点的个数不能超过 N,也不能小于 N/2(不然会造成页分裂或页合并)
  • 根节点的子节点个数可以不超过 m/2,这是一个例外
  • m 叉树只存储索引,并不真正存储数据,只有最后一行的叶子节点存储行数据。
  • 通过链表将叶子节点串联在一起,这样可以方便按区间查找
  • 同时,InnoDB主键索引和非主键索引不一样。主键索引,叶子节点是行所有数据,非主键索引叶子节点只是这一列的数据以及指向主键的指针,如果需要其他列数据则需要通过主键指针查询聚簇索引。

然后,就需要提到一个概念,innodb_page_size。InnoDB引擎读取数据,是一页一页读取的,这是InnoDB读取一页数据的大小。
innodb_page_size 是一个初始化数据库实例的参数,在目前的版本中(>=5.7.6),可以选择的值有4096, 8192, 16384, 32768, 65536默认是16KB

image

一般越小,内存划分粒度越大,使用率越高,但是会有其他问题,就是限制了索引字段还有整行的大小。innodb引擎读取内存还有更新都是一页一页更新的,这个innodb_page_size决定了,一个基本页的大小。常用B+Tree索引,B+树是为磁盘及其他存储辅助设备而设计一种平衡查找树(不是二叉树)。B+树中,所有记录的节点按大小顺序存放在同一层的叶子节点中,各叶子节点用指针进行连接。MySQL将每个叶子节点的大小设置为一个页的整数倍,利用磁盘的预读机制,能有效减少磁盘I/O次数,提高查询效率。
对于主键索引,如果一个行数据,超过了一页的一半,那么一个页只能容纳一条记录,这样B+Tree在不理想的情况下就变成了双向链表,B+树失去了意义。对于非主键索引,那么索引列数据+主键指针数据超过一页的一半,也是同理。

最后,说一下索引字段大小限制:

索引字段大小限制

  • 关于innodb_large_prefix这个配置的限制:
    • 对于MySQL5.7.7之前,这个值默认是False,之后(包含5.7.7),这个值默认为True
    • 对于使用DYNAMIC或者COMPRESSED的Row Format,并且innodb_large_prefix为True,最大所以字段前缀的限制大小是3072 bytes(也就是字段的前3072bytes才会被索引)。如果这个配置是false,最大是767bytes。如果索引的字段,总长度超过这个限制,建表或者修改表就会报错。
    • 如果更改了配置或者行格式,导致最大限制从3072 bytes变为767bytes。对于现有的表这个限制是会生效的,但是已有的索引不会受影响,就是不能新建而已。
    • 这个配置未来过期掉,也就是说,在之后的版本中,默认索引字段前缀最大值为3072Bytes(不考虑Row Format)
  • 关于innodb_page_size这个数据库实例初始化配置:
    • 如果在创建数据库实例的时候修改了innodb_page_size这个参数(默认16KB),那么字段前缀的限制大小是3072 bytes这个限制也会改变。3072bytes对应16KB的innodb_page_size,1563bytes对应8KB,767对应4KB
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 酷酷鲨 设计师:CSDN官方博客 返回首页