Learning Mysql database, what are the Mysql table types are necessary to know. Below I will introduce seven Mysql table types to you, hoping that it will be helpful for you to learn Mysql table types.
MySQL, as the most popular free database service engine, has been popular for a long time, but some people may not know much about the internal environment of MySQL, especially those mechanisms for concurrency processing. Today, let's first look at Mysql table types and some of 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 belong to transaction security tables, while others belong to transaction non-security tables.
DBD
The Berkeley DB (DBD) table is a transaction-enabled table developed by Sleepycat Software. It provides the long-awaited feature of MySQL users - transaction control. Transaction control is 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 if MySQL or server crashes, that memory data will be lost.
ISAM
ISAM tables were the default table type of early MySQL versions until MyIASM was developed. It is recommended not to use it again.
MERGE
MERGE is an interesting new genre that appears after 3.23.25. A MERGE table is actually another collection of MyISAM tables, merged into a table, mainly for efficiency considerations, because this not only improves speed, search efficiency, repair efficiency, but also saves disk space.
MyIASM
MyIASM is based on IASM code and should be said to be a derivative of IASM, but it has added a lot of useful extensions. It is MySQL's default data table type, based on the traditional ISAM type, which is the abbreviation of Indexed Sequential Access Method. Generally speaking, it is a standard method for storing records and files. Compared to other storage engines, MyISAM has most tools to check and repair tables. ISAM tables can be compressed, and they support full-text searches, but they are transactionally insecure and do not support foreign keys. If the transaction rollback is caused, it will cause incomplete rollback, thus not atomic. Therefore, if transactions and access concurrency are ignored and a large number of SELECT search statements need to be executed, MyISAM will be the best choice.
InnoDB
InnoDB is a relatively new data table type launched after MySQL 4.0. This type is transaction-safe. It has the same characteristics as the BDB type, and they also support foreign keys. InnoDB tables are fast and have richer features than BDB, so if you need a transaction-safe storage engine, it is recommended. If your data performs a large amount of INSERT or UPDATE, you should also use InnoDB tables for performance reasons. For InnoDB type tables that support transactions, the main reason for the speed is that AUTOCOMMIT default setting is on, and the program does not explicitly call BEGIN to start the transaction, resulting in automatic committing for each insert, which seriously affects the speed. You can call begin before executing SQL, and multiple SQLs form a thing (even if autocommit is open), which will greatly improve performance.
Gemeni
It is said that the Gemeni table was also launched after MySQL 4.0, but as of now, there are few introductions to it, and there are even fewer applications. We will not introduce it for the time being.
There are many types of data tables in MySQL, among which the more important are MyISAM and InnoDB.
These two types have their own advantages and disadvantages, and you need to choose the appropriate one according to the actual situation. MySQL supports setting different types for different tables. Here is a simple comparison:
MyISAM table type is a relatively mature and stable table type, but MyISAM does not support some functions.
MySQL, as the most popular free database service engine, has been popular for a long time, but some people may not know much about the internal environment of MySQL, especially those mechanisms for concurrency processing. Today, let's first look at Mysql table types and some of 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 belong to transaction security tables, while others belong to transaction non-security tables.
DBD
The Berkeley DB (DBD) table is a transaction-enabled table developed by Sleepycat Software. It provides the long-awaited feature of MySQL users - transaction control. Transaction control is 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 if MySQL or server crashes, that memory data will be lost.
ISAM
ISAM tables were the default table type of early MySQL versions until MyIASM was developed. It is recommended not to use it again.
MERGE
MERGE is an interesting new genre that appears after 3.23.25. A MERGE table is actually another collection of MyISAM tables, merged into a table, mainly for efficiency considerations, because this not only improves speed, search efficiency, repair efficiency, but also saves disk space.
MyIASM
MyIASM is based on IASM code and should be said to be a derivative of IASM, but it has added a lot of useful extensions. It is MySQL's default data table type, based on the traditional ISAM type, which is the abbreviation of Indexed Sequential Access Method. Generally speaking, it is a standard method for storing records and files. Compared to other storage engines, MyISAM has most tools to check and repair tables. ISAM tables can be compressed, and they support full-text searches, but they are transactionally insecure and do not support foreign keys. If the transaction rollback is caused, it will cause incomplete rollback, thus not atomic. Therefore, if transactions and access concurrency are ignored and a large number of SELECT search statements need to be executed, MyISAM will be the best choice.
InnoDB
InnoDB is a relatively new data table type launched after MySQL 4.0. This type is transaction-safe. It has the same characteristics as the BDB type, and they also support foreign keys. InnoDB tables are fast and have richer features than BDB, so if you need a transaction-safe storage engine, it is recommended. If your data performs a large amount of INSERT or UPDATE, you should also use InnoDB tables for performance reasons. For InnoDB type tables that support transactions, the main reason for the speed is that AUTOCOMMIT default setting is on, and the program does not explicitly call BEGIN to start the transaction, resulting in automatic committing for each insert, which seriously affects the speed. You can call begin before executing SQL, and multiple SQLs form a thing (even if autocommit is open), which will greatly improve performance.
Gemeni
It is said that the Gemeni table was also launched after MySQL 4.0, but as of now, there are few introductions to it, and there are even fewer applications. We will not introduce it for the time being.
There are many types of data tables in MySQL, among which the more important are MyISAM and InnoDB.
These two types have their own advantages and disadvantages, and you need to choose the appropriate one according to the actual situation. MySQL supports setting different types for different tables. Here is a simple comparison:
MyISAM table type is a relatively mature and stable table type, but MyISAM does not support some functions.