출처 : https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html
MySQL 5.7 Supported Storage Engines
InnoDB: The default storage engine in MySQL 5.7.InnoDBis a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data.InnoDBrow-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance.InnoDBstores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity,InnoDBalso supportsFOREIGN KEYreferential-integrity constraints. For more information aboutInnoDB, see Chapter 14, The InnoDB Storage Engine.MyISAM: 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.Memory: Stores all data in RAM, for fast access in environments that require quick lookups of non-critical data. This engine was formerly known as theHEAPengine. Its use cases are decreasing;InnoDBwith its buffer pool memory area provides a general-purpose and durable way to keep most or all data in memory, andNDBCLUSTERprovides fast key-value lookups for huge distributed data sets.CSV: 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 inInnoDBtables during normal operation, and only use CSV tables during the import or export stage.Archive: These compact, unindexed tables are intended for storing and retrieving large amounts of seldom-referenced historical, archived, or security audit information.Blackhole: The Blackhole storage engine accepts but does not store data, similar to the Unix/dev/nulldevice. Queries always return an empty set. These tables can be used in replication configurations where DML statements are sent to slave servers, but the master server does not keep its own copy of the data.Merge: Enables a MySQL DBA or developer to logically group a series of identicalMyISAMtables and reference them as one object. Good for VLDB environments such as data warehousing.Federated: 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.Example: 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 mostlyInnoDB 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:
Table 15.1 Storage Engines Feature Summary
| Feature | MyISAM | Memory | InnoDB | Archive | NDB |
|---|---|---|---|---|---|
| Storage limits | 256TB | RAM | 64TB | None | 384EB |
| Transactions | No | No | Yes | No | Yes |
| Locking granularity | Table | Table | Row | Row | Row |
| MVCC | No | No | Yes | No | No |
| Geospatial data type support | Yes | No | Yes | Yes | Yes |
| Geospatial indexing support | Yes | No | Yes[a] | No | No |
| B-tree indexes | Yes | Yes | Yes | No | No |
| T-tree indexes | No | No | No | No | Yes |
| Hash indexes | No | Yes | No[b] | No | Yes |
| Full-text search indexes | Yes | No | Yes | No | No |
| Clustered indexes | No | No | Yes | No | No |
| Data caches | No | N/A | Yes | No | Yes |
| Index caches | Yes | N/A | Yes | No | Yes |
| Compressed data | Yes[d] | No | Yes[e] | Yes | No |
| Encrypted data[f] | Yes | Yes | Yes | Yes | Yes |
| Cluster database support | No | No | No | No | Yes |
| Replication support[g] | Yes | Yes | Yes | Yes | Yes |
| Foreign key support | No | No | Yes | No | No |
| Backup / point-in-time recovery[h] | Yes | Yes | Yes | Yes | Yes |
| Query cache support | Yes | Yes | Yes | Yes | Yes |
| Update statistics for data dictionary | Yes | Yes | Yes | Yes | Yes |
|
[a] InnoDB support for geospatial indexing is available in MySQL 5.7.5 and higher. [b] InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature. InnoDB support for FULLTEXT indexes is available in MySQL 5.6.4 and higher. [d] Compressed MyISAM tables are supported only when using the compressed row format. Tables using the compressed row format with MyISAM are read only. [e] Compressed InnoDB tables require the InnoDB Barracuda file format. [f] Implemented in the server (via encryption functions), rather than in the storage engine. [g] Implemented in the server, rather than in the storage engine. [h] Implemented in the server, rather than in the storage engine. |
|||||
Leave a Reply