计算非常大的表中的确切行数的最快方法？-Java 学习之路

191

我遇到过一些文章，指出当表有很多行和很多列时 SELECT COUNT(*) FROM TABLE_NAME 会很慢 .

我有一个表可能包含数十亿行[它有大约15列] . 有没有更好的方法来获取表的行数 EXACT ？

请在回答之前考虑以下事项：

我正在寻找一个独立于数据库供应商的解决方案 . 如果它涵盖 MySQL ， Oracle ， MS SQL Server ，则可以 . 但是，如果没有数据库供应商独立的解决方案，那么我将为不同的数据库供应商寻求不同的解决方案 .
我无法使用任何其他外部工具来执行此操作 . 我主要是在寻找基于SQL的解决方案 .
我无法进一步规范化我的数据库设计 . 它已经在3NF，而且已经编写了很多代码 .

24 回答

1
如果您的 table 上有某个主键（唯一值），则可以使用 MAX(yourId) 来基本上为您提供总行数 . 以下是示例代码段：
```
SELECT MAX(yourId)
FROM YourTable
```
回复于 2024-05-03T18:28:27+08:00
5

也许有点晚了，但这可能对MSSQL有帮助

; WITH RecordCount AS（SELECT ROW_NUMBER（）OVER（ORDER BY COLUMN_NAME）AS [RowNumber] FROM TABLE_NAME）SELECT MAX（RowNumber）FROM RecordCount

回复于 2024-05-03T18:28:27+08:00
0

从sysindexes中选择行，其中id = Object_ID（'TableName'）和indid <2

回复于 2024-05-03T18:28:27+08:00
207

在某些列上添加索引 . 这应该允许优化器执行索引块的完整扫描，而不是对表的完整扫描 . 这将降低您的IO成本 . 查看前后的执行计划 . 然后双向测量挂钟时间 .

回复于 2024-05-03T18:28:27+08:00
4
如果您使用的是Oracle，那么（假设表统计信息已更新）：
```
select <TABLE_NAME>, num_rows, last_analyzed from user_tables
```
last_analyzed将显示上次收集统计信息的时间 .
回复于 2024-05-03T18:28:27+08:00

使用PostgreSQL：

SELECT reltuples AS approximate_row_count FROM pg_class WHERE relname = 'table_name'

回复于 2024-05-03T18:28:27+08:00

3
Simple answer:
- 数据库供应商独立解决方案=使用标准= COUNT(*)
- 有大致的SQL Server解决方案，但不使用COUNT（*）=超出范围
Notes:

COUNT(1) = COUNT(*) = COUNT(PrimaryKey)以防万一

Edit:

SQL Server示例（14亿行，12列）
```
SELECT COUNT(*) FROM MyBigtable WITH (NOLOCK)
-- NOLOCK here is for me only to let me test for this answer: no more, no less
```
1次运行，5：46分钟，计数= 1,401,659,700
```
--Note, sp_spaceused uses this DMV
SELECT
   Total_Rows= SUM(st.row_count)
FROM
   sys.dm_db_partition_stats st
WHERE
    object_name(object_id) = 'MyBigtable' AND (index_id < 2)
```
2次运行，均低于1秒，计数= 1,401,659,670

第二个有较少的行=错误 . 取决于写入将是相同或更多（删除是在几小时内完成的）
回复于 2024-05-03T18:28:27+08:00
4
到目前为止，MySQL上最快的方法是：
```
SHOW TABLE STATUS;
```
如果需要，您将立即获得包含行数（即总数）的所有表格以及大量额外信息 .
回复于 2024-05-03T18:28:27+08:00
24

我遇到过一些文章，指出当表有很多行和很多列时，SELECT COUNT（*）FROM TABLE_NAME会很慢 .

这取决于数据库 . 一些加速计数，例如通过跟踪索引中的行是活的还是死的，允许仅索引扫描来提取行数 . 其他人则没有，因此需要访问整个表格并逐个计算实时行 . 对于一张巨大的 table ，要么会很慢 .

请注意，您通常可以通过使用查询优化工具，表统计信息等来提取良好的估计值 . 例如，在PostgreSQL的情况下，您可以解析 explain count(*) from yourtable 的输出并获得相当好的行数估计值 . 这让我想到了你的第二个问题 .

我有一个表可能包含数十亿行[它有大约15列] . 有没有更好的方法来获得表的行数的精确计数？

真的吗？ :-)你真的是指具有数十亿行的表中的 exact 计数？你确定吗？ :-)

如果你真的这样做，你可以保留一下总的使用触发器，但如果你这样做，请注意并发和死锁 .

回复于 2024-05-03T18:28:27+08:00
2

你可以试试这个sp_spaceused (Transact-SQL)

