MySQL中使用count和information_schema.tables计算行数的区别_information.tables

在 MySQL 数据库中，了解如何计算表中的行数对于DBA和开发人员来说至关重要。本文将探讨使用两种不同方法来获取表行数的方法：一种是使用 COUNT 函数，另一种是通过查询 information_schem)a.tables 视图。

本文将比较这两种方法的优缺点，以及何时使用哪种方法更为合适。

作者公号：霸王龙的日常，欢迎关注

数据库：MySQL 8.0.33

1 案例演示

1.1 查询表结构

mysql> SHOW CREATE TABLE trexinfo;
+----------+-----------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+----------+-----------------------------------------------------------------------------------------------------------------------------+
| trexinfo | CREATE TABLE `trexinfo` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键，自增',
  `name` varchar(100) DEFAULT NULL COMMENT '姓名',
  `date_of_birth` date DEFAULT NULL COMMENT '出生日期',
  `gender` enum('Male','Female','Other') DEFAULT NULL COMMENT '性别',
  `email` varchar(100) DEFAULT NULL COMMENT '电子邮件',
  `phone_number` varchar(20) DEFAULT NULL COMMENT '电话号码',
  `address` varchar(255) DEFAULT NULL COMMENT '地址',
  `city` varchar(100) DEFAULT NULL COMMENT '城市',
  `country` varchar(100) DEFAULT NULL COMMENT '国家',
  `postal_code` varchar(20) DEFAULT NULL COMMENT '邮政编码',
  `job_title` varchar(100) DEFAULT NULL COMMENT '职务',
  `department` varchar(100) DEFAULT NULL COMMENT '部门',
  `salary` decimal(10,2) DEFAULT NULL COMMENT '工资',
  `employment_status` enum('全职','兼职','合同工','实习生') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '就业状态',
  `hire_date` date DEFAULT NULL COMMENT '入职日期',
  `termination_date` date DEFAULT NULL COMMENT '离职日期',
  `manager_id` int DEFAULT NULL COMMENT '上级领导ID',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间，默认为当前时间',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间，默认为当前时间，自动更新',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_department` (`department`),
  KEY `idx_job_title` (`job_title`)
) ENGINE=InnoDB AUTO_INCREMENT=11111001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='人员信息表' |
+----------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

1.2 COUNT 函数计算行数

COUNT 函数是 SQL 中常用的聚合函数之一，用于计算查询结果集中的行数。

mysql> select count(*) as table_rows from trexinfo;
+------------+
| table_rows |
+------------+
|   11110000 |
+------------+
1 row in set (2.06 sec)
1
2
3
4
5
6
7

1.3 information_schema.tables 视图获取行数

MySQL 提供了 information_schema.tables 视图，其中包含了有关数据库中所有表的元数据信息，包括行数。可以通过查询该视图来获取表的行数。

mysql> SELECT
    ->     table_rows
    -> FROM
    ->     information_schema.tables
    -> WHERE
    ->     table_schema='trexdb'
    -> AND table_name='trexinfo';
+------------+
| TABLE_ROWS |
+------------+
|    9297613 |
+------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

你可能会有疑问，在执行查询时，或许在数据库后台有其他delete操作影响了结果。

这里声明一下，该数据库为个人测试库，不存在其他操作影响表数据量的情况。

1.4 计算两种查询结果的差异率和差值

mysql> SELECT 
    -> CONCAT(FORMAT((1 - 9297613/11110000) * 100, 2), '%') AS percentage,
    -> 11110000 - 9297613 AS difference;
+------------+------------+
| percentage | difference |
+------------+------------+
| 16.31%     |    1812387 |
+------------+------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9

可以看到两种查询方法得到的结果差异率为16.31%，数据行数相差1812387条。

2 思考

2.1 为什么会出现数据行数不一致的情况？

因为在MySQL8.0的INFORMATION_SCHEMA.TABLES视图中，和统计信息相关的列保存的是缓存值，并不是实时计算的真实值。

2.2 要随时更新给定表的缓存值，如何操作？

可以使用 ANALYZE TABLE ，下面会进行验证。

2.3 缓存值的刷新频率是由什么控制的？

答案是information_schema_stats_expiry 这个变量。该变量表示的是统计信息的过期阈值。默认值为 86400 秒（24 小时）。如果没有缓存的统计信息或者统计信息已过期，则在查询表统计列时从存储引擎检索统计信息。

需要注意一下，MySQL 8.0.0 中引入了 information_schema_stats 变量，后来在 MySQL 8.0.3 中被删除并被 information_schema_stats_expiry 所取代。

要始终直接从存储引擎检索最新统计信息，可将 information_schema_stats_expiry 设置为 0 。

在 information_schema.tables 中，字段 TABLE_ROWS 表示表的行数。据MySQL官方介绍：在MyISAM引擎中存储确切的计数。在 InnoDB 引擎中，该值是一个近似值，可能与实际值存在高达 40% 到 50% 的差异。

3 使用 ANALYZE TABLE 更新表的缓存值

3.1 更新表的缓存值

mysql> ANALYZE TABLE trexinfo;
+-----------------+---------+----------+----------+
| Table           | Op      | Msg_type | Msg_text |
+-----------------+---------+----------+----------+
| trexdb.trexinfo | analyze | status   | OK       |
+-----------------+---------+----------+----------+
1 row in set (0.17 sec)
1
2
3
4
5
6
7

3.2 查询information_schema.tables

mysql> SELECT
    ->     table_rows
    -> FROM
    ->     information_schema.tables
    -> WHERE
    ->     table_schema='trexdb'
    -> AND table_name='trexinfo';
+------------+
| TABLE_ROWS |
+------------+
|   11021699 |
+------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

3.3 计算两种查询结果的百分比和差值

mysql> SELECT 
    -> CONCAT(FORMAT((1 - 11021699/11110000) * 100, 2), '%') AS percentage,
    -> 11110000 - 11021699 AS difference;
+------------+------------+
| percentage | difference |
+------------+------------+
| 0.79%      |      88301 |
+------------+------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9

可以发现，通过使用 ANALYZE TABLE 更新表的缓存值，但和真实行数仍然有差异。

4 设置information_schema_stats_expiry

information_schema_stats_expiry是可在会话级别和全局级别修改的动态参数，单位为：秒。最小值0，最大值31536000(365天)，默认值为86400（一天）。

下面以设置为0进行演示，将 information_schema_stats_expiry 设置为 0 后，information_schema.tables 将始终直接从存储引擎检索最新统计信息。

4.1 查看参数当前值

mysql> show variables like 'information_schema_stats_expiry';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| information_schema_stats_expiry | 86400 |
+---------------------------------+-------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

4.2 设置参数并查看

mysql> set information_schema_stats_expiry = 0;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like 'information_schema_stats_expiry';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| information_schema_stats_expiry | 0     |
+---------------------------------+-------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

4.3 information_schema.tables 视图获取行数

mysql> SELECT
    ->     table_rows
    -> FROM
    ->     information_schema.tables
    -> WHERE
    ->     table_schema='trexdb'
    -> AND table_name='trexinfo';
+------------+
| TABLE_ROWS |
+------------+
|   11021699 |
+------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

查询发现TABLE_ROWS没有发生变化，因为在步骤3中，通过ANALYZE TABLE已经更新到最新的缓存值了。

4.4 插入测试数据

表 trexinfo目前有1111w 条数据，这里往表里再插入11w 条数据进行验证（具体插入命令略）

4.5 COUNT 函数计算行数

mysql> select count(*) as table_rows from trexinfo;
+------------+
| table_rows |
+------------+
|   11220000 |
+------------+
1 row in set (4.84 sec)
1
2
3
4
5
6
7

4.6 information_schema.tables 视图获取行数

mysql> SELECT
    ->     table_rows
    -> FROM
    ->     information_schema.tables
    -> WHERE
    ->     table_schema='trexdb'
    -> AND table_name='trexinfo';
+------------+
| TABLE_ROWS |
+------------+
|   11158572 |
+------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

4.7 计算两种查询结果的百分比和差值

mysql> SELECT
    ->     CONCAT(FORMAT((1 - 11158572/11220000) * 100, 2), '%') AS percentage,
    ->     11220000 - 11158572 AS difference;
+------------+------------+
| percentage | difference |
+------------+------------+
| 0.55%      |      61428 |
+------------+------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9

通过information_schema_stats_expiry设置统计信息的过期阈值，可以有效率的减少统计信息的误差。

该步骤是在回话层面设置的，退出当前回话后则失效，你也可以将information_schema_stats_expiry写入配置文件（my.cnf），重启数据库后永久生效，可自行配置。

5 区别

性能：

• • 使用 COUNT 函数查询会直接扫描表并计算行数，可能会导致较大的性能开销，特别是表非常庞大时。
• • 查询 information_schema.tables 视图不会对表进行扫描，而是返回缓存值，因此通常性能更好。

实时性：

• • 使用 COUNT 函数可以获得实时的行数，反映了当前表的状态。
• • 查询 information_schema.tables 视图返回的行数是近似值，特别是在高负载的数据库中，如果表的数据变化频繁，而统计信息未及时更新，那么查询结果会不准确。

权限：

• • 使用 COUNT 函数需要对表具有查询权限。
• • 查询 information_schema.tables 视图通常需要对 information_schema 数据库的访问权限。

用途：

• • 使用 COUNT 函数适用于需要实时和准确的行数的场景，例如对于数据分析和报告。
• • 查询 information_schema.tables 视图适用于获取表的近似行数，对于监控和性能调优等情况非常有用。

6 总结

虽然使用 COUNT 函数和查询 information_schema.tables 视图都可以获取表的行数，但它们各有优缺点。选择合适的方法取决于具体需求，如果需要实时和准确的行数，可以使用 COUNT 函数。而如果需要较快的性能且可以接受近似值，则可以查询 information_schema.tables 视图。在实际应用中，可以根据场景灵活选择适当的方法。