MySQL: когда применять проверку условия и операцию файловой сортировки в этом простом случае?

Таблица: актер

-------------------------
| id | actor_id | notes |
-------------------------
| 1  |   1      |  'a'  |
| 2  |   1      |  'a'  |
| 3  |   2      |  'a'  |
| 4  |   3      |  'a'  |
| 5  |   3      |  'a'  |
| 6  |   1      |  'a'  |
-------------------------

SQL

SET @rownum := 0;
SELECT id, actor_id, @rownum FROM actor
WHERE @rownum <= 1
ORDER BY notes, LEAST(0, @rownum := @rownum + 1);

Это пример из раздела Детерминированный порядок оценки Глава 6 Оптимизация производительности запросов в High Performance MySQL, 3rd Edition.

Согласно тексту из книги и «Использование где; Использование временного; Используя результат filesort поля Extra объяснения SQL и этого я ожидал, что приведенный выше SQL выведет каждую строку таблицы актеров.

Я думал, что все шесть строк будут помещены во временную таблицу с использованием '@rownum ‹= 1', где сначала фильтруется, затем файловая сортировка применяется к этой временной таблице, и, наконец, распечатываются шесть строк.

Но реальных результатов всего 2:

+----+----------+---------+
| id | actor_id | @rownum |
+----+----------+---------+
|  1 |        1 |       1 |
|  2 |        1 |       2 |
+----+----------+---------+

Может ли кто-нибудь объяснить детали плана выполнения в этой ситуации?


mysql explain sql-order-by
person lfree    schedule 01.06.2016    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Во-первых, информация extra является флагами, они не описывают порядок выполнения этих операций (хотя выводятся в обычном порядке).

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

Оптимизатор не изменит набор результатов запроса, его задача всегда возвращать одни и те же строки, независимо от того, какой именно план выполнения будет выбран, но самым быстрым (или «самым дешевым») способом. (Хотя иногда вы можете получить некоторое представление о порядке набора результатов).

Таким образом, sql-запрос описывает, что вы хотите получить, он не заботится об оптимизаторе (хотя вы, очевидно, иногда можете «помочь» оптимизатору), ему все равно, будет ли оптимизатор использовать порядок после выбора. Таким образом, если какой-то алгоритм будет быстрее, если вы сначала упорядочиваете, а затем выбираете, или если выполнение будет делать что-то совершенно другое, вы все равно ожидаете того же набора результатов.

Код sql для выражения того, о чем вы думаете (сначала выбирая, а затем упорядочивая), будет выглядеть так:

SET @rownum := 0;
SELECT *, @rownum FROM 
(SELECT id, actor_id, @rownum, notes FROM actor
 WHERE @rownum <= 1
) as subquery
ORDER BY notes, LEAST(0, @rownum := @rownum + 1);

Это даст вам все строки (с внутренними @rownum = 0 и внешними @rownum = 1..6), поскольку порядок и, следовательно, оценка выполняются позже. Здесь у explain будет 2 ряда (внутренняя и внешняя часть).

Но все же есть некоторая неопределенность при использовании одной и той же переменной в запросе несколько раз: порядок ее выполнения (для одной строки) фактически не определен, поэтому возможно, что другая версия mysql будет вести себя немного иначе. Здесь он оценил order by перед select, вы можете видеть это, потому что вы получили 1 и 2 для столбца @rownum, а не 0 и 1.

person Solarflare    schedule 01.06.2016
comment
Я не думаю, что вы поняли мою точку зрения. Этот случай был взят из раздела, посвященного переменной, определяемой пользователем, и времени ее выполнения среди различных процессов выполнения SQL книги High Performance MySQL. Следующий SQL также даст те же 6 строк, что и ваши: SELECT id, act_id, (at)rownum := (at)rownum + 1 FROM acter WHERE (at)rownum ‹= 1 ORDER BY notes. И автор сказал, что это потому, что ORDER BY добавил сортировку по файлам, а WHERE оценивается перед сортировкой по файлам. - person lfree; 03.06.2016
comment
@IFree, ты прав, я не понял твоей точки зрения. Но я почти уверен, что where на самом деле выполняется первым. Я предполагаю, что условие where применяется дважды: первое where, которое поместит 6 строк во временную таблицу, затем сортировка по файлам, а затем, просто потому, что это ожидаемое поведение этого запроса, еще одно where после этого. Для отсутствия order by это ожидаемый результат, и нет второй оценки where с order by (таким образом, 6 строк). С @rownum в порядке, я думаю, я ожидал бы 2-й where (хотя я всегда сортировал бы ... - person Solarflare; 03.06.2016
comment
... вне подзапроса ранга, на всякий случай). Но в основном потому, что так было всегда. (Я предполагаю, что основная проблема заключается в том, что поведение при использовании переменных не везде на 100% логично, но все же последовательно). Я собираюсь подумать о том, как проверить это двойное поведение. - person Solarflare; 03.06.2016