Navicat 博客

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% 以内。

Navicat 文章
频道条目
分享
文章归档