在SQL Server中创建索引的最佳做法和反模式？ [英] Best practices and anti-patterns in creating indexes in SQL Server?

问题描述

为SQL Server定义索引（集群和非集群）时，您会考虑什么？ DB新手应该注意什么反模式？请解释为什么或尽可能提供引用。

What are the things that you would consider when defining indexes, clustered and non-clustered, for SQL Server? Are there any anti-patterns that DB newbies should be aware of? Please explain the "Why" or provide references if possible.

推荐答案

索引基本上是一个备忘单。它允许DBMS在磁盘上查找特定值（或值范围），而不必扫描整个表。一般来说，你通过索引在INSERT / UPDATE / DELETE上支付一点点罚金，但很少这么做，这是一个瓶颈。一个好的DBMS只会在帮助查询性能时使用索引，所以在这里没有很多负面的反模式;它通常不会伤害你非常，如果你有额外的索引（除非你在谈论非常高的事务表）。也就是说，仔细的索引全局将帮助你确保真正重要的，有最好的方式来发现是通过剖析你的应用程序。

An index is basically a "cheat sheet". It allows the DBMS to find a particular value (or range of values) on disk without having to scan the whole table. Generally, you pay a little bit of penalty on INSERT / UPDATE / DELETE by having an index, but rarely so much that it's a bottleneck on its own. A good DBMS will only use indexes when they help query performance, so there aren't a lot of hugely negative anti-patterns here; it doesn't usually hurt you very much if you have extra indexes (unless you're talking about very highly transactional tables). That said, careful indexing across the board will help you make sure that the really important ones are there, and the best way to discover that is by profiling your application.

了解何时何时不使用索引的关键是了解他们在封底下真正做什么。简而言之，当索引的选择性较高时（即，与关系的大小相比，不同的可能值的数量较高），您需要它们。因此，例如，如果您有一个包含10,000行的表，并且在该表上有一个名为color的列为红色或蓝色的列，它不会帮助太多的索引，因为DBMS将可能必须加载大多数页面到内存（假设随机分布）。相反，表的主键id（几乎总是自动添加的）上的索引将使该表中的查找快速（以log（n）的顺序）变快 - 因为树中的非常少数的节点必须

The key to understanding when and when not to use indexes is to get a grasp on what they're really doing under the covers. In a nutshell, you want them when the selectivity of the index is high (i.e. the number of different possible values is high compared to the size of the relation). So, for example, if you have a table with 10,000 rows, and you have a column called "color" on that table that's either "red" or "blue", it doesn't help much to have an index, because the DBMS will probably have to load most of the pages into memory anyway (assuming a random distribution). Conversely, an index on the primary key id of a table (which is nearly always automatically added) will make lookups in that table lightening fast - on the order of log(n) - because a very small number of nodes in the tree have to be examined to find the page on disk where the record resides.

大多数现代数据库系统中的索引都是用B +树来实现的，这是一个非常酷的B-针对慢辅助存储（磁盘而不是内存）优化的树。您可以从数据库系统：完整图书中获得有关其使用和功能的良好介绍。 。

Indexes in most modern database systems are implemented with a B+ tree, which is a very cool variant of B-Trees that's optimized for slow secondary storage (disks instead of memory). You can get a good introduction to their use and functionality from Database Systems: The Complete Book.

这篇关于在SQL Server中创建索引的最佳做法和反模式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！