MySQL Database Engine details

MySQL Database Engine details

As a Java programmer, we should be familiar with MySQL database engines, this article details the Innodb and MyIASM engines and their index structures. To consolidate your mastery of this knowledge.

Innodb Engine

The Innodb Engine supports ACID transactions in the database and implements four isolation levels of the SQL standard. For more information about database transactions and their isolation levels, see this article. The engine also provides row-level locks and foreign key constraints. It is designed to handle large-capacity database systems. It is actually a complete database system based on the MySQL background, when MySQL is running, Innodb creates a buffer pool in the memory to buffer data and indexes. However, the engine does not support FULLTEXT indexes and does not store the number of rows in the table.SELECT COUNT(*) FROM TABLETo scan the entire table. This engine is of course the first choice when you need to use database transactions. Because the lock granularity is smaller, write operations do not lock the entire table, so when the concurrency is high, Innodb engine will improve efficiency. However, row-level locks are not absolute. If MySQL cannot determine the scan range when executing an SQL statement, the InnoDB table will also lock the entire table.

MyIASM Engine

MyIASM is the default MySQL engine, but it does not support database transactions, and does not support row-level locks and Foreign keys. Therefore, when INSERT or UPDATE) when the data is written, the entire table needs to be locked, and the efficiency will be lower. However, unlike Innodb, MyIASM stores the number of rows in the table, soSELECT COUNT(*) FROM TABLEYou only need to read the saved values directly without scanning the entire table. MyIASM is also a good choice if the number of read operations for a table is much higher than that for write operations without the support of database transactions.

Two Engines

A large dataset tends to be an InnoDB Engine because it supports transaction processing and fault recovery. The size of the database determines the duration of fault recovery. InnoDB can use transaction logs to recover data, which is faster. Primary Key query will be quite fast in the InnoDB engine, but it should be noted that if the primary key is too long, it will also cause performance problems. I will discuss this issue below. A large numberINSERTStatement (in eachINSERTWrite multiple rows in the statement and insert them in batches) It will be faster in MyISAM,UPDATEThe statement in InnoDB is faster, especially when the concurrency is large.

Index -- Index

Index is a data structure that helps MySQL efficiently obtain data. Both MyIASM and Innodb use the tree data structure as the index. I have written an article about the tree. The tree is a great data structure, just for my own understanding, if you are interested, you can read it. Next I will talk about the index structures used by these two engines. Here, we should first talk about B-Tree and B + Tree.

B-Tree and B + Tree

B + Tree is a variant of B-Tree, so let's talk about B-Tree first. I believe everyone knows the red and black trees. This is when I learned algorithm some time ago, you can refer to the implementation of a red/black tree. In fact, the red and black trees are similar to 2, 3-search trees. These trees have two and three forks. B-Tree is similar. Each node of B-Tree can have d branches (forks) and d is called the degree of B-Tree, as shown in, each node can have four elements and five branches, so its degree is 5. The elements in B-Tree are ordered and smaller than 7 in the node pointed to by the pointer on the left of element 7, the pointer Between Elements 7 and 16 points to the elements in the nodes between 7 and 16. Only in this way can we find the elements efficiently: first, perform binary search from the root node, if it is found, the corresponding value is returned. Otherwise, the corresponding interval node is searched recursively until the corresponding element is found or the null pointer is found. If it is found, the search fails. This search is very efficient, and its time complexity is O (logN) (based on d, when d is very large, the height of the tree is very low ), because each retrieval only requires a maximum of H nodes in the tree.

Next, let's talk about B + Tree, which is a variant of B-Tree, as shown in the following two figures:

We can see that the internal node of B + Tree does not store data, but only the pointer, while the leaf node only stores data, not the pointer. In addition, a pointer pointing to the adjacent leaf node is added to each of its leaf nodes. This optimization improves the access performance of the interval, for example, in the second figure, to query all data whose keys are from 18 to 49, after 18 is found, you only need to traverse the nodes and pointers to access all data nodes at a time, the interval query efficiency is greatly mentioned.

Index Structure of MyISAM Engine

The index structure of the MyISAM engine is B + Tree. The content stored in the data domain of the B + Tree is the actual data address. That is to say, its index is separated from the actual data, the index points to the actual data.Non-clustered Index.

Index Structure of Innodb Engine

The index structure of MyISAM engine is also B + Tree, but the index file of Innodb itself is a data file, that is, the data domain of B + Tree stores actual data.Clustered Index. The key of this index is the primary key of the data table, so the InnoDB table data file itself is the primary index.

Because the data files in InnoDB need to be clustered by the primary key, InnoDB requires that the table have a primary key (MyISAM may not). If it is not explicitly specified, mySQL automatically selects a column that uniquely identifies a data record as the primary key. If this column does not exist, MySQL automatically generates an implicit field for the InnoDB table as the primary key, this field is 6 bytes in length and its type is long integer.

Different from MyISAM, InnoDB's secondary index data domain stores the primary key value instead of the address of the corresponding record. Therefore, when secondary index search is performed, the primary key is first found based on the secondary index, then, find the actual data based on the primary key index. Therefore, Innodb does not recommend that you use too long primary keys. Otherwise, the secondary index will become too large. We recommend that you use an auto-increment field as the primary key. In this way, each node of the B + Tree will be filled in order without frequent split and adjustment, which will effectively improve the efficiency of data insertion.