Three table types of MySQL learning notes

The database engine you can use depends on how MySQL was compiled at the time of installation. To add a new engine, you must compile MySQL. The idea of compiling an application just to add a feature might be a bit of a fuss for Windows developers, but in the Unix world this has become a standard. In the default case,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 the beginning of the design, takes into account the number of times the database has been queried far greater than the number of times it was updated. As a result,ISAM performs read operations quickly and does not consume large amounts of memory and storage resources . The major disadvantage of ISAM two is that it does not support transaction management and is not fault tolerant . For example, if you have a hard drive, data files will not 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 applications such as backup.

MyISAM

MyISAM is the ISAM extended format for MySQL and the default database engine. In addition to providing a number of functions for index and field management that are not available in ISAM,MyISAM also uses a form-locking mechanism to optimize multiple concurrent read and write operations . The cost is that you must run the OPTIMIZE TABLE command frequently to restore space wasted by the updated mechanism. MyISAM also has some extensions available, such as the Myisamchk tool for repairing database files and the Myisampack tool for recovering wasted space .

MyISAM emphasizes fast read and write operations, which may be the main reason why MySQL is so popular with web development, and the bulk of the data operations you perform in web development 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. The heap 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 .

InnoDB and Berkley DB

InnoDBand theBerkley DB(BDB) The database engine is createdMySQLflexibility of the technology of direct products, this technology ismysql++ API. When usingMySQL, almost every challenge you face comes fromISAMand theMyISAMThe database engine does not support transactional processing and does not support foreign keys. Although more than the ISAM and MyISAM engines are much slower, but InnoDB and BDB includes support for transactional and foreign keys , two points that are not available from the top two engines. As mentioned earlier, if your design requires accesses than either or both of these features, you will be forced to use one of the latter two engines.

    If you feel that you are really skilled, you can also use mysql++ to create your own database engine . ThisAPIprovides you with the ability to manipulate fields, records, tables, databases, connections, security accounts, and createMySQLthisDBMSall the other myriad features that are required. In-depth explanationAPIis beyond the scope of this article, but you need to understandmysql++the existence and technology behind the interchangeable engine, which is important. It is estimated that this plug-in database engine model can even be used toMySQLCreate a localXMLProvider (XML provider). Press the switch to make all the flexibility possible the switch is provided toANSI SQLof theMySQLExtended--typeparameters. MySQLallows you to specify the database engine at the level of the table, so they sometimes refer totable Formats.

The following example code shows how to create a table that uses the MyISAM,ISAM , and HEAP engines, respectively. Note that the code to create each table is the same, except for the last TYPE parameter, which is used to specify the data engine.  

CREATE TABLE tblmyisam ( ID INT not NULL auto_increment,PRIMARY KEY (id),value_a TINYINT )Type=myisa

CREATE TABLE tblisam ( ID INT not NULL auto_increment,PRIMARY KEY (id),value_a TINYINT )type=isam
CREATE TABLE tblheap (ID INT not NULL auto_increment,PRIMARY KEY (id),value_a TINYINT )type=heap

You can also use altertable command to move the original form from one engine to another . The following code shows how to use ALTER table to move the MyISAM table to InnoDB 's engine :

ALTER TABLE tblmyisam Change Type=innodb

MySQL use three steps to achieve this goal. First, a copy of the table is created. Any changes to the input data are then queued, and the copy is moved to another engine. Finally, any data changes queued to the queue are sent to the new table, and the original table is deleted.

If you just want to get the table from ISAM updated to MyISAM , you can use Mysql_convert_table_format Command , without having to write ALTER TABLE An expression .

You can use the Show Table command (which is another extension of MySQL to the ANSI standard) to determine which engine is managing a particular table. SHOW table Returns a result set with multiple data columns that you can use to query for all types of information: the name of the database engine is in the Type field. The following sample code illustrates the use of Show table:

showtable STATUS from Tblinnodb

    You can use show CREATE TABLE [TableName] to retrieve show TABLE The information that can be retrieved.

    Finally, if you want to use not compiled mysql also has no active engine, which is useless, mysql myisam mysql show TABLE To check the table format. More choices mean better performance for specific tables The engine needs to be recompiled and traced, and given this extra complexity, why do you want to use a non-default database engine? The answer is simple: adjust the database to meet your requirements.

to be sure,MyISAM is fast, but if your logic design requires transactional processing, you are free to use the engine that supports transaction processing. Further, since MySQL allows you to apply the database engine at the table level, you can optimize performance only for tables that require transaction processing, and leave the tables that do not require transaction processing to the lighter MyISAM engine . For MySQL , flexibility is the key.