In this chapter, we will talk about MySQL storage engines.
Storage engine is a software module that a database management system uses to create, read, update the data from a database.
There are two types of storage engines in MySQL: transactional and non-transactional.
For MySQL 5.5 and later:
The default storage engine is InnoDB.
For MySQL prior to version 5.5:
The default storage engine was MyISAM.
IMPORTANT: Choosing the right storage engine is an important strategic decision, which will impact future development.
InnoDB is the most widely used storage engine with transaction support. It is an ACID compliant storage engine. It supports row-level locking, crash recovery and multi-version concurrency control. It is the only engine which provides foreign key referential integrity constraint. Oracle recommends using InnoDB for tables except for specialized use cases.
MyISAM is the original storage engine. It is a fast storage engine. It does not support transactions. MyISAM provides table-level locking. It is used mostly in Web and data warehousing.
MySQL uses row-level locking for
InnoDB tables to support simultaneous write access by multiple sessions, making them suitable for multi-user, highly concurrent, and OLTP applications.
Advantages of row-level locking:
- Fewer lock conflicts when different sessions access different rows.
- Fewer changes for rollbacks.
- Possible to lock a single row for a long time.
MySQL uses table-level locking for
MERGE tables, permitting only one session to update those tables at a time. This locking level makes these storage engines more suitable for read-only, read-mostly, or single-user applications.
Advantages of table-level locking:
- Relatively little memory required (row locking requires memory per row or group of rows locked)
- Fast when used on a large part of the table because only a single lock is involved.
- Fast if you often do
GROUP BYoperations on a large part of the data or must scan the entire table frequently.
Powered by Facebook Comments