Статистика таблиц, независимая от движка

Введение

До MariaDB 10.0 оптимизатор MySQL/MariaDB полагался на движки хранения (например, InnoDB) для предоставления статистики для оптимизатора запросов. Этот подход работал; однако у него были некоторые недостатки:

• Движки хранения предоставляли плохую статистику (это было частично исправлено с введением Постоянной статистики).
• Статистика предоставлялась через интерфейс движка хранения MySQL, что накладывает множество ограничений на тип предоставляемых данных (например, нет возможности получить какие-либо данные о распределении значений в неиндексированном столбце)
• Контроль над статистикой был ограничен. Не было способа «закрепить» текущие значения статистики или предоставить свои значения и т. д.

Статистика таблиц, независимая от движка, устраняет эти ограничения.

• Статистика хранится в обычных таблицах в базе данных mysql.
  • Администратор базы данных может просматривать и обновлять значения.
• Сбор/использование большего объема данных.

Статистика хранится в трех таблицах, mysql.table_stats, mysql.column_stats и mysql.index_stats.

Использование или обновление данных из этих таблиц контролируется переменной use_stat_tables. Возможные значения указаны ниже:

Сбор статистики с помощью оператора ANALYZE TABLE

Оператор ANALYZE TABLE можно использовать для сбора статистики таблиц. Например:

ANALYZE TABLE table_name;

При выполнении оператора ANALYZE TABLE, MariaDB обращается к движку хранения таблицы, и движок хранения собирает свою статистику для таблицы. Конкретное поведение зависит от движка хранения. Для InnoDB см. Постоянную статистику InnoDB для получения дополнительной информации.

При выполнении оператора ANALYZE TABLE, MariaDB также может собирать статистику, независимую от движка, для таблицы. Конкретное поведение зависит от значения системной переменной use_stat_tables. Статистика, независимая от движка, будет собираться оператором ANALYZE TABLE только в том случае, если выполняется одно из следующих условий:

• Системная переменная use_stat_tables установлена в complementary или preferably.
• Оператор ANALYZE TABLE включает PERSISTENT FOR клаузу.

Сбор статистики для определенных столбцов или индексов

Синтаксис оператора ANALYZE TABLE был расширен с помощью PERSISTENT FOR клаузы. Эта клауза позволяет собирать статистику, независимую от движка, только для определенных столбцов или индексов. Эта клауза также позволяет собирать статистику, независимую от движка, независимо от значения системной переменной use_stat_tables. Например:

ANALYZE TABLE table_name PERSISTENT FOR ALL;

Примеры сбора статистики

-- update all engine-independent statistics for all columns and indexes
ANALYZE TABLE tbl PERSISTENT FOR ALL;

-- update specific columns and indexes:
ANALYZE TABLE tbl PERSISTENT FOR COLUMNS (col1,col2,...) INDEXES (idx1,idx2,...);

-- empty lists are allowed:
ANALYZE TABLE tbl PERSISTENT FOR COLUMNS (col1,col2,...) INDEXES ();
ANALYZE TABLE tbl PERSISTENT FOR COLUMNS () INDEXES (idx1,idx2,...);

-- the following will only update mysql.table_stats fields:
ANALYZE TABLE tbl PERSISTENT FOR COLUMNS () INDEXES ();

-- when use_stat_tables is set to 'COMPLEMENTARY' or 'PREFERABLY', 
-- a simple ANALYZE TABLE  collects engine-independent statistics for all columns and indexes.
SET SESSION use_stat_tables='COMPLEMENTARY';
ANALYZE TABLE tbl;

Ручные обновления таблиц статистики

Статистика хранится в трех таблицах, mysql.table_stats, mysql.column_stats и mysql.index_stats.

Можно вручную обновить таблицы статистики. Для этого необходимо изменить таблицу(ы) с помощью обычных операторов INSERT/UPDATE/DELETE. Данные статистики будут повторно прочитаны при повторном открытии таблиц. Один из способов принудительного повторного открытия всех таблиц — это выполнить команду FLUSH TABLES.

Несколько сценариев, в которых может потребоваться вручную обновлять таблицы статистики:

• Удаление статистики. В настоящее время команда ANALYZE TABLE собирает статистику, но нет специальной команды для удаления статистики.
• Выполнение ANALYZE на другом сервере. ANALYZE TABLE выполняет полное сканирование таблицы, что может создать слишком большую нагрузку на сервер. Можно запустить ANALYZE на сервере-slave, а затем получить данные из таблиц статистики на сервере-slave и применить их на сервере-master.
• В некоторых случаях знание базы данных позволяет вычислить статистику вручную более эффективно, чем делает ANALYZE. Можно вычислить статистику вручную и поместить ее в базу данных.

См. также

• Статистика индексов
• Постоянная статистика InnoDB
• Статистика на основе гистограмм
• Гистограммы JSON (блог mariadb.org)