Как оптимизировать вот такой mysql запрос

Question

[Пума Тайланд]

В таблице миллионы строк, запрос выполняется больше 3 секунд

SELECT COUNT(DISTINCT(ipaddress)) as uniquenotcloaked FROM accounts a WHERE date_visited BETWEEN STR_TO_DATE('2013-05-01 00:00:01', '%Y-%m-%d %H:%i:%s') AND STR_TO_DATE('2013-07-18 23:59:59', '%Y-%m-%d %H:%i:%s') AND a.server_id IN (2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,28,30,32) AND cloaked = 0;

+------------------+
| uniquenotcloaked |
+------------------+
| 574720 |
+------------------+
1 row in set (3.98 sec)

Есть много индексов в том числе и составной по date_visited + server_id + cloacked

EXPLAIN SELECT COUNT( DISTINCT ( ipaddress ) ) AS uniquenotcloaked FROM accounts a IGNORE INDEX ( Index_cloaked ) WHERE date_visited BETWEEN STR_TO_DATE( '2013-05-01 00:00:01', '%Y-%m-%d %H:%i:%s' ) AND STR_TO_DATE( '2013-07-18 23:59:59', '%Y-%m-%d %H:%i:%s' ) AND a.server_id IN ( 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 28, 30, 32 ) AND cloaked =0;
+----+-------------+-------+------+----------------------------------------------------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+----------------------------------------------------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | a | ALL | Index_date_visited,Index_8,date_server,date_server_cloak | NULL | NULL | NULL | 3252392 | Using where |
+----+-------------+-------+------+----------------------------------------------------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

Если убрать DISTINCT то запрос работает точно также, если считать count( account_id ) то запрос работает точно так же, то есть с distinct и ipaddress не использование индексов не связано.

Настройки mysql сервер выкручены по максимуму.

Данные из slow log

# Query_time: 5.030999 Lock_time: 0.000135 Rows_sent: 1 Rows_examined: 3269287 Rows_affected: 0 Rows_read: 3269287
# Bytes_sent: 76
SET timestamp=1374212229;
SELECT
COUNT(DISTINCT(ipaddress)) as uniquenotcloaked
FROM accounts a
WHERE date_visited BETWEEN STR_TO_DATE('2013-05-01 00:00:01', '%Y-%m-%d %H:%i:%s') AND STR_TO_DATE('2013-07-18 23:59:59', '%Y-%m-%d %H:%i:%s') AND a.server_id IN (2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,28,30,32) AND cloaked = 0;

Answer 1

[Melkij]

> составной по date_visited + server_id + cloacked
Именно в таком порядке?
Постройте с cloacked на первом месте (cloacked + date_visited и cloacked + server_id — посмотреть, что планировщику понравится больше), потому что поле константно. In, Between — это range запросы, части индекса после range mysql использовать не может.

> STR_TO_DATE( '2013-05-01 00:00:01', '%Y-%m-%d %H:%i:%s' )
Бесполезен же, mysql сам приведёт строку к дате. К теме не относится — просто запрос станет резко компактнее.

Comments

[Пума Тайланд]

1)Именно в таком.
2)Попробую
3)Без приведения как то не работали индексы по date_visited

[Пума Тайланд]

Понасоздавал индексов разных
explain SELECT
-> COUNT(DISTINCT(ipaddress)) as uniquenotcloaked
-> FROM accounts a
-> IGNORE INDEX ( Index_cloaked )
-> WHERE date_visited BETWEEN STR_TO_DATE('2013-05-01 00:00:01', '%Y-%m-%d %H:%i:%s') AND STR_TO_DATE('2013-07-18 23:59:59', '%Y-%m-%d %H:%i:%s') AND a.server_id IN (2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,28,30,32) AND cloaked = 0;
+----+-------------+-------+------+---------------------------------------------------------------------------------------------+------------+---------+-------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------------------------------------------------------------------------------------+------------+---------+-------+---------+-------------+
| 1 | SIMPLE | a | ref | Index_date_visited,Index_8,date_server,date_server_cloak,cloak_date,cloak_server,date_cloak | cloak_date | 5 | const | 1683931 | Using where |
+----+-------------+-------+------+---------------------------------------------------------------------------------------------+------------+---------+-------+---------+-------------+
1 row in set (0.00 sec)

Второй индекс который ему нравится

