When it comes to database index, you must be very familiar with it, and you often come into contact with it in your daily work. I have read a lot of related articles, books and courses these days. I decided to sum up an article by myself. Although the article I wrote is certainly not as good as the article written by all kinds of gods on the Internet, I still remember it more firmly after I summed it up. This should also be a good learning habit. No matter how beautiful other people’s handwriting is, it’s all other people’s handwriting. Even if the handwriting is scribbled, at least you can recognize it.
Index is a kind of data structure to improve our query efficiency. It’s like a dictionary directory, a dictionary with hundreds of pages. If you want to find a word quickly, you can’t turn it hard.
MySQL index is generally hash table or B + tree. The common InnoDB engine uses B + tree as the data structure of index by default.
If B + tree is used as index data structure, the time complexity of accessing or modifying a piece of data is O (log n), but when hash table is used as index structure, the time complexity is O (1). If you just check a piece of data or modify a data, it is awesome to use the hash table to index it. But the general business system is not so simple.
In business development, we often meet the requirements of range query and sort query. At this time, the hash table index can not efficiently handle these requirements. It can only achieve these functions by scanning the table, which should be a nightmare for the database.
MySQL uses B + tree data structure. Non leaf nodes only store key values, while leaf nodes store data or primary keys. And in the leaf node, the keys are stored in order, which makes the range query and Sorting Query extremely simple.
Although hash table index is very efficient in operating single column data, B + tree data structure is obviously more suitable when range query and sort query are needed. In our business development, it is impossible to operate only one line of data. In general, B + tree is more suitable for index data structure.
Hash table index does not support range query, can not use index to sort, and does not support the leftmost matching principle of joint index. If there are many duplicate key values, it is easy to cause hash collision and further reduce efficiency.
The non leaf node of B + tree only stores key values, while the non leaf node of B tree stores not only key values but also data. In MySQL database, the size of data page is fixed, and the default size of InnoDB engine data page is 16 KB. B + tree this approach is to make the tree order larger, make the tree more dwarf. When querying, the number of disk IO will be reduced, and the query efficiency will be faster.
All the data of B + tree are stored in leaf nodes, and the key values are arranged orderly. But the data of B-tree is scattered in each node. B-tree is certainly not as efficient as B + tree in range query and sorting query.
Disk block 1 stores 17 and 35 data items, as well as P1, P2 and P3 pointers. P1 represents the disk block whose data items are less than 17, P2 represents the data items whose data items are between 17 and 35, and P3 represents the data items whose data items are greater than 35. The non leaf node does not store data, but only the data items that guide the search direction.
We know the size of one data page per IO read, that is, one disk block. Suppose we want to find the data item 29. First, we perform the first IO to read disk block 1 into memory, and find 17 < 29 < 35. Then we select the P2 pointer to perform the second IO, and read disk block 3 into memory, and find 26 < 29 < 30. Then we select the P2 pointer to read disk block 8 into memory, and do a binary search in internal memory, find 29, and end the query.
By analyzing the query process, we can know that the IO times are directly proportional to the height of the B + tree. H is the height of the tree, M is the number of data items in each disk block, and N is the total number of data items. It can be seen from the following formula that if the data amount n is fixed, the larger the M is, the smaller the corresponding H is.
M is equal to the size of the disk block divided by the size of the data item. Because the size of the disk block is generally fixed, reducing the size of the data item can make m larger and make the tree shorter and fatter. This is also the reason why B + trees put real data on leaf nodes instead of non leaf nodes. If real data is placed on non leaf nodes, the number of data items stored in disk blocks will be greatly reduced, the tree will be increased, and the IO times of corresponding query data will be increased.
Here we first assume that the height of B + tree is 2, that is, there is a root node and several leaf nodes. Assuming that the data size of a row of records is 1 KB, then the number of records in a single leaf node (page) is 16 KB / 1 KB = 16 data.
Then, to calculate how many pointers a non leaf node can store, let’s assume that the primary key ID is bigint type, the length is 8 bytes, and the pointer size is set to 6 bytes in InnoDB source code, so that there are 14 bytes in total. The number of such units we can store in a page actually represents the number of pointers, that is, 16 KB / 14 b = 1170. Then we can calculate a B + tree with height of 2, which can store 1170 * 16 = 18720 pieces of data.
According to the same principle, we can calculate that a B + tree with height of 3 can store 21902400 pieces of data. Therefore, in InnoDB, the height of B + tree is generally 1-3 layers, which can meet the requirement of ten million level data storage. When searching data, one page search represents one IO, so it usually only needs 1-3 logical IO operations to find data through primary key index query.
- Hash table index can operate on a single data row very quickly, but it does not support range query, can not use index to sort, and does not support the leftmost matching principle of joint index.
- The data of B-tree can be stored in non leaf nodes, and there may be additional random disk IO during range query. And because the real data is stored in non leaf nodes, the height of B-tree is higher than that of B + tree in the same case. This is not conducive to improving efficiency.
- B + tree stores real data in leaf nodes to make the tree shorter and fatter, reduce IO times and improve efficiency.
Here are some notes I took when I learned the index structure of MySQL, and I will summarize a note on the use of index. I found that after reading the articles of various gods, I would be impressed if I wrote them again. Although it’s fried rice, it’s fried for yourself. I hope you can give me more encouragement, ha ha ha.
I thought I knew a lot about MySQL index until I met an interviewer from Ali.
Why MySQL uses B + tree – belief oriented programming
An article explains why MySQL uses B + tree to realize index Tencent cloud
How many rows of data can a B + tree store in InnoDB? -Tencent cloud
MySQL index principle and slow query optimization – meituan technical team
Understanding MySQL InnoDB B + tree index – alicloud developer community
This work adoptsCC agreementReprint must indicate the author and the link of this article