MySQL database MyISAM and InnoDB storage engine comparison

MySQL database MyISAM and InnoDB storage engine comparison

MySQL has a variety of storage engines, MyISAM and InnoDB are two of the most common. Here are some basic concepts about these two engines (not in-depth introduction).

MyISAM is the default storage engine for MySQL, based on the traditional ISAM type, which supports full-text search, but is not transaction-safe and does not support foreign keys. Each MyISAM table is stored in three files: the frm file is stored as a table definition, the data file is MyD (MYData), and the index file is myi (Myindex).

InnoDB is a transactional engine that supports rollback, crash resiliency, multi-version concurrency control, acid transactions, support for row-level locking (innodb table row locks are not absolute, and if MySQL cannot determine the range to scan when executing an SQL statement, the InnoDB table also locks the full table, such as the SQL statement at the time of the operation, and provide a non-lock read that is consistent with the Oracle type. InnoDB stores its tables and indexes in a table space, a tablespace can contain several files.

Main differences:

• MyISAM is a non-transactional security type, and InnoDB is a transaction-safe type.
• The granularity of MyISAM locks is table-level, while InnoDB supports row-level locking.
• MyISAM supports full-text type indexing, while InnoDB does not support full-text indexing.
• MyISAM is relatively simple, so in terms of efficiency is better than INNODB, small applications can consider the use of MyISAM.
• MyISAM tables are saved as files, and using MyISAM storage in cross-platform data transfer saves a lot of hassle.
• The InnoDB table is more secure than the MyISAM table and can be switched from non-transactional tables to transaction tables (ALTER TABLE tablename TYPE=INNODB) without ensuring that the data is not lost.

Application Scenarios:

• MyISAM Manage non-transactional tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. MyISAM is a better choice if you need to perform a large number of select queries in your application.
• InnoDB is used for transactional applications and has many features, including acid transaction support. If you need to perform a large number of insert or update operations in your app, you should use InnoDB, which can improve the performance of multiple user concurrency operations.

Common commands:

(1) View the storage type of table (three kinds):

• Show CREATE TABLE TableName
• Show table status from DBName where Name=tablename
• Mysqlshow-u user-p password--status dbname tablename

(2) Modify the table's storage engine:

• ALTER TABLE TableName TYPE=INNODB

(3) Add the following parameter to the command line that starts the MySQL database so that the newly published table will use the transaction by default:

• --default-table-type=innodb

(4) Temporarily change the default table type:

• Set Table_type=innodb
• Show variables like ' Table_type '

Source Text Address: http://www.cnblogs.com/panfeng412/archive/2011/08/16/2140364.html

MySQL database MyISAM and InnoDB storage engine comparison