How does a "serial" relational database work? (4)-array, binary search tree

After we understand the idea of hiding behind time complexity and sequencing, I have to talk about 3 more data structures. They are extremely important because they are the cornerstone of modern databases. I will also introduce the concept of the index in passing.

Array

A two-dimensional array is the simplest data structure, and a database table can be viewed as a two-dimensional array, for example:

A two-dimensional array is a table that has both rows and columns:

• A row represents a topic (record)
• A column is a feature that describes a topic (record)
• Each column stores the same type of data (integer, string, date ...)

While a table can store and display data well, it behaves badly when you need to search for data.

For example, if you want to find all the British friends, you have to look at each row of data and determine whether he or she is English. This consumes n operations (n indicates the number of rows), and although it doesn't seem to be much of a bad thing, let's think, is there a better way? The answer is the tree.

Note that most modern databases provide advanced arrays for more efficient storage of tables, such as heap-based, index-based. However, this does not solve the problem of fast searching under certain conditions.

Tree and Database Indexes

A binary search tree is a two-fork tree with specific attributes, each of which satisfies the following properties:

• Node value greater than all left subtrees
• Node value less than all right subtree

Let's use the graph to more visually illustrate the two-fork search tree:

In the tree contains 15 nodes, let's search for a node with a value of 208:

• I started from root node 136 and found 126<208, so we looked at the root node 136 right subtree;
• 398>208, so continue to view the left subtree of the 398 node;
• 250>208, so continue to view the left subtree of the 250 node;
• 200<208, so continue looking at the left subtree of the 200 node, but find that the 200 node does not have a left subtree, so a node with a value of 208 does not exist.

Let's change a value to search for, say 40:

• I started from root node 136 and found 136>40, so we looked at the left subtree of root node 136;
• 80>40, so continue to view the left subtree of the 80 node;
• 40=40, the node exists. If ROWID is included in each node, then we can look up the specified rowid in the table;
• In fact, once we know rowid, we can pinpoint the position of this row of data in the table so that I can get this row of data immediately.

The end-of-search operation is the height of the tree. If you read carefully the previous chapters on merge sorting, you should be able to infer that the actual operand is log (N), a good effect!

Back to the question

Above we describe a simple scenario where the node value is an integer, so what if our node value is not an integer? Consider the nationality of each person in the table above, which is a string type. Suppose you have a tree with a value of nationality:

• If you want to find all the people of UK nationality;
• You can find the tree and find the node with the value of UK;
• In the UK node, you can then get the position of this line of nationality in the UK on the node (ROWID);

The entire search process consumes log (n) operations, rather than n times when using arrays. The process above is actually the process of finding the specified row through the database index.

Once you have a function that compares column values, you can sort any type of column, and it means that you can construct an index tree. ( Translator Note: The key is to be able to compare column values, otherwise can not be sorted )

In the next section we continue to introduce very very very very much ( translator Note: Important thing to say three times ) important B+tree index.

How does a "serial" relational database work? (4)-array, binary search tree