Summary: MySQL has multiple storage engines, each with its own advantages and disadvantages. You can choose the best one to use: MyISAM, InnoDB, MERGE, MEMORY(HEAP), BDB(BerkeleyDB), EXAMPLE, FEDERATED, ARCHIVE, CSV, BLACKHOLE.

MySQL has multiple storage engines, each with its own advantages and disadvantages. You can choose the best one to use:

MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。

MySQL supports several storage engines as handlers for different table types. MySQL storage engines include engines that handle transaction-safe tables and engines that handle non-transaction-safe tables:

• MyISAM manages non-transactional tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. MyISAM is supported in all MySQL configurations and is the default storage engine unless you configure MySQL to use another engine by default.

• The MEMORY storage engine provides "in-memory" tables. The MERGE storage engine allows a collection of identically processed MyISAM tables to be handled as a single table. Like MyISAM, the MEMORY and MERGE storage engines handle non-transactional tables, and both engines are included in MySQL by default.

  Note: The MEMORY storage engine was formerly known as the HEAP engine.

• The InnoDB and BDB storage engines provide transaction-safe tables. BDB is included in the MySQL-Max binary distributions released for operating systems that support it. InnoDB is also included by default in all MySQL 5.1 binary distributions. You can configure MySQL to enable or disable either engine as you prefer.

• The EXAMPLE storage engine is a "stub" engine that does nothing. You can create tables with this engine, but no data is stored in or retrieved from it. The purpose of this engine is to serve as an example in the MySQL source code, demonstrating how to start writing a new storage engine. Likewise, its main interest is for developers.

• NDB Cluster is the storage engine used by MySQL Cluster to implement tables partitioned across multiple computers. It is provided in the MySQL-Max 5.1 binary distributions. This storage engine is currently supported only on Linux, Solaris, and Mac OS X. In future MySQL distributions, we intend to add support for this engine on other platforms, including Windows.

• The ARCHIVE storage engine is used to store large amounts of data without indexes, with a very small storage footprint.

• The CSV storage engine stores data in text files in comma-separated format.

• The BLACKHOLE storage engine accepts but does not store data, and retrieval always returns an empty set.

• The FEDERATED storage engine stores data in a remote database. In MySQL 5.1, it only works with MySQL, using the MySQL C Client API. In future distributions, we intend to make it connect to other data sources using other drivers or client connection methods.

The more commonly used ones are MyISAM and InnoDB

How to choose between MySQL storage engines MyISAM and InnoDB?

Although the storage engines in MySQL are not just MyISAM and InnoDB, these two are the commonly used ones. Some webmasters may not have paid attention to MySQL's storage engines. In fact, the storage engine is also an important point in database design. So which storage engine should a blog system use?

Below we look at the differences between the two storage engines.

