MySQL Storage Engines

Introduction

Storage engines (underlying software component) are MySQL components, that can handle the SQL operations for different table types to store and manage information in a database. InnoDB is mostly used general-purpose storage engine and as of MySQL 5.5 and later it is the default engine. There are many storage engines available in MySQL and they are used for different purposes.

Version : MySQL 5.6

Storage engines of MySQL

Engines	Description
InnoDB	This is the default storage engine for MySQL 5.5 and higher. It provides transaction-safe (ACID compliant) tables, supports FOREIGN KEY referential-integrity constraints. It supports commit, rollback, and crash-recovery capabilities to protect data. It also support row-level locking. It's "consistent nonlocking reads" increases performance when used in a multiuser environment. It stores data in clustered indexes which reduces I/O for queries based on primary keys.
MyISAM	This storage engine, manages non transactional tables, provides high-speed storage and retrieval, supports full text searching.
MEMORY	Provides in-memory tables, formerly known as HEAP. It sores all data in RAM for faster access than storing data on disks. Useful for quick looks up of reference and other identical data.
MERGE	Groups more than one similar MyISAM tables to be treated as a single table, can handle non transactional tables, included by default.
EXAMPLE	You can create tables with this engine, but can not store or fetch data. Purpose of this is to teach developers about how to write a new storage engine.
ARCHIVE	Used to store a large amount of data, does not support indexes.
CSV	Stores data in Comma Separated Value format in a text file.
BLACKHOLE	Accepts data to store but always returns empty.
FEDERATED	Stores data in a remote database.

Advantages of InnoDB storage engine

• InnoDB has maximum performance when processing large data volumes.
• Its DML operations (add, update and delete data) is ACID (atomic, consistent, isolated and durable) model compatible, with transactions featuring commit, rollback, and crash-recovery capabilities to protect user data.
• Row-level locking (locks are placed on single records (rows)) system increase multi-user concurrency and performance. All InnoDB locks held by a transaction are released when the transaction is committed or aborted.
• InnoDB tables arrange your data on disk to optimize queries based on primary keys.
• InnoDB supports FOREIGN KEY constraints to maintain data integrity. Therefore inserts, updates, and deletes are all checked to ensure they do not result in inconsistencies across different tables.
• It is possible to mix InnoDB tables with tables from other MySQL storage engines within the same statement. For example, you can use a join operation to combine data from InnoDB and MEMORY tables in a single query.

Creating InnoDB tables : 
Use CREATE TABLE statement to create am InnoDB table without any special clauses. As of MySQL 5.5, it is the default MySQL storage engine. In MySQL 5.6, issuing the CREATE TABLE statement without an ENGINE= clause creates an InnoDB table. Here is an example :

mysql> CREATE TABLE table1 (col1 INT, col2 CHAR(30), PRIMARY KEY (col1));
Query OK, 0 rows affected (1.11 sec)

Handling FOREIGN KEY Constraints in InnoDB :

MySQL supports foreign keys, which let you cross-reference related data across tables, and foreign key constraints, which help keep this spread-out data consistent. Foreign key definitions for InnoDB tables are subject to the following conditions :

• InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.
• InnoDB does not currently support foreign keys for tables with user-defined partitioning. This means that no user-partitioned InnoDB table may contain foreign key references or columns referenced by foreign keys.
• InnoDB allows a foreign key constraint to reference a non-unique key. This is an InnoDB extension to standard SQL.

Limitation: InnoDB table :

• Maximum 1017 columns are allowed in a table (raised in MySQL 5.6.9 from the earlier limit of 1000).
• Maximum 64 secondary indexes are allowed in a table. Secondary indexes is a type of InnoDB index that represents a subset of table columns.
• By default, an index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix.
• The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes (combined index key in a multi-column index).
• The maximum row length except for variable-length columns (VARBINARY, VARCHAR, BLOB and TEXT), is about 8000 bytes for the default page size of 16KB.
• Internally InnoDB supports row sizes larger than 65,535 bytes, but MySQL itself imposes a row-size limit of 65,535 for the combined size of all columns.
• The maximum table space size is four billion database pages (64TB) and the minimum table space size is slightly larger than 10MB.