Monday, 17 June 2013

MySql Storage Engine Types


A storage engine is a software module that a database management system uses to create, read, update data from a database. There are two types of storage engines in MySQL. Transactional and non-transactional.

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. Choosing the right storage engine is an important strategic decision, which will impact future development.


List of MySQL supported storage engines:


1.) MyISAM

2.) InnoDB

3.) Memory

4.) CSV

5.) Merge

6.) Archive

7.) Blackhole

8.) Federated

9.) Example



1.) MyISAM:- The default MySQL storage engine and the one that is used the most in Web, data warehousing, and other application environments.It is a fast storage engine. It does not support transactions. MyISAM provides table-level locking.

2.) InnoDB:- 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.

3.) Memory:- Stores all data in RAM for extremely fast access in environments that require quick lookups of reference and other like data. This engine was formerly known as the HEAP engine.

4.) CSV:- The CSV storage engine stores data in text files using comma-separated values format. You can use the CSV engine to easily exchange data between other software and applications that can import and export in CSV format.

5.) Merge:- Merge operates on underlying MyISAM tables. Merge tables help manage large volumes of data more easily. It logically groups a series of identical MyISAM tables, and references them as one object. Good for data warehousing environments.

6.) Archive:- Archive storage engine is optimized for high speed inserting. It compresses data as it is inserted. It does not support transactions. It is ideal for storing, retrieving large amounts of seldom referenced historical, archived data.

7.) Blackhole:- The Blackhole storage engine accepts but does not store data. Retrievals always return an empty set. The functionality can be used in distributed database design where data is automatically replicated, but not stored locally. This storage engine can be used to perform performance tests or other testing.

8.) Federated:- Federated storage engine offers the ability to separate MySQL servers to create one logical database from many physical servers. Queries on the local server are automatically executed on the remote (federated) tables. No data is stored on the local tables. It is good for distributed environments.

9.) Example:- The Example storage engine is “stub” engine that does nothing. You can create tables with this engine, but no data can be stored in them or retrieved from them. The purpose of this engine is to serve as an example in the MySQL source code that illustrates how to begin writing new storage engines. As such, it is primarily of interest to developers.