MySQL chooses the right storage engine

When it comes to databases, one of the most frequently asked questions is how to choose a storage engine in MySQL that meets current business needs. MySQL supports many storage engines, so what are they and how to use them elegantly?

Reasons for dividing the engine

In the file system, MySQL saves each database (also called schema) as a subdirectory under the data directory. When creating a table, MySQL will create a .frm file with the same name as the table in the database subdirectory to save the table definition. For example, if you create a table named DebugTable, MySQL will save the table definition in the DebugTable.frm file.

Because MySQL uses file system directories and files to store database and table definitions, case sensitivity is platform-dependent. On Windows systems, upper and lower case characters are not case sensitive; on Unix-like systems, they are case sensitive. Different storage engines store data and indexes in different ways, but the table definition is handled uniformly in the MySQL service layer wk.

View Support Engines

To find out which engines are supported in MySQL, you can use the following command:

show engines;

The results are as follows (MySQL version: Ver 8.0.19):

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

Storage Engine Classification

MySQL storage engines are classified into MyISAM, InnoDB, Memory, Merge, etc. You can see the supported engines listed in the table above, but the most commonly used engines are MyISAM and InnoDB. For the storage engines mentioned above, the following table compares them:

Differences between MyISAM and InnoDB

The main difference between the two types is that InnoDB supports transactions and foreign key and row-level locks.

1. InnoDB can use transaction logs to recover data errors caused by program crashes or unexpected terminations; however, when MyISAM encounters an error, it must perform a complete scan before rebuilding the index or correcting errors that were not written to the hard disk.
2. The repair time of InnoDB is generally fixed, but the repair time of MyISAM is proportional to the amount of data.
3. Relatively speaking, as the amount of data increases, InnoDB will have better stability.
4. MyISAM must rely on the operating system to manage read and write caches, while InnoDB has its own read and write cache management mechanism. (InnoDB will not immediately hand over the modified data pages to the operating system) Therefore, in some cases, InnoDB data access will be more efficient than MyISAM.
5. InnoDB currently does not support the compression and terse row formats provided by MyISAM, so it uses a lot of hard disk and cache.
6. When the operation is fully compatible with ACID (transactions), although InnoDB will automatically merge several connections, each transaction must still be written to the hard disk at least once. Therefore, for some hard disks or disk arrays, it will cause a transaction processing limit of 200 times per second. If you want to achieve higher performance and maintain transaction integrity, you must use disk caching and battery backup. Of course, InnoDB also provides several modes with lower performance impact, but these modes will also reduce the integrity of transactions. MyISAM does not have this problem, but this is not because it is more advanced, it is just because it does not support transactions.
   

Application Scenario

• MyISAM manages nontransactional tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. If your application needs to execute a large number of SELECT queries, then MyISAM is a better choice.
• InnoDB is designed for transaction processing applications and has many features, including ACID transaction support. If a large number of INSERT or UPDATE operations need to be performed in the application, InnoDB should be used to improve the performance of multi-user concurrent operations.

The above is the details of the selection of MySQL storage engine. For more information about MySQL storage engine, please pay attention to other related articles on 123WORDPRESS.COM!