Перейти к содержимому 
Я объясняю Оптимизатор MariaDB Из практики: как он строит планы, оценивает затраты и почему иногда ошибается. Так вы научитесь целенаправленно анализировать план выполнения SQL, грамотно использовать индексы и направлять работу оптимизатора, опираясь на факты, а не на интуицию.
Центральные пункты
Для начала я кратко обобщу основные компоненты, чтобы ты мог целенаправленно осмыслить последующие разделы и Обзор сохранишь.
- Фазы: Разбор, подготовка, оптимизация и выполнение составляют жизненный цикл каждого запроса.
- Модель затрат: Значения в микросекундах, зависящие от времени, определяют выбор индекса, порядок сканирования и порядок объединений.
- Статистика: Кардинальность и гистограмма определяют оценку селективности.
- Прозрачность: Команды EXPLAIN, EXPLAIN ANALYZE и Optimizer Trace позволяют заглянуть «в черный ящик».
- Тюнинг: Индексы, переформулировка запросов, команда ANALYZE TABLE и параметры затрат повышают скорость работы.
Жизненный цикл запроса в MariaDB
Прежде чем сформируется план, запрос проходит четыре этапа, которые я целенаправленно проверяю в повседневной работе, чтобы Причины найти причину медленной работы. При разборе MariaDB преобразует SQL во внутреннюю структуру; здесь выявляются синтаксические ошибки. На этапе подготовки движок проверяет таблицы, столбцы и потенциальные индексы, а также выполняет простые преобразования. Далее следует этап оптимизации, на котором вычисляются возможные планы и оцениваются с помощью модели затрат. На этапе выполнения сервер пошагово реализует выбранный план: чтение, объединение, фильтрация, возврат результатов.
Я четко разделяю ошибки анализа по этапам, потому что так диагностика дает быстрые результаты и Меры действовать целенаправленно. В большинстве случаев проблемы с производительностью коренятся в оптимизации: неверные оценки, отсутствующие индексы или неблагоприятный порядок соединений. Ошибки синтаксического анализа носят тривиальный характер, но этап подготовки (Preparing) уже может включать в себя такие тонкости, как разрешение представлений (view) или преобразование подзапросов. На этапе выполнения неэффективность становится безжалостно заметной, если ранее был выбран полный сканирование. Поэтому я начинаю каждое исследование со структурированного обзора всех четырёх этапов.
Как оптимизатор принимает решение внутри системы
MariaDB работает на основе затрат и оценивает альтернативные варианты выполнения с помощью Функция затрат. Для каждого варианта сервер оценивает количество прочитанных строк, селективность условий WHERE/ON, типы доступа (такие как сканирование таблицы, сканирование индекса, сканирование диапазона), а также временные затраты на отдельные операции. Внутри сервер различает этапы join_preparation и join_optimization. В этапе join_preparation выполняются перезапись запросов, упрощение условий, преобразование подзапросов и разрешение представлений. В фазе `join_optimization` рассчитывается порядок соединений, проверяются кандидаты в индексы с помощью `ref_optimizer_key_uses`, оценивается количество строк с помощью сканирования диапазонов и условия как можно раньше сопоставляются конкретным таблицам.
Этот механизм объясняет, почему небольшой фильтр, установленный не в том месте, может привести к дорогостоящим Последствия имеет. Если операция attaching_conditions_to_tables выполняется на позднем этапе, план ненужно протаскивает через соединения слишком много строк. Если статистика устарела, оценки rows_estimation и Selectivity оказываются неверными; в таком случае оптимизатор выбирает выгодные, но на деле медленные пути доступа. Именно на эти параметры я и ориентируюсь: более точная статистика, чёткие предикаты, аккуратно отсортированные составные индексы. После этого выбор плана часто заметно меняется в лучшую сторону.
Модель расчета затрат, начиная с MariaDB 11.0
В последних версиях оценка работы больше не производится приблизительно на основе весов, а с помощью микросекунды для конкретных операций с хранилищем. Такие параметры, как optimizer_disk_read_cost, optimizer_disk_read_ratio и optimizer_where_cost, позволяют модели более точно отражать реальное время выполнения. Таким образом, оптимизатор сравнивает сканирование диапазона индекса и полное сканирование на основе реальных временных показателей. LAST_QUERY_COST отображает расчетную общую стоимость и зачастую гораздо лучше коррелирует с реальностью, чем раньше. Для систем с большими объемами данных такая более точная настройка окупается сразу же.
Я тщательно калибрую модель, если характеристики оборудования противоречат стандартным допущениям и, следовательно, Выбор плана искажать. ТВР-накопители NVMe, распределенные системы хранения или специальные кэши могут заметно изменить соотношение дисковых операций и время чтения. Небольшие корректировки параметра `optimizer_costs` приводят к тому, что MariaDB отдаёт предпочтение оптимальным путям. Я документирую каждое изменение, а затем проверяю EXPLAIN ANALYZE, чтобы оценить его влияние. Без измерений настройка остаётся делом случая.
Селективность, статистика и гистограммы
Хорошие оценки начинаются с аккуратной кардинальность и надежной селективности. MariaDB ведет статистику по различным значениям для каждого столбца и может по желанию использовать гистограммы для распределений. Именно неравномерные данные — «горячие точки», распределения Ципфа, сезонные закономерности — извлекают максимальную выгоду из использования гистограмм. После значительных изменений в данных я выполняю команду ANALYZE TABLE, чтобы оптимизация вновь работала на основе актуальных данных. Тот, кто забывает об этом, рискует столкнуться с полными сканированиями, которые объективно являются ошибочными.
Я планирую запуск задания ANALYZE в качестве регулярного задания, с учетом Изменения по объему данных и по критическим таблицам. При сильно асимметричном распределении значений по столбцам гистограммы помогают реалистично оценить селективность единичных значений. Это позволяет снизить вероятность ошибочных оценок при использовании стратегий Range-Scan и Merge. В сочетании с подходящими составными индексами точность поиска значительно повышается. Результат: сокращение времени выполнения и уменьшение количества операций ввода-вывода.
EXPLAIN и чтение планов выполнения
Чтобы визуализировать выборки, я использую EXPLAIN, EXPLAIN EXTENDED и ФОРМАТ=JSON. Классические столбцы позволяют быстро сориентироваться: id, select_type, table, type, possible_keys, key, key_len, ref, rows и, при необходимости, filtered. Значение type=ALL указывает на полное сканирование, которое редко бывает желательным. FORMAT=JSON подробно показывает, как были перенесены условия и какие пути оценивал оптимизатор. В контексте хостинга я рекомендую руководство по Планы выполнения в хостинге, чтобы связать информацию о плане с последствиями для инфраструктуры.
Для быстрого анализа мне помогает небольшая таблица, в которой кратко приведены типичные значения, и благодаря этому Ошибочные толкования предотвращено.
| Поле EXPLAIN | Типичное значение | Значение на практике |
|---|---|---|
| тип | ALL, range, ref, eq_ref, const | Чем правее, тем выше степень селективности; ALL означает полное сканирование. |
| possible_keys | Список индексов | Индексы, которые теоретически подходят; если здесь отсутствуют кандидаты, то отсутствует и структура. |
| ключевой | Название индекса | Фактически используемый индекс; пустое поле означает отказ от индекса. |
| строки | Номер | Предполагаемое количество прочитанных строк; значительное расхождение с реальностью = некачественная статистика. |
| отфильтрованный | Процент | Какое количество пропускается через фильтр; как правило, меньшее количество — это лучше. |
Почему оптимизатор иногда ошибается
Ни одна модель расчёта затрат не подходит для всех ситуаций, поэтому я вношу корректировки Ошибки целенаправленно. Устаревшие статистические данные приводят к неверным оценкам количества строк и неэффективной последовательности соединений. Неправильно построенные составные индексы препятствуют использованию индексов при фильтрации по нескольким столбцам. Слишком вложенные подзапросы затрудняют эффективное переписание запросов и блокируют материализацию. Отсутствующие или вводящие в заблуждение фильтры вынуждают движок перемещать большое количество строк, прежде чем начнут действовать полезные предикаты.
Сначала я проверяю, соответствует ли формулировка запроса Индекс Что действительно помогает: правило левого префикса, подходящий порядок сортировки, отказ от использования функций над столбцами в условии WHERE. После этого я проверяю в EXPLAIN ANALYZE, подтверждает ли реальный результат эту оценку. Если нет, то выполняю ANALYZE TABLE и, при необходимости, переписываю запрос. И только в самом конце я прибегаю к FORCE INDEX или подсказкам (hinting), поскольку это может ограничить возможности будущей оптимизации.
Целенаправленное использование трассировки оптимизатора
Если EXPLAIN оказывается недостаточным, я включаю трассировку оптимизатора и отслеживаю Решения в журнале JSON. В нём я вижу, какие планы рассматривались, отклонялись или принимались. Я понимаю, почему то или иное условие срабатывает с задержкой или почему тот или иной индекс не попал в число финалистов. Журнал также показывает, как были перегруппированы условия. Такой обзор углубляет понимание и даёт конкретные рычаги для следующей оптимизации.
Я сохраняю соответствующие фрагменты трассировки вместе с хешем запроса и Параметрыоценивать. Так я смогу позже сравнить, какое изменение привело к какому эффекту. В документации по серверу MariaDB и в различных докладах, посвящённых экосистеме, эти поля описаны подробно (источник: документация по серверу MariaDB, раздел «Query Optimizer» и «Optimizer Trace»). С помощью этого инструмента я быстрее нахожу ошибочные допущения, чем методом проб и ошибок. Время я экономлю, прежде всего, при работе со сложными соединениями.
Практика: Пошаговая настройка базы данных
Я начинаю любую оптимизацию с четкого Измерение. Я выявляю проблемы с помощью мониторинга и… Журнал медленных запросов. Затем я сравниваю результаты EXPLAIN и EXPLAIN ANALYZE, чтобы сопоставить план и реальный результат. Стратегию индексирования я адаптирую к условиям WHERE, JOIN и ORDER BY; составные индексы я ориентирую на наиболее частые точки доступа. FORCE INDEX я использую только в том случае, если оптимизатор, несмотря на правильные статистические данные, выбирает неверный вариант.
Каждый шаг включает в себя уход за Статистика: ANALYZE TABLE для таблиц с высокой активностью, гистограммы для асимметричных распределений. Я упрощаю ненужные подзапросы, при необходимости материализую промежуточные результаты и устраняю устаревшие обходные решения. При использовании специального оборудования я проверяю значения optimizer_costs, чтобы обеспечить точность модели в микросекундах. Каждое изменение я сопровождаю значениями «до» и «после», чтобы его эффект оставался отслеживаемым в долгосрочной перспективе.
Типичные проблемы оптимизатора и их решения
Если EXPLAIN type=ALL показывает, что поле possible_keys заполнено, я сначала смотрю на Селективность. Часто порядок столбцов в составной индексной структуре не подходит, либо какая-либо функция препятствует использованию индекса. В таком случае я меняю порядок, удаляю мешающие функции или разбиваю предикаты. При неправильном порядке соединений я проверяю, возможна ли ранняя фильтрация, например, путем вынесения более селективной таблицы на начало. Подзапросы я преобразую, где это целесообразно, в соединения или временные таблицы.
Я распознаю ошибочные решения также по значительно отклоняющимся строки между планом и реальностью. В этом случае поможет команда ANALYZE TABLE или гистограмма по соответствующему столбцу. Если даже правильные статистические данные не приводят к желаемому результату, я рассматриваю возможность использования явных подсказок. Перед этим я сохраняю контрольные данные и измеренные значения, чтобы не затормозить работу последующих версий оптимизатора. В этом случае дисциплина в ведении документации окупается.
Контекст хостинга и аспекты эксплуатации
Качество запросов и инфраструктура должны быть согласованы, иначе приложение будет работать неэффективно Потенциал. Быстрые SSD-накопители, согласованные кэши и четкая настройка — вот основа, на которой оптимизатор принимает правильные решения. При высокой нагрузке нет места полным сканированиям; даже несколько некачественных запросов могут замедлить работу всей системы. Для среды MySQL/MariaDB в производственной эксплуатации полезны такие практические рекомендации, как Оптимизатор MySQL полезные идеи для размышлений о сочетании плана и платформы. Тот, кто учитывает этот аспект, предотвращает возникновение узких мест, прежде чем они приведут к серьезным проблемам.
Я всегда связываю анализ плана с показателями по ВВОД/ВЫВОД, задержка и параллелизм. Если значения не соответствуют принятой модели затрат, я проверяю параметры. Затем я обращаю внимание на размеры буферов, параллельные рабочие нагрузки и распределение «горячих наборов». Такой подход позволяет обеспечить сбалансированную работу запросов и ресурсов, а также контролировать пиковые нагрузки.
Пути объединения и доступа на практике
Многие недоразумения я разъясняю, объясняя, что Типы доступа целенаправленно сопоставляем друг с другом. Один диапазон- или ref-Доступ срабатывает почти всегда ВСЕ. При операциях «И» по уникальным ключам (eq_ref) конструкции отличаются особой прочностью. Кроме того, я проверяю, не Индекс покрытия полностью обслуживает запрос: если в индексе присутствуют все необходимые столбцы, MariaDB избегает затратных обращений к таблицам. Индексное отжимание (ICP) помогает проверять дополнительные условия WHERE уже в индексе — это сокращает количество возвращаемых строк и количество операций ввода-вывода.
О сайте Слияние индексов MariaDB может объединять несколько индексов (пересечение/объединение). Это полезно при использовании предикатов OR или нескольких условий отбора, но зачастую работает медленнее, чем правильно выбранный составной индекс. Кроме того, я анализирую MRR (чтение нескольких диапазонов) и Федеральное управление криминальной полиции (BKA) (Batched Key Access). MRR сортирует первичные ключи для чтения, чтобы сгладить случайные операции ввода-вывода; BKA объединяет запросы при соединении таблиц и особенно эффективен при неперекрывающихся соединениях. На практике я тестирую BKA/MRR с помощью optimizer_switch и с помощью EXPLAIN ANALYZE проверяю, снижаются ли показатели ввода-вывода. Если же MariaDB использует Блок вложенного цикла (BNL), в большинстве случаев целесообразнее увеличить размер буфера соединений (join_buffer_size) — или использовать перезапись, позволяющую выполнять соединения по индексам.
-- Пример: составной индекс для соединения + фильтра + сортировки
CREATE INDEX ix_orders_cust_status_created
ON orders (customer_id, status, created_at);
-- Типичный запрос
SELECT *
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'open' AND o.created_at >= '2026-01-01'
ORDER BY o.created_at DESC
LIMIT 50;
С помощью указанного выше индекса оптимизатор может выбрать наиболее селективный порядок, рано выполнить оценку фильтров и зачастую осуществлять сортировку без дополнительной файловой сортировки.
ORDER BY, GROUP BY, файловая сортировка и временные таблицы
Сортировка и агрегирование требуют времени. Я позабочусь о том, чтобы ORDER BY и ГРУППА ПО могут выполняться в порядке индекса. Это работает, если префикс и направление точно совпадают. В противном случае применяется Сортировка файлов с буфером сортировки (sort_buffer_size) и, при необходимости, временной таблицей. Если набор результатов содержит широкие столбцы типа TEXT/BLOB, MariaDB работает быстрее на диске TEMP-таблицы (Aria). Я принимаю меры предосторожности, выбирая только необходимые столбцы, загружая большие поля только в конце или используя префиксы с ограничением длины.
При агрегировании я, по возможности, использую, Сканирование индекса без фиксации (например, GROUP BY по ведущей части индекса) и выбираю составные индексы вдоль группировки. Когда промежуточные результаты становятся большими, материализация с использованием подходящих ключей масштабируется лучше, чем единственное мега-соединение. Я регулярно измеряю метрики обработчиков и счетчики Created_tmp_*, чтобы выявлять «горячие точки» сортировки и временных таблиц.
Подзапросы, полуобъединение и материализация
Многие подзапросы можно эффективно преобразовать на этапе подготовки. Конструкции IN/EXISTS можно использовать в качестве Полусоединение работают с такими стратегиями, как «материализация» или «LooseScan». Я проверяю, использует ли оптимизатор derived_merge смог выполнить: если производная таблица (или WITH-CTE) включается во внешний план, её индексы становятся сразу доступными. Если это не удаётся, подзапрос попадает во временную таблицу — в таком случае я, если это возможно, присваиваю ей ключ (например, с помощью SELECT DISTINCT/ORDER BY по ключевым столбцам), чтобы соединения с ней не затерялись в нирване.
-- Пример: EXISTS вместо IN и производная таблица, поддерживающая слияние
SELECT o.id
FROM orders o
WHERE EXISTS (
SELECT 1 FROM payments p
WHERE p.order_id = o.id AND p.state = 'captured'
);
-- Вывод с явно указанными ключами
WITH paid_orders AS (
SELECT DISTINCT order_id
FROM payments
WHERE state = 'captured'
)
SELECT o.*
FROM orders o
JOIN paid_orders po ON po.order_id = o.id;
Я проверяю с помощью EXPLAIN FORMAT=JSON, является ли материализовался или зависимый подзапрос был избран и существуют ли условия (условие спуска стека) принять меры достаточно рано.
Разбиение на сегменты и обрезка
Разделение на разделы не заменяет индексы, но может Объем данных за один доступ значительно сократить. Оптимизатор выполняет «prune» корректно только в том случае, если предикат Ключ раздела однозначно соответствует и не завуалируется функциями. Поэтому я избегаю использования выражений типа DATE(created_at) в условии WHERE для разбитых на партиции таблиц и вместо этого работаю с границами диапазонов. Команда EXPLAIN показывает, какие партиции считываются; широкие диапазоны указывают на неэффективную оптимизацию (pruning).
Слишком большое количество мелких партиций увеличивает накладные расходы на планирование. Поэтому я выбираю разумную степень детализации (например, ежемесячную вместо ежедневной), обновляю статистику по каждой партиции (ANALYZE PARTITION) и проверяю, имеются ли важные индексы локально в партициях. При реализации проектов миграции я учитываю влияние на репликацию и резервное копирование — оба этих фактора определяют, насколько активно я использую разбиение на партиции.
Sargability и паттерн перезаписи
Самый простой рычаг — это Возможность транспортировки в гробу – Условия, при которых можно использовать индексы. Я избегаю использования функций над столбцами в условии WHERE, возвращаю константы со стороны столбцов и, при необходимости, разбиваю условия OR на СОЮЗ ВСЕХ. Для поиска по LIKE без начального анкора ("%foo") индекс BTREE бесполезен; в этом случае я планирую использовать полнотекстовый поиск или подходящий поисковый сервис. При вычислениях я использую индексированные сгенерированные столбцы, чтобы оптимизатор мог распознать логику индекса.
-- Антипаттерн: функция на столбце
WHERE DATE(created_at) = '2026-08-01'
-- Лучше: диапазон по исходному значению
WHERE created_at >= '2026-08-01' AND created_at < '2026-08-02'
-- Антипаттерн: оператор OR препятствует использованию индекса
WHERE status = 'open' OR customer_id = 42
-- Лучше: два поиска с UNION ALL и отдельным индексом для каждого
(SELECT ... WHERE status = 'open')
UNION ALL
(SELECT ... WHERE customer_id = 42');
Что касается композитных индексов, я считаю, что правило левого префикса Строго соблюдайте порядок: упорядочивайте столбцы по избирательности и в соответствии с сортировкой, которая понадобится позже. Если мне требуется убывающая сортировка ORDER BY, я учитываю это при размещении индекса — так я избавляюсь от необходимости файловой сортировки.
Переключатель оптимизатора и точная настройка затрат
Прежде чем приступить к запросам, я проверяю optimizer_switch и буфер памяти. Такие функции, как mrr, batched_key_access, index_merge, полусоединение, derived_merge или условие_спуска_для_производного можно настроить для каждого сеанса. Я целенаправленно активирую кандидатов для тестового сеанса, измеряю с помощью EXPLAIN ANALYZE и отменяю изменения, если эффект отсутствует. Траектория соединения выигрывает от достаточного размер_буфера; большие сорта размер_буфера. При этом я слежу за буферами с учётом параллелизма, чтобы сервер не перешёл в режим свопинга при параллельной нагрузке.
На уровне затрат я, при необходимости, корректирую уже упомянутые затраты_оптимизатора в микросекундах. Мой подход: небольшие, обратимые шаги с задокументированными контрольными точками. Я использую LAST_QUERY_COST для проверки достоверности и повторяю измерения с реалистичными значениями параметров, поскольку планы могут в значительной степени зависеть от конкретных литералов.
Стабильность плана, регрессии и рабочий процесс команды
Даже хороший план может быть сорван из-за роста объема данных или смены версии опрокидывать. Поэтому я собираю информацию о планах выполнения: хеши запросов, JSON-результаты EXPLAIN, фрагменты трассировки оптимизатора и время выполнения EXPLAIN ANALYZE. Изменения индексов и переписание кода я отправляю в виде пулл-реквестов с подтверждающими данными «до» и «после». В средах CI/CD я автоматически проверяю критические запросы на репрезентативных наборах данных. Таким образом, я Планы регрессии рано утром.
Для сложных случаев я считаю, что Советы (FORCE INDEX, STRAIGHT_JOIN, optimizer_switch для каждого запроса) можно использовать в качестве крайней меры, но применять их следует с осторожностью и устанавливать срок действия. Лучше устранить первопричины — статистику, индексы, формулировку запроса. В командах наличие краткого руководства по Sargability, проектированию индексов и дисциплине измерений гарантирует, что новые функции не приведут к незаметному снижению производительности.
Краткий отчет: от плана к результату
Кто может использовать План понимает, управляет производительностью. Этапы разбора, подготовки, оптимизации и выполнения позволяют определить, где теряется время. Модель затрат, основанная на времени, доступная начиная с версии 11.0, а также тщательно ведёмые статистические данные и гистограммы делают оценки надёжными. EXPLAIN, EXPLAIN ANALYZE и трассировка оптимизатора обеспечивают прозрачность, которую я преобразую в конкретные меры. Благодаря грамотной стратегии индексирования, четкому дизайну запросов и подходящей инфраструктуре запросы MariaDB постоянно дают быстрые ответы.
