Mysql Data Table type

Like large database systems such as Oracle and SQL Server, MySQL is also a single-process, multi-threaded database.

An important feature that distinguishes MySQL from other database systems is the support for plug-in storage engines.

What is a storage engine (data table type)

The storage engine explains how to store data, create indexes for the stored data, and update and query data.

Because data in relational databases is stored as tables, the storage engine can also be called the table type (that is, the type of storing and operating the table ). There is only one storage engine in Oracle, SQL Server, and other databases. All data storage management mechanisms are the same. MySQL databases provide multiple storage engines. You can select different storage engines for data tables based on your needs. You can also write your own storage engines based on your needs.

MySQL supports five different table types: bdb, heap, isam, merge, and MyISAM.

Among them, the bdb type belongs to one category separately, which is called "transaction-safe", and the other table types belong to the second category, which is called "non-transaction-safe ).

1. isam data table

Isam is the only table Type of MySQL Branch before MySQL 3.23 and is out of date. The myiasm processing program gradually replaces the isam processing program. This old table type is no longer used by anyone. Storage of data tables on hard disks: iasm frm ISD ism

2. myiasm data table

This is the default data table type used by MySQL. Its advantages are:

• If the host operating system supports large-sized files, the data table length can be very large and more data can be accessed.
• The data table content is independent of the hardware, that is, the data table can be copied between machines at will.
• Improved indexing functions
• Provides better index key compression performance
• Auto_increment
  Enhanced capabilities
• Improved the data table integrity check mechanism
• Supports full Fulltext search

How to store data tables on a hard disk: MyISAM frm MYD myi

1. MyISAM: This engine was first provided by MySQL. No matter what MyISAM table is, it currently does not support the functions of transactions, row-level locks, and foreign key constraints. This engine can be divided into three types: static MyISAM, dynamic MyISAM, and compressed MyISAM:

   • Static MyISAM: if the length of each data column in the data table is pre-fixed, the server automatically selects this table type. Because each record in a data table occupies the same space, the efficiency of table access and update is very high. When data is damaged, recovery is easier.

   • Dynamic MyISAM: If the varchar, xxxtext, or xxxblob fields appear in the data table, the server automatically selects this table type. Compared with static MyISAM, this table has a small storage space, but because the length of each record is different, after data is modified multiple times, the data in the data table may be stored discretely in the memory, this leads to a decrease in execution efficiency. At the same time, many fragments may occur in the memory. Therefore, this type of table often uses the optimize table command or optimization tool for fragment.
   • Compression MyISAM: the two types of tables mentioned above can be compressed using myisamchk. This type of table further reduces the storage used, but the table cannot be modified after compression. In addition, because the data is compressed, such tables must be decompressed first.

3. Merge data table

This is a way to organize a myiasm data table with the same structure into a logical unit.

MyISAM merge engine: This type is a variant of the MyISAM type. Merging tables combines several identical MyISAM tables into a virtual table. It is often used in logs and data warehouses.

Storage of data tables on hard disks: Merge frm MRG

4. Heap data table

This is a data table that uses memory, and the length of each data row is fixed. These two features make it very fast to search for such data tables. As a temporary data table, heap is useful in certain situations.

Storage of data tables on hard disks: heap FRM

Memory (HEAP): This type of data table only exists in memory. It uses hash indexes, so the data access speed is very fast. Because it exists in the memory, this type is often used in temporary tables.

5. bdb data table

This type of data table supports transaction processing and has good concurrency performance.

Storage of data tables on hard disks: bdb frm DB

6. InnoDB data table

This is the data table Type recently added to MySQL and has many new features.

1. Support Transaction Processing Mechanism
2. Immediate recovery after crash
3. Supports Foreign keys, including cascading deletion.
4. Concurrency

Storage of data tables on hard disks: InnoDB FRM

InnoDB: the InnoDB table type can be viewed as a product for further updating MyISAM. It provides the functions of transactions, row-level locks, and foreign key constraints.

Archive: This type only supports select and insert statements, and does not support indexes. It is often used in logging and aggregate analysis.

Reference Source: http://www.nowamagic.net/database/db_TypeOfTable.php

Http://database.51cto.com/art/201011/234306.htm