SQL Server 2005索引和低基数 [英] sql server 2005 indexes and low cardinality
问题描述
SQL Server如何确定表列是否具有低基数？
How does SQL Server determine whether a table column has low cardinality?
我问的原因是因为查询优化器很可能不会在性别列上使用索引（值 m和 f）。但是，如何确定性别列的基数才能做出决定？
The reason I ask is because query optimizer would most probably not use an index on a gender column (values 'm' and 'f'). However how would it determine the cardinality of the gender column to come to that decision?
此外，如果在极少数情况下，我的记录中有100万个条目表，并且性别列中只有一个条目为'm'，SQL Server是否能够确定这一点并使用索引来检索该单行？还是只是知道该列中只有2个不同的值并且不使用索引？
On top of this, if in the unlikely event that I had a million entries in my table and only one entry in the gender column was 'm', would SQL server be able to determine this and use the index to retrieve that single row? Or would it just know there are only 2 distinct values in the column and not use the index?
我很感谢上面讨论了一些较差的db设计，但是我只是
I appreciate the above discusses some poor db design, but I'm just trying to understand how query optimizer comes to its decisions.
非常感谢。
推荐答案
请参见 Microsoft SQL Server 2005中查询优化器使用的统计信息。
使用1的值'm'和999999'f'，统计信息将得出'm'的基数估计为1，而''的基数估计接近1M。 F'。但是不管是否使用索引，还有更多因素。
With 1 value 'm' and 999999 'f' the statistics will give a cardinality estimate of 1 for 'm', and something close to 1M for 'f'. But that whether the index will be used or not, there are more factors.
通常，如此低的选择性列仅对索引没有意义。但是，它在更复杂的索引上最左边的列，甚至在聚集索引上最左边的列也确实有意义。即使一列对'm'而不对'f'有意义，查询自动参数化也可能会给您带来麻烦，并为变量 @gender生成计划。
In general such a low selectivity column does not make sense on an index alone. However, it does make sense as a leftmost column on a more complex index, and even as a leftmost column on the clustered index. And even if a column would make sense for 'm' and not for 'f', the query auto-parametrization may play a trick on you and generate a plan for a variable @gender instead.
您必须阅读更多或提供更多详细信息。 QO团队和团队成员博客是一些不错的资源：
You'll have to either read more or give more details. Some good resources are the QO team and team members blogs:
- QO team blog
- Conor Cunningham blog
- Craig Friedman blog
这篇关于SQL Server 2005索引和低基数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
