in Профессиональное

MySQL – анализ сложных запросов

Перевод статьи про анализ сложных MySQL запросов:

 

Для данной статьи возьмем запрос из статьи про ORDER BY RAND, так он достаточно интересен для рассмотрения различных аспектов запросов. Он содержит:

  • Подзапросы;
  • Объединения;
  • Особые случаи;
  • JOIN’ы
  • Сортировку ORDER BY + LIMIT

Если вы хотите понять как запрос был создан, посмотрите оригинальную статью ORDER BY RAND.

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

Рассмотрим результаты EXPLAIN запроса:

> EXPLAIN EXTENDED
(SELECT r1.name FROM random AS r1
   JOIN (SELECT (RAND() *
                (SELECT MAX(id) FROM random)) AS id
        ) AS r2
  WHERE r1.id >= r2.id
  ORDER BY r1.id ASC
  LIMIT 1)
UNION ALL
(SELECT r1.name FROM random AS r1
   JOIN (SELECT (RAND() *
                (SELECT MAX(id) FROM random)) AS id
        ) AS r2
  WHERE r1.id >= r2.id
  ORDER BY r1.id ASC
  LIMIT 1);

И собственно сами результаты:

+----+--------------+------------+--------+---------------+---------+---------+------+---------+----------+------------------------------+
| id | select_type  | table      | type   | possible_keys | key     | key_len | ref  | rows    | filtered | Extra                        |
+----+--------------+------------+--------+---------------+---------+---------+------+---------+----------+------------------------------+
|  1 | PRIMARY      | <derived2> | system | NULL          | NULL    | NULL    | NULL |       1 |   100.00 | Using filesort               |
|  1 | PRIMARY      | r1         | ALL    | PRIMARY       | NULL    | NULL    | NULL | 1000000 |   100.00 | Using where                  |
|  2 | DERIVED      | NULL       | NULL   | NULL          | NULL    | NULL    | NULL |    NULL |     NULL | No tables used               |
|  3 | SUBQUERY     | NULL       | NULL   | NULL          | NULL    | NULL    | NULL |    NULL |     NULL | Select tables optimized away |
|  4 | UNION        | <derived5> | system | NULL          | NULL    | NULL    | NULL |       1 |   100.00 |                              |
|  4 | UNION        | r1         | range  | PRIMARY       | PRIMARY | 4       | NULL |  153726 |   100.00 | Using where                  |
|  5 | DERIVED      | NULL       | NULL   | NULL          | NULL    | NULL    | NULL |    NULL |     NULL | No tables used               |
|  6 | SUBQUERY     | NULL       | NULL   | NULL          | NULL    | NULL    | NULL |    NULL |     NULL | Select tables optimized away |
| NULL | UNION RESULT | <union1,4> | ALL  | NULL          | NULL    | NULL    | NULL |    NULL |     NULL |                              |
+----+--------------+------------+--------+---------------+---------+---------+------+---------+----------+------------------------------+

Запрос состоит из 2х SELECT’ов и UNION’а. Результат UNION получается за счет объединения id 1 и 4 (union1,4), что можно увидеть в последней строке.

