我一直在阅读许多SQL书籍和文章,其中选择性是创建索引的重要因素.如果一列具有低选择性,则索引搜索会带来更多伤害.但这些文章都没有解释原因.任何人都可以解释为什么会这样,或提供相关文章的链接？
解决方法
来自Robert Sheldon撰写的SimpleTalk文章：
14 SQL Server Indexing Questions You Were Too Shy To Ask
The ratio of unique values within a key column is referred to as index
selectivity. The more unique the values,the higher the selectivity,
which means that a unique index has the highest possible selectivity.
The query engine loves highly selective key columns,especially if
those columns are referenced in the WHERE clause of your frequently
run queries. The higher the selectivity,the faster the query engine
can reduce the size of the result set. The flipside,of course,is
that a column with relatively few unique values is seldom a good
candidate to be indexed.
另请查看这些文章：
>由Pinal Dave检查this post
> this other在SQL Serverpedia上
> This forum post在SqlServerCentral上也可以帮到你.
> This article也在SqlServerCentral上
从SqlServerCentral文章：
In general,a nonclustered index should be selective. That is,the
values in the column should be fairly unique and queries that filter
on it should return small portions of the table.
The reason for this is that key/RID lookups are expensive operations
and if a nonclustered index is to be used to evaluate a query it needs
to be covering or sufficiently selective that the costs of the lookups
aren’t deemed to be too high.
If SQL considers the index (or the subset of the index keys that the
query would be seeking on) insufficiently selective then it is very
likely that the index will be ignored and the query executed as a
clustered index (table) scan.
It is important to note that this does not just apply to the leading column. There are scenarios where a very unselective column can be used as the leading column,with the other columns in the index making it selective enough to be used.
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
