Common engines for MySQL

In the MySQL database, the most commonly used engines are 2: InnoDB and Myiasm. This article will focus on the two engines, and how to choose the engine, and finally, what are the data structures used by these 2 engines?

First introduce the 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.

Next, say the Myiasm engine. It is the default engine for MySQL, but does not provide support for transactions, row-level locks, and foreign keys. 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.

We're talking about the choice of the two engines. In fact, it has been mentioned above. Here I have added two points:

1, large-capacity data sets 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.

2, high-volume INSERT statements (here is the INSERT statement) are performed faster in the Myiasm engine, but the UPDATE statement performs faster in InnoDB, especially when the concurrency is large.

Finally, what is the data structure of the indexes used by the two engines? The answer is a B + tree.

For the Myiasm engine, the content stored in the data structure of the B + tree 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 structures are stored in actual figures, and such indexes are called clustered indexes.

Reference: http://blog.csdn.net/lulei1217/article/details/50954232

Common engines for MySQL