Three engines supported by the MySQL database

The following article describes how to access the three engines supported by MySQL databases. We all know that MySQL databases support three engines by default: ISAM, MyISAM, and HEAP. The other two types are InnoDB and Berkley (BDB )......

ISAM

ISAM is a well-defined and time-tested data table management method. It is designed to take into account that the number of MySQL database queries is much larger than the number of updates. Therefore, ISAM performs read operations quickly without occupying a large amount of memory and storage resources. The two major disadvantages of ISAM are that it does not support transaction processing or fault tolerance:

If your hard disk crashes, the data file cannot be recovered. If you are using ISAM in a key task application, you must always back up all your real-time data. With its copy feature, MySQL can support such backup applications.

MyISAM

MyISAM is the MySQL ISAM extension format and default database engine. In addition to providing a large number of functions for indexing and field management not available in ISAM, MyISAM also uses a table lock mechanism to optimize multiple concurrent read/write operations. The cost is that you need to run the optimize table command frequently to restore the space wasted by the update mechanism. MyISAM also has some useful extensions, such as the MyISAMChk tool used to fix MySQL database files and the MyISAMPack tool used to restore wasted space.

MyISAM emphasizes fast read operations, which may be the main reason why MySQL is so favored by Web development: In Web development, a large number of data operations you perform are read operations. Therefore, most VM providers and Internet platform providers (Internet Presence Provider, IPP) only allow the use of MyISAM format.

HEAP

HEAP allows only temporary tables in memory. HEAP is faster than ISAM and MyISAM in the memory, but the data it manages is unstable. If it is not saved before shutdown, all the data will be lost. When a row is deleted, HEAP does not waste much space. HEAP tables are useful when you need to use SELECT expressions to SELECT and manipulate data. Remember to delete the table after the table is used up. Let me repeat it again: do not forget to delete the table after you have used up the table.

InnoDB and Berkley DB

InnoDB and Berkley DB (BDB) database engines are both direct products that make MySQL flexible technology. This technology is MySQL ++ API. When using MySQL, almost every challenge you face comes from the fact that the ISAM and MyISAM database engines do not support transaction processing or foreign keys.

Although it is much slower than ISAM and MyISAM engines, InnoDB and BDB include support for transaction processing and Foreign keys, both of which are not available in the first two engines. As mentioned above, if your design requires one or both of these features, you will be forced to use one of the two engines.

The above content is an introduction to the three engines supported by the MySQL database with ease, and I hope you will gain some benefits.