Comparison of MySQL database engines: Difference between revisions

Revision as of 11:04, 13 November 2020

Latest revision as of 04:30, 29 July 2025

(27 intermediate revisions by 13 users not shown)

Line 1:

{{short description|none}}

{{refimprove|date=March 2010}}

This is a comparison between notable database engines for the [[MySQL]] [[database management system]] (DBMS). A '''database engine''' (or "storage engine") is the underlying software component that a DBMS uses to [[create, read, update and delete]] (CRUD) [[data]] from a [[database]].

{{table alignment}}

{| class="wikitable sortable col6center col7center"

|-

! Name !! Vendor !! License !! [[Database transaction#Transactional databases|Transactional]] !! Under active development !! MySQL versions !! MariaDB versions<ref>{{Cite web |title=Storage Engines |url=https://mariadb.com/kb/en/storage-engines/ |access-date=2024-01-25 |website=MariaDB KnowledgeBase}}</ref>

|-

| [[MySQL Archive|Archive]] || Oracle || GPL || {{No}} || {{Yes}} || 5.0 – present || 5.1 – present

|-

| [[Aria (storage engine)|Aria]] || [[MariaDB]] || GPL || {{No}} || {{Yes}} || {{N/A}} || 5.1 – present

|-

| [[Berkeley DB]] || Oracle || [[GNU Affero General Public License|AGPLv3]] || {{Yes}} || {{No}} || ? – 5.0 || {{N/A}}

|-

| [[BLACKHOLE]] || Oracle || GPL || {{No}} || {{Yes}} || 5.0 – present || 5.1 – present

|-

| [[CONNECT (storage engine)|CONNECT]] || [[MariaDB]] || GPL || {{No}} || {{Yes}} || {{N/A}} || 10.0 – present

|-

| [[Comma-separatedCSV (storage valuesengine)|CSV]] || Oracle || GPL || {{No}} || {{Yes}} || 5.0 – present || 5.1 – present

|-

| [[Falcon (storage engine)|Falcon]] || Oracle || GPL || {{Yes}} || {{No}} || {{dunno}} || {{N/A}}

|-

| [[MySQL Federated|Federated]] || Oracle || GPL || {{dunno}} || {{No}} || 5.0 – present || 5.1 – {{dunno}}

|-

| [[InfiniDBFederatedX]] || Calpont[[MariaDB]] || GPL || {{Yes}} || {{No}} || {{N/A}} || ? – present

|-

| [[InnoDBInfiniDB|ColumnStore (formerly InfiniDB)]] || Oracle Calpont|| GPL || {{Yes}} || {{Yes}} || {{N/A}} || 10.5.4 – present

|-

| [[MyISAMInnoDB]] || Oracle || GPL || {{NoYes}} || {{NoYes}} || 3.23 – present || 5.1 – present<ref name="mdb_innodb" />

|-

| [[MyRocksMEMORY (storage engine)|MEMORY]] || FacebookOracle || GPLv2GPL || {{YesNo}} || {{Yes}} || 3.23 – present || 5.1 – present

|-

| [[MySQLMroonga Cluster|NDB]]| Groonga Project || OracleGPL || GPLv2{{No}} || {{Yes}} || {{YesN/A}} || 10.0 – present

|-

| [[S3 (storage engine)|S3MyISAM]] || [[MariaDB]]Oracle || GPL || {{No}} || {{YesNo}} || 3.23 – present || 5.1 – present

|-

| [[SPIDER (storage engine)|SPIDERMyRocks]] || Kentoku ShibaFacebook || GPLGPLv2 || {{Yes}} || {{Yes}} || {{N/A}} || 10.2 – present

|-

| [[TokuDBMySQL Cluster|NDB]] || PerconaOracle || ModifiedGPLv2 GPL|| {{Yes}} || {{Yes}} || {{Nodunno}} || {{N/A}}

|-

| [[XtraDBOQGRAPH]] || PerconaOracle || GPLGPLv2 || {{YesNo}} || {{YesNo}} || {{N/A}} || 5.2 – present

|-

| [[S3 (storage engine)|S3]] || [[MariaDB]] || GPL || {{No}} || {{Yes}} || {{N/A}} || 10.5 – present

|-

| SEQUENCE || MariaDB || GPL || {{No}} || {{Yes}} || {{N/A}} || 10.0 – present

|-

| [[Sphinx (search engine)#SphinxSE|Sphinx]] || Sphinx Technologies Inc. || GPL || {{No}} || {{No}} || {{N/A}} || 5.2 – present

|-

| [[SPIDER (storage engine)|SPIDER]] || Kentoku Shiba || GPL || {{Yes}} || {{Yes}} || {{N/A}} || 10.0 – present

|-

| [[TempTable]] || Oracle || GPL || {{No}} || {{Yes}} || 8.0 – present || {{N/A}}

|-

| [[TokuDB]] || Percona || Modified GPL || {{Yes}} || {{No}} || {{N/A}} || 5.5 – 10.6<ref>{{Cite web |title=TokuDB |url=https://mariadb.com/kb/en/tokudb/ |access-date=2024-01-25 |website=MariaDB KnowledgeBase}}</ref>

|-

| [[XtraDB]] || Percona || GPL || {{Yes}} || {{Yes}} || {{N/A}} || 5.1 – 10.1<ref name="mdb_innodb">{{Cite web |title=InnoDB Versions |url=https://mariadb.com/kb/en/innodb-versions/ |access-date=2024-01-25 |website=MariaDB KnowledgeBase}}</ref>

|}

== References ==

==Comparison between InnoDB and MyISAM==

{{reflist}}

#InnoDB recovers from a crash or other unexpected shutdown by replaying its logs. MyISAM must fully scan and repair or rebuild any indexes or possibly tables which had been updated but not fully flushed to disk. Since the InnoDB approach is approximately fixed time while the MyISAM time grows with the size of the data files, InnoDB offers greater availability as database sizes grow.

#InnoDB, with innodb_flush_log_at_trx_commit set to 1, flushes the transaction log after each transaction, greatly improving reliability.<ref name=trx_commit>[http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit MySQL 5.5 Reference Manual - InnoDB Startup Options and System Variables]</ref> MyISAM has to be run on top of a fully [[Journaling file system|journaled filesystem]], such as [[ext4]] mounted with data=journal, to provide the same resilience against data file corruption. (The journal can be put on an [[Solid-state drive|SSD]] device for improved MyISAM performance, similarly, the InnoDB log can be placed on a non-journaled filesystem such as [[ext2]] running on an SSD for a similar performance boost. Reliability is not sacrificed in either case.)

#InnoDB can be run in a mode where it has lower reliability but in some cases higher performance. Setting innodb_flush_log_at_trx_commit to 0 switches to a mode where transactions are not committed to disk before control is returned to the caller. Instead, disk flushes happen on a timer.<ref name=trx_commit/>

#InnoDB automatically groups together multiple concurrent inserts and flushes them to disk at the same time.<ref>{{Cite web |url=http://dev.mysql.com/doc/refman/5.5/en/innodb-performance-group_commit.html |title=MySQL 5.5 Reference Manual - InnoDB Group Commit |access-date=2011-10-08 |archive-url=https://web.archive.org/web/20111104041340/http://dev.mysql.com/doc/refman/5.5/en/innodb-performance-group_commit.html |archive-date=2011-11-04 |url-status=dead }}</ref> MyISAM relies on the filesystem block cache for caching reads to the data rows and indexes, while InnoDB does this within the engine itself, combining the row caches with the index caches.<ref>{{cite web|url=http://dev.mysql.com/doc/refman/5.5/en/innodb-storage-engine.html|title=MySQL 5.5 Reference Manual - The InnoDB Storage Engine|accessdate=28 May 2015}}</ref>

#InnoDB will store rows in [[primary key]] order if present, else first [[unique key]] order. This can be significantly faster if the key is chosen to be good for common operations.{{Citation needed|date=May 2009}} If there is no primary key or unique key InnoDB will use an internally generated unique integer key and will physically store records in roughly insert order, as MyISAM does. Alternatively, an autoincrementing primary key field can be used to achieve the same effect.

#InnoDB provides updatable [[Lempel–Ziv–Welch|LZW]] compressed page storage for both data and indexes. MyISAM compressed tables can't be updated.<ref>[http://dev.mysql.com/doc/refman/5.5/en/myisampack.html MySQL 5.5 Reference Manual - myisampack — Generate Compressed, Read-Only MyISAM Tables]</ref>

#When operating in fully [[ACID]]-compliant modes, InnoDB must do a flush to disk at least once per transaction, though it will combine flushes for inserts from multiple connections. For typical hard drives or arrays, this will impose a limit of about 200 update transactions per second. For applications which require higher transaction rates, disk controllers with write caching and battery backup will be required in order to maintain transactional integrity. InnoDB also offers several modes which reduce this effect, naturally leading to a loss of transactional integrity guarantees, though still retaining greater reliability than MyISAM. MyISAM has none of this overhead, but only because it does not support transactions.

#MyISAM uses table-level locking on updates and deletes to any existing row, with an option to append new rows instead of taking a lock and inserting them into free space. InnoDB uses row-level locking. For large database applications where many rows are often updated, row-level locking is crucial because a single table-level lock significantly reduces concurrency in the database.

#Both InnoDB and MyISAM support [[Full text search|full-text search]], with InnoDB gaining full-text index support in MySQL 5.6.4,<ref>{{cite web|url=https://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-4.html|title=Changes in MySQL 5.6.4 (2011-12-20, Milestone 7)|publisher=Oracle|date=December 12, 2011}}</ref> but the results can be notably different.<ref>{{cite web|url=http://www.mysqlperformanceblog.com/2013/03/04/innodb-full-text-search-in-mysql-5-6-part-2-the-queries/|title=InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!|publisher=MySQL Performance Blog|date=March 4, 2013}}</ref>

== References ==

{{Reflist}}

==External links==

* [httphttps://dev.mysql.com/doc/mysqlrefman/8.0/en/myisam-storage-engine.html MySQL Documentation on MyISAM Storage Engine]

* [http://www.geeksww.com/tutorials/database_management_systems/mysql/installation/mysql_tablecache_informationschema_and_open_files_limit.php MyISAM's open files limit and table-cache problem explained]

* [http://www.mysqlperformanceblog.com/2006/06/17/using-myisam-in-production/ The article about problems which will occur in using MyISAM]