Indexed Lookups from TIMESTAMP Columns

本文涉及的产品
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
云数据库 RDS SQL Server,基础系列 2核4GB
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
简介: UTC值存储在`TIMESTAMP`列中,在插入和检索时根据会话时区与UTC进行转换。若会话时区采用夏令时,可能导致本地时区的值不是唯一的,影响查询结果。无索引查询在会话时区中进行比较,可能返回多个匹配值;有索引查询则按UTC比较,可能仅返回一个匹配值。为确保返回所有匹配值,可使用`IGNORE INDEX`提示禁用索引。此外,使用`FROM_UNIXTIME()`和`UNIX_TIMESTAMP()`也可能遇到类似问题,请参考第12.7节了解详情。

时间值作为UTC值存储在TIMESTAMP列中,插入TIMESTAMP栏和从TIMESTAMP栏内检索的值在会话时区和UTC之间转换。(这与CONVERT_TZ()函数执行的转换类型相同。如果会话时区为UTC,则实际上没有时区转换。)
由于当地时区更改的惯例,如夏令时(DST),UTC和非UTC时区之间的转换在两个方向上不是一一对应的。不同的UTC值在另一个时区可能不不同。以下示例显示了在非UTC时区中变得相同的不同UTC值:

image.png

image.png

您可以看到,当转换为“MET”时区时,这两个不同的UTC值是相同的。对于给定的TIMESTAMP列查询,这种现象可能会导致不同的结果,具体取决于优化器是否使用索引来执行查询。
假设一个查询使用WHERE子句从前面显示的表中选择值,在ts列中搜索单个特定值,例如用户提供的时间戳文字:

image.png

进一步假设查询在以下条件下执行:
会话时区不是UTC,而是夏令时。例如:

image.png

由于夏令时偏移,存储在TIMESTAMP列中的唯一UTC值在会话时区中不是唯一的。(前面显示的示例说明了这是如何发生的。)
该查询指定了一个搜索值,该值在会话时区中进入夏令时的小时内。
在这些条件下,WHERE子句中的比较对于非索引和索引查找以不同的方式发生,并导致不同的结果:
如果没有索引或优化器无法使用它,则会在会话时区中进行比较。优化器执行表扫描,检索每个ts列值,将其从UTC转换为会话时区,并将其与搜索值(也在会话时区中解释)进行比较:

image.png

由于存储的ts值被转换为会话时区,因此查询可以返回两个与UTC值不同但在会话时区中相等的时间戳值:一个值发生在时钟更改时的夏令时转换之前,另一个值出现在夏令时切换之后。
如果有可用的索引,则以UTC进行比较。优化器执行索引扫描,首先将搜索值从会话时区转换为UTC,然后将结果与UTC索引条目进行比较:

image.png

在这种情况下,(转换后的)搜索值仅与索引条目匹配,并且由于不同存储的UTC值的索引条目也是不同的,因此搜索值只能匹配其中一个。
由于非索引和索引查找的优化器操作不同,查询在每种情况下都会产生不同的结果。非索引查找的结果返回会话时区中匹配的所有值。索引查找不能这样做:
它在存储引擎内执行,存储引擎只知道UTC值。
对于映射到相同UTC值的两个不同会话时区值,索引查找仅匹配相应的UTC索引条目,并仅返回一行。
在前面的讨论中,存储在tstable中的数据集恰好由不同的UTC值组成。在这种情况下,所有使用所示形式的索引查询最多只匹配一个索引条目。
如果索引不是唯一的,则表(和索引)可以存储给定UTC值的多个实例。例如,ts列可能包含UTC值“2018-10-28 00:30:00”的多个实例。在这种情况下,使用查询的索引将返回它们中的每一个(在结果集中转换为MET值'2018-10-28 02:30:00')。使用索引的查询将转换后的搜索值匹配到UTC索引条目中的单个值,而不是匹配转换为会话时区中搜索值的多个UTC值,这仍然是正确的。
如果返回会话时区中匹配的所有ts值很重要,解决方法是使用IGNORE index提示禁止使用索引:

image.png
在其他情况下也会出现双向时区转换缺乏一对一映射的情况,例如使用FROM_UNIXTIME()和UNIX_TIMESTAMP()函数执行的转换。见第12.7节“日期和时间函数”。

相关文章
|
26天前
|
存储 关系型数据库 MySQL
Column Indexes
常见的索引类型通过复制列值至高效数据结构(如B树),实现快速查找。B树助力WHERE子句中=、>、≤、BETWEEN等运算符对应值的检索。每表至少支持16个索引,总长不少于256字节,具体限制依存储引擎而定。字符串列索引可指定前N字符,减少索引文件大小;BLOB或TEXT列索引需指定前缀长度。全文索引用于全文搜索,适用于InnoDB和MyISAM引擎的CHAR、VARCHAR、TEXT列;空间索引则针对空间数据类型,MyISAM和InnoDB采用R树索引。MEMORY引擎默认使用HASH索引,也支持BTREE索引。
|
26天前
|
关系型数据库 MySQL 索引
Multiple-Column Indexes
MySQL 支持创建复合索引(多列索引),最多由 16 列组成,适用于查询中所有或部分列的查找。复合索引如同排序数组,通过连接索引列值创建。正确排列的单个复合索引能加速多种查询。若索引列非最左侧前缀,MySQL 无法使用索引查找。此外,还可引入基于其他列信息“哈希”的列作为替代方案,提高查询效率。
|
10月前
|
关系型数据库 MySQL
order by field
order by field
60 1
order by field
|
数据库
Data truncation: Incorrect date value: ‘2022-11-28T16:00:00.000Z‘ for column ‘start_date‘ at row 1
Data truncation: Incorrect date value: ‘2022-11-28T16:00:00.000Z‘ for column ‘start_date‘ at row 1
249 0
|
关系型数据库 MySQL
报错:Incorrect datetime value: '0000-00-00 00:00:00' for column 'login_time' at row 1
报错:Incorrect datetime value: '0000-00-00 00:00:00' for column 'login_time' at row 1
638 0
|
SQL 关系型数据库 MySQL
Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT
Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT
|
数据库
Data truncation: Out of range value for column ‘estimate_score‘
Data truncation: Out of range value for column ‘estimate_score‘
|
SQL
ORA-01440: column to be modified must be empty to decrease precision or scale
在修改表字段的NUMBER类型的精度或刻度时,你可能会遇到ORA-01440: column to be modified must be empty to decrease precision or scale,下面介绍一下,如何处理这个问题。
1591 0