MySQL Engine learning Notes

In general, MySQL has several engines: ISAM, MyISAM, HEAP, InnoDB, and Berkley (BDB). Note: Different versions of the supported engines are differentiated. Of course, if you feel that you are really skilled, you can also use mysql++ to create your own database engine, which is out of my knowledge, which can be implemented with reference to mysql++ API help. The characteristics of each of the 5 engines are described below:

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.

Having known so many engines and we know where they should be, then we'll learn how to replace them.

Globle: One of the easiest ways to do this is to change the server configuration and set it directly into the engine you need. This is done under win by changing the Default-storage-engine entry in the Mysql.ini under the Server installation directory, or by running the MySQL server Instance Configuration Wizard to make simple settings.

Per Table: In addition to the global approach, there is a more flexible configuration method, which is to set the engine by the table, so that we can use the transaction to set the table to InnoDB, other settings to MyISAM, the performance to the extreme, this is not exciting? The Setup method is also simpler:

1, you can add the extension statement at the end of the CREATE TABLE statement, such as type=myisam (or engine = INNODB) to specify the current target engine type. You can also use the ALTER statement to make changes after you create a table. You can use the Show Table STATUS from dbname when you don't know the engine of the tables in the current database.

2, the use of MySQL server with the release of the same MySQL client to create a table, at the time of creation can choose to use the storage engine.

Different engine choice in different business processing, performance will be a difference!!