Application of B-tree index in SQL Server and MySQL

When talking about database performance optimizations, it is common to refer to "index", but many people do not really understand the index, and do not know why the index can speed up the retrieval, so that in practice, the index is not good to apply.

In fact, the index can be said to be the cheapest and most effective one of the optimization methods, in general, well-designed indexes on query performance optimization does have an immediate effect.

Believe that many readers, understand and use the index, may have seen or heard the "Xinhua Dictionary", "library" and other relatively popular description, but the index of the storage structure and nature still more confused.

Readers with data structures and algorithmic foundations should have heard or practiced "sequential lookup, binary lookup (binary) lookup, binary tree lookup", which are common search algorithms. Among them, sequential lookup efficiency is the lowest, its algorithm complexity is O (n), and the binary search algorithm complexity is O (LOGN) but requires data to be ordered, usually in the list of widely used. The complexity of binary tree lookup is only O (log2n), but the data structure is called "tree".

In the mainstream relational database, the most widely used and supported B-tree indexes. Given the limited knowledge of most of the reader's data structures, readers can put b-tree (or its variants B+tree) for ease of understanding

Understood as a common two-fork tree. Although this is not accurate, it is believed that after reading the reader, it has been generally understood why it is much faster to find data by index than regular table scans.

Clustered index in SQL Server

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

Nonclustered indexes in SQL Server

In a table with a clustered index, a leaf node for a nonclustered index contains a key value for the clustered index (a pointer that can be understood as a clustered index).

In a heap table without a clustered index, a nonclustered index contains a RID (a pointer that can be interpreted as a data row).

In MySQL, there are usually "clustered indexes" (for InnoDB engines) and "nonclustered Indexes" (for MyISAM engines), primary key index, and level two index.

The index structure in the MySQL InnoDB engine

In the primary key index, the leaf node contains the data row (data page), the leaf interface of Level Two index, the key value of the primary key index (the primary key index)

The index structure in the MySQL MyISAM engine

There is not much difference between the primary key index and the two-level index structure, and the data row information (such as row number, etc.) that the leaf node holds can be directly pointed to and anchored to the data row

It is easy to see that the structure, storage and principle of the B-tree index in SQL Server and MySQL are roughly the same. Of course, there are also many details and differences in internal implementations.

Limited to the author level and understanding Limited, all the text and description of the article by the author's memory to write, inevitably error, please be enthusiastic readers timely criticism and correction.

Due to the limited time, most of the pictures of the author of the relatively rough, please understand the reader.

This article from http://blog.csdn.net/dinglang_2009, reproduced please indicate the source.

Application of B-tree index in SQL Server and MySQL