Do not convert MySQL system tables in the
mysql database from
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 regenerate them
by reinitializing the data directory (see
Section 2.10.1, “Initializing the Data Directory”).
It is not a good idea to configure
use data files or log files on NFS volumes. Otherwise, the files
might be locked by other processes and become unavailable for
use by MySQL.
A table can contain a maximum of 1017 columns (raised in MySQL 5.6.9 from the earlier limit of 1000). Virtual generated columns are included in this limit.
A table can contain a maximum of 64 secondary indexes.
innodb_large_prefixis enabled (the default in MySQL 5.7.7), the index key prefix limit is 3072 bytes for
InnoDBtables that use
COMPRESSEDrow format. If
innodb_large_prefixis disabled, the index key prefix limit is 767 bytes for tables of any row format.
innodb_large_prefixis deprecated in MySQL 5.7.7 and will be removed in a future release.
innodb_large_prefixwas introduced in MySQL 5.5 to disable large index key prefixes for compatibility with earlier versions of
InnoDBthat do not support large index key prefixes.
The index key prefix length limit is 767 bytes for
InnoDBtables that use the
COMPACTrow format. For example, you might hit this limit with a column prefix index of more than 255 characters on a
VARCHARcolumn, assuming a
utf8mb3character set and the maximum of 3 bytes for each character.
Attempting to use an index key prefix length that exceeds the limit returns an error. To avoid such errors in replication configurations, avoid enabling
innodb_large_prefixon the master if it cannot also be enabled on slaves.
The limits that apply to index key prefixes also apply to full-column index keys.
If you reduce the
InnoDBpage size to 8KB or 4KB by specifying the
innodb_page_sizeoption 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.
A maximum of 16 columns is permitted for multicolumn indexes. Exceeding the limit returns an error.
ERROR 1070 (42000): Too many key parts specified; max 16 parts allowed
The maximum row length, except for variable-length columns (
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_sizeof 16KB is about 8000 bytes. For an
InnoDBpage size of 64KB, the maximum row length is about 16000 bytes.
LONGTEXTcolumns must be less than 4GB, and the total row length, including
TEXTcolumns, must be less than 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.12.2, “File Space Management”.
InnoDBsupports 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:
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
On some older operating systems, files must be less than 2GB. This is not a limitation of
InnoDBitself, but if you require a large tablespace, configure it using several smaller data files rather than one large data file.
The combined size of the
InnoDBlog files can be up to 512GB.
The minimum tablespace size is slightly larger than 10MB. The maximum tablespace size depends on the
Table 15.6 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 default page size in
InnoDBis 16KB. You can increase or decrease the page size by configuring the
innodb_page_sizeoption when creating the MySQL instance.
Prior to MySQL 5.7.6, increasing the page size is not a supported operation, and there is no guarantee that
InnoDBfunctions normally with a page size greater than 16KB. Problems compiling or running
InnoDBmay occur. In particular,
ROW_FORMAT=COMPRESSEDin the Barracuda file format assumes that the page size is at most 16KB and uses 14-bit pointers.
As of MySQL 5.7.6, 32KB and 64KB page sizes are supported, but
ROW_FORMAT=COMPRESSEDis unsupported for page sizes greater than 16KB. For both 32KB and 64KB page sizes, the maximum record size is 16KB. For
innodb_page_size=32k, extent size is 2MB. For
innodb_page_size=64k, extent size is 4MB.
A MySQL instance using a particular
InnoDBpage size cannot use data files or log files from an instance that uses a different page size.
ANALYZE TABLEdetermines index cardinality (as displayed in the
SHOW INDEXoutput) by doing random dives to each of the index trees and updating index cardinality estimates accordingly. Because these are only estimates, repeated runs of
ANALYZE TABLEcould produce different numbers. This makes
ANALYZE TABLEfast on
InnoDBtables but not 100% accurate because it does not take all rows into account.
You can make the statistics collected by
ANALYZE TABLEmore precise and more stable by turning on the
innodb_stats_persistentconfiguration option, as explained in Section 184.108.40.206, “Configuring Persistent Optimizer Statistics Parameters”. When that setting is enabled, it is important to run
ANALYZE TABLEafter major changes to indexed column data, because the statistics are not recalculated periodically (such as after a server restart) as they traditionally have been.
You can change the number of random dives by modifying the
innodb_stats_persistent_sample_pagessystem variable (if the persistent statistics setting is turned on), or the
innodb_stats_transient_sample_pagessystem variable (if the persistent statistics setting is turned off).
MySQL uses index cardinality estimates only in join optimization. If some join is not optimized in the right way, you can try using
ANALYZE TABLE. In the few cases that
ANALYZE TABLEdoes not produce values good enough for your particular tables, you can use
FORCE INDEXwith your queries to force the use of a particular index, or set the
max_seeks_for_keysystem variable to ensure that MySQL prefers index lookups over table scans. See Section 6.1.5, “Server System Variables”, and Section B.5.5, “Optimizer-Related Issues”.
If statements or transactions are running on a table and
ANALYZE TABLEis run on the same table followed by a second
ANALYZE TABLEoperation, the second
ANALYZE TABLEoperation is blocked until the statements or transactions are completed. This behavior occurs because
ANALYZE TABLEmarks the currently loaded table definition as obsolete when
ANALYZE TABLEis finished running. New statements or transactions (including a second
ANALYZE TABLEstatement) 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.
SHOW TABLE STATUSdoes not give accurate statistics on
InnoDBtables, except for the physical size reserved by the table. The row count is only a rough estimate used in SQL optimization.
InnoDBdoes 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(*) FROM tstatements only count rows visible to the current transaction.
To process a
SELECT COUNT(*) FROM tstatement,
InnoDBscans the clustered index, which takes some time if clustered index records are not entirely in the buffer pool. For a faster count, you can create a counter table and let your application update it according to the inserts and deletes it does. However, this method may not scale well in situations where thousands of concurrent transactions are initiating updates to the same counter table. If an approximate row count is sufficient,
SHOW TABLE STATUScan be used.
As of MySQL 5.7.2,
SELECT COUNT(*) FROM tstatements invoke a single handler call to the storage engine to scan the clustered index and return the row count to the optimizer. This optimization is evident in
SELECT COUNT(*) FROM t, which reports
Select tables optimized away:
EXPLAIN SELECT COUNT(*) FROM t1\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: NULL partitions: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL filtered: NULL Extra: Select tables optimized away
In previous releases, a row count was typically performed by traversing a smaller secondary index and invoking a handler call for each record. A single handler call to the storage engine to count rows in the clustered index generally improves
SELECT COUNT(*) FROM tperformance. However, in the case of a large clustered index and a significantly smaller secondary index, performance degradation is possible compared to performance using the previous, non-optimized implementation.
InnoDBalways 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.
ai_colmust be defined as part of an index such that it is possible to perform the equivalent of an indexed
SELECT MAX(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.
InnoDBsets an exclusive lock on the end of the index associated with the
AUTO_INCREMENTcolumn while initializing a previously specified
AUTO_INCREMENTcolumn on a table.
InnoDBuses a special
AUTO-INCtable 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-INCtable lock is held. The same behavior occurs for “bulk inserts” with
AUTO-INClocks are not used with
innodb_autoinc_lock_mode=2. For more information, See Section 15.8.6, “AUTO_INCREMENT Handling in InnoDB”.
When you restart the MySQL server,
InnoDBmay reuse an old value that was generated for an
AUTO_INCREMENTcolumn but never stored (that is, a value that was generated during an old transaction that was rolled back).
AUTO_INCREMENTinteger column runs out of values, a subsequent
INSERToperation returns a duplicate-key error. This is general MySQL behavior, similar to how
DELETE FROMdoes not regenerate the table but instead deletes all rows, one by one.
Cascaded foreign key actions do not activate triggers.
You cannot create a table with a column name that matches the name of an internal
DB_MIX_ID). This restriction applies to use of the names in any letter case.
CREATE TABLE t1 (c1 INT, db_row_id INT) ENGINE=INNODB;ERROR 1166 (42000): Incorrect column name 'db_row_id'
LOCK TABLESacquires 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
InnoDBtable lock. Versions of MySQL before 4.1.2 did not acquire
InnoDBtable locks; the old behavior can be selected by setting
innodb_table_locks=0. If no
InnoDBtable lock is acquired,
LOCK TABLEScompletes even if some records of the tables are being locked by other transactions.
In MySQL 5.7,
innodb_table_locks=0has 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 ... WRITEimplicitly (for example, through triggers) or by
LOCK TABLES ... READ.
InnoDBlocks held by a transaction are released when the transaction is committed or aborted. Thus, it does not make much sense to invoke
autocommit=1mode because the acquired
InnoDBtable locks would be released immediately.
The limit on data-modifying transactions is now 96 * 1023 concurrent transactions that generate undo records. As of MySQL 5.7.2, 32 of 128 rollback segments are assigned to non-redo logs for transactions that modify temporary tables and related objects. This reduces the maximum number of concurrent data-modifying transactions from 128K to 96K. The 96K limit assumes that transactions do not modify temporary tables. If all data-modifying transactions also modify temporary tables, the limit is 32K concurrent transactions.