Principles of database indexing

Reference: http://www.ituring.com.cn/article/986

Why do I need an index

For example, the function of an index is the same as the phonetic directory in front of the dictionary.

If a dictionary 3000 pages, we want to find the word ' rope ', if there is no Pinyin directory we will start from scratch, with the Pinyin directory we can now find the Pinyin directory ' suo ', and then the ' suo ' on the front and back page to find the word ' rope '.

The same is true for databases. The data is stored as a block on disk, which is equivalent to a dictionary page. These blocks of data on disk are similar to linked lists, where they all contain a data segment and a pointer pointing to the memory address of the next node (the data block), and none of them require continuous storage (that is, logically contiguous chunks can be physically far apart).

We set up a table

Field	Data type	BYTE size
Id	Int	4 bytes
Name	CHAR (4)	4 bytes

Assuming that the secondary database has only one table, the initial size of the database is 1MB, then the database is partitioned into n=1024/8=128; if there are 256 records then each database has r=256/128=2 records.

If there is no index, we need to traverse all data blocks. Searching by ID, we need 128 times to find a name we need. That is, n times .

If the "ID" is indexed, that is, from small to large, we can use a binary tree to find the form. Then we only need 8 times, namely log2 256 = 8 times. That's log2n .

As shown

So the essential difference between indexes is that they look differently. No index is a linear lookup, and an indexed lookup is a two-prong tree lookup.

Principles of database indexing