Spec-Zone .ru
спецификации, руководства, описания, API
|
|
Spec-Zone .ru
спецификации, руководства, описания, API
|
InnoDB TablesInnoDBInnoDB
Concepts and ArchitectureInnoDB Performance Tuning and TroubleshootingInnoDB
Features for Flexibility, Ease of Use andReliabilityInnoDB
Startup Options and System VariablesInnoDB TablesInnoDB is a general-purpose storage engine that balances high reliability and high
performance. In MySQL 5.6, issuing the CREATE
TABLE statement with no ENGINE= clause creates an InnoDB table.
Key advantages of InnoDB tables include:
Its DML operations follow the ACID model, with transactions featuring commit, rollback, and crash-recovery capabilities to protect user data.
Row-level locking and Oracle-style consistent reads increase multi-user concurrency and performance.
InnoDB tables arrange your data on disk to optimize
queries based on primary
keys.
To maintain data integrity, InnoDB also supports FOREIGN
KEY constraints. Inserts, updates, and deletes are all checked to ensure they do not result
in inconsistencies across different tables.
You can freely mix InnoDB tables with tables from
other MySQL storage engines, even within the same statement. For example, you can use a join
operation to combine data from InnoDB and MEMORY tables in a single query.
The latest InnoDB offers significant new features over
MySQL 5.1 and earlier. These features focus on performance and scalability, reliability, flexibility,
and usability:
: add or drop indexes without copying the data.
Data compression: shrink tables, to significantly reduce storage and I/O.
More
efficient storage for large column values: fully off-page storage of long BLOB, TEXT, and VARCHAR columns.
Barracuda file format: enables new features while protecting upward and downward compatibility
INFORMATION_SCHEMA tables: information about compression and
locking
Performance and scalability enhancements: includes features such as multiple background I/O threads, multiple buffer pools, and group commit.
Other changes: for flexibility, ease of use and reliability.
Table 14.2. InnoDB Storage EngineFeatures
| Storage limits | 64TB | Transactions | Yes | Locking granularity | Row |
| MVCC | Yes | Geospatial data type support | Yes | Geospatial indexing support | No |
| B-tree indexes | Yes | T-tree indexes | No | Hash indexes | No[a] |
| Full-text search indexes | Yes[b] | Clustered indexes | Yes | Data caches | Yes |
| Index caches | Yes | Compressed data | Yes[c] | Encrypted data[d] | Yes |
| Cluster database support | No | Replication support[e] | Yes | Foreign key support | Yes |
| Backup / point-in-time recovery[f] | Yes | Query cache support | Yes | Update statistics for data dictionary | Yes |
|
[a] InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature. [b] InnoDB support for FULLTEXT indexes is available in MySQL 5.6.4 and higher. [c] Compressed InnoDB tables require the InnoDB Barracuda file format. [d] Implemented in the server (via encryption functions), rather than in the storage engine. [e] Implemented in the server, rather than in the storage engine. [f] Implemented in the server, rather than in the storage engine. |
|||||
InnoDB has been designed for maximum performance when processing large data volumes.
Its CPU efficiency is probably not matched by any other disk-based relational database engine.
The InnoDB storage engine maintains its own buffer pool
for caching data and indexes in main memory. By default, with the innodb_file_per_table setting enabled, each new InnoDB table and its associated indexes are stored in a separate file. When the
innodb_file_per_table
option is disabled, InnoDB stores all its tables and indexes in the single system tablespace, which may
consist of several files (or raw disk partitions). InnoDB tables can handle large
quantities of data, even on operating systems where file size is limited to 2GB.
InnoDB is published under the same GNU GPL License Version 2 (of June 1991) as
MySQL. For more information on MySQL licensing, see http://www.mysql.com/company/legal/licensing/.
For InnoDB-related terms and definitions, see MySQL Glossary.
A forum dedicated to the InnoDB storage engine is
available at .