索引的 DO 和 DONT
使用索引提高数据库性能的注意事项有哪些？
DO 是应该创建索引的情况，或者是另一个与索引相关的可以提高性能的提示。
DONT 是指不应创建索引或其他可能损害性能的索引相关操作的情况。
What are some DOs and DONTs for improving database performance using index?
A DO would be a case in which an index should be created, or another indexes related tip that will improve performance.
A DONT will be a case when an index shouldn't be created, or another index related action that can hurt the performance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
一般来说：
1. 除非确实需要，否则不要添加索引。
每个索引都会使写入速度变慢...
2. 索引将用于 where 子句：
同样，它将用于外键引用（在两个表上）。
3. 索引将用于排序，特别是在受到限制时：
4. 当 2. 和 3. 都适用时，多列索引有时会很有用。
在这种情况下，首先放置 where 条件，最后放置排序键：
5. 保持表统计信息最新。
如果表统计信息是垃圾，优化器就不太可能使用您的索引。如果需要，手动清理/分析您的数据库。
6. 索引的使用取决于您的表重新分区。
超过检索行的特定阈值后，执行全表扫描会更快。如果您的索引位于布尔字段上，该字段或多或少将表分成两部分，则永远不会使用它。
同样，如果您的数据存储方式使得索引扫描可能最终随机访问该表的几乎所有适用的磁盘页面，那么规划器将更喜欢全表扫描。
7. 如果可用，请考虑部分/表达式索引。
如果您有一个字段除了 10% 的行之外具有相同的值，请考虑在其上建立部分索引（即不是该值的地方）。这会导致索引小得多，但不会妨碍其实际用途。
如果您不断查询应用于列的表达式，并且您的平台提供表达式索引，请考虑在其上添加索引。使用时，不会对每行计算表达式。
Generally speaking:
1. Don't add an index unless you actually need it.
Each index makes writes slower...
2. An index will be used on where clauses:
By the same token it'll be used in foreign key references (on both tables).
3. An index will be used for sorting, especially when tied to a limit:
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:
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.
6. Index usage depends on your table repartition.
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.
7. Consider partial/expression indexes when available.
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.
衡量整个系统的吞吐量。索引可能有助于某些查询，但会损害插入、更新和删除。
创建索引。
衡量整个系统的吞吐量。
如果性能更好，则将索引保留在那里。如果性能较差，请将其删除。
Measure system throughput as a whole. An index may help some queries but harm insert, update and delete.
Create the index.
Measure system throughput as a whole.
If performance is better, leave the index there. If performance is worse, delete it.