php mysql переменная в запросе

MySQL — Использование переменных в запросе

Довольно часто спрашивают, есть ли аналоги аналитических (оконных) функций в MySQL. Примечание. На момент написания статьи таких аналогов не было, однако статья и ныне представляет собой академический интерес в плане разбора оригинального для MySQL подхода к использованию переменных.

Для замены аналитических функций часто используют запросы с самосоединением, сложные подзапросы и прочее. Большинство таких решений оказываются неэффективными с точки зрения производительности.

Также в MySQL нет рекурсии. Однако с некоторой частью задач, которые обычно решаются аналитическими функциями или рекурсией, можно справиться и средствами MySQL.

Одним из этих средств является уникальный, нехарактерный для прочих СУБД механизм работы с переменными внутри запроса SQL. Мы можем объявить переменную внутри запроса, менять ей значение и подставлять в SELECT для вывода. Причем порядок обработки строк в запросе и, как следствие, порядок присвоения значений переменным можно задать в пользовательской сортировке!

Предупреждение. В статье подразумевается, что обработка выражений в предложении SELECT осуществляется слева направо, однако официального подтверждения такого порядка обработки в документации MySQL нет. Это необходимо иметь в виду при смене версии сервера. Для гарантии последовательности вычисления можно использовать фиктивный оператор CASE или IF.

Аналог рекурсии

Рассмотрим простой пример, который генерирует последовательность Фибоначчи (в последовательности Фибоначчи каждый член равен сумме двух предыдущих, а первые 2 равны единице):

Данный запрос генерирует 18 чисел Фибоначчи, не считая первых двух:

Разберём теперь как оно работает.

В строчках 5) 6) генерируется 9 записей. Тут ничего необычного.

В строчке 7) мы объявляем две переменные @I, @J и присваиваем им 1.

В строке 3) происходит следующее: сначала переменной @I присваивается сумма двух переменных. Затем то же самое присваиваем переменной @J, причем с учетом того, что значение @I уже поменялось.

Другими словами, вычисления в SELECT выполняются слева направо – см. также замечание в начале статьи.

Причем изменение переменных осуществляется в каждой из наших 9 записей, т.е. при обработке каждой новой строки в переменных @I и @J будут содержаться значения, вычисленные при обработке предыдущей строки.

Чтобы решить эту же задачу средствами других СУБД, нам пришлось бы писать рекурсивный запрос!

Примечание:
Переменные нужно объявлять в отдельном подзапросе (строка 7), если бы мы объявили переменную в предложении SELECT, она, скорее всего, вычислилась бы только 1 раз (хотя конкретное поведение будет зависеть от версии сервера). Тип переменной определяется значением, которым она инициализирована. Этот тип может динамически меняться. Если переменной присвоить NULL, её типом будет BLOB.

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

Аналоги аналитических функций

Переменные также можно использовать для замены аналитических функций. Далее несколько примеров. Для простоты будем считать, что все поля NOT NULL, а сортировка и партиционирование (PARTITION BY) происходят по одному полю. Использование NULL значений и более сложных сортировок сделает примеры более громоздкими, но суть не поменяет.

Для примеров создадим таблицу TestTable:

где
group_id – идентификатор группы (аналог окна аналитической функции);
order_id – уникальное поле, по которому будет производиться сортировка;
value – некоторое числовое значение.

Заполним нашу таблицу тестовыми данными:

Примеры замены некоторых аналитических функций.

1) ROW_NUMBER() OVER(ORDER BY order_id)

group_id order_id value RowNum
1 1 1 1
1 2 2 2
1 3 2 3
2 4 1 4
2 5 2 5
2 6 3 6
3 7 1 7
3 8 2 8
4 9 1 9
3 11 2 10

2) ROW_NUMBER() OVER(PARTITION BY group_id ORDER BY order_id)

group_id order_id value RowNum
1 1 1 1
1 2 2 2
1 3 2 3
2 4 1 1
2 5 2 2
2 6 3 3
3 7 1 1
3 8 2 2
3 11 2 3
4 9 1 1

3) SUM(value) OVER(PARTITION BY group_id ORDER BY order_id)

group_id order_id value RunningTotal
1 1 1 1
1 2 2 3
1 3 2 5
2 4 1 1
2 5 2 3
2 6 3 6
3 7 1 1
3 8 2 3
3 11 2 5
4 9 1 1

4) LAG(value) OVER(PARTITION BY group_id ORDER BY order_id)

