Indexed Lookups from TIMESTAMP Columns

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
简介: 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节“日期和时间函数”。

目录
打赏
0
3
3
0
107
分享
相关文章
Oracle查询优化-计算字符在字符串中出现的次数
【2月更文挑战第3天】【2月更文挑战第7篇】只接上SQL
226 0
LangChain-04 RAG Retrieval-Augmented Generation 检索增强生成
LangChain-04 RAG Retrieval-Augmented Generation 检索增强生成
115 3
MySQL的 where 1=1会不会影响性能?
在MySQL动态SQL中,使用`where 1=1`主要目的是简化动态条件的拼接,有人担心这可能影响性能。然而,通过官方文档和实际测试发现,由于MySQL的Constant-Folding Optimization(常量折叠优化),`where 1=1`在大多数情况下会被优化掉,对性能影响微乎其微。MyBatis提供了`<where>`标签,能更有效地处理动态SQL,避免多余的`AND`或`OR`。当MySQL版本大于等于5.7时,两者性能差异不大,选择哪种方式可根据团队规范和个人喜好。而在旧版本中,如果使用MyBatis,推荐使用`<where>`标签。
185 0
InnoDB and MyISAM Index Statistics Collection
存储引擎收集表统计信息,供优化器使用,关键数据为平均值组大小,反映相同键前缀值的行数均值。该值影响索引效率,值越大,索引查找行数越多,效用越低。MySQL通过调整`innodb_stats_method`和`myisam_status`系统变量控制统计方法,涉及NULL值处理,如nulls_equal将所有NULL视为同一值组,可能影响索引使用决策。通过设置变量可优化统计信息收集,提升查询性能。
117 10
WHERE Clause Optimization
本节探讨了WHERE子句的优化方法,虽然示例基于SELECT语句,但也适用于DELETE和UPDATE语句。MySQL自动执行多种优化,例如仅计算一次索引使用的常量表达式、快速检测无效表达式、合并HAVING和WHERE子句、优先读取常量表、寻找最佳连接组合、使用内存中的临时表、选择最佳索引以及在某些情况下仅使用索引树解析查询,从而提升查询效率。
|
9月前
|
【python从入门到精通】-- 第一战:安装python
【python从入门到精通】-- 第一战:安装python
100 0
IntelliJ IDEA 解决控制台不能显示日志
IntelliJ IDEA 解决控制台不能显示日志
1088 0
在Linux中,如何启动、停止、重启一个系统服务?
在Linux中,如何启动、停止、重启一个系统服务?
【Linux】通过 PID 获取服务信息 带你玩转 linux
【Linux】通过 PID 获取服务信息 带你玩转 linux
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等

登录插画

登录以查看您的控制台资源

管理云资源
状态一览
快捷访问