关于关系数据库如何快速查询表的记录数详解

亚梨發表於 2023-8-16 00:00:00

关于关系数据库如何快速查询表的记录数详解

前言

在数据库中，很多人员习惯使用<code>SELECT COUNT（*）</code> 、<code>SELECT COUNT（1)</code> 、<code>SELECT COUNT(COL)</code>来查询一个表有多少记录，对于小表，这种SQL的开销倒不是很大，但是对于大表，这种查询表记录数的做法就是一个非常消耗资源了，而且效率很差。下面介绍一下SQL Server、 Oracle、MySQL中如何快速获取表的记录数。

SQL SERVER 数据库

在SQL Server数据库中, 对象目录视图sys.partitions中有一个字段rows会记录表的记录数。我们以AdventureWorks2014为测试数据库。

<img title="关于关系数据库如何快速查询表的记录数详解" alt="关于关系数据库如何快速查询表的记录数详解" class="medium-zoom-image" data-tag="bdshare" src="https://zhuji.jb51.net/uploads/img/202305/b976dce4537c56b6f5403d83752a7898.jpg">
<div class="jb51code">
SELECT OBJECT_NAME(object_id) AS Object_Name, i.name AS Index_Name, p.rows AS Table_Rows FROM sys.partitions p LEFT JOIN sys.sysindexes i ON p.object_id = i.id AND p.index_id = i.indid WHERE object_id = OBJECT_ID('TableName')</div>

<img title="关于关系数据库如何快速查询表的记录数详解" alt="关于关系数据库如何快速查询表的记录数详解" class="medium-zoom-image" data-tag="bdshare" src="https://zhuji.jb51.net/uploads/img/202305/37697f98f8a9bdc3fe9422e51e78f636.jpg">


那么我们还有一些疑问，我们先来看看这些问题吧！



1：没有索引的表是否也可以使用上面脚本？

2：只有非聚集索引的堆表是否可以使用上面脚本？

3：有多个索引的表，是否记录数会存在不一致的情况？

4：统计信息不准确的表，是否rows也会不准确

5: 分区表的情况又是怎么样？

6：对象目录视图<code>sys.partitions</code>与sp_spaceused获取的表记录函数是否准确。

如下所示，我们先构造测试案例：
<div class="jb51code">
IF EXISTS(SELECT 1 FROM sys.objects WHERE type='U' AND name='TEST_TAB_ROW') BEGIN DROP TABLE TEST_TAB_ROW; END IF NOT EXISTS(SELECT 1 FROM sys.objects WHERE type='U' AND name='TEST_TAB_ROW') BEGIN CREATE TABLE TEST_TAB_ROW ( ID INT, NAME CHAR(200) ) END GO SET NOCOUNT ON; BEGIN TRAN DECLARE @Index INT =1; WHILE @Index <= 100000 BEGIN INSERT INTO TEST_TAB_ROW VALUES(@Index, NEWID()); SET @Index+=1; IF (@Index % 5000) = 0 BEGIN IF @@TRANCOUNT > 0 BEGIN COMMIT; BEGIN TRAN END END END IF @@TRANCOUNT > 0 BEGIN COMMIT; END GO</div>

关于问题1、问题2，都可以使用上面脚本，如下测试所示：
<div class="jb51code">
SELECT OBJECT_NAME(object_id) AS Object_Name, i.name AS Index_Name, p.rows AS Table_Rows FROM sys.partitions p LEFT JOIN sys.sysindexes i ON p.object_id = i.id AND p.index_id = i.indid WHERE object_id = OBJECT_ID('dbo.TEST_TAB_ROW')</div>

<img title="关于关系数据库如何快速查询表的记录数详解" alt="关于关系数据库如何快速查询表的记录数详解" class="medium-zoom-image" data-tag="bdshare" src="https://zhuji.jb51.net/uploads/img/202305/edd25ff8505e6e42d189ad03fbcecdb1.jpg">

在表dbo.TEST_TAB_ROW 上创建非聚集索引后，查询结果如下所示：
<div class="jb51code">
CREATE INDEX IX_TEST_TAB_ROW ON TEST_TAB_ROW(ID);</div>

<img title="关于关系数据库如何快速查询表的记录数详解" alt="关于关系数据库如何快速查询表的记录数详解" class="medium-zoom-image" data-tag="bdshare" src="https://zhuji.jb51.net/uploads/img/202305/217c897a00a33979350f5529c09c48f6.jpg">

我们插入500条记录，此时，这个数据量不足以触发统计信息更新，如下所示， Rows Sampled还是1000000
<div class="jb51code">
DECLARE @Index INT =1; WHILE @Index <= 500 BEGIN INSERT INTO TEST_TAB_ROW VALUES(100000 +@Index, NEWID()); SET @Index+=1; END</div>

<img title="关于关系数据库如何快速查询表的记录数详解" alt="关于关系数据库如何快速查询表的记录数详解" class="medium-zoom-image" data-tag="bdshare" src="https://zhuji.jb51.net/uploads/img/202305/c55a4fb3c478be2ba5f6ff9364165372.jpg">

如下所示，发现<code>sys.partitions</code>中的记录变成了100500了，可见rows这个值的计算不依赖统计信息。

