将日期/日期时间元素分隔成不同的列，以便在MySQL中进行快速查询？

Question

我想将数据存储在MySQL中，并根据当前的情况进行查询。我想知道这样做的最佳做法是什么。

我想存储每天的数据总数，所以查询总数据将是快速的。我想对我的桌子建模如下：

TotalsByCountry
- Year
- Month
- Day
- countryId
- totalNumber

当我查询特定一天和特定国家的总计时，我将根据4列查询表，即年份、月份、日和countryId。

我想知道这是否是一个好做法，或者有更好的方法来做到这一点，比如使用一个列来保存月、日和年的数据，并且只查询两个列，即datetime列和coutryId。

需要你的帮助，选择正确的方式，以表建模。我还想制作另一张表，根据性别来存储总数，所以也要考虑到这一点。

需要频繁地访问数据，可能是实时，因为我想实时显示数据的变化。我将在asp.net中开发web应用程序，并可能使用web套接字创建常量连接，以便实时更新用户上的数据。因此，当数据发生变化时，它将实时地反映在用户的网页上.这就是为什么我需要一个表模型，它将为许多查询做好准备。我将使用缓存几秒钟，所以它想要重压数据库太多。

我希望我提供了足够的信息，如果没有，请评论，我会回复。

Answer 1

在插入性能和磁盘空间方面，有三个单独的列来存储日期(年份/月/日)的每个单独元素将给数据库增加不必要的开销。

您想要做的仅仅是有一个DATETIME列来存储日期和时间，并在(countryId, datetime_col)上设置一个复合索引。

即使您希望根据特定的日期或月份查询所有行，MySQL仍然能够利用日期时间字段上的索引，前提是您以正确的方式编写查询，并且在执行条件检查时确保永远不会将DATETIME列包装在函数中。

下面是如何编写查询，以便它仍然能够使用索引：

-- Get the sum of totalNumber of all rows based on current day
-- where countryId = 1

SELECT SUM(totalNumber) AS totalsum
FROM   tbl
WHERE  countryId = 1 AND
       datetime_col >= CAST(CURDATE() AS DATETIME) AND
       datetime_col <  CAST(CURDATE() + INTERVAL 1 DAY AS DATETIME)

通过对裸DATETIME列进行比较，查询仍然是可靠的(即能够利用索引范围扫描)，MySQL将能够使用索引快速查找行。

另一方面，如果要尝试将DATETIME列包装在函数中以进行比较：

-- Get the sum of totalNumber of all rows based on current day
-- where countryId = 1

SELECT SUM(totalNumber) AS totalsum
FROM   tbl
WHERE  countryId = 1 AND
       DATE(datetime_col) = CURDATE()

...It效率很低，因为包装该列的DATE()函数有效地将查询呈现为非sargable，并且您所设置的包含DATETIME列的任何类型的索引都不会被使用。

您还可以有效地查询当前月份中所有行的总和。

-- Get the sum of totalNumber of all rows based on current month
-- where countryId = 1

SELECT SUM(totalNumber) AS monthsum
FROM   tbl
WHERE  countryId = 1 AND
       datetime_col >= CAST(CONCAT(YEAR(NOW()), '-', MONTH(NOW()), '-01') AS DATETIME) AND
       datetime_col <  CAST(CONCAT(YEAR(NOW()), '-', MONTH(NOW()), '-01') AS DATETIME) + INTERVAL 1 MONTH

在当前的年内

-- Get the sum of totalNumber of all rows based on current year
-- where countryId = 1

SELECT SUM(totalNumber) AS yearsum
FROM   tbl
WHERE  countryId = 1 AND
       datetime_col >= CAST(CONCAT(YEAR(NOW()), '-01-01') AS DATETIME) AND
       datetime_col <  CAST(CONCAT(YEAR(NOW()), '-01-01') AS DATETIME) + INTERVAL 1 YEAR

Answer 2

我的论点是:如果您想快速地进行数据库查找，那么您需要构建良好的使用索引的查询。您的方法需要4个索引(这意味着较慢的插入)，使用单个日期列只需要两个索引，如果需要搜索日期范围，查询的复杂性也会增加。