read/LimitsonInnoDBTables

15.6.1.6 Limits on InnoDB Tables

Limits on InnoDB tables are described under the following topics in this section:

• Maximums and Minimums
• Restrictions on InnoDB Tables
• Locking and Transactions

InnoDB表的限制在以下几个话题中讨论 : 最大和最小, InnoDB表的限制, 锁和事务

Maximums and Minimums

• A table can contain a maximum of 1017 columns. Virtual generated columns are included in this limit.

  表最多可以包含 1017 列, 虚拟创建的列也包含在其中

• A table can contain a maximum of 64 secondary indexes.

  表最多能有 64 个次级索引

• The index key prefix length limit is 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format.

  使用 DYNAMIC 或 COMPRESSED 行存储的 InnoDB 表索引键的前缀宽度被限制在 3072 字节内 (<=)

  The index key prefix length limit is 767 bytes for InnoDB tables that use REDUNDANT or COMPACT row format. For example, you might hit this limit with a column prefixindex of more than 191 characters on a TEXT or VARCHAR column, assuming a utf8mb4 character set and the maximum of 4 bytes for each character.

  使用 REDUNDANT 或 COMPACT 行存储的 InnoDB 表索引间的前缀宽度被限制在 767 字节内 (<=)

  Attempting to use an index key prefix length that exceeds the limit returns an error.

  尝试使用超过限制的字节前缀宽度会返回错误

  The limits that apply to index key prefixes also apply to full-column index keys.

  适用于索引键前缀的限制也适用于全列索引键

• If you reduce the InnoDB page size to 8KB or 4KB by specifying the innodb_page_size option when creating the MySQL instance, the maximum length of the index key is lowered proportionally, based on the limit of 3072 bytes for a 16KB page size. That is, the maximum index key length is 1536 bytes when the page size is 8KB, and 768 bytes when the page size is 4KB.

  如果在创建 MySQL 实例时显式指定 innodb_page_size 减少 InnoDB 的页面大小(8kb -> 4kb)索引键的最大长度也会成比例减少

  16kb 页大小限制在 3072 字节, 8kb 页大小限制在 1536 字节, 而 4kb 页面大小限制在 768 字节

• A maximum of 16 columns is permitted for multicolumn indexes. Exceeding the limit returns an error.

  多列索引子列数不能超过 16 个

  ERROR 1070 (42000): Too many key parts specified; max 16 parts allowed

• The maximum row length, except for variable-length columns (VARBINARY, VARCHAR, BLOB and TEXT), is slightly less than half of a page for 4KB, 8KB, 16KB, and 32KB page sizes. For example, the maximum row length for the default innodb_page_size of 16KB is about 8000 bytes. However, for an InnoDB page size of 64KB, the maximum row length is approximately 16000 bytes. LONGBLOB and LONGTEXT columns must be less than 4GB, and the total row length, including BLOB and TEXTcolumns, must be less than 4GB.

  除变长列(VARBINARY, VATCHAR, BLOB, TEXT)外, 列的长度都稍少于一半页的大小. 比如, 页面大小为 16kb 的行长度最大 8000 字节左右, 然而 InnoDB 64kb 的页最大在 16000 字节左右. LONGBLOB 和 LONGTEXT 列必须少于 4GB, 整个行的长度, 包括 BLOB 和 TEXT 列, 也必须少于 4GB.

  If a row is less than half a page long, all of it is stored locally within the page. If it exceeds half a page, variable-length columns are chosen for external off-page storage until the row fits within half a page, as described in Section 15.11.2, “File Space Management”.

  如果行小于页的一般, 数据则全部保存在页中, 如果超过, 变长列被选择页外存储直到列的大小符合为止

• Although InnoDB supports row sizes larger than 65,535 bytes internally, MySQL itself imposes a row-size limit of 65,535 for the combined size of all columns:

  尽管 InnoDB 支持超过 65535 字节的行, MySQL 自身约束列的组合大小不能超过 65535

  mysql> CREATE TABLE t (a VARCHAR(8000), b VARCHAR(10000),
      -> c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
      -> f VARCHAR(10000), g VARCHAR(10000)) ENGINE=InnoDB;
  ERROR 1118 (42000): Row size too large. The maximum row size for the
  used table type, not counting BLOBs, is 65535. You have to change some
  columns to TEXT or BLOBs

  See Section C.10.4, “Limits on Table Column Count and Row Size”.

