In this tutorial, we are going to learn about storage engines in MySQL. It is used for CRUD operations by DBMS. We come across fowling kinds of storage engines:
1. Transactional
2. Non-transactional
InnoDB is default Storage Engine for MySQL version 5.5. Before that MyISAM was being used as default engine for storage. As a part of strategic planning selection of correct engine for storage is a crucial point. Because all the development that we do in the future will be affected by the selection of the storage engine.Let’s have a look at the following Storage Engines:
1. InnoDB
2. MyISAM
3. Memory
4. CSV
5. Merge
6. Archive
7. Federated
8. Blackhole
All these storage engines are supported by MySql but because of its features InnoDB is used mostly. It is ACID complaint and supports transactions. Few other features of InnoDB storage engine are as follows:
1. ACID compliant
2. Supports row-level locking
3. Crash recovery
4. Multi-version concurrency control
5. Foreign key referential integrity
Now let’s have a look at second popular storage engine MyISAM. It is one of the oldest storage engines. Following are major characteristics of MyISAM storage engine due to which it used to be so popular.
1. It is fast
2. Does not have support to transactions
3. Supports locking at table-level
4. Used mostly in data warehousing
Memory Storage Engine:
1. It creates memory tables
2. It is fastest of all other storage engines
3. Also supports row-level locking
4. Like MyISAM it also does not support transactions
5. It helps in creating tables for temporary use.
6. If DBMS is restarted data loss occurs.
CSV Storage Engine:
1. It stores data in the format of CSV
2. Flexible
3. Integral
Merge Storage Engine:
1. It takes MyISAM as base
2. Handles relatively more data size
3. Identical tables are grouped together in it
4. All tables are referenced as common object
5. Can be used as option for DWH
Archive storage
1. It has relatively high speed
2. Used for insertion of records rapidly
3. It also does not have support for transactions
4. Good option to store bulk amount of data.
5. Data can also be archived as historical reference
Blackhole Storage Engine:
1. It is not used for storing data instead it accepts data
2. An empty set is obtained when retrieval is requested.
3. Used for distributed databases
4. Database is replicated automatically.
5. Database is not stored locally
6. Used for performance testing
Federated Storage Engine
1. Merges logical databases
2. Can combine physical servers
3. Can separate servers running MySQL.
4. Queries are not executed on remote servers always, not on local servers.
