有哪些最佳实践和“经验法则"?用于创建数据库索引? [英] What are some best practices and "rules of thumb" for creating database indexes?

问题描述

我有一个应用程序，它循环访问数据库表中的大量记录，并对该数据库中的记录执行大量 SQL 和 .Net 操作(目前我在 PostgreSQL 上使用 Castle.ActiveRecord).

I have an app, which cycles through a huge number of records in a database table and performs a number of SQL and .Net operations on records within that database (currently I am using Castle.ActiveRecord on PostgreSQL).

我在几个字段上添加了一些基本的 btree 索引，并且如您所料，SQL 操作的性能显着提高.想要充分利用 dbms 的性能，我想对我应该为所有项目建立索引的内容做出一些更好的选择.

I added some basic btree indexes on a couple of the feilds, and as you would expect, the performance of the SQL operations increased substantially. Wanting to make the most of dbms performance I want to make some better educated choices about what I should index on all my projects.

我知道插入时会降低性能(因为数据库需要更新索引和数据)，但是在创建数据库索引时我应该考虑哪些建议和最佳实践?我如何最好地为一组数据库索引选择字段/字段组合(经验法则)?

I understand that there is a detrement to performance when doing inserts (as the database needs to update the index, as well as the data), but what suggestions and best practices should I consider with creating database indexes? How do I best select the feilds/combination of fields for a set of database indexes (rules of thumb)?

此外，我如何最好地选择将哪个索引用作聚集索引?当谈到访问方法时，在什么情况下我应该使用 btree 而不是散列或 gist 或 gin(它们到底是什么?).

Also, how do I best select which index to use as a clustered index? And when it comes to the access method, under what conditions should I use a btree over a hash or a gist or a gin (what are they anyway?).

推荐答案

我的一些经验法则:

• 索引所有主键(我认为大多数 RDBMS 在创建表时都会这样做).
• 索引所有外键列.
• 仅在以下情况下创建更多索引:
  • 查询速度很慢.
  • 您知道数据量将显着增加.

  如果查询很慢，请查看执行计划并:

  If a query is slow, look at the execution plan and:

  • 如果一个表的查询只使用了几列，将所有这些列都放在一个索引中，那么你可以帮助RDBMS只使用索引.
  • 不要浪费资源索引小表(数百条记录).
  • 按基数从高到低的顺序索引多列.这意味着:首先索引具有更多不同值的列，然后是具有更少不同值的列.
  • 如果查询需要访问超过 10% 的数据，完整扫描通常比索引更好.

  这篇关于有哪些最佳实践和“经验法则"?用于创建数据库索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！