• On some older operating systems, files must be less than 2GB. This is not a limitation of InnoDB itself, but if you require a large tablespace, configure it using several smaller data files rather than one large data file.

  在一些较老的操作系统上, 文件必须小于 2GB. 这不是 InnoDB 的限制, 但是如果你需要一个大的表空间, 可以配置成使用些许较小的数据文件, 而不是一个大的数据文件

• The combined size of the InnoDB log files can be up to 512GB.

  InnoDB 日志文件的组合大小最大 512GB

• The minimum tablespace size is slightly larger than 10MB. The maximum tablespace size depends on the InnoDB page size.

  表的最小容量稍大于 10mb, 最大的表大小取决于 InnoDB 页大小

  Table 15.3 InnoDB Maximum Tablespace Size

  InnoDB Page Size	Maximum Tablespace Size
  4KB	16TB
  8KB	32TB
  16KB	64TB
  32KB	128TB
  64KB	256TB

  The maximum tablespace size is also the maximum size for a table.

  最大表空间大小也是最大表大小

• The path of a tablespace file, including the file name, cannot exceed the MAX_PATH limit on Windows. Prior to Windows 10, the MAX_PATH limit is 260 characters. As of Windows 10, version 1607, MAX_PATH limitations are removed from common Win32 file and directory functions, but you must enable the new behavior.

  表空间文件的路径, 包括表名, 在 windows 上不能超过 MAX_PATH 限制, 在 win10 以前 MAX_PATH 限制为 260 字符. win10 1607 版本后移除了该限制

• The default page size in InnoDB is 16KB. You can increase or decrease the page size by configuring the innodb_page_size option when creating the MySQL instance.

  InnoDB 默认页大小为 16kb, 创建 MySQL 实例时可以通过设置 innodb_page_size 更改(PS. 页大小在创建后是属于表的局部常量么?)

  32KB and 64KB page sizes are supported, but ROW_FORMAT=COMPRESSED is unsupported for page sizes greater than 16KB. For both 32KB and 64KB page sizes, the maximum record size is 16KB. For innodb_page_size=32KB, extent size is 2MB. For innodb_page_size=64KB, extent size is 4MB.

  A MySQL instance using a particular InnoDB page size cannot use data files or log files from an instance that uses a different page size.

  32kb 和 64kb 的页也是支持的, 但是行存储为 COMPRESSED 不支持超过 16kb 大小的页. 对于 32kb 和 64kb 大小的也, 最大记录的大小其实是 16kb.

  MySQL 实例不能使用底层页面不一致的数据文件和日志文件

Restrictions on InnoDB Tables

