利用mysql上的大量数据优化查询

Question

我从我的表中有1,000多万个数据，为了在报告中显示，我需要把它拉出来。从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');

我已经尝试在time列上创建一个索引，用于筛选，但仍然得到了相同的结果：

​

​

是否有任何建议/评论，我如何提高提取数据的速度。提亚

Answer 1

当将函数应用于作为搜索一部分的列时，即使为该列定义索引，它也不能使用索引。

您还应该为该列使用适当的DATE或DATETIME数据类型，这将要求日期以YYYY DD格式存储，而不是以MM/DD/YYYY格式存储字符串列。

如果正确存储日期，则可以这样做：

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

这将利用索引。

Answer 2

您正在将日期/时间戳存储为文本，这将迫使您做一些不太理想的事情，比如到处调用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小时时钟模式。