Understanding the implementation of indexes from a simple perspective

00-Background Knowledge

-B-Tree & B + tree

Http://en.wikipedia.org/wiki/ B %2B_tree
Http://en.wikipedia.org/wiki/B-tree

-Binary Search)

Http://en.wikipedia.org/wiki/Binary_search_algorithm

-Database performance problems

A. the disk Io performance is very low, seriously affecting the database system performance.
B. Sequential disk read/write performance is much higher than random read/write performance.

-Basic Data Storage Structure

A. the disk space is divided into blocks or pages of the same size ).
B. These data blocks of a table are linked together in a linked list.
C. Data is stored in the disk block in a row ,.
D. read or write at least one complete block from the disk at a time when accessing data.

Fig. 1

01-implementation of basic data operations

Basic operations include insert, update, delete, and select.

-Select

A. Locate data
B. Read the data block and process the data
C. return data to the user

-Update and delete

A. Locate data
B. Read the data block and modify the data.
C. write back to disk

-Insert

A. Locate the page for data insertion (if data needs to be sorted)
B. Read the data page to be inserted and insert data.
C. write back to disk

How to locate data?
-Table Scan)

A. Read all data blocks from the disk in sequence, and match the data in one row.
B. the time complexity is O (n). If all data occupies 100 blocks. Although only one row of data is queried,
You also need to read all the data of 100 blocks.
C. A large number of disk I/O operations are required, which greatly affects the performance of data locating.

Because data location operations are required for all data operations, the efficiency of data location operations directly affects the efficiency of all data operations.
Therefore, we began to think about how to reduce disk I/O?
-Reduce disk Io

A. Reduce disk space occupied by data
CompressionAlgorithmOptimize the Data Storage Structure
B. reduce the total amount of data accessed
Some of the data read or written is required for data operations.Valid data. Remaining
Part of the data is not required for data operations. It is called invalid data. For example, the query name is a record of 'zhang san.
This record is valid, and other records are invalid. We need to try to reduce access to invalid data.

02-index generation

-Key)

First, we find that in most cases, the location operation does not need to match the entire row of data. Instead, it matches only one
Or the values of several columns. For example, you can use the 1st column in the figure to determine a record. These columns are used to determine a piece of data.
CalledKey).

Fig. 2

-Dense Index

Based on the principle of reducing access to invalid data, we take the key value and store it in an independent block. And add
Add a pointer to point to the original data block .,

Fig. 3

This is the ancestor of the 'index '.Dense Index. Table scanning is not performed when locating. Instead
Index Scan), Read all index blocks in sequence for key-value matching. After a matched key value is found,
Directly read the corresponding data block based on the pointer of this row. Assume that 100 rows of data can be stored in one block,
10,000,000 rows of data require 100,000 block storage space. Assume that the key-value column (+ pointer) occupies one row of data.
1/10 space. About 10,000 pieces are required to store the dense index. Therefore, we use approximately 1/10 additional storage
In exchange for the positioning efficiency of the full table scan.

03-index Evolution

In practical applications, such positioning efficiency still cannot meet requirements. A lot of people may have thought of it, by sorting and searching
Algorithm to reduce Io access.Therefore, we began to try to sort and store the dense index, and expected to use the sorting Query
Find algorithms to reduce disk Io.

-Half-block search

A. Sort dense Indexes
B. An array is required to store the index block address in sequence. Blocks do not store the addresses of all rows.
C. The index block address array should also be stored on the disk. Store the blockchain separately, as shown in.
D. the time complexity of semi-query is O (log 2 (N)). In the preceding column, the dense index contains a total of 10,000 blocks. Assume that one block
2000 pointers can be stored, and 5 blocks are required to store the array. By searching with half a block, we only need to read at most
5 (array block) + 14 (index BlockLog 2 (10000) + 1 (data block) = 20.

Fig. 4

 

-Sparse Index

When block-based half-fold search is implemented, it is found that after reading each block, it only needs to match the key value of the first line to determine the next block.
). Therefore, valid data is the first row of each block (except the last block. Or based on reducing none
The I/o principle of Data efficiency is to take out the data of the first row of each block separately and put it together with the address of the index array. In this way
You can perform a half-fold search on this array. As shown in, this array evolvedSparse Index.

Fig. 5

Because the storage structure of sparse index and dense index is the same, the occupied space is also the same. About
10 blocks are required to store the address and the first row key value of the 10000 dense index blocks. Through sparse index, only need to read
10 (sparse block) + 1 (dense block) + 1 (data block) = 12 blocks.

-Multi-layer sparse Index

Because the sparse index itself is ordered, you can create a sparse index for the sparse index. Pass
This method creates sparse indexes layer by layer until the sparse index at the top occupies only one block.
As shown in.

Fig. 6

A. The top sparse index is called the root of the entire index tree ).
B. Start searching from the root each time you perform the locating operation.
C. Each layer of index only needs to read one block.
D. The underlying dense index or data is called a leaf (leaf ).
E. you must search for the leaf node for each search to locate the data.
F. The layers of indexes are called the height of the index tree ).
G. the I/O performance of the index is closely related to the height of the index tree. The higher the index tree, the more disk I/O.

In our example, sparse index has only 10 blocks, so we only need to create a sparse index.
When searching through two-layer sparse index and one-layer dense index, you only need to read 1 + 1 + 1 = 4 blocks.

-Difference between dense index and sparse Index

A. Dense index contains the key values of all data, but sparse index only contains some key values.
Sparse index occupies less disk space.
B. The data indicated by the dense index can be unordered, but the data of the sparse index must be ordered.
C. sparse index can be used for indexing, but dense index cannot.
D. sparse index is more effective when data is ordered. Therefore, the dense index is only used for unordered data.
E. Index scan is actually used to traverse the dense index layer.

-Cluster Index and secondary index)

If the data is sorted based on a specific key, you can directly create a sparse index on the data,
You do not need to create a dense index layer (data can be considered as the dense index Layer ). As shown in:

Fig. 7

This index is what we often say:"Clustered IndexThe key used to sort data is called the primary key.Primary Key.

A. A table can only have one clustered index, because data can only be sorted by one key.
B. When you use other keys to create an index tree, you must first create a dense index layer.
Sort. Such an index tree is calledSecondary Index.
C. A table can have multiple secondary indexes.
D. traverse the Cluster Index, which is actually to traverse the data. Therefore, the traversal efficiency of the Cluster Index is lower than that of the secondary group index.
For example, in the select count (*) operation, it is more efficient to use secondary group index traversal.

-Range search)

Because the key value is orderedSearch by range. You only need to separate data blocks and dense index blocks with two-way linked lists.
Connection, you canEfficient range search. As shown in:

Fig. 8

Range search process:

A. select an appropriate boundary value to locate the block where the value data is located.

B. Select the proper direction and traverse the data block (or dense index block) chain.

C. Until the data does not meet another boundary value, end the range search.

Are you familiar with this index tree? Let's look at this picture from another angle!

Fig. 9

This is clearly the legendary B + tree.

-Operations on Indexes

A. Insert a key value

B. delete a key value

C. Split a node

D. merge two nodes

These operations are introduced in textbooks and will not be introduced here.

Write it here first. It's really hard to write it. It's easy to understand, and it's hard to write it clearly. In the next article, I plan to talk about several issues about the Standard B + tree and

B + tree deformation during implementation.

A lot of knowledge comes from the following two books.

"Database systems: the complete book (2nd edition )"
"Transaction Processing: concepts and techniques"

Recommendation relevanceArticle:

Understanding the implementation of indexes from a simple perspective (2)