mysql中innodb创建表的一些限制

1、 背景

在新创建mysql数据表的时候。不太确定表能创建多少个字段，多少个索引。索引多少有限制么？mysql的数据是怎么存储的存在在哪里。

2、基本个数限制

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

3、索引字段大小限制

• 关于innodb_large_prefix这个配置的限制： show variables like ‘innodb_large_prefix’ ; – on
  • 对于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）

4、innodb_page_size

• 关于innodb_page_size这个数据库实例初始化配置： show VARIABLES like ‘innodb_page_size’ ; – 16384
  • 如果在创建数据库实例的时候修改了innodb_page_size这个参数（默认16KB），那么字段前缀的限制大小是3072 bytes这个限制也会改变。3072bytes对应16KB的innodb_page_size，1563bytes对应8KB，767对应4KB
  • 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在不理想的情况下就变成了双向链表。

这也是为什么要限制住，每一行的大小，也就是下面要说的行长度与行大小限制

5、行长度与行大小限制

• 行长度（Row Length，就是一个表去掉可变长度的类型字段 (VARBINARY, VARCHAR, BLOB and TEXT)，定长字段的占用空间大小），这个行长度是一行数据至少要占用的长度。上一节提到了这个长度最好不要超过innodb_page_size的一半。对于LONGBLOB还有LONGTEXT字段，长度不能超过4GB，包含所有字段的总长度，不能超过4GB。InnoDB 表数据行（数据库页本地存储的数据）的最大大小略小于 innodb_page_size（4KB、8KB、16KB 以及 32KB）的一半。例如，对于默认的 16KB 页大小配置，数据行的最大大小为略少于 8KB。对于 64KB 数据页，最大的数据行大小略小于 16KB。
  如果一行中的变长字段超过了 InnoDB 数据行大小限制，InnoDB 会使用页外（off-page）存储的方式保存某些变长字段，直到数据行能够满足 InnoDB 数据行大小限制。对于页外存储的变长字段，本地存储的数据内容取决于数据行的格式，详细信息可以参考“InnoDB 数据行格式”。

• 长大小（Row Size，这个是MySQL的限制，不是InnoDB的）限制。虽然InnoDB支持长度不超过4GB，但是MySQL限制了默认所有column（不包括TEXT和BLOB，因为不和数据记录存储在一起）占用空间不能超过65535

• 不同存储引擎使用不同的页头和尾部数据，从而会影响到数据行实际可用的存储空间。

• 示例过程：

  CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
         c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
         f VARCHAR(10000), g VARCHAR(6000)) ENGINE=InnoDB CHARACTER SET latin1;
  ERROR 1118 (42000): Row size too large. The maximum row size for the used
  table type, not counting BLOBs, is 65535. This includes storage overhead,
  check the manual. You have to change some columns to TEXT or BLOBs
  

  InnoDB 表可以创建成功是因为将字段修改为 TEXT 可以避免超过 65535 字节的限制，同时 InnoDB 页外存储可以避免超过 InnoDB 数据行大小的限制。

  CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
         c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
         f VARCHAR(10000), g TEXT(6000)) ENGINE=InnoDB CHARACTER SET latin1;
  Query OK, 0 rows affected (0.02 sec)
  

  变长字段的存储包含了长度信息，这个内容也会被计算到数据行大小中。例如，一个 VARCHAR(255) CHARACTER SET utf8mb3 字段需要使用 2 个字节存储数据的长度，因此每个数值最多可能占用 767 个字节。

  以下语句能够成功创建表 t1，因为它的字段需要 32765 + 2 字节加上 32766 + 2 字节，能够满足 65535 字节的限制：

  CREATE TABLE t1
         (c1 VARCHAR(32765) NOT NULL, c2 VARCHAR(32766) NOT NULL)
         ENGINE = InnoDB CHARACTER SET latin1;
  Query OK, 0 rows affected (0.02 sec)
  

  以下语句创建表 t2 失败，因为虽然字段的长度没有超过 65535 字节的限制，但是增加 2 个记录长度的字节之后超过了该限制.

  改成65535之后就成功了

  CREATE TABLE t2(c1 VARCHAR(65535) NOT NULL)
  ENGINE = InnoDB CHARACTER SET latin1;
  [Err] 1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
  
  CREATE TABLE t2(c1 VARCHAR(65533) NOT NULL)
         ENGINE = InnoDB CHARACTER SET latin1;
  Query OK, 0 rows affected (0.01 sec)
  

  InnoDB 限制行大小（数据库页内存储的本地数据）为略小于数据库页的一半。以下语句失败的原因是全部字段长度超过了一个 InnoDB 页 16 KB 的数据行大小限制。

  CREATE TABLE t4 (
         c1 CHAR(255),c2 CHAR(255),c3 CHAR(255),
         c4 CHAR(255),c5 CHAR(255),c6 CHAR(255),
         c7 CHAR(255),c8 CHAR(255),c9 CHAR(255),
         c10 CHAR(255),c11 CHAR(255),c12 CHAR(255),
         c13 CHAR(255),c14 CHAR(255),c15 CHAR(255),
         c16 CHAR(255),c17 CHAR(255),c18 CHAR(255),
         c19 CHAR(255),c20 CHAR(255),c21 CHAR(255),
         c22 CHAR(255),c23 CHAR(255),c24 CHAR(255),
         c25 CHAR(255),c26 CHAR(255),c27 CHAR(255),
         c28 CHAR(255),c29 CHAR(255),c30 CHAR(255),
         c31 CHAR(255),c32 CHAR(255),c33 CHAR(255)
         ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET latin1;
  ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help.
  In current row format, BLOB prefix of 0 bytes is stored inline.
  

  • 官方给出的解决方式：

    • 优化表结构。变长字段设置是否合理，变长字段过长建议更改为blob或是text

    • 垂直表字段拆分或者大字段合并（大字段最多不超过768，业务进行合并＋拆分），divide your table into small ones. If one table contain more than 10 text colums, and the data contain is a little bit long. this error will be thrown out.

    • 修改表的存储引擎，modify InnoDB to MyISAM.

    • innodb的Barracuda(梭鱼)存储格式。这种格式对blob字段的处理方式是在page里面只存储一个20byte大小的指针，其他完全存在溢出区，所以轻易不会超过8K.修改row_format为COMPRESSED或者DYNAMIC，当然前提需保证innodb_file_format =Barracuda

      如何修改方法：
      1、修改mysql配置文件my.cnf

      innodb_file_per_table
      innodb_file_format = Barracuda
      

      2、修改造成这个问题的表属性

      ALTER TABLE $TABLE
      ENGINE=InnoDB
      ROW_FORMAT=COMPRESSED 
      KEY_BLOCK_SIZE=8;
      

      3、重启mysql服务
      4、通过写sql语句修改mysql环境配置，可以免重启服务。当然，配置文件中也必须配置上面两个参数，这个操作只是避免此次重启mysql服务

      set global innodb_file_per_table =ON;
      set global innodb_file_format = barracuda;
      

6、文件大小限制

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