Что такое explain sql
Что такое explain sql
EXPLAIN — показать план выполнения оператора
Синтаксис
Описание
Эта команда выводит план выполнения, генерируемый планировщиком Postgres Pro для заданного оператора. План выполнения показывает, как будут сканироваться таблицы, затрагиваемые оператором — просто последовательно, по индексу и т. д. — а если запрос связывает несколько таблиц, какой алгоритм соединения будет выбран для объединения считанных из них строк.
С параметром ANALYZE оператор будет выполнен на самом деле, а не только запланирован. При этом в вывод добавляются фактические сведения о времени выполнения, включая общее время, затраченное на каждый узел плана (в миллисекундах) и общее число строк, выданных в результате. Это помогает понять, насколько близки к реальности предварительные оценки планировщика.
Важно
Без скобок для этого оператора можно указать только параметры ANALYZE и VERBOSE и только в таком порядке. В PostgreSQL до версии 9.0 поддерживался только синтаксис без скобок, однако в дальнейшем ожидается, что все новые параметры будут восприниматься только в скобках.
Параметры
Выводимая информация
Замечания
Измеряя фактическую стоимость выполнения каждого узла в плане, текущая реализация EXPLAIN ANALYZE привносит накладные расходы профилирования в выполнение запроса. В результате этого, при запуске запроса командой EXPLAIN ANALYZE он может выполняться значительно дольше, чем при обычном выполнении. Объём накладных расходов зависит от природы запроса, а также от используемой платформы. Худшая ситуация наблюдается для узлов плана, которые сами по себе выполняются очень быстро, и в операционных системах, где получение текущего времени относительно длительная операция.
Примеры
План того же запроса, но выведенный в формате JSON:
План того же запроса, но в формате YAML:
Рассмотрение формата XML оставлено в качестве упражнения для читателя.
План того же запроса без вывода оценок стоимости:
Пример плана для запроса с агрегатной функцией:
Пример использования EXPLAIN EXECUTE для отображения плана выполнения подготовленного запроса:
Совместимость
Оператор EXPLAIN отсутствует в стандарте SQL.
Что такое explain sql
EXPLAIN — показать план выполнения оператора
Синтаксис
Описание
Эта команда выводит план выполнения, генерируемый планировщиком PostgreSQL для заданного оператора. План выполнения показывает, как будут сканироваться таблицы, затрагиваемые оператором — просто последовательно, по индексу и т. д. — а если запрос связывает несколько таблиц, какой алгоритм соединения будет выбран для объединения считанных из них строк.
С параметром ANALYZE оператор будет выполнен на самом деле, а не только запланирован. При этом в вывод добавляются фактические сведения о времени выполнения, включая общее время, затраченное на каждый узел плана (в миллисекундах) и общее число строк, выданных в результате. Это помогает понять, насколько близки к реальности предварительные оценки планировщика.
Важно
Без скобок для этого оператора можно указать только параметры ANALYZE и VERBOSE и только в таком порядке. В PostgreSQL до версии 9.0 поддерживался только синтаксис без скобок, однако в дальнейшем ожидается, что все новые параметры будут восприниматься только в скобках.
Параметры
Выводимая информация
Замечания
Измеряя фактическую стоимость выполнения каждого узла в плане, текущая реализация EXPLAIN ANALYZE привносит накладные расходы профилирования в выполнение запроса. В результате этого, при запуске запроса командой EXPLAIN ANALYZE он может выполняться значительно дольше, чем при обычном выполнении. Объём накладных расходов зависит от природы запроса, а также от используемой платформы. Худшая ситуация наблюдается для узлов плана, которые сами по себе выполняются очень быстро, и в операционных системах, где получение текущего времени относительно длительная операция.
Примеры
План того же запроса, но выведенный в формате JSON:
План того же запроса, но в формате YAML:
Рассмотрение формата XML оставлено в качестве упражнения для читателя.
План того же запроса без вывода оценок стоимости:
Пример плана для запроса с агрегатной функцией:
Пример использования EXPLAIN EXECUTE для отображения плана выполнения подготовленного запроса:
Совместимость
Оператор EXPLAIN отсутствует в стандарте SQL.
Используем EXPLAIN для оптимизации запросов к MySql
Во время выполнения запроса, оптимизатор запросов MySQL пытается ускорить этот процесс. Вы можете увидеть это, если добавите ключевое слово EXPLAIN перед запросом. EXPLAIN – один из самых мощных инструментов, находящийся в вашем распоряжении, для анализа процесса выполнения и оптимизации сложных MySQL-запросов, но, к сожалению, многие разработчики используют его редко. В этой статье я расскажу вам о том, какую информацию выводит EXPLAIN и как, используя ее, можно оптимизировать запросы и структуру базы данных.
Что включает в себя вывод EXPLAIN.
Чтобы использовать EXPLAIN, нужно добавить его перед вашим запросом:
EXPLAIN: выборка всех данных из таблицы
Хоть в это и трудно поверить, но в 10 строчках, которые возвращает запрос, хранится много полезной информации. Что же выводит EXPLAIN?
Расширенный вариант EXPLAIN EXTENDED
Результат запроса SHOW WARNINGS
Оптимизация производительности с помощью EXPLAIN.
Давайте сейчас попробуем проанализировать информацию, предоставляемую EXPLAIN, и поймем, как можно оптимизировать неэффективные запросы. В реальном приложении используется множество связанных таблиц, поэтому сложно заранее знать то, как нужно написать запрос, который выполняется быстро.
Анализируем запрос с помощью EXPLAIN
Теперь давайте выполним наш сложный запрос еще раз после добавления индексов. Результат будет следующий:
Результат запроса после добавления индексов
После добавления индексов, количество сканируемых записей снизилось до 4 (1 × 1 × 4 × 1 × 1). Это говорит о том, что для каждой записи с ключом orderNumber из таблицы orderdetails MySQL сможет найти связанные записи во всех таблицах, используя индексы, а не сканируя все таблицы полностью.
EXPLAIN с UNION
Результат EXPLAIN после добавления индексов
Заключение
Оптимизация запросов. Основы EXPLAIN в PostgreSQL
Почему запрос выполняется так долго? Почему не используются индексы?
Наверное, все слышали об EXPLAIN в PostgreSQL. Но не так много тех, кто понимает, как его использовать. Сам длительное время не мог найти доступного для понимания учебника (плохо искал?).
Надеюсь, эта статья поможет желающим разобраться с этим замечательным инструментом.
Это не перевод, а скорее авторская переработка материалов Understanding EXPLAIN от Guillaume Lelarge. Часть информации опущено, так что настоятельно рекомендую ознакомиться с оригиналом.
Не так страшен чёрт
Для оптимизации запросов очень важно понимать логику работы ядра PostgreSQL.
Постараюсь объяснить. На самом деле всё не так сложно. EXPLAIN выводит информацию, необходимую для понимания, что же делает ядро при каждом конкретном запросе. Будем рассматривать вывод команды EXPLAIN, параллельно разбираясь, что же происходит внутри PostgreSQL. Описанное применимо к PostgreSQL 9.2 и выше.
Наши задачи:
Первые шаги
Тренироваться будем на кошках тестовой таблице в миллион строк
Попробуем прочитать данные
QUERY PLAN
— Seq Scan on foo (cost=0.00..18334.00 rows=1000000 width=37)
(1 row)
QUERY PLAN
— Seq Scan on foo (cost=0.00..18334.00 rows=1000000 width=37)
(1 row)
QUERY PLAN
— Seq Scan on foo (cost=0.00..18334.10 rows=1000010 width=37)
(1 row)
Теперь rows отображает правильное количество строк.
Что происходит при выполнении ANALYZE?
Реальные данные
QUERY PLAN
— Seq Scan on foo (cost=0.00..18334.10 rows=1000010 width=37) (actual time=0.012..61.524 rows=1000010 loops=1)
Total runtime: 90.944 ms
(2 rows)
Основы индексирования и возможности EXPLAIN в MySQL
Темой доклада Василия Лукьянчикова является индексирование в MySQL и расширенные возможности EXPLAIN, т.е. нашей задачей будет ответить на вопросы: что мы можем выяснить с помощью EXPLAIN’а, на что следует обращать внимание?
Многие ограничения EXPLAIN’а связаны с оптимизатором, поэтому мы предварительно посмотрим на архитектуру, чтобы понять, откуда следуют ограничения и что, в принципе, с помощью EXPLAIN’а можно сделать.
По индексам мы пройдемся очень кратко, исключительно в плане того, какие нюансы есть в MySQL, в отличие от общей теории.
Доклад, таким образом, состоит из 3х частей:
Василий Лукьянчиков (Станигост)
Архитектура MySQL
Схематически сервер можно представить так:
Первый блок — клиенты, которые обращаются к серверу через функции соответствующего коннектора или C API по протоколу TCP/IP либо UNIX Socket, как правило. Они попадают на блок управления подключениями, где, собственно, происходит авторизация клиента в этот момент, и запуск процесса авторизации и исполнения. Каждый клиент работает в своем независимом потоке. Лишние потоки могут кэшироваться сервером и потом использоваться.
Про кэш запросов нужно отметить, что он представлен одним общим потоком для всех клиентов и в ряде случаев может оказаться узким местом, если у нас многоядерная архитектура и очень простые запросы к базе. База их быстро выполняет, время обращения к кэшу может стать узким местом, поскольку это единственный поток, и все к нему выстраиваются в очередь.
С управления подключениями запрос попадает на основной конвейер, который состоит их трех частей — парсер, оптимизатор и исполнитель. Собственно, эта часть и превращает полученный SQL-запрос в выборку данных. Эта часть общается с интерфейсом хранилищ, каждая по своим задачам:
Здесь нужно отметить ключевую особенность, связанную с MySQL, — это интерфейс подключаемых хранилищ, т.е. вы можете в своей компании разработать свое собственное хранилище (интерфейс стандартный) и подключить его. Таким образом, вы можете сделать, чтобы данные хранились наиболее удобным вам образом с учетом различных нюансов.
Обратной особенностью является то, что оптимизатор очень слабо связан с хранилищами, он не знает и не учитывает отдельные нюансы исполнения части запроса тем или иным хранилищем. Причем, что странно, это так же плохо происходит для основных хранилищ, которые разрабатывают непосредственно разработчики MySQL, и данная ситуация стала улучшаться только в последних версиях. Этот момент нужно учитывать.
Еще тут стоит отметить то, что индексы в MySQL реализованы именно на уровне хранилищ, они не стандартизированы. Поэтому нужно следить за тем, какой тип индекса — полнотекстовый, B-Tree, пространственный и др. — используется тем или иным хранилищем. И самое главное: один и тот же индекс в разных хранилищах — это может быть совершенно разная структура. Например, B-Tree индекс в MyISAM хранит указатель на сами данные, а в InnoDB он хранит указатель на первичный ключ; в MyISAM происходит сжатие префиксных индексов, а в InnoDB этого не происходит, но зато там есть кэширование и данных, и индексов.
Существует масса нюансов, которые нужно учитывать при работе, т.е. какие-то запросы будут быстрее выполняться в одном хранилище, какие-то — в другом, т.к. они по-разному хранят статистику. Например, запрос count (*) в случае MyISAM может выполняться очень быстро без обращения к самим данных, т.к. там хранится именно статистика в метаданных, это, правда, для частных случаев, но, тем не менее, такие нюансы есть.
Сразу скажем про план запроса. Его делает оптимизатор, и это не какой-то исполняемый код, а набор инструкций, который он передают исполнителю. Это некое предположение о том, как запрос будет выполняться. После того, как исполнитель сделает запрос, могут появиться какие-то отличия и, в отличие от PostgreSQL, MySQL не показывает то, что было сделано, т.е., когда мы смотрим EXPLAIN в MySQL, у нас нет ANALYZE. Точнее, оно появилось совсем недавно в версии 10.1 Maria, которая еще beta, и, естественно, пока не используется. Поэтому нужно учитывать, что когда мы смотрим EXPLAIN в MySQL, это некие предположения.
Часто возникает такая ситуация, что у нас один и тот же план, но разная производительность. Тут надо отметить тот момент, что сам оптимизатор в плане EXPLAIN дает очень мало вещей. Например, у нас запросы select (*) из таблицы и select пары полей из таблицы будут иметь одинаковый план, но в одном случае у нас будет выбрано несколько Кб для каждой записи, а в другом — может быть несколько Мб, если у нас записи огромные. Естественно, производительность этих запросов будет различаться на порядки, но план этого никак не покажет. Или же у нас может быть одинаковый план, один запрос, но выполняться на разных машинах он будет по-разному, потому что в одном случае индекс читается из памяти, в другом, если буфер маленький, индекс берется с диска. Опять при одинаковом плане производительность будет различаться. Поэтому в дополнение к EXPLAIN’у нужно на разные вещи смотреть, в первую очередь — на параметры сервера (show status).
Здесь уместно обратиться к прошедшей конференции РИТ++, на которой был доклад Григория Рубцова по ботаническому определителю MySQL — для тех, кто не умеет гипнотизировать сервер и сразу навскидку определять узкое место, там выстроена целая последовательная схема: посмотрели на такой-то параметр — пошли туда. Как в классическом ботаническом определителе, когда велся поиск определения растений, смотрели на количество листьев, на форму и т.д. и так пришли к ответу. Там именно такая структура — куда последовательно смотреть, что делать, чтобы найти узкие места.
Перейдем к индексам в MySQL
Мы не будем рассматривать и перечислять все типы индексов — они более-менее стандартны для каждой базы. Говоря о MySQL, нам нужно отметить следующее: MySQL никак не управляет дублированными индексами, т.е. если мы создаем таблицу так, как представлено, это не означает, что у нас будет создан primary index уникальный на колонку 1. Это означает, что у нас будет создано три одинаковых индекса на одну колонку. Они все три будут занимать место, будут обновляться, будут учитываться оптимизатором, и MySQL сам не выдаст никакого предупреждения, т.е. это нужно смотреть самостоятельно.
Говоря об индексах (в основном мы будем говорить о b-tree, как о наиболее используемых), чтобы не вдаваться в подробности и дерево не рисовать, индекс очень удобно представлять в виде алфавитного указателя. Например, адресная книга — это таблица, алфавитный указатель к ней — это и есть индекс. Применив такую аналогию, мы можем представить, как происходит работа с индексом, за счет чего там быстрее выбираются данные и пр. Но здесь есть некое отличие в самом MySQL, поскольку MySQL всегда идет по индексу, он использует индекс только слева направо последовательно, без фокусов, это может вызвать вопросы.
Например, если нам нужно в алфавитном указателе найти какие-то имена. «Руками» мы будем искать следующим образом: посмотрим первую фамилию, найдем нужные имена, пролистнем до следующей фамилии. Это не ограничение b-tree дерева, это ограничение реализации b-tree дерева непосредственно в MySQL. Другие базы так умеют делать — использовать не первую колонку, например, в случае WHERE B=3 индекс в MySQL использоваться, вообще, не будет. Только в частном случае, если мы попросим минимум и максимум от этой колонки, но это, скорее, исключение.
На слайде выше представлены различные варианты, и стоит отметить, что индекс обрывается на первом же неравенстве, т.е. он используется последовательно слева направо до первого неравенства. После неравенства дальнейшая часть индекса уже не используется. Т.е. в условии А — константа, В — диапазон, будут использованы первые две части. Во втором случае будет использован целиком индекс. В третьем случае индекс тоже будет использован целиком, т.к. порядок констант в условии не имеет значения, и сервер может их переставить местами. В случае В=3 индекс, вообще, использован не будет. В случае константы и неравенства будет использован индекс только на первые две части, на третью часть индекс уже не сможет использоваться. В случае А — константа и сортировка по последнему, будет только на первую часть использован индекс, потому что пропускается вторая часть, предпоследний индекс будет целиком использован. И в последнем варианте, опять же, индекс будет использован только на первую часть, потому что для сортировки индекс может использоваться только лишь, когда в одном направлении идет.
Это связано с тем, что составной индекс в MySQL — это, по сути, обычный b-tree индекс над конкатенацией входящих столбцов, соответственно, он может двигаться либо по возрастанию всего кортежа, либо по его убыванию. Поэтому сказать: «двигаемся по возрастанию В и по уменьшению С» по индексу мы не сможем. Это ограничение, характерное именно для MySQL.
Здесь есть такой нюанс. Например, A in (0,1) и А between 0 and 1 — это эквивалентные формы, это диапазон и там, и там, но в случае, когда это A in (0,1) — список, он понимает, что это не диапазон, а заменяет на множественное условие равенства. В этом случае он будет использовать индекс. Это еще один нюанс MySQL, т.е. нужно смотреть, как писать — либо списком, либо ставить <>. Он это различает.
Пара слов про избыточный синтаксис. Если у нас есть index(A) и index(A,B), то index(A) будет лишним, потому что в случае index(A,B) часть А может использоваться в нем. Поэтому нам нужно следить, чтобы избыточных индексов не было и самостоятельно их удалять. Понятно, это относится и к случаю, когда оба индекса b-tree, но, если, например, index(A) — это full-text, то, естественно, он может быть необходим.
Вернемся к нюансу на списке. Мы можем делать здесь более широкий индекс уникальным. Например, если мы сделаем index(A,B), то просто условие В использовать не будем, но мы можем в приложении сделать так, чтобы оно самостоятельно подставляло пропущенное условие, если там небольшой возможный вариант списка. Но с этой рекомендацией нужно быть очень осторожными, т.к., несмотря на то, что при наличии списка дальнейшее использование индекса не отбрасывается, он не может быть использован для сортировки, т.е. только на равенство. Поэтому, если у нас есть запросы на сортировку, то нам придется запрос перестраивать через union all, чтобы не было списков, дабы использовать сортировку. Естественно, это не всегда возможно и не всегда удобно. Если расширение индекса нам, например, позволит сделать индекс покрывающим (имеется в виду, что все поля, которые выбираются и используются в запросе, присутствуют в индексе), тогда сервер понимает, что лезть в таблицу за данными ему совсем не обязательно и он целиком обращается к индексу для формирования результата. Т.к. индекс более упорядочен, компактен и хранится чаще всего в памяти кэшированной, это более удобно. Поэтому мы при составлении индекса смотрим всегда, можем ли мы как-то подобрать покрывающий индекс для нашего запроса.
Далее рассмотрим случаи, когда индексы не используются.
Здесь есть общие нюансы с другими базами, например, когда индекс является частью выражения, как и в PostgreSQL, он не сможет его преобразовать, поэтому, если у нас в запросе id + 1 = 3, индекс на id использован не будет. Мы должны сами переносить. Если индекс является частью какого-то выражения, мы должны смотреть, можем ли мы его преобразовать так, чтобы индекс вынести в левую часть в явном виде.
Аналогично, за счет того, что он не производит преобразований (это не только математические, это могут быть несоответствие кодировок, преобразование типов), индекс тоже не будет использован. Индекс не используется, когда пропускается первая часть, когда идет поиск по суффиксу. Ну и, естественно, индекс не будет использован, когда идет сравнение с полями сходной таблицы, потому что в этом случае ему сначала будет нужно прочитать запись из таблицы, чтобы сравнить.
До этого я упоминал покрывающие индексы, и чем они хороши. Вернемся к слайду с архитектурой:
Происходит так: исполнитель запрашивает данные (условие WHERE) у хранилища, соответственно, если у нас есть условие WHERE на несколько позиций, то они все могут быть обработаны внутри самого хранилища. Это оптимальный вариант. Может быть вариант, когда часть условий будет обработано на уровне хранилищ по индексу, строки, переданные на уровень вверх, сервер будет применять, а дальнейшие условия отбрасывать. Понятно, это будет медленнее, т.к. будет идти передача самих записей из хранилища в исполнитель. Поэтому покрывающие индексы, если они у нас применяются только для тех полей, которые в запросе, выгоднее, т.к. мы выбираем уже меньше строк.
Вот, к примеру, подобная оптимизация, называемая index condition pushdown:
Имеется в виду следующее. У нас есть индекс на три поля — А, В, С. В таких условиях мы можем использовать только часть первого. Казалось бы, мы можем в самом хранилище проверить индексы, но раньше (до версии MySQL 5.6, MariaDB 5.3) сервер этого не делал, поэтому нужно внимательно смотреть на конкретные релизы — что умеет делать сервер. В новых версиях сервер производит поиск по первой части индекса, только по колонке А, выбирает данные и, прежде чем передавать записи исполнителю, он проверяет условие на вторую и третью части и смотрит, нужно ли выбирать целиком записи или нет. Это естественно уменьшает количество тех записей, которые нужно считывать с диска.
Особенность ключей в InnoDB — это то, что вторичные ключи ссылаются на первичный ключ, поэтому фактически вторичный ключ в InnoDB представляет собой вторичный ключ + указатель на первичный ключ.
Такой длинный индекс имеет невидимый «хвост». Невидимый в том смысле, что раньше оптимизатор его в своих действиях не учитывал.
Вот, у нас есть primary (A, B) составной, соответственно, вторичный ключ — это будет составной ключ на (C, A, B) и по нему уже можно проводить поиск.
Таким образом, когда вы работаете с таблицами InnoDB и делаете индексы, вы всегда должны учитывать, что длинный первичный ключ — это может быть либо хорошо, либо плохо в зависимости от того, какие у вас запросы, потому что он будет добавляться ко всем индексам.
Здесь следующий нюанс — эта оптимизация будет учитываться только для фильтрации строк. У нас в отсортированном виде хранятся только значения вторичного ключа, а указатели на первичный ключ не отсортированы, поэтому такой длинный невидимый «хвост» сервер сможет использовать только для условий равенства фильтрации строк. Это доступно в MariaDB 5.5, MySQL 5.6.
Ключевые ограничения оптимизатора — это то, что он исторически использует очень мало статистики. Он запрашивает у хранилищ данные. Вот здесь указано подробнее, что учитывает сервер:
Он может учитывать результаты вводимых команд, поля, количество строк, т.е. довольно-таки мало данных. Особенность в том, что изначально статистика вычисляется следующим образом: мы запускаем сервер или делаем какую-то команду типа ALTER, у нас статистика обновляется, потом таблица живет некоторое время, какой-то процесс вдруг меняется, потом статистика вновь обновляется. Т.е. бывает, что сама статистика не соответствует распределению данных.
Опять же, каждое хранилище выбирает статистику по-своему — где-то больше, где-то меньше. В последних версиях реализуется идея независимой статистики, т.е. статистики на уровне сервера — выделяются служебные таблицы, в которых единым образом уже независимо от механизмов хранения собирается статистика для всех таблиц, причем, если в Percona 5.6. это сделано только для индексов в InnoDB, то в Maria 10 пошли дальше и собирают ее даже для неиндексированных столбцов, за счет чего оптимизатор может выбирать более оптимальные планы выполнения, поскольку лучше понимает распределение данных.
Оптимизатор не учитывает особенности хранилищ — когда мы передаем запрос в хранилище, то, понятно, что в InnoDB поиск по вторичному ключу будет идти дольше, потому что мы пройдемся по вторичному ключу, получим указатели на первичный ключ, возьмем эти указатели, пойдем за данными, т.е. у нас будет двойной проход, а в MyISAM, например, будут сразу указатели непосредственно на сами строки. Вот подобных нюансов относительного быстродействия тех или иных частей (у нас запрос может одновременно обращаться к разным хранилищам) оптимизатор и не учитывает. Он также не учитывает очень много вопросов, связанных с оборудованием, т.е. какие данные у нас закэшированы, какие буферы.
Метрика. Понятно, что оптимизатор выбирает самый дешевый план, но дешевый план с точки зрения оптимизатора — это план с наименьшей стоимостью, а вопрос стоимости — это некоторая условность, которая может не совпадать с нашими представлениями о ней. Опять же, сложность выбора — это когда много таблиц и нужно их по-разному перемещать и смотреть.
Еще он использует правила, т.е. если он, например, понимает, что нужно использовать full text index, то он использует его, даже несмотря на то, что у нас может быть условие на первичный ключ, которое однозначно выдаст одну колонку.
Есть еще такой нюанс — эти две записи с нашей точки зрения эквивалентны: