Оптимизация SQL запроса. Что можно сделать?

Question

[Сослан Хлоев]

День добрый!

Есть стандартная таблица CDR asterisk с несколькими дополнительными полями. В таблице около 250000 записей.

Запрос:

SELECT DISTINCT(dstnum) AS t2dstnum, MAX(calldate) OVER (PARTITION BY dstnum) AS t2lastcall FROM cdr WHERE route = "out"

выполняется около 13 минут.

Вывод EXPLAIN:

+------+-------------+-------+------+---------------+-------+---------+-------+--------+----------+----------------------------------------+
| id   | select_type | table | type | possible_keys | key   | key_len | ref   | rows   | filtered | Extra                                  |
+------+-------------+-------+------+---------------+-------+---------+-------+--------+----------+----------------------------------------+
|    1 | SIMPLE      | cdr   | ref  | route,r_and_d | route | 51      | const | 140379 |   100.00 | Using index condition; Using temporary |
+------+-------------+-------+------+---------------+-------+---------+-------+--------+----------+----------------------------------------+

Вывод SHOW PROFILE:

+-------------------------+------------+
| Status                  | Duration   |
+-------------------------+------------+
| Starting                | 0.000170   |
| Checking permissions    | 0.000020   |
| Opening tables          | 0.000050   |
| After opening tables    | 0.000017   |
| System lock             | 0.000015   |
| Table lock              | 0.000019   |
| Init                    | 0.000091   |
| Optimizing              | 0.000036   |
| Statistics              | 0.000214   |
| Preparing               | 0.000046   |
| Creating tmp table      | 0.000082   |
| Executing               | 0.000013   |
| Sending data            | 0.948192   |
| Converting HEAP to Aria | 0.073438   |
| Sending data            | 8.220165   |
| Removing duplicates     | 755.840777 |
| Removing tmp table      | 0.000293   |
| Removing duplicates     | 0.000036   |
| End of update loop      | 0.000011   |
| Query end               | 0.000005   |
| Commit                  | 0.000007   |
| Closing tables          | 0.000006   |
| Unlocking tables        | 0.000004   |
| Closing tables          | 0.000013   |
| Starting cleanup        | 0.000004   |
| Freeing items           | 0.000014   |
| Updating status         | 0.000037   |
| Reset for next command  | 0.000005   |
+-------------------------+------------+

Проблема понятно где, но как оптимизировать не разобрался. Прошу помощи у более опытных и продвинутых писателей SQL. Получится ли вообще оптимизировать?

Comments

[Vitsliputsli]

Сослан Хлоев,
это запрос в MySQL:

SELECT DISTINCT(dstnum) AS t2dstnum, MAX(calldate) OVER (PARTITION BY dstnum) AS t2lastcall FROM cdr WHERE route = "out"

А как запрос выглядит в PostgreSQL?

[Vitsliputsli]

И можете еще дать структуру таблицу, на всякий случай

[Сослан Хлоев]

Vitsliputsli, запрос я кажется не менял.

И можете еще дать структуру таблицу, на всякий случай

Соберу все данные, и отпишусь.

[Vitsliputsli]

Сослан Хлоев, а что должен делать DISTINCT(dstnum), я такой синтаксис не знаю, а найти описание не смог? Но эта конструкция в разы медленней, чем обычный DISTINCT или GROUP BY, а делает вроде как тоже самое.

[Сослан Хлоев]

Vitsliputsli, а что Вас смущает в этом дистинкте? Разве у дистинкта есть какое то другое предназначение кроме вывода уникальных значений?

[Vitsliputsli]

Вот так:

SELECT DISTINCT dstnum AS t2dstnum, MAX(calldate) OVER (PARTITION BY dstnum) AS t2lastcall FROM cdr WHERE route = "out"

не смущает, а вот так:

SELECT DISTINCT(dstnum) AS t2dstnum, MAX(calldate) OVER (PARTITION BY dstnum) AS t2lastcall FROM cdr WHERE route = "out"

очень смущает, особенно время работы такой конструкции, оно в разы больше предыдущей. И с таким синтаксисом раньше не сталкивался.

[Vitsliputsli]

Сослан Хлоев, замените DISTINCT на GROUP BY и все будет работать быстро. Оптимизатор PostgreSQL это делает сам, а в MySQL нужно явно написать. Т.е. должно быть так:

SELECT dstnum AS t2dstnum, MAX(calldate) OVER (PARTITION BY dstnum) AS t2lastcall FROM cdr WHERE route = "out" GROUP BY dstnum

Насчет странного синтаксиса DISTINCT(dstnum), я так понимаю, (dstnum) не имеет никакого отношения к оператору distinct, т.к. это отдельное указание поля, но так как оно в скобках, то отсутствие пробела не вызывает ошибку.

[Сослан Хлоев]

Vitsliputsli,

SELECT DISTINCT dstnum AS t2dstnum, MAX(calldate) OVER (PARTITION BY dstnum) AS t2lastcall FROM cdr WHERE route = "out";

С таким запросом ничего по сути не поменялось, но после замены DISTINCT на GROUP BY, запрос выполнился за 1, с небольшим, секунду. Я очень удивлен! Не дадите ссылку на какой нибудь полезный ресурс с подобными моментами? В каких случаях нужно использовать DISTINCT, а в каких GROUP BY? Откуда такая разница? Если хотите оформите в виде ответа, я его сделаю основным. Чтобы потомкам было на что обратить внимание. Спасибо большое, что открыли глаза!

[Vitsliputsli]

Сослан Хлоев, написал ответ.
Обычно рекомендуется использовать DISTINCT, а если это не решает задачу, тогда GROUP BY. Разница между ними следующая, DISTINCT удаляет из результата дубли, GROUP BY группирует данные по определенному полю, а результат по другим полям агрегирует функцией (например, берет максимальное как в нашем случае). Здесь получилась интересная ситуация, DISTINCT не мог помочь и нужно было использовать GROUP BY, но задачу решили с помощью аналитики. В итоге, после отработки аналитической функции в каждом окне все строки были дублями (т.е. весь смысл аналитики исчез), а эти дубли были удалены DISTINCT. Удаление дублей очень затратная операция, поэтому и такие временные показатели. При использовании GROUP BY при прохождении таблицы сразу будет выбираться только максимальное значение для группируемого поля и дубли даже не будут созданы.
Почему в PostgreSQL это происходит гораздо быстрее могу только предположить, что оптимизатор PostgreSQL умеет смотреть на шаг вперед и откидывает дубли еще при первом прохождении таблицы. Тут нужно копать в сторону работы оптимизатора, если, конечно, это интересно. Описание DISTINCT и GROUP BY в любой документации хорошо дано. А вот по ресурсам, где почитать про внутреннюю работу СУБД, не подскажу, так как структурированную информацию не находил, только по крупицам и в разных местах.

[Сослан Хлоев]

Vitsliputsli, спасибо огромное!

Answer 1

[Vitsliputsli]

нужно так:

SELECT 
        dstnum AS t2dstnum, 
        MAX(calldate) AS t2lastcall 
    FROM cdr 
    WHERE route = "out" 
    GROUP BY dstnum

аналитика здесь не нужна, только агрегатная функция с группировкой. В итоге, при прохождении таблицы мы не собираем дубликаты, а берем только нужные строки. Иначе, будет создана временная таблица со множеством ненужных дубликатов, на удаление которых у DISTINCT уйдет время. Судя по работе запроса, в PostgreSQL оптимизатор сам догадывается что нужно сделать именно так, в MySQL нужно задавать явно.