The seven types of Mysql tables are described in detail
- 2020-05-15 02:16:56
- OfStack
Learn Mysql database, Mysql table types are what you need to know, the following is to introduce seven types of Mysql table, I hope to help you learn Mysql table types.
As the most popular free database service engine, MySQL has been popular for a long time, but some people may not be familiar with the internal environment of MySQL, especially those dealing with concurrency. Today, we'll look at 1 for Mysql table types and some of their simple properties.
So far, MySQL1 provides users with seven Mysql table types, DBD, HEAP, ISAM, MERGE, MyIAS, InnoDB, and Gemeni. Among them, DBD and InnoDB belong to the transaction security class table, while others belong to the transaction non-security class table.
DBD
The Berkeley DB (DBD) table is a transaction-enabled table developed by the Sleepycat software company. It provides transaction control, a long-awaited feature for MySQL users. Transaction controls are a valuable feature in any database system because they ensure that a set of commands can be successfully executed or rolled back.
HEAP
The HEAP table is the fastest table in MySQL to access data. This is because they use a hash index stored in dynamic memory, but this in-memory data will be lost if MySQL or the server crashes.
ISAM
The ISAM table was the default table type in earlier versions of MySQL, until MyIASM was developed. It is recommended not to use it again.
MERGE
MERGE is an interesting new type that appears after 3.23.25. One MERGE table is actually a collection of another MyISAM table, which is merged into one table for the sake of efficiency, because it can not only improve the speed, search efficiency, repair efficiency, but also save disk space.
MyIASM
MyIASM is based on the IASM code, which is arguably a derivative of IASM, but adds a number of useful extensions. It is the default data table type for MySQL, based on the traditional ISAM type. ISAM is short for Indexed Sequential Access Method (indexed sequential access method). Generally speaking, it is the standard method for storing records and files. Compared to other storage engines, MyISAM has most tools for checking and repairing tables. The ISAM tables can be compressed and they support full-text search, but they are transactionally insecure and do not support foreign keys. If the transaction is rolled back, it will result in an incomplete rollback that is not atomic. If transactions and access concurrency are ignored and a large number of SELECT retrieval statements need to be executed, MyISAM is the best option.
InnoDB
InnoDB is a relatively new data table type introduced after MySQL 4.0, which is transaction-safe. It has the same characteristics as the BDB type, which also supports foreign keys. The InnoDB table is fast and has more features than BDB, so if you need a transaction-safe storage engine, it is recommended. If your data performs large amounts of INSERT or UPDATE, you should also use InnoDB tables for performance reasons. For transactions-enabled tables of type InnoDB, the main reason for the speed impact is that the default setting of AUTOCOMMIT is turned on, and the program does not explicitly call BEGIN to start the transaction, resulting in automatic commit for each insert, seriously affecting the speed. begin can be called before sql is executed, and multiple sql form one thing (even if autocommit is turned on), which greatly improves performance.
Gemeni
Gemeni table, it is said, was also launched after MySQL 4.0, but so far, there is little introduction to it, and even less application, so we will not introduce it for the moment.
There are many data table types for MySQL, among which the two most important are MyISAM and InnoDB.
Each of these two types has its advantages and disadvantages, and you need to choose the right one based on the actual situation. MySQL supports setting different types for different tables. Here's a simple comparison:
The MyISAM table type is a more mature and stable table type, but MyISAM does not support 1.
As the most popular free database service engine, MySQL has been popular for a long time, but some people may not be familiar with the internal environment of MySQL, especially those dealing with concurrency. Today, we'll look at 1 for Mysql table types and some of their simple properties.
So far, MySQL1 provides users with seven Mysql table types, DBD, HEAP, ISAM, MERGE, MyIAS, InnoDB, and Gemeni. Among them, DBD and InnoDB belong to the transaction security class table, while others belong to the transaction non-security class table.
DBD
The Berkeley DB (DBD) table is a transaction-enabled table developed by the Sleepycat software company. It provides transaction control, a long-awaited feature for MySQL users. Transaction controls are a valuable feature in any database system because they ensure that a set of commands can be successfully executed or rolled back.
HEAP
The HEAP table is the fastest table in MySQL to access data. This is because they use a hash index stored in dynamic memory, but this in-memory data will be lost if MySQL or the server crashes.
ISAM
The ISAM table was the default table type in earlier versions of MySQL, until MyIASM was developed. It is recommended not to use it again.
MERGE
MERGE is an interesting new type that appears after 3.23.25. One MERGE table is actually a collection of another MyISAM table, which is merged into one table for the sake of efficiency, because it can not only improve the speed, search efficiency, repair efficiency, but also save disk space.
MyIASM
MyIASM is based on the IASM code, which is arguably a derivative of IASM, but adds a number of useful extensions. It is the default data table type for MySQL, based on the traditional ISAM type. ISAM is short for Indexed Sequential Access Method (indexed sequential access method). Generally speaking, it is the standard method for storing records and files. Compared to other storage engines, MyISAM has most tools for checking and repairing tables. The ISAM tables can be compressed and they support full-text search, but they are transactionally insecure and do not support foreign keys. If the transaction is rolled back, it will result in an incomplete rollback that is not atomic. If transactions and access concurrency are ignored and a large number of SELECT retrieval statements need to be executed, MyISAM is the best option.
InnoDB
InnoDB is a relatively new data table type introduced after MySQL 4.0, which is transaction-safe. It has the same characteristics as the BDB type, which also supports foreign keys. The InnoDB table is fast and has more features than BDB, so if you need a transaction-safe storage engine, it is recommended. If your data performs large amounts of INSERT or UPDATE, you should also use InnoDB tables for performance reasons. For transactions-enabled tables of type InnoDB, the main reason for the speed impact is that the default setting of AUTOCOMMIT is turned on, and the program does not explicitly call BEGIN to start the transaction, resulting in automatic commit for each insert, seriously affecting the speed. begin can be called before sql is executed, and multiple sql form one thing (even if autocommit is turned on), which greatly improves performance.
Gemeni
Gemeni table, it is said, was also launched after MySQL 4.0, but so far, there is little introduction to it, and even less application, so we will not introduce it for the moment.
There are many data table types for MySQL, among which the two most important are MyISAM and InnoDB.
Each of these two types has its advantages and disadvantages, and you need to choose the right one based on the actual situation. MySQL supports setting different types for different tables. Here's a simple comparison:
The MyISAM table type is a more mature and stable table type, but MyISAM does not support 1.