explain SELECT
-> COUNT(DISTINCT(ipaddress)) as uniquenotcloaked
-> FROM accounts a
-> IGNORE INDEX ( cloak_date )
-> WHERE date_visited BETWEEN STR_TO_DATE('2013-05-01 00:00:01', '%Y-%m-%d %H:%i:%s') AND STR_TO_DATE('2013-07-18 23:59:59', '%Y-%m-%d %H:%i:%s') AND a.server_id IN (2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,28,30,32) AND cloaked = 0;
+----+-------------+-------+------+------------------------------------------------------------------------------------------------+---------------+---------+-------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+------------------------------------------------------------------------------------------------+---------------+---------+-------+---------+-------------+
| 1 | SIMPLE | a | ref | Index_date_visited,Index_cloaked,Index_8,date_server,date_server_cloak,cloak_server,date_cloak | Index_cloaked | 5 | const | 1606951 | Using where |
+----+-------------+-------+------+------------------------------------------------------------------------------------------------+---------------+---------+-------+---------+-------------+
1 row in set (0.00 sec)

При это я так понимаю в обоих случаях индекс не используется, так как обычно в конце пишется using where; using index;

[Melkij]

> При это я так понимаю в обоих случаях индекс не используется, так как обычно в конце пишется using where; using index;
Неправильно понимаете. Используется ли индекс и какой — колонка key.
dev.mysql.com/doc/refman/5.1/en/explain-output.html
using index — это использован покрывающий индекс, когда в индексе есть всё, чтобы ответить на запрос и читать данные не нужно.

В два раза объём рядов сократился, а что с реальным временем исполнения?
Боюсь, что оптимизациями запросов тут больше ничего не выиграть, полтора миллиона записей пробегать с группировкой… Надо строить таблицы, в которые складывать аггрегированные данные и следить за их своевременным обновлением.

[Пума Тайланд]

Реальное время исполнения осталось таким же.

Answer 2

[Пума Тайланд]

1)Именно в таком.
2)Попробую
3)Без приведения как то не работали индексы по date_visited

Answer 3

[scu]

Попробуйте
— разбивать таблицы на отдельные по месяцам (или неделям или дням — в зависимости от объема и скорости накопления информации в БД) — это значительно сократит скорость выборки.
— IP хранить как 4 части по 3 знака int. + сделать индекс: на каждую часть, на 1+2 часть и 2+3 часть.
— дату хранить и выбирать в timestamp

+ если есть желание, киньте дамб базы в приват (кусок) на поиграться, возможно что-то еще смогу подсказать.

Comments

[Melkij]

> это значительно сократит скорость выборки.
хорошая оговорка ;-)
Партицирование данных — да, попробовать можно.

> IP хранить как 4 части по 3 знака int. + сделать индекс: на каждую часть, на 1+2 часть и 2+3 часть.
И в чём смысл, кроме как раздуть объём базы? ip и есть unsigned int сам по себе.
А индексы по ip здесь использоваться не будут. Чтобы дойти до тяжёлого distinct'а — сперва надо where пройти.

[Пума Тайланд]

1)С партицированием сейчас очень не просто, это сильно усложнит переделку всех запросов.
2)Айпи без разницы как хранить, я там делал count по id скорость работы точно такая же, так что скорее всего дистинкт довольно легкий.
3)Какой выигрыш от timestamp?

[Melkij]

> 3)Какой выигрыш от timestamp?
4 байта против 8 у datetime. Похудеют индексы и база.

[Пума Тайланд]

К сожалению это не так важно, все влезает в память.

Answer 4

[KEKSOV]

Большой IN не айс… попробуйте сделать так, если, конечно, данные в IN всегда одни и те же.
Итак, превращаем AND a.server_id IN (2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,28,30,32) в (порядок важен)
AND a.server_id >1 AND a.server_id < 33 AND ( a.server_id < 23 OR a.server_id IN ( 28, 30, 32 ) )

и еще попробуйте перенести проверку cloaked =0 в начало WHERE

Comments

[Пума Тайланд]

Если полностью убрать условие in запрос выполняется точно так же.
Попробую

[Пума Тайланд]

explain
-> SELECT
-> COUNT(DISTINCT(ipaddress)) as uniquenotcloaked
-> FROM accounts a
-> WHERE cloaked = 0 and date_visited BETWEEN STR_TO_DATE('2013-05-01 00:00:01', '%Y-%m-%d %H:%i:%s') AND STR_TO_DATE('2013-07-18 23:59:59', '%Y-%m-%d %H:%i:%s') AND a.server_id IN (2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,28,30,32);
+----+-------------+-------+------+-----------------------------------------------------------------------------------------------------------+---------------+---------+-------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-----------------------------------------------------------------------------------------------------------+---------------+---------+-------+---------+-------------+
| 1 | SIMPLE | a | ref | Index_date_visited,Index_cloaked,Index_8,date_server,date_server_cloak,cloak_date,cloak_server,date_cloak | Index_cloaked | 5 | const | 1606951 | Using where |
+----+-------------+-------+------+-----------------------------------------------------------------------------------------------------------+---------------+---------+-------+---------+-------------+
1 row in set (0.00 sec)

[Пума Тайланд]

