ИТ Блог. Администрирование серверов на основе Linux (Ubuntu, Debian, CentOS, openSUSE)
(Пока оценок нет)
Загрузка...
Пользовательские переменные, хотя и не входят в стандартный SQL, большие объекты в MySQL. Они позволяют сохранить некоторое «состояние» для жизни сеанса: сеанс может выполнить инструкцию для вычисления значения, хранить это значение в переменной пользователя и использовать его во всех последующих заявлениях. Это позволяет избежать перерасчет одинакового значения в каждом из следующих утверждений, таким образом, улучшить эффективность.
Например, давайте моделировать лотерею: подобрать случайного пользователя, хранить его в таблице победителей, и отправить по электронной почте ему поздравление:
Использование @winner позволило нам запустить подзапрос один раз, а не два раза; что быстрее и правильнее: работает она в два раза, что привело бы к потенциально двум разным победителям для вставки и отправки по электронной почте, в результате чего появилась бы логическая ошибка в нашем проекте.
Это все красиво и аккуратно. Выше пример ясно отделяет фазу:
Но это также может устанавливать и считывать пользовательскую переменную в одном операторе. Случай использования: иметь «движущееся значение», которое изменяется для каждой строки, на основе значения, которое она имела в предыдущем ряду (это предполагает, что понятие «предыдущего ряда», определяемого некоторой упорядоченности).
Вот классический пример, который генерирует номера строк (1, 2, …) в соответствии с каким-то порядком. У нас есть таблица известных людей:
CREATE TABLE people (name VARCHAR(100), birthdate DATE, sex CHAR(1),
citizenship CHAR(2));
INSERT INTO people VALUES
("Andrey Ex", "19375653", "M", "RU"),
("Alex Merphy", "18364556", "M", "UK"),
("Anna Level", "19475664", "F", "DE"),
("Rosa Hutor", "19676756", "F", "GT"),
("Vadim Bublickov", "17948373", "M", "FR"),
("Artem Narik", "19374645", "F", "RU");
Мы хотим отредактировать и пронумеровать их по дате рождения:
SET @rownum:=0; SELECT @rownum:=(@rownum+1) AS num, name, birthdate FROM people ORDER BY birthdate; +--------+------------------+------------+ | num | name |дата рождения| +--------+------------------+------------+ | 1 | Vadim Bublickov | 1759-11-12 | | 2 | Alex Merphy | 1873-02-05 | | 3 | Andrey Ex | 1933-07-23 | | 4 | Anna Level | 1951-08-10 | | 5 | Rosa Hutor | 1960-11-24 | | 6 | Artem Narik | 1971-06-15 | +--------+------------------+------------+
Оно работает.
Мы добавим: удивительно. Теперь объясним.
Стандарт SQL говорит, что мы должны объединить таблицы (здесь мы имеем только одну, people), положить результат объединения в воображаемой таблице, затем оценить выбранные выражения для каждой строки, образуя еще одну воображаемую таблицу, а затем отсортировать эту таблицу и вернуть его пользователю.
Да, в теории упорядочения является очень заключительным этапом выполнения, после того, как вычислены выбранные выражения для всех соответствующих строк (что логично, поскольку ORDER BY разрешено ссылаться на отдельные выражения с помощью псевдонима). Таким образом, если мы будем следовать этому, мы читаем people, оцениваем отдельные выражения для каждой строки, вычислим номер строки для каждой строки, и, наконец, мы упорядочиваем результат по дате рождения. Вычисляется перед упорядочиванием, номера строк не будет иметь никаких оснований, чтобы быть в соответствии с порядком.
К счастью, здесь, MySQL видит, что есть только одна таблица и что выражение упорядочения является столбец этой таблицы, так что делает оптимизацию: это первый orders ряда people, а затем читает этот сортированный результат, и для каждой строки вычисляет выбранные выражения ( в том числе @rownum). Так @rownum увеличивается в порядке, по желанию. Но, как уже отмечалось, опираясь на ORDER BY оптимизации проделанной MySQL, которая не гарантированно остается постоянным в будущем (кто знает? Мы вновь эвристические и экономически обоснованных решений по оптимизации время от времени). Более того, если в дальнейшем разработчик приложения изменяет наш запрос, эта модификация может привести MySQL изменить свою стратегию оптимизации и сделать сортировку после оценки выбранных выражений, полностью нарушая логику @RowNum. Чтобы продемонстрировать это, давайте говорить, что об этом не знает разработчик, просто хочет добавить в наш запрос столбец, отображающий полное название страны гражданства – довольно невинно, да? Он первым делает таблицу с полными именами:
CREATE TABLE country(code CHAR(2), name VARCHAR(100));
INSERT INTO country VALUES
("GT", "Guatemala"),
("DE", "Germany"),
("FR", "France"),
("UK", "United Kingdom"),
("RU", "Russia");
и теперь он редактирует наш запрос пронумерованных строк, соединяя людей со страной:
SET @rownum:=0; SELECT @rownum:=(@rownum+1) AS num, people.name, people.birthdate as дата рождения, country.name FROM people JOIN country ON people.citizenship=country.code ORDER BY people.birthdate; +--------+------------------+-------------+--------------------------+ | num | name |дата рождения| name | +--------+------------------+-------------+--------------------------+ | 5 | Vadim Bublickov | 1759-10-26 | France | | 2 | Alex Merphy | 1873-02-05 | United Kingdom | | 1 | Andrey Ex | 1933-07-23 | Russia | | 3 | Anna Level | 1951-08-10 | Germany | | 4 | Rosa Hutor | 1960-11-24 | Guatemala | | 6 | Artem Narik | 1971-06-15 | Russia | +--------+------------------+-------------+--------------------------+
num совершенно неправильно! Поскольку оптимизатор выбрал план, который делает заказ в прошлом, таким образом, после оценки пользовательских переменных. Перечень факторов, которые могут нарушить нелимитирующий Num являются: порядок соединения, использование присоединения буферизации, использование временной таблицы, GROUP BY …
Идем дальше … чтобы избежать туда-обратно, который посылает инициализирующее заявление SET, некоторые умные пользователи (без иронии на «умной») связывают два в одном с этим трюком:
SELECT @rownum:=(@rownum+1) AS num, name, birthdate FROM (SELECT @rownum:=0) AS initialization, people ORDER BY birthdate;
Оно работает. Он основан на том, что MySQL оценивает содержание производной таблицы с именем initialization. Но в будущем, MySQL может стать умнее и сказать, что «эта производная таблица не используется в инструкции, так что я могу просто пропустить его оценку». И, таким образом, пропустить инициализацию.
Эти запросы делают предположения, которые могут держаться сегодня, но не являются будущими. Если они удерживаются сегодня, у нас нет сознательного намерения нарушать их, но однажды мы неизбежно сделаем, как старый код получает рефакторинг, как новые функции SQL реализуются, как мы приближаемся к стандарту SQL. То, что никогда не было явно гарантировано, может измениться. Случайное изменение может произойти с нашей стороны или с вашей стороны (см. пример с полным именем гражданина и вступлением).
Наше руководство осторожно предупреждает об этом :
«Как правило, кроме как в отчетности SET, вы никогда не должны присвоить значение переменной пользователя и читать значение в том же заявлении. Например, чтобы увеличить переменную, это нормально: SET @a = @a + 1. Для других заявлений, таких как SELECT, вы можете получить результаты, которые вы ожидаете, но это не гарантировано».
Мы слышали, что кто – то сказал: « Вы критикуете, но нет никакого другого способа получить пронумерованные строки в MySQL!». Вот хорошие новости: в MySQL 8.0, есть, наконец, еще один способ: оконные функции. Вот запрос, использует оконную функцию row_number:
SELECT ROW_NUMBER() OVER (ORDER BY birthdate) AS num, name, birthdate as дата рождения FROM people; +--------+------------------+-------------+ | num | name |дата рождения| +--------+------------------+-------------+ | 1 | Vadim Bublickov | 1759-10-26 | | 2 | Alex Merphy | 1873-02-05 | | 3 | Andrey Ex | 1933-07-23 | | 4 | Anna Level | 1951-08-10 | | 5 | Rosa Hutor | 1960-11-24 | | 6 | Artem Narik | 1971-06-15 | +--------+------------------+-------------+
Это дает правильный результат. Если вы новичок в синтаксисе функций окна, вы можете проверить руководство.
В отличие от запроса с пользовательской переменной, добавление присоединения к запросу с оконной функцией не нарушает num:
SELECT ROW_NUMBER() OVER (ORDER BY people.birthdate) AS num, people.name, people.birthdate as дата рождения, country.name FROM people JOIN country ON people.citizenship=country.code; +--------+------------------+-------------+--------------------------+ | num | name |дата рождения| name | +--------+------------------+-------------+--------------------------+ | 1 | Vadim Bublickov | 1759-10-26 | France | | 2 | Alex Merphy | 1873-02-05 | United Kingdom | | 3 | Andrey Ex | 1933-07-23 | Russia | | 4 | Anna Level | 1951-08-10 | Germany | | 5 | Rosa Hutor | 1960-11-24 | Guatemala | | 6 | Artem Narik | 1971-06-15 | Russia | +--------+------------------+-------------+--------------------------+
Подобный случай использования получает номер строки внутри группы; допустим я хочу группу по полу; для этого, умные пользователи написали следующий запрос, который обнаруживает, если пол так же, как в предыдущей строке; если это так, мы в той же группе, что и предыдущие строки, увеличиваем счетчик, в противном случае мы открываем новую группу, чтобы сбросить его до 1:
SET @prev_sex:=''; SELECT @rownum:= ( CASE WHEN (sex=@prev_sex) THEN @rownum+1 ELSE 1 END ) AS num, (@prev_sex:=sex), name, birthdate as дата рождения FROM people ORDER BY sex, birthdate; +------+------------------+------------------+-------------+ | num | (@prev_sex:=sex) | name |дата рождения| +------+------------------+------------------+------------+ | 1 | F | Anna Level | 1951-08-10 | | 2 | F | Rosa Hutor | 1960-11-24 | | 3 | F | Artem Narik | 1971-06-15 | | 1 | M | Vadim Bublickov | 1759-10-26 | | 2 | M | Alex Merphy | 1873-02-05 | | 3 | M | Andrey Ex | 1933-07-23 | +------+------------------+------------------+-------------+
Оно работает. Но это зависит от того, что MySQL устанавливает в @rownum перед установкой @prev_sex, т.е. оценивает выбранное выражение слева направо. Это касается данного запроса, но не для всех запросов. Как сказано в руководстве: «порядок вычисления выражений, включающих пользовательские переменные не определен». Тот же результат теперь достижим с вложенным предложением PARTITION для окна:
SELECT ROW_NUMBER() OVER (PARTITION BY пол ORDER BY birthdate) AS num, sex, name, birthdate as дата рождения FROM people; +--------+------+------------------+-------------+ | num | пол | name |дата рождения| +--------+------+------------------+-------------+ | 1 | F | Anna Level | 1951-08-10 | | 2 | F | Rosa Hutor | 1960-11-24 | | 3 | F | Artem Narik | 1971-06-15 | | 1 | M | Vadim Bublickov | 1759-10-26 | | 2 | M | Alex Merphy | 1873-02-05 | | 3 | M | Andrey Ex | 1933-07-23 | +--------+------+------------------+-------------+
Каковы преимущества нового подхода с оконными функциями? Во- первых, гораздо короче запрос. Во- вторых, это стандарт совместимый так:
Возможно, некоторым особенно сложным и интеллектуальным запросам еще нужны пользовательские переменные методы чтения и набор, или, возможно, некоторые работают лучше, чем их эквивалент с оконными функциями.
Но, как правило, этот старая практика черной магии не должно быть больше необходимым, и преимущественно заменен оконными функции ROW_NUMBER, RANK, LEAD, LAG, FIRST_VALUE…
Вот и на сегодняшний день. Благодарим за использование MySQL!
