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

8.3.14 从 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.15 节,“MySQL 服务器时区支持”

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

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

SELECT ts FROM tstable
WHERE ts = 'literal';

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

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

    SET time_zone = 'MET';
  • TIMESTAMP由于 DST 偏移, 存储在列中的唯一 UTC 值在 会话时区中不是唯一的。(前面显示的示例说明了这是如何发生的。)

  • 该查询指定一个搜索值,该值在会话时区进入 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 转换之后出现的值。

  • 如果有可用索引,则比较以 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'。在这种情况下,使用索引的查询将返回它们中的每一个(转换为'2018-10-28 02:30:00'结果集中的 MET 值)。确实,使用索引的查询将转换后的搜索值与 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()函数执行的转换。请参阅 第 12.7 节,“日期和时间函数”