• First, InnoDB supports transactions, while MyISAM does not. This is very important. A transaction is an advanced processing method. For example, in a series of insertions, deletions, and modifications, if any error occurs, it can be rolled back and restored, but MyISAM cannot do this.
• Second, MyISAM is suitable for applications focused on queries and inserts, while InnoDB is suitable for applications with frequent modifications and higher security requirements.
• Third, InnoDB supports foreign keys, while MyISAM does not.
• Fourth, the default storage engine for MySQL versions before 5.1 is MyISAM, and for versions after 5.1 it is InnoDB.
• Fifth, InnoDB does not support FULLTEXT type indexes.
• Sixth, InnoDB does not save the row count of a table. For example, when executing select count(*) from table, InnoDB needs to scan the entire table to calculate how many rows there are, but MyISAM can simply read out the saved row count. Note that when the count(*) statement includes a WHERE condition, MyISAM also needs to scan the entire table.
• Seventh, for auto-increment fields, InnoDB must contain an index that includes only that field, but in MyISAM tables, a composite index can be created together with other fields.
• Eighth, when clearing the entire table, InnoDB deletes row by row, which is very inefficient. MyISAM will rebuild the table instead.
• Ninth, InnoDB supports row locking (in some cases it still locks the entire table, such asupdate table set a=1 where user like '%lee%'

Based on the above nine differences and the characteristics of personal blogs, it is recommended that personal blog systems use MyISAM, because in a blog the main operations are reading and writing, and there are few chain operations. Therefore, choosing the MyISAM engine makes the efficiency of opening pages of your blog higher than blogs using the InnoDB engine. Of course, this is only a personal suggestion; most blogs should still choose carefully based on actual circumstances.

Some notes on choosing between MyISAM and InnoDB:

MYISAM and InnoDB are two storage engines provided by the MySQL database. Each has its own advantages and disadvantages. InnoDB supports advanced relational database features such as transactions and row-level locking, which MyISAM does not support. MyISAM has better performance and occupies less storage space. Therefore, which storage engine to choose depends on the specific application.

If your application definitely requires transactions, you should undoubtedly choose the InnoDB engine. However, note that InnoDB's row-level locking is conditional. When the WHERE condition does not use a primary key, the entire table will still be locked. For example, a DELETE statement such as DELETE FROM mytable.

If your application has high query performance requirements, you should use MyISAM. MyISAM separates indexes from data, and its indexes are compressed, allowing better use of memory. Therefore, its query performance is significantly better than InnoDB. The compressed indexes also save some disk space. MyISAM supports full-text indexing, which can greatly optimize the efficiency of LIKE queries.

Some people say that MyISAM can only be used for small applications, but this is actually just a prejudice. If the data volume is relatively large, this should be solved by upgrading the architecture, such as table partitioning and database sharding, rather than relying solely on the storage engine.

Some other statements:

Nowadays, InnoDB is generally chosen. The main reason is that MyISAM uses table-level locking, which causes read-write serialization issues and table lock contention, resulting in low concurrency efficiency. MyISAM is generally not selected for read-write intensive applications.

Regarding the default storage engine of the MySQL database:

MyISAM and InnoDB are two storage engines of MySQL. If it is a default installation, it should be InnoDB. You can find default-storage-engine=INNODB in the my.ini file. Of course, you can specify the corresponding storage engine when creating a table. You can see the relevant information by executing show create table xx.

Comparison of InnoDB and MyISAM in MySQL

MyISAM：

Each MyISAM table is stored on disk as three files. The first file's name starts with the table name, and the extension indicates the file type. The .frm file stores the table definition. The data file has the extension .MYD (MYData).

MyISAM tables can be compressed, and they support full-text search. They do not support transactions or foreign keys. If a transaction rolls back, it will cause an incomplete rollback and lack atomicity. When performing UPDATE, a table lock is used, so the concurrency is relatively small. If you execute a large number of SELECT statements, MyISAM is a better choice.

MyISAM separates indexes from data, and the indexes are compressed, which significantly improves memory utilization. More indexes can be loaded. InnoDB, on the other hand, tightly binds indexes and data together without compression, making InnoDB considerably larger than MyISAM.

MyISAM caches indexes in memory, not data. InnoDB caches data in memory. Relatively speaking, the larger the server memory, the greater the advantage InnoDB can leverage.

Advantages:Querying data is relatively fast, suitable for a large number of SELECT statements, and supports full-text indexing.

Disadvantages:Does not support transactions, does not support foreign keys, has low concurrency, and is not suitable for a large number of UPDATE statements.

InnoDB：

This type is transaction-safe. It has the same characteristics as the BDB type, and they also support foreign keys. InnoDB tables are very fast. They have even richer features than BDB, so if you need a transaction-safe storage engine, it is recommended to use it. Row-level locking is used during UPDATE, so concurrency is relatively high. If your data involves a large number of INSERT or UPDATE operations, you should use InnoDB tables for performance reasons.

Advantages:Supports transactions, supports foreign keys, has high concurrency, and is suitable for a large number of UPDATE statements.

Disadvantages:Querying data is relatively fast, but not suitable for a large number of SELECT statements.

For InnoDB tables that support transactions, the main reason affecting speed is that the AUTOCOMMIT default setting is enabled, and the program does not explicitly call BEGIN to start a transaction, causing every inserted row to be automatically committed, which seriously affects speed. You can call BEGIN before executing SQL, and multiple SQL statements form one transaction (even if autocommit is enabled), which will greatly improve performance.

The basic differences are:The MyISAM type does not support advanced processing such as transaction processing, while the InnoDB type supports it.

MyISAM-type tables emphasize performance, and their execution speed is faster than InnoDB-type tables, but they do not provide transaction support. InnoDB provides transaction support and advanced database features such as foreign keys.

Other comparisons:

MyIASM is a new version of the ISAM table, with the following extensions:

• Binary-level portability.
• NULL column indexing.
• Less fragmentation for variable-length rows than ISAM tables.
• Supports large files.
• Better index compression.
• Better key statistical distribution.
• Better and faster auto_increment handling.

The following are some differences in details and specific implementation:

• 1. InnoDB does not support FULLTEXT type indexes.
• 2. InnoDB does not store the exact number of rows in a table. That is, when executing select count(*) from table, InnoDB has to scan the entire table to calculate the number of rows, while MyISAM only needs to read the saved row count. Note that when the count(*) statement includes a WHERE condition, the operation on both tables is the same.
• 3. For AUTO_INCREMENT fields, InnoDB must include an index containing only that field, but in MyISAM tables, a composite index can be created together with other fields.
• 4. When executing DELETE FROM table, InnoDB does not rebuild the table; it deletes rows one by one.
• 5. The LOAD TABLE FROM MASTER operation does not work on InnoDB. The solution is to first change the InnoDB table to a MyISAM table, import the data, and then change it back to an InnoDB table. However, this is not applicable to tables that use additional InnoDB features (such as foreign keys).

In addition, InnoDB's row-level locking is not absolute. If MySQL cannot determine the range to scan when executing a SQL statement, InnoDB tables will also lock the entire table, for example: update table set num=1 where name like "%aaa%".

No table type is omnipotent. Only by appropriately selecting the table type according to the business type can MySQL's performance advantages be maximized.

Update comparison between InnoDB and MyISAM:

InnoDB's data organization is a B+ tree built according to the primary key. If no primary key is explicitly defined, InnoDB will select a NOT NULL UNIQUE key as the primary key. If there is still none, InnoDB will create a 6-byte primary key. The primary key index points to a page, not a specific row position.

A non-incrementing primary key makes insertion very slow, for example using a mobile phone number or ID card number as the primary key, so make good use of AUTO_INCREMENT.

A large table is not scary; the scary thing is COUNT or a high-offset LIMIT. You can replace a large LIMIT big with LIMIT max_id, xxxxx.

Limit 0 1000 | limit 1001 1000 | limit 2001 1000

Limit 0 1000 | where id>max_id1 limit 1000 | where id>max_id2 limit 1000

For InnoDB, partitioning a table by a certain column and hoping to improve performance on a single server is meaningless.

Insert speed and query speed are sometimes irreconcilable contradictions.

It is wrong to say that InnoDB is not suitable for COUNT. MyISAM is equally slow. It is just that MyISAM caches the row count of the entire table, so counting the whole table is fast. If there is a query condition and it is not a primary key query, there is no difference. The reason why primary key COUNT is slow is that InnoDB is organized by primary key. When counting by primary key, data is loaded.

InnoDB's page-based storage makes it easier to perform full-table caching and hot backups.

If a table has many indexes, then InnoDB's update speed is greater than MyISAM's. Because InnoDB's secondary indexes are associated with the table's primary key, which is a logical value, while all MyISAM indexes are associated with the physical location of the data. During updates, the physical location of the data may change. If it changes, all indexes need to be updated.

InnoDB does not store the exact number of rows in a table. That is, when executing select count(*) from table, InnoDB has to scan the entire table to calculate the number of rows, while MyISAM only needs to read the saved row count. Note that when the count(*) statement includes a WHERE condition, the operation on both tables is the same.

Original address: https://my.oschina.net/junn/blog/183341