我应该何时创建数据库索引？ [英] When should I create database indexes?

问题描述

Hai guys，

Hai guys,

• 何时在表创建期间或在表上设置表的索引（即）性能调优？

• when to set index for a table (ie) during table creation or on performance tuning?

索引的优点和缺点是什么？

What are the advantages and disadvantages of indexing?

推荐答案

许多（大多数？）DBMS使用索引来支持唯一约束。始终创建索引以强制执行唯一约束;它们（约束）对于数据库的正确操作至关重要。

Many (most?) DBMS use indexes to support unique constraints. Always create indexes to enforce unique constraints; they (the constraints) are crucial to the correct operation of your database.

如果您可以选择如何在多个列上创建索引，请将列设置为总是在其他字段之前的查询中引用 - 通常。如果前导列也有一定的选择性，这是最好的。

Where you have a choice of how to create the index on multiple columns, put the column that will always be referenced in the queries ahead of other fields - usually. This is best if the leading column is also somewhat selective.

在获得唯一性所需的约束之后，请考虑实施参照完整性所需的约束。它们通常也由DBMS强制执行。同样，你不能让你的数据库处于不完整的状态 - 它是一个逻辑系统，如果它包含谬误，你可以从中证明任何东西，这是没有用的。

After you have the constraints necessary for uniqueness, consider those needed to enforce referential integrity. They are usually mandated by the DBMS too. Again, you cannot afford to have your database in a state of disintegrity -- it is a logical system, and if it contains fallacies, you can prove anything from it, which is not helpful.

在处理（索引）唯一性和参照完整性约束之后，您可能会或可能不会从其他人那里获益。仔细选择，并添加尽可能少的额外内容（零是一个很好的数字）。每个索引都会减慢更新操作（UPDATE，INSERT，DELETE）并使用存储空间。目的是通过加快查询来赢得它的位置。但是，不要忘记优化器必须考虑每个索引以及它是否有助于回答查询，因此索引也会降低优化器的速度（尽管您可能很难测量该效果）。

After the uniqueness and referential integrity constraints are dealt with (indexed), then you may or may not benefit from some others. Choose carefully, and add as few extras as possible (zero is a good number). Each index slows up update operations (UPDATE, INSERT, DELETE), and uses storage space. The intention is that it should win its place by speeding up queries. However, don't forget that the optimizer has to think about each index and whether it can be useful in answering the query, so indexes also slow down the optimizer (though you'd probably be hard pressed to measure that effect).

当您添加索引时，将它们添加到选择性列上（不是'性'包含'M'和'F'，但可能'dob'包含1900年之间的出生日期和考虑额外列是否有助于回答更多查询。考虑额外列是否有助于回答更多查询。某些DBMS（如DB2）为索引提供了额外的列，这些列不是唯一性约束的一部分，但提供了经常使用的列在查询中。这些可以允许仅索引扫描（一个不需要访问表数据，因为所需的值都在索引中）。

When you do add indexes, add them on selective columns (not 'sex' containing 'M' and 'F', but maybe 'dob' containing dates of birth between 1900 and 2010, or maybe even more distinct values than that. Consider whether extra columns will help answer more queries. Some DBMS (such as DB2) provide for indexes with extra columns that are not part of the uniqueness constraint but which provide columns that are frequently used in the query. These can allow an index-only scan (one which does not need to access the table data because the needed values are all in the index).

可以说更多，但这涵盖了很多领域。

There is much more that could be said, but this covers a lot of the territory.

• 尽可能少的索引 - 和最好不要额外。

这篇关于我应该何时创建数据库索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！