Что такое сложный запрос mysql

Тут выберутся все поля. Все с математикой дружат? допустим средний размер фотки 3,5Мб умножаем на 300 выбранных записей 1Гб. просто так выбрали из БД, время выполнения скрипта может и небольшое, но вот то кол-во выбранных полей неправильно. Но скорее всего этот запрос относится не к сложности запроса, а к тупости разработчика, которому пофиг на оптимизацию, даже и такую простую, как выборка только нужных полей из таблиц.

Сложный запрос к БД это и такой запрос. Затрагиваем несколько таблиц + производим выборку с помощью LIKE. Наверное сложный, хоть и выполняется он достаточно быстро, при 10К записях в таблице. Это код из модуля для ModX, писал словарь.

SELECT dic.name as dic_name, dic.id as dic_id,dw.id,dw.alias,dw.short_desc, dw.name AS d_name FROM `dictionary_word` dw LEFT JOIN `word` ww ON ww.id=dw.word_id LEFT JOIN `dictionary` dic ON dw.dictionary_id=dic.id where ww.name like '%".$_GET['search']."%' and dic.id=".$_GET['sid']." and dic.public=0 

Всё-таки из общенародного и общепринятого сложный запрос к БД это запрос в котором затрагиваются несколько таблиц, т.е. происходит JOIN. Но на практике сложность запроса формируется многими факторами, если взглянуть на первый запрос, то там глупость разработчика, во втором запрос большой, но выполняется быстро. Всё таки сложный запрос - это тот который занимает определенно больше времени на исполнение на которое вы расчитывали, и подразумевает оптимизацию, если она возможна. Причем возможна несколькими способами, либо изменением условий, либо разбитием на несколько более простых. Экспериментировать можно много,тут главное правильный окончательный эффект. так же если смотреть на любой запрос с

LIMIT 900000,300 

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

можно ли одним запросом обратиться к нескольким БД? 

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

$link1=mysql_connect(); $link2=mysql_connect(); 

2 разных подключения, и запрос предназначенный для БД 1 не выполнится для БД 2. Так что думаю тут выбрать правильный ответ тяжеловато, они по сути своей все правильны, ну или хотя бы смотрят в нужную сторону.

у меня как обычно всё 🙂

ps 3341 буквы / 538 слова

Сложный запрос MySql: группировка и фильтрация в запросе?

Добрый день.
Практикуюсь в работе с Rest API на реальном примере.
есть игровой сервер, задача: сделать базу данных для полиции, с поиском по пользователям и их данным. Наверное, типичный CRUD
База - Mysql 8, api сгенерировано через xmysql

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

axios.get('http://***:3000/api/xjoin?_join=user.users,_lj,vehicle.owned_vehicles,_lj,property.owned_properties,_lj&_on1=(user.identifier,eq,vehicle.owner)&_on2=(user.identifier,eq,property.owner)&_fields=user.firstname,user.lastname,user.dateofbirth,user.phone_number,user.job,property.name,vehicle.plate,vehicle.type,vehicle.job&_size=1000') .then(function (response) < console.log(response) >)

Т.к. я не очень силен в mysql, даже сказал бы, очень не силен, у меня возникли следующие вопросы:

1) Возможно ли сгруппировать "property_name" и подобные ключи с разными value в один, чтобы не плодить кучу объектов одинаковых? И как правильно это сделать. Гуглом был найдет gorup_concat, но не понял как его подружить с моими требованиями. В апи есть поддержка dynamic query, куда по сути можно его вставить, переделав запрос под синтаксис самого mysql

2) Как правильно отфильтровать нерелевантные ответы?
Например, есть "vehicle_type": "helicopter", но мне нужен type: car или null, если машины нет. А если машина есть, то "vehicle_job" должен быть null. То же самое с проперти, сгруппировать в один, если несколько или вернуть null, если нет

Фильтруя прям в запросе с помощью

&_where=(vehicle.type,like,car)~and(vehicle.job,is,null)

