问题描述
生产库中一张表的数据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亿行数据统计信息
图二 插入1亿行数据占用空间
在没有任何索引的情况下使用count(*)测试,语句如下:
DECLARE @d datetime
SET @d=getdate()
SELECT COUNT(*) FROM count_Test;
SELECT [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())
测试时内存使用率一度飙到96%,可见效率是极低的。测试结果用时1分42秒,如图三,我们查看此时的执行计划,如图四。可以清晰地看到此时走的是全表扫描,并且绝大多数的开销都花销在这上面。
图三 无索引使用count(*)执行时间
图四 无索引使用count(*)执行计划
在没有任何索引的情况下使用sysindexes测试,语句如下:
DECLARE @d datetime
SET @d=getdate()
SELECT object_name(id) as TableName,indid,rows,rowcnt
FROM sys.sysindexes WHERE id = object_id('count_Test')
and indid in(0,1);
SELECT [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())
测试结果用时450毫秒,如图五。我们查看此时的执行计划,如图六。可以看到此时走的是聚集索引扫描,并且全部的开销都在此。
图五 无索引使用使用sysindexes执行时间