V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
ShutTheFu2kUP
V2EX  ›  MySQL

使用 count(*) 统计后的字段作为 order by 的字段怎么优化

  •  1
     
  •   ShutTheFu2kUP · 2019-10-11 15:58:19 +08:00 · 9257 次点击
    这是一个创建于 1630 天前的主题,其中的信息可能已经有所发展或是发生改变。

    四百万行数据,GROUP BY 后统计,然后 DESC 排序后,还要分页

    LOG( 统计该用户操作的日志表 )

    id 主键
    user_id 用户 ID
    date 创建日期
    

    SQL( date, user_id 这两个字段建立复合索引 )

    SELECT
        user_id,
        count(*) AS count
    FROM
        log
    GROUP BY
        date, user_id
    ORDER BY
        date DESC, user_id DESC
    LIMIT 0, 10
    

    以上 SQL 语句可以走索引,但是这时候如果要 count 字段进行排序,explain 就走全表了,执行了 1 分半,有其他办法优化吗?

    SELECT
        user_id,
        count(*) AS count
    FROM
        log
    GROUP BY
        date, user_id
    ORDER BY
        count DESC, date DESC, user_id DESC
    LIMIT 0, 10
    
    setsunakute
        1
    setsunakute  
       2019-10-11 16:19:12 +08:00
    select `user`, count from (
    SELECT
    `date`,
    user_id,
    count(*) AS count
    FROM
    log
    GROUP BY
    date, user_id
    ) as a
    order by count DESC, `date` DESC, user_id DESC limit 0, 10;
    这样试试?
    ShutTheFu2kUP
        2
    ShutTheFu2kUP  
    OP
       2019-10-11 16:29:13 +08:00
    @setsunakute 貌似还是一个结果,子查询不走索引,我启动强制索引,虽然 explain 的 key 有索引,但是还是 row 还是全表的行数
    ShutTheFu2kUP
        3
    ShutTheFu2kUP  
    OP
       2019-10-11 16:50:08 +08:00
    是我自己傻了...子查询还是走索引的,只是因为子查询里没有 LIMIT,所以行数还是全表的行数...
    reus
        4
    reus  
       2019-10-11 16:55:23 +08:00
    不走全表,是没可能算出结果的,你怎么优化都不能违背基本逻辑。
    可以给 date 加范围条件,如果业务允许的话。
    ShutTheFu2kUP
        5
    ShutTheFu2kUP  
    OP
       2019-10-11 16:58:10 +08:00
    @reus 是的..在不重构表的情况下我也只能想到这个方法了..
    saulshao
        6
    saulshao  
       2019-10-11 17:48:00 +08:00
    这种我之前的办法都是把 count 结果直接写到表里....然后查询这个表...
    zhengwhizz
        7
    zhengwhizz  
       2019-10-11 20:37:55 +08:00 via Android   ❤️ 1
    首先要确认你的业务场景,从语句来看只是要知道用户每天的操作次数,这其实属于数据统计了,你的日志表为原始数据表,每次请求都去拿原始表肯定很慢,所以要建立一个统计表(userid, count, date ),然后在每次用户有操作时 count 加 1 (实时性要求高的情况),或者定时脚本把前一天的统计了放进去。这种设计还可以满足时间段的统计,只需要 sum 下即可。
    Caballarii
        8
    Caballarii  
       2019-10-11 20:40:53 +08:00
    redis
    Leigg
        9
    Leigg  
       2019-10-11 20:46:51 +08:00 via Android
    兄 die,你是要全表排序啊,怎么避免扫全表。需求,表设计,库选择,总有一个是有问题的。
    非要在现有的基础上解决这个问题,楼上的建议是不错的。
    ShutTheFu2kUP
        10
    ShutTheFu2kUP  
    OP
       2019-10-12 11:20:36 +08:00
    @zhengwhizz 嗯,谢谢大佬,我的思路也是如果重构就用字段+1 的方式。定时统计也是一种解决办法,之前没有想到,感谢指导
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   951 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 28ms · UTC 21:22 · PVG 05:22 · LAX 14:22 · JFK 17:22
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.