首发于MySQL全解

MySQL原理 - InnoDB表的限制

本文基于MySQL 5.7

目前OLTP业务的表如果是使用MySQL一般都会使用InnoDB引擎,这也是默认的表引擎。那么这种引擎有什么限制呢?根据官方文档总结下:

参考:dev.mysql.com/doc/refma 参考:dev.mysql.com/doc/refma

基本个数限制

  • 在MySQL5.6.9以后的版本,一个表的最大列个数(包含虚拟列,虚拟列是MySQL5.7的新特性)为1017,在之前的版本是1000
  • 一个表的最大索引数量(非主键索引)为64个
  • 复合索引最多可以包括16个列,超过会报错:ERROR 1070 (42000): Too many key parts specified; max 16 parts allowed

索引字段大小限制

  • 关于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

innodb_page_size

innodb_page_size是一个初始化数据库实例的参数,在目前的版本中(>=5.7.6),可以选择的值有4096, 8192, 16384, 32768, 65536。默认是16KB

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

这也是为什么要限制住,每一行的大小,也就是下一小节要讲的行长度与行大小限制

行长度与行大小限制

  • 行长度(Row Length,就是一个表去掉可变长度的类型字段 (VARBINARY, VARCHAR, BLOB and TEXT),定长字段的占用空间大小),这个行长度是一行数据至少要占用的长度。上一节提到了这个长度最好不要超过innodb_page_size的一半。对于LONGBLOB还有LONGTEXT字段,长度不能超过4GB,包含所有字段的总长度,不能超过4GB
  • 长大小(Row Size,这个是MySQL的限制,不是InnoDB的)限制。虽然InnoDB支持长度不超过4GB,但是MySQL限制了默认所有column(不包括TEXT和BLOB,因为不和数据记录存储在一起)占用空间不能超过65535

文件大小限制

  • InnoDB所有日志文件加在一起不能超过512GB
  • 表空间(tableSpace)最小微微大于10MB,最大由innodb_page_size决定:

|InnoDB Page Size| Maximum Tablespace Size |--|--| |4KB| 16TB |8KB| 32TB |16KB| 64TB |32KB| 128TB |64KB| 256TB

发布于 2019-08-27

文章被以下专栏收录