为什么limit前面加上order by 索引查询性能会更好?
SELECT * FROM sys_client
LIMIT 100000,10 耗时:0.0004
而:
SELECT * FROM sys_client
ORDER BY id DESC LIMIT 100000,10
耗时:0.0001
找了个2千5百万数据的表进行测试,id是自增主键。
用下面语句进行测试,关query cache,每个连续跑10遍,计算平均耗时:
SELECT * FROM user_orders limit 100000,10; | 71.3ms |
SELECT * FROM user_orders order by id desc limit 100000,10; | 83.4 ms |
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.4 ms |
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.9 ms |
SELECT * FROM user_orders order by id asc limit 10000000,10; | 6918.7ms |
limit offset小的时候,加order by走索引只扫少量行更快,而offset较大时,加order by会更慢,全表扫描反而会快。