Tips & tricks for MySQL Developers. Работа с SQL

Эта статья задумана мной как сборник некоторых интересных моментов по использованию и оптимизации SQL запросов в БД MySQL, на мой взгляд, плохо освещенных в интернете. Так, из статьи вы узнаете о конструкции with rollup, и о том, как переписать подзапросы in и not in на join'ы, а так же обновление и удаление данных в нескольких таблицах — одним запросом, и многое другое. Начнем по порядку.

Переписываем подзапросы с in и not in на join'ы

Одни из самых распространённых подзапросов являются запросы с in и not in. Причём, мы знаем, что в MySQL join запросы отрабатывают быстрее за счёт эффективного построения плана выполнения запроса оптимизатором (для других БД это утверждение может быть диаметрально противоположным), поэтому попробуем переписать классические подзапросы на join. Как мы это будем делать? Для начала уясним то, что in запросы выводят все значения, которые присутствуют в обоих таблицах, а значит такому запросу будет однозначно соответствовать внутренний inner join. Запросы с not in наоборот выводят все значения, которые не присутствуют в подзапросе, а значит им уже будет соответствовать внешний outer join. Итак, начало положено, попробуем с этим что-нибудь сделать.

Для примера буду использовать тестовую БД world, которую можно взять с официального сайта mysql здесь

В БД world есть таблицы Country (страны) и CountryLanguage (официальные языки). Поставим себе задачу найти все страны, в которых говорят хотя бы на одном языке. Говоря другими словами, мы ищем территории с населением, у которого есть официальные языки. Напишем привычный in подзапрос:

SELECT *
FROM Country
WHERE Code IN (SELECT CountryCode FROM CountryLanguage)

На заметку, этот запрос можно переписать ещё и так:

SELECT *
FROM Country c
WHERE EXISTS (
  SELECT 1 FROM CountryLanguage cl WHERE c.Code = cl.CountryCode
)

Теперь, исходя из предположения выше, перепишем подзапрос на inner join:

SELECT c.Name
FROM Country c 
INNER JOIN CountryLanguage cl ON c.Code = cl.CountryCode

+-------------+ 
| Name        | 
+-------------+ 
| Aruba       | 
| Aruba       | 
| Aruba       | 
| Aruba       | 
| Afghanistan | 
+-------------+ 
5 rows in set (0.00 sec) 

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

SELECT DISTINCT c.*
FROM Country c 
INNER JOIN CountryLanguage cl ON c.Code = cl.CountryCode

Отлично! Подзапрос in успешно переписан на join.

Теперь немного сложнее — перепишем not in на outer join. Цель запроса — все территории, на которых не проживают люди и нет официальных языков. Снова вначале привожу стандартный not in подзапрос:

SELECT Name
FROM Country
WHERE Code NOT IN (SELECT CountryCode FROM CountryLanguage)

И показываю его же для not exists:

SELECT Name
FROM Country c
WHERE NOT EXISTS (
  SELECT 1 FROM CountryLanguage cl WHERE c.Code = cl.CountryCode
)

Как и в первом случае, перепишем на left join:

SELECT c.*
FROM Country c 
LEFT JOIN CountryLanguage cl ON c.Code = cl.CountryCode

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

SELECT c.*
FROM Country c 
LEFT JOIN CountryLanguage cl ON c.Code = cl.CountryCode
WHERE cl.CountryCode IS NULL

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

Сравнение строк в подзапросах

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

SELECT c.Name
FROM City c
WHERE c.ID = (SELECT Capital FROM Country WHERE Name='Finland')
 AND c.CountryCode = (SELECT Code FROM Country WHERE Name='Finland')

Для этих целей существует регламентированный SQL стандартом запрос, отрабатываемый всеми базами данных:

SELECT c.Name
FROM City c
WHERE (c.ID, c.CountryCode) =
 (SELECT Capital, Code
  FROM Country
  WHERE Name='Finland')

Такой запрос называется «конструктором строк» и может быть подчёркнут функцией ROW(). В этом случае мы бы написали:

SELECT c.Name
FROM City c
WHERE ROW(c.ID, c.CountryCode) =
 (SELECT Capital, Code
  FROM Country
  WHERE Name='Finland')

Несмотря на свою привлекательность, конструктор строк имеет ряд ограничений:
1. Подзапрос должен возвращать одну строку, а не несколько
2. Вы не можете использовать операторы сравнения <, > или <>, хотя это ограничение можно обойти специальными словами all, any, in или exists

Стоит обратить внимание, что такую конструкцию можно использовать не только для подзапросов, но и в скалярных выражениях:

SELECT Name, Population
FROM Country
WHERE (Continent, Region) = ('Europe', 'Western Europe')

Правда, на практике, конструктор запросов не очень эффективен для скалярных выражений, поэтому перепишем запрос к нормальному виду:

SELECT Name, Population
FROM Country
WHERE Continent = 'Europe' AND Region = 'Western Europe'

Обновление и удаление данных одновременно из нескольких таблиц.

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

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]

Скорее всего, вы сделаете запрос вида:

UPDATE t1, t2 SET t1.name1 = t2.name2 WHERE t1.id = t2.id;

С другой стороны, никто не мешает сделать запрос, который обновит данные сразу в двух, трёх и более таблицах:

UPDATE t1, t2 SET t1.name1 = 'name1', t2.name2 = 'name2' WHERE t1.id = t2.id;

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

С операцией удаления ещё интереснее обстоят дела. Официальная документация декларирует такой синтаксис:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

Либо

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

Что соответствует запросам вида:

DELETE t1 FROM t1, t2 WHERE t1.id = t2.id;
DELETE FROM t1 USING t1, t2 WHERE t1.id = t2.id;

В этих двух запросах удаление происходит из таблицы t1, а t2 используется для создания условия выборки данных.

И как вы уже догадались, для удаления данных одновременно из двух таблиц делаем так:

DELETE t1, t2 FROM t1, t2 WHERE t1.id = t2.id;
DELETE FROM t1, t2 USING t1, t2 WHERE t1.id = t2.id;

Немного про OLAP. Модификатор WITH ROLLUP

Возможно те, кто сталкивался с такими промышленными БД как Oracle или SQL Server при чтении заголовка вскрикнут: «Ну надо же!», — но, увы, я сразу остужу их пламенные возгласы. С версии MySQL 4.1.1, когда появился модификатор with rollup, эта тема не продвинулась ни на миллиметр, поэтому никаких кубов данных вы не сможете построить встроенными средствами данной БД.

Для тех, кто не в курсе, что означает модификатор with rollup кратко поясню, что он используется для создания отчетов, содержащих подытоги и окончательное итоговое значение. В примерах, буду снова использовать базу world.

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

Суммарное и среднее число проживающих людей на всех географических территориях (регионах):

SELECT Continent, Region, SUM(Population) AS pop, AVG(Population) AS avg_pop
FROM Country
GROUP BY Continent, Region

Суммарное и среднее число проживающих людей на всех континентах:

SELECT Continent, SUM(Population) AS pop, AVG(Population) AS avg_pop
FROM Country
GROUP BY Continent

Суммарное и среднее число проживающих людей во всём мире:

SELECT SUM(Population) AS pop, AVG(Population) AS avg_pop
FROM Country

Вместо выполнения этих запросов и последующего сложного объединения результатов, можно выполнить всего один:

SELECT Continent, Region, SUM(Population) AS pop, AVG(Population) AS avg_pop
FROM Country
GROUP BY Continent, Region
WITH ROLLUP;

