Rechercher dans le manuel MySQL
15.6.1.6 Limits on InnoDB Tables
Limits on InnoDB
tables are described under the
following topics in this section:
Before using NFS with InnoDB
, review
potential issues outlined in Using NFS with MySQL.
Maximums and Minimums
A table can contain a maximum of 1017 columns. Virtual generated columns are included in this limit.
A table can contain a maximum of 64 secondary indexes.
The index key prefix length limit is 3072 bytes for
InnoDB
tables that useDYNAMIC
orCOMPRESSED
row format.The index key prefix length limit is 767 bytes for
InnoDB
tables that useREDUNDANT
orCOMPACT
row format. For example, you might hit this limit with a column prefix index of more than 191 characters on aTEXT
orVARCHAR
column, assuming autf8mb4
character set and the maximum of 4 bytes for each character.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 theinnodb_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.A maximum of 16 columns is permitted for multicolumn indexes. Exceeding the limit returns an error.
The maximum row length, except for variable-length columns (
VARBINARY
,VARCHAR
,BLOB
andTEXT
), is slightly less than half of a page for 4KB, 8KB, 16KB, and 32KB page sizes. For example, the maximum row length for the defaultinnodb_page_size
of 16KB is about 8000 bytes. However, for anInnoDB
page size of 64KB, the maximum row length is approximately 16000 bytes.LONGBLOB
andLONGTEXT
columns must be less than 4GB, and the total row length, includingBLOB
andTEXT
columns, 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.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:- ERROR 1118 (42000): Row size too large. The maximum row size for the
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.The combined size of the
InnoDB
log files can be up to 512GB.The minimum tablespace size is slightly larger than 10MB. The maximum tablespace size depends on the
InnoDB
page size.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, theMAX_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.The default page size in
InnoDB
is 16KB. You can increase or decrease the page size by configuring theinnodb_page_size
option when creating the MySQL instance.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. Forinnodb_page_size=32KB
, extent size is 2MB. Forinnodb_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.
ANALYZE TABLE
determines index cardinality (as displayed in theCardinality
column ofSHOW 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 ofANALYZE TABLE
could produce different numbers. This makesANALYZE TABLE
fast onInnoDB
tables but not 100% accurate because it does not take all rows into account.You can make the statistics collected by
ANALYZE TABLE
more precise and more stable by turning on theinnodb_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 runANALYZE TABLE
after major changes to indexed column data, because the statistics are not recalculated periodically (such as after a server restart).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 theinnodb_stats_transient_sample_pages
system variable instead.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 thatANALYZE TABLE
does not produce values good enough for your particular tables, you can useFORCE 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”.If statements or transactions are running on a table, and
ANALYZE TABLE
is run on the same table followed by a secondANALYZE TABLE
operation, the secondANALYZE TABLE
operation is blocked until the statements or transactions are completed. This behavior occurs becauseANALYZE TABLE
marks the currently loaded table definition as obsolete whenANALYZE TABLE
is finished running. New statements or transactions (including a secondANALYZE 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.SHOW TABLE STATUS
does not give accurate statistics onInnoDB
tables except for the physical size reserved by the table. The row count is only a rough estimate used in SQL optimization.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.For information about how
InnoDB
processesSELECT COUNT(*)
statements, refer to theCOUNT()
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.An
AUTO_INCREMENT
columnai_col
must be defined as part of an index such that it is possible to perform the equivalent of an indexedSELECT 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.ai_col
)InnoDB
sets an exclusive lock on the end of the index associated with theAUTO_INCREMENT
column while initializing a previously specifiedAUTO_INCREMENT
column on a table.With
innodb_autoinc_lock_mode=0
,InnoDB
uses a specialAUTO-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 theAUTO-INC
table lock is held. The same behavior occurs for “bulk inserts” withinnodb_autoinc_lock_mode=1
. Table-levelAUTO-INC
locks are not used withinnodb_autoinc_lock_mode=2
. For more information, See Section 15.6.1.4, “AUTO_INCREMENT Handling in InnoDB”.When an
AUTO_INCREMENT
integer column runs out of values, a subsequentINSERT
operation returns a duplicate-key error. This is general MySQL behavior.DELETE FROM
does not regenerate the table but instead deletes all rows, one by one.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 (includingDB_ROW_ID
,DB_TRX_ID
,DB_ROLL_PTR
, andDB_MIX_ID
). This restriction applies to use of the names in any letter case.
LOCK TABLES
acquires two locks on each table ifinnodb_table_locks=1
(the default). In addition to a table lock on the MySQL layer, it also acquires anInnoDB
table lock. Versions of MySQL before 4.1.2 did not acquireInnoDB
table locks; the old behavior can be selected by settinginnodb_table_locks=0
. If noInnoDB
table lock is acquired,LOCK TABLES
completes even if some records of the tables are being locked by other transactions.In MySQL 8.0,
innodb_table_locks=0
has no effect for tables locked explicitly withLOCK TABLES ... WRITE
. It does have an effect for tables locked for read or write byLOCK TABLES ... WRITE
implicitly (for example, through triggers) or byLOCK 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 invokeLOCK TABLES
onInnoDB
tables inautocommit=1
mode because the acquiredInnoDB
table locks would be released immediately.You cannot lock additional tables in the middle of a transaction because
LOCK TABLES
performs an implicitCOMMIT
andUNLOCK TABLES
.For limits associated with concurrent read-write transactions, see Section 15.6.6, “Undo Logs”.
Nederlandse vertaling
U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.
Bij voorbaat dank.
Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-innodb-restrictions.html
De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.
Referenties
Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.