Restrictions on InnoDB Tables

cocolala

• A table cannot contain more than 1000 columns.
• The internal maximum key length is 3500 bytes, but MySQL itself restricts this to 1024 bytes.
• The maximum row length, except for VARCHAR, BLOB and TEXT columns, is slightly less than half of a database page. That is, the maximum row length is about 8000 bytes. LONGBLOB and LONGTEXT columns must be less than 4GB, and the total row length, including also BLOB and TEXT columns, must be less than 4GB. InnoDB stores the first 768 bytes of a VARCHAR, BLOB, or TEXT column in the row, and the rest into separate pages.
• On some older operating systems, data files must be less than 2GB.
ext2以上就不存在此问题了,不用考虑这个限制
• The combined size of the InnoDB log files must be less than 4GB.
• The minimum tablespace size is 10MB. The maximum tablespace size is four billion database pages (64TB). This is also the maximum size for a table.
• InnoDB tables do not support FULLTEXT indexes.
• InnoDB tables do not support spatial column types.
• ANALYZE TABLE counts cardinality by doing 10 random dives to each of the index trees and updating index cardinality estimates accordingly. Note that because these are only estimates, repeated runs of ANALYZE TABLE may produce different numbers. This makes ANALYZE TABLE fast on InnoDB tables but not 100% accurate as it doesn't take all rows into account. MySQL uses index cardinality estimates only in join optimization. If some join is not optimized in the right way, you may try using ANALYZE TABLE. In the few cases that ANALYZE TABLE doesn't produce values good enough for your particular tables, you can use FORCE INDEX with your queries to force the usage of a particular index, or set max_seeks_for_key to ensure
that MySQL prefers index lookups over table scans. See Section 5.3.3, “Server System Variables”. See Section A.6, “Optimizer-Related Issues”.

• On Windows, InnoDB always stores database and table names internally in lowercase. To move databases in binary format from Unix to Windows or from Windows to Unix, you should have all database and table names in lowercase.

• Warning: Do not convert MySQL system tables in the mysql database from MyISAM to InnoDB tables! This is an unsupported operation. If you do this, MySQL does not restart until you restore the old system tables from a backup or re-generate them with the mysql_install_db script.
• InnoDB does not keep an internal count of rows in a table. (This would actually be somewhat complicated because of multi-versioning.) To process a SELECT COUNT(*) FROM t statement, InnoDB must scan an index of the table, which takes some time if the index is not entirely in the buffer pool. To get a fast count, you have to use a counter table you create yourself and let your application update it according to the inserts and deletes it does. If your table does not change often, using the MySQL query cache is a good solution. SHOW TABLE STATUS also can be used if an approximate row count is sufficient. See Section 15.12, “InnoDB Performance
Tuning Tips”.
• For an AUTO_INCREMENT column, you must always define an index for the table, and that index must contain just the AUTO_INCREMENT column. In MyISAM tables, the
AUTO_INCREMENT column may be part of a multi-column index.

  这里的说法有误,修正的说法见后面的翻译部分
• InnoDB does not support the AUTO_INCREMENT table option for setting the initial sequence value in a CREATE TABLE or ALTER TABLE statement. To set the value with InnoDB, insert a dummy row with a value one less and delete that dummy row, or insert the first row with an explicit value specified.

  这里的说法有误,修正的说法见后面的翻译部分
• When you restart the MySQL server, InnoDB may reuse an old value for an
AUTO_INCREMENT column (that is, a value that was assigned to an old transaction that was rolled back).

  至今未遇到过:-)
• When an AUTO_INCREMENT column runs out of values, InnoDB wraps a BIGINT to -
9223372036854775808 and BIGINT UNSIGNED to 1. However, BIGINT values have 64
bits, so do note that if you were to insert one million rows per second, it would still take nearly three hundred thousand years before BIGINT reached its upper bound. With all other integer type columns, a duplicate-key error results. This is similar to how MyISAM works, because it is mostly general MySQL behavior and not about any storage engine in particular.

  基本不用考虑,没有谁用bigint unsigned 做序列 :-)
• DELETE FROM tbl_name does not regenerate the table but instead deletes all rows, one by one.
• TRUNCATE tbl_name is mapped to DELETE FROM tbl_name for InnoDB and doesn't
reset the AUTO_INCREMENT counter.
   
  这个说法也不对,实际情况见后面部分
• 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.
• Before MySQL 4.0.14 or 4.1.0, if you tried to create a unique index on a prefix of a column you got an error: CREATE TABLE T (A CHAR(20), B INT, UNIQUE (A(5))) TYPE = InnoDB; If you created a non-unique index on a prefix of a column, InnoDB created an index over the whole column. These restrictions were removed in MySQL 4.0.14.

  现在可以在前缀上建索引了
