In depth understanding of the type of database engine for MySQL
- 2020-05-17 06:43:47
- OfStack
The database engine you can use depends on how mysql was compiled at the time of installation. To add a new engine, you must recompile MYSQL. By default, MYSQL supports three engines: ISAM, MYISAM, and HEAP. Two other types, INNODB and BERKLEY (BDB), are also often used.
ISAM
ISAM is a well-defined, time-tested approach to data table management that was designed with the database being queried far more often than updated. As a result, ISAM performs read operations quickly and does not consume a large amount of memory and storage resources. The two main drawbacks of ISAM are that it does not support transactions and is not fault-tolerant: if your hard drive crashes, your data files cannot be recovered. If you are using ISAM for mission-critical applications, you must always back up all of your real-time data. With its replication feature, MYSQL supports such backup applications.
MYISAM
MYISAM is MYSQL's ISAM extension format and the default database engine. In addition to providing a number of indexing and field management features not available in ISAM, MYISAM also USES a table locking mechanism to optimize multiple concurrent read and write operations. The cost is that you often need to run the OPTIMIZE TABLE command to recover the space wasted by the update mechanism. MYISAM also has some useful extensions, such as the MYISAMCHK tool for fixing database files and the MYISAMPACK tool for recovering wasted space.
MYISAM emphasizes quick read operations, which is probably the main reason why MYSQL is so popular with WEB development: most of the data operations you do in WEB development are read operations. As a result, most web hosting providers and INTERNET platform providers are only allowed to use the MYISAM format.
HEAP
HEAP allows temporary tables that reside only in memory. Being resident in memory makes HEAP faster than ISAM and MYISAM, but the data it manages is unstable, and if it is not saved before the shutdown, all the data is lost. While HEAP doesn't waste a lot of space when rows are deleted, HEAP tables are useful when you need to use SELECT expressions to select and manipulate data. Remember to delete the table after using it.
INNODB and BERKLEYDB
The INNODB and BERKLEYDB (BDB) database engines are both direct products of the technology that makes MYSQL flexible: MySql++ API. Almost every challenge you face with MySql stems from the fact that the ISAM and MYIASM database engines do not support transaction processing or foreign keys. Although much slower than the ISAM and MYISAM engines, the INNODB and BDB include support for transaction processing and foreign keys, both of which were not available in the first two engines. As mentioned earlier, if your design requires either one of these features or both, you will be forced to use one of the latter two engines.
ISAM
ISAM is a well-defined, time-tested approach to data table management that was designed with the database being queried far more often than updated. As a result, ISAM performs read operations quickly and does not consume a large amount of memory and storage resources. The two main drawbacks of ISAM are that it does not support transactions and is not fault-tolerant: if your hard drive crashes, your data files cannot be recovered. If you are using ISAM for mission-critical applications, you must always back up all of your real-time data. With its replication feature, MYSQL supports such backup applications.
MYISAM
MYISAM is MYSQL's ISAM extension format and the default database engine. In addition to providing a number of indexing and field management features not available in ISAM, MYISAM also USES a table locking mechanism to optimize multiple concurrent read and write operations. The cost is that you often need to run the OPTIMIZE TABLE command to recover the space wasted by the update mechanism. MYISAM also has some useful extensions, such as the MYISAMCHK tool for fixing database files and the MYISAMPACK tool for recovering wasted space.
MYISAM emphasizes quick read operations, which is probably the main reason why MYSQL is so popular with WEB development: most of the data operations you do in WEB development are read operations. As a result, most web hosting providers and INTERNET platform providers are only allowed to use the MYISAM format.
HEAP
HEAP allows temporary tables that reside only in memory. Being resident in memory makes HEAP faster than ISAM and MYISAM, but the data it manages is unstable, and if it is not saved before the shutdown, all the data is lost. While HEAP doesn't waste a lot of space when rows are deleted, HEAP tables are useful when you need to use SELECT expressions to select and manipulate data. Remember to delete the table after using it.
INNODB and BERKLEYDB
The INNODB and BERKLEYDB (BDB) database engines are both direct products of the technology that makes MYSQL flexible: MySql++ API. Almost every challenge you face with MySql stems from the fact that the ISAM and MYIASM database engines do not support transaction processing or foreign keys. Although much slower than the ISAM and MYISAM engines, the INNODB and BDB include support for transaction processing and foreign keys, both of which were not available in the first two engines. As mentioned earlier, if your design requires either one of these features or both, you will be forced to use one of the latter two engines.