php - 如何动态过滤掉MySQL中的某些行以对其进行查询？

Question

我有一个 PHP-MySQL 设置。我有一个表 devicevalue 结构是这样的

devId | vals | date          | time
xysz  | 23   | 2020.02.17    | 22.06
abcs  | 44   | 2020.02.31    | 22.07

vals 列保存温度值。登录我的网络应用程序的任何用户只能访问某些设备。

步骤如下

1. 在我的网站上，“用户”选择从和到他想查看数据并提交的日期
2. 然后将这些日期传递给页面“getrecords.php”，其中有很多选择查询(并且很多在循环中)以获取所需格式的过滤数据

问题是这张表有将近 2-3 百万条记录。在每个 where 子句中，我都必须添加 to 和 from 条件。这会导致在整个表中搜索。

我的问题是有什么方法可以让我在第 1 步 获得临时表，该表将只有基于给定两个日期的某些行，然后我在其他页面上的所有查询都将针对该临时表？

Answer 1

编辑:如果您的 date column 是文本字符串，您必须将其转换为 DATE 类型的列或 TIMESTAMP ，否则您将永远从此表中获得良好的性能。 MySQL 服务器中有大量优化代码，可以有效地处理时间/日期数据类型。如果您将日期或时间存储为字符串，就会打败所有优化代码。

然后，在您的 date 上添加索引像这样的专栏。

CREATE INDEX date_from_to ON devicevalue (`date`, devId, vals, `time` );

之所以称为覆盖索引，是因为仅使用它就可以满足整个查询。

然后，在您的查询中使用

   WHERE date >= <<<fromdate>>> 
     AND date <  <<<todate>> + INTERVAL 1 DAY

正确执行此索引可以消除创建临时表的需要。

如果您的查询中包含类似`WHERE devId = <<>> 的内容，则您需要这个索引(或另外)。

CREATE INDEX date_id_from_to ON devicevalue (devId, `date`, vals, `time` );

如果您有机会更改此表格的布局，请结合 date和 time列合并为具有 TIMESTAMP 数据类型的单个列。如果您这样做，我在上面向您展示的 WHERE 子句仍然可以正常工作。一切都会一样快。

SQL 旨在简单快速地解决您的问题。通过良好的数据选择和适当的索引，几百万条记录是一个中等大小的表。