MySQL Database Engine Quick Guide

What would it be like if you were a racing driver and could switch the engine immediately without having to drive into the garage? What the MySQL database does for developers is like pressing a button to change the engine; it lets you choose the database engine and gives you a simple way to switch it.

MySQL's own engine must be enough, but in some cases other engines might be better suited to the task than the one at hand. If you want, you can even use the mysql++ API to create your own database engine, like a cylinder with your own carburetor. Now let's see how you can choose the engine and how to switch between the available engines.

Choose your engine.

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. The idea of compiling an application just to add a feature may seem strange to Windows developers, but in the Unix world this has become a standard. By default, MySQL supports three engines: ISAM, MyISAM, and heap. The other two types of InnoDB and Berkley (BDB) are also often available.

ISAM

ISAM is a well-defined and time-tested method of data table management, designed to take into account that the number of times a database is queried is much larger than the number of updates. As a result, ISAM performs read operations quickly and does not consume large amounts of memory and storage resources. The two major drawbacks of ISAM are that it does not support transactions or fault tolerance: If your hard drive crashes, the data files cannot be recovered. If you are using ISAM in mission-critical applications, you must always back up all of your real-time data, and MySQL can support such a backup application by replicating the features.

MyISAM

MyISAM is the ISAM extended format and default database engine for MySQL. In addition to providing a large number of functions for indexing and field management not available in ISAM, MyISAM also uses a form-locking mechanism to optimize multiple concurrent read and write operations. The price is that you need to run the Optimize Table command frequently to restore the space wasted by the updated mechanism. MyISAM also has some useful extensions, such as MYISAMCHK tools for repairing database files and Myisampack tools to restore wasted space.

MyISAM highlights the fast read operation, which may be the main reason why MySQL is so favored by Web development: The large amount of data operations you do in Web development are read operations. As a result, most virtual host providers and internet Platform providers (Internet Presence Provider,ipp) only allow the use of MyISAM format.

HEAP

Heap allows temporary tables that reside only in memory. resides in memory to make heap faster than ISAM and MyISAM, but the data it manages is unstable, and if not saved before shutdown, all data will be lost. When data rows are deleted, heap does not waste a lot of space. Heap tables are useful when you need to select and manipulate data using a select expression. Remember to delete the table after you have finished using it. Let me repeat: After you finish using the form, don't forget to delete the form.

InnoDB and Berkley DB

The InnoDB and Berkley DB (BDB) database engines are direct products that create MySQL-flexible technologies, the mysql++ API. When using MySQL, almost every challenge you face comes from ISAM and MyISAM the database engine does not support transaction processing or foreign key support. Although it is much slower than the ISAM and MyISAM engines, InnoDB and BDB include support for transaction processing and foreign keys, which are not in the top two engines. As mentioned earlier, if your design requires one or both of these features, you will be forced to use one of the last two engines.

If you feel you are really skilled, you can use mysql++ to create your own database engine. This API provides you with the ability to manipulate fields, records, tables, databases, connections, security accounts, and all the myriad other features needed to build a DBMS such as MySQL. Delving into the API is beyond the scope of this article, but it is important to understand the mysql++ and the technology behind the interchangeable engine. Estimating the plug-in database engine's model can even be used to create a local XML provider (XML provider) for MySQL. (Any mysql++ developer who reads this article can see this as a requirement.) ）