显示当前数据库中的表，索引视图或Service Broker队列使用的行数，保留的磁盘空间和磁盘空间，或显示整个数据库保留和使用的磁盘空间 .

回复于 2024-05-03T18:28:27+08:00
2
有没有更好的方法来获得表的行数的精确计数？

简单地回答你的问题， No .

如果您需要以DBMS独立的方式执行此操作，最快的方法将始终是：
```
SELECT COUNT(*) FROM TableName
```
一些DBMS供应商可能有更快的方法，只适用于他们的系统 . 其中一些选项已经发布在其他答案中 .

COUNT(*) 应该由DBMS（至少任何PROD值得的数据库）进行优化，所以不要试图绕过他们的优化 .

旁注：
我相信你的许多其他查询也需要很长时间才能完成，因为你的表格大小 . 任何性能问题都应该通过考虑速度考虑您的架构设计来解决 . 我意识到你说它不是一个可以改变的选项，但可能会发现10分钟的查询并不是一起存储的.1035987_ t have . 需要考虑的事情......
回复于 2024-05-03T18:28:27+08:00

我用

select /*+ parallel(a) */  count(1) from table_name a;

回复于 2024-05-03T18:28:27+08:00

5
我已经远远不及那些已经回答的其他专家，但我遇到的问题是我用来从表中选择一个随机行的过程（不过分相关）但我需要知道我的参考表中的行数计算随机指数 . 使用传统的Count（*）或Count（1）工作，但偶尔我的查询运行时间最长为2秒 . 所以相反（我的名为'tbl_HighOrder'的表）我正在使用：
```
Declare @max int

Select @max = Row_Count
From sys.dm_db_partition_stats
Where Object_Name(Object_Id) = 'tbl_HighOrder'
```
它工作得很好，Management Studio中的查询时间为零 .
回复于 2024-05-03T18:28:27+08:00

-3

如果SQL Server版本是2005/2008，则可以使用DMV计算表中的行数：

-- Shows all user tables and row counts for the current database 
-- Remove is_ms_shipped = 0 check to include system objects 
-- i.index_id < 2 indicates clustered index (1) or hash table (0) 
SELECT o.name, 
 ddps.row_count 
FROM sys.indexes AS i 
 INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID 
 INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID 
 AND i.index_id = ddps.index_id 
WHERE i.index_id < 2 
 AND o.is_ms_shipped = 0 
ORDER BY o.NAME

对于SQL Server 2000数据库引擎，sysindexes将起作用，但强烈建议避免在SQL Server的未来版本中使用它，因为它可能在不久的将来被删除 .

示例代码取自：How To Get Table Row Counts Quickly And Painlessly

回复于 2024-05-03T18:28:27+08:00

1

好吧，迟到5年，不确定它是否有帮助：

我试图算不上 . SQL中的行数服务器表使用 MS SQL Server Management Studio 并遇到一些溢出错误，然后我用下面的：

select count_big （1）FROM [dbname] . [dbo] . [FactSampleValue];

结果：

24296650578行

回复于 2024-05-03T18:28:27+08:00
2

我不认为有一个通用的最快的解决方案：一些RDBMS /版本对 SELECT COUNT(*) 进行了特定的优化，使用更快的选项而其他只是表扫描 . 您需要转到第二组的文档/支持站点，这可能需要一些更具体的查询来编写，通常是以某种方式命中索引的查询 .

编辑：

这是一个可能有效的思路，具体取决于您的架构和数据分布：您是否有一个索引列引用了一个增加的值，一个数字增加的ID，比如说，甚至是一个时间戳或日期？然后，假设删除没有发生，应该可以将计数存储到某个最近的值（昨天的日期，某个最近的样本点的最高ID值）并添加超出该值的计数，这应该在索引中很快解决 . 当然，它非常依赖于值和索引，但适用于任何DBMS的任何版本 .

回复于 2024-05-03T18:28:27+08:00
7
不完全是与DBMS无关的解决方案，但至少您的客户端代码不会看到差异......

创建另一个表T只有一行和一个整数字段N1，并创建刚刚执行的INSERT TRIGGER：
```
UPDATE T SET N = N + 1
```
还要创建一个执行的DELETE TRIGGER：
```
UPDATE T SET N = N - 1
```
一个值得它的盐的DBMS将保证上面的操作的原子性，并且N将始终包含准确的行数，然后通过简单的超快速获得：
```
SELECT N FROM T
```
虽然触发器是特定于DBMS的，但从T中选择不是，并且您的客户端代码不需要为每个受支持的DBMS进行更改 .

但是，如果表是INSERT或DELETE密集型，则可能会出现一些可伸缩性问题，尤其是在INSERT / DELETE之后不立即执行COMMIT时 .

