Introduction to the concept of MySQL data engine

What is a database engine?
The data format of each database, the internal implementation mechanism is different, to use a development tool to access a database, you must pass a mediation program, this development tool and database intermediary program is called the Database engine.

How would it feel if you were a racer and clicked the button to change the engine immediately without having to drive into the garage? What the MySQL database does for developers is like a button changer, which lets you choose the database engine and gives you an easy way to switch it on.

MySQL 's own engine must be sufficient, but in some cases, other engines may be better suited to complete the task than is used at hand. If you want, you can even use the mysql++ API to create your own database engine, just like hitting a cylinder and putting on 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 when it was installed. To add a new engine, you must recompile MySQL. The idea of compiling an application just to add a feature might be strange for Windows developers, but in the UNIX World This has become a standard. By default,MySQL supports three engines:ISAM, MyISAM, and HEAP. Two other types of InnoDB and Berkley(BDB) are also often available.

ISAM

ISAM is a well-defined and time-tested form of data management that, at design time, takes into account that the number of times the 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 main disadvantages of ISAM are that it does not support transactional processing or fault tolerance: If your hard drive crashes, the data file cannot be recovered. If you are using ISAM in mission-critical applications, you must always back up all of your real-time data, and with its replication features,MySQL can support such a backup application.

MyISAM

myisam mysqlisam extended format and default database engine. In addition to providing isammyisam A table locking mechanism is also used to optimize multiple concurrent read and write operations. The cost is that you need to run optimize tablemyisammyisamchk for repairing database files Tools and Myisampack tools to restore wasted space.

MyISAM emphasizes fast read operations, which may be the main reason why MySQL is so popular with web development: in Web development, the bulk of your data operations are read operations. Therefore, most virtual hosting providers and Internet platform providers (Internet presence Provider,IPP) only allow the use of the MyISAM format.

HEAP

the HEAP allows temporary tables that reside only in memory. Residing in memory makes the HEAP faster than ISAM and MyISAM , but the data it manages is unstable, and if it is not saved before shutting down, all the data will be lost. TheHEAP does not waste a lot of space when data rows are deleted. The HEAP table is useful when you need to select and manipulate data using a select expression. Remember to delete the table after you have finished using the table. Let me repeat: Don't forget to delete the table after you have finished using the form.

InnoDB and Berkley DB

innodb and Berkley db ( bdb) database engine is a direct product of the technology that makes mysql flexibility, This technology is mysql++ apimysqlisammyisam The database engine does not support transactional processing and does not support foreign keys. Although the engine is much slower than isam The innodbbdb