2018 年 4 月 10 日，由 Robert Gravelle 撰写

在上周的在 MySQL 中获取高级的行计数(第 2 部分)文章中，我们使用了原生的 COUNT() 函数来计算唯一值以及满足条件的值。在今天的最后一部分中，我们将学习如何从数据库或整个模式中的所有表中获取行计数。

查询 information_schema 数据库

你不必对每个表运行计数查询以获取行数。如果你计划多次运行它，这将是繁琐的并且可能需要外部脚本。

INFORMATION_SCHEMA 数据库是每个 MySQL 实例存储有关 MySQL 服务器维护的所有其他数据库的信息的位置。它有时也称为数据字典和系统目录，是查找有关数据库、表、列的数据类型或访问权限的信息的理想位置。

INFORMATION_SCHEMA“TABLES”表提供有关数据库中还有哪些其他表的信息。通过查询“TABLES”表，你可以使用单个查询获得精确的行计数。

一个数据库的表计数

获取一个数据库的行计数很容易。只需添加一个 WHERE 子句，其条件是 table_schema 列与你的数据库名匹配：

SELECT
TABLE_NAME,
TABLE_ROWS
FROM
`information_schema`.`tables`
WHERE
`table_schema` = 'YOUR_DB_NAME';
+------------+------------+
| TABLE_NAME | TABLE_ROWS |
+------------+------------+
| Table1     | 105        |
+------------+------------+
| Table2     | 10299      |
+------------+------------+
| Table3     | 0          |
+------------+------------+
| Table4     | 1045       |
+------------+------------+

整个模式的表计数

获取模式中所有数据库的行数需要更多的工作量。为此，我们必须使用已准备的语句。

在语句中，group_concat() 函数将多行打包成一个字符串，以便将表名列表转换为由联合连接的多个计数的一个字符串。

Select
-- Sort the tables by count
concat(
'select * from (',
-- Aggregate rows into a single string connected by unions
group_concat(
-- Build a "select count(1) from db.tablename" per table
concat('select ',
quote(db), ' db, ',
quote(tablename), ' tablename, '
'count(1) "rowcount" ',
'from ', db, '.', tablename)
separator ' union ')
, ') t order by 3 desc')
into @sql
from (
select
table_schema db,
table_name tablename
from information_schema.tables
where table_schema not in
('performance_schema', 'mysql', 'information_schema')
) t;

我们的串联 select 语句保存在 @sql 变量中，以便我们可以将它作为已准备的语句运行：

-- Execute @sql
prepare s from @sql; execute s; deallocate prepare s;
+-----+-----------+------------+
| db  | tablename | rowcount   |
+-----+-----------+------------+
| DB1 | Table1    | 1457       |
+-----+-----------+------------+
| DB1 | Table2    | 1029       |
+-----+-----------+------------+
| DB2 | Table1    | 22002      |
+-----+-----------+------------+
| DB2 | Table2    | 1022       |
+-----+-----------+------------+

关于速度和准确性

这些查询在 MyISAM 表上运行得非常快，并生成非常精确的结果。但是，如 InnoDB 之类的事务存储引擎不会在表中保留内部的行计数。而是，在表中采样许多随机页，然后估计整个表的总行数。MVCC(一种允许并发访问行的功能)的后果是，在任何一个时间点，都会有多个版本的行。因此，实际的 count(1) 将取决于事务开始的时间及其隔离级别。像 InnoDB 这样的事务存储引擎，你可以预料其计数的准确性为实际行数的 ±4% 以内。