前言
在數(shù)據(jù)庫(kù)中，很多人員習(xí)慣使用SELECT COUNT（*） 、SELECT COUNT（1) 、SELECT COUNT(COL)來(lái)查詢一個(gè)表有多少記錄，對(duì)于小表，這種SQL的開銷倒不是很大，但是對(duì)于大表，這種查詢表記錄數(shù)的做法就是一個(gè)非常消耗資源了，而且效率很差。下面介紹一下SQL Server、 Oracle、MySQL中如何快速獲取表的記錄數(shù)。
SQL SERVER 數(shù)據(jù)庫(kù)
在SQL Server數(shù)據(jù)庫(kù)中, 對(duì)象目錄視圖sys.partitions中有一個(gè)字段rows會(huì)記錄表的記錄數(shù)。我們以AdventureWorks2014為測(cè)試數(shù)據(jù)庫(kù)。
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')
那么我們還有一些疑問(wèn)，我們先來(lái)看看這些問(wèn)題吧！
1：沒(méi)有索引的表是否也可以使用上面腳本？
2：只有非聚集索引的堆表是否可以使用上面腳本？
3：有多個(gè)索引的表，是否記錄數(shù)會(huì)存在不一致的情況？
4：統(tǒng)計(jì)信息不準(zhǔn)確的表，是否rows也會(huì)不準(zhǔn)確
5: 分區(qū)表的情況又是怎么樣？
6：對(duì)象目錄視圖sys.partitions與sp_spaceused獲取的表記錄函數(shù)是否準(zhǔn)確。
如下所示，我們先構(gòu)造測(cè)試案例：
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
關(guān)于問(wèn)題1、問(wèn)題2，都可以使用上面腳本， 如下測(cè)試所示：
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 上創(chuàng)建非聚集索引后，查詢結(jié)果如下所示：
CREATE INDEX IX_TEST_TAB_ROW ON TEST_TAB_ROW(ID);
我們插入500條記錄，此時(shí)，這個(gè)數(shù)據(jù)量不足以觸發(fā)統(tǒng)計(jì)信息更新，如下所示， Rows Sampled還是1000000
DECLARE @Index INT =1;
WHILE @Index = 500
BEGIN
INSERT INTO TEST_TAB_ROW
VALUES(100000 +@Index, NEWID());
SET @Index+=1;
END
如下所示，發(fā)現(xiàn)sys.partitions中的記錄變成了100500了，可見rows這個(gè)值的計(jì)算不依賴統(tǒng)計(jì)信息。
當(dāng)然，如果你用sp_spaceused，發(fā)現(xiàn)這里面的記錄也是100500
sp_spaceused 'dbo.TEST_TAB_ROW'
關(guān)于問(wèn)題3：有多個(gè)索引的表，是否記錄數(shù)會(huì)存在不一致的情況？
答案：個(gè)人測(cè)試以及統(tǒng)計(jì)來(lái)看，暫時(shí)發(fā)現(xiàn)多個(gè)索引的情況下，sys.partitions中的rows記錄數(shù)都是一致的。暫時(shí)沒(méi)有發(fā)現(xiàn)不一致的情況，當(dāng)然也不排除有特殊情況。
關(guān)于問(wèn)題5: 分區(qū)表的情況又是怎么樣？
答案：分區(qū)表和普通表沒(méi)有任何區(qū)別。
關(guān)于問(wèn)題6：對(duì)象目錄視圖sys.partitions與sp_spaceused獲取的表記錄函數(shù)是否準(zhǔn)確？
答案：對(duì)象目錄視圖sys.partitions與sp_spaceused獲取的表記錄數(shù)是準(zhǔn)確的。
ORACLE 數(shù)據(jù)庫(kù)
在ORACLE數(shù)據(jù)庫(kù)中，可以通過(guò)DBA_TABLES、ALL_TABLES、USER_TABLES視圖查看表的記錄數(shù)，不過(guò)這個(gè)值（NUM_ROWS）跟統(tǒng)計(jì)信息有很大的關(guān)系，有時(shí)候統(tǒng)計(jì)信息沒(méi)有更新或采樣比例會(huì)導(dǎo)致這個(gè)值不是很準(zhǔn)確。
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'
更新統(tǒng)計(jì)信息后，就能得到準(zhǔn)確的行數(shù)。所以如果需要得到正確的數(shù)據(jù)，最好更新目標(biāo)表的統(tǒng)計(jì)信息，進(jìn)行100%采樣分析。對(duì)于分區(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ù)庫(kù)
在MySQL中比較特殊，雖然INFORMATION_SCHEMA.TABLES也可以查到表的記錄數(shù)，但是非常不準(zhǔn)確。如下所示，即使使用ANALYZE TABLE更新了統(tǒng)計(jì)信息，從INFORMATION_SCHEMA.TABLES中獲取的記錄依然不準(zhǔn)確
SELECT TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES
WHERE 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>
如上所示，MySQL這種查詢表記錄數(shù)的方法看來(lái)還是有缺陷的。當(dāng)然如果不是要求非常精確的值，這個(gè)方法也是不錯(cuò)的。
當(dāng)然，上面介紹的SQL Server、Oracle、MySQL數(shù)據(jù)庫(kù)中的方法，還是有一些局限性的。例如，只能查詢整張表的記錄數(shù)，對(duì)于那些查詢記錄數(shù)帶有查詢條件（WHERE）這類SQL。還是必須使用SELECT COUNT(*）這種方法。
總結(jié)
以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，謝謝大家對(duì)腳本之家的支持。
您可能感興趣的文章:- sql 查詢記錄數(shù)結(jié)果集某個(gè)區(qū)間內(nèi)記錄
- SQL Server數(shù)據(jù)庫(kù)按百分比查詢出表中的記錄數(shù)
- mysql實(shí)現(xiàn)查詢最接近的記錄數(shù)據(jù)示例
