mysql innodb 行数_mysql – 为什么InnoDB不存储行数？

我同意@RemusRusanu(1回答)

在InnoDB中,SELECT COUNT(*)FROM mydb.mytable的行为就像事务存储引擎一样.将它与MyISAM进行比较.

MyISAM数据

如果mydb.mytable是MyISAM表,则启动SELECT COUNT(*)FROM mydb.mytable;就像运行SELECT table_rows FROM information_schema.table WHERE table_schema =’mydb’AND table_name =’mytable’;.这会触发快速查找MyISAM表标题中的行计数.

InnoDB的

如果mydb.mytable是一个InnoDB表,你会得到大量的东西.你有MVCC正在进行,管理以下内容：

> ib_logfile0 / ib_logfile1(重做日志)

> ibdata1

>撤消日志

>回滚

>数据字典更改

>缓冲池管理

>交易隔离(4种类型)

>可重复读取

>阅读提交

>阅读未提交

>可序列化

询问InnoDB表的数量需要通过这些不祥的东西进行导航.实际上,人们从未真正知道mydb.mytable中的SELECT COUNT(*)是否只计算可重复读取,或者包括已提交的读取和未提交的读取.

When this variable is enabled (which is the default, as before the variable was created), InnoDB updates statistics during metadata statements such as SHOW TABLE STATUS or SHOW INDEX, or when accessing the INFORMATION_SCHEMA tables TABLES or STATISTICS. (These updates are similar to what happens for ANALYZE TABLE.) When disabled, InnoDB does not update statistics during these operations. Disabling this variable can improve access speed for schemas that have a large number of tables or indexes. It can also improve the stability of execution plans for queries that involve InnoDB tables.

在设置EXPLAIN计划方面,禁用它可能会也可能不会给您更稳定的计数.它可能以一种好的方式,坏方式或根本不影响mydb.mytable的SELECT COUNT(*)性能.试一试!!!