MySQL data storage engine InnoDB and MyISAM advantages and differences

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 to 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.

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. Features that are richer 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 the InnoDB table for performance reasons.

For INNODB types that support 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.

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

InnoDB and MyISAM are the most commonly used two table types in MySQL, each with its own pros and cons, depending on the specific application. The following is a known difference between the two, for informational purposes only.

InnoDB

InnoDB provides MySQL with transaction security (commit), rollback (rollback), and crash repair (crash recovery capabilities) (Transaction-safe (ACID compliant ) Type table. InnoDB provides a row lock (locking on row level) that provides an unlocked read (non-locking read in selects) consistent with the Oracle type. These features improve the performance of multi-user concurrency operations. You do not need to extend the lock (lock escalation) in the InnoDB table because InnoDB column locks (row level locks) are suitable for very small spaces. InnoDB is the first table engine on MySQL that provides a foreign key constraint (FOREIGN key constraints).

InnoDB's design goal is to handle a large-capacity database system, which is not comparable to other disk-based relational database engines. Technically, InnoDB is a complete database system in the MySQL background, InnoDB to build its dedicated buffer pool in main memory for caching data and indexing. InnoDB the data and index in a tablespace, possibly containing multiple files, unlike other, for example, in MyISAM, tables are stored in separate files. The size of the InnoDB table is limited to the file size of the operating system, typically 2 GB.

InnoDB All tables are stored in the same data file ibdata1 (or multiple files, or separate tablespace files), relatively difficult to backup, the free scheme can be copied data files, backup Binlog, or with mysqldump.

MyISAM

MyISAM is the MySQL default storage engine.

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.

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

Here are some of the links and differences between InnoDB and MyISAM!

1. More than 4.0 mysqld support transactions, including non-max versions. 3.23 requires Max version mysqld to support transactions.

2. When you create a table, the default is MyISAM if you do not specify a type, and transactions are not supported.

You can look at the type of the table with the Show CREATE TABLE tablename command.

2.1 does not have any effect on the start/commit of tables that do not support transactions, submitted before the execution of a commit, testing:

Execute a MSYQL:

Use test;

drop table if exists TN;

Create TABLE TN (a varchar (a)) Type=myisam;

drop table if exists Ty;

Create table Ty (a varchar (a)) Type=innodb;

Begin

Insert into TN values (' a ');

Insert into Ty values (' a ');

SELECT * from TN;

select * from Ty;

Can see a single record.

Execute another MySQL:

Use test;

SELECT * from TN;

select * from Ty;

Only TN can see a record.

And then on the other side

Commit

To see the record.

3. You can perform the following command to switch non-transaction tables to transactions (data is not lost), and InnoDB tables are more secure than MyISAM tables:

ALTER TABLE TableName TYPE=INNODB;

3.1 InnoDB table cannot be used with repair table commands and MYISAMCHK-R table_name

But you can use check table, and Mysqlcheck [OPTIONS] database [tables]

4. The following parameters are added to the command line that launches the MySQL database to make the newly published MySQL data table default to the use transaction (

Affects only the CREATE statement. )

--default-table-type=innodb

Test command:

Use test;

drop table if exists TN;

Create TABLE TN (a varchar (10));

Show CREATE TABLE TN;

5. Temporarily change the default table type can be used:

Set Table_type=innodb;

Show variables like ' table_type ';

Or:

C:\mysql\bin\mysqld-max-nt--standalone--default-table-type=innodb