时间戳

首页 > 解决方案 > 如果查询中的日期时间有时区，MariaDB MySQL 查询要长得多，但如果没有添加时区，则非常快 - 为什么？

mysql - 如果查询中的日期时间有时区，MariaDB MySQL 查询要长得多，但如果没有添加时区，则非常快 - 为什么？

问题描述

我在 MariaDB (MySQL) 中有一个表，其start_date_time字段类型为DATETIME(no TZ stored) 。

当我按如下要求时，我得到

MariaDB [db]> explain 
SELECT * 
FROM mytable  
WHERE start_date_time>= '2021-03-04 00:00:00+00:00' 
  AND start_date_time<='2021-03-04 11:08:00+00:00' 
ORDER BY start_date_time;
+------+-------------+----------+-------+---------------+---------+---------+------+---------+-------------+
| id   | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+------+-------------+----------+-------+---------------+---------+---------+------+---------+-------------+
|    1 | SIMPLE      | mytable  | index | PRIMARY       | PRIMARY | 5       | NULL | 4504011 | Using where |
+------+-------------+----------+-------+---------------+---------+---------+------+---------+-------------+

在实践中需要很长时间才能完成。该表包含多年的数据，请求大约需要 30 秒，看起来类似于完整扫描。

但是，如果我只是减少+00:00查询时间（2021-03-04 00:00:00+00:00变成2021-03-04 00:00:00），解释如下所示，查询速度更快：

MariaDB [db]> explain 
SELECT * 
FROM mytable 
WHERE start_date_time>= '2021-03-04 00:00:00' 
  AND start_date_time<='2021-03-04 11:08:00' 
ORDER BY  start_date_time;
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| id   | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
|    1 | SIMPLE      | mytable  | range | PRIMARY       | PRIMARY | 5       | NULL |    1 | Using where |
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.000 sec)

我想了解为什么会发生这种执行时间差异。理想情况下，我想在查询中保留时区，以避免数据库和客户端之间对时区的任何假设、转换或误解。

更新 根据要求，我还提供了下面的 JSON 格式的解释

第一次（慢）查询

 {
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "mytable",
      "access_type": "index",
      "possible_keys": ["PRIMARY"],
      "key": "PRIMARY",
      "key_length": "5",
      "used_key_parts": ["start_date_time"],
      "rows": 11403954,
      "filtered": 100,
      "attached_condition": "mytable.start_date_time >= '2021-03-04 00:00:00+00:00' and mytable.start_date_time <= '2021-03-04 11:08:00+00:00'"
    }
  }
}

第二（快速）查询


| {
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "mytable",
      "access_type": "range",
      "possible_keys": ["PRIMARY"],
      "key": "PRIMARY",
      "key_length": "5",
      "used_key_parts": ["start_date_time"],
      "rows": 1,
      "filtered": 100,
      "attached_condition": "mytable.start_date_time >= '2021-03-04 00:00:00' and mytable.start_date_time <= '2021-03-04 11:08:00'"
    }
  }
} 

SHOW CREATE TABLE:

 CREATE TABLE `mytable` (
  `start_date_time` datetime NOT NULL,
  ... more fields...
  PRIMARY KEY (`start_date_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

标签： mysqldatetimeindexingmariadb

解决方案

由于隐式类型转换，这看起来像是索引滥用的情况。当没有发生类型转换时，它会扫描索引范围 - 找到两个边界值并返回它们之间的所有内容。如果它必须对存储的值应用某种转换函数，那么它会从头到尾扫描整个索引，并根据给定的谓词测试每一行。

我认为您可能会尝试将查询中的常量显式转换为DATETIME以确认此猜想。像：

  WHERE start_date_time >= cast('2021-03-04 00:00:00+00:00' as datetime)
  AND start_date_time <= cast('2021-03-04 11:08:00+00:00' as datetime)

虽然，上面可能会丢弃时区信息。我相信，类似的东西convert_tz('2021-03-04 00:00:00', '+00:00', @@GLOBAL.time_zone)会处理得更好。

推荐阅读