group_id order_id value LAG
1 1 1 NULL
1 2 2 1
1 3 2 2
2 4 1 NULL
2 5 2 1
2 6 3 2
3 7 1 NULL
3 8 2 1
3 11 2 2
4 9 1 NULL

Для LEAD всё то же самое, только нужно сменить сортировку на ORDER BY group_id, order_id DESC

Для функций COUNT, MIN, MAX всё несколько сложнее, поскольку, пока мы не проанализируем все строчки в группе(окне), мы не сможем узнать значение функции. MS SQL, например, для этих целей «спулит» окно (временно помещает строки окна в скрытую буферную таблицу для повторного к ним обращения), в MySQL такой возможности нет. Но мы можем для каждого окна вычислить значение функции в последней строке при заданной сортировке (т.е. после анализа всего окна), а затем, отсортировав строки в окне в обратном порядке, проставить вычисленное значение по всему окну.

Таким образом, нам понадобится две сортировки. Чтобы итоговая сортировка осталась той же, что и в примерах выше, отсортируем сначала по полям group_id ASC, order_id DESC, затем по полям group_id ASC, order_id ASC.

5) COUNT(*) OVER(PARTITION BY group_id)

В первой сортировке мы просто нумеруем записи. Во второй всем строкам окна присваиваем максимальный номер, который и будет соответствовать количеству строк в окне.

group_id order_id value Cnt
1 1 1 3
1 2 2 3
1 3 2 3
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 3
3 8 2 3
3 11 2 3
4 9 1 1

Функции MAX и MIN вычисляются по аналогии. Приведу только пример для MAX:

6) MAX(value) OVER(PARTITION BY group_id)

group_id order_id value MaxVal
1 1 1 2
1 2 2 2
1 3 2 2
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 2
3 8 2 2
3 11 2 2
4 9 1 1

7) COUNT(DISTINCT value) OVER(PARTITION BY group_id)

Интересная вещь, которая отсутствует в MS SQL Server, но её можно вычислить с подзапросом, взяв MAX от RANK. Так же поступим и здесь. В первой сортировке вычислим RANK() OVER(PARTITION BY group_id ORDER BY value DESC), затем во второй сортировке проставим максимальное значение всем строкам в каждом окне:

group_id order_id value Cnt
1 1 1 2
1 2 2 2
1 3 2 2
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 2
3 8 2 2
3 11 2 2
4 9 1 1

Производительность

Для начала сравним по производительности нумерацию строк в запросе с помощью самосоединения и с помощью переменных.

1) Классический способ с самомоединением

Что на 10000 записей в таблице TestTable выдаёт:

Duration / Fetch
16.084 sec / 0.016 sec

2) С использованием переменных:

Duration / Fetch
0.016 sec / 0.015 sec

Результат говорит сам за себя. Однако надо понимать, что вычисленные с помощью переменных значения не оптимально использовать в условиях фильтрации. Сортировка и вычисление будут происходить для ВСЕХ строк, несмотря на то, что в итоге нам нужна только малая их часть.

Рассмотрим более подробно на примере такой задачи:

Вывести по 2 первые строки из таблицы TestTable для каждого значения group_id, отсортированных по order_id.

Вот как эта задача решалась бы в СУБД с поддержкой аналитических функций:

Однако оптимизатор MySQL ничего не знает о том, по каким правилам мы вычисляем поле RowNum. Ему придётся пронумеровать ВСЕ строки, и только потом отобрать нужные.

Теперь представьте, что у нас 1 миллион записей и 20 уникальных значений group_id. Т.е. чтобы выбрать 40 строк, MySQL будет вычислять значение RowNum для миллиона строк! Красивого решения этой задачи одним запросом в MySQL нет. Но можно сначала получить список уникальных значений group_id, например, так:

Затем средствами любого другого языка программирования сгенерировать запрос вида:

20 лёгких запросов отработают намного быстрее, чем вычисление RowNum для миллиона строк.

Источник

Оптимизация запросов MySQL с использованием пользовательских переменных

Введение. В современном мире существует большое количество задач, в рамках которых приходится обрабатывать большие массивы однотипных данных. Яркими примерами являются системы для анализа биржевых котировок, погодных условий, статистики сетевого трафика. Многие из этих систем используют различные реляционные базы данных, в таблицах которых содержатся такие объемы данных, что правильное составление и оптимизация запросов к этим таблицам становится просто необходимым для нормального функционирования системы. В этой статье описаны методы решения ( и сравнительные временные характеристики используемых методов ) нескольких задач по получению данных из таблиц СУБД MySQL, содержащих статистику о проходящем через маршрутизаторы одного из крупных российских сетевых провайдеров сетевом трафике. Интенсивность потока данных, поступающего с главного маршрутизатора такова, что ежесуточно в таблицы базы данных используемой системы мониторинга сетевого трафика поступает в среднем от 400 миллионов до миллиарда записей, содержащих информацию о транзакциях TCP/IP (рассматриваемый маршрутизатор экспортирует данные по протоколу netflow). В качестве СУБД для системы мониторинга используется MySQL.

