Storage Engine is the core component of MySQL server. There are various type of storage engines each having its own advantages and disadvantages. This chapter of storage engine is very useful in determining the correct engine for each table of your database for best performance of your MySQL server.
Some table needs transaction safe environment and some doesn’t, that’s why choosing appropriate engine is necessary. Storage engine is responsible for storing and managing the data in the different tables. Every engine has its own way of handing the index and data in different files.Innodb is the most useful and secure engine for MySQL and set as default storage engine in MySQL versionsMySQL 5.5.5 or above.
MySQL also has a feature of pluggable storage engines in which you can manage (load / unload) the storage engines in the running state of MySQL server.
One can set the storage engine during the creation of the table or can change it later:
CREATE TABLE table_name1 (employee_number INT) ENGINE = myisam; Or ALTER TABLE table_name1 ENGINE = ‘innodb’;
The output shown as:
You can check available storage engines using the command SHOW ENGINES. The output looks like :
mysql>SHOW ENGINES\G *************************** 1. row *************************** Engine: PERFORMANCE_SCHEMA Support: YES Comment: Performance Schema Transactions: NO XA: NO Savepoints: NO *************************** 2. row *************************** Engine: InnoDB Support: DEFAULT Comment: Supports transactions, row-level locking, and foreign keys Transactions: YES XA: YES Savepoints: YES *************************** 3. row *************************** Engine: BLACKHOLE Support: YES Comment: /dev/null storage engine (anything you write to it disappears) Transactions: NO XA: NO Savepoints: NO *************************** 4. row *************************** Engine: MyISAM Support: YES Comment: MyISAM storage engine Transactions: NO XA: NO Savepoints: NO
The main storage engines provided by MySQL are follows:
MySQL divides the engines on the basis of transactional and Non-transactional tables, storage requirements, warehousing requirements etc. Some engine provides additional features like InnoDB provides referential Integrity. Some important storage engine are described as:
InnoDB: It is ACID complaint storage engine having properties of providing atomicity, consistency, integrity and durability. It has commit, roll back, crash recovery features to protect the data.
One of the best features of InnoDB is its row-level locking unlike other engines, it means it locks the single tuple (row) on which the operation is performed and allow all other rows to be fetched at same time thus multiple users can access the table at the same time. It also has referential consistency mean allow foreign key relationship between two tables which mean if the data in one table exist then must be present in other related table otherwise not, in short maintain parent-child relationship between the tables.
Here the Employee code is the key between the two tables that maintain consistency. If employee is not present in the employee table then these must not any entry in the order table having Employee id which is not exists in employee table.
MyISAM: It is the second most important database storage engine after InnoDB. Although it does not provide integrity and consistency but it provides other important features. It has speed feature, Myisam is faster than the InnoDB in a single query. It has fast insert, delete and update occurs.MyISAM is an advanced version of Isam engine.
It is generally used in warehousing as data transfer/copy can be possible by just copy the physical files to other locations. It is the default storage engine prior to MySQL 5.5.5.
Other main advantage of myisam engine is that it repairs the table automatically on startup and also provides command to repair it manually on corruption.MyISAM table is generally used where text or blob fields are required to be indexed as it provide full text search.
Each MyISAM table is stored in three different files:
- FRM -> Stores the table structure, definition of the table.
- MYI -> Stores the indexes of the table.
- MYD -> Stores the actual data of the table.
For example, if the table name is Employee then it creates three files at the physical level named as Employee.frm, Employee.MYI and Employee.MYD.
MEMORY:This storage engine keeps the table in the memory of the system (RAM) all the time so if the MySQL server restarts due to power failure or any hardware failure it will be lost.
So we will keep the tables in memory engine only if we bearable that loss and can manage the data from somewhere else or like the session data that is not important for us and can be fetch able easily.
The memory engine is the fastest engine in the MySQL server as all data keeps in cache memory and server not need to perform any disk input/output.
Generally memory tables are used for keeping the data of another table in memory as follows:
mysql>CREATE TABLE memory_table1 ENGINE = MEMORY SELECT * FROM database1.table_name; Query OK, 0 rows affected (0.07 sec)
MERGE:This storage engine is used to merge the multiple MyISAM tables logically. This storage engine is also known as MRG_MYISAM.The merge engine basically used for portioning of the main MyISAM tables. You can perform all select, delete, update, insertoperations on the MERGE table. Although the drop operation on merge table only drops the merge table specification not the actual myisam tables.
To create MERGE table from the myisam tables, you need to specify UNION=(list-of-tables) option (which tells MyISAM tables to use for creating merge table) in CREAE TABLE statement.
Following is the example to show how three tables can be used to create a single table with MERGE storage engine.
mysql> CREATE TABLE tableone (emp_idint(11) NOT NULL) ENGINE = MyISAM; Query OK, 0 rows affected (0.02 sec) mysql> CREATE TABLE tabletwo (emp_idint(11) NOT NULL) ENGINE = MyISAM; Query OK, 0 rows affected (0.02 sec) mysql> CREATE TABLE tablethree (emp_idint(11) NOT NULL) ENGINE = MyISAM; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE tablemerge (emp_idint(11) NOT NULL) ENGINE = MERGE UNION = (tableone, tabletwo, tablethree) INSERT_METHOD = LAST; Query OK, 0 rows affected (0.09 sec)
ARCHIVE:This is used to store very large amount of data like historical information, warehousing purposes. This storage engine doesn’t support any indexes on the table so the accessing of records is very slow. It stores all the data in the compressed format and save the disk space.
The archive engine allow only select and insert in the table not the delete, replace and update operations. It uses the zlib lossless data compression technique. It doesn’t’ allow keys but still allow Auto-Increment while creating the table. During the selection it scans the whole table because of un-availability of indexes on the key.
Generally used for backup on live server where you want to keep all the deleted data in to archive locations for any future use with the help of triggers on main table.
CSV: This storage engine stores the data in the form of text file that are comma delimited. This storage engine creates the two files at physical level while creation on MySQL server. These two files extensions are .frm file and the .csv file. FRM contains the structure and the CSV contains the actual data in text format.
The data file looks like any normal text file and one can read the data easily by opening the data file. This storage engine also creates another file which contains the meta information of the table like state of table and the total number of rows in the table. This meta file extension is .CSM. The read operation here also needs the full table scan.
The main advantage of this storage engine is that it can be used by any other application that can be able to oprn text files like Microsoft Office, open Office, Notepad++ etc.
Federated: This storage engine is to access the remote MySQL server data. It means when a user execute a query it will automatically fetch the data from the remote server, there is no data at the local machine. This storage engine is rarely used and not enabled automatically, if you want to use a federated engine you have to enabled it with the option “--federated” while startup.
You can create the federated table in two possible ways:
- Create the table on the remote server.
- Create the table on local server and includes the connection information from the remote server.
Create a table Using MySQL connection definition while creating table:
CREATE TABLE table_federated1 ( id INT(10) NOT NULL AUTO_INCREMENT, student_name VARCHAR(32) NOT NULL DEFAULT '' PRIMARY KEY (id), Key student_name (student_name) ) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://username:password@hostname:9312/fdbfederated/database';
This hostname and port is the remote server details to connect by the federated table on local server.
Create a federated table use create server command to set the connection parameter in advance to be used by federated engines at local sevrer.
CREATE SERVER remotehost FOREIGN DATA WRAPPER mysql OPTIONS (USER 'username', HOST 'hostname1', PORT 9312, DATABASE 'dbfederated'); CREATE TABLE table_federated ( id INT(10) NOT NULL AUTO_INCREMENT, student_name VARCHAR(32) NOT NULL DEFAULT '' PRIMARY KEY (id), KEY name (student_name) ) ENGINE=FEDERATED CONNECTION=remotehost/table_test1';
Blackhole:As the name implies this engine works as black hole means it doesn’t hold any data it catches the data and throws away without storing. We get an empty result set while fetching the data as there is no data it is always empty This engine is not used in live situations although it can be used in measurement purposes as it gives an impressions of generating and managing binary logging operations.
All the queries running on black hole are logging into the binary log and hence replicated in to the slave servers so this engine tables can be used as a dummy MySQL system.
The black hole engine creates only one physical file with extension .frm only as there is no data in the system. FRM file always contain the structure of the data.
Black hole engine supports all kind of indexes in the CREATE TABLE syntax although it is not useful as there is no selection on the table.
This engine is also not enabled by default, we have to enable it in mysql configurations or by the parameter passed during start up.
NDB:This is very special storage engine of MySQL using a distributed, nothing shared architecture. All systems are independently working and has all the data to provide reliability. This engine is commonly known as Cluster engine of MySQL because it provides clustering facility in the MySQL server.
NDB refers to the Network Database, it is formed / written separately from the MySQL and then integrated. This engine is not available in the basic package of MySQL as it requires very high resources to run on the MySQL server and can be implemented whenever necessary only, it requires multiple servers for different data nodes and a management node. A management node (a single MySQL system) handle the other data nodes (other Mysql server) to store and retrieve the data.
You can check the Storage engines of your MySQL server by the following command:
mysql> SHOW ENGINE STATUS ;