Mysql Storage Engines

Mysql Storage Engines

Patel Pratik MYSQL

In programming world we are always talking about data security, fast accessing of data and data searching algorith from database. In today’s article we will see how different Mysql Storage Engines perform their part. In this article, we are going to learn various MySQL storage engines or table types. It is essential to understand the features of each MySQL storage engine in details so that we can use them effectively to maximize the performance of our databases.Storage engines are MySQL components, that can handle the SQL operations for different table types to store and manage information in a database.

Various MySQL Storage Engines:

  • MyISAM
  • InnoDB
  • MERGE
  • MEMORY (HEAP)
  • ARCHIVE
  • CSV
  • FEDERATED

MyISAM:

  • This storage engine, manages non transactional tables, provides high-speed storage and retrieval, supports full text searching.
  • It supports table-level locking which limits the performance in read/write workloads, so it is often used in read-only or read-mostly workloads in Web and data warehousing configurations.
  • The size of the MyISAM table can be up to 256TB, which is huge. In addition, MyISAM tables can be compressed into read-only tables to save spaces. At startup, MySQL checks MyISAM tables for corruption and even repairs them in a case of errors. The MyISAM tables are not ansaction-safe.
  • Each MyISAM table is stored on disk in two files. The files have names that begin with the table name and have an extension to indicate the file type. The data file has an .MYD (MYData) extension. The index file has an .MYI (MYIndex) extension. The table definition is stored in the MySQL data dictionary.

InnoDB:

  • This is the default storage engine for MySQL 5.5 and higher. Its DML(Data Manipulation Language) operations follow the ACID model, with transactions featuring commit, rollback, and crash-recovery capabilities to protect user data.
  • It supports Row-level locking which provides consistent reads increase multi-user concurrency and performance. InnoDB tables arrange our data on disk to optimize queries based on primary keys.
  • Each InnoDB table has a primary key index called the clustered index that organizes the data to minimize I/O for primary key lookups. To maintain data integrity, InnoDB supports FOREIGN KEY constraints. With foreign keys, inserts, updates, and deletes are checked to ensure they do not result in inconsistencies across different tables.

InnoDB is mostly used general-purpose storage engine.

Transaction: A transaction is a sequential group of database manipulation operations, which is performed as if it were one single work unit. In other words, a transaction will never be complete unless each individual operation within the group is successful. If any operation within the transaction fails, the entire transaction will fail.

MERGE:

  • A MERGE table is a virtual table that combines group of MyISAM tables that have a similar structure to one table.
  • The MERGE table doesn’t contain its own indexes. it uses indexes of the component tables instead. The MERGE storage engine is also known as the MRG_MyISAM engine.
  • Using MERGE table, we can speed up performance when joining multiple tables. MySQL only allows us to perform SELECT, DELETE, UPDATE and INSERT operations on the MERGE tables.
  • If we use DROP TABLE statement on a MERGE table, only MERGE specification is removed. The underlying tables will not be affected.
mysql> CREATE TABLE t1 (
-> a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> message CHAR(20)) ENGINE=MyISAM;
mysql> CREATE TABLE t2 (
-> a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> message CHAR(20)) ENGINE=MyISAM;
mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');
mysql> CREATE TABLE total (
-> a INT NOT NULL AUTO_INCREMENT,
-> message CHAR(20), INDEX(a))
-> ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

Output

Mysql Storage Engines - MERGE
Mysql Storage Engines – MERGE

MEMORY:

  • Stores all data in RAM, for fast access in environments that require quick access of non-critical data. This engine was formerly known as the HEAP engine.
  • The memory tables are stored in memory and use hash indexes so that they are faster than MyISAM tables.
  • The lifetime of the data of the memory tables depends on the uptime of the database server. Mostly it stored non-critical data such as session management or caching because the data will be lost on database crashes, hardware issues, or power outages.

Archive:

  • The archive storage engine allows you to store a large number of records for archiving purpose, into a compressed format to save disk space.
  • The archive tables only allow INSERT and SELECT statements. The ARCHIVE tables do not support indexes, so it is required a full table scanning for reading rows.
  • The archive storage engine compresses a record when it is inserted and decompresses it using the zlib library as it is read.

CSV:

  • The CSV storage engine stores data in comma-separated values (CSV) file format. A CSV table brings a convenient way to migrate data into non-SQL applications such as spreadsheet software.
  • CSV table does not support NULL data type. In addition, the read operation requires a full table scan.
  • These tables are really text files with comma-separated values. CSV tables let you import or dump data in CSV format, to exchange data with scripts and applications that read and write that same format.
  • Because CSV tables are not indexed, you typically keep the data in InnoDB tables during normal operation, and only use CSV tables during the import or export stage.

FEDERATED:

  • The FEDERATED storage engine allows you to manage data from a remote MySQL server without using the cluster or replication technology.
  • The local federated table stores no data. When you query data from a local federated table, the data is pulled automatically from the remote federated tables.

Leave a Reply