What is the underlying implementation principle of MySQL index?

This article mainly introduces the underlying implementation principle of MySQL index, which is very detailed and has certain reference value. Friends who are interested must read it!

The underlying implementation principle of MySQL Index

1. Hash index

2. BTree index and B+Tree index

3. Full-text index

(1) BTree index

(2) B+Tree index

(3) the advantages of B+Tree over BTree:

I. Preface

II. Type of index

I. Preface

MySQL supports many storage engines, and each storage engine supports different indexes, so MySQL database supports a variety of index types, such as BTree index, B+Tree index, Hash index, full-text index and so on.

II. Type of index

1. Hash index

Only the memory (memory) storage engine supports the Hash index. The Hash index refers to the value of the index column to calculate the hashCode of the value, and then stores the physical location of the row data of the value in the corresponding location of the hashCode. Because the hashing algorithm is used, the access speed is very fast, but a value can only correspond to one hashCode, and the hash is distributed, so the Hash index does not support the function of range search and sorting.

2. BTree index and B+Tree index

(1) BTree index

BTree index is a balanced search for multi-forked trees. If the depth of the tree is 2d (d > 1) and the height is h, then BTree must meet the following conditions:

The height of each leaf node of ① should be the same, which is equal to h

Each leaf node of ② consists of 1 key and n pointer point, where d