+---------------+---------------------------+------------+----------------+
| Continent     | Region                    | pop        | avg_pop        |
+---------------+---------------------------+------------+----------------+
| Asia          | Eastern Asia              | 1507328000 | 188416000.0000 |
| Asia          | Middle East               |  188380700 |  10465594.4444 |
| Asia          | Southeast Asia            |  518541000 |  47140090.9091 |
| Asia          | Southern and Central Asia | 1490776000 | 106484000.0000 |
| Asia          | NULL                      | 3705025700 |  72647562.7451 |
| Europe        | Baltic Countries          |    7561900 |   2520633.3333 |
| Europe        | British Islands           |   63398500 |  31699250.0000 |
...
| Europe        | Eastern Europe            |  307026000 |  30702600.0000 |
| Europe        | Nordic Countries          |   24166400 |   3452342.8571 |
| Europe        | Southern Europe           |  144674200 |   9644946.6667 |
...
| Europe        | Western Europe            |  183247600 |  20360844.4444 |
| Europe        | NULL                      |  730074600 |  15871186.9565 |
| North America | Caribbean                 |   38140000 |   1589166.6667 |
| North America | Central America           |  135221000 |  16902625.0000 |
| North America | North America             |  309632000 |  61926400.0000 |
| North America | NULL                      |  482993000 |  13053864.8649 |
| Africa        | Central Africa            |   95652000 |  10628000.0000 |
...
| Africa        | Eastern Africa            |  246999000 |  12349950.0000 |
| Africa        | Northern Africa           |  173266000 |  24752285.7143 |
| Africa        | Southern Africa           |   46886000 |   9377200.0000 |
...
| Africa        | Western Africa            |  221672000 |  13039529.4118 |
| Africa        | NULL                      |  784475000 |  13525431.0345 |
| Oceania       | Australia and New Zealand |   22753100 |   4550620.0000 |
...
| Oceania       | Melanesia                 |    6472000 |   1294400.0000 |
| Oceania       | Micronesia                |     543000 |     77571.4286 |
| Oceania       | Micronesia/Caribbean      |          0 |         0.0000 |
...
| Oceania       | Polynesia                 |     633050 |     63305.0000 |
| Oceania       | NULL                      |   30401150 |   1085755.3571 |
| Antarctica    | Antarctica                |          0 |         0.0000 |
| Antarctica    | NULL                      |          0 |         0.0000 |
| South America | South America             |  345780000 |  24698571.4286 |
| South America | NULL                      |  345780000 |  24698571.4286 |
| NULL          | NULL                      | 6078749450 |  25434098.1172 |
+---------------+---------------------------+------------+----------------+

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

| South America | NULL                      |  345780000 | 24698571.4286 |

нужно читать как в Южной Америке суммарное население составляет 345780000 человек, а среднее значение 24698571.4286

А вот строка

| NULL          | NULL                      | 6078749450 | 25434098.1172 |

Является окончательным итогом по отношению к численности населения на всём земном шаре.

Положительный эффект модификатора with rollup заключается в том, что проход по записям происходит один раз! Кстати, эта функциональность очень удобна при выводе какой-либо статистики на сайте (программе). Если вас заинтересовала данная функциональность или остались вопросы, то за подробностями прошу в официальную документацию

Опция запуска --i-am-a-dummy

«--i-am-a-dummy» в разговорной форме переводится как – «я — болван». Эта опция является синонимом опции --safe-updates, которая создана для новичков и накладывает ряд ограничений для того, чтобы вашей любимой БД не стало плохо от ваших действий:
1. Запросы update и delete выполняются только при наличии условия where или limit
2. select одной таблицы (без join'ов и подзапросов) возвращает только первые 1000 строк, если явно не указан limit
3. select запросы с join или подзапросом обрабатывает только первые 1 000 000 строк

Для переопределения этих ограничений выполните следующий запрос со своими параметрами:

SET sql_safe_updates=1, sql_select_limit=1000, sql_max_join_size=1000000;

Для просмотра текущих настроек:

SELECT @@sql_safe_updates, @@sql_select_limit, @@sql_max_join_size

Также возможно параметризировать настройки при запуске клиента в шелле

mysql -u user -p -h host --safe-updates --select_limit=500 --max_join_size=10000

Эстетичный комментарий

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

Что я имею ввиду. В MySQL мы пишем так:

-- SELECT 1

(с пробелом перед SELECT), а в других БД:

--SELECT 1

(без управляющего символа).

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

UPDATE account SET credit=credit--1

то первое тире будет трактоваться минусом, а второе -1, но никак не комментарием. Если после двух тире поставить управляющий символ, то только в этом случае мы получим комментарий. Подробнее об этом комментарии здесь