MySQL Storage engines is a very important topic that you must study if you are planning to make a career in the database domain. In this tutorial, we will learn what is storage engines in MySQL, what are its types, information about all storage engines that MySQL supports and how to specify a storage engine while creating a table. Let’s get started.
What is Storage Engine/Table Type?
A storage engine is a software module that is used in DBMS to perform operations such as create, insert, read and delete. There are many different storage engines available for MySQL DBMS that you can use depending on your application and use case.
It is very important to understand the features of every storage engine so that you can take advantage of their features and use them effectively. If you are learning the MySQL database, then it is not very important to know in-depth details about the storage engine. But, if you are planning a production database, you must be aware of each storage engine.
InnoDB is the default storage engine for MySQL 5.5 and later versions. Prior to version 5.5, MyISAM was the default storage engine for MySQL. Choosing the best storage engine is a critical strategic choice that will have a significant influence on future development.
Now let’s see the list of storage engines supported by MySQL.
List of MySQL Storage Engines
- InnoDB
- MyISAM
- Memory
- CSV
- Archive
- Blackhole
- NDB
- Merge
- Federated
- Example
InnoDB- InnoDB is the MySQL 8.0’s default storage engine. InnoDB is a transaction-safe (ACID compliant) MySQL storage engine that protects user data with commit, rollback, and crash recovery features. Multi-user concurrency and speed are improved by InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads. To decrease I/O for typical queries based on primary keys, InnoDB stores user data in clustered indexes. InnoDB also supports FOREIGN KEY referential-integrity requirements to ensure data integrity.
MyISAM- These tables take up a little amount of space. Because table-level locking slows read/write workloads, it’s commonly utilised in read-only or read-mostly workloads in Web and data warehousing systems.
Memory- It keeps all data in RAM for easy access in contexts where non-critical data must be looked up quickly. The HEAP engine was the previous name for this engine. InnoDB’s buffer pool memory region provides a general-purpose and durable means to hold most or all data in memory, whereas NDBCLUSTER delivers rapid key-value lookups for large distributed data sets.
CSV- Its tables are text files containing comma-separated values. CSV tables allow you to import or dump data in CSV format, allowing you to share information with scripts and apps that can read and write in the same format. Because CSV tables aren’t indexed, you should retain your data in InnoDB tables for normal operations and only use CSV tables for import and export.
Archive- These unindexed, compressed tables are designed to store and retrieve vast amounts of very seldom historical, archival, or security audit data.
Blackhole- Similar to the Unix /dev/null device, the Blackhole storage engine receives data but does not save anything. The results of queries are always empty. These tables can be used in replication settings where DML statements are delivered to replica servers but no duplicate of the data is kept on the source server.
NDB(also known as NDBCLUSTER)- This clustered database engine is best suited for applications that demand the greatest level of availability and uptime.
Merge- It Allows a MySQL DBA or developer to conceptually aggregate and refer to a succession of identical MyISAM tables as a single entity. VLDB situations, such as data warehousing, are a good fit.
Federated- This allows you to join several MySQL servers together to build a single logical database from many physical servers. For dispersed or data mart situations, this is an excellent choice.
Example- In the MySQL source code, this engine provides an example of how to start creating new storage engines. Developers are the primary target audience. The storage engine is little more than a “stub.” This engine allows you to create tables, but there is no way to save or retrieve data in them.
If you enter the following query on the MySQL CLI, you will get currently available storage engines.
SHOW ENGINES;
As you can see, we get the information about storage engines.
Specifying Storage Engine in MySQL
You can set the storage engine while creating a table in the CREATE TABLE query as shown below.
CREATE TABLE emp(
Id INT PRIMARY KEY,
Name VARCHAR(50),
City VARCHAR(50)
) ENGINE='MyISAM';Code language: SQL (Structured Query Language) (sql)When we execute the above query, the table of type MyISAM will be created.
If you want to see the storage engine/ table type of any table, you can use the following query.
SELECT ENGINE FROM information_schema.TABLES
WHERE TABLE_SCHEMA='journaldev'
AND TABLE_NAME='emp';Code language: SQL (Structured Query Language) (sql)
As you can see, we are trying to get the storage engine of the table that we created recently and we have got the correct output.
Changing Storage Engine in MySQL
To change the storage engine, we can use the ALTER TABLE statement.
ALTER TABLE emp ENGINE="InnoDB";Code language: SQL (Structured Query Language) (sql)When we execute the above query, the table type will change from MyISAM to InnoDB.
Let’s check if the table type is changed or not.
SELECT ENGINE FROM information_schema.TABLES
WHERE TABLE_SCHEMA='journaldev'
AND TABLE_NAME='emp';Code language: SQL (Structured Query Language) (sql)
As you can see, the table type of the emp table is changed from MyISAM to InnoDB.
Summary
In this tutorial, we learned-
- What the MySQL storage engine/table type is.
- What storage engines does MySQL support.
- Information about each storage engine.
- How to specify a storage engine while creating a table.
- How to see the storage engine/table type of a table.
- How to change the storage engine/table type of a table.
References
MySQL official documentation on storage engines.