FROM_UNIXTIME в условии — зло.
Очень часто мне приходится сталкиваться с оптимизацией кода. Оптимизация SQL запросов очень важная часть работы программиста. Сегодня я расскажу историю ускорения запроса более чем в 100 раз. Причиной замедления работы MySQL — стала функция FROM_UNIXTIME. Но обо всем по порядку.
2 года назад мною был написан скрипт по сбору и отображению статистики (с одной онлайновой игрушки). Каждый день скачивалась страница с профилем игрока, парсилась регулярками и заносилась в БД. Исходя из этой информации можно было выводить различную статистику. Особой популярностью пользовалась недельная статистика развития (на сколько игрок прокачался).
Это была присказка. Данные накапливаются. Таблицы растут. Некоторые достигли 100000 записей и запросы стали выполняться 0,5-2 секунды. За кэшированием оно не заметно, но в логах проскакивали медленные запросы. И я решил оптимизировать.
Делаем EXPLAIN:
EXPLAIN SELECT t.pl_id, t.pl_name, t.lvl, t.exp-t1.exp as exp, round(t.r1+t.r2+t.r3+t.r4+t.r5+t.r6+t.r7+t.r8 -t1.r1-t1.r2-t1.r3-t1.r4-t1.r5-t1.r6-t1.r7-t1.r8,2) um, round(t.g1-t1.g1,2) rg1, t.g2-t1.g2 rg2, t.g3-t1.g3 rg3, t.g4-t1.g4 rg4, t.g5-t1.g5 rg5, round(t.g6-t1.g6,2) rg6, t.g7-t1.g7 rg7, t.g8-t1.g8 rg8, t.g9-t1.g9 rg9, round(t.gt-t1.gt,1) rgt FROM s_302clan_old t LEFT JOIN s_302clan_old t1 ON (t.pl_id = t1.pl_id) WHERE (FROM_UNIXTIME( t.lastupd, '%Y%m%d' ) = '20110314') AND (FROM_UNIXTIME( t1.lastupd, '%Y%m%d' ) = '20110307') ORDER BY exp DESC LIMIT 250; +----+-------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------+ | 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 95377 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 95377 | Using where | +----+-------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------+ #Запрос занял: 180 rows in set (1.02 sec). |
Сказать что я был удивлен — ничего не сказать. Полный скан таблицы — это зло.
Сразу же нашелся источник проблемы:
WHERE (FROM_UNIXTIME( t.lastupd, '%Y%m%d' ) = '20110314') |
Всё дело в том, что сначала MySQL для каждой строки применяет функцию FROM_UNIXTIME, а потом сравнивает. Что бы этого избежать нужно при запросе сравнивать содержимое ячейки lastupd, без применения к нему функций. К примеру так:
WHERE t.lastupd BETWEEN UNIX_TIMESTAMP('2011-03-14 00:00:00') AND UNIX_TIMESTAMP('2011-03-14 23:59:59') |
По сути это тот же самый запрос. Мы находим метку UNIX_TIMESTAMP на границе дня и ищем данные, которые попадут во внутрь диапазона.
Делаем еще один EXPLAIN:
EXPLAIN SELECT t.pl_id, t.pl_name, t.lvl, t.exp-t1.exp as exp, round(t.r1+t.r2+t.r3+t.r4+t.r5+t.r6+t.r7+t.r8 -t1.r1-t1.r2-t1.r3-t1.r4-t1.r5-t1.r6-t1.r7-t1.r8,2) um, round(t.g1-t1.g1,2) rg1, t.g2-t1.g2 rg2, t.g3-t1.g3 rg3, t.g4-t1.g4 rg4, t.g5-t1.g5 rg5, round(t.g6-t1.g6,2) rg6, t.g7-t1.g7 rg7, t.g8-t1.g8 rg8, t.g9-t1.g9 rg9, round(t.gt-t1.gt,1) rgt FROM s_302clan_old t LEFT JOIN s_302clan_old t1 ON (t.pl_id = t1.pl_id) WHERE t.lastupd BETWEEN UNIX_TIMESTAMP('2011-03-14 00:00:00') AND UNIX_TIMESTAMP('2011-03-14 23:59:59') AND t1.lastupd BETWEEN UNIX_TIMESTAMP('2011-03-07 00:00:00') AND UNIX_TIMESTAMP('2011-03-07 23:59:59') ORDER BY exp DESC LIMIT 250; +----+-------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------+ | 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 95377 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 95377 | Using where | +----+-------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------+ #Сам запрос: 180 rows in set (0.42 sec) |
Уже лучше. Но от полного скана таблицы нужно избавляться.
Добавим индекс к полю lastupd.
ALTER TABLE `s_302clan_old` ADD INDEX ( `lastupd` ); |
Проверяем.
#1. Старый запрос с FROM_UNIXTIME( t.lastupd, '%Y%m%d' )... +----+-------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------+ | 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 95377 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 95377 | Using where | +----+-------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------+ #180 rows in set (0.28 sec) #2. Новый запрос с t.lastupd BETWEEN UNIX_TIMESTAMP... +----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+ | 1 | SIMPLE | t | range | lastupd | lastupd | 5 | NULL | 378 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | t1 | range | lastupd | lastupd | 5 | NULL | 379 | Using where | +----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+ #180 rows in set (0.0163 sec) |
Что и требовалось сделать. Итоговое ускорение — более чем в 100 раз.
Но и это не предел. В первой строчке есть запись «Using temporary; Using filesort», что означает — «Использование временной таблицы; Использование сортировки». За это говорим спасибо: ORDER BY exp DESC
В силу специфики приложения — сортировка не нужна. При выводе данных на страницу пользователю используется JS скрипт, который автоматически сортирует данные. Поэтому от этой строчки можно избавится. Окончательный вид запроса:
EXPLAIN SELECT t.pl_id, t.pl_name, t.lvl, t.exp-t1.exp as exp, round(t.r1+t.r2+t.r3+t.r4+t.r5+t.r6+t.r7+t.r8 -t1.r1-t1.r2-t1.r3-t1.r4-t1.r5-t1.r6-t1.r7-t1.r8,2) um, round(t.g1-t1.g1,2) rg1, t.g2-t1.g2 rg2, t.g3-t1.g3 rg3, t.g4-t1.g4 rg4, t.g5-t1.g5 rg5, round(t.g6-t1.g6,2) rg6, t.g7-t1.g7 rg7, t.g8-t1.g8 rg8, t.g9-t1.g9 rg9, round(t.gt-t1.gt,1) rgt FROM s_302clan_old t LEFT JOIN s_302clan_old t1 ON (t.pl_id = t1.pl_id) WHERE t.lastupd BETWEEN UNIX_TIMESTAMP('2011-03-14 00:00:00') AND UNIX_TIMESTAMP('2011-03-14 23:59:59') AND t1.lastupd BETWEEN UNIX_TIMESTAMP('2011-03-07 00:00:00') AND UNIX_TIMESTAMP('2011-03-07 23:59:59') LIMIT 250; +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | t | range | lastupd | lastupd | 5 | NULL | 378 | Using where | | 1 | SIMPLE | t1 | range | lastupd | lastupd | 5 | NULL | 379 | Using where | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ #180 rows in set (0.0133 sec) |
Выводы:
1. Избегайте в условиях конструкций типа: WHERE function(field) = value. Такие запросы почти всегда вызывают fullscan таблицы. Куда лучше переписать запрос WHERE field = function(value);
2. Если часто используете поле при поиске/объединении — делайте по нему индекс;
PS. Нет предела совершенству. Можно было бы избавиться от timestamp, заменив его на дату и добавить составной первичный ключ pl_id + lastupd. Но меня устраивает всё как есть, к тому же уникальность данных в течении суток контролируется самим сборщиком данных.
Вывод в статье написан правильно: функции в условии — это зло. Индексы — это очень нужно.
Но вот название статьи («FROM_UNIXTIME в условии — зло.») и мысль в начале («Причиной замедления работы MySQL — стала функция FROM_UNIXTIME») — верны только в этом конкретном случает. Не хорошо.
Указанная в статье оптимизация существенно помогла: время выполнения скрипта сократилось до 7 секунд, ранее уходило более 30. Однако, выбор периода стал странным — выбирает не все записи, а иногда записи не из указанного периода. FROM_UNIXTIME таких осечек не давала.
Если ваш скрипт делает запросы постоянно (например при заходе пользователя на web-страницу), то 7 секунд — это очень долго. Если же скрипт запускается редко (например раз в сутки для создании сводного отчета) — то это допустимо. Хотя в любом случае желательно скрипт оптимизировать дальше.
Что касается расхождении в результатах двух запросов — ищите ошибку. Приведенные мною примеры дают идентичные результаты. Возможно при оптимизации вы неверно заменили условие.