The difference and performance of MyISAM and InnoDB in MySQL database

MyISAM: This is the default type, which is based on the traditional ISAM type, and ISAM is the abbreviation for indexed sequential access method (indexed sequential access methods), which is the standard way to store records and files. Compared with other storage engines, MyISAM has most of the tools for checking and repairing tables. MyISAM tables can be compressed, and they support full-text search. They are not transaction-safe and do not support foreign keys. If something is rolled back it will cause an incomplete rollback and not be atomic. It is a better choice if you perform a lot of select,myisam.

Myiasm is a new version of the Iasm table, with the following extensions:

Each MyISAM table is stored in three files. The frm file holds the table definition. The data file is MyD (MYData). Index files are myi (myindex) extensions.

Because MyISAM is relatively simple, so it is better than innodb in efficiency. Small applications use MyISAM is a good choice.

MyISAM tables are saved in the form of files, and using MyISAM storage in Cross-platform data transfer saves a lot of trouble

The following are some details and specific implementations of the differences:

The fulltext type index is not supported for 1.InnoDB.

The number of rows in the table is not saved in 2.InnoDB, that is, when the select count (*) from table is executed, InnoDB scans the entire table to calculate the number of rows, but MyISAM simply reads out the saved rows. Note that when the COUNT (*) statement contains the Where condition, the operations of the two tables are the same.

3. For Auto_increment type fields, the InnoDB must contain only the index of the field, but in the MyISAM table, you can establish a federated index with the other fields.

4.DELETE from table, InnoDB does not re-establish the table, but deletes one row at a time.

5.LOAD table from Master does not work for InnoDB, the solution is to first change the InnoDB table to MyISAM table, import the data and then change to InnoDB table, but for the use of additional InnoDB features (such as foreign key) of the table does not apply.

In addition, row locks on innodb tables are not absolute, and if MySQL cannot determine the range to scan when executing an SQL statement, the InnoDB table also locks the entire table, such as Update table set num=1 where name like "%aaa%"

Any kind of table is not omnipotent, only appropriate for the business type to choose the appropriate table type, in order to maximize the performance of MySQL advantage.

InnoDB: This type is transaction-safe. It has the same characteristics as the BDB type, and they also support foreign keys. The InnoDB table is fast and has a richer feature than BDB, so it is recommended if you need a transaction-safe storage engine. If your data performs a large number of inserts or updates, you should use InnoDB tables for performance reasons. For tables that support innodb types of things, the main reason for the speed is that the AUTOCOMMIT default setting is open, and the program does not explicitly call begin transactions, causing each insert to automatically commit, seriously affecting the speed. You can call begin before executing SQL, and multiple SQL forms one thing (even if the autocommit is open), which can greatly improve performance.

You can look at the following table:

	MyISAM	InnoDB
The composition of the difference	Each myisam is stored on disk as three files. The file name is the table name and the extension is the file type. . frm file storage table definition; . MyD (MYData) The extension of the data file; . The extension of the Myi (myindex) index file.	A disk-based resource is a InnoDB tablespace data file and its log files, and the size of the InnoDB table is limited to the size of the operating system file, typically 2GB
Transaction processing aspects	Tables of the MyISAM type emphasize performance, which executes faster than the InnoDB type, but does not provide transactional support.	INNODB provides transaction support transactions , foreign key, and other advanced database functions.
Lock	Table-Level Locks	Row-level Locks InnoDB table row locks are also not absolute, and if MySQL cannot determine the range to scan when executing an SQL statement, the INNODB table will also lock the entire table, such as Update table set num=1 where name like "%aaa%"
Select, insert, UPDATE, delete operations	It is a better choice if you perform a lot of select,myisam.	1. If your data performs a large number of inserts or updates, you should use InnoDB tables for performance reasons. 2.DELETE from table, InnoDB does not re-establish the table, but deletes one row at a time. 3.LOAD table from Master does not work for InnoDB, the solution is to first change the InnoDB table to MyISAM table, import the data and then change to InnoDB table, but for the use of additional InnoDB features (such as foreign key) of the table does not apply.
For fields of type auto_increment	Must contain index only for this field	You can set up a federated index with other fields
		InnoDB does not support indexes of type Fulltext.
	Binary data files of type MyISAM can be migrated in different operating systems

The following are some details and specific implementations of the differences:

