Shulou(Shulou.com)06/01 Report--

This article mainly explains "what are the knowledge points of the server index". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "what are the knowledge points of the server index?"

The source of the index

Let's use the previous table structure as an example, where a table corresponds to a file. Here we add an ID column. This table is used to store employee information. In the employee management system, it is usually necessary to display all the information of a person, which requires all the attributes of a person to be found in the database at one time. Generally speaking, it will be looked up through the person's ID, because the name will be duplicated, and the ID can be added and not repeated. We take ID as the unique identity of a record and can be set as the primary key of the table in the relational database.

In the absence of an index, when I query all the information about an ID, I need a traversal, reading each row and comparing it, which is very slow when the amount of data is large. As a result, the additional structure (index) associated with the file is born.

Simply put, the index in the database is similar to the book catalog, recording the title and page number of each section.

Taking the above data as an example, we list the location of these records on disk on the left, and the index records the location of each record on disk: 1-> 10 ~ 2-> 20 ~ 3-> 30. As you can see, the index takes up much less space than the original data.

Each of our-> is an index entry (index entry) or index record (index record).

Generally, the index is based on certain fields, which can be called search keys (index fields). Only when the query is made on the index field, the query can be accelerated with the corresponding index structure. The index field in the above example is ID.

Of course, you can also build indexes on multiple fields. For example, an index is also set up on the height field, so that the query based on height is fast. Each indexed field can be called an indexed field.

According to the official definition of "database system concept":

An index entry consists of a key value and a pointer to one or more records with the key value. The pointer to the record includes an identification of the disk block and an intra-block offset that identifies the recording within the disk block.

Classification of indexes

The first classification method is what we often call primary index (clustered index) and secondary index (nonclustered index).

This classification is related to the way the records are sorted in the file. If the records in the file are sorted and stored on disk in the order of an index field, the index is called the primary index or clustered index (Clustered Index). Indexes on other fields are called secondary indexes or nonclustered indexes (NonClustered Index). To put it simply: the primary index is related to the disk order, and the secondary index is independent.

You can also refer to the SQL Server documentation (https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-server-2017) for clustered and nonclustered indexes.

There is at most one clustered index on a file because the disk is one-dimensional and can only be sorted by one field.

What we are talking about today is a sequential index, that is, the index is sorted according to the order of some field values, and the data items in the file are organized sequentially.

Dense and sparse

In the sequential index, the index is divided into dense index and sparse index, dense index is that each record has an index entry. In the sparse index, only part of the records correspond to the index items.

Dense indexing is easy to understand, as in the example above, recording an index entry for each person's ID and location.

If you treat every sentence in the book as a data item, the catalog is a sparse index. When we find the page number of a chapter, the content of this chapter is between this chapter and the next chapter.

That is, sparse indexes must be clustered, and nonclustered indexes must be dense. These two sentences are a little roundabout, you can think about it carefully, this is to support fast query. After you have figured it out, you can make up a jingle: sparse must be orderly, disorder must be dense.

Evaluation index of index

Before there is no index, we just need to update the data file, and after we have the index file, we need to update the index file at the same time. Therefore, the index is a tradeoff among write speed, space footprint, and query performance. Among them, write speed and query performance are the two most important aspects.

The quality of an index can be evaluated from the following angles:

Access type: that is, supported query modes, such as single point query or range query, fuzzy query or precise query, etc.

Access time: that is, the time to query a specific data item or a set of data items.

Insert time: the time when a new data item is located and inserted in the index structure.

Delete time: the time when an item of data is located and deleted in the index structure.

Space overhead: the extra space occupied by the index structure.

At this point, I believe you have a deeper understanding of "what are the knowledge points of the server index?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.