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.
I love cooking. Yes ! you read it right. I love cooking however I am fond of writing technical blogs too.
I work as a Cloud Developer Support Engineer II at DigitalOcean.
I hope my articles helps you to sail out from the Ocean of the technical problems. Also on other hand helps to take a deep dive into different type of technologies.
Powered by Facebook Comments