у меня теряются нужные пользователи

Буду благодарен любой помощи

UPD: tiny.cc/o9thez - база с данными для примера

• Вопрос задан более трёх лет назад
• 272 просмотра

7 комментариев

Средний 7 комментариев

MySQL — сложные запросы

Привет. Стал я тут писать одно Web-приложение и столкнулся с тем что на вывод одной таблице у меня получается около 4 запрос в БД. Если делать мини сервис с мелкой посещаемостью, то нагрузка как бы не о чем. А вот если людей будет уже около 2000 тысяч хотя бы в сутки, то запросы растут с геометрической прогрессией.

Чтобы не нагружать мой сервер, решил я более глубоко погрузится в MySQL и выяснил, что можно составлять сложные запросы к Базе Данных(БД). Т.е. было 4 select к БД, а стал один с вложенными запросами и join`ами.

К тому же сложные запросы MySQL помогут сократить код логики. Раньше было 4 запроса и соответственно получали 4 массива, каждый нужно было обойти, придать ему нужный вид, дальше объединить его с другими. А сейчас получается один запрос и один массив, все.

Задача

1. Даллар США (это название валюты на русском).
2. USD (это название валюты на английском).
3. Текущая дата.
4. Значение валюты.
5. Вчерашняя дата.
6. Значение валюты.
7. Колонка разность (отянть вчера от сегодня).

И так представим, что БД у нас построена по правилам «Трех нормальных форм». Т.е. 1 и 2 пункт из списка выше хранится в одной таблице, 3-6 хранятся данные в другой таблице. А 7 пункт вообще вычисляется средствами MySql.

Сам код запроса

Запрос будет выглядеть следующем образом.

SELECT r.id_name_currency, r.value, yesterday.value, r.date, yesterday.date, pc.name_currency_en, pc.name_currency_ru, (yesterday.value - r.value) FROM parser_all_exchange_rates r JOIN ( SELECT rr.date, rr.value, rr.id_name_currency FROM parser_all_exchange_rates rr WHERE rr.id_name_banks = 233 AND rr.date = CURRENT_DATE() - INTERVAL 1 DAY ) yesterday JOIN parser_name_currencies pc ON r.id_name_currency = pc.id WHERE r.id_name_banks = 233 AND r.date = CURRENT_DATE() AND yesterday.id_name_currency = r.id_name_currency

В начале для легкости понимания лучше разбить задачу на блоки.

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

Нюансы

А как же без них.

FROM parser_all_exchange_rates r, ( SELECT rr.date, rr.value, rr.id_name_currency FROM parser_all_exchange_rates rr WHERE rr.id_name_banks = 233 AND rr.date = CURRENT_DATE() - INTERVAL 1 DAY ) yesterday INNER JOIN parser_name_currencies pc ON r.id_name_currency = pc.id

Если посмотреть на код выше, то после from идет указатель на имя таблицы, в которой мы ищем информацию, затем идет в скобках следующая таблица, после скобок ей присваивается имя yesterday и затем указываем join. Если написать так, то будет синтаксическая ошибка.

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

Необязательно писать INNER JOIN, можно просто JOIN. СУБД по умолчанию выполнить именно внутреннее соединение.

Будут вопросы пишите в комментариях.

6 SQL-запросов, о которых должен знать каждый дата-инженер

Знание продвинутого синтаксиса SQL необходимо и новичку, и опытному дата-инженеру или аналитику данных.

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

Объем данных на этом графике показан в зеттабайтах.

1 зеттабайт = 1 триллион гигабайтов

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

Высококачественными и очень надежными для относительно естественного моделирования данных их делают ACID-свойства SQL.

Я и сам дата-инженер, давно использую SQL и знаю, как важно быстрее писать сложные запросы. И продвинутый синтаксис SQL будет здесь очень кстати.

В примерах использованы данные таблицы bill («Счет»):

Нарастающий итог

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

Нарастающим итогом называется сумма значений во всех ячейках столбца до следующей ячейки в этом конкретном столбце.

Вот запрос на эту сумму:

SELECT id,month 
, Amount 
, SUM(Amount) OVER (ORDER BY id) as total_sum
FROM bill

А вот как будет выглядеть результат:

Обобщенные табличные выражения

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

Фактически это временный именованный результирующий набор данных, на который можно ссылаться внутри оператора SELECT , INSERT , UPDATE или DELETE .

Рассмотрим простой запрос:

SELECT *
FROM bill
WHERE id in 
(SELECT DISTINCT id 
FROM id 
WHERE country = "US" 
AND status = "Y" 
)

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

WITH idtempp as ( 
SELECT id as id 
FROM id 
WHERE country = "US" 
AND status = "Y"
)

SELECT *
FROM bill
WHERE id in (SELECT id from idtempp)

Упорядочение данных

Дата-инженерам и аналитикам данных очень часто приходится упорядочивать значения по каким-либо параметрам, например зарплате, затратам и т. д. И это экономит много времени при поиске точного запроса.

SELECT 
id, 
Amount, 
RANK() OVER (ORDER BY Amount desc)
FROM bill

В этом запросе набор данных упорядочен по столбце amount («Сумма»).

Вместо RANK() используется также DENSE_RANK() . Он аналогичен, но не пропускает следующее по порядку значение, если у двух строк одинаковое значение.

Добавление подытогов

Наличие промежуточного итога (подытога) помогает оценить данные в контексте общего итога.

Это расширенная версия оператора GROUP BY : здесь есть возможность добавления к данным промежуточных и общих итогов.

SELECT 
Type, 
id, 
SUM (Amount) AS total_amount
FROM bill
GROUP BY Type,id WITH ROLLUP

Примечание: это запрос в MySQL. Для других синтаксис свертки может отличаться.

Здесь в запросе строка со значениями null и для типа, и для идентификатора — это итог. Есть также подытоги со значениями null только в столбце идентификатора: это 4-я и предпоследняя строки.

Временные функции

Временные функции позволяют легко менять данные без использования огромных операторов case .

В следующем примере временная функция применяется для преобразования типа в род. Это можно было сделать с помощью встроенного в запрос оператора case , но тогда было бы неудобно читать.

CREATE TEMPORARY FUNCTION get_gender(type varchar) AS ( 
CASE WHEN type = "M" THEN "male" 
WHEN type = "F" THEN "female" 
ELSE "n/a" 
END
)
SELECT 
name, 
get_gender(Type) as gender
FROM bill

Дисперсия и среднеквадратическое отклонение

Для получения этих значений есть специальные агрегатные функции: VARIANCE , VAR_POP и VAR_SAMP . Они группируют данные и используются для определения дисперсии, дисперсии группы и дисперсии выборки набора данных по отдельности.

SELECT 
VARIANCE(amount) AS var_amount, 
VAR_POP(amount) AS var_pop_amount, 
VAR_SAMP(amount) AS var_samp_amount, 
STDDEV_SAMP(amount) as stddev_sample_amount, 
STDDEV_POP(amount) as stddev_pop_amount,
FROM bill

VAR_POP — дисперсия совокупности;
VAR_SAMP — дисперсия выборки;
STDDEV_SAMP — среднеквадратическое отклонение для выборки;
STDDEV_POP — среднеквадратическое отклонение для совокупности.

Это были основные SQL-команды, которые я постоянно использовал, работая дата-инженером, и которые пришлись очень кстати при решении многих бизнес-задач.

Stats подтверждает, что экосистема инструментов SQL, которая включает в себя все: от Excel и Tableau до SparkSQL — используется в более чем 60 % организаций. Это настоящий подвиг для SQL, особенно учитывая его возраст.

Уверен, что и вам как дата-инженеру эти команды будут полезны.

• Как подключить базу данных MySQL к сайту на PHP
• SQL в науке о данных
• Руководство по анализу данных с SQL