• Before MySQL 4.0.20 or 4.1.2, the MySQL LOCK TABLES operation does not know about
InnoDB row-level locks set by completed SQL statements. This means that you can get a table lock on a table even if there still exist transactions by other users who have row level locks on the same table. Thus your operations on the table may have to wait if they collide with these locks of other users. Also a deadlock is possible. However, this does not endanger transaction integrity, because the row level locks set by InnoDB always take care of the integrity. Also, a table
lock prevents other transactions from acquiring more row level locks (in a conflicting lock mode) on the table.
• Beginning with MySQL 4.0.20 and 4.1.2, the MySQL LOCK TABLES operation acquires two locks on each table if innodb_table_locks=1. (1 is the default.) In addition to a table lock on the MySQL layer, it also acquires an InnoDB table lock. Older versions of MySQL do not acquire InnoDB table locks. Beginning with MySQL 4.0.22 and 4.1.7, 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.
• 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 on InnoDB tables in AUTOCOMMIT= 1 mode, because the acquired InnoDB table locks would be released immediately.
• Sometimes it would be useful to lock further tables in the course of a transaction. Unfortunately, LOCK TABLES in MySQL performs an implicit COMMIT and UNLOCK TABLES. An InnoDB variant of LOCK TABLES has been planned that can be executed in the middle of a transaction.
• Before MySQL 3.23.52, replication always ran with autocommit enabled. Therefore consistent reads in the slave would also see partially processed transactions, and thus the read would not be really consistent in the slave. This restriction was removed in MySQL 3.23.52.
• The LOAD TABLE FROM MASTER statement for setting up replication slave servers does not yet work for InnoDB tables. A workaround is to alter the table to MyISAM on the master, do then the load, and after that alter the master table back to InnoDB.
• The default database page size in InnoDB is 16KB. By recompiling the code, you can set it to values ranging from 8KB to 64KB. You have to update the values of UNIV_PAGE_SIZE and UNIV_PAGE_SIZE_SHIFT in the univ.i source file.
• Currently, triggers are not activated by cascaded foreign key actions.


. 最大1000列

. Innodb内部最大key length是3500bytes, Mysql限制为1024bytes
   
. 没有varchar,blob,text列时,最大行长度为8k(pase size是16k);
Longblob, longtext最大4GB
包括blob/text列在内的最大行长度4GB
Innodb存储varchar/blob/text列的前768个字节(在一个page), 剩余的放到分开的pages中

. 在某些os中,数据文件尺寸必须小于2G

. 所有log files总尺寸小于4G

. 最小表空间为10M, 最大表空间40亿个pages(64TB),同时也是表的最大尺寸

. 不支持fulltext index

. 不支持spatial column types

. anylyze table通过10次随机探测(dive)索引树来计算索引的cardinality,并相应更新它;
这样做不能保证100%准确,但执行很快; 由于这个特性,每次执行anylyze table的结果都会不同
Mysql 只在join 优化时使用索引cardinality, 如果join不能很好的优化,可以使用anylyze table来优化,在极少数情况下,anylyze table可能不能产生足够好的数据. 这时可以使用force index来强制使用适合的索引, 或者可以设置max_seeks_for_key来强制index lookups

. 在windows中,Innodb使用小写存储db和table name; 在与unix转化时注意这一点
. 不要转换mysql系统库中表到innodb,否则不能正常启动,除非从备份中恢复或者通过mysql_install_db重新创建

. Innodb没有在表中存储内部计数器(可能是因为multi-versioning)
所以,count(*)必须扫描索引,如果索引没有全部在buffer pool,会比较费时
建议做一个计数器表,insert/delete时更新该表.
如果表变更很少,使用Query cache
而show table status能获得表记录数(不准确)

. Innodb中,必须为auto_increment列创建一个索引,可以是多列索引,但auto列必须是第一个列; 如果没有定义pk,则选取包括auto列的unique key作为pk; 如果没有定义pk及uk,则内部在行标识符上创建一个pk


. Innodb中truncate table会reset auto_increment计数器
指定的auto_increment=xxx 表选项,truncate后,选项丢失
. show table status中的表统计并不精确
. 可以创建non-unique的前缀索引
. 重启mysqld时,Innodb可能会重新新使用rollback造成的gap中的auto_increment值
  (至今未发现这种情况)

. 由于innodb中,事务结束后,lock会被释放,所以当设置autocommit=1时,调用lock tables没有实际意义. 因为获得的innodb table lock会马上释放掉

. lock tables执行一个隐含的commit和unblock操作

. Innodb中不支持用LOAD TABLE FROM MASTER设置slave
建议的方法: 先将master上的表转换成myisam,执行load…后转换成innodb

. Innodb的缺省db page size(相当于oracle中的db_block_size)是16k, 可以修改univ.i中的UNIV_PAGE_SIZE,UNIV_PAGE_SIZE_SHIFT 来增大或减小 (8k-64k)
root# grep -ir "UNIV_PAGE_SIZE" * |grep -i "univ.i"
Docs/mysql.info:     `UNIV_PAGE_SIZE_SHIFT' in the `univ.i' source file.
innobase/include/univ.i:#define UNIV_PAGE_SIZE          (2 * 8192) /* NOTE! Currently, this has to be a
innobase/include/univ.i:/* The 2-logarithm of UNIV_PAGE_SIZE: */
innobase/include/univ.i:#define UNIV_PAGE_SIZE_SHIFT    14
innobase/include/univ.i:#define UNIV_EXTERN_STORAGE_FIELD (UNIV_SQL_NULL - UNIV_PAGE_SIZE)

. cascade foreign key不会激活trigger



本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u2/67276/showart_585699.html