什么是索引，如何使用它们来优化数据库中的查询？ [英] What are indexes and how can I use them to optimize queries in my database?

问题描述

我正在维护一个相当可观的应用程序和数据库，并注意到我们的一些存储过程中数据库性能不佳。

我总是听到添加一个索引可以做到以帮助表现。我当然没有DBA，我不明白索引是什么，为什么他们帮助，以及如何创建它们。

我基本上需要一个索引101.

任何人都可以给我资源，以便我可以学习？

解决方案

经验法则，索引应该在您在连接中使用的任何字段或where子句（如果他们有足够的不同值使得使用索引值得，只有几个可能的值的字段不会从索引中受益，这就是为什么它没有意义的尝试索引一个位字段）。

如果您的结构正式创建了主键（应该是从不创建没有主键的表），那些按照定义进行索引，因为需要一个主键有一个独特的索引。人们常常忘记他们必须索引外键，因为在设置外键关系时不会自动创建索引。由于外键的目的是给你一个字段加入，大多数外键应该被索引。

一旦创建的索引需要维护。如果您有很多数据更改活动，它们可能会获得碎片和缓慢的性能，需要刷新。在线阅读有关索引的书籍。您还可以在那里找到创建索引语句的语法。

索引是一种平衡操作，您添加的每个索引通常会为数据插入，更新和删除添加时间，但可能会加快复杂插入，更新和删除中的选择和连接。没有一个公式是什么是最好的索引，虽然上面的经验是一个好的开始。

I am maintaining a pretty sizable application and database and am noticing some poor database performance in a few of our stored procedures.

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.

I basically need an indexes 101.

Can anyone give me resources so that I can learn?

解决方案

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.

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.

这篇关于什么是索引，如何使用它们来优化数据库中的查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！