mysql - MYSQL查询中高效转换时区的最佳方式

Question

我的表“my_logs”有大约 20,000,000 条记录，我想在几天内找出每个日期有多少条日志。

我想要这样的结果


+------------+--------+
|日期 |计数 |
+------------+--------+
| 2016-07-01 | 1623 |
| 2016-07-02 | 1280 |
| 2016-07-03 | 2032 |
+------------+--------+


下面这个查询只需要几毫秒就可以完成，这很好

SELECT  DATE_FORMAT(created_at, '%Y-%m-%d') as date,
        COUNT(*) as count
    FROM  my_logs
    WHERE  created_at BETWEEN '2016-07-01' AND '2016-07-04'
    GROUP BY  DATE_FORMAT(created_at, '%Y-%m-%d')

查询的解释:


+------------+--------+--------+---------------- ----------+
|选择类型|表 |类型 |可能的键 |
+------------+--------+--------+---------------- ----------+
|简单 |我的日志|索引 | index_my_logs_on_created_at |
+------------+--------+--------+---------------- ----------+



+----------------------------+--------+-------- -+
| key | key 长度 |行 |
+----------------------------+--------+-------- -+
| index_my_logs_on_created_at | 10 | 23458462 |
+----------------------------+--------+-------- -+



+------------------------------------------------ ----------+
|额外 |
+------------------------------------------------ ----------+
|在哪里使用；使用索引；使用临时的；使用文件排序 |
+------------------------------------------------ ----------+


但是，我需要转换每条记录的时区以适应我所在国家/地区的时间，并且我需要按“日期”信息进行分组，因此我需要转换列本身。

两者

SELECT  COUNT(*)
    FROM  my_logs
    WHERE  DATE_ADD(created_at, INTERVAL 8 HOUR) BETWEEN '2016-07-01' AND '2016-07-04'
    GROUP BY  DATE_FORMAT(DATE_ADD(created_at, INTERVAL 8 HOUR), '%Y-%m-%d')

和

SELECT  COUNT(*)
    FROM  my_logs
    WHERE  CONVERT_TZ(created_at, "+00:00", "+08:00") BETWEEN '2016-07-01' AND '2016-07-04'
    GROUP BY  DATE_FORMAT(CONVERT_TZ(created_at, "+00:00", "+08:00"),

'%Y-%m-%d')

查询完成大概需要12s，慢的让人无法忍受!!

(Explain和上面的query一样)

---

我认为这是一个常见问题，但我找不到处理它的好方法，有没有人有更有效的方法来做到这一点？谢谢!

Answer 1

您使用了哪种数据类型，TIMESTAMP 与 DATETIME？ (但是，我会忽略它。)

不要在任何函数 (CONVERT_TZ()) 中“隐藏”索引列 (created_at)。它使得 WHERE 子句不能使用索引而必须扫描表。此修复很简单:

WHERE created_at >= '2016-07-01' - INTERVAL 8 HOUR
  AND created_at  < '2016-07-04' - INTERVAL 8 HOUR

(或使用 CONVERT_TZ)。请注意，我还修复了您从 4 日开始包括午夜的错误。注意:即使 + INTERVAL... 也是一个有效的函数。

SELECT 和 GROUP BY 中的表达式对性能的影响要小得多。