Перевод статьи про анализ сложных 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 )
Да, лично для меня статья оказалось полезна в плане использования счетчиков и интерпретации их значений. Надеюсь и вам тоже.
Одного не пойму – если нам все равно нужны лишь случайные строки, зачем нам ORDER BY r1.id ASC ?
Разве не достаточно WHERE r1.id >= r2.id LIMIT 1 – таким образом и влияние дыр возможно чуть меньше будет?
Да вроде и незачем. Во всяком случае в этих синтетических примерах.