MySQL engine learning notes

MySQL engine learning notes

MySQL has the following engines: ISAM, MyISAM, HEAP, InnoDB, and Berkley (BDB ). Note: different versions support different engines. Of course, if you feel that you are indeed highly skilled, you can also use MySQL ++ to create your own database engine, niu Ren can refer to MySQL ++ API help for implementation. The features of these five engines are described as follows:

ISAM

ISAM is a well-defined and time-tested data table management method. It is designed to take into account that the number of 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, data files 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 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.

After knowing so many engines, we also know under what circumstances they should be used for emergency response, so we should learn how to replace these engines.

Globle: The simplest way is to change the server configuration and directly set it to the engine you need. In Windows, you can change the default-storage-engine item in mysql. ini under the Server installation directory. You can also perform simple settings by running MySQL Server Instance Configuration Wizard.

Per Table: In addition to the global method, there is also a more flexible configuration method, that is, to set the engine by Table, in this way, we can set the tables that require transaction processing to InnoDB, and set other tables to MyISAM to improve the performance to the extreme, isn't it very exciting? The setting method is also simple:

1. You can add an extension statement at the end of the Create Table statement, such as TYPE = MyISAM (or ENGINE = INNODB) to specify the engine type of the current target. You can also use the Alter statement to change the number of created tables. You can use show table status from DBname to view the engines of tables in the current database.

2. Use the MySQL client released with the MySQL server to create a table. You can select the storage engine used when creating the table.

Different engines are chosen for different business processing, and the performance will be quite different !!