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

109 天前
HelloAmadeus  HelloAmadeus
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 计算分组。

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

2107 次点击
所在节点   数据库  数据库
8 条回复
HowardTang
HowardTang
109 天前
来自 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
Jinnrry
109 天前
1 、什么数据库
2 、表结构贴上来
3 、数据量多少啊

这 3 个都不知道,盲猜子查询,group by 性能问题。你先试试子查询耗时,然后再加上 group by 看耗时
yeqizhang
yeqizhang
109 天前
数据量? tt 那个查询查询多慢?
irisdev
109 天前
这个子查询意义何在
ntedshen
109 天前
如果没记错那么 health_level 同样需要索引。。。
以及 date 直接存时间戳然后-86400*7 得了,一堆日期函数感觉是花拳绣腿。。。
ryalu
108 天前
看看这个 https://mp.weixin.qq.com/s/Gr3yk7J1XSe6QCmPmvIjWg ,当中提到"用双重 group by 代替 count(distinct)" 以及一些其他优化方式,具体我没试过,但感觉可能对你有点用。
EthanZC
108 天前
明明可以不用子查询,为啥非得套一层,还有这个 group by tt.date, select 又是 DATE_FORMAT(`date`, "%Y-%m-%d"),这种语法也就 MySQL 能让你这么干,其实非常不建议.若是实在要用,在子查询里,就 DATE_FORMAT(`date`, "%Y-%m-%d") 转换好格式再在外面的查询上面直接用
zizon
108 天前
distinct 可以不用吧?子查询已经保证了明天每个 server 只有一条 health record.

这是一个专为移动设备优化的页面(即为了让你能够在 Google 搜索结果里秒开这个页面),如果你希望参与 V2EX 社区的讨论,你可以继续到 V2EX 上打开本讨论主题的完整版本。

https://www.v2ex.com/t/1086588

V2EX 是创意工作者们的社区,是一个分享自己正在做的有趣事物、交流想法,可以遇见新朋友甚至新机会的地方。

V2EX is a community of developers, designers and creative people.

© 2021 V2EX