MySQL storage engines
A storage engine is a
software module that a database management system uses to create, read, update
data from a database. There are two types of storage engines in MySQL.
Transactional and non-transactional.
List of storage engines
MySQL supported storage engines:
- MyISAM
- InnoDB
- Memory
- CSV
- Merge
- Archive
- Federated
- Blackhole
- Example
- XTraDB
- NDB
Cluster
- TokuDB
- The
IBMDB2I
- ·
MyISAM is the original storage engine. It is a
fast storage engine. It does not support transactions. MyISAM provides
table-level locking. It is used most in Web, data warehousing.
- ·
InnoDB is the most widely used storage engine
with transaction support. It is an ACID compliant storage engine. It supports
row-level locking, crash recovery and multi-version concurrency control. It is
the only engine which provides foreign key referential integrity constraint.
- ·
Memory storage engine creates tables in memory.
It is the fastest engine. It provides table-level locking. It does not support
transactions. Memory storage engine is ideal for creating temporary tables or
quick lookups. The data is lost when the database is restarted.
- ·
CSV stores data in csv files. It provides great
flexibility, because data in this format is easily integrated into other
applications.
- ·
Merge operates on underlying MyISAM tables.
Merge tables help manage large volumes of data more easily. It logically groups
a series of identical MyISAM tables, and references them as one object. Good
for data warehousing environments.
- ·
Archive storage engine is optimized for high speed
inserting. It compresses data as it is inserted. It does not support
transactions. It is ideal for storing, retrieving large amounts of seldom
referenced historical, archived data.
- ·
The Blackhole storage
engine accepts but does not store data. Retrievals always return an empty set.
The functionality can be used in distributed database design where data is
automatically replicated, but not stored locally. This storage engine can be
used to perform performance tests or other testing.
- ·
Federated storage engine offers the ability to
separate MySQL servers to create one logical database from many physical
servers. Queries on the local server are automatically executed on the remote
(federated) tables. No data is stored on the local tables. It is good for
distributed environments.
- XTraDB
Percona XtraDB is an enhanced version of the InnoDB storage
engine for MySQL® and MariaDB®. It has much faster performance than InnoDB and
better scalability on modern hardware. XtraDB has more features than InnoDB;
these are useful in high-load environments. It is backwards-compatible with
InnoDB, so you can use it as a drop-in replacement. Percona XtraDB includes all
of InnoDB's reliable ACID-compliant design and advanced MVCC architecture. It
adds features, tunability, metrics, and scalability. In particular, it is
designed to scale better than InnoDB on many cores, to use memory more
efficiently than InnoDB, and to be more convenient and usable than InnoDB. The
new features are specially designed to overcome some of InnoDB's limitations.
We choose enhancements based on customer requests, and on our best judgment of
real-world needs as a high-performance consulting company
- NDB
Cluster is the distributed database system underlying MySQL Cluster. It
can be used independently of a MySQL Server with users accessing the Cluster
via the NDB API (C++).From the MySQL Server perspective the NDB Cluster is a
Storage engine for storing tables of rows.From the NDB Cluster perspective, a
MySQL Server instance is an API process connected to the Cluster. NDB Cluster
can concurrently support access from other types of API processes including
Memcached, JavaScript/Node.JS, Java, JPA and HTTP/REST. All API processes can
operate on the same tables and data stored in the NDB Cluster.For a full
description of NDB Cluster capabilities, use-cases and resources, see the entry
for MySQL Cluster.
- TokuDB is
a storage engine for MySQL and MariaDB that is specifically designed for high
performance on write-intensive workloads. It achieves this via Fractal Tree
indexing. TokuDB is a scalable, ACID and MVCC compliant storage engine that
provides indexing-based query improvements, offers online schema modifications,
and reduces slave lag for both hard disk drives and flash memory.A Community
Edition of TokuDB was released under a modified GNU General Public License in
April 2013.
- The
IBMDB2I storage engine is designed as a fully featured
transaction-capable storage engine that enables MySQL to store its data in DB2
tables running on IBM i. With the IBMDB2I storage engine, data can be shared
between MySQL applications and applications coded for native DB2 for i
interfaces.IBMDB2I provides ACID-compliant transactions, support for foreign
key constraints, full crash recovery, radix-tree-based indexes, and the unique
ability to enable DB2 for i applications to see and update table data in real
time.
In some cases, the answer is clear. Whenever we are dealing with
some payment systems, we are obliged to use the most secure solution. We cannot
afford to loose such sensitive data. InnoDB is the way to go. If we want
full-text search, than we must choose MyISAM. Only InnoDB supports foreign key
referential integrity constraint and if we plan to use this constraint, then
the choice is clear. In many situations we must have enough experience to
choose the right engine.
Specifying and altering storage engines
·
The storage engine is specified at the time of the table creation. mysql> CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name VARCHAR(50),
-> Cost INTEGER) ENGINE='MyISAM';
·
The ENGINE keyword specifies the storage engine
used for this particular table. mysql> SHOW VARIABLES LIKE 'storage_engine';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | InnoDB |
+----------------+--------+
1 row in set (0.00 sec)
·
The default storage engine can be found in the storage_engine variable. mysql> SELECT ENGINE FROM information_schema.TABLES
-> WHERE TABLE_SCHEMA='mydb'
-> AND TABLE_NAME='Cars';
+--------+
| ENGINE |
+--------+
| InnoDB |
+--------+
1 row in set (0.00 sec)
·
This SQL statement finds out the storage engine used for a Cars
table in mydb database. We could also use SELECT
CREATE TABLE Cars SQL
statement. The information_schema is a table which stores technical
information about our tables. mysql> ALTER TABLE Cars ENGINE='MyISAM';
·
This SQL statement changes the storage engine to MyISAM. mysql> SELECT ENGINE FROM information_schema.TABLES
-> WHERE TABLE_SCHEMA='mydb'
-> AND TABLE_NAME='Cars';
+--------+
| ENGINE |
+--------+
| MyISAM |
+--------+
1 row in set (0.00 sec)
·
Now the storage engine is MyISAM.
mysql> CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name VARCHAR(50),
-> Cost INTEGER) ENGINE='MyISAM';
ENGINE keyword specifies the storage engine
used for this particular table. mysql> SHOW VARIABLES LIKE 'storage_engine';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | InnoDB |
+----------------+--------+
1 row in set (0.00 sec)
storage_engine variable. mysql> SELECT ENGINE FROM information_schema.TABLES
-> WHERE TABLE_SCHEMA='mydb'
-> AND TABLE_NAME='Cars';
+--------+
| ENGINE |
+--------+
| InnoDB |
+--------+
1 row in set (0.00 sec)
SELECT
CREATE TABLE Cars SQL
statement. The information_schema is a table which stores technical
information about our tables. mysql> ALTER TABLE Cars ENGINE='MyISAM';
mysql> SELECT ENGINE FROM information_schema.TABLES
-> WHERE TABLE_SCHEMA='mydb'
-> AND TABLE_NAME='Cars';
+--------+
| ENGINE |
+--------+
| MyISAM |
+--------+
1 row in set (0.00 sec)
No comments:
Post a Comment