newtover的回复列表

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个最大行。

查询的缺点是查询缓存无法缓存其结果。