什么是基数以及它如何影响性能 (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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
基数是表中该字段的唯一值的数量。
我怀疑 SQL Server 实际上并没有创建索引，因为没有它的记录。该字段是否可能全部为
NULL，或者它上面有一个过滤器消除了所有记录，例如WHERE 1=0？一个好的起点是运行更新并捕获实际的执行计划。
编辑：
索引类型 3 是
XML 索引。请参阅此页面以供参考。
XML< 的结构/code> 索引有很大不同。我自己不使用它们，但从一些快速阅读来看，它听起来像是创建了一系列附加索引，这些索引无法由用户直接访问，但由查询引擎使用。Cardinality is the number of unique values for that field within the table.
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 likeWHERE 1=0?A good place to start would be to run the update and capture the actual execution plan.
EDIT:
Index Type 3 is an
XML Index.Please see this page for reference.
The structure of
XMLindexes 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.