[转帖]mysql中innodb创建表的一些限制

mysql,innodb,创建,一些,限制 · 浏览次数 : 0

小编点评

**DB 数据行格式限制** InnoDB 支持最大数据行大小为 65535 字节,而 MySQL 的默认数据行大小是 65535 字节。这意味着,**MySQL 限制了默认所有数据行(不包括 TEXT 和 BLOB,因为不和数据记录存储在一起)占用空间不能超过 65535 个存储引擎使用不同的页头和尾部数据**。 **解决方法:** * 将字段修改为 TEXT 或 BLOB。 * 将变长字段设置是否合理。 * 将变长字段过长建议更改为 blob 或 text垂直表字段拆分或大字段合并。 * 优化表结构。 * 修改表的存储引擎。

正文

https://www.cnblogs.com/jackssybin/p/16258953.html

 

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 SizeMaximum Tablespace Size
4KB 16TB
8KB 32TB
16KB 64TB
32KB 128TB
64KB 256TB

参考:

https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_page_size

https://www.cnblogs.com/zhiqian-ali/p/5037317.html

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

与[转帖]mysql中innodb创建表的一些限制相似的内容:

[转帖]mysql中innodb创建表的一些限制

https://www.cnblogs.com/jackssybin/p/16258953.html 1、 背景 在新创建mysql数据表的时候。不太确定表能创建多少个字段,多少个索引。索引多少有限制么?mysql的数据是怎么存储的存在在哪里。 2、基本个数限制 在MySQL5.6.9以后的版本,一

【转帖】MySQL InnoDB存储原理深入剖析与技术分析

一、MySQL记录存储: MySQL InnoDB的数据由B+树来组织,数据记录存储在B+树数据页(page)中,每个数据页16kb,数据页 包括页头、虚记录、记录堆、自由空间链表、未分配空间、slot区、页尾七部分组成。 所有的非终端结点可以看成是索引部分,结点中仅含有其子树根结点中最大(或最小)

[转帖]MySQL提升笔记(4)InnoDB存储结构

https://cdn.modb.pro/u/310923 这一节本来计划开始索引的学习,但是在InnoDB存储引擎的索引里,存在一些数据存储结构的概念,这一节先了解一下InnodDB的逻辑存储结构,为索引的学习打好基础。 从InnoDB存储引擎的存储结构看,所有数据都被逻辑地放在一个空间中,称之为

【转帖】mysql一个索引块有多少指针_深刻理解MySQL系列之索引

索引 查找一条数据的过程 先看下InnoDB的逻辑存储结构:node 表空间:能够看作是InnoDB存储引擎逻辑结构的最高层,全部的数据都存放在表空间中。默认有个共享表空间ibdata1。若是启用innodb_file_per_table参数,须要注意每张表的表空间内存放的只是数据、索引和插入缓冲B

[转帖]深入理解mysql-第五章 InnoDB记录存储结构-页结构

前言: 页是InnoDB管理存储空间的基本单位,上一章我们主要分析了页中的主要的构成行的存储结构-行格式,其中简单提了一下页的概念。这章我们详细讲解一下页的存储结构。 一、数据页结构 前边我们简单提了一下页的概念,它是InnoDB管理存储空间的基本单位,一个页的大小一般是16KB。和存储一条条数据的

[转帖]MySQL pid 和 socket 文件说明

2021-10-13 11:595110转载MySQL 1 pid-file文件 MySQL 中的 pid 文件记录的是当前 mysqld 进程的 pid ,pid 亦即 Process ID 。可以通过 pid-file 参数来配置 pid 文件路径及文件名,如果未指定此变量,则 pid 文件默认

【转帖】Mysql一张表可以存储多少数据

https://www.cnblogs.com/wenbochang/p/16723537.html Mysql一张表可以存储多少数据 在操作系统中,我们知道为了跟磁盘交互,内存也是分页的,一页大小4KB。同样的在MySQL中为了提高吞吐率,数据也是分页的,不过MySQL的数据页大小是16KB。(确

[转帖]MySQL 慢查询日志深入理解

https://www.jb51.net/article/210312.htm + 目录 什么是慢查询日志 MySQL的慢查询日志是 MySQL提供的一种日志记录,它用来记录在 MySQL 中响应时间超过阀值的语句,具体指运行时间超过long_query_time 值的 SQL,则会被记录到慢查询日

[转帖] mysql的timestamp会存在时区问题?

我感觉 这样理解也有点不对 timestamp 应该是不带时区 只是 UTC1970-1-1 的时间戳 但是展示时会根据时区做一下计算 date time 就不会做转换而已. 原创:打码日记(微信公众号ID:codelogs),欢迎分享,转载请保留出处。 简介# 众所周知,mysql中有两个时间类型

[转帖]Mysql向表中循环插入数据

如何查看MySQL的当前存储引擎 看你的mysql现在已提供什么存储引擎: mysql> show engines; 看你的mysql当前默认的存储引擎: mysql> show variables like '%storage_engine%'; 创建表 create table per2 (id