Shulou(Shulou.com)06/01 Report--

This article mainly introduces the method of improving the query efficiency of MySQL index, which has a certain reference value, friends who need can refer to it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

Text

The index of MySQL is essentially a data structure.

Let's first take a look at the data loading of the computer.

Disk IO and pre-read:

First talk about disk IO, disk read data depends on mechanical movement, each read data needs to seek, find a point, copy to memory three steps.

Seek time is the time it takes for the magnetic arm to move to the specified track, usually below 5ms

The search point is to find the point where the data exists in the track, and the average time is half circle time. If it is a disk with 7200 rpm / min, the average search time is 600000/7200/2=4.17ms.

The time to copy to memory is very fast, which is negligible compared to the previous two times, so the average time of an IO is around 9ms. It sounds fast, but the million-level data in the database reaches 9000s at a time, which is obviously disaster-level.

Considering that disk IO is a very expensive operation, the computer operating system optimizes pre-reading. When an IO, 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.

Every time the data read by IO is called page, how much big data is related to the operating system, usually 4k or 8k, that is, when we read the data in a page, IO actually occurs only once.

I suddenly thought of a question I was asked just after graduation. How many bytes does the int type in Java account for in a 64-bit operating system? What's the maximum? Why? )

So if we want to optimize the database query, we should minimize the IO operation of the disk, so the index appears.

What is the index?

MySQL officially defines index as: index (Index) is a data structure that helps MySQL to obtain data efficiently.

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

This time, we will mainly talk about BTree index.

BTree index

BTree is also called multi-path balanced search tree. An m-ary BTree has the following features:

Each node in the tree contains a maximum of m children. In addition to the root node and leaf node, each node has at least [ceil (m))] children (ceil () is rounded up). If the root node is not a leaf node, there are at least two children. All the leaf nodes are on the same layer. Each non-leaf node consists of n key and 1 pointer, where [ceil (m _ hand 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.