Почему innodb SHOW TABLE STATUS настолько ненадежный?

Я знаю, что вы не должны полагаться на значения, возвращаемые InnoDB SHOW TABLE STATUS. В частности, количество строк и средняя длина данных.

Но я подумал, что, возможно, это была точная величина, полученная в какой-то момент, а затем innodb только обновляет ее во время таблицы ANALYZE или, возможно, в каком-то другом нечастом событии.

Вместо того, что я вижу, я могу запустить SHOW TABLE STATUS в той же таблице 5 раз за 5 секунд и просто получать совершенно разные номера каждый раз (несмотря на то, что таблица не имеет никакой операции вставки/удаления между ними)

Откуда берутся эти значения? Они просто коррумпированы в innodb?

Ответ 1

Официальная документация MySQL 5.1 подтверждает, что InnoDB не дает точной статистики с SHOW TABLE STATUS. В то время как таблицы MYISAM специально содержат внутренний кеш метаданных, таких как количество строк и т.д., Механизм InnoDB хранит как данные таблицы, так и индексы в */var/lib/mysql/ibdata **

InnoDB не имеет подходящего индексного файла, позволяющего быстро запрашивать номера строк.

Непоследовательные номера строк таблицы сообщаются с помощью SHOW TABLE STATUS, потому что InnoDB динамически оценивает значение Rows путем выборки диапазона данных таблицы (в */var/lib/mysql/ibdata **), а затем экстраполирует приблизительное число строк. Настолько, что в документации InnoDB подтверждается неточность номера строки до 50% при использовании SHOW TABLE STATUS

Документация MySQL предлагает использовать кеш запросов MySQL для получения согласованных запросов номера строки, но в документах не указывается, как это сделать. Ниже приводится краткое объяснение того, как это можно сделать.

Сначала проверьте, включено ли кэширование запросов:

mysql> SHOW VARIABLES LIKE 'have_query_cache';

Если значение has_query_cache равно NO, включите кеш запросов, добавив следующие строки в /etc/my.cnf, а затем перезапустите mysqld.

have_query_cache=1    # added 2017 08 24 wh
query_cache_size  = 1048576
query_cache_type  = 1
query_cache_limit = 1048576

(для получения дополнительной информации см. http://dev.mysql.com/doc/refman/5.1/en/query-cache.html)

Запросить содержимое кеша с помощью

mysql> SHOW STATUS LIKE 'Qcache%';

Теперь используйте оператор SQL_CALC_FOUND_ROWS в запросе SELECT:

SELECT SQL_CALC_FOUND_ROWS COUNT(*) FROM my_innodb_table

SQL_CALC_FOUND_ROWS попытается прочитать чтение из кеша и, если этот запрос не будет найден, выполнить запрос по указанной таблице, а затем зафиксировать количество строк таблицы в кеше запросов. Дополнительные исполнения вышеупомянутого запроса (или других операторов "cachable" SELECT - см. Ниже) будут обращаться к кешу и возвращать правильный результат.

Последующий 'cachable' SELECT query - даже если они LIMIT результат - будет обращаться к кешу запроса и позволит вам get (только однократное) общее число строк таблицы с

SELECT FOUND_ROWS();

который возвращает предыдущую полную таблицу строк в кэшированном запросе.

Ответ 2

Причины несоблюдения точной статистики, в том числе количество строк в таблице, - это многоверсия строк, которые InnoDB использует для предоставления транзакций. Каково фактическое количество строк на самом деле зависит от уровня изоляции транзакций (поскольку транзакция не может быть удалена или вставлена), а разные транзакции могут выполняться на разных уровнях изоляции, что означает, что вопрос "сколько записей есть 'может быть правильно ответен, только если нет транзакций. Поэтому сохранение счетчика строк или длины данных почти невозможно.

Подробнее о Ограничения InnoDB