MySQL :: MySQL 9.0 参考手册 :: 10.3.14 来自 TIMESTAMP 列的索引查找

版本 9.0

8.4 当前
8.0
5.7

8.0 日语

MySQL 9.0 参考手册 / ... / 来自 TIMESTAMP 列的索引查找

10.3.14 来自 TIMESTAMP 列的索引查找

时间值存储在 TIMESTAMP 列中作为 UTC 值，插入到 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' 等命名时区，必须正确设置时区表。有关说明，请参见第 7.1.15 节，“MySQL 服务器时区支持”。

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

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

SELECT ts FROM tstable
WHERE ts = 'literal';

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

会话时区不是 UTC，并且具有 DST 偏移。例如
```
SET time_zone = 'MET';
```
存储在 TIMESTAMP 列中的唯一 UTC 值由于 DST 偏移在会话时区中并不唯一。（前面显示的示例说明了这种情况是如何发生的。）
查询指定了一个在会话时区进入 DST 的小时内搜索的值。

在这些条件下，WHERE 子句中的比较针对非索引查找和索引查找以不同的方式发生，并导致不同的结果

如果没有索引，或者优化器无法使用索引，则比较在会话时区中进行。优化器执行表扫描，其中它检索每个 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 值。
对于映射到相同 UTC 值的两个不同的会话时区值，索引查找仅匹配相应的 UTC 索引条目，并且仅返回一行。

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

如果索引不是 UNIQUE，则表（和索引）可能存储给定 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() 函数执行的转换。请参见第 14.7 节，“日期和时间函数”。