问题描述

生产库中一张表的数据10亿级别，另一张表数据100亿级别，还有其他表的数据也是相当地庞大。入职之前不知道这些表有那么大的数据量，于是习惯了使用count(*)来统计表的记录数。但这一执行就不得了，跑了30多分钟都没出结果，最后只有取消查询。后来采取了另一种办法查询记录数。首先说明下解决的办法，使用如下SQL：

SELECT object_name(id) as TableName,indid,rows,rowcnt  
FROM sys.sysindexes WHERE id = object_id('TableName') 
and indid in (0,1);

问题模拟

接着我做了一个模拟，并且试着从原理的角度分析下使用count(*)和查询sysindexes视图为什么会出现那么大的差距。

我们做模拟之前首先要得测试数据。所以我创建一个了测试表，并且插入测试数据。这里插入1亿条数据。

创建测试表的语句如下：

DROP TABLE count_Test;
CREATE TABLE count_Test
(
       id bigint,
       name VARCHAR(20),
       phoneNo VARCHAR(11)
);

由于插入大量数据，我们肯定不能手动来。于是我写了一个存储过程，插入1亿条数据。为了模拟出数据的复杂性，数据我采用随机字符串的形式。插入测试数据的存储过程如下：

CREATE PROCEDURE pro_Count_Test  
AS
BEGIN
    SET STATISTICS IO ON;
    SET STATISTICS TIME ON;
    SET NOCOUNT ON;
    WITH Seq(id,name,phoneNo) AS
    (
        SELECT 1,cast('13'+right('000000000' +cast(cast(rand(checksum(newid()))*100000000 AS int)
        AS varchar),9) AS VARCHAR(20)),
        cast('name_'+right('000000000' +cast(cast(rand(checksum(newid()))*100000000 AS int)
        AS varchar),9) AS VARCHAR(40))
        UNION ALL
        SELECT id+1,cast('13'+right('000000000'+ cast(cast(rand(checksum(newid()))*100000000 AS int) 
        AS varchar),9) AS VARCHAR(20)),
        cast('name_'+right('000000000' +cast(cast(rand(checksum(newid()))*100000000 AS int)
        AS varchar),9) AS VARCHAR(40))
        FROM Seq
        WHERE id <= 100000000
    )
    INSERT INTO count_Test(id,name,phoneNo)
    SELECT id,name,phoneNo
    FROM Seq
    OPTION (MAXRECURSION 0)
    SET STATISTICS IO OFF ;
    SET STATISTICS TIME OFF;
END

接着我们执行此存储过程，插入测试数据。SQL Server Management Studio在输出窗口的右下角记录了操作的时间。为了更直观，我们手动写了个记录时间的语句，如下：

DECLARE @d datetime
SET @d=getdate()
print '开始执行存储过程...'
EXEC pro_Count_Test;
 
SELECT [存储过程执行花费时间(毫秒)]=datediff(ms,@d,getdate())

好了，等待47分29秒，数据插入完毕，插入数据的统计信息如图一，占用数据空间如图二，我们开始测试count(*)和sysindexes在效率上的差别。

图一 插入1亿行数据统计信息