mysql查询耗时波动大,查询架构时，MySQL的行数会波动吗？

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)

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)

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

解决方案

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

And this note:

The TABLE_ROWS column is NULL if the table is in the

INFORMATION_SCHEMA database.

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.)