14.5.7 Limits on InnoDB Tables InnoDB 表的限制

14.5.7 limits on innodb tables innodb 表的限制

警告:

不要转换mysql 系统表 在mysql 数据库里 从myisam表转换为innodb 表。

这是一个不支持的操作,如果你这么做了,mysql 不重启知道你恢复老的system tables 从一个备份

或者重新生成 通过 mysql_install_db program。

不是一个好的注意配置innodb 来使用data files或者log files在nfs卷上。

否则, 这个文件可能被其他进程锁定和变的不可用

最大值和最小值:

1.一个表可以包含最大1017列( 早期版本为1000列,5.6.9以后是1017）

一个表可以包含最多64个 secondary indexes.

3.默认的, 一个index key 用于一个单独的列的索引可以最大到767字节。

同样的长度限制应用于任何index key 前缀。

see section 13.1.13, “create index syntax”.

比如,你可能打破这个限制 索引前缀列的限制大于255个字符在一个text或者varchar列

假设 一个utf-8 字符设置, 3个字节一个字符。

当innodb_large_prefix configuration 配置选项被启用, 这个长度限制是提高到3072,

对于innodb 表使用动态和压缩行格式

尝试使用一个索引前缀长度 大于运行的最大值会产生一个错误。

为了避免这个错误对于复制配置,避免设置innodb_large_prefix 选项在master上

如果它不能在slaves上也设置

mysql> show variables like ‘%innodb_large_prefix%’;

+———————+——-+

| variable_name | value |

| innodb_large_prefix | off |

1 row in set (0.00 sec)

slaves有唯一索引 会受此限制的影响

innodb 内部最大值 key length是3500字节, 但是mysql 它本身限制这个为3072字节。

这个限制适用于组合索引键的长度在一个多列索引里

如果你减少innodb page size从8kb或者4kb 通过制定 innodb_page_size 选项 当创建mysql 实例,

index key 的最大长度是适当的降低的,基于3072字节的限制对于一个16kb的页大小。

那是, 最大index key length 是1536字节当page size 是8kb, 768字节当页是4kb

mysql> show variables like ‘%innodb_page_size%’;

+——————+——-+

| variable_name | value |

| innodb_page_size | 16384 |

16kb

mysql 默认是16kb:

最大的行长度, 除了可变长度列(varbinary, varchar, blob and text),

是略小于数据库页的一半。也就是说,最大的行长度是大约8000字节 对于默认页是16kb的,

如果你降低了page size 通过指定 innodb_page_size 当创建mysql instance，

最大的行长度是4000字节对于8kb页大小 和 2000字节对于4kb页。

longblob and longtext 列必须小于4gb,总的行长度,包括blob和text 列,必须小于4gb。

如果一个行是小于半个page的长度, 所有都是存储在本地的页里。如果 它超过一半,

可变长度的列是选择外部 off-page storage 直到 记录填满一半的页。

虽然 innodb 支持 行的大小大于65535字节内部的, mysql 它本身施加一个row-size 限制为65536

对所有列的组合的大小

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

在一些老的操作系统上,文件必须小于2gb. 这个对于innodb 本身不是限制,

但是你需要一个大的表空间,你需要配置它使用多个小的数据文件 相比一个大的文件

innodb log 文件的组合大小可以到512gb

最小的表空间大小是稍大于10mb,最大的表空间是40亿的数据库页(64tb). 这也是一个表的最大大小。

默认的数据库页是innodb 是16kb,或者你能降低page size 到8kb或者4kb 通过指定 innodb_page_size 选项

当创建mysql 实例的时候。

注意:

增加page的大小是不支持的操作.没有保障 innodb会正常的工作 当page 大小大于16kb时。

编译和运行innodb 可能发生错误。 特别的,row_format=compressed

一个mysql 实例使用一个特别的innodb page size 不能使用数据文件或者log files 从一个使用不同page size的实例。

这个限制会影响 存储和降级操作 在使用mysql 5.6,不支持大于16kb

index types 索引类型:

innodb 表支持 全文索引,从mysql 5.6.4开始 see section 14.2.6.3, “innodb fulltext indexes” for details.

2.innodb 表支持空间的数据类型,但不是索引上

在innodb 表限制:

analyze table 决定index 基数( show index 输出的cardinality列) 通过做随机跳水到每个索引树

和 更新index cardinality 评估。因为那些只是评估,重复运行 analyze table会产生不同的数值。

这使得analyze table 在innodb 上很快但不是100%精准的,因为它不需要扩了所有的行

你可以让统计信息收集通过analyze table 更加精确和稳定通过打开 innodb_stats_persistent 配置选项。

mysql> show variables like ‘%innodb_stats_persistent%’

-> ;

+————————————–+——-+

| variable_name | value |

| innodb_stats_persistent | on |

| innodb_stats_persistent_sample_pages | 20 |

+———————————————–

配置 持久的优化统计参数。 当设置被启用,它是重要的来运行analyze table 在主要的改变对索引列,

因为统计信息没有重新计算

你可以改变 random dives 值 通过修改innodb_stats_persistent_sample_pages系统变量

(如果持续的优化器 统计参数)， 或者 innodb_stats_transient_sample_pages 系统变量

（如果持续的统计设置是启用的)

