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
guangzhouwuyanzu
V2EX  ›  MySQL

MySQL group by 优化

  •  1
     
  •   guangzhouwuyanzu · 2022-01-28 17:41:03 +08:00 · 2944 次点击
    这是一个创建于 791 天前的主题,其中的信息可能已经有所发展或是发生改变。

    目的取一段时间内 uid 对应消耗,然后划分区间

    发现 group by uid返回的数据越多越慢,这个从 SQL 下手有优化空间吗

    MySQL 5.7
    
    select
      elt(interval(total, null, 300), '-INF~300', '300~INF') as section,
      count(*) AS total
    from (
      select
        uid,
        SUM(gold) as total
      from `table_name`
      where `time` > 1640966400 and `time` <= 1642176000 group by `uid`
    ) as `tmp` group by `section`;
    
    -- 执行了 3-4 秒
    
    返回结果:
    -INF~300	46319
    300~INF		15060
    

    EXPLAIN 结果:

    select_type table type possible_keys rows rows
    PRIMARY <derived2> ALL 217073 Using temporary; Using filesort
    DERIVED table_name index time,uid 434146 Using where
    第 1 条附言  ·  2022-01-28 18:39:45 +08:00

    附上表结构

    CREATE TABLE `table_name` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `time` int(11) unsigned NOT NULL DEFAULT '0',
      `uid` bigint(20) unsigned NOT NULL DEFAULT '0',
      `gold` bigint(20) unsigned NOT NULL DEFAULT '0',
      PRIMARY KEY (`id`),
      KEY `time` (`time`),
      KEY `uid` (`uid`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
    
    16 条回复    2022-02-08 17:34:27 +08:00
    enjoychen0318
        1
    enjoychen0318  
       2022-01-28 18:02:33 +08:00
    加个 order by null 试试?
    guangzhouwuyanzu
        2
    guangzhouwuyanzu  
    OP
       2022-01-28 18:08:22 +08:00
    @enjoychen0318 作用不大,还是很慢
    xshell
        3
    xshell  
       2022-01-28 18:13:35 +08:00
    没走索引
    Gota
        4
    Gota  
       2022-01-28 18:27:50 +08:00
    猜测省掉一次 group 会不会好一点? 不过我没数据也不好试, 写出来大概是这样.

    select
    SUM(lt300) as lt300,
    SUM(gt300) as gt300
    from (
    select
    IF(SUM(gold) < 300, 1, 0) as lt300,
    IF(SUM(gold) < 300, 0, 1) as gt300
    from `table_name`
    where `time` > 1640966400 and `time` <= 1642176000
    group by `uid`
    ) as `tmp`
    blakejia
        5
    blakejia  
       2022-01-28 18:30:31 +08:00
    select
    uid,
    SUM(gold) as total
    from `table_name`
    where `time` > 1640966400 and `time` <= 1642176000 group by `uid`

    第一层耗时多少秒?
    sanggao
        6
    sanggao  
       2022-01-28 18:31:48 +08:00
    time 加索引,并且 force use time 这个索引
    guangzhouwuyanzu
        7
    guangzhouwuyanzu  
    OP
       2022-01-28 18:32:35 +08:00
    @blakejia 第一层 3 秒左右

    @sanggao time
    guangzhouwuyanzu
        8
    guangzhouwuyanzu  
    OP
       2022-01-28 18:33:49 +08:00
    @sanggao 刚才不小心点错了,

    time 是有索引,FORCE INDEX(time)后耗时减少 1s
    galileo1214
        9
    galileo1214  
       2022-01-28 18:51:11 +08:00
    开窗?
    blakejia
        10
    blakejia  
       2022-01-28 18:59:14 +08:00
    整表有多少数据量呢?
    23fksd
        11
    23fksd  
       2022-01-28 20:20:16 +08:00
    联合索引+覆盖:CREATE INDEX idx_uid_time_gold ON table_name (uid,`time`,gold);
    WhereverYouGo
        12
    WhereverYouGo  
       2022-01-29 09:45:55 +08:00
    可以,就在这个帖子里学到了 elt()、interval()、force index()
    a222QAQ
        13
    a222QAQ  
       2022-01-29 21:42:01 +08:00 via Android
    @sweetsorrow211 学习+1
    opengps
        14
    opengps  
       2022-01-30 20:43:51 +08:00
    我理解,time 和 uid 应该是个联合索引
    whoisix
        15
    whoisix  
       2022-02-07 15:13:29 +08:00
    mark ,学习+1
    thinkmore
        16
    thinkmore  
       2022-02-08 17:34:27 +08:00
    尝试建立下 index(time,uid ,gold)联合索引,不知道是否可以提供数据供分析
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   2926 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 869ms · UTC 14:09 · PVG 22:09 · LAX 07:09 · JFK 10:09
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.