Graphical MySQL index--b-tree (b+tree)

Read a lot about the index of the blog, talk about the same. But it never made me understand some concepts about indexes, such as B-tree index, hash index, unique index .... Perhaps a lot of people like me, did not understand the concept began to study b-tree,b+tree and other structures, resulting in the interview time irrelevantly replying!

What is an index?

An index is a data structure that helps MySQL to get data efficiently.

What can an index do?

Improve the efficiency of your data queries.

Index: Ordered Quick Find data Structure! The index affects the lookup after where, and the sort after order by.

I. Classification of indexes

1?? Partition from the storage structure: Btree index (b-tree or B+tree index), hash index, full-test full-text index, R-tree index.

2?? From the application hierarchy: Normal index, unique index, composite index

3?? Based on the logical (indexed) Order of the data in the physical order with the key value: Clustered index, non-clustered index.

? 1?? is described in the form of saving the index when stored, 2?? is the classification in the process of index use, and the two are divided at different levels. However, the index type usually refers to the classification at the application level.

Like mobile phone Category: Android phone, iOS phone and Huawei mobile phone, Apple phone, oppo mobile phone.

Normal index : that is, an index contains only a single column, and a table can have multiple single-row indexes

Unique index : The value of an indexed column must be unique, but a null value is allowed

Composite Index : An index that contains multiple columns

Second, the index of the underlying implementation (single-valued index)

mysql默认存储引擎innodb只显式支持B-Tree( 从技术上来说是B+Tree)索引，对于频繁访问的表，innodb会透明建立自适应hash索引，即在B树索引基础上建立hash索引，可以显著提高查找效率，对于客户端是透明的，不可控制的，隐式的。

Do not talk about the storage engine, only the implementation

Hash index

Based on the hash table implementation, only queries that accurately match all columns of the index are valid, and for each row of data, the storage engine computes a hash code for all indexed columns, and the hash index stores all the hash codes in the index, while holding pointers to each data row in the Index table.

B-tree index (MySQL using b+tree)

? B-tree can speed up data access because the storage engine no longer needs a full table scan to get data, and the data is distributed across nodes.

B+tree Index

? is an improved version of B-tree and is also the storage structure used by the database index index. The data is on the leaf node, and a sequential access pointer is added, each of which points to the address of the adjacent leaf node. In contrast to B-tree, you only need to look up two nodes for a range lookup to traverse. and B-tree need to get all the nodes, in contrast b+tree more efficient.

In conjunction with the storage engine (typically using B+tree by default)

Case: Suppose there is a student table, ID is the primary key

ID	name	Birthday
1	Tom	1996-01-01
2	Jann	1996-01-04
3	Ray	1996-01-08
4	Michael	1996-01-10
5	Jack	1996-01-13
6	Steven	1996-01-23
7	Lily	1996-01-25

Implementation in the MyISAM engine

The implementation in the InnoDB

Iii. issues

Q: Why does the index structure use B-tree instead of hash, binary tree, red-black tree by default?

Hash: Although it can be quickly positioned, but there is no order, IO complexity is high.

Binary tree: The height of the tree is uneven, cannot be self-balanced, the lookup efficiency is related to the data (the height of the tree), and the IO cost is high.

Red-black Tree: The height of the tree increases with the amount of data, and the IO cost is high.

Q: Why the official recommendation is to use the self-growing primary key as an index.

Combined with the characteristics of B+tree, the self-increment primary key is continuous, minimizing page splitting during insertion, even if the page splits, only a fraction of the split. And can reduce the movement of data, each insert is inserted into the last. In short, the frequency of splitting and moving is reduced.

To insert continuous data:

Inserting non-contiguous data

The original address: Li Qiang's personal blog (based on the Ssm,nginx+redis background architecture)

Graphical MySQL index--b-tree (b+tree)