时间戳

首页 > 解决方案 > 优化mysql上大量数据的查询

mysql - 优化mysql上大量数据的查询

问题描述

我的表中有超过 1000 万条数据，需要提取它才能显示在报告中。数据的来源是从 csv 中提取的，它们都是文本格式。这是从我的桌子上看的样子：

在此处输入图像描述

我尝试仅以 1000 的限制进行查询，但它会快速显示，但是如果我要使用日期过滤器来获取 1 天的数据，则大约需要 25-30 秒：

SELECT STR_TO_DATE(SUBSTRING_INDEX(time, '_', 1), '%m/%d/%Y') FROM myTable
WHERE STR_TO_DATE(SUBSTRING_INDEX(time, '_', 1), '%m/%d/%Y') BETWEEN DATE('2019-9-3') AND DATE('2019-9-3');

我已经尝试在用于过滤器的时间列上创建一个索引，但仍然得到相同的结果：

在此处输入图像描述

有什么建议/意见如何提高提取数据的速度。TIA

标签： mysqlindexing

解决方案

您将日期/时间戳存储为文本，这将迫使您做次优STR_TO_DATE的事情，比如到处打电话。我建议添加一个新的真正的datetime列，然后索引该列：

ALTER TABLE myTable ADD COLUMN time_dt DATETIME;

然后，使用以下方法填充它STR_TO_DATE：

UPDATE myTable
SET time_dt = STR_TO_DATE(time, '%m/%d/%Y_%H:%i:%s.%f');

然后，在 上添加索引time_dt：

CREATE INDEX idx ON myTable (time_dt);

最后，重写您的查询，使该WHERE子句是 sargable（即它可以使用上述索引）：

SELECT DATE(time_dt)
FROM myTable
WHERE time_dt >= '2019-09-03' AND time_dt < '2019-09-04';

旁注：您需要%H在格式掩码中使用STR_TO_DATE，因为您的时间是 24 小时制模式。

推荐阅读