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

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

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

16 天前

来自 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

16 天前

1 、什么数据库
2 、表结构贴上来
3 、数据量多少啊

这 3 个都不知道，盲猜子查询，group by 性能问题。你先试试子查询耗时，然后再加上 group by 看耗时

yeqizhang

16 天前 via Android

数据量？ tt 那个查询查询多慢？

irisdev

16 天前

这个子查询意义何在

ntedshen

16 天前

如果没记错那么 health_level 同样需要索引。。。
以及 date 直接存时间戳然后-86400*7 得了，一堆日期函数感觉是花拳绣腿。。。

ryalu

16 天前

看看这个 https://mp.weixin.qq.com/s/Gr3yk7J1XSe6QCmPmvIjWg ，当中提到"用双重 group by 代替 count(distinct)" 以及一些其他优化方式，具体我没试过，但感觉可能对你有点用。

promisenev

16 天前

明明可以不用子查询,为啥非得套一层,还有这个 group by tt.date, select 又是 DATE_FORMAT(`date`, "%Y-%m-%d"),这种语法也就 MySQL 能让你这么干,其实非常不建议.若是实在要用,在子查询里,就 DATE_FORMAT(`date`, "%Y-%m-%d") 转换好格式再在外面的查询上面直接用

zizon

16 天前

distinct 可以不用吧?子查询已经保证了明天每个 server 只有一条 health record.