Documentation Home
MySQL 5.7 参考手册
MySQL 8.0 参考手册  / 第8章优化  / 8.3 优化和索引  /  8.3.11 从 TIMESTAMP 列进行索引查找

8.3.11 从 TIMESTAMP 列进行索引查找

时间值作为 UTC 值存储在 TIMESTAMP列中，插入列和从 TIMESTAMP列检索的值在会话时区和 UTC 之间转换。（这与 CONVERT_TZ()函数执行的转换类型相同。如果会话时区为 UTC，则实际上没有时区转换。）

由于夏令时 (DST) 等本地时区更改的约定，UTC 和非 UTC 时区之间的转换在两个方向上并不是一对一的。不同的 UTC 值在另一个时区可能不同。以下示例显示了在非 UTC 时区中变得相同的不同 UTC 值： 

mysql> CREATE TABLE tstable (ts TIMESTAMP);
mysql> SET time_zone = 'UTC'; -- insert UTC values
mysql> INSERT INTO tstable VALUES
       ('2018-10-28 00:30:00'),
       ('2018-10-28 01:30:00');
mysql> SELECT ts FROM tstable;
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 00:30:00 |
| 2018-10-28 01:30:00 |
+---------------------+
mysql> SET time_zone = 'MET'; -- retrieve non-UTC values
mysql> SELECT ts FROM tstable;
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 02:30:00 |
| 2018-10-28 02:30:00 |
+---------------------+
笔记

要使用诸如'MET'或 'Europe/Amsterdam'之类的命名时区，必须正确设置时区表。有关说明，请参阅 第 5.1.13 节，“MySQL 服务器时区支持”。

您可以看到两个不同的 UTC 值在转换'MET'为时区时是相同的。这种现象可能导致给定 TIMESTAMP列查询的不同结果，具体取决于优化器是否使用索引来执行查询。

假设查询从前面显示的表中选择值，使用WHERE子句在 ts列中搜索单个特定值，例如用户提供的时间戳文字： 

SELECT ts FROM tstable
WHERE ts = 'literal';

进一步假设查询在这些条件下执行：

  • 会话时区不是 UTC 并且有 DST 偏移。例如： 

    SET time_zone = 'MET';

  • Unique UTC values stored in the TIMESTAMP column are not unique in the session time zone due to DST shifts. (The example shown earlier illustrates how this can occur.)

  • The query specifies a search value that is within the hour of entry into DST in the session time zone.

Under those conditions, the comparison in the WHERE clause occurs in different ways for nonindexed and indexed lookups and leads to different results:

  • If there is no index or the optimizer cannot use it, comparisons occur in the session time zone. The optimizer performs a table scan in which it retrieves each ts column value, converts it from UTC to the session time zone, and compares it to the search value (also interpreted in the session time zone): 

    mysql> SELECT ts FROM tstable
       WHERE ts = '2018-10-28 02:30:00';
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 02:30:00 |
| 2018-10-28 02:30:00 |
+---------------------+

    Because the stored ts values are converted to the session time zone, it is possible for the query to return two timestamp values that are distinct as UTC values but equal in the session time zone: One value that occurs before the DST shift when clocks are changed, and one value that was occurs after the DST shift.

  • If there is a usable index, comparisons occur in UTC. The optimizer performs an index scan, first converting the search value from the session time zone to UTC, then comparing the result to the UTC index entries: 

    mysql> ALTER TABLE tstable ADD INDEX (ts);
mysql> SELECT ts FROM tstable
       WHERE ts = '2018-10-28 02:30:00';
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 02:30:00 |
+---------------------+

    In this case, the (converted) search value is matched only to index entries, and because the index entries for the distinct stored UTC values are also distinct, the search value can match only one of them.

Due to different optimizer operation for nonindexed and indexed lookups, the query produces different results in each case. The result from the nonindexed lookup returns all values that match in the session time zone. The indexed lookup cannot do so:

  • It is performed within the storage engine, which knows only about UTC values.

  • For the two distinct session time zone values that map to the same UTC value, the indexed lookup matches only the corresponding UTC index entry and returns only a single row.

In the preceding discussion, the data set stored in tstable happens to consist of distinct UTC values. In such cases, all index-using queries of the form shown match at most one index entry.

If the index is not UNIQUE, it is possible for the table (and the index) to store multiple instances of a given UTC value. For example, the ts column might contain multiple instances of the UTC value '2018-10-28 00:30:00'. In this case, the index-using query would return each of them (converted to the MET value '2018-10-28 02:30:00' in the result set). It remains true that index-using queries match the converted search value to a single value in the UTC index entries, rather than matching multiple UTC values that convert to the search value in the session time zone.

如果返回ts在会话时区中匹配的所有值很重要，解决方法是通过提示禁止使用索引IGNORE INDEX： 

mysql> SELECT ts FROM tstable
       IGNORE INDEX (ts)
       WHERE ts = '2018-10-28 02:30:00';
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 02:30:00 |
| 2018-10-28 02:30:00 |
+---------------------+

在其他情况下，同样缺乏双向时区转换的一对一映射，例如使用 FROM_UNIXTIME()和 UNIX_TIMESTAMP()函数执行的转换。请参阅 第 12.7 节，“日期和时间函数”。