A storage engine is a software that is used by a database management system to create, read, and update data from a database. Most DBMS use APIs (Application Programming Interface) to enable interactions of users with the storage engines. There are two types of storage engines; Transactional and Non-transactional storage engines.
Transactional Databases
Transactional databases mean that the write operations on these databases are able to be rolled back if they do not complete. These operations are known as transactions. Most of the modern databases are transactional databases.
Non-Transactional Databases
The impact of no Rollback/Commit is felt. In order to perform rollback operation the user will need to do it manually with codes.
Common storage engines in MySQL
Common storage engines used in MySQL are InnoDB and MyISAM. The default storage engine in MySQL prior to version 5.5 was MyISAM. In the case of MySQL 5.5 and later, the default storage engine is InnoDB. There are many other storage engines also which are used in MySQL. Some of them are listed below.
1) Memory
2) CSV
3) Merge
4) Archive
5) Federated
6) Blackhole
7) Example
As these are the storage engines supported by MySQL, we are now going to discuss about MyISAM and InnoDB.
MyISAM
As we have seen, MyISAM was the default storage engine used in MySQL up to version 5.5. It is comparatively faster compared to other storage engines. It is a non-transactional storage engine and it was one of the major drawbacks of the MyISAM storage engine. The MyISAM provides table-level locking.
MyISAM Characteristics
There are many characteristics for MyISAM tables. The MyISAM is stored in three files which include:
1) .frm file – It stores table format.
2) .MYD file – It is the data file.
3) .MYI file – It is the index file.
The major characteristics of the MyISAM tables are given below.
1) All data values are sorted in a way that the low byte will be first. It is helpful because there is no significant speed penalty for storing data low byte first.
2) All numeric key values are sorted such as high byte first and it allows better indexing.
3) Large files (up to 63-bit file length) are supported on file systems.
4) There is a limit of (232)2 (1.844E+19) rows.
5) The maximum number of columns per index is 16.
6) The maximum number of indexes per table is 64.
7) The maximum key length is 1000 bytes. It can be changed by recompilation.
8) When new rows are added, the index tree is split so that the high node only contains only one key which will help to improve space utilization in the index tree.
MyISAM Storage Formats
MyISAM supports three different storage formats. The storage formats supported by MyISAM are given below.
1) Fixed
2) Dynamic
3) Compressed
The first two, fixed, and dynamic are chosen automatically according to the types of columns usd. The third format, compressed can be created only with the myisampack utility. We can decompress the compressed tables myisamchk –unpack; MyISAM tables are reliable. It is because all changes to a table made by an SQL statement are written before the statement returns, but there are problems to the MyISAM tables.
Problems in the MyISAM tables
The common problems regarding the MyISAM tables are listed below.
1) Corrupted MyISAM Tables
2) Tables Not Being Closed Properly
Let’s see each one in detail.
1) Corrupted MyISAM Tables
Even though the MyISAM tables are reliable, there is a chances that the tables get corrupted. The following are reasons why MyISAM tables get corrupted.
1) The mysqld process is killed in the middle of a write.
2) An unexpected computer shutdown (unexpected power offs are example).
3) Various hardware failures.
4) We are using an external program (eg: myisamchk) to modify a table that is being modified by the server at the same time.
5) A software bug in the MySQL or MyISAM code.
These are the common reasons for a MyISAM tables gets corrupted.
The symptoms of a table corruption
The symptoms of a table corruption are,
Getting the following error while selecting data from table.
“Incorrect key file for table: ‘…’. Try to repair it”
The queries don’t find rows in the table or returns incomplete results.
2) Problems from Tables Not Being Closed Properly
Every MyISAM index file, .MYI files, will have a counter in the header which can be used to check whether the table has been closed properly. If we get the following warning from CHECK TABLE or myisamchk, this means that this counter has gone out of sync.
“clients are using or haven’t closed the table properly”
InnoDB
The InnoDB is the most widely used storage engine with transaction support. It is the default storage engine from the MySQL 5.5. Unless MyISAM; it supports row-level locking. The other important features of the InnoDB are crash recovery and multi version concurrency control. The InnoDB is the only engine which provides foreign key referential integrity constraint. It is a general purpose storage engine which provides high reliability and high performance.
InnoDB Characteristics
There are many advantages when using InnoDB storage engine. The most important features include:
1) It’s DML operations follow the ACID model with transactions featuring commit, rollback, and crash recovery capabilities to protect user data.
2)The DML operations is Data Manipulation Language. It includes the following.
SELECT – retrieve data from a database.
INSERT – insert data into a table,
UPDATE – updates existing data within a table.
DELETE – Delete all records from a database table.
MERGE – UPSERT operation (insert or update).
CALL – call a PL/SQL or Java subprogram.
EXPLAIN PLAN – interpretation of the data access path.
3) Row-level locking and increases multi-user concurrency and performance.
4) The data is arranged on disk to optimize queries based on primary keys.
5) InnoDB supports FOREIGN KEY constraints to maintain data integrity.
6) InnoDB is published under the same GNU GPL License Version 2 (of June 1991) as MySQL.
7) InnoDB supports automatic crash recovery.
Disadvantages of InnoDB Storage Engine
Increased complexity
1) Creating an InnoDB table is no more complex than MyISAM.
2) InnoDB tables do not support full-text searches; it is not easy to match one or more keywords against multiple columns.
3) If your application is primarily selecting data and performance is a priority, MyISAM tables will normally be faster and use fewer system resources.
4) If you have a significantly large or heavily-used system, the speed differences are not likely to be negligible.
These are the main advantages and disadvantages of InnoDB. The InnoDB is recommended in most of these situations unless you have a significantly large or heavily-used system.
These are the two commonly used storage engines in MySQL. Even though there are many other storage engines which are supported by MySQL, these two are the commonly used storage engines.
If you need any further assistance please reach our support department.