This section describes restrictions and limitations of the 'InnoDB' storage engine.
You cannot create a table with a column name that matches the name of an internal 'InnoDB' column (including 'DB_ROW_ID', 'DB_TRX_ID', and 'DB_ROLL_PTR'. This restriction applies to use of the names in any lettercase.
mysql> CREATE TABLE t1 (c1 INT, db_row_id INT) ENGINE=INNODB;
ERROR 1166 (42000): Incorrect column name 'db_row_id'*note 'SHOW TABLE STATUS': show-table-status. does not provide accurate statistics for 'InnoDB' 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' processes 'SELECT COUNT()' statements, refer to the 'COUNT()' description in note aggregate-functions::.
'ROW_FORMAT=COMPRESSED' is unsupported for page sizes greater than 16KB.
A MySQL instance using a particular 'InnoDB' page size ('innodb_page_size') cannot use data files or log files from an instance that uses a different page size.
For limitations associated with importing tables using the Transportable Tablespaces feature, see *note Table Import Limitations: innodb-statistics-estimation.
For limitations associated with online DDL, see *note innodb-online-ddl-limitations::.
For limitations associated with general tablespaces, see *note general-tablespaces-limitations::.
For limitations associated with data-at-rest encryption, see *note innodb-data-encryption-limitations::.
File: manual.info.tmp, Node: storage-engines, Next: replication, Prev: innodb-storage-engine, Up: Top
15 Alternative Storage Engines ******************************
Menu:
pluggable-storage-overview:: Overview of MySQL Storage Engine Architecture
Storage engines are MySQL components that handle the SQL operations for different table types. note 'InnoDB': innodb-storage-engine. is the default and most general-purpose storage engine, and Oracle recommends using it for tables except for specialized use cases. (The note 'CREATE TABLE': create-table. statement in MySQL 5.7 creates 'InnoDB' tables by default.)
MySQL Server uses a pluggable storage engine architecture that enables storage engines to be loaded into and unloaded from a running MySQL server.
To determine which storage engines your server supports, use the *note 'SHOW ENGINES': show-engines. statement. The value in the 'Support' column indicates whether an engine can be used. A value of 'YES', 'NO', or 'DEFAULT' indicates that an engine is available, not available, or available and currently set as the default storage engine.
mysql> SHOW ENGINES\G
*************************** 1. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 2. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 3. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
...This chapter covers use cases for special-purpose MySQL storage engines. It does not cover the default note 'InnoDB': innodb-storage-engine. storage engine or the note 'NDB': mysql-cluster. storage engine which are covered in note innodb-storage-engine::, and note mysql-cluster::. For advanced users, this chapter also contains a description of the pluggable storage engine architecture (see *note pluggable-storage-overview::).
For information about features offered in commercial MySQL Server binaries, see 'MySQL Editions' (https://www.mysql.com/products/), on the MySQL website. The storage engines available might depend on which edition of MySQL you are using.
For answers to commonly asked questions about MySQL storage engines, see *note faqs-storage-engines::.
MySQL 5.7 Supported Storage Engines
note 'InnoDB': innodb-storage-engine.: The default storage engine in MySQL 5.7. 'InnoDB' is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. 'InnoDB' row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance. 'InnoDB' stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, 'InnoDB' also supports 'FOREIGN KEY' referential-integrity constraints. For more information about 'InnoDB', see note innodb-storage-engine::.
*note 'MyISAM': myisam-storage-engine.: These tables have a small footprint. Table-level locking limits the performance in read/write workloads, so it is often used in read-only or read-mostly workloads in Web and data warehousing configurations.
*note 'Memory': memory-storage-engine.: Stores all data in RAM, for fast access in environments that require quick lookups of non-critical data. This engine was formerly known as the 'HEAP' engine. Its use cases are decreasing; 'InnoDB' with its buffer pool memory area provides a general-purpose and durable way to keep most or all data in memory, and 'NDBCLUSTER' provides fast key-value lookups for huge distributed data sets.
*note 'CSV': csv-storage-engine.: Its tables are really text files with comma-separated values. CSV tables let you import or dump data in CSV format, to exchange data with scripts and applications that read and write that same format. Because CSV tables are not indexed, you typically keep the data in 'InnoDB' tables during normal operation, and only use CSV tables during the import or export stage.
*note 'Archive': archive-storage-engine.: These compact, unindexed tables are intended for storing and retrieving large amounts of seldom-referenced historical, archived, or security audit information.
*note 'Blackhole': blackhole-storage-engine.: The Blackhole storage engine accepts but does not store data, similar to the Unix '/dev/null' device. Queries always return an empty set. These tables can be used in replication configurations where DML statements are sent to replica servers, but the source server does not keep its own copy of the data.
note 'NDB': mysql-cluster. (also known as note 'NDBCLUSTER': mysql-cluster.): This clustered database engine is particularly suited for applications that require the highest possible degree of uptime and availability.
*note 'Merge': merge-storage-engine.: Enables a MySQL DBA or developer to logically group a series of identical 'MyISAM' tables and reference them as one object. Good for VLDB environments such as data warehousing.
*note 'Federated': federated-storage-engine.: Offers the ability to link separate MySQL servers to create one logical database from many physical servers. Very good for distributed or data mart environments.
*note 'Example': example-storage-engine.: This engine serves as an example in the MySQL source code that illustrates how to begin writing new storage engines. It is primarily of interest to developers. The storage engine is a 'stub' that does nothing. You can create tables with this engine, but no data can be stored in them or retrieved from them.
You are not restricted to using the same storage engine for an entire server or schema. You can specify the storage engine for any table. For example, an application might use mostly 'InnoDB' tables, with one 'CSV' table for exporting data to a spreadsheet and a few 'MEMORY' tables for temporary workspaces.
Choosing a Storage Engine
The various storage engines provided with MySQL are designed with different use cases in mind. The following table provides an overview of some storage engines provided with MySQL, with clarifying notes following the table.
Storage Engines Feature Summary
Feature MyISAM Memory InnoDB Archive NDB
B-tree indexes
Yes Yes Yes No No
Backup/point-in-time recovery (note 1)
Yes Yes Yes Yes Yes
Cluster database support
No No No No Yes
Clustered indexes
No No Yes No No
Compressed data
Yes No Yes Yes No (note
2)
Data caches
No N/A Yes No Yes
Encrypted data
Yes Yes (note Yes (note Yes (note Yes (note (note 3) 4) 3) 5) 3)
Foreign key support
No No Yes No Yes
Full-text search indexes
Yes No Yes (note No No 6)
Geospatial data type support
Yes No Yes Yes Yes
Geospatial indexing support
Yes No Yes (note No No 7)
Hash indexes
No Yes No (note No Yes 8)
Index caches
Yes N/A Yes No Yes
Locking granularity
Table Table Row Row Row
MVCC
No No Yes No No
Replication support (note 1)
Yes Limited Yes Yes Yes (note 9)
Storage limits
256TB RAM 64TB None 384EB
T-tree indexes
No No No No Yes
Transactions
No No Yes No Yes
Update statistics for data dictionary
Yes Yes Yes Yes Yes
Notes: 1. Implemented in the server, rather than in the storage engine. 2. Compressed MyISAM tables are supported only when using the compressed row format. Tables using the compressed row format with MyISAM are read only. 3. Implemented in the server via encryption functions. 4. Implemented in the server via encryption functions; In MySQL 5.7 and later, data-at-rest encryption is supported. 5. Implemented in the server via encryption functions; encrypted NDB backups as of NDB 8.0.22; transparent NDB file system encryption supported in NDB 8.0.29 and later. 6. Support for FULLTEXT indexes is available in MySQL 5.6 and later. 7. Support for geospatial indexing is available in MySQL 5.7 and later. 8. InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature. 9. See the discussion later in this section.
File: manual.info.tmp, Node: storage-engine-setting, Next: myisam-storage-engine, Prev: storage-engines, Up: storage-engines