MySQL index b+tree and hash

The main contents of the MySQL index include the type of index commonly used by MySQL (primary key index, unique index, normal index and full-text index and composite index) and two commonly used structure B-tree, hash index (custom hash index and InnoDB Adaptive Hash Index)

B-tree (MongoDB)

B-tree is what we often call B-trees, B-trees. This data structure is often used to implement database indexing because it is relatively efficient to find.

Each disk IO reads the data we call a page. The size of a page is related to the operating system, typically 4k or 8k. This also means that when reading a page of data, there is actually a disk IO.

The time complexity of binary lookup tree queries is O (LOGN), the fastest to find and the least number of comparisons, since performance has been so excellent, but why the index is implemented using B-tree instead of binary lookup trees, the key factor is the number of disk IO.

Database indexes are stored on disk, and when the amount of data in a table is large, the size of the index grows, reaching several grams or even more. When we use the index to query, it is not possible to load all the index into memory, can only load each disk page, where the disk page corresponds to the node of the index tree.

From the two-tree lookup process, the height of the tree and the number of disk IO are 4, so the worst case of disk IO is determined by the height of the tree.

From the previous analysis of the situation, the number of disk IO must be compressed to compress the height of the tree, so that the tall tree as far as possible to become a stout tree, so b-tree in such a great time in the background of the birth.

B+tree (MySQL)

There is no difference between the query process and the B-tree. But there are actually two different points:

A, first B + Tree intermediate nodes do not store satellite data, so the same size of the disk page can accommodate more node elements, so that the same amount of data, B + tree is relatively more chunky, disk IO less times.

b, because only the leaf node to save the satellite data, B + tree each query to the leaf node, and the tree each query is not the same, the best case is the root node, the worst case is the leaf node, no B + tree stability.

A single node can store more elements, making the query disk IO less frequently.

All queries to find the leaf node, query performance stability.

All leaf nodes form an orderly chain list, which facilitates range querying.

Combined index

Alter table newcity add index idx (countrycode,district,Name);

The index is composed from the leftmost start. So three indexes were generated sequentially.

Use explain to view execution plans

MySQL index b+tree and hash