查询架构时，MySQL的行数会波动吗? [英] MySQL gives fluctuating row count when I query schema?

问题描述

在这里，我一遍又一遍地在笔记本电脑上的dev DB上运行并运行相同的命令；

Here I am pressing up and running the same command on my dev DB on my laptop, over and over;

mysql> select count(*) from tblTraceOutput;
+----------+
| count(*) |
+----------+
|   300175 |
+----------+
1 row in set (0.42 sec)

mysql> select count(*) from tblTraceOutput;
+----------+
| count(*) |
+----------+
|   300175 |
+----------+
1 row in set (0.35 sec)

mysql> select count(*) from tblTraceOutput;
+----------+
| count(*) |
+----------+
|   300175 |
+----------+
1 row in set (0.45 sec)

我在这里做同样的事情，按下'up'并再次运行最后一个命令，但是输出改变了.这里发生了什么?没有使用该数据库，因为它是我本地笔记本电脑上的副本，供我自己修改.为什么表tblTraceOutput的表行计数发生变化?

Here I am doing the same, pressing 'up' and running the last command again, but the output is chaning. What is going on here? Nothing is using this database as it's a copy on my local laptop for my own tinkering. Why is the table row count changing for table tblTraceOutput?

mysql> SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'smoketrace';
+----------------+------------+
| table_name     | table_rows |
+----------------+------------+
| tblCategories  |          9 |
| tblResults     |      32463 |
| tblRoutes      |        300 |
| tblSettings    |          2 |
| tblTraceOutput |     303463 |
| tblTraces      |         12 |
+----------------+------------+
6 rows in set (0.01 sec)

mysql> SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'smoketrace';
+----------------+------------+
| table_name     | table_rows |
+----------------+------------+
| tblCategories  |          9 |
| tblResults     |      32948 |
| tblRoutes      |        246 |
| tblSettings    |          2 |
| tblTraceOutput |     297319 |
| tblTraces      |         12 |
+----------------+------------+
6 rows in set (0.00 sec)

mysql> SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'smoketrace';
+----------------+------------+
| table_name     | table_rows |
+----------------+------------+
| tblCategories  |          9 |
| tblResults     |      32948 |
| tblRoutes      |        451 |
| tblSettings    |          2 |
| tblTraceOutput |     302127 |
| tblTraces      |         12 |
+----------------+------------+
6 rows in set (0.02 sec)

刷新页面时，我在phpMyAdmin中看到了此行为，因此我想在CLI上进行自我检查，正如您所看到的，它确实正在改变！

I was seeing this behaviour in phpMyAdmin when refreshing the page, so I wanted to check for myself on the CLI and as you can see, it really is changing!

mysql --version
./bin/mysql  Ver 14.14 Distrib 5.5.8, for Linux (i686) using  EditLine wrapper
free -m
             total       used       free     shared    buffers     cached
Mem:          1880       1830         49          0         51        600
-/+ buffers/cache:       1179        701
Swap:         1027          0       1026
uname -a
Linux laptop 3.4.11 #1 SMP Sun Sep 23 15:03:21 BST 2012 i686 i686 i386 GNU/Linux

推荐答案

假设您使用的是InnoDB，因为这是5.5.x中的默认设置. /5.5/zh-CN/tables-table.html"rel =" noreferrer>根据MySQL INFORMATION_SCHEMA TABLES文档进行操作.

Assuming you are using InnoDB, as that is the default in 5.5.x according to the MySQL INFORMATION_SCHEMA TABLES documentation.

此注释:

如果表位于表中，则TABLE_ROWS列为NULL. INFORMATION_SCHEMA数据库.

The TABLE_ROWS column is NULL if the table is in the INFORMATION_SCHEMA database.

对于InnoDB表，行数只是SQL中使用的粗略估计 优化. (如果InnoDB表已分区，也是如此.)

For InnoDB tables, the row count is only a rough estimate used in SQL optimization. (This is also true if the InnoDB table is partitioned.)

这篇关于查询架构时，MySQL的行数会波动吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！