MySQL 单表大小

ゝ一世哀愁。 2022-05-25 05:15 182阅读 0赞

# 背景 #

## 存储引擎 ##

MySQL 存储引擎包括：MyISAM（缺省）、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDBCluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等。

其中InnoDB和BDB提供事务安全表，其他存储引擎都是非事务安全表。
    最常用的两种存储引擎是MyISAM、InnoDB.
    当 create 新表时，未指定新表的存储引擎时，默认使用MyISAM。每个MyISAM在磁盘上存储成三个文件。文件名都和表名相同，扩展名分别是.frm（存储表定义）、.MYD(MYData，存储数据)、.MYI(MYIndex，存储索引)。数据文件和索引文件可以放置在不同的目录，平均分布io，获得更快的速度。
    InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比 MyISAM，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

# MySQL 单表是否有大小限制？ #

MySQL 3.22中，MySQL 的存储引擎是 ISAM ，单表最大限为 4 GB。  
MySQL 3.23起，MySQL 的存储引擎是 MyISAM，单表最大限为 64 PB（67108864 GB）。

1 PB = 1024 TB
    1 TB = 1024 GB
    此时，单表最大限已经不再由 MySQL 限制了，改为电脑容量限制了。

MySQL 4.0起，MySQL 的存储引擎支持 InnoDB，Innodb存储数据的策略是分为两种：

1.  共享表空间存储方式  
    Innodb的所有数据保存在一个单独的表空间里面，而这个表空间可以由很多个文件组成，一个表可以跨多个文件存在，所以其大小限制不再是文件大小的限制，而是其自身的限制。官方指出 Innodb 表空间的最大限制为 64 TB。
2.  独享表空间存储方式  
    每个表的数据以一个单独的文件来存放，此时的单表限制，就变成文件系统的大小限制了。

以下是从收集到的一点信息，不一定全部准确：  
操作系统 大小限制  
win32 w/ FAT/FAT32 2GB/4GB  
win32 w/ NTFS 2TB（可能更大）  
Linux 2.2-Intel 32-bit 2GB (LFS: 4GB)  
Linux 2.4+ 4TB(ext3)  
Solaris 9/10 16TB  
NetWare w/NSS filesystem 8TB  
MacOS X w/ HFS+ 2TB

以下是MySQL文档中的内容：  
Windows用户请注意： FAT和VFAT (FAT32)不适合MySQL的生产使用。应使用NTFS。

## 列个数上限 ##

MySQL 5.7 硬性限制每个表有最多可以有 4096 个列。但列极限的有效最大值可能更少，取决于几个因素:

1.  行容量上限，所有列容量加在一起不能超过该值；
2.  列的数据类型：某些数据类型的存储需求取决于存储引擎、存储格式和字符集等因素；
3.  存储引擎：如 InnoDB 每表最多只能有 1017 个列；
4.  .frm 文件：每个表都有一个包含表定义的.frm文件。定义将影响该文件的内容，其方式可能会影响表中允许的列数；

## 行容量上限 ##

[行容量上限][Link 1]由下面几个因素决定：

1.  MySQL 规定，即使存储引擎能支持更大的容量，行容量上限仍为 65535 个字节（64 KB）。BLOB 和 TEXT 列除外，因为它们的存储与行其他内容是分开存储的；
2.  InnoDB 表的行容量上限（仅适用于用数据库页面做的本地存储）略小于 innodb\_page\_size 设置大小（如4KB、8KB、16KB和32KB）的一半；
3.  存储引擎：不同的存储引擎使用不同数量的页头和挂载数据，这将影响到行的存储容量；

## 数据类型 ##

[官方 5.7 版本数据类型的存储要求][5.7]：

虽然不同存储引擎下的数据类型，以不同的方式存储原始数据。但用于通信和交换表行信息的 MySQL APIs 内部 MySQL api使用了一致的数据结构，适用于所有存储引擎。

表数据可能被压缩，无论是对一个列还是整个行，使计算表或列的存储需求变得更加复杂。

### InnoDB 表存储要求 ###

参考官方 [InnoDB表物理行结构][InnoDB]。

### NDB 表存储要求 ###

NDB 表使用 4 字节对齐；所有的 NDB 数据存储都以 4 个字节的倍数进行。

比如，在NDB表中，TINYINT、SMALLINT、MEDIUMINT和INTEGER (INT) 列类型每个记录都需要 4 个字节的存储，这是由于对齐因素造成的。BIT（M）以 4 字节的倍数进行存储（不管 M 是否 4 的倍数）。

NDB 引擎下每个表都需要一个主键，如果没有显式定义主键，NDB 会自动创建一个隐藏的主键，其大小占用 31~35 个字节。

While a NULL itself does not require any storage space, NDB reserves 4 bytes per row if the table definition contains any columns defined as NULL, up to 32 NULL columns. (If an NDB Cluster table is defined with more than 32 NULL columns up to 64 NULL columns, then 8 bytes per row are reserved.)
    虽然 NULL 不占用存储空间，但 NDB 还是会给定义为 NULL 的一列 4 个字节，最多 32 个空列。（如果一个 NDB 集群表定义 32~64个 NULL 列，那每列占 8 个字节。）
    上面一段话个人翻译总觉得哪里有问题，怪怪的……

也可以使用官方给出的 [Perl脚本][Perl] 来预测 NDB 表的存储要求。它会连接到当前 MySQL（非 NDB 集群）数据库，并且创建一个 report ，说明用 NDB 存储引擎的表需要占用多少空间。

### 数字存储要求 ###

<table> 
 <thead> 
  <tr> 
   <th>数据类型</th> 
   <th align="center">存储要求</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>TINYINT</td> 
   <td align="center">1 byte</td> 
  </tr> 
  <tr> 
   <td>SMALLINT</td> 
   <td align="center">2 bytes</td> 
  </tr> 
  <tr> 
   <td>MEDIUMINT</td> 
   <td align="center">3 bytes</td> 
  </tr> 
  <tr> 
   <td>INT, INTEGER</td> 
   <td align="center">4 bytes</td> 
  </tr> 
  <tr> 
   <td>BIGINT</td> 
   <td align="center">8 bytes</td> 
  </tr> 
  <tr> 
   <td>FLOAT(p)</td> 
   <td align="center">4 bytes(0&lt;=p&lt;=24) 或 8bytes(25&lt;=p&lt;=53)</td> 
  </tr> 
  <tr> 
   <td>FLOAT</td> 
   <td align="center">4 bytes</td> 
  </tr> 
  <tr> 
   <td>DOUBLE [PRECISION], REAL</td> 
   <td align="center">8 bytes</td> 
  </tr> 
  <tr> 
   <td>DECIMAL(M,D), NUMERIC(M,D)</td> 
   <td align="center">Varies; see following discussion</td> 
  </tr> 
  <tr> 
   <td>BIT(M)</td> 
   <td align="center">approximately (M+7)/8 bytes</td> 
  </tr> 
 </tbody> 
</table>

DECIMAL(M, D)：M-整数位数，范围1~65，D-小数位数，范围1~30. MySQL 要求 D <= M.
    MySQL 中允许使用 DECIMAL(M)，相当于 DECIMAL(M, 0)。也允许使用 DECIMAL 相当于DECIMAL(10, 0)。

### 日期、时间存储要求 ###

### 字符串存储要求 ###

### 特殊类型存储要求 ###

### JSON存储要求 ###

[Link 1]: https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html#row-size-limits
[5.7]: https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html
[InnoDB]: https://dev.mysql.com/doc/refman/5.7/en/innodb-physical-record.html
[Perl]: https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-programs-ndb-size-pl.html