Application of B-Tree indexes in sqlserver and mysql

When talking about database performance optimization, we usually mention "indexes". However, many people do not really understand indexes, and do not know why indexes can speed up retrieval, so that indexes cannot be applied well in practice. In fact, the index can be said to be the cheapest and very effective optimization method. In general, the design of excellent indexes for query

When talking about database performance optimization, we usually mention "indexes". However, many people do not really understand indexes, and do not know why indexes can speed up retrieval, so that indexes cannot be applied well in practice. In fact, the index can be said to be the cheapest and very effective optimization method. In general, the design of excellent indexes for query

When talking about database performance optimization, we usually mention "indexes". However, many people do not really understand indexes, and do not know why indexes can speed up retrieval, so that indexes cannot be applied well in practice.

In fact, the index can be said to be the cheapest and very effective optimization method. In general, the design of excellent indexes can indeed achieve immediate results in the query performance optimization.

I believe that many readers know about and use indexes, and may have read or heard popular descriptions such as "Xinhua Dictionary" and "library, however, we are still confused about the storage structure and nature of indexes.

Readers with data structures and algorithms should have heard or practiced several common search algorithms, namely "sequential search, binary search (half-fold) search, and binary tree search. Among them, the sequential search efficiency is the lowest, its algorithm complexity is O (n), while the binary search algorithm complexity is O (logn), but data must be ordered, it is usually widely used in linked lists. However, binary tree search only requires O (log2n) complexity, but requires the data structure to be "tree ".

B-Tree indexes are the most widely used and supported relational databases. Considering that most readers have limited knowledge about data structures, to facilitate understanding, you can use B-Tree (or its variant B + Tree)

It is understood as a common binary tree. Although this is not accurate, I believe that after reading it, the reader has roughly understood why data searching through indexes is much faster than normal table scanning.

Clustered index in sqlserver

The leaf node (bottom node) of the clustered index directly contains the data page.

Non-clustered index in sqlserver

In a table with clustered indexes, the leaf nodes of non-clustered indexes contain the key values of clustered indexes (which can be understood as pointers of clustered indexes ).

In a heap table without clustered indexes, non-clustered indexes contain the RID (which can be understood as the pointer of data rows ).

In mysql, there are also "clustered Index" (for the InnoDB engine), "non-clustered Index" (for the MyIsam engine), "primary key index", and "secondary index".

Index Structure in mysql InnoDB Engine

In the primary key index, the leaf node contains the data rows (data pages), the leaf interface of the secondary index, and the key value of the primary key index (pointing to the primary key index)

Index Structure in mysql MyIsam Engine

There is no big difference between the primary key index and the secondary index structure. The data row information (such as row number) stored by each leaf node can direct to and locate the data row.

I believe that the structure, storage method, and principle of B-Tree indexes in sqlserver and mysql are roughly the same. Of course, there are also many differences between details and internal implementation.

Limited by the level and understanding of the author, all the text and descriptions in this article are written in the memory of the author, and errors are inevitable. Please be enthusiastic readers to criticize and correct them in time.

Due to the limited time, most of the pictures I painted are rough, please forgive me.

Http://blog.csdn.net/dinglang_2009