我想计算一个有3.5M行的表的行数：
DESC rp_uploadFile;
+---------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+--------------+------+-----+---------+----------------+
| rp_uploadFileID | int(11) | NO | PRI | NULL | auto_increment |
| rp_uploadFileTypeID | int(11) | NO | MUL | NULL | |
| rp_clientID | int(11) | NO | MUL | NULL | |
| rp_facilityID | mediumint(4) | YES | MUL | NULL | |
| rp_groupID | int(11) | YES | MUL | NULL | |
| rp_userID | int(11) | NO | | NULL | |
| rp_dateUpload | datetime | NO | | NULL | |
| rp_extensionCode | varchar(5) | NO | | NULL | |
| rp_fileName | varchar(255) | NO | MUL | NULL | |
| rp_folderID | int(11) | NO | | NULL | |
| rp_isActive | tinyint(1) | NO | MUL | NULL | |
| rp_type | tinyint(1) | NO | | NULL | |
+---------------------+--------------+------+-----+---------+----------------+
12 rows in set (0,000 sec)这些指标：
SHOW INDEXES FROM rp_uploadFile;
+---------------+------------+-----------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+-----------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| rp_uploadFile | 0 | PRIMARY | 1 | rp_uploadFileID | A | 3315091 | NULL | NULL | | BTREE | | |
| rp_uploadFile | 1 | rp_uploadFileTypeID | 1 | rp_uploadFileTypeID | A | 24 | NULL | NULL | | BTREE | | |
| rp_uploadFile | 1 | rp_clientID | 1 | rp_clientID | A | 1105030 | NULL | NULL | | BTREE | | |
| rp_uploadFile | 1 | rp_groupID | 1 | rp_groupID | A | 216 | NULL | NULL | YES | BTREE | | |
| rp_uploadFile | 1 | rp_fileName | 1 | rp_fileName | A | 3315091 | NULL | NULL | | BTREE | | |
| rp_uploadFile | 1 | rp_uploadFileTypeID_2 | 1 | rp_facilityID | A | 4818 | NULL | NULL | YES | BTREE | | |
| rp_uploadFile | 1 | rp_uploadFileTypeID_2 | 2 | rp_uploadFileTypeID | A | 14351 | NULL | NULL | | BTREE | | |
| rp_uploadFile | 1 | rp_uploadFileTypeID_2 | 3 | rp_isActive | A | 14733 | NULL | NULL | | BTREE | | |
| rp_uploadFile | 1 | rp_isActive | 1 | rp_isActive | A | 2 | NULL | NULL | | BTREE | | |
+---------------+------------+-----------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
9 rows in set (0,000 sec)这个很长：
SELECT COUNT(1) FROM `rp_uploadFile`;
+----------+
| COUNT(1) |
+----------+
| 3299571 |
+----------+
1 row in set (0,688 sec)而这个带有rowCount的查询最长
SELECT rp_uploadFileID FROM `rp_uploadFile`;
....
3299577 rows in set (1,128 sec)这是伟大的：
SHOW TABLE STATUS LIKE 'rp_uploadFile';
+---------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | Max_index_length | Temporary |
+---------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
| rp_uploadFile | InnoDB | 10 | Dynamic | 3315119 | 124 | 411959296 | 0 | 723451904 | 12582912 | 5085809 | 2023-01-05 14:52:07 | 2023-10-16 13:54:33 | NULL | utf8_general_ci | NULL | | | 0 | N |
+---------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
1 row in set (0,001 sec)但我不喜欢在业务中使用这种系统查询（SHOW TABLE STATUS）。
你有办法更快地数这些行吗？
非常感谢
1条答案
按热度按时间1szpjjfi1#
TABLE_ROWS只是对InnoDB表的估计。在支持事务隔离的存储引擎中，行数不是一个简单的答案。
因此INFORMATION_SCHEMA（或SHOW TABLE STATUS）不是获取行计数的正确位置。
这也指出了为什么在InnoDB上执行一个查询需要很长时间。它必须评估表中的每一行，以查看该行是否属于数据库的事务视图。这是使用ACID数据库的缺点。
如果您需要以高性能访问计数，则需要跟踪作为聚合查询结果的计数，并将该值存储在其他地方，例如另一个表或内存中的缓存。