关于java:mysql中innodb创建表的一些限制

44次阅读

共计 4916 个字符，预计需要花费 13 分钟才能阅读完成。

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 决定：
InnoDB Page Size Maximum Tablespace Size
4KB 16TB
8KB 32TB
16KB 64TB
32KB 128TB
64KB 256TB

参考：

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

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

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

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

正文完
java
发表至： java
2022-05-11
 0
Spring-MVC
关于java:25W-字详解线程与锁了面试随便问
关于java:Jenkins-Pipeline-动态使用分支名触类旁通的玩法值得拥有
拒绝等通知我还会一致性哈希算法