什么是基数以及它如何影响性能 (SQL Server)? [英] What is cardinality and how does it affect performance (SQL Server)?
问题描述
我们有一个很大的表，我需要在其中对单行进行更新.我不知道该行的主键，但我有一个在该表中唯一的 varchar 值.我还有那个表中其他几列的值.
We have a massive table where I need to make an update on a single row. I don't know the primary key of the row, but I have a varchar value that is unique within that table. I also have the value of a few other columns in that tables.
运行更新需要三分钟以上，我假设它会进行全表扫描.
Running the update is taking upwards of three minutes, and I assume its doing a full table scan.
查看表上的索引，列上的索引的基数为零，页数为零.还有其他索引的基数等于表中的行数(几百万)和几十万页数.
Looking at the indices on the table, the index on the column has a cardinality of zero and page count of zero. There are other indices with a cardinality equal to the number of rows in the table (couple million) and page count of a couple hundred thousand.
这些数字实际上是什么意思?
What do these numbers actually mean?
作为后续，添加一个限制以命中具有更高基数或页数的索引会加快执行速度吗?或者我可以在索引上查看其他任何内容，以找到更适合快速找到我需要更改的行的索引.
And as a followup, would adding a restriction that hits an index with a higher cardinality or number of pages speed up the execution? Or is there anything else I can look at on the indicies to find one that would be better suited to quickly find the row I ned to change.
推荐答案
Cardinality 是表中该字段的唯一值的数量.
Cardinality is the number of unique values for that field within the table.
我怀疑 SQL Server 实际上并没有创建索引，因为它没有记录.该字段是否可能全部为 NULL ，或者其上有一个过滤器可以消除所有记录，例如 WHERE 1=0?
I suspect that SQL Server didn't actually create the index because there are no records for it. Is is possible this field is all NULLs, or there is a filter on it that eliminates all records like WHERE 1=0?
一个好的起点是运行更新并捕获实际的执行计划.
A good place to start would be to run the update and capture the actual execution plan.
索引类型 3 是 XML 索引.
XML 索引的结构有点不同.我自己不使用它们，但从一些快速阅读来看，它听起来像是创建了一系列额外的索引，用户无法直接访问这些索引，但查询引擎会使用这些索引.
The structure of XML indexes is quite a bit different. I don't use them myself but from some quick reading it sounds like it creates a series of additional indexes that can't be accessed directly by users but are used by the query engine.
这篇关于什么是基数以及它如何影响性能 (SQL Server)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
