[SQL Learning] The connection between ordered index and order by

Generally, there are two implementation methods for order by. One is to use the ordered index to automatically implement it. That is to say, using the orderliness of the ordered index does not require additional sorting operations. The other is to sort the results after selecting them.Using ordered index is of course the fastest, but there are some restrictions. Let’s take a look at the test below.

Test data: The student table has two fields id, sid, and id is the primary key. There are a total of 20W records, with IDs ranging from 1 to 200000, and sids also ranging from 1 to 200000.

The first situation:

The order by field is neither in the where condition nor in the select

select sid from zhuyuehua.student where sid

##Second case:

order by The field is not in the where condition but in the select.

select id,sid from zhuyuehua.student where sid


The third case:

The order by field is in the where condition but not in the select.

select sid from zhuyuehua.student where sid

##Fourth case:

The order by field is in the where condition but not in the select. Sort in reverse order

select sid from zhuyuehua.student where sid


Test results:

The field of order by is not in the where condition and is not in the select There is a sorting operation

The field of order by is not in the where condition but in select There is a sorting operation

The field of order by is in the where condition but not in the select No sorting operation

The fields of order by are in the where condition but not in the select (reverse order) No sorting operation

Conclusion:


When the order by field appears in the where condition, the index will be used without sorting operation.
In other cases, order by will not perform sorting operations.analyze:


Why only when the order by field appears in the where condition, the index of the field will be used to avoid sorting.
This is about how the database gets the data we need.A SQL can actually be divided into three steps.

1. Get data

2. Process data

3. Return the processed data

For example, the above statement select sid from zhuyuehua.student where sid
Step 1: Generate execution based on where condition and statistical information Plan, get data.

Step 2: Sort the obtained data.


When executing data processing (order by), the database will first check the execution plan of the first step to see whether the fields of order by use indexes in the execution plan. If so, you can use the index order to directly obtain the sorted data.
If not, sort operation.Step 3: Return the sorted data.

in addition:

The above 50,000 data sort only took 25ms. Maybe you think the sort does not take up much resources. However, since the data in the above table is ordered, sorting takes less time. If it is a relatively unordered table, the sort time will increase a lot. In addition, sorting operations are generally performed in memory, which is a kind of CPU consumption for the database. Due to the enhanced performance of the CPU, ordinary sorting of dozens or hundreds of records will not have a great impact on the system. big. But when your record set increases to more than one million records, you need to pay attention to whether you must do this. Sorting of large record sets not only increases CPU overhead, but also may cause hard disk sorting due to insufficient memory. When a hard disk occurs Performance drops dramatically when sorting.

Note: ORACLE or DB2 have a space for SORT operations (the memory sorting mentioned above). For example, in ORACLE, it is the user global area (UGA), which contains settings for parameters such as SORT_AREA_SIZE. If the amount of data being sorted is large, sorting overflow (hard disk sorting) will occur, and the performance will be reduced a lot.

Summarize:



When the fields in order by appear in the where condition, the index will be used without sorting. To be more precise, the fields in order by are in the execution plan When using indexes, no sorting operation is required.#This conclusion is not only valid for order by, but also for other operations that require sorting. For example,

group by, union, distinct, etc.
If you want to know more tutorials about sql, please pay attention to the PHP Chinese website

!

The above is the detailed content of [SQL Learning] The connection between ordered index and order by. For more information, please follow other related articles on the PHP Chinese website!