PHP разработчик
0,0
рейтинг
7 октября 2013 в 22:25

Разработка → Tips & tricks for MySQL Developers. Работа с SQL tutorial


Эта статья задумана мной как сборник некоторых интересных моментов по использованию и оптимизации 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 --i-am-a-dummy --select_limit=500 --max_join_size=10000


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


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

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

-- SELECT 1


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

--SELECT 1


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

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

UPDATE account SET credit=credit--1


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

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

UDP:
В комментариях есть сомнения в целесообразности переписывания запросов с in на join. Ниже, мой небольшой бенчмарк.

Исходные данные:

mysql> SELECT @@version;
+-------------------------+
| @@version               |
+-------------------------+
| 5.5.32-0ubuntu0.12.04.1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE t11\G
*************************** 1. row ***************************
       Table: t11
Create Table: CREATE TABLE `t11` (
  `id` int(10) unsigned NOT NULL DEFAULT '0',
  `t11` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `t11` (`t11`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM t11;
+----------+
| COUNT(*) |
+----------+
|   977360 |
+----------+
1 row in set (0.03 sec)

mysql> SHOW CREATE TABLE t22\G
*************************** 1. row ***************************
       Table: t22
Create Table: CREATE TABLE `t22` (
  `id` int(10) unsigned NOT NULL DEFAULT '0',
  `t22` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `t22` (`t22`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM t22;
+----------+
| COUNT(*) |
+----------+
|   962078 |
+----------+
1 row in set (0.04 sec)


JOIN vs IN vs EXISTS

При работе с индексом JOIN (7.84 сек) сильно проигрывает по сравнению с IN (1.74 сек) и EXISTS (2.44 сек).
Ниже пример, когда колонки t11 и t22 без индекса:
mysql> SELECT SQL_NO_CACHE *
    -> FROM t11
    -> WHERE t11 IN (SELECT t22 FROM t22)
    -> LIMIT 1000;
+------+-------+
| id   | t11   |
+------+-------+
|    2 | 5718  |
|   ........   |
| 1001 | 54192 |
+------+-------+
1000 rows in set (13.59 sec)

mysql> SELECT SQL_NO_CACHE *
    -> FROM t11 c
    -> WHERE EXISTS (
    ->   SELECT 1 FROM t22 cl WHERE c.t11 = cl.t22
    -> )
    -> LIMIT 1000;
+------+-------+
| id   | t11   |
+------+-------+
|    2 | 5718  |
|   ........   |
| 1001 | 54192 |
+------+-------+
1000 rows in set (13.94 sec)

mysql> SELECT DISTINCT SQL_NO_CACHE c.*
    -> FROM t11 c
    -> INNER JOIN t22 cl ON c.t11 = cl.t22
    -> LIMIT 1000;
+-------+-------+
| id    | t11   |
+-------+-------+
|    25 | 74376 |
|   .........   |
| 34359 | 62666 |
+-------+-------+
1000 rows in set (3.36 sec)

Поставил limit, чтобы долго не ждать ответ. На результат он не влияет.

OUTER JOIN vs NOT IN vs NOT EXISTS

Без индекса эти запросы в MySQL 5.5 отрабатываются примерно за одинаковое время.
Ниже примеры с использованием индекса:
mysql> SELECT SQL_NO_CACHE *
    -> FROM t11
    -> WHERE t11 NOT IN (SELECT t22 FROM t22)
    -> LIMIT 1000;
+--------+---------------------+
| id     | t11                 |
+--------+---------------------+
|      1 | 0.23670763544431667 |
|   ........................   |
| 891610 | 97811               |
+--------+---------------------+
91 rows in set (1.36 sec)

mysql> SELECT SQL_NO_CACHE *
    -> FROM t11 c
    -> WHERE NOT EXISTS (
    ->   SELECT 1 FROM t22 cl WHERE c.t11 = cl.t22
    -> )
    -> LIMIT 1000;
+--------+---------------------+
| id     | t11                 |
+--------+---------------------+
|      1 | 0.23670763544431667 |
|   ........................   |
| 891610 | 97811               |
+--------+---------------------+
91 rows in set (1.87 sec)

mysql> SELECT SQL_NO_CACHE c.*
    -> FROM t11 c 
    -> LEFT JOIN t22 cl ON c.t11 = cl.t22
    -> WHERE cl.t22 IS NULL
    -> LIMIT 1000;

+--------+---------------------+
| id     | t11                 |
+--------+---------------------+
|      1 | 0.23670763544431667 |
|   ........................   |
| 891610 | 97811               |
+--------+---------------------+
91 rows in set (1.27 sec)


Как итог — результат зависит от версии БД и исходных данных!
Афанасьев Юрий @Oriand
карма
35,2
рейтинг 0,0
PHP разработчик
Реклама помогает поддерживать и развивать наши сервисы

Подробнее
Спецпроект

Самое читаемое Разработка

Комментарии (28)

  • +4
    Mysql оптимизатор запросов почти всегда переписывал подзапросы внутри IN на JOIN-ы,
    так-что не обязательно делать это за него.

    Если интересно, переписанный запрос даже можно увидеть:
    EXPLAIN EXTENDED ваш_запрос;
    SHOW WARNINGS;
    dev.mysql.com/doc/refman/5.5/en/subquery-optimization-with-exists.html

    Что важно, такое поведение оптимизатора частенько приводило к тормозам, поэтому в версии Mysql 5.6 специально появились принудительная материализация подзапроса для борьбы с такой вредной оптимизацией:
    dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html
    • 0
      Да, MySQL оптимизатор переписывает запросы, но на это он тратит немного ресурсов и всегда есть шанс, что его запрос не будет на 100% оптимальным. Так что если есть возможность помочь оптимизатору, то почему бы этого не сделать?
    • 0
      Несмотря на то, что MySQL пытается улучшить запрос, у него это не очень хорошо получается. Результаты своего эксперимента описал в этом комментарии

      habrahabr.ru/post/196692/#comment_6825020
  • +25
    Немного релевантно первому пункту — очень полезная картинка:
    • 0
      Вижу эту картинку на многих статьях к mysql. Ну и правильно!
      • +5
        Вот только full outer join не поддерживается в mysql. Но его можно заменить на пару left join с union.

        А еще в документации рекомендуется использовать left join нежели right join для большей портабельности кода.
        • +1
          а кто-то пользуется right join?
        • 0
          Вообще RIGHT JOIN оптимизатор преобразует перед выполнением к LEFT JOIN, поэтому лучше и писать сразу LEFT JOIN
    • +1
      А ещё вот такая есть: image
  • +3
    Вопрос по первому примеру. Вы сравнивали производительность запросов с EXISTS и INNER JOIN? Проверять лень, но тут, имхо, сравнение будет в пользу EXISTS, ибо в запросе с INNER JOIN есть DISTINCT, для которого может понадобиться временная таблица.
    • 0
      Да, делал эксперимент из двух таблиц t11 и t22, имеющие общую структуру:

      CREATE TABLE `t11` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `t11` varchar(45) NOT NULL,
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=202981 DEFAULT CHARSET=latin1
      


      Обе таблицы имеют примерно 200000 непоследовательных (хаотичных) записей.

      В итоге:
      Подзапрос и EXISTS отрабатывал за 13.7 секунды
      JOIN за 3.2 секунды
  • +1
    «Tips & tricks for MySQL Developers», если уж быть совсем точным.
    • +1
      Спасибо за идею! Изменил.
      • 0
        Спасибо, что учли :)
      • 0
        Кстати, было бы классно, если бы что-то подобное и для MySQL админов опубликовали. Достаточно мало cheetsheets и howto встречалось мне.
  • 0
    Спасибо. А кто-нибудь знает о еще подобнымх познавательных статьях о SQL?
    • +1
      Могу порекомендовать мою предыдущую статью по MySQL. В ней тоже много интересных нюансов habrahabr.ru/post/166411/
      • 0
        Благодарю!
      • +2
        Если вас когда-то посетит мысль писать ли еще на эту тему, сомнения прочь — пишите!
  • 0
    Для этих целей существует регламентированный SQL стандартом запрос, отрабатываемый всеми базами данных


    А не подскажите синтаксис для SQLITE?

    Или может не везде таки работает?
    • –1
      Не могу точно ответить, т.к. под руками нет SQLite, но, в самом плохом случае, никто не мешает сделать так:

      SELECT * FROM table1 WHERE (col1 || col2) = (SELECT col3 || col4 FROM table2)
      
      • 0
        Неправильность результата (например, col1=col4='a', col2=col3='') и невозможность использования индексов мешают.
    • 0
      В MSSQL такая штука тоже работать не будет
      • 0
        А вот тут ты не прав. Когда работал с Microsoft SQL Server, то пару раз использовал такой синтаксис и тогда узнал о нём впервые.
        • 0
          Увы, не работает (MSSQL2012). В документации тоже ничего про это не упоминается msdn.microsoft.com/en-us/library/ms177682(v=sql.110).aspx
          • 0
            Давно не работал с SQL Server, сейчас даже попробовать не на чем. Порывшись в интернете понял, что мои знания из Oracle, который изучал некоторое время назад.
            Признаю свою ошибку и правлю фразу. Всем спасибо за бдительность :-)
  • 0
    Причём, мы знаем, что в MySQL join запросы отрабатывают быстрее за счёт эффективного построения плана выполнения запроса оптимизатором


    Это чаще всего так, но не всегда. Особенно в последних версиях. Поэтому стоит добавить ещё один пункт: тестируйте!
  • 0
    Переписывать подзапросы в join-ы это из серии вредных советов. Или надо явно говорить, для какой версии это применимо.

    Начиная с 5.6 (а в MariaDB с 5.3) оптимизатор знает кучу способов выполнить IN подзапрос. И только один из них — переписать в форму join-а. А другие — материализация, semi-join и еще более хитрые трюки. Оптимизатор выбирает самый из них быстрый, оценивая их «стоимости». Так что перетисывать IN-подзапрос в join уже нельзя, так как это сильно ограничивает возможности оптимизатора по выбору оптимального плана.

    Хотя… в последней версии MariaDB оптимизатор уже умеет переписывать некоторые EXISTS подзапросы в IN. Может когда-нибудь он и join-ы научится в подзапросы переписывать. Тогда будет опять можно.

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