Shulou(Shulou.com)05/31 Report--

MySQL index to improve the query efficiency of what is the reason, many novices are not very clear about this, in order to help you solve this problem, the following small series will explain in detail for everyone, there are people who need to learn, I hope you can gain something.

Disk IO and read-ahead:

Let's talk about disk IO first. Disk reading data depends on mechanical movement. Every time you read data, you need to seek, find points and copy to memory.

Seek time is the time required for the magnetic arm to move to the designated track, generally less than 5ms;

Point seeking is to find the point where data exists from the track, the average time is half a turn time, if it is a 7200 rpm disk, the average point seeking time is 600000/7200/2=4.17ms;

Copy to memory time is very fast, and the first two times compared to negligible, so an IO time is about 9ms on average. It sounds fast, but the database's million-level data has reached 9000s, which is obviously disaster level.

Considering that disk IO is a very expensive operation, the computer operating system has made pre-read optimization. When an IO occurs, not only the data of the current disk address, but also the adjacent data are read into the memory buffer, because when the computer accesses the data of an address, the adjacent data will also be accessed quickly.

Each IO read data we call a page (page), how much data a page has is related to the operating system, generally 4k or 8k, that is, when we read a page of data, actually occurred once IO.

(I suddenly thought of a question I was asked just after graduation, in a 64-bit operating system, how many bytes does the int type in Java occupy?) What's the maximum? Why?)

If we want to optimize database queries, we must minimize disk IO operations, so there is an index.

What is an index?

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

The indexes commonly used in MySQL are physically divided into two categories, B-tree indexes and hash indexes.

This time, we focus on BTree index.

BTree index

BTree is also called multi-path balanced search tree. The characteristics of an m-fork BTree are as follows:

Each node in the tree contains at most m children.

Every node except root and leaf nodes has at least [ceiling (m/2)] children (ceiling () rounded up).

If the root node is not a leaf node, it has at least two children.

All leaf nodes are at the same level.

Each non-leaf node consists of n keys and n+1 pointers, where [ceiling (m/2)-1]

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.