Seven Mysql table types

To learn about the Mysql database and what Mysql table types are required, we will introduce you to the seven Mysql table types below, hoping to help you learn about the Mysql table types.

MySQL, as the most popular free database service engine, has been popular for a long time. However, some people may not know much about the internal environment of MySQL, especially those mechanisms for concurrent processing. Today, let's take a look at the Mysql table types and their simple properties.

Up to now, MySQL provides users with seven Mysql table types, including DBD, HEAP, ISAM, MERGE, MyIAS, InnoDB, and Gemeni. Among them, DBD and InnoDB are transaction security tables, while others are transaction non-security tables.
 
DBD
The Berkeley DB (DBD) Table supports transaction processing and is developed by Sleepycat. It provides the long-awaited MySQL feature-transaction control. Transaction control is a very valuable feature in any database system, because they ensure that a group of commands can be successfully executed or rolled back.

HEAP
The HEAP table is the fastest table to access data in MySQL. This is because they use a hash index stored in the dynamic memory, but if MySQL or the server crashes, the memory data will be lost.

ISAM
ISAM tables are the default table types of earlier MySQL versions until MyIASM is developed. We recommend that you do not use it any more.

MERGE
MERGE is an interesting new type that appears after 3.23.25. A merge table is actually a set of MyISAM tables that are merged into a table mainly for efficiency consideration, this not only improves the speed, search efficiency, and repair efficiency, but also saves disk space.

MyIASM
MyIASM is based on the IASM code and should be considered a derivative of IASM, but it adds a lot of useful extensions. It is the default data table Type of MySQL. Based on the traditional ISAM type, ISAM is the Sequential Access Method of Indexed Sequential Access Method with indexes, it is a standard method for storing records and files. Compared with other storage engines, MyISAM provides most tools for checking and repairing tables. ISAM tables can be compressed and support full-text search. However, they are insecure and do not support foreign keys. If the transaction is rolled back, incomplete rollback will be caused and it is not atomic. Therefore, if you ignore transactions and access concurrency and need to execute a large number of SELECT search statements, MyISAM will be the best choice.

InnoDB
InnoDB is a relatively new data table Type launched after MySQL 4.0, Which is transaction security. It has the same features as the BDB type and supports foreign keys. InnoDB tables are faster than BDB tables. Therefore, if you need a transaction-safe storage engine, we recommend that you use it. If you execute a large number of INSERT or UPDATE operations on your data, InnoDB tables should also be used for performance considerations. For InnoDB tables that support transactions, the main cause of the impact on the speed is that AUTOCOMMIT is enabled by default, and the program does not explicitly call BEGIN to start the transaction, as a result, each inserted entry is automatically submitted, seriously affecting the speed. You can call begin before executing the SQL statement. Multiple SQL statements can form a transaction even if autocommit is enabled. This will greatly improve the performance.

Gemeni
Gemeni table has been released since MySQL 4.0. However, up to now, there have been few introductions to it, and even fewer applications. We will not introduce it for the time being.

MySQL has many data table types, including MyISAM and InnoDB.
The two types have their own advantages and disadvantages. You need to select a suitable one based on the actual situation. MySQL supports setting different types for different tables. The following is a simple comparison:
MyISAM Table type is a mature and stable table type, but MyISAM does not support some features.

MySQL connection query Overview

Optimization instance for MySQL random Query

MySQL query cache Variables

How to enable MySQL slow Query

Mysql multi-Table query implementation