1这些名称只是占位符 - 在生产环境中使用更有意义的东西 .

2 I.e.只要读取和写入都在单个SQL语句中完成，就不能通过读取和写入N之间的并发事务来更改N.
回复于 2024-05-03T18:28:27+08:00
0
一个字面上疯狂的答案，但如果你设置了某种复制系统（对于一个有十亿行的系统，我希望你这样做），你可以使用粗略估计器（如 MAX(pk) ），将该值除以你有奴隶，并行运行几个查询 .

在大多数情况下，你会根据最好的密钥（或者我猜的主键）在奴隶之间划分查询，这样（我们将使用250000000作为我们的Rows / Slaves）：
```
-- First slave
SELECT COUNT(pk) FROM t WHERE pk < 250000000
-- Ith slave where 2 <= I <= N - 1
SELECT COUNT(pk) FROM t WHERE pk >= I*250000000 and pk < (I+1)*250000000
-- Last slave
SELECT COUNT(pk) FROM t WHERE pk > (N-1)*250000000
```
但是你只需要SQL . 真是太棒了 . 好吧，让我们说你是一个虐恋者 . 在主服务器（或最近的服务器）上，您很可能需要为此创建一个表：
```
CREATE TABLE counter_table (minpk integer, maxpk integer, cnt integer, slaveid integer)
```
因此，您不必只在选择器中运行选择，而是必须执行插入操作，类似于：
```
INSERT INTO counter_table VALUES (I*25000000, (I+1)*250000000, (SELECT COUNT(pk) FROM ... ), @@SLAVE_ID)
```
您可能会遇到从属写入主表上的表的问题 . 你可能需要更多悲伤 - 我的意思是，创造性：
```
-- A table per slave!
INSERT INTO counter_table_slave_I VALUES (...)
```
最后，您应该在复制图遍历的路径中最后存在一个从属设备，相对于第一个从属设备 . 该奴隶现在应该拥有所有其他计数器值，并且应该有自己的值 . 但是当你完成时，可能会添加行，所以你必须插入另一行来补偿你的counter_table中记录的最大pk和当前的最大pk .

此时，您必须执行聚合函数来确定总行数，但这更容易，因为您最多只运行“您拥有并更改”的行数 .

如果您处于从属中具有单独表的情况，则可以 UNION 获取所需的所有行 .
```
SELECT SUM(cnt) FROM (
    SELECT * FROM counter_table_slave_1
      UNION
    SELECT * FROM counter_table_slave_2
      UNION
    ...
  )
```
或者你知道，不那么疯狂并将你的数据迁移到分布式处理系统，或者使用数据仓库解决方案（这将在未来为你提供令人敬畏的数据处理） .

请注意，这取决于您的复制设置的好坏 . 由于主要瓶颈很可能是持久性存储，如果您的存储不稳定或隔离度较差的数据存储具有较大的邻居噪声，这可能会比等待单个 SELECT COUNT(*) ... 更慢 .

但是如果你有很好的复制，那么你的速度增益应该与数量或奴隶直接相关 . 事实上，如果单独运行计数查询需要10分钟，并且您有8个奴隶，那么您将把时间减少到不到几分钟 . 也许一个小时来解决这个解决方案的细节 .

当然，你从来没有真正得到一个非常准确的答案，因为这种分布式求解会引入一些时间来删除和插入行，但是你可以尝试在同一个实例中获得分布式的行锁并获得精确的计数表中特定时刻的行数 .

实际上，这似乎是不可能的，因为你基本上坚持使用仅限SQL的解决方案，而且我认为你不是提供了一种机制，可以立即跨多个从站运行分片和锁定查询 . 也许如果您控制了复制日志文件......这意味着您实际上就是为了这个目的而旋转奴隶，这无疑比仅在一台机器上运行计数查询要慢 .

所以这是我的两个2013年的便士 .
回复于 2024-05-03T18:28:27+08:00
10

If insert trigger is too expensive to use, but a delete trigger could be afforded, and there is an auto-increment id ，然后计算整个表一次，并记住计数为 last-count 和 last-counted-id ，

然后 each day 只需要计算 id > last-counted-id ，将其添加到 last-count ，并存储新的 last-counted-id .

如果已删除记录的id <= last-counted-id，则删除触发器将减少last-count .

