转载请注明转载自:忆雨林枫

InnoDB表的限制

InnoDB表的限制
  • ANALYZE TABLE决定索引基数(显示在SHOW INDEX输出的 Cardinality列),通过执行在每个索引树执行random dives,并相应的更新索引基数的估值。由于这些只是估计值,所以重复执行ANALYZE TABLE会产生不同的数值。这使得ANALYZE TABLE可以快速的在InnoDB上执行,但是不是100%的准确。因为它没有考虑到所有的行。
    • 您可以通过打innodb_stats_persistent配置选项,您可以使ANALYZE TABLE收集的统计信息更加精确和稳定。当启用了改设置时,当索引列的数据有大的变更后,重新运行ANALYZE TABLE是重要的,因此,统计信息会不定期的重新计算(如,重启服务器之后)。
    • 如果启用了持久统计设置,您可以通过修改innodb_stats_persistent_sample_pages系统变量,来改变随机取样的数量,如果这个设置被禁用了,那么可以修改 innodb_stats_transient_sample_pages系统变量来代替。
    • MySQL在连接优化中使用索引基数估值。如果一个连接不是以正确的方式进行优化,请尝试 ANALYZE TABLE。在少数情况下, ANALYZE TABLE不会产生足够好的值,那么您可以在您的查询语句中使用FORCE INDEX来强制使用一个特殊的索引。或者设置max_seeks_for_key系统变量来确保MySQL执行索引查找优先与表扫描。
  • 如果一个表上正在运行语句或事务,并对表进行ANALYZE TABLE操作,然后又对表执行一个 ANALYZE TABLE操作,那么第二个 ANALYZE TABLE操作将被阻塞,知道语句或事务完成。这个情况的出现,是因为 ANALYZE TABLE完成时,将当前加载的表定义标记为已过时,那么新语句或事务(包括第二个 ANALYZE TABLE语句)必须加载新的表定义到表缓存中,而此时,就需要等到当前正在运行的语句或事务已完成,且旧的表定义被清理了,才能继续进行。不支持加载并发加载多个表定义。
  • 除了表中保留的物理大小之外,SHOW TABLE STATUS不会给出InnoDB表准确的统计信息。行计数仅是在SQL优化中粗略的估计。
  • InnoDB 内部不会保存表中行的数量,因此并发事务在同一时间可能 “看到” 不同的行数。因此,SELECT COUNT(*)语句只能统计当前事务看得到的行。
    • InnoDB 执行 SELECT COUNT(*) 语句是通过扫描聚集索引。
    • 如果所以记录不是完全都在缓冲池中,那么处理SELECT COUNT(*)语句就需要花费一些时间。为了更快的计数。您可以创建一个计数表,让应用程序根据插入和删除来更新它,但是,如果数千个并发事务对同一个计数表进行更新的话,那么这种方法就不具备伸缩性了。如果一个近似值就足够了,那么可以使用 SHOW TABLE STATUS
    • InnoDB 处理 SELECT COUNT(*) 和 SELECT COUNT(1)操作是用相同的方式,他们之间没有性能差异。
  • 在 Windows上,InnoDB内部通常使用小写保存库名和表名。所以使用二进制格式将数据库从Unix上移动到Windows上,或者从Windows上到Unix上,所有的库名和表名都要使用小写。
  • 必须将AUTO_INCREMENTai_col定义为索引的一部分,以至于 SELECT MAX(ai_col)可以通过表上的索引查找, 以获得列的最大值。通常,这是通过将列设置为某个索引的第一列来实现的。
  • 当初始化指定AUTO_INCREMENT列的表时,InnoDBAUTO_INCREMENT列相关联的索引末尾设置排它锁。
    • innodb_autoinc_lock_mode=0时, InnoDB使用一个特殊的AUTO-INC表锁模式,在访问自增计数器时,获得锁,并将其保存在当前SQL语句的末尾,当AUTO-INC表锁被持有时,其他客户端就不能对表进行插入。 当innodb_autoinc_lock_mode=1时,进行“批量插入”也会出现这个情形。 表级锁AUTO-INC锁定不与innodb_autoinc_lock_mode=2一起使用。
  • 当一个AUTO_INCREMENT整数列的值用完了,随后的INSERT操作返回一个重复键错误,这通常是MySQL做的,类似于 MyISAM工作方式。
  • DELETE FROM tbl_name不会重新生成表,而是一行一行的,删除所有行。
  • 级联的外键行为不会激活触发器。
  • 您创建的表中不能有与InnoDB内部列同名的列 (包括 DB_ROW_IDDB_TRX_IDDB_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'
InnoDB中的最大值和最小值
  • 一个表最多可以有1017列。虚拟生成的列也在这个限制内。
  • 一个表最多可以含有64个辅助索引。
  • 使用DYNAMICCOMPRESSED行格式的InnoDB表的一个索引前缀的长度的限制是3072字节。使用REDUNDANTCOMPACT行格式的InnoDB表的一个索引前缀的长度的限制是767字节。 举个例子。您可能碰到过这样的限制, 在TEXT 或 VARCHAR列上, 索引列前缀超过了191字符, 假设字符集是utf8mb4,那么每个字符最大4个字节。
    • 试图超过限制长度的索引键前缀,会返回一个错误。
    • 这个限制不仅适用于索引键前缀,还适用于全列索引键。
  • 如果您将在创建MySQL实例时,使用innodb_page_size选项,将 InnoDB的 page size缩小到8KB 或 4KB 。那么索引键的最大长度会成比例的缩小。3072字节的限制是一句16KB的页,也就是说,页是8KB时,索引键的最大长度是1536字节。页是4KB时,是768字节。
  • 一个复合索引最多16个列。超过了这个限制返回一个错误。
    ERROR 1070 (42000): Too many key parts specified; max 16 parts allowed
    
  • 除了可变长列 (VARBINARYVARCHARBLOB和 TEXT),行的最大长度要略小于页大小的一半。举个例子,如果innodb_page_size是默认的16k,那么行的最大长度就是约8000字节,对于一个InnoDB页的大小是64KB,那么行的最大长度约16000字节。LONGBLOB 和 LONGTEXT列必须小于4GB,并且行的总长度,包括 BLOB 和 TEXT列,必须小于4GB。
    • 如果一行的长度小于页大小的一半,那么就可以全部存储在这个页内,如果超过了页的一半,可变长度的列会选择下一个页来存储,直到行是在页的一半。
  • 尽管InnoDB支持在内部大于65,535个字节的行,但是MySQL本身的行的限制为65,535,这是所有列的组合大小:
    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
  • 在某些老的操作系统上,文件的大小必须小于2G,这并不是InnoDB本身的限制,但是如果您需要一个大的表空间,可以将其配置为使用多个小的数据文件,而不是一个大的数据文件。
  • InnoDB日志文件的总大小可以高达512GB
  • 最小表空间大小略大于10MB。表空间最大大小取决于InnoDB的页大小。

    表 15.6 InnoDB表空间最大大小

    InnoDB 页大小 最大表空间大小
    4KB 16TB
    8KB 32TB
    16KB 64TB
    32KB 128TB
    64KB 256TB


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

  • InnoDB页的默认大小是16KB,在创建MySQL实例时,可以配置选项 innodb_page_size来增加或减小页的大小。支持32KB 和 64KB 的页,但是对于大于16KB的页不支持 ROW_FORMAT=COMPRESSED。对于32KB和64KB的页,记录的大小最大是16KB。 对于innodb_page_size=32k, 一个区(extent)的大小是2MB。对于 innodb_page_size=64k, 一个区(extent)的大小是4MB。使用特定大小的InnoDB页的MySQL实例,不能使用来自不同大小页的实例的数据文件或日志文件。