Three major features of the index, example application

Three features of Indexes

1 index height

 

When SQL queries data (select), the indexing efficiency varies significantly depending on the index height, the data block to be read by the database to access the index is usually the index height + the number of data blocks. That is to say, the higher the index height, the more data blocks to be read by the index, and the worse the efficiency.

 

 

From the test above, we can see that as the table index height increases, the consistent reads produced by the query index also increases.

 

2 Index storage column Value

 

By analyzing an index block, we can know that the index block not only stores rowid information, but also stores the value of the index column, this feature can be used when the queried value is in the index or some aggregate calculations (such as sum, Max, min.

 

3. The index itself is ordered.

 

From the index storage structure, we can see that the index storage is stored in an orderly manner. During index scanning, the index starts from the root node and passes through the neck node to the leaf node, in this case, the index range query or equivalent query only needs to scan a range to obtain the results, because the index itself has a range, we can use the index feature to reduce the sorting operations of actual queries.

 

 

In which scenarios will indexes not be used (the index is not valid)

 

. 1. The index cost is higher.

 

2. Index column type conversion occurred.

 

3. perform various operations on the index Column

 

Three major features of the index, example application