Перейти к содержимому 
Гистограммы MySQL предоставляют оптимизатору реальные данные о распределении, чтобы он мог правильно оценивать селективность и формировать более быстрые планы запросов — зачастую даже без дополнительного индекса. Я покажу, как я настраиваю и контролирую гистограммы в MySQL 8+ с помощью ANALYZE TABLE и использую их для принятия более эффективных решений при соединениях, фильтрации и сканировании.
Центральные пункты
Краткий обзор: В приведенных ниже пунктах указано, на что я обращаю особое внимание при использовании гистограмм.
- Селективность Вместо интуиции: более реалистичные оценки кардинальности
- Без указателя быстрее: более оптимальный выбор плана при асимметричных распределениях
- Типы Понять: целенаправленное использование Singleton и Equi-Height
- Ведра управление: сопоставить выгоды от ликвидации с затратами на метаданные
- Уход В поле зрения: обновлять, проверять, при необходимости удалять
Почему гистограммы без индекса выглядят так
Я использую Гистограммы, поскольку в противном случае оптимизатор часто исходит из равномерного распределения и в результате выбирает неэффективные планы. Гистограмма отражает Распределение значений приблизительно по столбцу и, таким образом, предоставляет реалистичные оценки селективности для предикатов, таких как =, >, BETWEEN, IN или IS NULL. Затем оптимизатор решает, что будет более эффективным: сканирование диапазона индекса, сканирование таблицы или стратегия соединения с вложенными циклами. Например, если условие соответствует только 0,1 % строк, я предпочитаю целенаправленный доступ вместо широкого сканирования. Если же фильтр охватывает почти все строки, я отказываюсь от дорогостоящих обращений к индексу, которые не приносят пользы, и таким образом повышаю Эффективность каждого плана.
Типы гистограмм в MySQL 8.0
Я выделяю два Типы: „Singleton“ и „Equi-Height“. Гистограммы типа „Singleton“ объединяют часто встречающиеся единичные значения в отдельные ячейки — это идеально подходит для столбцов с небольшим количеством доминирующих категорий, таких как «активный», «неактивный» или «архивный». Гистограммы «Equi-Height» делят диапазон значений таким образом, чтобы каждый интервал содержал примерно одинаковое количество Линии ; это подходит для непрерывных или неравномерных распределений, таких как цены, временные метки или диапазоны идентификаторов с „пробелами“. Оба варианта предоставляют оптимизатору более точные показатели точности фильтров. Я всегда выбираю тип в зависимости от свойств данных, а не по личным предпочтениям.
Технические основы: управление выбором типов данных в MySQL
MySQL определяет конкретную Вариант гистограммы автоматически на основе распределения данных. На практике это означает: если количество различных значений (NDV) достаточно мало по сравнению с количеством интервалов, фактически получается гистограмма типа „singleton“; в противном случае создается гистограмма типа «equi-height». Поэтому я «выбираю» тип непрямой, выбирая подходящий столбец и соответствующее количество интервалов. Для столбцов с очень небольшим количеством, но сильно доминирующих категорий я сознательно устанавливаю небольшое количество бакетов, чтобы получить точность, близкую к синглтону, для этих значений. В случае мелкодисперсных, непрерывных данных я постепенно увеличиваю количество бакетов, пока EXPLAIN не выдаст желаемый результат. Селективность отражает.
Важно: гистограммы — это в одну колонку. Они не могут напрямую отражать зависимости между столбцами (например, «status» и «country»). В таких случаях помогает создать гистограмму для наиболее селективного столбца и соответствующим образом определить порядок соединений.
Как правильно выбрать ведра
По умолчанию MySQL использует 100 Ведра, но с помощью WITH N BUCKETS допускается значение от 1 до 1024. Увеличение количества корзин повышает разрешение, однако при этом возрастает объем метаданных и трудоемкость анализа. Я обычно начинаю с консервативных настроек, оцениваю влияние с помощью EXPLAIN и постепенно увеличиваю количество корзин, если план по-прежнему кажется неподходящим. При сильно сконцентрированных значениях (например, 90 % в одном статусе) часто достаточно небольшого количества бакетов; при мелко рассеянных ценах или временных метках целесообразно использовать больше бакетов. Цель — достичь разумного Зернистость, что позволило заметно сократить количество ошибочных оценок, не увеличивая при этом излишне административную нагрузку.
Практика: рабочий процесс с использованием ANALYZE TABLE
Я следую четкому Рабочий процесс: Сначала я выявляю столбцы, которые часто встречаются в условиях WHERE или JOIN и имеют явно неравномерное распределение. Затем я создаю гистограмму с помощью команды ANALYZE TABLE tbl UPDATE HISTOGRAM ON col WITH N BUCKETS; и проверяю её через INFORMATION_SCHEMA.COLUMN_STATISTICS. После перемещения данных я снова обновляю статистику с помощью ANALYZE TABLE. Если статистика не подходит, я удаляю её с помощью ANALYZE TABLE tbl DROP HISTOGRAM ON col;. Для оценки влияния плана я читаю Интерпретация EXPLAIN ANALYZE и сопоставить эти оценки с фактическими данными Линии от.
Конкретные приказы и контроль
Я работаю по четкой, повторяемой схеме, состоящей из нескольких простых шагов, и проверяю сгенерированные статистические данные в формате JSON.
-- Создание гистограмм по отдельным столбцам
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 32 BUCKETS;
ANALYZE TABLE orders UPDATE HISTOGRAM ON created_at WITH 128 BUCKETS;
-- Несколько столбцов за один проход с одинаковым количеством бакетов
ANALYZE TABLE orders UPDATE HISTOGRAM ON status, payment_method WITH 64 BUCKETS;
-- Целевое удаление гистограмм
ANALYZE TABLE orders DROP HISTOGRAM ON status;
-- Визуальный просмотр статистики
SELECT
SCHEMA_NAME, TABLE_NAME, COLUMN_NAME,
JSON_PRETTY(HISTOGRAM) AS histogram
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
WHERE SCHEMA_NAME = DATABASE()
AND TABLE_NAME = 'orders'
AND COLUMN_NAME IN ('status','created_at');
Я оцениваю эффективность непосредственно с помощью EXPLAIN ANALYZE:
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE status = 'canceled'
AND created_at >= NOW() - INTERVAL 7 DAY;
Улучшается ли оценка строки Если разница заметна и план, например, переключается с полного сканирования на сканирование по индексу и диапазону или изменяется порядок соединений, значит, мера оказалась успешной. Если отклонение остается значительным, я увеличиваю или уменьшаю количество бакетов и сравниваю результаты заново.
Пример: статус заказа и редкие значения
В таблице заказов чаще всего встречается статус „completed“, тогда как статус „pending“ встречается довольно часто, а „canceled“ — очень редко; это нестабильное положение без использования гистограммы легко приводит к неверным оценкам селективности. Если API запрашивает значение „canceled“, оптимизатор может ошибочно выбрать полное сканирование таблицы, хотя достаточно было бы узкого доступа по индексу. С помощью гистограммы-синглтона MySQL распознает, что „canceled“ составляет лишь ничтожную долю, и переключается на сканирование диапазона индекса или оптимизирует порядок соединений. Таким образом, снижается задержка, и мне не нужен дополнительный индекс для каждого Вариант фильтра. В дашбордах с жесткими показателями SLO такая корректировка часто приводит к заметному улучшению отклика.
Временные ряды и временные метки
В случае временных рядов часто встречаются Доступы на основе свежих данных; более старые временные интервалы, как правило, остаются «холодными». Гистограмма Equi-Height по полям `created_at` или `updated_at` позволяет четко различить периоды с высокой активностью и периоды с низкой. Оптимизатор затем правильно оценивает, целесообразно ли выполнить просмотр диапазона (Range-Scan) или же сканирование таблицы (Table-Scan) приведёт к результату быстрее. Особенно при использовании частичных временных фильтров над большими таблицами я замечаю заметные изменения плана выполнения и снижение затрат на ввод-вывод. Я считаю, что Статистика здесь обновляется чаще, поскольку акцент смещается в сторону повседневной деятельности.
Разделы, типы данных и коллизации
Рассматриваю распределение данных в разбитых на разделы таблицах по всем разделам. Сильные колебания (например, по месяцам) могут сглаживать глобальные гистограммы. Если отдельные партиции являются чрезвычайно избирательными или чрезвычайно широкими, я дополнительно проверяю с помощью фильтров «partition pruning» в условии WHERE, подходит ли качество плана в любом случае. В целом я стараюсь формулировать фильтры таким образом, чтобы MySQL мог выявлять партиции на раннем этапе исключить Может.
Гистограммы наиболее эффективно работают со скалярными, сопоставимыми типами данных (числа, даты и время, VARCHAR/CHAR с подходящей коллицией). При Данные в формате LOB/JSON я скорее делаю ставку на Сгенерированные столбцы с извлеченными, типизированными значениями и, при необходимости, сопровождаю их гистограммами или индексами. Для строк определяет Коллиция логика сравнения; в зависимости от коллиции значения могут совпадать (например, при учете регистра). Я поддерживаю коллицию в соответствии с запросами, чтобы получить реалистичные показатели селективности.
Пределы и ошибки
Гистограммы позволяют оценить, прежде всего, отдельные столбцы с Константы Хорошо; однако они могут отображать многостолбцовые зависимости лишь в ограниченной степени. В случае сильно коррелирующих столбцов или динамических параметров (например, заполняемых на стороне приложения) они сталкиваются с ограничениями. Булевы поля или столбцы с практически равномерным распределением редко выигрывают от дополнительной статистики. Слишком большое количество интервалов и чрезмерные затраты на обслуживание, в свою очередь, могут увеличить время на администрирование и анализ. Поэтому я целенаправленно использую гистограммы и регулярно проверяю Эффект на реальные варианты.
Проверка и обновление оптимизатора
Я проверяю Используйте от гистограмм до ANALYZE TABLE и соответствующих параметров оптимизатора, чтобы планировщик мог эффективно использовать статистику. В системах с высокой нагрузкой я планирую обновление в периоды низкой нагрузки или пакетно после крупных загрузок. До и после этого я сравниваю результаты команд EXPLAIN и EXPLAIN ANALYZE, чтобы оценить изменения в последовательности соединений, этапах фильтрации и моделях затрат. В случае негативных последствий я немедленно реагирую и откатываю статистику. Для более детального управления Параметры оптимизатора я слежу за тем, чтобы зависимости с другими статистическими показателями не привели к незамеченным ошибкам Допущения производить.
Мониторинг, защита от регрессии и руководство по действиям
Я собираю себе легкий Справочник для рабочей среды:
- Определение базовых показателей: перед внесением изменений запустить EXPLAIN ANALYZE, зафиксировать время выполнения, количество „проанализированных строк“ и счетчик обработчиков.
- Создание/изменение гистограммы: с учетом столбцов фильтра, консервативные интервалы.
- Сразу после этого провести измерение: план, расчетные и фактические показатели; отклонение с коэффициентом >10 для меня является сигналом тревоги.
- Точная настройка: перемещение сегментов вверх/вниз; при необходимости — изменение порядка фильтров в запросе.
- Будьте готовы к откату: выполните команду DROP HISTOGRAM, если задержки увеличатся.
- Автоматизация: выполнение команды ANALYZE после загрузки данных по схеме ETL или крупных волн операций DML в окнах технического обслуживания.
Для анализа причин я использую Трассировки оптимизатора а также EXPLAIN ANALYZE, чтобы определить, выдвигает ли планировщик на передний план правильную таблицу с высокой селективностью на основе гистограмм. Для A/B-тестов я в экспериментальном порядке фиксирую порядок соединений (STRAIGHT_JOIN) или принудительно включаю/отключаю отдельные индексы, чтобы изолированно оценить влияние статистики.
С организационной точки зрения хорошо себя зарекомендовал короткий Журнал изменений В каждой таблице: столбец, количество бакетов, момент времени, значения измерений «до» и «после». Это облегчает внесение поправок в дальнейшем и позволяет избежать неясных взаимосвязей.
Операционные аспекты: блокировка, затраты, переносимость
ANALYZE TABLE принимает Блокировка метаданных в таблице, но не блокирует обычные операции чтения/записи на постоянной основе. Для очень больших таблиц я закладываю достаточно времени; построение гистограммы осуществляется на основе выборок и ограничено объемом памяти (ключевое слово: внутренняя оперативная память для вычислений). Объём памяти, необходимый для самой статистики, остаётся умеренным: от нескольких десятков до нескольких сотен килобайт на столбец с 100–256 корзинами — это реалистичный ориентир. В целом я всё же провожу расчёты, поскольку много столбцов, умножённых на много таблиц, даёт видимые метаданные.
На сайте Логические дампы (mysqldump) гистограммы не передаются вместе с данными; после восстановления я специально создаю их заново. При обновлении на месте (In-Place-Upgrade) они сохраняются. С точки зрения прав доступа мне требуются достаточные привилегии для выполнения команды ANALYZE TABLE на соответствующих объектах; в строго регулируемых средах я включаю эту операцию в конвейеры технического обслуживания.
Когда гистограммы бесполезны
Я не буду Гистограммы в отношении столбцов, которые содержат очень мало значений и в любом случае хорошо оцениваются. Даже там, где хороший индекс уже охватывает минимальные наборы результатов, гистограмма редко приносит дополнительную пользу. Равномерные распределения не требуют сложной детализации. В высокодинамичных системах с интенсивной записью данных обслуживание может создавать ненужную нагрузку, если запускать его слишком часто. В таких ситуациях я использую Энергия скорее в стратегиях индексирования, проектировании запросов и кэшировании.
Таблица-шпаргалка
Я использую следующий Обзор для быстрого принятия решений: какой тип гистограммы подходит, как настроить интервалы и какие затраты при этом возникают. Таблица служит памяткой при анализе проблемных запросов. Я обновляю её с учётом выводов, полученных при использовании EXPLAIN ANALYZE и производственных метрик. При этом я учитываю, что распределения данных меняются, а исторические допущения устаревают. Решающим фактором остаётся Качество плана подтвердить с помощью реальных измерений.
| Аспект | Рекомендация | Выгода | компромисс | Пример |
|---|---|---|---|---|
| Тип | Синглтон при небольшом количестве доминирующих значений | Точные показатели точности для популярных категорий | Не особо помогает при непрерывных областях | статус_заказа |
| Тип | Equi-Height при искажённых непрерывных данных | Более точная оценка по всему диапазону значений | Больше метаданных при большом количестве бакетов | created_at, price |
| Ведра | Начните со 100, затем скорректируйте | Сбалансированное разрешение | Более высокая нагрузка на системы анализа и хранения данных при значениях 512–1024 | С 100 ВЕДРАМИ |
| Уход | После значительных изменений в данных ANALYZE | Текущие показатели селективности | Планирование периода технического обслуживания | АНАЛИЗ ТАБЛИЦЫ … ОБНОВЛЕНИЕ ГИСТОГРАММЫ |
| Управление | Проверить с помощью COLUMN_STATISTICS | Прозрачность и аудит | Требуется интерпретация JSON | INFORMATION_SCHEMA.COLUMN_STATISTICS |
Вписывание в общую картину тюнинга
Я лечу Гистограммы в качестве одного из компонентов наряду с индексами, построением запросов, кэшированием и параметрами оборудования. Зачастую хорошо построенная гистограмма изменяет порядок соединений, снижает количество операций ввода-вывода и обеспечивает постоянное время отклика. Тем не менее, я не использую её в качестве замены грамотной стратегии индексирования и эффективной схемы базы данных. Тем, кто более глубоко изучает процесс планирования запросов, будет полезно Понимание планов выполнения и сравнивает модели расчета затрат с фактическими сроками выполнения. Я регулярно проверяю, соответствуют ли Рабочие нагрузки соответствуют ли они статистическим данным или требуются корректировки.
Сложные сценарии объединения
Гистограммы особенно полезны, когда речь идет о нескольких таблицах с фильтрами. Пример:
SELECT o.id, o.amount
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.country = 'DE'
AND o.status = 'canceled'
AND o.created_at >= NOW() - INTERVAL 30 DAY;
Без гистограмм оптимизатор может недооценить селективность o.status=’canceled‘ или переоценить долю немецких пользователей. С гистограммой на и страна и без статуса (при необходимости также на o.created_at) разработчик обычно понимает, что данное сочетание является чрезвычайно избирательным. На практике я затем вижу, что MySQL сначала определяет меньшее подмножество (например, с помощью индекса на users(country) или orders(status, created_at)), а уже после этого выполняет соединение — вместо того, чтобы сканировать большую таблицу. Это экономит операции ввода-вывода, буферную память и ресурсы ЦП, а также стабилизирует задержку даже при высокой нагрузке.
Поскольку гистограммы только в одну колонку , индексные стратегии по-прежнему остаются важными: составной индекс по (status, created_at) может ещё больше ускорить сканирование диапазона. Гистограмма в данном случае, прежде всего, обеспечивает, чтобы оптимизатор Стратегия вообще считает выгодным.
Резюме для практики
Я установил MySQL-Использую гистограммы, когда оптимизатор ошибается при использовании стандартных статистик, а асимметричные распределения приводят к формированию некорректных планов. С помощью ANALYZE TABLE я целенаправленно создаю, обновляю и удаляю статистику по столбцам, которые преобладают в фильтрах и соединениях. Выбор между Singleton и Equi-Height я делаю на основе данных, а количество корзин калибрую с помощью измерений. С помощью EXPLAIN ANALYZE я проверяю, изменяются ли порядок соединений, позиции фильтров и сканирования так, как нужно. Таким образом, я достигаю с минимальными Накладные заметно более быстрые запросы — зачастую без дополнительных индексов.
