为什么innodb的SHOW TABLE STATUS如此不可靠? [英] Why is innodb's SHOW TABLE STATUS so unreliable?

问题描述

我知道您不应该依赖InnoDB的SHOW TABLE STATUS返回的值. 特别是行数和平均数据长度.

但是我认为这可能是在某个时候获取的准确值，然后innodb仅在ANALYZE表或其他不常见事件期间刷新它.

相反，我看到的是我可以在5秒内在同一张表上运行5次SHOW TABLE STATUS，并且每次都获得完全不同的数字(尽管表之间没有任何插入/删除活动)

这些值实际上从哪里来?它们只是在innodb中损坏了吗?

解决方案

MySQL 5.1官方文档确认InnoDB没有提供SHOW TABLE STATUS的准确统计信息. MYISAM表专门保留元数据的内部高速缓存，例如行数等，而InnoDB引擎将表数据和索引都存储在*/var/lib/mysql/ibdata **

中

InnoDB没有方便的索引文件，无法快速查询行号.

SHOW TABLE STATUS报告表行号不一致，因为InnoDB通过采样表数据的范围(在*/var/lib/mysql/ibdata **中)动态估算行"值，然后外推近似数行.如此之多以至于InnoDB文档承认使用SHOW TABLE STATUS

时行号不准确度高达50％

MySQL文档建议使用MySQL查询缓存来获取一致的行号查询，但是文档未指定 how .以下是如何做到这一点的简要说明.

首先，检查是否已启用查询缓存:

mysql> SHOW VARIABLES LIKE 'have_query_cache';

如果 have_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%';

现在在SELECT查询中使用SQL_CALC_FOUND_ROWS语句:

SELECT SQL_CALC_FOUND_ROWS COUNT(*) FROM my_innodb_table

SQL_CALC_FOUND_ROWS将尝试从缓存中读取数据，如果找不到此查询，则对指定的表执行查询，然后将表行数提交给查询缓存.上面的查询的其他执行(或其他"cachable" SELECT语句-见下文)将查询缓存并返回正确的结果.

随后的可缓存的" SELECT查询-即使它们LIMIT结果-也会查询查询缓存，并允许您(仅一次)获取表的总行数

SELECT FOUND_ROWS();

返回上一个缓存查询的正确表行总数.

I know that you shouldn't rely on the values returned by InnoDB's SHOW TABLE STATUS. In particular, the row count and avg data length.

But I thought maybe it was an accurate value taken at some point, and then innodb only refreshes it during an ANALYZE table or maybe some other infrequent event.

Instead what Im seeing is that I can run a SHOW TABLE STATUS on the same table 5 times in 5 seconds, and just get completely different numbers each time (despite the table not having any insert/delete activity in between)

Where are these values actually coming from? Are they just corrupt in innodb?

解决方案

The official MySQL 5.1 documentation acknowledges that InnoDB does not give accurate statistics with SHOW TABLE STATUS. Whereas MYISAM tables specifically keep an internal cache of meta-data such as number of rows etc, the InnoDB engine stores both table data and indexes in */var/lib/mysql/ibdata**

InnoDB has no expedient index file allowing a quick query of row numbers.

Inconsistent table row numbers are reported by SHOW TABLE STATUS because InnoDB dynamically estimates the 'Rows' value by sampling a range of the table data (in */var/lib/mysql/ibdata**) and then extrapolates the approximate number of rows. So much so that the InnoDB documentation acknowledges row number inaccuracy of up to 50% when using SHOW TABLE STATUS

MySQL documentation suggests using the MySQL query cache to get consistent row number queries, but the docs don't specify how. A succinct explanation of how this can be done follows.

First, check that query caching is enabled:

mysql> SHOW VARIABLES LIKE 'have_query_cache';

If the value of have_query_cache is NO then enable the query cache by adding the following lines to /etc/my.cnf and then restart mysqld.

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

(for more information see http://dev.mysql.com/doc/refman/5.1/en/query-cache.html)

Query the contents of the cache with

mysql> SHOW STATUS LIKE 'Qcache%';

Now use the SQL_CALC_FOUND_ROWS statement in a SELECT query:

SELECT SQL_CALC_FOUND_ROWS COUNT(*) FROM my_innodb_table

SQL_CALC_FOUND_ROWS will attempt a read from cache and, should this query not be found, execute the query against the specified table and then commit the number of table rows to the query cache. Additional executions of the above query (or other 'cachable' SELECT statements - see below) will consult the cache and return the correct result.

Subsequent 'cachable' SELECT queries - even if they LIMIT the result - will consult the query cache and allow you to get (once-off only) the total table row numbers with

SELECT FOUND_ROWS();

which returns the previous cached query's correct table row total.

这篇关于为什么innodb的SHOW TABLE STATUS如此不可靠?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！