MySQL Table type detailed

MySQL provides us with a lot of table types to choose from, there are MyISAM, ISAM, HEAP, BerkeleyDB, Innodb,merge table types, radish cabbage is not false, but the real choice of what kind of table type or to see business needs ah, Each type of table has its own properties and advantages. Let's have a brief discussion here.

MyISAM Table Type:

(1) The MYISAM table (TYPE=MYISAM) is an extension of the ISAM type, with many optimizations and enhancements.

(2) is the default table type for MySQL.

(3) MyISAM optimizes compression ratios and speeds, and can be easily ported between different operating systems and platforms.

(4) MyISAM supports large table files (greater than 4G)

(5) Allow indexing of blobs and text columns

(6) To support the use of key prefixes and the use of the full key search records

(7) Table data and table index files can be dependent on different locations, even in different file systems.

(8) Even with a fairly large number of inserts, updates, and deletions, the smart anti-fragmentation logic guarantees 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 and therefore does not provide good optimization and execution efficiency as the MyISAM type. Because the ISAM index cannot be compressed, it overcomes less system resources than the same index in the MyISAM. The ISAM index requires more disk space, which is a problem for this small environment like this site. Oh.

Like MyISAM, ISAM tables can be fixed-length or variable-length, but the maximum key length of the format is smaller, the table in ISAM format cannot be larger than 4G, and tables cannot be ported between different platforms. In addition, the ISAM table splits easily, which reduces query speed and limits the compression of data/indexes.

Help Table Type:

The HEAP table (TYPE=HEAP) is an in-memory table that uses a fast hash index (when an insert query is run, an independent evaluation indicates that the HEAP table is at least 30% faster than the MyISAM table), so it can be optimized for temporary tables. Access rules and usage of MyISAM or ISAM tables are the same. The data stored inside is only available during the lifetime of the MySQL server, and if the MySQL server crashes or is turned off, the data will be lost. Although the heap table has performance benefits, it cannot be used frequently in practice due to its temporary and some other functional limitations.

The size of the heap table is limited only by the available memory on the system, and MySQL is smart and has built-in protection to prevent the unconscious use of all available memory. So we don't have to worry about memory being exhausted by the heap table. The heap table does not support blobs or text columns and cannot exceed the size specified by the max_heap_table_size variable.

BerkeleyDB Table Type:

The BerkeleyDB table (TYPE=BDB) is designed to meet the growing demands of MySQL developers for the Transaction security table. The BerkeleyDB table has a number of interesting geese, including commit and rollback operations, multiuser concurrent access, checkpoints, secondary indexes, log-recovery crashes, continuous and keyed access to data, and so on, which facilitates complex, transactional-based SQL with a viable choice.

However, the BerkeleyDB table also has some limitations, let us briefly understand:

It's difficult to move (when created, the table path is hard-coded in the table file)

Table indexes cannot be compacted, and their tables are usually larger than myisam corresponding tables

A little chicken feeling, because now InnoDB format can largely replace the BERKELEYDB format

InnoDB Table Type:

The INNODB table (TYPE=INNODB) is a fully compatible acid (atomicity, consistency, independence, and durability of transactions), a highly efficient table that fully supports MySQL transactions and does not btwagkyaakftntce. Fine (row-and table-level) locks improve the removal of MySQL transactions, while also supporting unlocked read operations (previously included only in Oracle) and multiple versions of the feature.

Asynchronous input/output and a series of read buffering will improve the speed of data retrieval, and can be optimized for files and memory management. It is necessary to support the automatic creation of hash indexes on memory to improve performance and to use buffering to improve reliability and speed of database operations. InnoDB Watch can not be comparable to MyISAM, even more than the MyISAM.

is fully portable on different operating systems and architectures. Because it's always in a consistent state (MySQL makes them more robust by checking for errors at startup and fixing bugs). Support for external keys, commit, rollback, and roll forward, making it the most complete form in MySQL.

Merge table type:

(1) The MERGE table (Type=merge) is a virtual table created by combining multiple MyISAM tables into a single table.

(2) A table can be grouped only if the table involved has exactly the same table structure. Any difference in the field type or index is not a successful combination.

(3) The merge table uses the index that makes up the table, and it does not maintain its own index, and in some cases it can increase speed.

(4) Allow select,delete,update operation

(5) This table is useful when you need to put data from different tables together to improve the performance of the connection or to search through a series of tables.

(6) When dealing with large myisam tables, we can reduce the space that these tables conquer by embossing or by using the Myisampack utility that is included in the MySQL release to "pack". Myisampack creates smaller, read-only tables without causing any significant performance overhead when smart compression is used.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/