выполняется также медленно
SELECT
-> COUNT(DISTINCT(ipaddress)) as uniquenotcloaked
-> FROM accounts a
-> WHERE cloaked = 0 and date_visited BETWEEN STR_TO_DATE('2013-05-01 00:00:01', '%Y-%m-%d %H:%i:%s') AND STR_TO_DATE('2013-07-18 23:59:59', '%Y-%m-%d %H:%i:%s') AND a.server_id IN (2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,28,30,32);
+------------------+
| uniquenotcloaked |
+------------------+
| 574720 |
+------------------+
1 row in set (3.95 sec)

[KEKSOV]

А если мое условие перенести в начало запроса, до проверки даты?

[Пума Тайланд]

В данном случае in не важен.

Без него все точно также плохо

mysql> SELECT
-> COUNT(DISTINCT(ipaddress)) as uniquenotcloaked
-> FROM accounts a
-> WHERE cloaked = 0 and date_visited BETWEEN STR_TO_DATE('2013-05-01 00:00:01', '%Y-%m-%d %H:%i:%s') AND STR_TO_DATE('2013-07-18 23:59:59', '%Y-%m-%d %H:%i:%s');
+------------------+
| uniquenotcloaked |
+------------------+
| 574720 |
+------------------+
1 row in set (3.92 sec)

mysql> explain SELECT
-> COUNT(DISTINCT(ipaddress)) as uniquenotcloaked
-> FROM accounts a
-> WHERE cloaked = 0 and date_visited BETWEEN STR_TO_DATE('2013-05-01 00:00:01', '%Y-%m-%d %H:%i:%s') AND STR_TO_DATE('2013-07-18 23:59:59', '%Y-%m-%d %H:%i:%s');
+----+-------------+-------+------+---------------------------------------------------------------------------------------------------+---------------+---------+-------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------------------------------------------------------------------------------------------+---------------+---------+-------+---------+-------------+
| 1 | SIMPLE | a | ref | Index_date_visited,Index_cloaked,date_server,date_server_cloak,cloak_date,cloak_server,date_cloak | Index_cloaked | 5 | const | 1606951 | Using where |
+----+-------------+-------+------+---------------------------------------------------------------------------------------------------+---------------+---------+-------+---------+-------------+
1 row in set (0.00 sec)

[KEKSOV]

А… елки… индекс по ipaddress есть?
Может я заблуждаюсь, но ваш запрос можно переписать следующим образом
SELECT COUNT(*) AS uniquenotcloaked
FROM
WHERE
GROUP BY ipaddress

серверу же надо как-то сравнивать ipaddress для посчета одинаковых значений
Попробуйте убрать DISTINCT для теста, как изменится скорость работы?

[Пума Тайланд]

DISTINCT для ipaddress очень легкий, я делал без его с count(id) запрос выполняется так же медленно 3-4 секунды, то есть оверхед дистинкта по айпиадресу совсем маленький на фоне where.

[KEKSOV]

Ну, раз такое дело, то остается попробовать только следующее — добавить новое поле date_visited_timestamp UNSIGNED INT и сделать UPDATE date_visited_timestamp = UNIX_TIMESTAMP(date_visited) построить по этому полю индеск и изменить запрос на date_visited_timestamp BETWEEN UNIX_TIMESTAMP('2013-05-01 00:00:01') AND UNIX_TIMESTAMP('2013-07-18 23:59:59')

Да, и еще один вариант — попробуйте в запросе уйти от BETWEEN, заменив его на два СТРОГИХ (больше, меньше) условия (только даты начала конца немного подправьте)

[Пума Тайланд]

Попробую завтра

[KEKSOV]

Чем завершилась оптимизация, если завершилась?

[Пума Тайланд]

Этот запрос успеха не достиг, частично решил проблему через кеширование.

[KEKSOV]

А дамп можете куда-нибудь выложить? У меня уже чисто спортивный интерес :)

[Пума Тайланд]

Не могу к сожалению, это чертовски секретные данные у меня под них нда.

Answer 5

[Walt Disney]

Избавьтесь от BETWEEN с ним всё всегда плохо, два варианта это сделать:
1) Добавьте колонку INT которую заполните UPDATE accounts SET new_col = YEAR(date_visited_timestamp ) * 1000 + DAYOFYEAR(date_visited_timestamp )
сделайте по ней индекс и дёргйте её через IN()
2) используете гео индекс, он хорошо решает проблему с BETWEEN dev.mysql.com/doc/refman/5.7/en/populating-spatial-columns.html

Другой вариант завидите новую таблицу
server_id | counter | crdate
И или на прошлую таблицу повесьте тригер на инсёрт или добавьте запрос в ваше приложение который будет инкрементить счётчик

Третий вариант, совместить первые два.