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

First of all, it is important to understand that the storage engine in MySQL database loyalty is based on tables, not databases.

Describe the difference between the two:

InnoDB storage engine, mainly for OLTP (Online Transaction Processing, online transaction processing) applications, is the first storage engine to fully support ACID transactions (BDB the first storage engine to support transactions, has stopped development).

Features:

Row lock design, support for foreign keys

Support for consistent unlocked reads similar to Oracle style (read operations do not produce locks by default)

InnoDB puts the data in a logical tablespace, which is managed by InnoDB itself. Starting with the MySQL4.1 version, you can store the tables of each InnoDB storage engine in a separate ibd file.

InnoDB achieves high concurrency by using MVCC (multi-version concurrency control: read does not block write and write does not block read) and implements four isolation levels of the SQL standard (REPEATABLE level by default)

InnoDB also provides high performance and high availability functions such as insert buffer (insert buffer), second write (double write), adaptive hash index (adaptive hash index), pre-read (read ahead) and so on.

InnoDB uses clustered to store the data in the table, and each target storage is stored in the order of the primary key (if there is no explicit primary key specified when the table is being built, InnoDB generates a 6-byte ROWID for each row and uses it as the primary key)

The InnoDB table has three hidden fields: in addition to the 6-byte DB_ROW_ID mentioned above, there are 6-byte B_TX_ID (transaction ID) and 7-byte DB_ROLL_PTR (pointing to the address of the corresponding rollback segment). This can be seen through innodb monitor.

MyISAM storage engine is the official storage engine provided by MySQL, which is mainly for OLAP (Online Analytical Processing, online analytical processing) applications.

Features:

Transactions are not supported, table places and full-text indexing are supported. Operation speed is fast

The MyISAM storage engine table is composed of MYD and MYI. MYD is used to store data files and MYI is used to store index files.

The MySQL database only caches its index files, and the cache of the data files is left to the operating system itself to complete.

Starting from the MySQL5.0 version, MyISAM supports 256T single table data by default.

Finally, let's sum up:

1. InnoDB supports things, but MyISAM does not support things

2. InnoDB supports row-level locks, while MyISAM supports table-level locks

3. InnoDB supports MVCC, but MyISAM does not.

4. InnoDB supports foreign keys, but MyISAM does not.

5. InnoDB does not support full-text indexing, but MyISAM does.

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.