1. InnoDB does not support indexes of type Fulltext.
2. InnoDB does not save the exact number of rows in the table, that is, when the select count (*) from table is executed, InnoDB scans the entire table to calculate how many rows, but MyISAM simply reads out the saved rows. Note that when the COUNT (*) statement contains the Where condition, the operations of the two tables are the same.
3. For a field of type auto_increment, InnoDB must contain an index with only that field, but in the MyISAM table, you can establish a federated index with the other fields.
4. When you delete the From table, InnoDB does not re-establish the table, but deletes one row at a time.
5. The LOAD table from master operation does not work for InnoDB, and the solution is to first change the InnoDB table to a MyISAM table, import the data and then change it to the InnoDB table, but not the table for the extra InnoDB attributes (such as foreign keys) used.

Performance comparison

To replace our MyISAM engine.
MySQL table structure

code is as follows	copy code
CREATE TABLE ' MyISAM ' (   ' id ' int (one) not NULL auto_increment,   ' name ' varchar (MB) default NULL,   ' Content ' text,   PRIMARY key  (' id ') ) Engine=myisam DEFAULT CHARSET=GBK; CREATE TABLE ' InnoDB ' (   ' id ' int (one) not NULL auto_increment,   ' name ' varchar () default null,   ' content ' text,   PRIMARY key  (' id ') ) Engine=innodb DEFAULT charset=gbk;

Data content
$name = "Heiyeluren";
$content = "MySQL supports several storage engines as processors for different types of tables. The MySQL storage engine includes the engine that handles the transaction security table and the engine that handles the non-transactional security table: · MyISAM Manage non-transaction tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. MyISAM is supported in all MySQL configurations and is the default storage engine unless you configure MySQL to use another engine by default. · The memory storage engine provides an in-memory table. The merge storage engine allows the collection to be processed by the same MyISAM table as a separate table. Like MyISAM, memory and the merge storage engine handle non-transaction tables, both of which are included by default in MySQL. Interpretation: the memory storage engine is officially identified as the heap engine. · The InnoDB and BDB storage engines provide transaction security tables. BDB is included in the Mysql-max binary distribution that is released for the operating system that supports it. InnoDB is also included in all MySQL 5.1 binary distributions by default, and you can configure MySQL to allow or disable any engine as you prefer. The example storage engine is a "stub" engine, and it does nothing. You can use this engine to create a table, but no data is stored in or retrieved from it. The purpose of this engine is to serve an example in the MySQL source code, which demonstrates how to start writing a new storage engine. Again, its main interest is for developers. ";

The code is as follows

Copy Code

[Insert data-1] (innodb_flush_log_at_trx_commit=1) MyISAM 1w:3/s InnoDB 1w:219/s MyISAM 10w:29/s InnoDB 10w:2092/s MyISAM 100w:287/s InnoDB 100W: not daring to test [Insert Data-2] (innodb_flush_log_at_trx_commit=0) MyISAM 1w:3/s InnoDB 1w:3/s MyISAM 10w:30/s InnoDB 10w:29/s MyISAM 100w:273/s InnoDB 100w:423/s [Insert Data 3] (innodb_buffer_pool_size=1024m) InnoDB 1w:3/s InnoDB 10w:33/s InnoDB 100w:607/s [Insert Data 4] (innodb_buffer_pool_size=256m, Innodb_flush_log_at_trx_commit=1, set autocommit=0) InnoDB 1w:3/s InnoDB 10w:26/s InnoDB 100w:379/s

[MySQL configuration file] (Default configuration)

code is as follows

copy code

# MySQL Server Instance Configuration File [client] port=3306 [MySQL] default-character-set=gbk [ MYSQLD] port=3306 basedir= "c:/mysql50/" datadir= "c:/mysql50/data/" DEFAULT-CHARACTER-SET=GBK Default-storage-engine=innodb sql-mode= "Strict_trans_tables,no_auto_create_user,no_engine_substitution" max_connections=100 Query_cache_size=0 table_cache=256 tmp_table_size=50m Thread_cache_size=8 myisam_max_sort_file_size=100g myisam_max_extra_sort_file_size=100g myisam_sort_buffer_size=100m key_buffer_size=82m read_buffer_size=64k read_rnd_buffer_size=256k sort_buffer_size=256k InnoDB _additional_mem_pool_size=4m innodb_flush_log_at_trx_commit=1 innodb_log_buffer_size=2m Innodb_buffer _pool_size=159m innodb_log_file_size=80m innodb_thread_concurrency=8