UPD:2017-03-31,版本
5.7.5
在mysql中,默认情况下只启用了按开关的完整组(因此,禁用了不确定的按查询分组)。此外,他们按实现更新了组,即使使用禁用的交换机,解决方案也可能不再按预期工作。需要检查一下。
当组内的项计数很小时,bill karwin的上述解决方案可以正常工作,但是当组很大时,查询的性能就会变差,因为该解决方案需要大约
n*n/2 + n/2
仅有的
IS NULL
比较。
我在innodb表上做了测试
18684446
行与
1182
组。该表包含功能测试的测试结果,并具有
(test_id, request_id)
作为主键。因此,
test_id
是一个小组,我在寻找最后一个
request_id
对于每一个
试验样品
.
Bill的解决方案已经在我的Dell E4310上运行了几个小时,我不知道它什么时候完成,即使它在覆盖率索引上运行(因此
using index
在解释中)。
我还有两个基于相同想法的解决方案:
-
如果基础索引是btree index(通常是这种情况),则最大的
(group_id, item_value)
pair是每个
group_id
,这是第一个
群标识
如果我们按降序遍历索引;
-
如果我们读取索引包含的值,则按索引的顺序读取这些值;
-
每个索引隐式地包含附加到该索引的主键列(即主键在覆盖率索引中)。在下面的解决方案中,我直接操作主键,在您的情况下,您只需要在结果中添加主键列。
-
在许多情况下,在子查询中按所需的顺序收集所需的行id并将子查询的结果连接到id上要便宜得多。由于子查询结果中的每一行mysql都需要一个基于主键的提取,因此子查询将首先放在连接和行中将按子查询中ID的顺序输出(如果我们为联接省略显式的ORDERBY)
3 ways MySQL uses indexes
是了解一些细节的好文章。
解决方案1
这个速度惊人,在我的18m+行上大约需要0,8秒:
SELECT test_id, MAX(request_id), request_id
FROM testresults
GROUP BY test_id DESC;
如果要将顺序更改为asc,请将其放入子查询中,仅返回id并将其用作子查询以联接到其余列:
SELECT test_id, request_id
FROM (
SELECT test_id, MAX(request_id), request_id
FROM testresults
GROUP BY test_id DESC) as ids
ORDER BY test_id;
这个需要1,2秒的数据。
解决方案2
下面是另一个对我的桌子来说大约需要19秒的解决方案:
SELECT test_id, request_id
FROM testresults, (SELECT @group:=NULL) as init
WHERE IF(IFNULL(@group, -1)=@group:=test_id, 0, 1)
ORDER BY test_id DESC, request_id DESC
它还按降序返回测试。它的速度要慢得多,因为它会执行完整的索引扫描,但这里是为了让您了解如何为每个组输出n个最大行。
查询的缺点是查询缓存无法缓存其结果。