回复于 2024-05-03T18:28:27+08:00
1
我迟到了这个问题，但这是你可以用MySQL做什么（因为我使用MySQL） . 我在这里分享我的观察：
```
1) SELECT COUNT(*) AS TOTAL_ROWS FROM <TABLE_NAME>
```
Result
行数： 508534
控制台输出：受影响的行：0找到行：1警告：0 1个查询的持续时间：0.125秒 .
对于具有大量行的表需要一段时间，但行计数非常精确 .
```
2) SHOW TABLE STATUS or SHOW TABLE STATUS WHERE NAME="<TABLE_NAME>"
```
Result
行数： 511235
控制台输出：受影响的行：0找到行：1警告：0 1个查询的持续时间：0.250秒摘要：行计数不准确 .
```
3) SELECT * FROM information_schema.tables WHERE table_schema = DATABASE();
```
Result
行数： 507806
控制台输出：受影响的行：0找到行：48警告：0 1个查询的持续时间：1.701秒 .
行数不准确 .

我不是MySQL或数据库专家，但我发现对于非常大的表，您可以使用选项2或3，并获得存在多少行的“公平想法” .

我需要获取这些行数来在UI上显示一些统计信息 . 通过上面的查询，我知道总行数超过500,000，所以我想出了“超过500,000行”的统计数据而没有显示确切的行数 .

也许我没有真正回答OP的问题，但我正在分享我在需要此类统计数据的情况下所做的事情 . 在我的情况下，显示大概的行是可以接受的，所以上面的工作对我来说 .
回复于 2024-05-03T18:28:27+08:00
0
如果您具有自动递增主键列的典型表结构，其中行永远不会被删除，则以下将是确定记录计数的最快方法，并且应该在大多数ANSI兼容数据库中工作方式类似：
```
SELECT TOP(1) <primarykeyfield> FROM <table> ORDER BY <primarykeyfield> DESC;
```
我使用包含数十亿行的MS SQL表，这些行需要亚秒的数据响应时间，包括记录计数 . 类似的SELECT COUNT（*）通过比较需要几分钟才能处理 .
回复于 2024-05-03T18:28:27+08:00

对于Sql server，试试这个

SELECT T.name, 
       I.rows AS [ROWCOUNT] 
FROM   sys.tables AS T 
       INNER JOIN sys.sysindexes AS I 
               ON T.object_id = I.id AND I.indid < 2 
WHERE T.name = 'Your_Table_Name'
ORDER  BY I.rows DESC

回复于 2024-05-03T18:28:27+08:00

-2
我从 martijnh1 找到了这篇好文章SQL Server–HOW-TO: quickly retrieve accurate row count for table，它给出了每个场景的一个很好的回顾 .

我需要扩展我需要提供基于特定条件的计数，当我想到这一部分时，我将进一步更新这个答案 .

与此同时，以下是文章的详细信息：

Method 1:

查询：
```
SELECT COUNT(*) FROM Transactions
```
评论：

执行全表扫描 . 在大 table 上慢

Method 2:

查询：
```
SELECT CONVERT(bigint, rows) 
FROM sysindexes 
WHERE id = OBJECT_ID('Transactions') 
AND indid < 2
```
评论：

快速检索行数的方法 . 取决于统计数据并且不准确 .

使用COUNT_ROWS运行DBCC UPDATEUSAGE（数据库），这可能会占用大型表的大量时间 .

Method 3:

查询：
```
SELECT CAST(p.rows AS float) 
FROM sys.tables AS tbl 
INNER JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id and
idx.index_id < 2 
INNER JOIN sys.partitions AS p ON p.object_id=CAST(tbl.object_id AS int) 
AND p.index_id=idx.index_id 
WHERE ((tbl.name=N'Transactions' 
AND SCHEMA_NAME(tbl.schema_id)='dbo'))
```
评论：

SQL管理工作室计算行的方式（查看表属性，存储，行计数） . 非常快，但仍然是大约行数 .

Method 4:

查询：
```
SELECT SUM (row_count) 
FROM sys.dm_db_partition_stats 
WHERE object_id=OBJECT_ID('Transactions')    
AND (index_id=0 or index_id=1);
```
评论：

快速（虽然不如方法2快）操作同样重要，可靠 .
回复于 2024-05-03T18:28:27+08:00
8
我从另一个StackOverflow问题/答案得到了这个脚本：
```
SELECT SUM(p.rows) FROM sys.partitions AS p
  INNER JOIN sys.tables AS t
  ON p.[object_id] = t.[object_id]
  INNER JOIN sys.schemas AS s
  ON s.[schema_id] = t.[schema_id]
  WHERE t.name = N'YourTableNameHere'
  AND s.name = N'dbo'
  AND p.index_id IN (0,1);
```
我的表有5亿条记录，上述回报不到1毫秒 . 与此同时，
```
SELECT COUNT(id) FROM MyTable
```
需要整整39分52秒！

它们产生完全相同的行数（在我的情况下，恰好是519326012） .

我不知道是否总会如此 .
回复于 2024-05-03T18:28:27+08:00

计算非常大的表中的确切行数的最快方法？

24 回答

相关问题