關(guān)于關(guān)系數(shù)據(jù)庫如何快速查詢表的記錄數(shù)詳解

2020-10-29 21:46:01

字體：大中小

供稿：網(wǎng)友

前言

在數(shù)據(jù)庫中，很多人員習(xí)慣使用SELECT COUNT（*） 、SELECT COUNT（1) 、SELECT COUNT(COL)來查詢一個表有多少記錄，對于小表，這種SQL的開銷倒不是很大，但是對于大表，這種查詢表記錄數(shù)的做法就是一個非常消耗資源了，而且效率很差。下面介紹一下SQL Server、 Oracle、MySQL中如何快速獲取表的記錄數(shù)。

SQL SERVER 數(shù)據(jù)庫

在SQL Server數(shù)據(jù)庫中, 對象目錄視圖sys.partitions中有一個字段rows會記錄表的記錄數(shù)。我們以AdventureWorks2014為測試數(shù)據(jù)庫。

SELECT OBJECT_NAME(object_id) AS Object_Name,  i.name   AS Index_Name, p.rows   AS Table_RowsFROM sys.partitions pLEFT JOIN sys.sysindexes i ON p.object_id = i.id AND p.index_id = i.indid WHERE object_id = OBJECT_ID('TableName')

那么我們還有一些疑問，我們先來看看這些問題吧！

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

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

3：有多個索引的表，是否記錄數(shù)會存在不一致的情況？

4：統(tǒng)計信息不準(zhǔn)確的表，是否rows也會不準(zhǔn)確

5: 分區(qū)表的情況又是怎么樣？

6：對象目錄視圖sys.partitions與sp_spaceused獲取的表記錄函數(shù)是否準(zhǔn)確。

如下所示，我們先構(gòu)造測試案例：

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) )ENDGO SET NOCOUNT ON;BEGIN TRANDECLARE @Index INT =1; WHILE @Index <= 100000BEGIN  INSERT INTO TEST_TAB_ROW VALUES(@Index, NEWID());  SET @Index+=1;  IF (@Index % 5000) = 0  BEGIN IF @@TRANCOUNT > 0   BEGIN   COMMIT;  BEGIN TRAN  END ENDEND IF @@TRANCOUNT > 0 BEGIN  COMMIT;ENDGO

關(guān)于問題1、問題2，都可以使用上面腳本，如下測試所示：

SELECT OBJECT_NAME(object_id) AS Object_Name,  i.name   AS Index_Name, p.rows   AS Table_RowsFROM sys.partitions pLEFT 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')

在表dbo.TEST_TAB_ROW 上創(chuàng)建非聚集索引后，查詢結(jié)果如下所示：

CREATE INDEX IX_TEST_TAB_ROW ON TEST_TAB_ROW(ID);

我們插入500條記錄，此時，這個數(shù)據(jù)量不足以觸發(fā)統(tǒng)計信息更新，如下所示， Rows Sampled還是1000000

DECLARE @Index INT =1; WHILE @Index <= 500BEGIN  INSERT INTO TEST_TAB_ROW VALUES(100000 +@Index, NEWID());  SET @Index+=1; END

如下所示，發(fā)現(xiàn)sys.partitions中的記錄變成了100500了，可見rows這個值的計算不依賴統(tǒng)計信息。

當(dāng)然，如果你用sp_spaceused，發(fā)現(xiàn)這里面的記錄也是100500

sp_spaceused 'dbo.TEST_TAB_ROW'

關(guān)于問題3：有多個索引的表，是否記錄數(shù)會存在不一致的情況？

答案：個人測試以及統(tǒng)計來看，暫時發(fā)現(xiàn)多個索引的情況下，sys.partitions中的rows記錄數(shù)都是一致的。暫時沒有發(fā)現(xiàn)不一致的情況，當(dāng)然也不排除有特殊情況。

關(guān)于問題5: 分區(qū)表的情況又是怎么樣？

答案：分區(qū)表和普通表沒有任何區(qū)別。

關(guān)于問題6：對象目錄視圖sys.partitions與sp_spaceused獲取的表記錄函數(shù)是否準(zhǔn)確？

答案：對象目錄視圖sys.partitions與sp_spaceused獲取的表記錄數(shù)是準(zhǔn)確的。

ORACLE 數(shù)據(jù)庫

在ORACLE數(shù)據(jù)庫中，可以通過DBA_TABLES、ALL_TABLES、USER_TABLES視圖查看表的記錄數(shù)，不過這個值（NUM_ROWS）跟統(tǒng)計信息有很大的關(guān)系，有時候統(tǒng)計信息沒有更新或采樣比例會導(dǎo)致這個值不是很準(zhǔn)確。

SELECT OWNER  ,   TABLE_NAME,   NUM_ROWS ,  LAST_ANALYZEDFROM DBA_TABLES WHERE OWNER = '&OWNER'   AND TABLE_NAME = '&TABLE_NAME';   SELECT OWNER,  TABLE_NAME,  NUM_ROWS ,  LAST_ANALYZEDFROM ALL_TABLESWHERE OWNER ='&OWNER'AND TABLE_NAME='&TABLE_NAME';  SELECT TABLE_NAME,  NUM_ROWS ,  LAST_ANALYZEDFROM USER_TABLESWHERE TABLE_NAME='&TABLE_NAME'

更新統(tǒng)計信息后，就能得到準(zhǔn)確的行數(shù)。所以如果需要得到正確的數(shù)據(jù)，最好更新目標(biāo)表的統(tǒng)計信息，進行100%采樣分析。對于分區(qū)表，那么就需要從dba_tab_partitions里面查詢相關(guān)數(shù)據(jù)了。

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

MySQL數(shù)據(jù)庫

在MySQL中比較特殊，雖然INFORMATION_SCHEMA.TABLES也可以查到表的記錄數(shù)，但是非常不準(zhǔn)確。如下所示，即使使用ANALYZE TABLE更新了統(tǒng)計信息，從INFORMATION_SCHEMA.TABLES中獲取的記錄依然不準(zhǔn)確

SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_NAME='table_name'

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>