MySQL :: MySQL 8.4 参考手册 :: 10.3.14 从 TIMESTAMP 列进行索引查找

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 10 优化 / 10.3 优化和索引 / 10.3.14 从 TIMESTAMP 列进行索引查找

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

10.3.14 从 TIMESTAMP 列的索引查找10.3.14 Indexed Lookups from TIMESTAMP Columns

时间值存储在 TIMESTAMP 列中作为 UTC 值，并且插入到 TIMESTAMP 列以及从中检索的值会在会话时区和 UTC 之间转换。（这与 CONVERT_TZ() 函数执行的转换类型相同。如果会话时区为 UTC，则实际上没有时区转换。）Temporal values are stored in TIMESTAMP columns as UTC values, and values inserted into and retrieved from TIMESTAMP columns are converted between the session time zone and UTC. (This is the same type of conversion performed by the CONVERT_TZ() function. If the session time zone is UTC, there is effectively no time zone conversion.)

由于本地时区变化（如夏令时 DST）的惯例，UTC 与非 UTC 时区之间的转换在两个方向上并非一一对应。不同的 UTC 值在另一个时区可能并不不同。以下示例显示了在非 UTC 时区中会变得相同的不同 UTC 值：Due to conventions for local time zone changes such as Daylight Saving Time (DST), conversions between UTC and non-UTC time zones are not one-to-one in both directions. UTC values that are distinct may not be distinct in another time zone. The following example shows distinct UTC values that become identical in a non-UTC time zone:

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

Note

要使用诸如 'MET' 或 'Europe/Amsterdam' 这样的命名时区，必须正确设置时区表。有关说明，请参见第 7.1.15 节，“MySQL 服务器时区支持”。To use named time zones such as 'MET' or 'Europe/Amsterdam', the time zone tables must be properly set up. For instructions, see Section 7.1.15, “MySQL Server Time Zone Support”.

当转换为 'MET' 时区时，可以看到两个不同的 UTC 值是相同的。这种现象可能导致对给定 TIMESTAMP 列的查询产生不同结果，这取决于优化器是否使用索引来执行查询。You can see that the two distinct UTC values are the same when converted to the 'MET' time zone. This phenomenon can lead to different results for a given TIMESTAMP column query, depending on whether the optimizer uses an index to execute the query.

假设一个查询使用 WHERE 子句从前面显示的表中选择值，以在 ts 列中搜索单个特定值，例如用户提供的时间戳字面量：Suppose that a query selects values from the table shown earlier using a WHERE clause to search the ts column for a single specific value such as a user-provided timestamp literal:

SELECT ts FROM tstable
WHERE ts = 'literal';

进一步假设查询在以下条件下执行：Suppose further that the query executes under these conditions:

会话时区不是 UTC，并且存在夏令时调整。例如：The session time zone is not UTC and has a DST shift. For example:
```
SET time_zone = 'MET';
```
存储在 TIMESTAMP 列中的唯一 UTC 值在会话时区中并非唯一，这是由于夏令时的变化。（之前的示例展示了这种情况是如何发生的。）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.

在这些条件下，WHERE 子句中的比较在非索引和索引查找中以不同的方式进行，并导致不同的结果：Under those conditions, the comparison in the WHERE clause occurs in different ways for nonindexed and indexed lookups and leads to different results:

如果没有索引或者优化器无法使用索引，则比较会在会话时区中进行。优化器会执行表扫描，检索每个 ts 列值，将其从 UTC 转换为会话时区，然后将其与搜索值（也以会话时区解释）进行比较：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 |
+---------------------+
```
由于存储的 ts 值被转换为会话时区，查询可能会返回两个在 UTC 值上不同但在会话时区中相等的时间戳值：一个值发生在夏令时调整前，另一个值发生在夏令时调整后。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.
如果存在可用的索引，比较将在 UTC 中进行。优化器执行索引扫描，首先将搜索值从会话时区转换为 UTC，然后将结果与 UTC 索引条目进行比较：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 |
+---------------------+
```
在这种情况下，（转换后的）搜索值仅匹配索引条目，并且由于不同存储的 UTC 值的索引条目也是不同的，搜索值只能匹配其中一个。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:

它是在存储引擎内部执行的，而存储引擎仅知道 UTC 值。It is performed within the storage engine, which knows only about UTC values.
对于映射到相同 UTC 值的两个不同会话时区值，索引查找仅匹配相应的 UTC 索引条目，并且只返回单行。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.

在前面的讨论中，存储在 tstable 中的数据集恰好包含不同的 UTC 值。在这种情况下，所有使用索引的查询按示例形式执行时最多匹配一个索引条目。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.

如果索引不是 UNIQUE，则表格（以及索引）可能存储给定 UTC 值的多个实例。例如，ts 列可能包含多个 UTC 值 '2018-10-28 00:30:00' 的实例。在这种情况下，使用索引的查询会返回每个实例（在结果集中转换为 MET 值 '2018-10-28 02:30:00'）。仍然成立的是，使用索引的查询会将转换后的搜索值与 UTC 索引条目中的单个值匹配，而不是匹配会转换为会话时区中搜索值的多个 UTC 值。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 提示来抑制索引的使用：If it is important to return all ts values that match in the session time zone, the workaround is to suppress use of the index with an IGNORE INDEX hint:

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 节，“日期和时间函数”。The same lack of one-to-one mapping for time zone conversions in both directions occurs in other contexts as well, such as conversions performed with the FROM_UNIXTIME() and UNIX_TIMESTAMP() functions. See Section 14.7, “Date and Time Functions”.

首页上一级下一节