mysql 使用index cardinality 只评估在连接优化。 如果有些连接是没有正确的方式优化,

你可以尝试使用analyze table. 在少数情况下,analyze table 不产生足够好的值对于特定的表,

你可以使用force index 让你的查询来强制使用一个特定的索引,或者设置 max_seeks_for_key 系统变量

来确保mysql 提升索引查询。

如果表或者事务是运行在表,analyze table 是运行在相同的表更着一个第2个 analyze table操作,

第2个 analyze table 操作会被堵塞 直到语句或者事务完成。

这种行为发生因为 analyze table 标记当前加载的表的定义作为废弃的 当analyze table 是完成运行。

运行语句或者事务(包括一个2次分析表语句)必须 加载新的 表定义到table cache，

不能运行直到当前运行的语句或者事务是被完成和老的表定义被清除。

加载多个并发的表定义是不支持的

show table status 没有给出准确的innodb表的统计信息,除了表的物理大小,行数只用于sql优化粗略的评估。

innodb 不保证内部的数据记录,因为并发访问的事务可能看到不同的记录数 在同一时间。

处理select count(*) from t 语句,innodb 扫描表的索引,这个可能会花费一些时间当index 不是整个在buffer pool里。

如果你的表不经常改变,使用mysql query cache 是一个好的解决方案。

在windows上,innodb 总是存储数据库和表内部按小写,

移动数据库在一个binary format从unix 到windows或者从windows到unix，

创建所有的数据库表使用小写名字。

一个auto_increment 列 ai_col 必须被定义作为一个index的一部分,它是可以执行一个等价的索引的列

select max(ai_col) 寻找表来得到最大值列的值。典型的, 这是通过一些索引的第一列来完成

innodb 设置 一个排它锁在索引的尾部相关的auto_increment 列当初始化一个先前指定的auto_increment列在表上。

当 innodb_autoinc_lock_mode=0, innodb 使用一个单独的 auto-inc 表锁模式

当lock 被获取和持续到当前sql语句的尾部 当访问auto-increment 统计。

另外的客户端不能插入到表 当auto-inc 表lock 是被持有的。

相同的行为发生在 批量插入 使用 innodb_autoinc_lock_mode=1.

table-level auto-inc locks 不使用 innodb_autoinc_lock_mode=2.

mysql> show variables like ‘%innodb_autoinc_lock_mode%’;

+————————–+——-+

| variable_name | value |

| innodb_autoinc_lock_mode | 1 |

当你重启mysql server，innodb 可能重新使用老的值有auto_increment列生成,但是不会存储

(即,一个值是在老的事务被回滚期间生成)

当一个auto_increment 整型列值耗尽,一个后续插入的操作返回一个重复键的错误,

这个是一般的mysql行为,类似于myisam.

delete from tbl_name 不会重新组织表,只是删除所有的记录,一条接一条。

locking and transactions 锁和事务：

lock tables 需要2个锁在每个表 如果innodb_table_locks=1 (默认的).

除了对mysql 层上的表锁, 它也需要一个innodb 表锁。

在mysql 4.12不需要innodb 表锁,老的表现可以设置innodb_table_locks=0.

如果没有innodb 表锁是被需要,lock tables 完成即使表的一些记录被其他事务锁定

在mysql 5.6, innodb_table_locks=0 对表没有影响 显示的使用lock tables write.

它对表locked with read 或者lock tables write 有影响