Лучший способ эффективно конвертировать часовой пояс в запросе 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 | +-----------------------------------------------------------+

Однако мне нужно преобразовать часовой пояс каждой записи, чтобы он соответствовал времени в моей стране, и мне нужно сгруппировать информацию по дате, поэтому мне нужно преобразовать сам столбец.

И то и другое

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

Возьми меня около 12 секунд, чтобы закончить запрос, это невыносимо медленно!

(Объяснение совпадает с запросом в верхней части)

---

Я думаю, что это общая проблема, но я не могу найти хороший способ справиться с ней, есть ли у кого-нибудь более эффективный способ сделать это? Спасибо!

Answer 1

Какой тип данных, TIMESTAMP против DATETIME, ты использовал? (Но я буду игнорировать это.)

Не "скрывать" индексированный столбец (created_at) внутри любой функции (CONVERT_TZ()). Это делает так, чтобы 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 гораздо менее критичны для производительности.