The difference between InnoDB and MyISAM in MySQL

The difference between InnoDB and MyISAM in MySQL

1. Transactions and foreign keys

InnoDB has Transactions, supporting 4 transaction isolation levels, rollback, crash recovery capabilities and multi-version concurrent transaction security, including ACID. If your application needs to perform a large number of INSERT or UPDATE operations, you should use InnoDB, which can improve the performance of multi-user concurrent operations

MyISAM manages non-transaction tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. If a large number of SELECT queries need to be executed in the application, then MyISAM is a better choice

2. Full-text index

Innodb does not support full-text index, if you must use it , it is best to use search engines such as sphinx. Myisam does not support Chinese very well

But the new version of Innodb already supports

3, lock

mysql supports three locking levels. Row level, page level, table level;

MyISAM supports table-level locking and provides non-locking read in SELECTs consistent with Oracle type

InnoDB supports row level Locks, row locks of InnoDB tables are not absolute. If MySQL cannot determine the range to be scanned when executing a SQL statement, the InnoDB table will also lock the entire table. Pay attention to the impact of gap locks

For example, update table set num=1 where name like “�a%”

4. Storage

MyISAM is stored in three files on the disk. The name of the first file starts with the name of the table, and the extension indicates the file type. The .frm file stores the table definition. The extension of the data file is .MYD. The extension of the index file is .MYI

InnoDB. Disk-based resources are the InnoDB table space data file and its log file. The size of the InnoDB table is only limited by the size of the operating system file

Note: MyISAM tables are saved in the form of files. Using MyISAM storage in cross-platform data transfer will save a lot of trouble

5. Index

InnoDB (index organized table) use The clustered index and index are data, which are stored sequentially, so the index can be cached, and the data can also be cached.

MyISAM (heap-organized table) uses a non-clustered index. The index is separated from the file and is stored randomly. It can only Cache index

6. Concurrency

MyISAM reading and writing block each other: not only will it block reading when writing, MyISAM will also block writing when reading. InnoDB read and write blocking is related to the transaction isolation level

Recommended tutorial: "

" " MySQL Tutorial》

The above is the detailed content of The difference between InnoDB and MyISAM in MySQL. For more information, please follow other related articles on the PHP Chinese website!