对 group by 结果集中的 max(field)排序，如何优化？

[johnsona]

表test,300w 数据

字段如下

`key`  varchar index

`value`  int index

key 有重复数据

现在想对 key 分组后根据 max(value)排序

或者叫查询 key 去重后的 value 的前几位？

我的 sql 是

select key,max(value) as m_value from test group by key order by m_value desc limit 10

耗时 3 分钟了要 explain 的结果是 index 索引类型

去掉 order_by m_value之后 耗时 0.3 秒

应该是 max 要重新排序的原因？

[gaohongyuan]

不排序 0.3s 是因为 limit 10 的原因吧？查出来十个就不继续查了。你去掉 limit 10 不排序试试呢

[johnsona]

@gaohongyuan 去掉 limit 300w 数据都到内存了 这查到什么时候

[ccde8259]

select key,max(value) as m_value from test group by key order by m_value desc limit 10
去掉 order by 以后变成
select key,max(value) as m_value from test group by key limit 10
我盲猜这个执行计划就很简单，key index 拽前 10 个 key 出来，拿 id 回表跑一个优先队列。key 区分度高 scan 量就很少。
如果保留 order by 的情况下，limit 10 是对有序表进行限制。你需要所有的 m_value 值，再搞一波排序，才能确定谁是前 10 被返回的。
这个时候的处理就比较需要一点操作。不妨试试这样：首先用(‘key’,’value’)开一根联合索引。用 select id from test where key=key order by value desc limit 1 踩索引拿最大值 id 回来。再拿 id 回表取 id 和 value，order by value 排序并 limit 10 取 id 。最后 id 回表返回。

[512357301]

@johnsona 有个思路，你可以试试：原 SQL 去掉排序、去掉 limit，这样查出来的结果作为子查询，from 它，然后在外层查询里排序，limit 。
我没试过这样的性能怎么样，但是像你原 SQL 那么写，我是理解了半天才看懂，(挺骚操作的)，虽然解释起来很好解释，但就是不知道 mysql 是不是看懂你的意思了，执行了 3 分钟，估计它没正确理解你的意思。
PS：我日常是写 hiveSQL 的，所以也不能保证这个思路绝对没问题