Storage Engines in MySQL 8

InnoDB is the default storage engine in MySQL, chosen for its robustness, reliability, and performance. Here’s a detailed explanation of why it’s the default and its key features:

1. ACID Compliance

InnoDB is the storage engine that supports all types of transactions.

A) Atomicity: It involves transactions like COMMIT & ROLLBACK.

B) Consistency : It is a mechanism used for Crash Recovery.

C) Isolation : Different isolation levels apply at each transaction level.

D) Durability : The storage engine interacts with underlying hardware to provide the best performance

2. Transactions

InnoDB is a transactional storage engine supporting Commit, Rollback, and Savepoint operations. Transactions help to ensure data integrity, especially in the case of complex operations.

3. Row-Level Locking

It uses Row-level locking instead of table-level locking, It also supports Indexing.

4. Efficient Memory Usage

It maintains its own buffer pool (memory area where the InnoDB cache table and indexed data are stored)

5. Crash Recovery

It has also supported the crash recovery mechanism. At the time of system failure, it uses its transactional logs and checkpoint system to detect the failure and resolve it.

1. FEDERATED Storage Engine

a) FEDERATED Storage Engine is Disabled by default.

b) The Tables that are created with FEDERATED Storage Engine, normally point to a table in another MySQL instance installed on a separate server.

c) In Microsoft SQL Server this mechanism is called Linked Server whereas in Oracle it is called Database link.

d) Both tables should have the same name and definition i.e. Requester and Target tables.

e) The table in the requester acts like a view.

f) The target table can have a different storage engine but the requester table should be created with FEDERATED.

Limitation

a) This storage engine is disabled by default so we cannot touch it also it does not support any transactions support.

Syntax

Create table employees (Employee_id int, Employee_salary int) ENGINE=FEDERATED CONNECTION = 'mysql://db_user@target-server:3306/employees/employees';

2. MEMORY Storage Engine

a) In MEMORY storage engine Table data will be written in Memory only instead of the physical files.

b) In previous versions it was called HEAP, but now it has been renamed to Memory Storage engine.

c) It is very useful for the temporary tables.

d) It is temporary which means data will be lost on server crash.

e) Extremely fast data access, but memory is volatile, so it's best suited for read-only cache or temporary tables.

Limitations

a) No Transaction support.

b) No Referential Integrity support – No Foreign Key.

c) No TEXT data type support – No BLOB column.

Syntax

CREATE TABLE continents ( id int NOT NULL, name VARCHAR(25) NOT NULL) ENGINE=MEMORY;

3. BLACKHOLE Storage Engine

a) BLACKHOLE Storage engine acts like a Black hole, which means whatever the data goes in this engine will never come back. It will disappear.

b) You can store as much data as you want, when you retrieve it, it returns an empty result set.

Limitation

a) It does not support transaction mechanisms.

Syntax

CREATE TABLE continents ( id int NOT NULL, name VARCHAR(25) NOT NULL) ENGINE=BLACKHOLE;

4. CSV Storage Engine

a) CSV storage engine stores tables in text files using comma-separated values format.

b) MySQL creates a .csv file in the $DATA_DIR – plain text file.

c) CSV format can be read, and written by spreadsheet applications like Excel.

Note: – Below is the command to find out the $DATA_DIR path.

Limitations

a) It does not support transaction mechanisms.

b) CSV files are not indexed.

Syntax

CREATE TABLE continents ( id int NOT NULL, name VARCHAR(25) NOT NULL) ENGINE=CSV;

5. MyISAM Storage Engine

a) MyISAM = My + ISAM = Indexed Sequential Access Method.

b) Indexing algorithm developed by IBM that allows retrieving information from large sets of data in a fast way.

c) MyISAM was the default storage engine up until MySQL 5.5 – around 2009-2010.

d) Good speed advantages especially useful in Data warehouse scenarios.

e) it is replaced by InnoDB.

Limitation

a) It does not support transactions – ACID Model

Note: – MyISAM ENGINE is used in Data Warehouse databases where there are a lot of reads of the records.

Syntax

CREATE TABLE continents ( id int NOT NULL, name VARCHAR(25) NOT NULL) ENGINE=MYISAM;

6. ARCHIVE Storage Engine

a) ARCHIVE Storage Engine Produces special-purpose tables that store large amounts of un-indexed data in a very small footprint.

b) Creates .ARZ files with the same name as the table name.

c) ARZ files are binary data files and are called MySQL Archive Storage Engine Data Files.

d) Uses gzip to compress rows.

Limitations

a) No DELETE or UPDATE operation.

b) No Partitioning.

Syntax

CREATE TABLE continents ( id int NOT NULL, name VARCHAR(25) NOT NULL) ENGINE=ARCHIVE;

7. InnoDB Storage Engine

a) It is a general-purpose storage engine that balances high reliability and high
performance.

b) In MySQL 8, It is a default storage engine.

c) Its DML operations follow the ACID model.

d) Transactions support commit, rollback, and crash-recovery to protect data.

e) It supports the Row-Level Locking function.

f) InnoDB tables arrange data on disk to optimize queries based on PK.

g) Each InnoDB table has a primary key index, clustered index, and arranged data.

h) Data Integrity supports FK constraints.

Syntax

CREATE TABLE continents ( id int NOT NULL, name VARCHAR(25) NOT NULL) ENGINE=InnoDB;

CREATE TABLE continents ( id int NOT NULL, name VARCHAR(25) NOT NULL);

Note: – We can use the above commands to create a table in the InnoDB engine, if we cannot give the engine value then also it will be created under the InnonDb engine as it is set by default.