Detailed information about Storage Engines in MySQL

MySQL STORAGE ENGINES

What is storage engine?

       In MySQL the datas are stored as files in any one of the types in storage engines. MySQL supports pluggable storage engines that we can use any types of engine belongs to your data.There are two types of storage engines in MySQL  Transactional(The data can be modified in engines) and non-transactional(It can only fetch the data from engines). The default storage engine for MySQL prior to version 5.5 was MyISAM. For MySQL 5.5 and later, the default storage engine is InnoDB.

Types of Storage Engine

1.MyISAM

2.InnoDB

3.Merge

4.Memory

5.Blackhole

6.Archive

7.CSV

8.Federated

1.MyISAM

       MyISAM is the oldest storage engines and it does not support transactions.It provides table level locking.If you create a table without representing the storage engine it take MyISAM engine as default (before MySQL 5.5 version ).MySQL databases are generally store in data directories and MYISAM tables are stored using 3 files.

          .frm - Table structure

          .MYD - Data file

          .MYI - Index file

MyISAM has most flexible auto increment.The tables can be used to set up merge tables.The table storage format is portable.Maximum no of indexes per table = 64.Maximum no of columns per index = 16.Blob and TEXT columns can be indexed.Table size is 256TB. It does not provide for foreign_key.

2.InnoDB

       InnoDB is a Transactional safe storage engine.It is an ACID (Atomicity, Consistency, Isolation, Durability) compliant storage engine.It has commits,rollback and crash recovery capabilty to recover the data.InnoDB provide row level locking to use multi user concurrency and performance.It supports foreign key. InnoDB creates two log files namely "ib_logfile0" and "ib_logfile1" and a data file "ibdata1" in the MySQL data directory where it stores its tables.MySQL creates an auto-extending 10MB data file in ibdata1 and two 5MB log files in ib_logfile0 and ib_logfile1 in the MySQL data directory. The table definitions are stored in database directory with a .frm extension whereas the data is stored in the "ibdata1" - tablespace.Minimum tablespace size is 10MB. And maximum tablespace size is 64TB.

3.Merge

       The MERGE engine type allows you to combine a number of identical tables into a single table. You can then execute queries that return the results from multiple tables as if they were just one table. Each table merged must have the same table definition. The MERGE table is particularly effective if you are logging data directly or indirectly into a MySQL database and create an individual table per day, week or month and want to be able to produce aggregate queries from multiple tables. Transaction No Locking level Table

4.Memory

       The MEMORY storage engine (previously known as the HEAP storage engine) stores all data in memory; once the MySQL server has been shut down any information stored in a MEMORY database will have been lost. However, the format of the individual tables is kept and this enables you to create temporary tables that can be used to store information for quick access without having to recreate the tables each time the database server is started.Cannot contain BLOB or TEXT columns.It has table level locking .

5.Blackhole

        It acts as a “black hole” that accepts data but throws it away and does not store it. BLACKHOLE engine does not actually store any data. Although you can create tables and indexes, all SQL statements that would add or update information to the database are executed without actually writing any data. The database structure is retained, however, and you can create any indexes on the (non-existent) information that you want.

6.Archive

       It is used for storing large amounts of data without indexes in a very small footprint.It supports only the INSERT and SELECT statements, but does support most of the MySQL field types. Information stored in an ARCHIVE storage engine table is compressed and cannot be modified and so ARCHIVE tables are perfect for storing log data When an archive table is created, following files are created in the database directory.

           .frm - table definition
           .ARZ - DATA file
           .ARM - METADATA file

       It does NOT support DELETE, REPLACE and UPDATE.It provides row level locking.

7. CSV(Comma Separated Value)

       It stores data in text files using comma-separated values format. When a table is created 2 files are created in the database directory

       .frm - table definition
       .CSV - data file

It is not an efficient method for storing large volumes of data, or larger data types like BLOB, although such types are supported. There is also no indexing. However, because the data is stored in the CSV format it is exceedingly portable.

8.Federated

     The FEDERATED storage engine (added in MySQL 5.03) enables you to access data from remote MySQL database (other databases may be supported in the future) as if it were a local database. In effect, the MySQL server acts as a proxy to the remote server, using the MySQL client access library to connect to the remote host, execute queries and then reformat the data into the localized format.It does not support transactions.

Features of  MySQL Storage Engines 

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[c]	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.

^[c] 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). Data-at-rest tablespace encryption is available in MySQL 5.7 and higher.

^[g] Implemented in the server, rather than in the storage engine.

^[h] Implemented in the server, rather than in the storage engine.