在資料庫中,很多人員習慣使用SELECT COUNT(*)、SELECT COUNT(1)、SELECT COUNT(COL)來查詢一個表有多少記錄,對於小表,這種SQL的開銷倒不是很大,但是對於大表,這種查詢表記錄數的做法就是一個非常消耗資源了,而且效率很差。下麵介紹一下SQL Server、 O... ...
在資料庫中,很多人員習慣使用SELECT COUNT(*)、SELECT COUNT(1)、SELECT COUNT(COL)來查詢一個表有多少記錄,對於小表,這種SQL的開銷倒不是很大,但是對於大表,這種查詢表記錄數的做法就是一個非常消耗資源了,而且效率很差。下麵介紹一下SQL Server、 Oracle、MySQL中如何快速獲取表的記錄數。
SQL SERVER 資料庫
在SQL Server資料庫中, 對象目錄視圖sys.partitions中有一個欄位rows會記錄表的記錄數。我們以AdventureWorks2014為測試資料庫。
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')
那麼我們還有一些疑問,我們先來看看這些問題吧!
1:沒有索引的表是否也可以使用上面腳本?
2:只有非聚集索引的堆表是否可以使用上面腳本?
3:有多個索引的表,是否記錄數會存在不一致的情況?
4:統計信息不准確的表,是否rows也會不准確
5: 分區表的情況又是怎麼樣?
6:對象目錄視圖sys.partitions與sp_spaceused獲取的表記錄函數是否準確。
如下所示,我們先構造測試案例:
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
關於問題1、問題2,都可以使用上面腳本, 如下測試所示:
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')
在表dbo.TEST_TAB_ROW 上創建非聚集索引後,查詢結果如下所示:
CREATE INDEX IX_TEST_TAB_ROW ON TEST_TAB_ROW(ID);
我們插入500條記錄,此時,這個數據量不足以觸發統計信息更新,如下所示, Rows Sampled還是1000000
DECLARE @Index INT =1;
WHILE @Index <= 500
BEGIN
INSERT INTO TEST_TAB_ROW
VALUES(100000 +@Index, NEWID());
SET @Index+=1;
END
如下所示,發現sys.partitions中的記錄變成了100500了,可見rows這個值的計算不依賴統計信息。
當然,如果你用sp_spaceused,發現這裡面的記錄也是100500
sp_spaceused 'dbo.TEST_TAB_ROW'
關於問題3:有多個索引的表,是否記錄數會存在不一致的情況?
答案:個人測試以及統計來看,暫時發現多個索引的情況下,sys.partitions中的rows記錄數都是一致的。暫時沒有發現不一致的情況,當然也不排除有特殊情況。
關於問題5: 分區表的情況又是怎麼樣?
答案:分區表和普通表沒有任何區別。
關於問題6:對象目錄視圖sys.partitions與sp_spaceused獲取的表記錄函數是否準確?
答案:對象目錄視圖sys.partitions與sp_spaceused獲取的表記錄數是準確的。
ORACLE 資料庫
在ORACLE資料庫中,可以通過DBA_TABLES、ALL_TABLES、USER_TABLES視圖查看表的記錄數,不過這個值(NUM_ROWS)跟統計信息有很大的關係,有時候統計信息沒有更新或採樣比例會導致這個值不是很準確。
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'
更新統計信息後,就能得到準確的行數。所以如果需要得到正確的數據,最好更新目標表的統計信息,進行100%採樣分析。對於分區表,那麼就需要從dba_tab_partitions裡面查詢相關數據了。
SQL>execute dbms_stats.gather_table_stats(ownname => 'username', tabname =>'tablename', estimate_percent =>100, cascade=>true);
MySQL資料庫
在MySQL中比較特殊,雖然INFORMATION_SCHEMA.TABLES也可以查到表的記錄數,但是非常不准確。如下所示,即使使用ANALYZE TABLE更新了統計信息,從INFORMATION_SCHEMA.TABLES中獲取的記錄依然不准確。
SELECT TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME='table_name'
mysql> SELECT TABLE_ROWS
-> FROM INFORMATION_SCHEMA.TABLES
-> WHERE TABLE_NAME='jiraissue'