SQL Server DBA调优日记（一）——大数据量查询记录数优化及原理探讨

最新推荐文章于 2023-04-10 11:16:56 发布

我以前很喜欢漫画

最新推荐文章于 2023-04-10 11:16:56 发布

阅读量349

点赞数

本文链接：https://blog.csdn.net/qq_43679849/article/details/87791428

版权

本文记录了一位SQL Server DBA在处理大数据量表时遇到的问题，对比分析了使用count(*)和sysindexes视图查询记录数的效率差异。通过模拟实验，展示了不同索引类型对查询速度的影响，揭示了count(*)全表扫描的低效以及sysindexes视图快速获取记录数的原理。实验表明，sysindexes在大多数情况下能提供更快的查询速度。

摘要由CSDN通过智能技术生成

问题描述

生产库中一张表的数据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  ASBEGIN    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('0