• ANALYZE TABLE determines index cardinality (as displayed in the Cardinality column of SHOW INDEX output) by performing random dives on each of the index trees and updating index cardinality estimates accordingly. Because these are only estimates, repeated runs of ANALYZE TABLE could produce different numbers. This makesANALYZE TABLE fast on InnoDB tables but not 100% accurate because it does not take all rows into account.

  ANALYZE TABLE 语句每个索引树执行随机潜水(PS. ???)更新预估索引的基数(在 SHOW INDEX 的输出中 Cardinality 列显示)

  因为只是估计, 重复执行 ANALYZE TABLE 将会产生不同的值, 这使得 ANALYZE TABLE 在 InnoDB 上运行地很快, 但并不是 100% 正确, 因为并不会估计所有的行

  (PS. ANALYZE TABLE 将会分析表, 其中部分分析结果可以在 SHOW INDEX 中查看. 这里以列的基数为例)

  You can make the statistics collected by ANALYZE TABLE more precise and more stable by turning on the innodb_stats_persistent configuration option, as explained in Section 15.8.10.1, “Configuring Persistent Optimizer Statistics Parameters”. When that setting is enabled, it is important to run ANALYZE TABLE after major changes to indexed column data, because the statistics are not recalculated periodically (such as after a server restart).

  通过启用 innodb_stats_persistent 使 ANALYZE TABLE 的数据统计更加精确, 当启用这个选项后, 需要在对索引列数据进行主要更改后再使用 ANALYZE TABLE, 因为统计信息不是定期重新计算的

  If the persistent statistics setting is enabled, you can change the number of random dives by modifying the innodb_stats_persistent_sample_pages system variable. If the persistent statistics setting is disabled, modify the innodb_stats_transient_sample_pages system variable instead.

  如果启用了持续性统计, 可以更改 innodb_stats_persistent_sample_pages 系统变量, 如果持久性统计设置取消, 则可以通过 innodb_stats_transient_sampke_pages 更改

  MySQL uses index cardinality estimates in join optimization. If a join is not optimized in the right way, try using ANALYZE TABLE. In the few cases that ANALYZE TABLEdoes not produce values good enough for your particular tables, you can use FORCE INDEX with your queries to force the use of a particular index, or set themax_seeks_for_key system variable to ensure that MySQL prefers index lookups over table scans. See Section B.4.5, “Optimizer-Related Issues”.

  MySQL 在 Join 优化中使用索引基数估计, 如果 Join 不能正确优化, 尝试使用 ANALYZE_TABLE. 在少数情况下 ANALYZE_TABLE 不能为指定的表产生足够好的值, 可以使用 FORCE INDEX 配合查询, 强制使用指定的索引或者设置 max_seeks_for_key 系统变量确保 MySQL 更偏向于索引查找而不是表扫描

• If statements or transactions are running on a table, and ANALYZE TABLE is run on the same table followed by a second ANALYZE TABLE operation, the second ANALYZE TABLE operation is blocked until the statements or transactions are completed. This behavior occurs because ANALYZE TABLE marks the currently loaded table definition as obsolete when ANALYZE TABLE is finished running. New statements or transactions (including a second ANALYZE TABLE statement) must load the new table definition into the table cache, which cannot occur until currently running statements or transactions are completed and the old table definition is purged. Loading multiple concurrent table definitions is not supported.

  如果语句和事务正在运行于表上, ANALYZE TABLE 同样运行于这张表上, 之后继续执行 ANALYZE_TABLE 操作, 第二个 ANALYZE TABLE 操作将会阻塞直到之前的事务/语句执行完成. 会发生这样的情况是因为 ANALYZE_TABLE 在执行完成时会将当前加载的表标记为已过时, 新的语句/事务(包括第二个 ANALYZE_TABLE 语句)必须加载新的表到表缓存中, 改操作在当前语句/事务已经完成, 并且旧表的定义已经被清理后才能发生, 不支持加载多个并发表

• SHOW TABLE STATUS does not give accurate statistics on InnoDB tables except for the physical size reserved by the table. The row count is only a rough estimate used in SQL optimization.

  SHOW TABLE STATUS 不提供 InnoDB 表除表存储的物理大小外, 其他统计信息的精确性, 行数量只是一个 SQL 优化中粗略的估计

• InnoDB does not keep an internal count of rows in a table because concurrent transactions might “see” different numbers of rows at the same time. Consequently, SELECT COUNT(*) statements only count rows visible to the current transaction.

  InnoDB 不保存表中行的内部计数, 因为并发事务可能在同样的时间见到不同的值.

  因此, SELECT COUNT(*) 语句只计数对当前事务可见的行

  For information about how InnoDB processes SELECT COUNT(*) statements, refer to the COUNT() description in Section 12.20.1, “Aggregate (GROUP BY) Function Descriptions”.

• On Windows, InnoDB always stores database and table names internally in lowercase. To move databases in a binary format from Unix to Windows or from Windows to Unix, create all databases and tables using lowercase names.

  在 Windows 上, InnoDB 总是以小写存储数据库和表的名字(大小写不敏感 = =)

• An AUTO_INCREMENT column ai_col must be defined as part of an index such that it is possible to perform the equivalent of an indexed SELECT MAX(*ai_col*) lookup on the table to obtain the maximum column value. Typically, this is achieved by making the column the first column of some table index.

  AUTO_INCREMENT 列 ai_col 必须被定义成索引的一部分, 这样就可以执行已被索引的 SELECT MAX(ai_col) 相等性判断, 获取最大的列值. 通常都是公国将列成为列的首列来获得的

