Simple comparison of MySQL table types

MySQL provides many table types for selection, including MyISAM, isam, heap, berkeleydb, and InnoDB. Each table Type of MERGE table type has its own attributes and advantages. The following is a brief discussion.

MyISAM Table type:

MyISAM
Table (type = MyISAM) is an extension of the isam type and has many optimizations and enhancements. Is the default table Type of MySQL. MyISAM optimizes the compression ratio and speed, and
It can be easily transplanted between different operating systems and platforms. MyISAM supports large table files (larger than 4 GB), allowing index of blob and text columns, supporting the use of key prefixes and the use of complete
Key search records table data and table index files can be stored in different locations, or even in different file systems. Even for tables with a considerable number of insert, update, and delete operations, the smart anti-fragment logic ensures its high
Performance collaboration.

Isam table Type:


The isam table (type = isam) is similar to the MyISAM table, but it does not have many enhancements in the MyISAM format. Therefore, it cannot provide better optimization and execution efficiency as the MyISAM type does. Because isam indexes cannot be compressed
It overcomes less system resources than the same index in MyISAM. Isam indexes require more disk space
This is very problematic for small environments like this site. Haha.
Like MyISAM, an isam table can be of a fixed or variable length, but the maximum key length in the isam format is relatively small. Tables processed in the isam format cannot be larger than 4 GB, in addition, tables cannot be migrated between different platforms.
. In addition, isam tables are easy to split, which reduces the query speed and limits data/index compression.
.
Help table Type:


Heap table (type = heap) is a memory table that uses a Fast Hash index (when an insert query is run, standalone evaluation points that the heap table is at least 30% faster than the MyISAM Table), so temporary tables can be optimized. The access rules and usage of MyISAM and isam tables are the same. The stored data only exists in the life cycle of the MySQL server.
If the MySQL server crashes or is shut down, the data will disappear. Although heap tables have performance advantages, they are not frequently used in practice due to their temporary nature and some other functional limitations.
The size of the heap table is limited only by the available memory on the system. MySQL is very smart and has built-in protection to prevent unintentional use of all available memory. Therefore, we do not have to worry that the memory will be exhausted by the heap table. The heap table does not support blob or text columns and cannot exceed the size specified by the max_heap_table_size variable.
Berkeleydb table Type:


Berkeleydb
Table (type = bdb) is developed to meet the increasing demand of MySQL developers for transaction security tables. The berkeleydb table has many interesting geese, including commit and rollback operations.
Multi-user concurrent access, checkpoint, secondary index, crash recovery through logs, continuous and key-coded access to data, etc. This facilitates complicated and Feasible Choice of transaction-based SQL.
However, the berkeleydb table also has some restrictions. Let's take a look at the following:

It is difficult to move the table (during creation, the table path is hard-coded in the table file). It cannot compress the table index, and its table is usually better than the corresponding MyISAM table, because the InnoDB format can largely replace the berkeleydb format

InnoDB table Type:


InnoDB
Table (type = InnoDB), is a fully compatible acid (atomicity, consistency, independence, and durability of transactions), efficient table fully supports MySQL transaction processing and does not
Btwagkyaakftntce. Fine-grained (Row-level and table-level) locks increase the take-away degree of MySQL transaction processing, and also support non-locked read Operations (previously only included in Oracle)
And multi-version features.
Asynchronous input/output and a series of read buffers will increase the data retrieval speed and optimize the file and manage the memory. You can automatically create hash indexes in the memory to improve the performance and use buffering to improve the reliability and speed of database operations. The hate of InnoDB tables cannot be comparable to that of MyISAM, or even exceed MyISAM.
It is completely portable in different operating systems and architectures. Because it remains consistent (MySQL makes them more robust by checking for errors at startup and fixing errors ).Supports external key, commit, rollback, and rollback operations to make it the most comprehensive table style in MySQL

.
MERGE table Type:

Merge
A table (type = merge) is a virtual table created by combining multiple MyISAM tables into a single table. Only tables with identical table structures can be combined.
Field types or indexes cannot be combined successfully. A merge table uses an index that makes up a table and cannot maintain its own index. In some cases, it can increase the speed. Allow
The Select, delete, and update operations are useful when you need to put data from different tables together to improve the connection performance or perform searches in a series of tables. Handling Large
When using MyISAM tables, we can "package" them by pressing the grain or using the myisampack utility included in MySQL release to reduce the space to overcome these tables.
Myisampack creates a relatively small read-only table without causing any high performance overhead when using smart compression.