mysql - 倒序时选择慢10倍

Question

为什么这个选择，称之为A，(0.02406s):

select * from char_kills
where rid_first <= 110 and rid_last >= 110
order by kills desc
limit 500;

比排序顺序颠倒时慢 10 倍，称之为 B，(0.00229 秒):

select * from char_kills
where rid_first <= 110 and rid_last >= 110
order by kills
limit 500;

您如何优化 A？在 MySQL 5.5 上使用 InnoDB 表。

---

更多信息如下。

describe char_kills;

cid, int(10) unsigned, NO, PRI, , 
rid_first, int(10) unsigned, NO, PRI, , 
rid_last, int(10) unsigned, NO, PRI, , 
kills, int(11), NO, PRI, , 
offi_rank, smallint(5) unsigned, YES, , , 

select count(*) from char_kills;

146312

select count(*) from char_kills where rid_first <= 110 and rid_last >= 110;

7207

show indexes from char_kills;

char_kills, 0, PRIMARY, 1, kills, A, 160711, , , , BTREE, , 
char_kills, 0, PRIMARY, 2, rid_first, A, 160711, , , , BTREE, , 
char_kills, 0, PRIMARY, 3, rid_last, A, 160711, , , , BTREE, , 
char_kills, 0, PRIMARY, 4, cid, A, 160711, , , , BTREE, , 

一个:

Explain:
1, SIMPLE, char_kills, index, , PRIMARY, 16, , 500, Using where

Profiling:
0.02406750, select * from char_kills where rid_first <= 110 and rid_last >= 110 order by kills desc limit 500

starting, 0.000050
checking permissions, 0.000007
Opening tables, 0.000029
System lock, 0.000008
init, 0.000022
optimizing, 0.000008
statistics, 0.000013
preparing, 0.000012
executing, 0.000003
Sorting result, 0.000006
Sending data, 0.023822
end, 0.000007
query end, 0.000004
closing tables, 0.000015
freeing items, 0.000058
logging slow query, 0.000002
cleaning up, 0.000004

B:

Explain:
1, SIMPLE, char_kills, index, , PRIMARY, 16, , 500, Using where

Profiling:
0.00229975, select * from char_kills where rid_first <= 110 and rid_last >= 110 order by kills limit 500

starting, 0.000049
checking permissions, 0.000007
Opening tables, 0.000027
System lock, 0.000008
init, 0.000019
optimizing, 0.000008
statistics, 0.000012
preparing, 0.000009
executing, 0.000003
Sorting result, 0.000004
Sending data, 0.002091
end, 0.000004
query end, 0.000003
closing tables, 0.000010
freeing items, 0.000042
logging slow query, 0.000002
cleaning up, 0.000003

更多排列

慢:

where rid_first <= 110 and rid_last >= 110 order by kills desc limit 500; -- 0.031s, A, 7k matching rows
where rid_first >= 110 and rid_last <= 110 order by kills      limit 500; -- 0.094s, 449 rows

快速:

where rid_first <= 110 and rid_last >= 110 order by kills      limit 500; -- 0.000s, B
where rid_first <= 110 and rid_last <= 110 order by kills      limit 500; -- 0.000s
where rid_first >= 110 and rid_last >= 110 order by kills desc limit 500; -- 0.000s
where rid_first <= 110 and rid_last <= 110 order by kills desc limit 500; -- 0.000s
where rid_first <= 110 and rid_last <= 110 order by kills desc limit 500; -- 0.000s
where rid_first <= 110                     order by kills desc limit 500; -- 0.000s
where                      rid_last >= 110 order by kills desc limit 500; -- 0.000s

Answer 1

为什么会出现这种情况的答案是相当愚蠢的(并且在问题查询的大量发送数据时间中很明显):

将您的数据库(您可能知道，它存储为一个文件)想象成一个大的矩形 Jade 米田，您正站在它前面。如果你想从位于 Jade 米田 4 个角落的 Jade 米计划中采摘 Jade 米，那么从你面前的 Jade 米植物中采摘 Jade 米显然会更慢(因为你必须走很远)。

在计算机中，(在任何方面)距离都很重要，它以 IO 延迟表示(以及“未命中”以在某处找到预期信息)。

所以缓慢的原因是:a) 问题查询恰好需要从整个文件中读取数据(而不是像快速查询那样顺序读取)

b) 你最近没有对你的数据库进行碎片整理

c) 你的索引定义不当

编辑:为了清楚起见，查询找到所需记录的速度几乎一样快，因此问题不在索引或其定义中(至少不是最大的，尝试制作多列索引以包括您所有的列，只是为了测试)但是当查询说“好的，我知道现在一切都在哪里，让我去获取它以进行显示”<- 这个获取过程就是杀死其中一个的原因。