8.3.11 TIMESTAMP 列中的索引查询

时间值作为 UTC 值存储在TIMESTAMP列中，并且在会话时区和 UTC 之间转换插入和从TIMESTAMP列中检索的值。 (这与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 |
+---------------------+

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

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

SELECT ts FROM tstable
WHERE ts = 'literal';

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

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

SET time_zone = 'MET';

• 由于 DST 偏移，在TIMESTAMP列中存储的唯一 UTC 值在会话时区中不是唯一的。 (前面显示的示例说明了这种情况的发生.)

• 该查询指定了在会话时区中 ImportingDST 小时内的搜索值。

在这种情况下，对于未构建索引和构建索引的查找，WHERE子句中的比较以不同的方式发生，并导致不同的结果：

• 如果没有索引或优化器无法使用它，则会在会话时区中进行比较。优化器执行 table 扫描，其中检索每个ts列值，将其从 UTC 转换为会话时区，并将其与搜索值(也在会话时区中解释)进行比较：

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 |
+---------------------+

由于已存储的ts值已转换为会话时区，因此查询有可能返回两个时间戳值，这些时间戳值与 UTC 值不同，但在会话时区中相等：更改时钟时，在 DST 移位之前出现的一个值，以及 DST 移位后出现的一个值。

• 如果有可用的索引，则以 UTC 进行比较。优化器执行索引扫描，首先将搜索值从会话时区转换为 UTC，然后将结果与 UTC 索引条目进行比较：

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 |
+---------------------+

在这种情况下，(转换后的)搜索值仅与索引条目匹配，并且由于不同存储的 UTC 值的索引条目也不同，因此搜索值只能匹配其中之一。

由于针对非索引和索引查找的优化器操作不同，因此在每种情况下查询都会产生不同的结果。非索引查找的结果将返回在会话时区中匹配的所有值。索引查找不能这样做：

• 它在仅了解 UTC 值的存储引擎内执行。

• 对于 Map 到相同 UTC 值的两个不同的会话时区值，索引查找仅匹配相应的 UTC 索引条目，并且仅返回单行。

在前面的讨论中，存储在tstable中的数据集恰好由不同的 UTC 值组成。在这种情况下，所示形式的所有使用索引的查询最多匹配一个索引条目。

如果索引不是UNIQUE，则 table(和索引)可以存储给定 UTC 值的多个实例。例如，ts列可能包含多个 UTC 值'2018-10-28 00:30:00'的实例。在这种情况下，使用索引的查询将返回它们中的每一个(在结果集中转换为 MET 值'2018-10-28 02:30:00')。仍然使用索引的查询将转换后的搜索值与 UTC 索引条目中的单个值进行匹配，而不是将在会话时区中转换为搜索值的多个 UTC 值进行匹配。

如果返回在会话时区中匹配的所有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()函数执行的转换，在两个方向上也存在相同的缺少时空转换的双向 Map。参见第 12.6 节“日期和时间函数”。