V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
HelloAmadeus
V2EX  ›  数据库

有句 SQL 性能很差,不知道怎么优化,求助下大家?

  •  
  •   HelloAmadeus · 4 天前 · 1433 次点击
    select
      DATE_FORMAT(`date`, "%Y-%m-%d") as time,
      thealth_level as "健康度",
      COUNT(DISTINCT(tserver_name)) as "总数"
    from
      (
        select
          date,
    	  server_name as tserver_name,
          MIN(health_level) as thealth_level
        FROM
          cpu_throttled_health
        WHERE
          date > DATE_FORMAT(CURRENT_DATE() - INTERVAL 7 DAY, "%Y-%m-%d")
        GROUP BY
          date,
    	  server_name
      ) as tt
    group by
      tt.thealth_level,
      tt.date
    order by date,thealth_level
    

    已经给 date,sever_name 增加了 index ,explain 显示能用到 index ,但是速度还是很慢,不知道有什么可以优化的。

    数据是每分钟统计服务一个 health_level 指标,目的是按天统计服务数,计算每个 health_level 下有几个服务,一个服务按当天最小 health_level 计算分组。

    目前能想到的点就是不要实时计算,每天离线统计昨天的数据,存在另外一个表里。不知道有没有可以优化的地方,能实时读。

    8 条回复    2024-11-05 12:16:54 +08:00
    HowardTang
        1
    HowardTang  
       4 天前
    来自 AI 的建议:
    我来帮你分析和优化这个查询。以下是几个优化建议:
    1. 首先可以把子查询提取出来作为一个临时表,减少重复计算:

    WITH daily_min_health AS (
    SELECT
    DATE_FORMAT(`date`, "%Y-%m-%d") as date,
    server_name,
    MIN(health_level) as health_level
    FROM cpu_throttled_health
    WHERE date > DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
    GROUP BY DATE_FORMAT(`date`, "%Y-%m-%d"), server_name
    )
    SELECT
    date,
    health_level as "健康度",
    COUNT(DISTINCT server_name) as "总数"
    FROM daily_min_health
    GROUP BY date, health_level
    ORDER BY date, health_level;


    2.可以创建复合索引来优化:
    CREATE INDEX idx_date_server_health ON cpu_throttled_health(date, server_name, health_level);

    3.如果需要实时查询,可以考虑增加物化视图:
    CREATE MATERIALIZED VIEW mv_daily_health_stats
    REFRESH ON COMMIT AS
    SELECT
    DATE_FORMAT(`date`, "%Y-%m-%d") as date,
    health_level,
    COUNT(DISTINCT server_name) as server_count
    FROM daily_min_health
    GROUP BY DATE_FORMAT(`date`, "%Y-%m-%d"), health_level;

    4.分区表优化:
    ALTER TABLE cpu_throttled_health
    PARTITION BY RANGE (TO_DAYS(date)) (
    PARTITION p_history VALUES LESS THAN (TO_DAYS('2024-01-01')),
    PARTITION p_current VALUES LESS THAN MAXVALUE
    );

    主要优化思路:
    使用复合索引提升查询效率
    考虑物化视图或预计算
    使用分区表减少扫描范围
    如果可以接受,建议改为每天离线计算更新统计表
    Jinnrry
        2
    Jinnrry  
       4 天前
    1 、什么数据库
    2 、表结构贴上来
    3 、数据量多少啊

    这 3 个都不知道,盲猜子查询,group by 性能问题。你先试试子查询耗时,然后再加上 group by 看耗时
    yeqizhang
        3
    yeqizhang  
       4 天前 via Android
    数据量? tt 那个查询查询多慢?
    irisdev
        4
    irisdev  
       4 天前
    这个子查询意义何在
    ntedshen
        5
    ntedshen  
       4 天前
    如果没记错那么 health_level 同样需要索引。。。
    以及 date 直接存时间戳然后-86400*7 得了,一堆日期函数感觉是花拳绣腿。。。
    ryalu
        6
    ryalu  
       3 天前
    看看这个 https://mp.weixin.qq.com/s/Gr3yk7J1XSe6QCmPmvIjWg ,当中提到"用双重 group by 代替 count(distinct)" 以及一些其他优化方式,具体我没试过,但感觉可能对你有点用。
    promisenev
        7
    promisenev  
       3 天前
    明明可以不用子查询,为啥非得套一层,还有这个 group by tt.date, select 又是 DATE_FORMAT(`date`, "%Y-%m-%d"),这种语法也就 MySQL 能让你这么干,其实非常不建议.若是实在要用,在子查询里,就 DATE_FORMAT(`date`, "%Y-%m-%d") 转换好格式再在外面的查询上面直接用
    zizon
        8
    zizon  
       3 天前
    distinct 可以不用吧?子查询已经保证了明天每个 server 只有一条 health record.
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   1004 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 21ms · UTC 19:47 · PVG 03:47 · LAX 11:47 · JFK 14:47
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.