添加数据库索引

索引可以用来加速数据库查询，但是什么时候应该添加新索引呢?传统上，这个问题的答案是为用于过滤或连接数据的每个列添加索引。例如，考虑以下查询:

选择＊从项目在哪里user_id＝2；

这里我们通过user_id列，因此开发人员可能决定对该列建立索引。

虽然在某些情况下，使用上述方法索引列可能是有意义的，但实际上可能会产生负面影响。每当向表写入数据时，任何现有的索引都需要更新。索引越多，可能会变得越慢。根据索引的数据量和索引类型，索引也会占用相当多的磁盘空间。例如，PostgreSQL提供了“GIN”索引，它可以用来索引某些不能被常规b树索引索引的数据类型。然而，与b树索引相比，这些索引通常占用更多的数据，更新速度也较慢。

因此，不应该盲目地为用于过滤数据的每一列添加新索引。相反，人们应该问自己以下问题:

我是否可以以这样一种方式编写查询，使其重用尽可能多的现有索引?
数据是否足够大，以至于使用索引会比迭代表中的行更快?
维护索引的开销是否值得减少查询时间?

我们将在下面详细探讨每个问题。

重用查询

第一步是确保查询重用尽可能多的现有索引。例如，考虑以下查询:

选择＊从待办事项在哪里user_id＝123和状态＝“开放”；

现在假设我们已经有了索引user_id列而不列状态列。有人可能会认为这个查询会执行得很差，因为状态被去。取消建立索引实际上，在给定索引的情况下，查询可能执行得很好user_id可以过滤掉足够的行。

确定索引是否被重用的最佳方法是使用解释分析．根据可能连接的任何额外表和用于过滤的其他列，您可能会发现额外的索引不会产生太大(如果有的话)差异。另一方面，您可以确定索引五月做出改变。

简而言之:

尝试以这样一种方式编写查询，它可以重用尽可能多的现有索引。
使用以下命令运行查询解释分析并研究输出以找到最理想的查询。

数据大小

如果常规序列扫描(=简单地遍历所有现有行)更快，数据库可能决定不使用索引，尽管它已经存在。对于小桌子来说尤其如此。

如果预计表的大小会增长，并且您希望查询必须过滤掉大量行，则可能需要考虑添加索引。如果表的大小非常小(例如只有几行)，或者任何现有的索引都可以过滤掉足够的行不想要添加一个新索引。

维护开销

索引必须在每次写表时更新。对于PostgreSQL所有每当将数据写入表时，将更新现有索引。因此，在同一个表上有许多索引将减慢写入速度。

因此，人们应该问自己:查询性能的降低是否值得维护额外索引的开销?

如果添加一个索引使SELECT时间减少了5毫秒，但使INSERT/UPDATE/DELETE时间增加了10毫秒，那么这个索引可能不值得。另一方面，如果SELECT时间减少了，但INSERT/UPDATE/DELETE时间没有受到影响，那么您可能还是想添加索引。

查找未使用的索引

要查看哪些索引是未使用的，可以运行以下查询:

选择relname作为table_name，indexrelname作为index_name，idx_scan，idx_tup_read，idx_tup_fetch，pg_size_pretty（pg_relation_size（indexrelname：：regclass））从pg_stat_all_indexes在哪里schemaname＝“公共”和idx_scan＝0和idx_tup_read＝0和idx_tup_fetch＝0订单通过pg_relation_size（indexrelname：：regclass）desc；

该查询输出一个包含从未使用过的所有索引的列表，并按索引大小降序对它们进行排序。这个查询对于确定之前的索引是否有用非常有用。有关各栏含义的更多信息可在https://www.postgresql.org/docs/current/static/monitoring-stats.html．

由于此查询的输出依赖于数据库的实际使用情况，因此可能会受到以下因素的影响(但不限于):

某些查询永远不会执行，因此无法使用某些索引。
某些表的数据很少，导致PostgreSQL使用序列扫描而不是索引扫描。

换句话说，该数据仅对具有大量数据和尽可能多地启用(和使用)GitLab特性的频繁使用的数据库是可靠的。