MySQL :: MySQL 8.4 参考手册 :: 10.4.6 表大小的限制

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 10 优化 / 10.4 优化数据库结构 / 10.4.6 表大小的限制

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

10.4.6 表大小限制10.4.6 Limits on Table Size

MySQL 数据库的有效最大表大小通常由操作系统对文件大小的限制决定，而不是由 MySQL 内部限制决定。有关操作系统文件大小限制的最新信息，请参阅您所使用操作系统的相关文档。The effective maximum table size for MySQL databases is usually determined by operating system constraints on file sizes, not by MySQL internal limits. For up-to-date information operating system file size limits, refer to the documentation specific to your operating system.

Windows 用户请注意，FAT 和 VFAT (FAT32) 不被认为适合 MySQL 的生产使用。请改用 NTFS。Windows users, please note that FAT and VFAT (FAT32) are not considered suitable for production use with MySQL. Use NTFS instead.

如果遇到全表错误，可能有多种原因导致发生：If you encounter a full-table error, there are several reasons why it might have occurred:

磁盘可能已满。The disk might be full.
您正在使用 InnoDB 表，并且 InnoDB 表空间文件中的空间不足。最大表空间大小也是表的最大大小。有关表空间大小限制，请参阅第 17.21 节“InnoDB 限制”。You are using InnoDB tables and have run out of room in an InnoDB tablespace file. The maximum tablespace size is also the maximum size for a table. For tablespace size limits, see Section 17.21, “InnoDB Limits”.
一般来说，对于大小大于 1TB 的表，建议将表分区为多个表空间文件。Generally, partitioning of tables into multiple tablespace files is recommended for tables larger than 1TB in size.
您已达到操作系统文件大小限制。例如，您在仅支持最大 2GB 文件大小的操作系统上使用 MyISAM 表，并且您的数据文件或索引文件已达到此限制。You have hit an operating system file size limit. For example, you are using MyISAM tables on an operating system that supports files only up to 2GB in size and you have hit this limit for the data file or index file.
您正在使用 MyISAM 表，并且该表所需的空间超出了内部指针大小所允许的空间。 MyISAM 默认情况下允许数据和索引文件增长到 256TB，但可以将此限制更改为最大允许大小 65,536TB（256⁷ - 1 字节）。You are using a MyISAM table and the space required for the table exceeds what is permitted by the internal pointer size. MyISAM permits data and index files to grow up to 256TB by default, but this limit can be changed up to the maximum permissible size of 65,536TB (256⁷ − 1 bytes).
如果您需要大于默认限制的 MyISAM 表，并且您的操作系统支持大文件，则 CREATE TABLE 语句支持 AVG_ROW_LENGTH 和 MAX_ROWS 选项。请参见第 15.1.20 节“CREATE TABLE 语句”。服务器使用这些选项来确定允许的表大小。If you need a MyISAM table that is larger than the default limit and your operating system supports large files, the CREATE TABLE statement supports AVG_ROW_LENGTH and MAX_ROWS options. See Section 15.1.20, “CREATE TABLE Statement”. The server uses these options to determine how large a table to permit.
如果指针大小对于现有表来说太小，您可以使用 ALTER TABLE 更改选项，以增加表允许的最大大小。请参阅第 15.1.9 节“ALTER TABLE 语句”。If the pointer size is too small for an existing table, you can change the options with ALTER TABLE to increase a table's maximum permissible size. See Section 15.1.9, “ALTER TABLE Statement”.
```
ALTER TABLE tbl_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn;
```
You have to specify AVG_ROW_LENGTH only for tables with BLOB或者TEXT columns; in this case, MySQL cannot optimize the space required based only on the number of rows.You have to specify AVG_ROW_LENGTH only for tables with BLOB or TEXT columns; in this case, MySQL cannot optimize the space required based only on the number of rows.
更改MyISAM表，设置myisam_data_pointer_size，它设置用于内部行指针的字节数。如果未指定MAX_ROWS选项，该值用于设置新表的指针大小。myisam_data_pointer_size的值可以从 2 到 7。例如，对于使用动态存储格式的表，值为 4 允许表达到 4GB；值为 6 允许表达到 256TB。使用固定存储格式的表具有更大的最大数据长度。有关存储格式特性，请参见。即使有.To change the default size limit for MyISAM tables, set the myisam_data_pointer_size, which sets the number of bytes used for internal row pointers. The value is used to set the pointer size for new tables if you do not specify the MAX_ROWS option. The value of myisam_data_pointer_size can be from 2 to 7. For example, for tables that use the dynamic storage format, a value of 4 permits tables up to 4GB; a value of 6 permits tables up to 256TB. Tables that use the fixed storage format have a larger maximum data length. For storage format characteristics, see Section 18.2.3, “MyISAM Table Storage Formats”.
您可以使用以下语句检查最大数据和索引大小：You can check the maximum data and index sizes by using this statement:
```
SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';
```
您还可以使用myisamchk -dv /path/to/table-index-file。请参阅第 15.7.7 节“SHOW 语句”，或第 6.6.4 节“myisamchk — MyISAM 表维护工具” 。You also can use myisamchk -dv /path/to/table-index-file. See Section 15.7.7, “SHOW Statements”, or Section 6.6.4, “myisamchk — MyISAM Table-Maintenance Utility”.
解决 MyISAM 表文件大小限制的其他方法如下：Other ways to work around file-size limits for MyISAM tables are as follows:
- 如果您的大型表是只读的，您可以使用 myisampack 对其进行压缩。 myisampack 通常会将表压缩至少 50%，因此实际上您可以拥有更大的表。 myisampack 还可以将多个表合并为一个表。请参阅第 6.6.6 节“myisampack — 生成压缩的只读 MyISAM 表”。If your large table is read only, you can use myisampack to compress it. myisampack usually compresses a table by at least 50%, so you can have, in effect, much bigger tables. myisampack also can merge multiple tables into a single table. See Section 6.6.6, “myisampack — Generate Compressed, Read-Only MyISAM Tables”.
- MySQL 包含一个 MERGE 库，使您能够处理具有与单个 MERGE 表相同结构的一组 MyISAM 表。请参阅第 18.7 节“MERGE 存储引擎”。MySQL includes a MERGE library that enables you to handle a collection of MyISAM tables that have identical structure as a single MERGE table. See Section 18.7, “The MERGE Storage Engine”.
您正在使用 MEMORY (HEAP) 存储引擎；在这种情况下，您需要增加 max_heap_table_size 系统变量的值。请参阅第 7.1.8 节“服务器系统变量”。You are using the MEMORY (HEAP) storage engine; in this case you need to increase the value of the max_heap_table_size system variable. See Section 7.1.8, “Server System Variables”.

首页上一级下一节