在MYSQL查询中有效转换时区的最好方法

Question

我的表'my_logs'有大约20,000,000条记录，我想知道在几天内每个日期有多少条日志。

我想要一个像

这样的结果

+------------+---------+ | date | count | +------------+---------+ | 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')

查询的解释:

+------------+---------+-------+-----------------------------+ |select_type | table | type | possible_keys | +------------+---------+-------+-----------------------------+ | SIMPLE | my_logs| index | index_my_logs_on_created_at | +------------+---------+-------+-----------------------------+

+-----------------------------+---------+----------+ | key | key_len | rows | +-----------------------------+---------+----------+ | index_my_logs_on_created_at | 10 | 23458462 | +-----------------------------+---------+----------+

+-----------------------------------------------------------+ | Extra | +-----------------------------------------------------------+ | Using where; Using index; Using temporary; Using filesort | +-----------------------------------------------------------+

但是，我需要将每个记录的时区转换为适合我所在国家的时间，并且需要按"Date"信息分组，因此我需要转换列本身。

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 % - % - % d ')

花了我大约12s完成查询，它是难以忍受的慢!!

(Explain与顶部的查询相同)

---

我认为这是一个普遍的问题，但我找不到一个好的方法来处理它，有人有更有效的方法吗?谢谢!

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中的表达式对性能的影响要小得多。