MariaDB查询性能优化求助：30天查询耗时远超预期

环境与查询现象 #

运行环境：Linux 4核8G内存VPS上的MariaDB数据库。

15天数据查询（带时间匹配） #

执行SQL：

SELECT * 
from Sensorwerte 
WHERE (Datum>='2023-10-01 00' 
    and Datum <='2023-10-15 00' 
    AND (Datum LIKE '%15:00.000')) 
    AND SensorNr=3004 
ORDER BY ID DESC;

结果：返回335行，耗时约2.3秒。

30天数据查询（带时间匹配） #

执行SQL：

SELECT * 
from Sensorwerte 
WHERE (Datum>='2023-10-01 00' 
    and Datum <='2023-10-30 00'
    AND (Datum LIKE '%15:00.000')) 
    AND SensorNr=3004 
ORDER BY ID DESC;

结果：返回696行，总耗时84秒（查询29.6秒+网络传输55.3秒），远高于预期的5秒。

去掉时间匹配的15天查询 #

执行SQL：

SELECT * 
from Sensorwerte 
WHERE (Datum>='2023-10-01 00'
       AND Datum <='2023-10-15 00')
       AND SensorNr=3004 
ORDER BY ID DESC;

结果：耗时14秒。

表结构与执行计划 #

表结构 #

CREATE TABLE `Sensorwerte` 
    (`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
     `Datum` datetime(3) NOT NULL,
     `SensorNr` smallint(6) NOT NULL,
     `Wert` decimal(15,5) DEFAULT NULL,
      PRIMARY KEY (`ID`),
      KEY `Zeit` (`Datum`)) ENGINE=InnoDB AUTO_INCREMENT=52208193 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

执行计划 #

select_type=simple,
table=sensorwerte, 
type=index, 
possible_keys=zeit, 
key=primary, 
key_len=4, 
ref=(NULL), 
rows=48615830, 
r_rows=51973599.00, 
filtered=31.65, 
r_filtered=0, 
extra=using where

问题根源 #

1. 索引选择错误：执行计划显示查询使用了主键ID的索引，而非Datum的Zeit索引。主键索引按ID排序，无法高效过滤SensorNr和Datum条件，导致数据库扫描了近5200万行全表数据，这是性能瓶颈的核心原因。
2. 后缀LIKE导致索引失效：Datum LIKE '%15:00.000'是后缀模糊匹配，Datum的Zeit索引按datetime完整前缀排序，后缀匹配无法利用索引，只能全表扫描后逐行过滤，进一步增加计算开销。
3. 耗时非线性增长：30天查询耗时远高于15天的2倍，是因为全表扫描时数据量翻倍后，磁盘IO和内存缓存压力呈非线性上升，加上大结果集的网络传输开销（55秒），导致总耗时剧增。
4. 无复合索引支撑多条件过滤：现有仅单字段索引，无法同时支持SensorNr和Datum的联合过滤，即使去掉LIKE条件，依然需要全表扫描后过滤数据，导致耗时14秒。

优化方案 #

1. 调整SQL语句，避免后缀模糊匹配 #

将Datum LIKE '%15:00.000'替换为可利用索引的时间条件：

方案A：使用时间函数提取时间部分

SELECT * 
from Sensorwerte 
WHERE SensorNr = 3004
  AND Datum >= '2023-10-01 00:00:00.000'
  AND Datum <= '2023-10-30 00:00:00.000'
  AND HOUR(Datum) = 15
  AND MINUTE(Datum) = 0
  AND MICROSECOND(Datum) BETWEEN 0 AND 999 -- 匹配datetime(3)的000毫秒
ORDER BY ID DESC;

方案B：构造精确时间范围（推荐）

直接指定每天15:00的精确区间，避免函数调用：

SELECT * 
from Sensorwerte 
WHERE SensorNr = 3004
  AND Datum BETWEEN '2023-10-01 15:00:00.000' AND '2023-10-30 15:00:00.000'
  AND MINUTE(Datum) = 0
  AND MICROSECOND(Datum) = 0
ORDER BY ID DESC;

2. 创建复合索引（核心优化） #

创建覆盖SensorNr和Datum的复合索引，让数据库直接通过索引过滤条件，无需全表扫描：

CREATE INDEX idx_sensor_datum ON Sensorwerte (SensorNr, Datum);

• 该索引优先按SensorNr过滤，再按Datum排序，完美匹配查询的WHERE条件；同时InnoDB的二级索引会自动包含主键ID，ORDER BY ID DESC也可利用索引排序（若Datum与ID正相关，即插入时间随ID递增）。

3. 服务器配置优化 #

针对8G内存的VPS，调整以下MariaDB配置（修改my.cnf或my.ini）：

• InnoDB缓冲池：设置为内存的70%左右，减少磁盘IO：

  innodb_buffer_pool_size = 5G
  

• 排序与读取缓冲：适度调整，避免内存浪费：

  sort_buffer_size = 2M
  read_buffer_size = 1M
  

• 关闭查询缓存：高并发场景下查询缓存会降低性能，确保配置：

  query_cache_type = 0
  query_cache_size = 0
  

内容的提问来源于stack exchange，提问作者Arnold K.