MariaDB查询性能波动原因及诊断方法咨询

表结构 #

执行desc house.solar;得到表结构：

执行的查询语句 #

select  hour(ts),avg(value)
    from  solar
    where  month(ts)=4
      and  measurement = 'DP1'
      and  year(ts)=2023
    group by  hour(ts);

问题现象 #

该查询执行时长不稳定，有时耗时几乎为0，但多次重复执行（更换年份等参数）后，耗时会达到数秒。在C++/Qt 6.6.2应用和Linux MySQL命令行中均出现此问题，更换参数甚至会超时。

补充执行计划 #

执行explain select hour(ts),avg(value) from house.solar where month(ts)=5 and measurement = 'DP1' and year(ts)=2023 group by hour(ts);得到执行计划：

建表语句 #

CREATE TABLE `solar` (
  `ts` datetime DEFAULT NULL,
  `measurement` varchar(15) DEFAULT NULL,
  `unit` varchar(10) DEFAULT NULL,
  `value` float DEFAULT NULL
)

1. 全表扫描引发性能波动
   执行计划显示查询为ALL类型（全表扫描），未使用任何索引。当目标数据不在内存缓存中时，需要从磁盘读取近300万行数据，耗时骤增；若之前执行过类似查询，数据已被缓存到内存，查询就会极快，这是时长不稳定的核心原因。

2. 字段函数操作导致索引失效
   WHERE子句中对ts使用month()、year()函数，这种直接对字段的函数运算会让MySQL无法利用ts字段的索引（即便后续创建），只能强制走全表扫描。

3. 临时表与文件排序的额外开销
   执行计划的Extra字段显示Using temporary; Using filesort，说明MySQL需要创建临时表存储分组中间结果，还要对分组结果排序。当数据量较大时，临时表可能从内存转至磁盘，进一步拉长耗时。

• 用EXPLAIN分析执行计划：确认查询是否走索引、扫描行数、是否用到临时表/文件排序，这是定位性能问题的核心第一步。
• 监控缓存命中率：执行SHOW STATUS LIKE 'Qcache%';和SHOW STATUS LIKE 'Innodb_buffer_pool%';，查看数据是否在缓存中，缓存命中率低会大幅增加磁盘IO开销。
• 开启慢查询日志：设置slow_query_log=1并配置long_query_time阈值，记录耗时超标的查询，针对性分析优化。
• 监控系统硬件资源：用top、iostat等工具查看服务器CPU、内存、磁盘IO使用率，确认是否因硬件资源瓶颈导致超时。
• 更新表统计信息：执行ANALYZE TABLE solar;，确保MySQL优化器能基于最新的表数据生成最优执行计划。

内容的提问来源于stack exchange，提问作者waiwurrie