SELECT COUNT(*) 查询是否必须进行全表扫描? [英] Does a SELECT COUNT(*) query have to do a full table scan?

问题描述

获取表中所有行数的查询是否必须执行全表扫描，或者 SQL Server 是否在某处维护行数?

Does a query that gets the count of all rows in a table have to do a full table scan or does SQL Server maintain a count of rows somewhere?

SELECT COUNT(*) FROM TABLE_NAME;

表 TABLE_NAME 有一个主键，因此有一个聚集索引，如下所示:

The table TABLE_NAME has a primary key, and therefore a clustered index, and looks like so:

CREATE TABLE TABLE_NAME
(
  Id int PRIMARY KEY IDENTITY(1, 1),
  Name nvarchar(50) NOT NULL
);

我使用的是 Microsoft SQL Server 2014.

I am using Microsoft SQL Server 2014.

推荐答案

服务器将始终读取所有记录(如果有索引，它将扫描整个索引)以计算行数.只要您在执行SELECT COUNT(*) FROM Table，就无法逃避这一点.

The server will always read all records (if there's an index then it will scan the entire index) to count the rows. You can't escape this as long as you are doing SELECT COUNT(*) FROM Table.

如果您的表有聚集索引，您可以更改您的查询为幕后"查询以检索计数而不实际获取记录:

If your table has a clustered index, you can change your query to an "under the hood" query to retrieve the count without actually fetching the records with:

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

如果您要查找近似记录数，您还可以使用以下查询:

if you are looking for an approximate count of the records, you can also use the following query:

SELECT 
    TableName = t.NAME,
    SchemaName = s.Name,
    [RowCount] = p.rows,
    TotalSpaceMB = CONVERT(DECIMAL(18,2), SUM(a.total_pages) * 8 / 1024.0), 
    UsedSpaceMB = CONVERT(DECIMAL(18,2), SUM(a.used_pages) * 8 / 1024.0),
    UnusedSpaceMB = CONVERT(DECIMAL(18,2), (SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024.0)
FROM 
    sys.tables t
    INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
    INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
    LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, 
    s.Name, 
    p.Rows
ORDER BY 
    TotalSpaceMB DESC

这将显示非系统表及其计算的(不精确的)行数和数据大小的总和(包括它们可能具有的任何索引)，相对较快，无需检索记录.

This will show non-system tables with their calculated (not exact) row count and the sum of the sizes of their data (with any index they might have), relatively fast without retrieving the records.

这篇关于SELECT COUNT(*) 查询是否必须进行全表扫描?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！