Shulou(Shulou.com)06/01 Report--

Difference between InnoDB and MyISAM in MySQL

1. Transactions and foreign keys

InnoDB has transactions, supports 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 to improve the performance of multi-user concurrent operations.

MyISAM manages non-transactional tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. MyISAM is a better choice if your app needs to perform a lot of SELECT queries

2. Full-text index

Innodb does not support full-text indexing, if you must use it, it is best to use search engines such as sphinx. myisam is not very good at supporting Chinese

However, the new version of Innodb is already supported.

3, lock

mysql supports three locking levels, row level, page level and table level;

MyISAM supports table-level locking and provides non-locking reads in SELECTs consistent with Oracle types

InnoDB supports row-level locking, and row locking of InnoDB tables is not absolute. If MySQL cannot determine the scope to scan when executing a SQL statement, InnoDB tables will also lock the entire table. Note the impact of gap locking.

Example: update table set num=1 where name like "%aaa%"

4. Storage

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

InnoDB, a disk-based resource is the InnoDB table space data file and its log file, the size of the InnoDB table is limited only by the size of the operating system file

Note: MyISAM tables are saved as files, and using MyISAM storage for cross-platform data transfer saves a lot of trouble

5. Index

Cluster index and index used by InnoDB are data and stored sequentially, so index and data can be cached.

MyISAM (heap organization table) uses non-clustered index, index and file separation, random storage, only cache index

6, concurrent

MyISAM reads and writes block each other: not only does MyISAM block reads while writing, MyISAM also blocks writes while reading, but reads themselves do not block other reads.

InnoDB read and write blocking is related to transaction isolation levels

The above is the difference between InnoDB and MyISAM in MySQL in detail, more please pay attention to other related articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.