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

1. Changes of MySQL default storage engine

In versions prior to MySQL 5.1, the default search engine was MyISAM, and since MySQL 5.5, the default search engine has been changed to InnoDB.

2. The main features of MyISAM and InnoDB storage engines.

MyISAM storage engine is characterized by table-level locks, no support for transactions and full-text indexing, which is suitable for some CMS content management systems as background databases, but the table lock structure is inadequate in large concurrency and heavy-load production systems.

The following are the version features of the MySQL 5.7 MyISAM storage engine:

The InnoDB storage engine features row-level locks, transaction security (ACID compatible), support for foreign keys, and no support for indexes of type FULLTEXT (indexes of type FULLTEXT are supported after version 5.6.4). The InnoDB storage engine provides a transaction secure storage engine with commit, rollback, and crash recovery capabilities. InnoDB is designed for maximum performance when dealing with large quantities. Its CPU efficiency may be unmatched by any other disk-based relational database engine.

The following are the version features of the MySQL 5.7 InnoDB storage engine:

Note:

Row locks on InnoDB tables are not absolute either. If MySQL cannot determine the range to scan when executing a SQL statement, the InnoDB table will also lock the entire table, such as update table set num=1 where name like "a%".

The main difference between the two types is that InnoDB supports transactions with foreign keys and row-level locks. MyISAM does not support it. So it is easy to think that MyISAM is only suitable for small projects.

3. Performance test of MyISAM and InnoDB

The following two pictures show the official stress test results of MyISAM and InnoDB.

It can be seen that with the increase of the number of CPU cores, the throughput of InnoDB is better, while that of MyISAM is almost unchanged. Obviously, MyISAM's table locking mechanism reduces the throughput of reads and writes.

4. Whether the transaction is supported or not

MyISAM is a non-transactional engine, which enables the MySQL of MyISAM engine to provide high-speed storage and retrieval, as well as full-text search ability, which is suitable for frequent query applications such as data warehouse.

InnoDB is transaction secure.

Transactions are an advanced way of processing, such as in some column additions and deletions, as long as any error can be rolled back, but MyISAM is not.

5. The difference between MyISAM and InnoDB in composition.

(1) each MyISAM is stored as three files on disk:

The first file name starts with the name of the table, the extension indicates the file type, and the .frm file stores the table definition.

The second file is a data file with the extension .MYD (MYData).

The third file is the index file with the extension .myi (MYIndex).

(2) the disk-based resources are InnoDB tablespace data files and its log files. The size of InnoDB tables is only limited by the size of operating system files, which is generally 2GB.

6. Interpretation of MyISAM and InnoDB table locks and row locks

There are two modes of MySQL table-level locks: table shared read locks (Table Read Lock) and table exclusive write locks (Table Write Lock). What does it mean, that is, when reading a MyISAM table, it will not block other users' read requests to the same table, but it will block writes to the same table, while writing to the MyISAM table will block other users' read and write operations to the same table.

InnoDB row locks are achieved by locking index items, that is, InnoDB uses row-level locks only when data is retrieved through index conditions, otherwise table locks are used! Row-level locks consume more resources than table locks each time they acquire and release locks. When a deadlock occurs between two InnoDB transactions, the number of rows affected by each transaction is calculated, and the transaction with a small number of rows is rolled back. When Innodb is not involved in the locked scene, InnoDB is undetectable. It can only be solved by locking timeout.

7. Whether to save the specific number of rows of the table in the database

The specific number of rows of the table is not saved in InnoDB, that is, when select count (*) from table is executed, InnoDB scans the entire table to calculate how many rows there are, but MyISAM can simply read out the number of saved rows.

Note that when the count (*) statement contains the where condition, the operation of the two tables is the same. This is one of the cases where InnoDB uses table locks as described in "6" above.

8. How to choose

MyISAM is suitable for:

(1) do a lot of count calculations; (2) insert infrequently, query very frequently, if you execute a large number of SELECT,MyISAM is a better choice; (3) no transactions.

InnoDB is suitable for:

(1) High reliability requirements or transactions; (2) frequent table updates and queries, and high chances of table locking specify the creation of the data engine; (3) if your data performs a large amount of INSERT or UPDATE, you should use InnoDB tables for performance reasons; (4) when DELETE FROM table, InnoDB does not re-establish the table, but deletes one row by one (5) LOAD TABLE FROM MASTER operation does not work on InnoDB, the solution is to first change the InnoDB table to MyISAM table, import data and then change to InnoDB table, but for the use of additional InnoDB features (such as foreign keys) is not applicable to the table.

Note that the code to create each table is the same, except for the final TYPE parameter, which is used to specify the data engine.

Other differences:

1. For fields of type AUTO_INCREMENT, the InnoDB must contain an index with only that field, but in the MyISAM table, a federated index can be established with other fields.

2. When DELETE FROM table, InnoDB does not re-establish the table, but deletes it row by row.

3. LOAD TABLE FROMMASTER operation does not work on InnoDB. The solution is to change the InnoDB table to MyISAM table first, and then change it to InnoDB table after importing data, but it is not applicable to tables that use additional InnoDB features (such as foreign keys).

4. The InnoDB storage engine is fully integrated with the MySQL server, and the InnoDB storage engine maintains its own buffer pool to cache data and indexes in main memory.

5. For self-growing fields, the InnoDB must contain an index with only that field, but a federated index can be established with other fields in the MyISAM table.

6. When emptying the entire table, InnoDB is deleted row by row, which is very slow. MyISAM rebuilds the table.

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support. If you want to know more about it, please see the relevant links below.

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.