mysql - 为什么limit前面加上order by 索引查询性能会更好？

Answer 1

I found a table with 25 million data for testing. The id is an auto-incrementing primary key.

Use the following statement to test, turn off the query cache, run each time 10 times in a row, and calculate the average time:

SELECT * FROM user_orders limit 100000,10;	71.3ms
SELECT * FROM user_orders order by id desc limit 100000,10;	83.4ms
SELECT * FROM user_orders order by id asc limit 100000,10;	69.3ms
SELECT * FROM user_orders limit 200000,10;	133.2ms
SELECT * FROM user_orders order by id desc limit 200000,10;	178.4ms
SELECT * FROM user_orders order by id asc limit 200000,10;	133.4ms
SELECT * FROM user_orders limit 10000000,10;	6429.5ms
SELECT * FROM user_orders order by id desc limit 10000000,10;	8270.9ms
SELECT * FROM user_orders order by id asc limit 10000000,10;	6918.7ms

limit When the offset is small, it is faster to add order by to scan only a small number of rows in the index. When the offset is large, it is slower to add order by, and the full table scan is faster.

Answer 2

If no index is added, SELECT * FROM sys_client LIMIT 100000,10 will 全表扫描, and then the 10 records from 100001 to 100010 will be fetched;
After adding the index, only 100010 records will be retrieved instead of the full table search. So the execution efficiency will be better!

Answer 3

In the first statement without order by, the mysql processing steps are as follows: order by 语句中，mysql 处理步骤是这样的：

1. 根据语句取出 10 条数据；

2. 对这十条数据进行排序；

3. 返回这 10 条数据；

但是在第二条语句中，假设你的 id 字段为主键索引，mysql 处理步骤是这样的：

1. 取出 10 条数据；

2. 利用索引顺序直接取得已经排好序的数据；

3. 返回这 10 条数据；

总结一下：
如果 order by

1. Retrieve 10 pieces of data based on the statement;🎜
2. 🎜Sort these ten pieces of data;🎜
3. 🎜Return these 10 pieces of data;🎜

🎜But in the second statement, assuming your id field is a primary key index, the mysql processing steps are as follows: 🎜

1. 🎜Get 10 pieces of data;🎜
2. 🎜Use the index sequence to directly obtain the sorted data;🎜
3. 🎜Return these 10 pieces of data;🎜

🎜To summarize:
If the fields of order by utilize indexes during execution. You can use the index sequence to directly obtain the sorted data. If not, the sorting operation is performed. 🎜

Answer 4

Is your ID indexed?

Answer 5

0.0001... You need to verify a more complex sql