MySQL Engines

Translate the Page

Google

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:

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:

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.

 

Row-Level Locking:

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.

 

Table-Level Locking:

MySQL uses table-level locking for MyISAM, MEMORY, and 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 BY operations on a large part of the data or must scan the entire table frequently.

Reference: https://dev.mysql.com

Thank you.

My name is Shashank Shekhar. I am a Software Engineer, currently working in one of the leading web-hosting companies in India. I am having 2 years of experience in Linux Server Administration.

I love to work in Linux environment & love learning new things.

Powered by Facebook Comments

Be the first to comment

Leave a Reply