在 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 COUNT 函数计算行数
COUNT 函数是 SQL 中常用的聚合函数之一,用于计算查询结果集中的行数。
mysql> select count(*) as table_rows from trexinfo;
+------------+
| table_rows |
+------------+
| 11110000 |
+------------+
1 row in set (2.06 sec)
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)
你可能会有疑问,在执行查询时,或许在数据库后台有其他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)
可以看到两种查询方法得到的结果差异率为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)
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)
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)
可以发现,通过使用 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)
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)
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)
查询发现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)
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)
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)
通过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
视图。在实际应用中,可以根据场景灵活选择适当的方法。