DOs和DONTs [英] DOs and DONTs for Indexes

问题描述

使用索引改善数据库性能的一些DO和DONT是什么？

What are some DOs and DONTs for improving database performance using index?

DO将是创建索引的情况或另一个索引相关提示

A DO would be a case in which an index should be created, or another indexes related tip that will improve performance.

DONT将是不应创建索引的情况，或另一个可能损害性能的索引相关操作。

A DONT will be a case when an index shouldn't be created, or another index related action that can hurt the performance.

推荐答案

一般来说：

每个索引使写入速度变慢...

Each index makes writes slower...

-- index on foo (bar)
select bar from foo where bar = :bar;

同样的道理，它将用于外键引用。 p>

By the same token it'll be used in foreign key references (on both tables).

-- index on foo (bar) if baz (bar) is frequently updated/deleted.
create table foo (bar references baz (bar)); 

3。将使用索引进行排序，特别是在限制时：

3. An index will be used for sorting, especially when tied to a limit:

-- index on foo (bar)
select bar from foo order by bar limit 10;

4。在3.和4.都适用时，多列索引偶尔有用。

在这种情况下，首先放置where条件，最后放置排序键：

4. Multicolumn indexes are occasionally useful when 3. and 4. both apply.

In this case put the where conditions first, and the sort key last:

-- index on foo (baz, bar)
select bar from foo where baz between :baz1 and :baz2 group by bar;

5。保持您的表格统计信息为最新状态。

如果表统计信息是垃圾，那么优化器很少有机会使用您的索引。如果需要，请手动抽真空/分析数据库。

5. Keep your table statistics up to date.

If the table stats are garbage, there is little chances that the optimizer will use your indexes. Manually vacuum/analyze your database if needed.

超过检索的行的某个阈值，执行全表扫描将更快。如果你的索引在布尔字段上，或多或少将你的表分成两部分，它将永远不会被使用。

Past a certain threshold of rows retrieved, it'll be faster to do a full table scan. If your index is on a boolean field that more or less splits your table in two, it'll never be used.

同样，如果你的数据存储在这样一个索引扫描将可能最终随机访问该表的几乎适用的磁盘页，计划者将更喜欢全表扫描。

Likewise, if your data is stored in such a way that the index scan will likely end up randomly accessing nearly ever applicable disk page for that table, the planner will prefer a full table scan.

如果你有一个字段除了10％的行之外具有相同的值，不是那个值）。

If you've a field that has the same value except for 10% of your rows, consider a partial index on it (i.e. where not that value). This results in a much smaller index without hindering its actual usefulness.

如果你不断地对一个应用于你的列的表达式进行查询，而你的平台提供了表达式索引，那么可以考虑添加一个索引就可以了。使用时，不会为每一行计算表达式。

If you're constantly querying against an expression applied to your column and you platform offers expression indexes, consider adding an index on it. When used, the expression won't get evaluated for each row.

这篇关于DOs和DONTs的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！