V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
V2EX 提问指南
johnsona
V2EX  ›  问与答

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

  •  1
     
  •   johnsona · 331 天前 · 532 次点击
    这是一个创建于 331 天前的主题,其中的信息可能已经有所发展或是发生改变。

    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 要重新排序的原因?

    4 条回复    2021-07-01 20:39:13 +08:00
    gaohongyuan
        1
    gaohongyuan  
       331 天前 via Android
    不排序 0.3s 是因为 limit 10 的原因吧?查出来十个就不继续查了。你去掉 limit 10 不排序试试呢
    johnsona
        2
    johnsona  
    OP
       331 天前 via iPhone
    @gaohongyuan 去掉 limit 300w 数据都到内存了 这查到什么时候
    ccde8259
        3
    ccde8259  
       331 天前 via iPhone
    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
        4
    512357301  
       330 天前 via Android
    @johnsona 有个思路,你可以试试:原 SQL 去掉排序、去掉 limit,这样查出来的结果作为子查询,from 它,然后在外层查询里排序,limit 。
    我没试过这样的性能怎么样,但是像你原 SQL 那么写,我是理解了半天才看懂,(挺骚操作的),虽然解释起来很好解释,但就是不知道 mysql 是不是看懂你的意思了,执行了 3 分钟,估计它没正确理解你的意思。
    PS:我日常是写 hiveSQL 的,所以也不能保证这个思路绝对没问题
    关于   ·   帮助文档   ·   API   ·   FAQ   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   2696 人在线   最高记录 5497   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 21ms · UTC 09:49 · PVG 17:49 · LAX 02:49 · JFK 05:49
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.