<img title="关于关系数据库如何快速查询表的记录数详解" alt="关于关系数据库如何快速查询表的记录数详解" class="medium-zoom-image" data-tag="bdshare" src="https://zhuji.jb51.net/uploads/img/202305/d78b401666e47badb873073100c5852b.jpg">

当然，如果你用sp_spaceused，发现这里面的记录也是100500

<code>sp_spaceused 'dbo.TEST_TAB_ROW'</code>

<img title="关于关系数据库如何快速查询表的记录数详解" alt="关于关系数据库如何快速查询表的记录数详解" class="medium-zoom-image" data-tag="bdshare" src="https://zhuji.jb51.net/uploads/img/202305/d195cf6ee4793193edff4974c9c3fce0.jpg">

关于问题3：有多个索引的表，是否记录数会存在不一致的情况？

答案：个人测试以及统计来看，暂时发现多个索引的情况下，sys.partitions中的rows记录数都是一致的。暂时没有发现不一致的情况，当然也不排除有特殊情况。

关于问题5: 分区表的情况又是怎么样？

答案：分区表和普通表没有任何区别。

关于问题6：对象目录视图sys.partitions与sp_spaceused获取的表记录函数是否准确？

答案：对象目录视图sys.partitions与sp_spaceused获取的表记录数是准确的。

ORACLE 数据库

在ORACLE数据库中，可以通过DBA_TABLES、ALL_TABLES、USER_TABLES视图查看表的记录数，不过这个值（NUM_ROWS）跟统计信息有很大的关系，有时候统计信息没有更新或采样比例会导致这个值不是很准确。
<div class="jb51code">
SELECT OWNER , TABLE_NAME, NUM_ROWS , LAST_ANALYZED FROM DBA_TABLES WHERE OWNER = '&OWNER' AND TABLE_NAME = '&TABLE_NAME'; SELECT OWNER, TABLE_NAME, NUM_ROWS , LAST_ANALYZED FROM ALL_TABLES WHERE OWNER ='&OWNER' AND TABLE_NAME='&TABLE_NAME'; SELECT TABLE_NAME, NUM_ROWS , LAST_ANALYZED FROM USER_TABLES WHERE TABLE_NAME='&TABLE_NAME'</div>

更新统计信息后，就能得到准确的行数。所以如果需要得到正确的数据，最好更新目标表的统计信息，进行100%采样分析。对于分区表，那么就需要从dba_tab_partitions里面查询相关数据了。

<code>SQL>execute dbms_stats.gather_table_stats(ownname => 'username', tabname =>'tablename', estimate_percent =>100, cascade=>true);</code>

MySQL数据库

在MySQL中比较特殊，虽然<code>INFORMATION_SCHEMA.TABLES</code>也可以查到表的记录数，但是非常不准确。如下所示，即使使用ANALYZE TABLE更新了统计信息，从<code>INFORMATION_SCHEMA.TABLES</code>中获取的记录依然不准确
<div class="jb51code">
SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='table_name'</div>

<div class="jb51code">
mysql> SELECT TABLE_ROWS -> FROM INFORMATION_SCHEMA.TABLES -> WHERE TABLE_NAME='jiraissue' -> ; +------------+ | TABLE_ROWS | +------------+ | 36487 | +------------+ 1 row in set (0.01 sec) mysql> select count(*) from jiraissue; +----------+ | count(*) | +----------+ | 36973 | +----------+ 1 row in set (0.05 sec) mysql> analyze table jiraissue; +----------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------------+---------+----------+----------+ | jira.jiraissue | analyze | status | OK | +----------------+---------+----------+----------+ 1 row in set (1.41 sec) mysql> SELECT TABLE_ROWS -> FROM INFORMATION_SCHEMA.TABLES -> WHERE TABLE_NAME='jiraissue'; +------------+ | TABLE_ROWS | +------------+ | 34193 | +------------+ 1 row in set (0.00 sec) mysql></div>
<div class="jb51code">
</div>

<img title="关于关系数据库如何快速查询表的记录数详解" alt="关于关系数据库如何快速查询表的记录数详解" class="medium-zoom-image" data-tag="bdshare" src="https://zhuji.jb51.net/uploads/img/202305/df7592b256ffb0314c1f65015d457941.jpg">

<img title="关于关系数据库如何快速查询表的记录数详解" alt="关于关系数据库如何快速查询表的记录数详解" class="medium-zoom-image" data-tag="bdshare" src="https://zhuji.jb51.net/uploads/img/202305/cd7acb905958da7c483b5adad7047be8.jpg">

如上所示，MySQL这种查询表记录数的方法看来还是有缺陷的。当然如果不是要求非常精确的值，这个方法也是不错的。

当然，上面介绍的SQL Server、Oracle、MySQL数据库中的方法，还是有一些局限性的。例如，只能查询整张表的记录数，对于那些查询记录数带有查询条件（WHERE）这类SQL。还是必须使用SELECT COUNT(*）这种方法。

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对的支持。

原文链接：https://www.cnblogs.com/kerrycode/p/6830970.html

頁: [1]

琼殿技术论坛's Archiver

关于关系数据库如何快速查询表的记录数详解