Переменная innodb_stats_auto_recalc, которая включена по умолчанию, управляет тем, рассчитывается ли статистика автоматически, когда таблица претерпевает изменения более чем в 10% строк. Вы также можете настроить автоматический перерасчет статистики для отдельных таблиц, указав предложение STATS_AUTO_RECALC при создании или изменении таблицы.

Из-за асинхронного характера автоматического перерасчета статистики, происходящего в фоновом режиме, статистика может не пересчитываться мгновенно после выполнения операции DML, затрагивающей более 10% таблицы, даже если innodb_stats_auto_recalc включен. Перерасчет статистики может в некоторых случаях задерживаться на несколько секунд. Если актуальная статистика требуется немедленно, выполните ANALYZE TABLE, чтобы инициировать синхронный (фоновый) перерасчет статистики.

Если innodb_stats_auto_recalc отключен, вы можете гарантировать точность статистики оптимизатора, выполнив оператор ANALYZE TABLE после существенных изменений индексированных столбцов. Вы также можете рассмотреть добавление ANALYZE TABLE в скрипты настройки, которые вы выполняете после загрузки данных, и выполнение ANALYZE TABLE по расписанию в моменты низкой активности.

Когда к существующей таблице добавляется индекс или добавляется или удаляется столбец, статистика индекса рассчитывается и добавляется в таблицу innodb_index_stats независимо от значения innodb_stats_auto_recalc.

innodb_stats_persistent, innodb_stats_auto_recalc и innodb_stats_persistent_sample_pages - это глобальные переменные. Для переопределения этих параметров системы и настройки параметров статистики оптимизатора для отдельных таблиц можно определить предложения STATS_PERSISTENT, STATS_AUTO_RECALC и STATS_SAMPLE_PAGES в операторах CREATE TABLE или ALTER TABLE.

Все три предложения указаны в следующем примере CREATE TABLE:

CREATE TABLE `t1` (
`id` int(8) NOT NULL auto_increment,
`data` varchar(255),
`date` datetime,
PRIMARY KEY  (`id`),
INDEX `DATE_IX` (`date`)
) ENGINE=InnoDB,
  STATS_PERSISTENT=1,
  STATS_AUTO_RECALC=1,
  STATS_SAMPLE_PAGES=25;

Оптимизатор использует оценку распределения ключей для выбора индексов в плане выполнения, основываясь на относительной важности индекса. Такие операции, как ANALYZE TABLE, приводят к тому, что InnoDB берёт выборку случайных страниц из каждого индекса таблицы для оценки важности индекса. Этот метод выборки называется .

Параметр innodb_stats_persistent_sample_pages контролирует количество выборочных страниц. Можно изменить это значение во время работы для управления качеством оценок статистики, используемых оптимизатором. Значение по умолчанию равно 20. Рассмотрите возможность изменения этого значения при возникновении следующих проблем:

По умолчанию, InnoDB считывает данные, не подтверждённые, при расчёте статистики. В случае незавершенной транзакции, удаляющей строки из таблицы, строки, помеченные на удаление, исключаются при вычислении оценок строк и статистики индексов, что может привести к не оптимальным планам выполнения для других транзакций, работающих с таблицей одновременно с уровнем изоляции транзакции, отличным от READ UNCOMMITTED. Чтобы избежать этой ситуации, можно включить параметр innodb_stats_include_delete_marked для включения строк, помеченных на удаление, при расчёте постоянной статистики оптимизатора.

При включенном innodb_stats_include_delete_marked, ANALYZE TABLE учитывает строки, помеченные на удаление, при перерасчёте статистики.

innodb_stats_include_delete_marked - это глобальное значение, которое влияет на все InnoDB таблицы, и оно применимо только к постоянной статистике оптимизатора.

innodb_stats_include_delete_marked была введена в MySQL 5.7.16.

Функция постоянной статистики использует управляемые изнутри таблицы в базе данных mysql, называемые innodb_table_stats и innodb_index_stats. Эти таблицы создаются автоматически во всех процедурах установки, обновления и сборки из исходного кода.

Таблицы innodb_table_stats и innodb_index_stats включают столбец last_update, показывающий, когда последняя обновлялась статистика индекса:

mysql> SELECT * FROM innodb_table_stats \G
*************************** 1. row ***************************
           database_name: sakila
              table_name: actor
             last_update: 2014-05-28 16:16:44
                  n_rows: 200
    clustered_index_size: 1
sum_of_other_index_sizes: 1
...

mysql> SELECT * FROM innodb_index_stats \G
*************************** 1. row ***************************
   database_name: sakila
      table_name: actor
      index_name: PRIMARY
     last_update: 2014-05-28 16:16:44
       stat_name: n_diff_pfx01
      stat_value: 200
     sample_size: 1
     ...

Таблицы innodb_table_stats и innodb_index_stats можно обновлять вручную, что позволяет принудительно задавать определённый план оптимизации запроса или тестировать альтернативные планы без изменения базы данных. Если вы обновляете статистику вручную, используйте оператор FLUSH TABLE tbl_name для загрузки обновлённой статистики.

Постоянная статистика рассматривается как локальная информация, поскольку она относится к экземпляру сервера. Следовательно, таблицы innodb_table_stats и innodb_index_stats не реплицируются при автоматическом перерасчёте статистики. Если вы запускаете ANALYZE TABLE для инициирования синхронного перерасчёта статистики, этот оператор реплицируется (если вы не отключили для него логирование), и перерасчёт выполняется на репликах.

Вы можете получить размер индекса для таблиц, разделов или подразделов, используя таблицу innodb_index_stats. В приведенном ниже примере размеры индексов извлекаются для таблицы t1. Для определения таблицы t1 и соответствующей статистики индекса см. Раздел 14.8.11.1.6, «Пример таблиц постоянной статистики InnoDB».

mysql> SELECT SUM(stat_value) pages, index_name,
       SUM(stat_value)*@@innodb_page_size size
       FROM mysql.innodb_index_stats WHERE table_name='t1'
       AND stat_name = 'size' GROUP BY index_name;
+-------+------------+-------+
| pages | index_name | size  |
+-------+------------+-------+
|     1 | PRIMARY    | 16384 |
|     1 | i1         | 16384 |
|     1 | i2uniq     | 16384 |
+-------+------------+-------+

Для разделов или подразделов вы можете использовать тот же запрос с изменённым фрагментом WHERE для получения размеров индексов. Например, следующий запрос извлекает размеры индексов для разделов таблицы t1:

mysql> SELECT SUM(stat_value) pages, index_name,
       SUM(stat_value)*@@innodb_page_size size
       FROM mysql.innodb_index_stats WHERE table_name like 't1#P%'
       AND stat_name = 'size' GROUP BY index_name;