SQL 计数(*) 性能 [英] SQL count(*) performance

问题描述

我有一个包含超过 2000 万行的 SQL 表 BookChapters.它有一个聚集主键 (bookChapterID)，没有任何其他键或索引.运行以下查询需要几毫秒

I have a SQL table BookChapters with over 20 millions rows. It has a clustered primary key (bookChapterID) and doesn't have any other keys or indexes. It takes miliseconds to run the following query

if (select count(*) from BookChapters) = 0
...

但是，我这样改要10多分钟

However, it takes over 10 minutes when I change it like so

if (select count(*) from BookChapters) = 1
...

或

if (select count(*) from BookChapters) > 1
...

这是为什么?如何让 select count(*) 执行得更快?

Why is that? How can I get select count(*) to execute faster?

推荐答案

Mikael Eriksson 很好地解释了为什么第一个查询很快:

Mikael Eriksson has a good explanation bellow why the first query is fast:

SQL server 优化成:如果存在(从 BookChapters 中选择 *).所以它会寻找一行的存在，而不是计算表中的所有行.

SQL server optimize it into: if exists(select * from BookChapters). So it goes looking for the presence of one row instead of counting all the rows in the table.

对于其他两个查询，SQL Server 将使用以下规则.要执行像 SELECT COUNT(*) 这样的查询，SQL Server 将使用最窄的非聚集索引来计算行数.如果桌子上没有非聚集索引，它必须扫描表.

For the other two queries, SQL Server would use the following rule. To perform a query like SELECT COUNT(*), SQL Server will use the narrowest non-clustered index to count the rows. If the table does not have any non-clustered index, it will have to scan the table.

此外，如果您的表具有聚集索引，您可以使用以下查询更快地获得计数(从本网站快速获取行数！)

Also, if your table has a clustered index you can get your count even faster using the following query (borrowed from this site Get Row Counts Fast!)

--SQL Server 2005/2008
SELECT OBJECT_NAME(i.id) [Table_Name], i.rowcnt [Row_Count]
FROM sys.sysindexes i WITH (NOLOCK)
WHERE i.indid in (0,1)
ORDER BY i.rowcnt desc

--SQL Server 2000
SELECT OBJECT_NAME(i.id) [Table_Name], i.rows [Row_Count]
FROM sysindexes i (NOLOCK)
WHERE i.indid in (0,1)
ORDER BY i.rows desc

它使用 sysindexes 系统表.您可以在此处找到更多信息 SQL Server 2000、SQL Server 2005、SQL Server 2008、SQL Server 2012

It uses sysindexes system table. More info you can find here SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2012

这是另一个链接 为什么我的 SELECT COUNT(*) 运行这么慢吗? 使用另一种解决方案.它展示了 Microsoft 用于在您右键单击表格并选择属性时快速显示行数的技术.

Here is another link Why is my SELECT COUNT(*) running so slow? with another solution. It shows technique that Microsoft uses to quickly display the number of rows when you right click on the table and select properties.

select sum (spart.rows)
from sys.partitions spart
where spart.object_id = object_id(’YourTable’)
and spart.index_id < 2

您应该发现无论您有多少张表，这都会很快返回.

You should find that this returns very quickly no matter how many tables you have.

如果您仍然使用 SQL 2000，您可以使用 sysindexes 表来获取数字.

If you are using SQL 2000 still you can use the sysindexes table to get the number.

select max(ROWS)
from sysindexes
where id = object_id(’YourTable’)

这个数字可能会略有偏差，具体取决于 SQL 更新 sysindexes 表的频率，但通常是正确的(或至少足够接近).

This number may be slightly off depending on how often SQL updates the sysindexes table, but it’s usually corrent (or at least close enough).

这篇关于SQL 计数(*) 性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！