索引的 DO 和 DONT
使用索引提高数据库性能的 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;
出于同样的原因，它将用于外键引用(在两个表上).
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.索引将用于排序，尤其是在绑定到限制时:
-- index on foo (bar)
select bar from foo order by bar limit 10;
4.当 2. 和 3. 都适用时，多列索引有时很有用.
在这种情况下，将 where 条件放在第一位，然后将排序键放在最后:
4. Multicolumn indexes are occasionally useful when 2. and 3. 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.
相关文章