使用具有预处理日期条件的表会提高效率吗？

I inherited a system that keeps track of temperature data related to time. I had asked a previous question about it: (What is the most efficient way to store a collection of temperature values into MYSQL?)

我继承了一个跟踪与时间相关的温度数据的系统。我曾经问过一个关于它的问题:(将一组温度值存储到MYSQL中的最有效方法是什么?)

The system has a separate table which is used to keep track of dates (shown below). It contains several descriptor columns of the current day. I am hesitant of the benefits this kind of structure provides, as it seems to add extra weight to do the same thing a few date functions and math can do.

系统有一个单独的表,用于跟踪日期(如下所示)。它包含当天的几个描述符列。我对这种结构所带来的好处犹豫不决,因为它似乎增加了额外的重量来做同样的事情,一些日期功能和数学可以做。

I was told by the creator of the system that it is better to select a range of data by using the DATE_ID with operators instead of a date function.

系统的创建者告诉我,最好通过将DATE_ID与运算符而不是日期函数一起使用来选择一系列数据。

For example: Let's say you want to collect all temperature information from June 1st, 2012 till the end of 2012, you could do the following.

例如:假设您要从2012年6月1日到2012年底收集所有温度信息,您可以执行以下操作。

1) Get the date ID that corresponds to June 1st, 2012. Lets say the id was 23000

1)获取与2012年6月1日对应的日期ID。假设id为23000

2) Get the date ID that corresponds to the end of the year by using something like:

2)使用以下内容获取与年末相对应的日期ID:

SELECT DATE_ID FROM DATE_REPRESENTATION WHERE DATE_ID >= 23000 AND END_YEAR_FLAG = 1 AND LIMIT 1;

Lets say that one was 23213

可以说一个是23213

3) Now we would have 2 DATE_IDs, which we could just use like so:

3)现在我们将有2个DATE_ID,我们可以这样使用:

SELECT * FROM temperature_readings WHERE DATE_ID BETWEEN 23000 AND 23213;

I feel that it might be better to properly index the 'temperature_readings' table and use date functions. For example:

我觉得正确索引'temperature_readings'表并使用日期函数可能更好。例如:

SELECT ...... actual_date BETWEEN DATE('2012-06-01') AND LAST_DAY(DATE_ADD(DATE('2012-06-01'), INTERVAL (12 - MONTH(DATE('2012-06-01'))) MONTH))

Is there a better solution than what is currently in use in terms of improving the overall performance? In the previous question, I mention that the system uses the data to produce graphs and alerts based on the data selected by date ranges (daily,weekly, monthly, yearly, or a range that a user can specify).

在提高整体性能方面,是否有比目前使用的解决方案更好的解决方案?在上一个问题中,我提到系统使用数据根据日期范围(每日,每周,每月,每年或用户可指定的范围)选择的数据生成图表和警报。

Current table:

 CREATE TABLE `DATE_REPRESENTATION` (
  `DATE_ID` int(10) NOT NULL,
  `DAY_DATE` timestamp NULL DEFAULT NULL,
  `DATE_DESC_LONG` varchar(18) DEFAULT NULL,
  `MB_DATE_M_D_YYYY` varchar(18) DEFAULT NULL,
  `WEEKDAY` varchar(9) DEFAULT NULL,
  `WEEKDAY_ABBREV` char(4) DEFAULT NULL,
  `WEEKDAY_NUM` decimal(1,0) DEFAULT NULL,
  `WEEK` char(13) DEFAULT NULL,
  `WEEK_NUM` decimal(4,0) DEFAULT NULL,
  `WEEK_NUM_ABS` decimal(4,0) DEFAULT NULL,
  `MONTH_LONG` varchar(9) DEFAULT NULL,
  `MONTH_ABBREV` char(3) DEFAULT NULL,
  `MONTH_NUM` decimal(2,0) DEFAULT NULL,
  `MONTH_NUM_ABS` decimal(5,0) DEFAULT NULL,
  `QUARTER` char(1) DEFAULT NULL,
  `QUARTER_NUM` decimal(1,0) DEFAULT NULL,
  `QUARTER_NUM_ABS` decimal(5,0) DEFAULT NULL,
  `YEAR4` decimal(4,0) DEFAULT NULL,
  `BEG_WEEK_FLAG` decimal(1,0) DEFAULT NULL,
  `END_WEEK_FLAG` decimal(1,0) DEFAULT NULL,
  `BEG_MONTH_FLAG` decimal(1,0) DEFAULT NULL,
  `END_MONTH_FLAG` decimal(1,0) DEFAULT NULL,
  `BEG_QUARTER_FLAG` decimal(1,0) DEFAULT NULL,
  `END_QUARTER_FLAG` decimal(1,0) DEFAULT NULL,
  `BEG_YEAR_FLAG` decimal(1,0) DEFAULT NULL,
  `END_YEAR_FLAG` decimal(1,0) DEFAULT NULL,
  PRIMARY KEY (`DATE_ID`),
  UNIQUE KEY `DATEID_PK` (`DATE_ID`),
  KEY `timeStampky` (`DAY_DATE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1 个解决方案

#1

A DATE should be stored internally as just a number, so the only thing I can imagine is that the old person used to store dates as CHAR and suffered for it :)

DATE应该在内部存储为一个数字,所以我唯一可以想象的是,旧的人习惯将日期存储为CHAR并为此而受苦:)

When MySQL calculates the BETWEEN values, it will do that once, so there will be little math to be done. Add in the standard optimizations (preparing, parameterizing, indexing, etc), and you should be fine.

当MySQL计算BETWEEN值时,它会执行一次,所以几乎没有数学要做。添加标准优化(准备,参数化,索引等),你应该没问题。

The formulas might be a little illegible. Maybe you could wrap them in a stored procedure, so you could call GET_LAST_DAY_OF_QUARTER(date) instead of putting all the date math in the SELECT.

公式可能有点难以理解。也许你可以将它们包装在一个存储过程中,这样你就可以调用GET_LAST_DAY_OF_QUARTER(date)而不是将所有日期数学放在SELECT中。

#1