Перейти к содержимому 
С помощью трассировки оптимизатора в MariaDB я шаг за шагом понимаю, почему оптимизатор выбирает тот или иной план и какие варианты он отбрасывает. Эта трассировка в формате JSON показывает мне Решения о затратах, порядке соединений и фильтрах, чтобы я мог целенаправленно настраивать SQL-запросы.
Центральные пункты
- Прозрачность: В отчете на основе JSON объясняются переработки, затраты и отклоненные планы.
- Фокус: join_preparation и join_optimization дают наиболее важную информацию.
- Система управления: Сессионные переменные позволяют сократить накладные расходы и объем памяти.
- Рабочий процесс: EXPLAIN/ANALYZE — для плана, Trace — для выяснения причин.
- Практические преимущества: Грамотно настраивать индексы, статистику и порядок соединений.
Что такое трассировка оптимизатора MariaDB?
Начиная с версии 10.4, MariaDB поддерживает Оптимизатор Trace, который документирует в формате JSON каждый крупный этап оптимизации оператора SELECT, UPDATE или DELETE. В нём я вижу, как движок расширяет запросы, нормализует условия и, в конечном итоге, определяет порядок соединений вместе с обращениями к индексам. Этот обзор значительно глубже, чем EXPLAIN, который в основном показывает конечный план, и раскрывает отброшенные альтернативы с обоснованием. Трассировка хранится в памяти для каждого соединения и доступна через information_schema.OPTIMIZER_TRACE готово. Таким образом, я получаю полное, машиночитаемое описание внутренних Шаги, которые привели к составлению плана реализации.
Включение и считывание трассировки оптимизатора
Я включаю эту функцию целенаправленно для каждого сеанса, чтобы иметь возможность проводить диагностику без глобальной нагрузки и полностью контролировать Память есть. Обычно я ставлю SET SESSION optimizer_trace = 'enabled=on'; и если потребуется SET SESSION optimizer_trace_max_mem_size = 1048576; или выше, если трассировка становится объемной. Затем я выполняю подозрительный запрос и считываю трассировку с помощью SELECT * FROM information_schema.OPTIMIZER_TRACE LIMIT 1\G;. Важно: таблица сохраняет только последний запрос активного соединения, и я обращаю внимание на такие поля, как MISSING_BYTES_BEYOND_MAX_MEM_SIZE или INSUFFICIENT_PRIVILEGES для получения диагностических указаний. Такой подход позволяет сохранить оптимизированную производственную среду и упрощает анализ точный.
| Переменная/поле | Назначение | Пример значения |
|---|---|---|
optimizer_trace | Включает трассировку для каждого сеанса | 'enabled=on' |
optimizer_trace_max_mem_size | Максимальный объем памяти на одну трассировку | 1048576 (1 МБ) |
OPTIMIZER_TRACE.QUERY | Исходный SQL-запрос | SELECT ... |
OPTIMIZER_TRACE.TRACE | JSON-документ оптимизации | Текст JSON |
MISSING_BYTES_BEYOND_MAX_MEM_SIZE | Обрезанные байты при слишком большом размере трассировки | 0 или количество |
INSUFFICIENT_PRIVILEGES | Достаточно ли прав на чтение? | 0 или 1 |
Структура JSON: join_preparation и join_optimization
Структура JSON состоит из следующих блоков: подготовка к соединению и оптимизация соединений, которые я просматриваю в первую очередь, поскольку они являются наиболее важными Примечания поставлять. В разделе подготовка к соединению я распознаю расширенный запрос (расширенный_запрос) и посмотрю, преобразовал ли движок условия или прогнозы и если да, то как. Второй блок оптимизация соединений регистрирует оценки количества строк, рассматриваемые планы, выбранный порядок соединений и добавление выборочных частей WHERE к таблицам. Особенно полезны поддеревья rows_estimation, рассмотренные_планы_выполнения и привязка условий к таблицам, поскольку они непосредственно ссылаются на допущения о затратах и настройки фильтров. Благодаря этому я быстро выявляю, где имеются ошибочные оценки или неблагоприятные Индексы привести к неоптимальным планам.
Сравнение с помощью EXPLAIN и ANALYZE
Для полной оценки я использую в сочетании команды EXPLAIN, ANALYZE и След в четком порядке. Сначала я использую ПОЯСНИТЬ или EXPLAIN FORMAT=JSON, чтобы просмотреть выбранный план и ключевые пути. После этого я устанавливаю EXPLAIN ANALYZE , чтобы получить реальные данные о времени выполнения и счетные показатели, такие как количество циклов и отфильтрованных строк. Если остаются неясные моменты, я включаю трассировку оптимизатора и проверяю, какие варианты оптимизатор рассмотрел и отклонил. Краткое введение в интерпретацию результатов я нахожу в этой статье о Понимание команд EXPLAIN и ANALYZE, к которому я при необходимости обращаюсь в качестве дополнительного источника.
Анализ решений по плану: затраты, кардинальности, фильтры
Логика принятия решений основана на кардинальностях, моделях затрат и размещении Фильтр в соответствии с планом. В трассировке я вижу для каждого рассматриваемого порядка соединений, какие наборы строк ожидает движок и как он на их основе рассчитывает общие затраты. Я проверяю, не приводят ли устаревшие статистические данные или неблагоприятные корреляции к тому, что сканирование диапазонов недооценивается, а предпочтение отдается полному сканированию. Кроме того, я проверяю, присоединяет ли движок условия WHERE к наиболее селективной таблице достаточно рано, чтобы сократить количество дорогостоящих этапов соединения. Таким образом, я могу сделать обоснованные выводы о том, почему был выбран тот или иной план, и как его можно Индексы, перенаправления или ведение статистики.
Практика: отслеживание простого запроса к фильтру
На сайте SELECT * FROM t1 WHERE a < 10 я проверяю в разделе подготовка к соединению, расширил ли движок проекцию и, возможно, объединил ли условия, что дало мне первое Индикаторы поставляет. После этого я вижу в блоке rows_estimation, сколько строк движок использует для Range-Scan на a по сравнению с полным сканированием таблицы. Если обнаруживаются нереалистичные значения, я часто расцениваю это как признак устаревших статистик или отсутствующих гистограмм. В разделе рассмотренные_планы_выполнения Затем я вижу, действительно ли доступ к индексу оказался более экономичным, чем полное сканирование. В заключение показывается привязка условий к таблицам, выполняется ли условие отбора для a осуществляется раньше запланированного срока, что значительно сокращает время выполнения снижает.
Функции JSON: целенаправленное извлечение фрагментов
Поскольку трассировка представлена в формате JSON, я целенаправленно фильтрую поддеревья с помощью JSON_EXTRACT и создаю небольшие отчеты по повторяющимся Образец. Например, я просто просматриваю список рассматриваемых планов, чтобы проверить, не приводят ли определённые последовательности соединений к систематическим сбоям. Кроме того, я извлекаю поля затрат из лучших кандидатов и сравниваю их с данными ANALYZE, чтобы выявить ошибочные допущения. С помощью простых представлений или хранимых процедур я автоматизирую эти проверки для своих диагностических сеансов. Таким образом, я создаю для себя простой Мониторинг для принятия решений оптимизатором без включения постоянного трассирования.
Типичные сценарии применения и преимущества
Я использую трассировку, если EXPLAIN показывает неожиданное полное сканирование и мне нужно выяснить причину отказа в выполнении Индекс хочу узнать. Кроме того, при работе со многими таблицами трассировка объясняет мне причины выбранного порядка соединений, что помогает мне найти альтернативные планы. При смене версии я сохраняю трассировки до и после обновления, чтобы оценить изменения в поведении оптимизатора. В стратегических вопросах настройки этот обзор помогает мне внутренние механизмы оптимизатора, который я связываю с результатами трассировки. Таким образом, я систематически принимаю решение, следует ли внести изменения в индексы, статистику или формулировку запросов, чтобы регулировочный винт поставлю.
Передовой опыт в сфере производства
Я всегда включаю трассировку следующим образом: Сессия-Настройка и корректное завершение диагностики, как только у меня будет достаточно данных. Для больших трассировок я увеличиваю optimizer_trace_max_mem_size только на короткое время, а затем снова устанавливаю небольшое значение. Перед тем как делиться файлами JSON, я маскирую конфиденциальные константы, тексты комментариев или ключевые бизнес-показатели. Я использую трассировку исключительно в качестве диагностического инструмента, тогда как для постоянного мониторинга предпочитаю журналы медленных запросов, представления производительности или внешние профилировщики. Такой подход позволяет поддерживать системы в оптимальном состоянии и предотвращает ненужные Накладные в повседневной деятельности.
Трассировка оптимизатора в наборе инструментов
Для комплексной настройки я отображаю цепочку, состоящую из понимания плана, анализа причин и системных измерений, и связываю Выводы. Команда EXPLAIN показывает мне план, ANALYZE подтверждает фактические затраты, а трассировка раскрывает причины принятого решения. Параллельно я изучаю концепции планов выполнения запросов, чтобы выявить закономерности в выборе ключей, кардинальностях и стратегиях соединений. Хорошим дополнением к этой точке зрения является краткий обзор Планы выполнения запросов, к которому я обращаюсь при возникновении вопросов по архитектуре. На этом основании я делаю обоснованные Приоритеты для работы с индексами, переработки кода и параметров.
Более подробно: анализ диапазонов и выбор ключей
В трассировке часто встречается блок анализ диапазона для каждой таблицы, где я могу определить, какие индексы подходили для доступа по диапазону (Range), по ключу (Ref) или по ключу с равенством (EQ-Ref). Оптимизатор сравнивает там такие варианты, как „range по idx_a“, „range по idx_b“ или „full scan“, присваивает им затраты и ожидаемое количество строк и выделяет наиболее эффективный вариант. Если я вижу, что подходящий индекс был отклонен из-за высокой стоимости, я в следующую очередь проверяю лежащие в основе показатели селективности и статистику. Если допущения неверны, то АНАЛИЗИРОВАТЬ ТАБЛИЦУ (при необходимости с постоянной статистикой) или создание более целенаправленного Индекс покрытия отменить решение.
Также полезно проанализировать разбивки составных индексов: трассировка показывает, используется ли в условии только первый столбец индекса или же возможны дополнительные предикаты, в результате чего в действие вступают и другие ключевые столбцы. На основании этого я принимаю решение: либо переформулировать предикаты (например, избегать использования функций), либо расширить индекс таким образом, чтобы он охватывал типичные фильтры и сортировки.
Подробно о соединениях: полусоединения, BKA/MRR и буферы соединений
В случае запросов с несколькими таблицами фрагменты трассировки показывают, рассматривалась ли стратегия полусоединения и какая именно (например, FirstMatch, DuplicateWeedout, LooseScan или Materialization). Я вижу там, почему тот или иной вариант был отклонен — например, из-за высоких затрат на материализацию или слишком низкой селективности. Также Пакетный доступ к ключу (BKA) и Многодиапазонное считывание (MRR) появляются в трассировке, если эта функция включена. Эти методы объединяют поиски по ключам и улучшают локальность кэша. Если BKA/MRR не отображаются в трассировке, я проверяю optimizer_switch и такие параметры, как join_cache_level. В рабочих нагрузках с большим количеством случайных обращений к ключам это позволяет заметно ускорить этап соединения, что можно проверить с помощью команды EXPLAIN ANALYZE.
Кроме того, решающее значение имеют размер и тип буфера соединения: трассировка показывает, выполнялись ли варианты Nested Loop с буфером или без него, а также в каком месте срабатывают фильтры. Я оцениваю, что является более эффективным выбором: создание дополнительных индексов по ключам соединения или переработка запроса для сокращения количества промежуточных результатов, по сравнению с увеличением размера буферов.
Подзапросы, производные таблицы и представления
На сайте подготовка к соединению Я считаю, что подзапросы в форме EXISTS/IN в Полусоединения были преобразованы (in_to_exists), объединены ли производные таблицы (derived_merge) или были материализованы, и является ли Условный жим происходит в производных таблицах. Эти шаги имеют решающее значение, поскольку отсутствие слияния может привести к дорогостоящей материализации. Если в трассировке я неоднократно вижу решения о материализации с высокой стоимостью, я проверяю, можно ли использовать явное STRAIGHT_JOIN, подсказка или реорганизация запроса (например, общие табличные выражения с целевыми фильтрами) побуждают движок выбрать более эффективную стратегию. В случае представлений я проверяю, достаточно ли оптимизатор развертывает их содержимое или же в базовой таблице отсутствуют дополнительные индексы.
Разбиение на сегменты и обрезка
В случае разбитых на партиции таблиц трассировка показывает, какие партиции были исключены на основании ключей партиций и предикатов (Обрезка разбиения на сегменты). Если ожидаемое прореживание не происходит, это является сигналом того, что фильтры следует сформулировать раньше и с учетом ключа разбиения. Кроме того, я обращаю внимание на взаимодействие партиционирования и индексов: при отсутствии локальных или глобальных индексов движок может, несмотря на обрезку, проверять чрезмерно большое количество строк, что отражается в трассировке в виде высоких затрат на сканирование.
Целенаправленная проверка подсказок, настроек индекса и параметра `optimizer_switch`
Я использую трассировку, чтобы оценить влияние подсказок и переключателей параметров на оккупировать. Если я, например, поставлю. ИНДЕКС СИЛЫ или подсказку оптимизатора я вижу в трассировке, действительно ли была применена альтернатива и как она была оценена. Через optimizer_switch Я могу временно включать или отключать стратегии (например, для решений типа semijoin, index_merge или derived_merge). Трейс затем служит мне подтверждением того, принял ли движок заданные параметры или же другие ограничения (например, кардинальности) по-прежнему доминируют. По желанию я использую флаги форматирования, такие как one_line или end_markers на сайте optimizer_trace-строку, чтобы адаптировать читаемость к моему инструменту анализа.
Update/DELETE и пути записи
Трейс оптимизатора не ограничивается операторами SELECT. В случае операторов UPDATE и DELETE я также вижу, как выбираются пути доступа и применяются ли фильтры достаточно рано, чтобы свести к минимуму количество затронутых строк. Я проверяю, не является ли фильтр WHERE некомпрессируемым или не приводит ли отсутствие индекса к широкому сканированию перед выполнением собственно изменения. По результатам трассировки я определяю, позволяет ли компактный индекс (например, содержащий только необходимые столбцы) избежать ненужных многократных обращений к базе данных и, таким образом, сократить количество блокировок и объём журнала.
Безопасность, привилегии и подготовленные запросы
Чтобы я мог полностью прочитать запись, мне требуются соответствующие привилегии на доступ к объекту — если их нет, поле сигнализирует об этом INSUFFICIENT_PRIVILEGES Ограничения. Поэтому в сценариях, близких к производственной среде, я использую те же логины, что и приложение, либо специально авторизованную диагностическую учетную запись. В случае подготовленных запросов трассировка, как правило, уже отображает оптимизированную форму с привязанными параметрами, что позволяет мне оценивать селективность без раскрытия конфиденциальных констант. Если мне приходится делиться трассировками, я маскирую значения параметров или заменяю их репрезентативными диапазонами, чтобы соблюдать требования по защите данных.
Автоматизация: сбор, классификация и документирование трассировок
Для обеспечения воспроизводимости анализов я сохраняю трассировки выборочно в диагностической таблице и снабжаю их метаданными, такими как схема, версия, переменные сеанса и временные метки. Таким образом, я могу перед/после изменений индекса или обновлений версий diffen, принятие каких решений было отложено. Удобно разбивать блоки рассмотренные_планы_выполнения и rows_estimation сохранять отдельно, чтобы быстро сравнивать изменения стоимости. Небольшие вспомогательные запросы извлекают для меня выбранный порядок соединений и рассчитанную стоимость — например, с помощью JSON_EXTRACT(TRACE, '$.join_optimization.considered_execution_plans') – и сохраняем результат наряду с выводами команд EXPLAIN и ANALYZE. Таким образом, для каждого этапа оптимизации создается надежная документация.
Ограничения, особенности версий и сравнение с MySQL
Ключевые структуры траса ориентированы на MySQL, однако детали и названия полей могут незначительно отличаться в зависимости от версии MariaDB. Поэтому я сосредоточусь на семантический Разделы (Rewrites, Rows-Estimation, рассматриваемые планы, Condition-Attachments), вместо того чтобы отвлекаться на косметические различия. Важно: в MariaDB основное внимание уделяется последнему оператору активного соединения. Поэтому при анализе множества последовательных операторов данные следует считывать сразу после выполнения или автоматически с помощью хука, чтобы не перезаписать важные следы. В случае очень больших JSON-файлов я учитываю потребность в памяти и понимаю MISSING_BYTES_BEYOND_MAX_MEM_SIZE в качестве предложения временно повысить лимит и запустить анализ заново.
Конкретные примеры извлечения данных из JSON для повседневной работы
В заключение — несколько кратких выдержек, которые я часто использую на практике, чтобы быстро перейти к сути:
- Выбранный порядок соединений и списки кандидатов: я извлекаю префиксы плана и соответствующие присоединенные таблицы, чтобы понять последовательность принятия решений.
- Альтернативные диапазоны и затраты: я извлекаю список оцениваемых индексов для наиболее избирательных таблиц, чтобы точно оценить целесообразность переписывания или создания новых индексов.
- Фильтры, добавленные на ранних этапах: Я читаю
привязка условий к таблицам-разделы, чтобы обеспечить расположение сильных предикатов как можно ближе к источнику данных.
Благодаря небольшому количеству просмотров этих выдержек у меня есть удобный „инструмент“ для анализа решений оптимизатора, который я при необходимости включаю во время диагностических сессий, а затем снова отключаю.
Частые камни преткновения и устранение неполадок
Если гистограммы отсутствуют или статистические данные устарели, оценки оказываются неточными и приводят к Планы из-за ненужных полных сканирований. Если в трассировке я замечаю значительные отклонения в кардинальности, я обновляю статистику, создаю подходящие индексы или переформулирую фильтры с использованием Sargable. Слишком скудные трассировки я распознаю по MISSING_BYTES_BEYOND_MAX_MEM_SIZE и реагирую, временно повышая лимит. Если ANALYZE показывает лучшее время выполнения для альтернативного пути, я проверяю в трассировке, какой фактор затрат способствовал выбору данного варианта. Таким образом, я шаг за шагом восполняю пробелы в знаниях и достигаю Ясность о логике принятия решений.
Краткое резюме
Трейс оптимизатора MariaDB в документе JSON объясняет мне, как движок преобразует запросы, оценивает количество строк, сравнивает планы и, в конечном итоге, выбирает Последовательность выбирает. Я активирую его на каждый сеанс, считываю данные со следа, проверяю подготовка к соединению и оптимизация соединений и сопоставляю полученные выводы с результатами EXPLAIN/ANALYZE. Исходя из причин отклонения индексов, запоздалых фильтров или неверных оценок, я определяю конкретные меры: улучшение индексов, обновление статистики и четкое формулирование запросов. С помощью функций JSON я извлекаю фрагменты, выявляю закономерности и документирую решения таким образом, чтобы их можно было воспроизвести. Таким образом, я обеспечиваю надёжную обработку даже обширных SQL-нагрузок. Производительность и обеспечить прозрачность решений по тюнингу.