Использование баз данных. Запросы в теории реляционных баз данных базируются на концепции операций над множествами, а в теории множеств концепция времени отсутствует. В действительности же всё иначе: СУБД реализуют абстрактные концепции на базе реального оборудования. В результате выполнение запросов требует много (иногда чрезвычайно много) времени, и не всегда есть возможность ждать длительное время, поэтому проблема поиска путей ускорения применяемых запросов стоит достаточно остро — благо, эти пути существуют. Один из основных и наиболее часто применяемых способов – индексирование таблиц, позволяющее ускорить их просмотр. Другой способ – создание запросов, оказывающих влияние на механизм планирования, позволяющий запросам различных клиентов взаимодействовать более эффективно. Также существует возможность модифицировать настройки аппаратной части, позволяющие повысить производительность, обойдя физические ограничения, присущие тому или иному типу оборудования.

Работа с индексами. Таблица, в который нет индексов, представляет собой беспорядочный набор строк, и для поиска нужной записи, допустим по id, необходимо проверить все ее строки на совпадение с искомым значением, для этого нужно просканировать всю таблицу от начала до конца. Это может занять много времени и будет исключительно неэффективно, особенно если таблица содержит только несколько записей, удовлетворяющих условию поиска.

В качестве примера рассмотрим таблицу, содержащую подведомственные рассматриваемому интернет-провайдеру организации. В данном контексте в таблице присутствует два поля – id организации (org_id) и название организации (org_name).

Допустим, мы добавили индекс на поле org_id (см. Рис. 1 ). Индекс содержит запись о каждой строке из таблицы, и записи индекса отсортированы по значению org_id. Теперь вместо сканирования всех записей в таблице мы можем воспользоваться индексом. Предположим, что требуется найти строку, содержащую запись об организации (Институт автоматики и электрометрии СО РАН), у которой уникальный идентификатор (org_id) равен 2. Сканирование по индексу возвращает одну строку. Значения в индексе отсортированы по возрастанию, поэтому достижении следующего значения (с org_id равным 3) можно завершать сканирование: после 3 мы уже не найдем нужных значений. В случае, если искомые значения находятся где-то посередине таблицы, с помощью специальных алгоритмов (например, методом бинарного поиска) можно перейти к нужной строке без длительного линейного сканирования таблицы.

Рис. 1. Взаимодействие с таблицей, с использованием индекса

В целом, большинство запросов можно оптимизировать, если правильно создать нужные индексы в таблице и построить запрос так, чтобы они эффективно использовались. Однако существуют такие запросы, скорости выполнения которых отнюдь не помогают индексы — например, в случае, когда по индексу выбирается больше чем около 1/10 всех записей в таблице, оптимизатор предпочтет FTS (Full Table Scan) вместо использования индекса, поскольку последовательное чтение с диска происходит быстрее, чем читать из разных частей диска (передвижение головки по диску — seek — это “дорогая” операция). Оптимизатор можно “заставить” использовать индекс, используя опцию FORCE INDEX, но это обычно не даёт выигрыша в скорости. Также таблицы могут быть такого большого размера, что создание индекса будет непрактичным с точки зрения занимаемого объема или длительности выполнения операции. Кроме того, за удобство использования индексов приходится определенным образом “расплачиваться”, у индексов есть свои недостатки. В большинстве своем они незначительны, но о них стоит знать.

Недостатки использования индексов. Во-первых, индексы ускоряют поиск данных, но замедляют операции добавления, удаления и модификации данных в индексируемых столбцах, поскольку при каждом изменении данных в такой таблице приходится перестраивать индекс. Взаимосвязь тут простая: чем больше индексов имеет таблица, тем больше замедление операций над записями.

Необходимо инициализировать переменные до выполнения запроса, так как иначе тип переменных определится в процессе выполнения самого запроса, а определится он может некорректно, в результате будет получен неверный результат. Пример, обсуждение.

Преимущества использования ПП. Возможность сохранять промежуточные результаты в переменных и оперировать им по ходу дальнейшего выполнения запроса позволяет значительно ускорить выполнение некоторых запросов (за счет меньшего количества сканирований таблицы), а также позволяет выполнять такие запросы, которые в стандартной реляционной модели реализуются очень сложно или вовсе не реализуются.

Система мониторинга сохраняет информацию о входящем и исходящем трафике целиком, и в частности, по каждому из каналов в отдельных таблицах. Тип таблицы – MyISAM.
Упрощенная форма таблицы такого типа:

Для поиска максимального скачка входящего (также как и исходящего, но в этом случае идёт работа с полем src) трафика необходимо просканировать всю таблицу, при этом сравнивая значения поля dst, соответствующие меткам t_prev (временная метка на предыдущем шаге ) и t_curr (временная метка на текущем шаге). В этом и состоит основная сложность: в рамках реляционной модели невозможно запомнить предыдущее значение в процессе сканирования таблицы и использовать его напрямую. Его можно вычислить с помощью подзапроса
где t1.t – текущее значение временной метки), но такая конструкция сильно усложняет запрос, в общем случае сложность запроса с O(n) увеличивается до O(n 2 ) (из-за того, что приходится для каждой временной метки вычислять предыдущую путём сканирования всей таблицы), что сильно сказывается на скорости выполнения запроса. В случае наличия в таблице уникального индекса на поле t, вычисление будет проводиться значительно быстрее, но это тогда, когда точно известно, что в таблице нет пропущенных временных меток (а такая идеальная ситуация практически не встречается), и предыдущая временная метка вычисляется просто вычитанием нужного интервала из текущей (по уникальному индексу выборка проходит очень быстро). В общем же случае значение временной метки на предыдущем шаге приходится вычислять подзапросом, основанным не на строгом равенстве, а на нестрогом, используя сортировку, и такой запрос, естественно, работает значительно дольше. В реляционном варианте в подзапросе для более быстрой выборки данных требуется индекс на поле t, а во внешнем запросе идёт работа с полем dst, поэтому в данном случае в таблице создан составной индекс t_dst на поля (t, dst).

Запрос, работающий с таблицей, где данные расположены в нужном порядке.

Запрос, выбирающий данные в нужном порядке по индексу:

Запрос, сортирующий данные в процессе выборки:

В таблице 1 приведены длительности выполнения запросов (при разном количестве строк в таблице). Количество строк в таблице определяется длительностью интервала. В данном случае рассматриваются интервалы длительностью в одну минуту.

Таблица 1. Измерения длительностей выполнения запросов из примера №1.

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

Пример №2. Для анализа загрузки канала регулярно приходится оценивать загрузку за определенные временные промежутки (по несколько секунд, несколько минут). В данном случае выбран интервал с длительностью 5 секунд. Структура таблицы, из которой выбираются данные — такая же, как в примере №1, временная метка t должна иметь тип TIMESTAMP.

Реляционным запросом такая выборка реализуется через группировку с последующей сортировкой результатов:
Помимо полного прохода по таблице в данном запросе еще реализуется группировка и сортировка результатa.
Тот же запрос можно реализовать через пользовательские переменные с меньшей нагрузкой за счет отсутствия явной группировки, и, следовательно, сортировки (при условии, что в таблице нет пропущенных временных меток).

Запрос, работающий с таблицей, где данные расположены в нужном порядке:
Запрос, выбирающий данные в нужном порядке по индексу:
Запрос, сортирующий данные в процессе выборки:
В рамках выполнения подзапроса сперва идёт суммирование в переменную @c, и в случае, если временная метка соответствует пятисекундному интервалу, то обнуляется счетчик и метке mark присваивается значение 1. Далее внешний запрос выбирает те строки, у которых mark равен 1, то есть искомые.
В таблице 2 приведены длительности выполнения запросов (при разном количестве строк в таблице).

Таблица 2. Измерения длительностей выполнения запросов из примера №2

Как и в первом примере, приведенные варианты запросов с использованием пользовательских переменных MySQL работают в несколько раз быстрее.

Пример №3. С точки зрения работы сети подведомственные организации описываются определенными техническими характеристиками, в частности, выделенными для организации диапазонами IP-адресов. Иногда возникает потребность вычленить самые крупные непрерывные блоки IP-адресов, выделенные для организации.
Таблица организаций и их сетевых диапазонов (nets) представлена следующим образом:

В случае, если нужно выбрать самый крупный непрерывный сетевой диапазон у каждой организации, особых сложностей нет. Но если необходимо выбрать по два самых крупных сетевых диапазона – реляционный вариант запроса на выборку становится достаточно сложным – необходимо для каждого диапазона посчитать количество более крупных сетевых диапазонов, после отсеять те, которые меньше первых двух (подробнее см. здесь).

Вариант запроса в реляционной модели:С помощью пользовательских переменных можно в рамках одного прохода подсчитывать количество выведенных сетевых диапазонов и обнулять счетчик при смене организации. В случае, если счетчик достигает нужного значения (в данном случае 2), записи не выводятся. Саму таблицу перед использованием нужно отсортировать по размерам сетевых диапазонов в рамках организации.

Вариант запроса с пользовательскими переменными MySQL:Большая эффективность запроса с ПП истекает из того, что в реляционном варианте запроса присутствует подзапрос, который обрабатывает каждую запись в таблице.

Пример №4. В предыдущих экспериментах ни разу не рассматривался вариант модификации структуры таблицы (в реальной жизни далеко не всегда ее можно или удобно менять). Однако при работе с темпоральными таблицами просто напрашивается решение сделать еще одну колонку, которая будет содержать предыдущее значение prev измеряемого параметра. Для наиболее высокой скорости поиска максимального перепада значении можно добавить поле, которая будет содержать разницу diff между текущим значением измерения d и предыдущим.

Рассмотрим вариант изменения структуры таблицы – добавление колонки prev. Исходная таблица с данными такова:

Таблица не содержит индексов и первичного ключа – при наличии индекса на поле t или составного индекса на поля (t,d) реляционный вариант запроса работает значительно медленнее.

Строго говоря, в MySQL нет небходимости использовать rownum: для ограничения количества возвращаемых запросом строк существует инструкция LIMIT, но иногда может потребоваться.

PostgreSQL: СУБД PostgreSQL не поддерживает возможность использовать пользовательские переменные в процессе выполнения запроса. Но начиная с версии 9.0 в PostgreSQL есть возможность создавать анонимные блоки кода. К сожалению, они не поддерживают формальных параметров и не возвращают значений. Однако есть возможность создать функцию в схеме pg_temp, что обеспечит автоматическое удаление функции из схемы по завершении сессии. Пример функции, вычисляющей максимальный перепад:Microsoft SQL: Основное отличие запроса от запроса с использованием ПП MySQL в том, что у переменной нужно задавать тип заранее.Oracle: на момент написания этой статьи в языке Oracle SQL отсутствует возможность использовать пользовательские переменные — такая возможность имеется лишь в языке PL/SQL и таких расширениях, как SQLPLUS, Oracle XE, и. т. д.

Источник

Как объявить переменную в MySQL?

как объявить переменную в mysql, чтобы мой второй запрос мог ее использовать?

Я хотел бы написать что-то вроде:

4 ответов

в MySQL есть в основном три типа переменных:

вы можете получить доступ к любой пользовательской переменной без объявления или проинициализируйте его. Если вы ссылаетесь на переменную, которая не была инициализированный, он имеет значение NULL и типом string.

вы можете инициализировать переменную, используя SET или SELECT заявление:

пользовательским переменным можно присвоить значение из ограниченного набора данных типы: целочисленная, десятичная, с плавающей запятой, двоичная или небинарная строка, или значение NULL.

пользовательские переменные сессии. То есть пользователь переменная, определенная одним клиентом, не может быть видна или использована другим клиенты.

локальные переменные должны быть объявлены с помощью DECLARE перед доступ к нему.

их можно использовать как локальные переменные и входные параметры внутри хранимой процедуры:

глобальные переменные влияют на общую работу сервера, в то время как переменные сеанса влияют на его работу для отдельного клиента подключение.

они могут быть установлены при запуске сервера с помощью параметров в командной строке или в файле опции. Большинство из них можно динамически изменять во время работы сервера с помощью SET GLOBAL или SET SESSION :

обоих операторов = и := приняты

если несколько наборов записей найдено только последнее значение в col2-keep (override);

в этом случае результат select не содержит значений col2

использовать set или выберите

Ex. Объявить id INT без знака по умолчанию 1;

чтобы предоставить значение по умолчанию для переменной, включите предложение по умолчанию. Значение может быть указано как выражение; оно не обязательно должно быть константой. Если предложение по умолчанию отсутствует, начальное значение равно NULL.

локальные переменные обрабатываются как сохраненные рутинные параметры с уважением для проверки типа данных и переполнения.

объявления переменных должны отображаться перед объявлениями курсора или обработчика.

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

Источник