什么是索引以及如何使用它们来优化数据库中的查询?
我正在维护一个相当大的应用程序和数据库，并注意到我们的一些存储过程中的一些数据库性能很差.
I am maintaining a pretty sizable application and database and am noticing some poor database performance in a few of our stored procedures.
我总是听说添加索引"可以帮助提高性能.我当然不是 DBA，我不明白什么是索引，它们为什么有用，以及如何创建它们.
I always hear that "adding an index" can be done to help performance. I am certainly no DBA, and I do not understand what indexes are, why they help, and how to create them.
我基本上需要一个索引 101.
I basically need an indexes 101.
谁能给我资源让我学习?
Can anyone give me resources so that I can learn?
推荐答案
根据经验，索引应该位于您在连接或 where 子句中使用的任何字段上(如果它们有足够多的不同值，因此值得使用索引), 只有几个可能值的字段不会从索引中受益，这就是尝试索引位字段毫无意义的原因).
As a rule of thumb, indexes should be on any fields that you use in joins or where clauses (if they have enough different values to make using an index worthwhile, field with only a few possible values doesn't benefit from an index which is why it is pointless to try to index a bit field).
如果您的结构已经正式创建了主键(它应该如此，我从不创建没有主键的表)，根据定义，这些主键是索引的，因为主键需要在其上具有唯一索引.人们经常忘记他们必须索引外键，因为在设置外键关系时不会自动创建索引.由于外键的目的是为您提供一个可以加入的字段，因此大多数外键可能应该被编入索引.
If your structure has formally created primary keys (which it should, I never create a table without a primary key), those are by definition indexed becasue a primary key is required to have a unique index on it. People often forget that they have to index the foreign keys becasue an index is not automatically created when you set up the foreign key relationsship. Since the purpose of a foreign key is to give you a field to join on, most foreign keys should probably be indexed.
索引一旦创建就需要维护.如果您有大量数据更改活动，它们可能会变得碎片化并降低性能，需要刷新.阅读有关索引的在线书籍.您还可以在那里找到 create index 语句的语法.
Indexes once created need to be maintained. If you have a lot of data change activity, they can get fragmented and slow performance and need to be refreshed. Read in Books online about indexes. You can also find the syntax for the create index statement there.
索引是一种平衡行为，您添加的每个索引通常都会增加数据插入、更新和删除的时间，但可能会加速复杂插入、更新和删除中的选择和连接.虽然以上的经验法则是一个很好的起点，但没有一个公式可以说明什么是最佳索引.
Indexes are a balancing act, every index you add usually will add time to data inserts, updates and deletes but can potentially speed up selects and joins in complex inserts, updates and deletes. There is no one formula for what are the best indexes although the rule of thumb above is a good place to start.
相关文章