What does a mysql index do?

The index in the database is similar to the bibliographic index built in a university library, which can improve the efficiency of data retrieval and reduce the IO cost of the database. MySQL's performance will begin to gradually decline around 3 million records, so it is very necessary to create indexes when encountering large amounts of data.

#MySQL’s official definition of index is: Index is a data structure that helps MySQL obtain data efficiently.

We can simply understand it as: a data structure that can be quickly searched and sorted.

Mysql index mainly has two structures: B Tree index and Hash index.

What we usually call indexes, unless otherwise specified, generally refers to indexes organized in a B-tree structure (B Tree index). The index is shown in the picture above.​

​ The outermost light blue disk block 1 contains data 17, 35 (dark blue) and pointers P1, P2, P3 (yellow). The P1 pointer represents disk blocks less than 17, P2 is between 17-35, and P3 points to disk blocks greater than 35. Real data exists in cotyledon nodes, that is, the bottom layer 3, 5, 9, 10, 13... Non-leaf nodes do not store real data, but only store data items that guide the search direction, such as 17, 35 .

Search process: For example, to search for 28 data items, first load disk block 1 into the memory, an I/O occurs, and use binary search to determine the P2 pointer. Then it is found that 28 is between 26 and 30, disk block 3 is loaded into the memory through the address of the P2 pointer, and the second I/O occurs. Disk block 8 is found in the same way, and the third I/O occurs.

The real situation is that the above three layers of B Tree can represent millions of data. Millions of data only require three I/Os instead of millions of I/Os. The time improvement is huge.

The above is the detailed content of What does a mysql index do?. For more information, please follow other related articles on the PHP Chinese website!