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 usedtable 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 BLOBsCREATE 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_tableinnodb_file_format = Barracuda

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

        ALTER TABLE $TABLEENGINE=InnoDBROW_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决定：
InnoDB Page SizeMaximum Tablespace Size
4KB16TB
8KB32TB
16KB64TB
32KB128TB
64KB256TB

参考：

https://dev.mysql.com/doc/ref...

https://dev.mysql.com/doc/ref...

https://www.cnblogs.com/zhiqi...

本文由博客一文多发平台 OpenWrite 公布！