Mysql storage engine: differences between InnoDB and MyISAM/evaluate/test performance

Introduction to InnoDB and MyISAM: This is the default type. It is based on the traditional ISAM type, and ISAM is the abbreviation of IndexedSequentialAccessMethod (sequential access method with indexes, it is a standard method for storing records and files. compared with other storage engines, MyISAM has most tools for checking and repairing tables. myISAM tables can be pressed

Introduction to InnoDB and MyISAM: This is the default type. It is based on the traditional ISAM type, and ISAM is the abbreviation of Indexed Sequential Access Method (Sequential Access Method with indexes, it is a standard method for storing records and files. compared with other storage engines, MyISAM has most tools for checking and repairing tables. myISAM tables can be pressed

Introduction to InnoDB and MyISAM

MyISAM: This is the default type. It is based on the traditional ISAM type, and ISAM is the abbreviation of Indexed Sequential Access Method (Sequential Access Method with indexes, it is a standard method for storing records and files. compared with other storage engines, MyISAM has most tools for checking and repairing tables. myISAM tables can be compressed and support full-text search. they are not transaction-safe and do not support foreign keys. If a transaction is rolled back, incomplete rollback is not atomic. If you execute a large number of SELECT statements, MyISAM is a better choice.

InnoDB: This type is transaction-safe. it has the same features as the BDB type and supports foreign keys. the InnoDB table is fast. it has more features than BDB. Therefore, if you need a transaction-safe storage engine, we recommend that you use it. if your data executes a large number of INSERT or UPDATE operations, InnoDB tables should be used for performance considerations,

For the InnoDB type labels that support transactions, AUTOCOMMIT is enabled by default, and the program does not explicitly call BEGIN to start the transaction. As a result, each inserted entry is automatically Commit, the speed is seriously affected. You can call begin before executing the SQL statement. Multiple SQL statements form a transaction (even if you open the autocommit statement), which greatly improves the performance.

========================================================== ======================================

Differences between InnoDB and MyISAM

InnoDB and MyISAM are the two most commonly used table types in MySQL, each with its own advantages and disadvantages, depending on the specific application.

The following are the known differences between the two.

Innodb
InnoDB provides MySQL with a transaction-safe (ACID compliant) table with transaction (commit), rollback, and crash recovery capabilities. InnoDB provides locking on row level and non-locking read in SELECTs ). These features improve the performance of multi-user concurrent operations. In the InnoDB table, no need to expand the lock escalation, because the row level locks of InnoDB is suitable for a very small space. InnoDB is the first MySQL table engine to provide foreign key constraints (foreign key constraints.

InnoDB is designed to handle large-capacity database systems. Its CPU utilization is incomparable to other disk-based relational database engines. Technically, InnoDB is a complete database system on the MySQL background. InnoDB establishes a dedicated buffer pool in the primary memory for high-speed data buffering and indexing. InnoDB stores data and indexes in tablespaces and may contain multiple files, which is different from others. For example, in MyISAM, tables are stored in separate files. The size of the InnoDB table is limited by the file size of the operating system, generally 2 GB.
All InnoDB tables are stored in the same data file ibdata1 (multiple files or independent tablespace files), which is relatively difficult to back up, the free solution can be copying data files, backing up binlogs, or using mysqldump.
MyISAM
MyISAM is the default storage engine of MySQL.

Each MyISAM table is stored in three files. Frm file storage table definition. The data file is MYD (MYData ). The index file is an extension of MYI (MYIndex.

Because MyISAM is relatively simple, it is much more efficient than InnoDB .. It is a good choice for small applications to use MyISAM.

MyISAM tables are saved as files. Using MyISAM storage in cross-platform data transfer saves a lot of trouble.

The following are some differences between details and specific implementations:

1. InnoDB does not support FULLTEXT indexes.
2. innoDB does not store the specific number of rows in the table. That is to say, when you execute select count (*) from table, InnoDB needs to scan the entire table to calculate the number of rows, however, MyISAM simply needs to read the number of lines saved. Note that when the count (*) statement contains the where condition, the operations on the two tables are the same.
3. For fields of the AUTO_INCREMENT type, InnoDB must contain only the index of this field. However, in the MyISAM table, you can create a joint index with other fields.
4. When deleting FROM table, InnoDB does not create a new table, but deletes a row.
5. the load table from master operation does not work for InnoDB. The solution is to first change the InnoDB TABLE to the MyISAM TABLE, and then change the imported data to the InnoDB TABLE, however, it is not applicable to tables that use additional InnoDB features (such as foreign keys.

In addition, the row lock of the InnoDB table is not absolute. If MySQL cannot determine the scope to be scanned when executing an SQL statement, the InnoDB table will also lock the entire table, for example, update table set num = 1 where name like "% aaa %"

Any type of table is not omnipotent. You only need to select a proper table type for the business type to maximize the performance advantage of MySQL.

========================================================== ======================================

The following are some relationships and differences between InnoDB and MyISAM:

1. MySQL 4.0 and above support transactions, including non-max versions. 3.23 requires the max version mysqld to support transactions.

2. If no type is specified during table creation, the default value is myisam. transactions are not supported.
You can run the show create table tablename command to view the table type.

2.1 The start/commit operation on a table that does not support transactions has no effect. It has been submitted before the execution of the commit operation. test:
Execute an msyql:
Use test;
Drop table if exists tn;
Create table tn (a varchar (10) type = myisam;
Drop table if exists ty;
Create table ty (a varchar (10) type = innodb;

Begin;
Insert into tn values ('A ');
Insert into ty values ('A ');
Select * from tn;
Select * from ty;
You can see a record.

Execute another mysql:
Use test;
Select * from tn;
Select * from ty;
Only tn can see one record
Then on the other side
Commit;
You can see the record.

3. You can run the following command to switch the non-transaction table to the transaction (data will not be lost). The innodb table is safer than the myisam table:
Alter table tablename type = innodb;

3.1 innodb tables cannot use the repair table command and myisamchk-r table_name
However, you can use check table and mysqlcheck [OPTIONS] database [tables]

4. The following parameters are added to the command line for starting the mysql database to enable the newly released mysql data tables to use transactions by default (
Only the create statement is affected .)
-- Default-table-type = InnoDB

Test command:
Use test;
Drop table if exists tn;
Create table tn (a varchar (10 ));
Show create table tn;

5. You can temporarily change the default table Type:
Set table_type = InnoDB;
Show variables like 'table _ type ';
Or:
C: \ mysql \ bin \ mysqld-max-nt -- standalone -- default-table-type = InnoDB

========================================================== ========================

Explain the differences between "InnoDB" and "MyISAM"

InnoDB and MyISAM are the two most common table types used by many people when using MySQL. The two table types have their own advantages and disadvantages, depending on the specific application. The basic difference is that the MyISAM type does not support advanced processing such as transaction processing, while the InnoDB type does. MyISAM tables emphasize performance, and the execution speed is faster than that of InnoDB, but transactions are not supported. InnoDB provides advanced database functions such as external keys for transactions.

MyIASM is a new version of the IASM table and has the following extensions:

· Binary hierarchy portability.

· NULL column index.

· There are fewer fragments for Long-varying rows than the ISAM table.

· Supports large files.

· Better index compression.

· Better key statistics distribution.

· Better and faster auto_increment processing.

The following are some differences between details and specific implementations:

◆ 1. InnoDB does not support FULLTEXT indexes.

◆ 2. innoDB does not store the specific number of rows in the table. That is to say, when you execute select count (*) from table, InnoDB needs to scan the entire table to calculate the number of rows, however, MyISAM simply needs to read the number of lines saved. Note that when the count (*) statement contains the where condition, the operations on the two tables are the same.

◆ 3. For fields of the AUTO_INCREMENT type, InnoDB must contain only the index of this field. However, in the MyISAM table, you can create a joint index with other fields.

◆ 4. When deleting FROM table, InnoDB does not create a new table, but deletes a row.

◆ 5. the load table from master operation does not work for InnoDB. The solution is to first change the InnoDB TABLE to the MyISAM TABLE, and then change the imported data to the InnoDB TABLE, however, it is not applicable to tables that use additional InnoDB features (such as foreign keys.

In addition, the row lock of the InnoDB table is not absolute. If MySQL cannot determine the scope to be scanned when an SQL statement is executed, the InnoDB table will also lock the entire table, for example, update table set num = 1 where name like "% aaa %"

To sum up, none of the tables is omnipotent. Only by selecting the appropriate table type for the business type can we maximize the performance advantages of MySQL.

========================================================== ========================

Simple Performance Test of MyISAM engine and InnoDB Engine in MySQL

[Hardware configuration]
CPU: AMD2500 + (1.8 GB)
Memory: 1 GB/modern
Hard Disk: 80 GB/IDE

[Software configuration]
OS: Windows XP SP2
SE: PHP5.2.1
DB: MySQL5.0.37
Web: IIS6

[MySQL table structure]

CREATE? TABLE? 'Myisam '? (
'Id '? Int (11 )? NOT? NULL? Auto_increment,
'Name '? Varchar (100 )? Default? NULL,
'Content '? Text,
PRIMARY? KEY ?? ('Id ')
)? ENGINE = MyISAM? DEFAULT? CHARSET = gbk;

CREATE? TABLE? 'Innodb '? (
'Id '? Int (11 )? NOT? NULL? Auto_increment,
'Name '? Varchar (100 )? 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 table types. MySQL storage engine includes the engine for processing transaction security tables and the engine for processing non-transaction security tables: · MyISAM manages non-transaction tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. MyISAM is supported in all MySQL configurations. It is the default storage engine, unless you configure MySQL to use another engine by default. · The MEMORY storage engine provides "in-MEMORY" tables. The MERGE storage engine allows a set to process the same MyISAM table as a separate table. Like MyISAM, MEMORY and MERGE storage engines process non-transaction tables. Both engines are included in MySQL by default. Note: The MEMORY storage engine is officially identified as the HEAP engine. · InnoDB and BDB storage engines provide transaction security tables. BDB is included in the MySQL-Max binary distribution version released for the operating system that supports it. InnoDB is also included in all MySQL 5.1 binary distributions by default. You can configure MySQL to allow or disable any engine as you like. · The EXAMPLE storage engine is a "stub" engine, which does not do anything. You can use this engine to create tables, but no data is stored or retrieved from them. The purpose of this engine is to provide a service. In the MySQL source code example, it demonstrates how to start writing a new storage engine. Similarly, it is mainly interested in developers. ";

[Insert data-1] (innodb_flush_log_at_trx_commit = 1)
MyISAM 1 W: 3/s
InnoDB, W: 219/s

MyISAM 10 W: 29/s
InnoDB 10 W: 2092/s

MyISAM 100 W: 287/s
InnoDB 100 W: dare not test

[Insert data-2] (innodb_flush_log_at_trx_commit = 0)
MyISAM 1 W: 3/s
InnoDB 1 W: 3/s

MyISAM 10 W: 30/s
InnoDB 10 W: 29/s

MyISAM 100 W: 273/s
InnoDB 100 W: 423/s

[Insert data 3] (innodb_buffer_pool_size = 1024 M)
InnoDB 1 W: 3/s
InnoDB 10 W: 33/s
InnoDB 100 W: 607/s

[Insert data 4] (innodb_buffer_pool_size = 256 M, innodb_flush_log_at_trx_commit = 1, set autocommit = 0)

InnoDB 1 W: 3/s
InnoDB 10 W: 26/s
InnoDB 100 W: 379/s

[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 = 50 M
Thread_cache_size = 8
Myisam_max_sort_file_size = 100G
Myisam_max_extra_sort_file_size = 100G
Myisam_sort_buffer_size = 100 M
Key_buffer_size = 82 M
Read_buffer_size = 64 K
Read_rnd_buffer_size = 256 K
Sort_buffer_size = 256 K

Innodb_additional_mem_pool_size = 4 M
Innodb_flush_log_at_trx_commit = 1
Innodb_log_buffer_size = 2 M
Innodb_buffer_pool_size = 159 M
Innodb_log_file_size = 80 M
Innodb_thread_concurrency = 8

[Summary]

It can be seen that in MySQL 5.0, the performance of MyISAM and InnoDB Storage engines is not very different. For InnoDB, innodb_flush_log_at_trx_commit is the main option that affects performance. If it is set to 1, therefore, data is automatically submitted each time it is inserted, resulting in a sharp decline in performance. It should be related to refreshing logs. Setting it to 0 can significantly improve the efficiency. Of course, similarly, you can submit "set autocommit = 0" in SQL to SET the performance. In addition, I also heard that setting innodb_buffer_pool_size can improve the performance of InnoDB, but I did not find it significantly improved.

Basically, we can consider using InnoDB to replace our MyISAM engine, because InnoDB has many good features, such as transaction support, stored procedures, views, row-level locking, etc, in the case of a lot of concurrency, I believe that InnoDB must be much better than MyISAM. the configuration in cnf is also critical. A good configuration can effectively accelerate your application.

If it is not a complex Web application, non-critical application, you can continue to consider MyISAM. You can consider the specific situation as needed.

Reference URL:

Http://dev.mysql.com/doc/refman/5.1/zh/index.html

Http://dev.mysql.com/doc/refman/5.1/zh/storage-engines.html#innodb

========================================================== =

Author: fu_zk posted on 0:03:40 Original article link

Read: 126 comments: 0 view comments

Original article address: mysql storage engine: differences between InnoDB and MyISAM, evaluation, evaluation, and performance testing. Thank you for sharing with me.