Differences between InnoDB and MyISAM In the MySQL storage engine

The following articles mainly introduce the differences between the MySQL storage engine (InnoDB) and MyISAM, including evaluation, evaluation, and related performance tests, the following is a detailed description of the article, hoping to help you in this regard.

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 MySQL storage engines, MyISAM provides 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 MySQL 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 can form a transaction even if autocommit is opened. This will greatly improve 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 and may also be 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 MySQL storage engine 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 without losing data). 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’; 
 
 

The above content is related to the MySQL storage engine: differences between InnoDB and MyISAM, evaluation, evaluation, and performance testing. I hope you will get some benefits.