The difference of MySQL storage engine and its startup method

What is the storage engine?

Data in MySQL is stored in files (or memory) in a variety of different technologies. Each of these technologies uses different storage mechanisms, indexing techniques, locking levels, and ultimately provides a wide range of different capabilities and capabilities. By selecting different technologies, you can gain additional speed or functionality to improve the overall functionality of your application.

For example, if you are studying a large amount of temporary data, you may need to use a memory storage engine. The memory storage engine is able to store all of the tabular data in memory. Alternatively, you may need a database that supports transaction processing (to ensure that the data is rolled back when the transaction is unsuccessful).

These different technologies and associated functions are called storage engines (also known as table types) in MySQL. MySQL defaults to a number of different storage engines, which can be set up in advance or enabled in the MySQL server. You can choose the storage engine that applies to servers, databases, and tables to provide you with the greatest flexibility in choosing how to store your information, how to retrieve it, and what performance and functionality you want your data to combine.

The flexibility to choose how to store and retrieve your data is the main reason why MySQL is so popular. Other database systems, including most commercial choices, support only one type of data store. Unfortunately, other types of database solutions take the "one size meets all needs" approach, which means that you either sacrifice some performance or you can spend hours or even days tweaking your database in detail. With MySQL, we just need to modify the storage engine we use.

In this article, we're not going to focus on the technical aspects of the different storage engines (although we inevitably have to look at some aspects of these factors), instead we'll focus on what these different engines are best suited to and how to enable different storage engines. To do this, we have to understand some basic issues before we introduce the specifics of each storage engine.

How to determine which storage engines are available

You can get a list of available engines using the command for the display engine in MySQL (assuming the MySQL server 4.1.2 version above).

Mysql> show engines;
+------------+---------+----------------------------------------------------+
| Engine | Support | Comment |
+------------+---------+-----------------------------------------------------+
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance |
| HEAP | YES | Alias for MEMORY |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables |
| MERGE | YES | Collection of identical MyISAM tables |
| Mrg_myisam | YES | Alias for MERGE |
| ISAM | NO | Obsolete storage engine, now replaced by MyISAM |
| Mrg_isam | NO | Obsolete storage engine, now replaced by MERGE |
| InnoDB | YES | Supports transactions, Row-level locking, and foreign keys |
| Innobase | YES | Alias for INNODB |
| BDB | NO | Supports transactions and Page-level locking |
| BerkeleyDB | NO | Alias for BDB |
| Ndbcluster | NO | Clustered, fault-tolerant, memory-based tables |
| NDB | NO | Alias for Ndbcluster |
| EXAMPLE | NO | Example Storage Engine |
| ARCHIVE | NO | Archive Storage Engine |
| CSV | NO | CSV Storage Engine |
+------------+---------+-------------------------------------------------------+

Rows in Set (0.01 sec) This table shows the full list of available database engines and whether these engines are supported in the current database server.

For previous versions of MySQL 4.1.2, you can use mysql> show variables like "have_%" (display variables similar to "have_%"):

mysql> show variables like "have_%";
+------------------+----------+
| Variable_name | Value |
+------------------+----------+
| have_bdb | YES |
| have_crypt | YES |
| have_innodb | DISABLED |
| have_isam | YES |
| have_raid | YES |
| have_symlink | YES |
| have_openssl | YES |
| have_query_cache | YES |
+------------------+----------+
8 rows in set (0.01 sec)

You can set the engine that is available in the MySQL installation software by modifying the options in the settings script. If you are using a pre-packaged MySQL binary release software, then the software contains a common engine. However, it should be noted that if you want to use some of the less commonly used engines, especially the CSV, rchive (archive) and blackhole (black hole) engines, you will need to manually recompile the MySQL source code.