The difference between MyISAM and InnoDB

The difference between MyISAM and InnoDB

$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 in the face of developers.

Hardware configuration

cpu:amd2500+ (1.8G)
Memory: 1g/Modern
Hard drive: 80g/ide

2: Software configuration

Os:windows XP SP2
se:php5.2.1
db:mysql5.0.37
Web:iis6

3:MYSQL table Structure

CREATE TABLE ' MyISAM ' (
' id ' int (one) not NULL auto_increment,
' Name ' varchar (+) 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;

4: Data contents

$name = "Heiyeluren";

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

5.MySQL configuration file (default configuration)

# 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
 

MyISAM and InnoDB Storage engine performance difference is not very large, for the InnoDB, the impact of performance is mainly innodb_flush_log_at_trx_commit this option, if set to 1, then each time you insert the data will be automatically submitted, resulting in a sharp drop in performance, should be related to the refresh log, set to 0 efficiency can see a significant increase, of course, you can also submit in SQL "Set autocommit = 0" To set to achieve good performance. In addition, it is also heard that by setting up innodb_buffer_pool_size can improve the performance of InnoDB, but I test found no particularly noticeable improvement.

Basically we can consider using InnoDB to replace our MyISAM engine, because InnoDB own many good characteristics, such as transaction support, stored procedure, view, row level lock and so on, in many cases, believe that InnoDB performance is certainly much stronger than MyISAM, of course , the corresponding configuration in the MY.CNF is also more critical, good configuration, can effectively accelerate your application.