提问人：user22909110 提问时间：11/14/2023 最后编辑：Bill Karwinuser22909110 更新时间：11/14/2023 访问量：40
在时间戳上搜索大型MySQL表很慢
Searching big MySQL table on timestamp is slow
问：
我有以下名为“log_data”的MySQL表 - MyISAM
没有键、索引等。
VarId INT(11)
VarValue float
CurDate datetime(3)
此表的目的是记录数据，VarId 是一个整数，指向提供此实际变量“名称”的不同表。因此，特定log_data表具有所有 processdata，这些数据由 VarId 分隔，并且通常在特定的日期范围 （CurDate） 内。由于每个变量的记录间隔约为 100 毫秒，因此此表的增长速度相当快。当前数据集有 200M 条记录。
示例查询：
SELECT
CurDate AS "time",
VarValue AS 'Motor Power'
FROM log_data
WHERE
CurDate BETWEEN FROM_UNIXTIME(1699511240) AND FROM_UNIXTIME(1699543207) AND VarId = 6
我无法确定加快此类查询速度的最佳方法是什么。当我在 Grafana 中显示这些变量时，单个面板可以有 10 个这样的查询（具有不同的 VarId）。然后，加载单个面板大约需要 20 秒。
优化这些类型查询的最佳方法是什么？
我尝试使用主键“id”，在 CurDate 字段上建立索引，但这没有帮助。它甚至将查询时间增加到 1 分钟以上。此外，从 InnoDB 切换到 MyISAM 也带来了性能上的胜利，但我仍然希望它更快。
答：
3赞
Bill Karwin
11/14/2023
#1
在两列上添加索引：
ALTER TABLE log_data ADD INDEX (VarId, CurDate);
列的顺序很重要，并由查询中的条件决定。请参阅我的答案 MySQL 中多列索引字段的顺序是否重要？
您还可以从将列添加到索引中，使其成为覆盖索引来获得好处。然后，查询可以通过仅读取索引来获取其结果，因此它根本不需要接触表行。VarValue
使用此索引而不是我上面显示的索引。
ALTER TABLE log_data ADD INDEX (VarId, CurDate, VarValue);
使用 EXPLAIN 确认查询正在使用索引。优化器可能会认为不值得使用索引，例如，如果您要搜索的特定值出现在大部分行上。
我不建议使用MyISAM。当然，性能很好，但更重要的是支持对表的并发更新并且不丢失数据。请参阅我对MyISAM与InnoDB的回答。
评论
0赞
user22909110
11/15/2023
哇，我已经尝试了带有 3 个索引的第二个 ALTER 表，在实现这一点后，我在 grafana 中的查询时间降至 400 毫秒，而不是 12-15 秒。我将使用 InnoDB 运行另一个测试，看看速度会发生什么。多谢！
评论