• InnoDB sets an exclusive lock on the end of the index associated with the AUTO_INCREMENT column while initializing a previously specified AUTO_INCREMENT column on a table.

  InnoDB 在和 AUTO_INCREMENT 列相关的索引末尾设置互斥锁

  With innodb_autoinc_lock_mode=0, InnoDB uses a special AUTO-INC table lock mode where the lock is obtained and held to the end of the current SQL statement while accessing the auto-increment counter. Other clients cannot insert into the table while the AUTO-INC table lock is held. The same behavior occurs for “bulk inserts”with innodb_autoinc_lock_mode=1. Table-level AUTO-INC locks are not used with innodb_autoinc_lock_mode=2. For more information, See Section 15.6.1.4, “AUTO_INCREMENT Handling in InnoDB”.

  innodb_automic_lock_mod 设置为 0 时, InnoDB 使用特殊的 AUTO_INC 表锁模式. 在访问自增计数器时锁被持有直到当前 SQL 语句结束

• When an AUTO_INCREMENT integer column runs out of values, a subsequent INSERT operation returns a duplicate-key error. This is general MySQL behavior.

  当 AUTO_INCREMENT 越界, 插入操作会返回 重复的键 错误, 这是通常 MySQL 的行为

• DELETE FROM *tbl_name* does not regenerate the table but instead deletes all rows, one by one.

  DELETE FROM tbl_name 不再生表, 而是依次删除所有行

• Cascaded foreign key actions do not activate triggers.

  级联外键操作不激活触发器

• You cannot create a table with a column name that matches the name of an internal InnoDB column (including DB_ROW_ID, DB_TRX_ID, DB_ROLL_PTR, and DB_MIX_ID). This restriction applies to use of the names in any letter case.

  你不能创建和内部 InnoDB 列重名的列, 包括(DB_ROW_ID, DB_TRX_ID, DB_ROLL_PTR, DB_MIX_ID)

  mysql> CREATE TABLE t1 (c1 INT, db_row_id INT) ENGINE=INNODB;
  ERROR 1166 (42000): Incorrect column name 'db_row_id'

Locking and Transactions

• LOCK TABLES acquires two locks on each table if innodb_table_locks=1 (the default). In addition to a table lock on the MySQL layer, it also acquires an InnoDB table lock. Versions of MySQL before 4.1.2 did not acquire InnoDB table locks; the old behavior can be selected by setting innodb_table_locks=0. If no InnoDB table lock is acquired, LOCK TABLES completes even if some records of the tables are being locked by other transactions.

  如果 innodb_table_locks = 1 则 LOCK TABLES 在每个表上获取两个锁, 除了在 MySQL 层上的锁, 还获取 InnoDB 表锁, 如果没有获取 InnoDB 表锁, 即使一些记录被其他事务锁定, LOCK TABLES 也会完成

  In MySQL 8.0, innodb_table_locks=0 has no effect for tables locked explicitly with LOCK TABLES ... WRITE. It does have an effect for tables locked for read or write by LOCK TABLES ... WRITE implicitly (for example, through triggers) or by LOCK TABLES ... READ.

• All InnoDB locks held by a transaction are released when the transaction is committed or aborted. Thus, it does not make much sense to invoke LOCK TABLES onInnoDB tables in autocommit=1 mode because the acquired InnoDB table locks would be released immediately.

  通过事务获取的所有 InnoDB 锁在事务提交或退出时释放, 因此, 在 autocommit = 1 模式下没有那么必要在 InnoDB 表上显式调用 LOCK TABLES 语句, 因为获取的 InnoDB 表锁会立即释放

• You cannot lock additional tables in the middle of a transaction because LOCK TABLES performs an implicit COMMIT and UNLOCK TABLES.

  你不能在事务中锁额外的表, 因为 LOCK TABLES 执行隐式的 COMMIT 和 UNLOCK TABLES

• For limits associated with concurrent read-write transactions, see Section 15.6.6, “Undo Logs”.