Id 1 это JOIN между временной таблицей derived2 и random. Поскольку id остается неизменным, запрос как правило является частью JOIN. deriverd2 это результат id 2 (DERIVED) с его подзапросом id3.

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

  • MAX(id) может быть оптимизирован в данном случае последним значением индекса. Это единичный поиск, который не требует ни сортировки, ни группировки… (id 3 and 6)
  • Мы вынесли рассчет RAND() * MAX(id) в подзапрос, чтобы быть уверенными, что оптимизация MAX() действительно имеет место (вычисляется один раз // прим. пер.) (id 2 and 5)
  • Мы использовали ORDER BY + LIMIT чтобы оптимайзер выполнил INDEX READ и остановился после обнаружения одной строки. (id 1 and 4)

стоимость выборки

Мы можем доказать нашу теорию при помощи проверки SHOW STATUS.

> FLUSH STATUS;
> SELECT ...;
> SHOW SESSION STATUS;

Команда FLUSH STATUS сбрасывает значения счетчиков для текущей сессии, команда SHOW SESSION STATUS отображает счетчики:

| Select_full_join                  | 0         |
| Select_full_range_join            | 0         |
| Select_range                      | 2         |
| Select_range_check                | 0         |
| Select_scan                       | 1         |

Мы имеем 2 range scans (ORDER BY + LIMIT), по одному для каждой части нашего UNION и 1 table scan для чтения результатов UNION и отправки их на клиент.

Для того чтобы доказать что мы не используем дорогую сортировку – проверим счетчики Sort-*:

| Sort_merge_passes                 | 0         |
| Sort_range                        | 0         |
| Sort_rows                         | 0         |
| Sort_scan                         | 0         |

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

## наши временные таблицы:
| Created_tmp_tables                | 3         | 2 вторичные таблицы + UNION RESULT
## тут мы пишем только во временные таблицы в памяти
| Handler_write                     | 4         | (1 строка + 1 строка) для вторичных таблиц + 2 строки для результата UNION
## MAX(id) выполняет Index-lookup для _last_ id
| Handler_read_first                | 2         |
## ... и мы ищем одну строку для random-id внутри JOIN
| Handler_read_key                  | 2         |
| Handler_read_next                 | 0         |
| Handler_read_prev                 | 0         |
| Handler_read_rnd                  | 0         |
## все чтения из временных таблиц не индексированы
| Handler_read_rnd_next             | 5         |
## виртуальная стоимость запроса ... "foobars"
| Last_query_cost                   | 10.499000 |

а что насчет “старого доброго” ORDER BY RAND?

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

> FLUSH STATUS;
> SELECT name FROM random ORDER BY RAND() LIMIT 1;
> SHOW SESSION STATUS;
> EXPLAIN SELECT name FROM random ORDER BY RAND() LIMIT 1;
+----+-------------+--------+------+---------------+------+---------+------+---------+---------------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | Extra                           |
+----+-------------+--------+------+---------------+------+---------+------+---------+---------------------------------+
|  1 | SIMPLE      | random | ALL  | NULL          | NULL | NULL    | NULL | 1000000 | Using temporary; Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+---------+---------------------------------+

В общем дело было так: ALL rows – все строки – сортируются во временной таблице и озвращается лишь одна!

## цена запроса "несколько" выше :)
| Last_query_cost                   | 210744.186500 |
## у нас используется только одна временная таблица, с намного бОльшим числом строк
| Created_tmp_tables                | 1             |
| Handler_write                     | 1349207       |
## Выполняется один ГИГАНТСКИЙ table-scan для заполнения временной таблицы
| Select_scan                       | 1             |
| Handler_read_rnd_next             | 2349209       |
## и тяжелейшая сортировка
| Sort_merge_passes                 | 19            |
| Sort_range                        | 0             |
| Sort_rows                         | 2             |
| Sort_scan                         | 1             |

Источник: http://jan.kneschke.de/projects/mysql/analyzing-complex-queries/

Перевод как водится мой. Также хочу отметить что не смотря на небольшой объем текста перевод выдался тяжелым с точки зрения понимания. Если видите что я где-то накосячил или не прав – отпишите в комметариях или в личку. 10nx )

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

Write a Comment

Comment

ERROR: si-captcha.php plugin: GD image support not detected in PHP!

Contact your web host and ask them to enable GD image support for PHP.

ERROR: si-captcha.php plugin: imagepng function not detected in PHP!

Contact your web host and ask them to enable imagepng for PHP.

  1. Одного не пойму – если нам все равно нужны лишь случайные строки, зачем нам ORDER BY r1.id ASC ?
    Разве не достаточно WHERE r1.id >= r2.id LIMIT 1 – таким образом и влияние дыр возможно чуть меньше будет?