MySQL Database common engine

In the MySQL database, the most commonly used engines are 2: InnoDB and Myiasm.

First of all:

1. Brief introduction of these two engines, and how to choose.
2. What are the data structures used by these two engines?

1.

A.innodb engine, the InnoDB engine provides support for database acid transactions. It also provides row-level and foreign-key constraints. Its design goal is to deal with large data capacity of the database system. It itself is actually a complete system based on the MySQL backend. When MySQL runs, InnoDB creates buffer pools in memory for buffering data and indexes. However, the engine does not support full-text search. At the same time, startup is slower, and it is not saving the number of rows in the table. When the Select COUNT (*) from table instruction is made, the full table needs to be scanned. So when you need to use a database transaction, the engine is the first choice. Because of the small size of the lock, the write operation does not lock the full table. Therefore, the use of high-concurrency scenarios will improve efficiency.

B.myiasm engine, which is the default engine for MySQL, but does not provide support for transactions, row-level locks and foreign keys are not supported. Therefore, when you execute the INSERT INSERT and UPDATE statement, you need to lock the table when you perform a write operation. Therefore, the efficiency will be reduced. Unlike InnoDB, however, the Myiasm engine is the number of rows in the table, so when a select count (*) From table statement is made, you can directly read the saved values without having to scan the entire table. Therefore, if the table reads much more than the write operation, and does not require the support of the transaction. You can use MYIASM as the database engine first.

Add 2 points:

C. Large-capacity datasets tend to choose InnoDB. Because it supports transaction processing and failure recovery. InnoDB can use the data log for data recovery. The primary key query is also relatively fast in InnoDB.

D. When a large number of INSERT statements (here is an INSERT statement) are performed faster in the Myiasm engine, the UPDATE statement executes faster in InnoDB, especially when the concurrency is large.

2. What is the data structure of the index used by the two engines?

Answer: All the B + trees!

The Myiasm engine, the B + tree, stores content in the data structure, which is actually the address value of the actual data. That is, its index is separate from the actual data, but the index is used to point to the actual data. The pattern for this index is called a nonclustered index.

The data structure of the index of the InnoDB engine is also a B + tree, except that the data in the data structure is actually stored, and this index is called a clustered index.

MySQL Database common engine