Shulou(Shulou.com)05/31 Report--

This article mainly introduces the relationship between the ordered index in SQL and order by, which has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, let the editor take you to understand it.

In general, there are two ways to implement order by. One is to use the ordered index to realize it automatically, that is to say, there is no other sorting operation because of the ordered index. The other is to sort the results after selecting them. Using an ordered index is, of course, the fastest, but there are some limitations, let's take a look at the following test.

Test data: the student table has two fields: id, sid, and id are the primary keys. There are 20W records, and id from 1 to 200000 minute sid is also from 1 to 200000.

The first situation:

The field of order by is not in the where condition or in the select

Select sid from zhuyuehua.student where sid

The second situation:

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

Select id,sid from zhuyuehua.student where sid

The third situation:

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

Select sid from zhuyuehua.student where sid < 50000 and id < 50000 order by id

The fourth situation:

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

Select sid from zhuyuehua.student where sid < 50000 and id < 50000 order by id desc

Test results:

The field of order by is not in where condition and there is no sort operation in select.

The field of order by is not in the where condition, but there is a sort operation in select.

Fields of order by have no sort operation in where condition but not in select

Fields of order by are in where condition but not in select (in reverse order) no sort operation

Conclusion:

When the order by field appears in the where condition, the index is utilized without a sort operation. In other cases, order by does not have a sort operation.

Analysis:

Why the index of the order by field is used to avoid sorting only if it appears in the where condition. It's time to talk about how the database gets the data we need.

A SQL can actually be divided into three steps.

1. Get the data

two。 Processing data

3. Return the processed data

For example, the above statement select sid from zhuyuehua.student where sid < 50000 and id < 50000 order by id desc

The first step: generate an execution plan based on where conditions and statistics to get the data.

Step 2: sort the resulting data.

When processing data (order by) is executed, the database first looks at the execution plan of the first step to see if the fields of order by take advantage of the index in the execution plan. If so, the already sorted data can be obtained directly by using the index order. If not, sort the operation.

Step 3: return the sorted data.

In addition:

The above 50, 000 data sort only uses 25ms, so you may think that sort doesn't take up much resources. However, because 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 carried out in memory, which is a kind of CPU consumption for the database. Due to the enhanced performance of CPU, the sorting of dozens or hundreds of ordinary records will not have a great impact on the system. But when your recordset increases to more than a million, you need to pay attention to whether it is necessary to do so. Large recordset sorting not only increases CPU overhead, but also may cause hard disk sorting due to insufficient memory, which will lead to a sharp decline in performance when hard disk sorting occurs.

Note: both ORACLE and DB2 have a space for SORT operations (memory sorting mentioned above). For example, ORACLE is the user's global area (UGA), which contains settings for parameters such as SORT_AREA_SIZE. If the sort overflow (hard disk sorting) occurs when the amount of sorted data is large, the performance will be much lower.

Summary:

When the fields in the order by appear in the where condition, the index is used instead of sorting, or more precisely, the fields in the order by take advantage of the index in the execution plan without sorting.

This conclusion is valid not only for order by, but also for other operations that require sorting. Such as group by, union, distinct and so on.

Size: 16 KB

Size: 16 KB

Size: 20.6 KB

Size: 21 KB

Thank you for reading this article carefully. I hope the article "what is the relationship between ordered Index in SQL and order by" shared by the editor will be helpful to you. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you 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.