This question already has an answer here:
这个问题在这里已有答案:
- When should you consider indexing your sql tables? 8 answers
- Index all columns 5 answers
什么时候应该考虑索引你的sql表? 8个答案
索引所有列5个答案
- When to set index for a table, during table creation or on performance tuning?
- What are the advantages and disadvantages of indexing?
何时为表设置索引,在表创建期间还是在性能调整期间?
索引的优点和缺点是什么?
4 个解决方案
#1
10
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.
许多(大多数?)DBMS使用索引来支持唯一约束。始终创建索引以强制执行唯一约束;它们(约束)对数据库的正确操作至关重要。
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.
如果您可以选择如何在多个列上创建索引,请将查询中始终引用的列放在其他字段之前 - 通常。如果前导列也有一定的选择性,这是最好的。
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.
在获得唯一性所需的约束之后,请考虑实施参照完整性所需的约束。它们通常也由DBMS强制执行。同样,你不能让你的数据库处于不完整的状态 - 它是一个逻辑系统,如果它包含谬误,你可以从中证明任何东西,这是没有用的。
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).
在处理(索引)唯一性和参照完整性约束之后,您可能会或可能不会从其他一些人那里获益。仔细选择,并添加尽可能少的额外内容(零是一个很好的数字)。每个索引都会减慢更新操作(UPDATE,INSERT,DELETE)并使用存储空间。目的是通过加快查询来赢得它的位置。但是,不要忘记优化器必须考虑每个索引以及它是否有助于回答查询,因此索引也会降低优化器的速度(尽管您可能很难测量该效果)。
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).
当你添加索引时,将它们添加到选择性列上(不是'性'包含'M'和'F',但可能'dob'包含1900年到2010年之间的出生日期,或者甚至可能是更明显的值。考虑是否额外的列将有助于回答更多查询。某些DBMS(例如DB2)为索引提供了额外的列,这些列不是唯一性约束的一部分,但提供了查询中经常使用的列。这些列可以允许仅索引扫描(一个不需要访问表数据,因为所需的值都在索引中)。
There is much more that could be said, but this covers a lot of the territory.
还有更多可以说的,但这涵盖了很多领域。
- As few indexes as necessary - and preferably no extras.
尽可能少的索引 - 最好没有额外的。
#2
5
There is a balance that needs to be struck. If you KNOW that a table will be queried and FieldA will be part of the where clause and it is a field which is highly selectable (google cardinality) then it makes a good candidate for pre-emptive tuning.
需要达成平衡。如果您知道将查询一个表并且FieldA将成为where子句的一部分,并且它是一个高度可选的字段(谷歌基数),那么它就成为先发制人调整的良好候选者。
DONT throw indexes on all kinds of fields because you THINK it makes sense, one must KNOW these things. Premature tuning/optimizations are the root of all evil a wise man once said. In this case indexes can hurt the insert/update performance because not only does the table data need to be updated but the index as well.
不要在所有类型的字段上抛出索引,因为你认为它是有道理的,你必须知道这些事情。过早的调整/优化是智者曾经说过的所有邪恶的根源。在这种情况下,索引会损害插入/更新性能,因为不仅需要更新表数据,还需要更新索引。
Side note - for some large data loads people will often drop indexes, do the load and then re-create the indexes so that the load performs more quickly.
旁注 - 对于某些大型数据加载,人们通常会删除索引,执行加载,然后重新创建索引,以便加载执行速度更快。
As far as the advantages and disadvantages - well that is a huge topic. I suggest you start here.
至于优缺点 - 那是一个很大的话题。我建议你从这里开始。
#3
1
In general, you set up the indexes during table creation. In this phase you should already have a good indication of how your data will be queried, and which fields will be used most as query criteria.
通常,您在表创建期间设置索引。在此阶段,您应该已经很好地指示了如何查询数据,以及哪些字段将最常用作查询条件。
During performance tuning, it is generally recommended to have the obvious indexes in place. During this phase, you will be able to understand if there are indexes that are not being used efficiently, or if there are queries where an index can increase performance. There is nothing stopping you for dropping or adding new indexes during performance tuning.
在性能调优期间,通常建议使用明显的索引。在此阶段,您将能够了解是否存在未被有效使用的索引,或者是否存在索引可以提高性能的查询。在性能调优期间,没有什么能阻止您删除或添加新索引。
#4
1
I'd add the 'obvious' indexes, ie on fields you know will be queried at table create time, and then add others as necessary as part of a tune. It's probably better to have fewer indexes at first and then get a feel for how the system is performing and being used - the profile is your friend hear.
我会添加“明显的”索引,即在表创建时查询的字段上,然后根据需要添加其他字段作为曲调的一部分。最初可以更少地使用较少的索引,然后了解系统的执行和使用方式 - 您的朋友可以听到该配置文件。
Advantages - faster access (when the index is being used), and the ability to enforce certain business logic like no duplicates.
优点 - 更快的访问(使用索引时),以及强制执行某些业务逻辑的能力,如无重复。
Disadvantages - table takes more space, inserting rows is slower (can be much slower), updates that touch the key field(s) are slower
缺点 - 表占用更多空间,插入行更慢(可能慢得多),触摸键字段的更新速度较慢
#1
10
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.
许多(大多数?)DBMS使用索引来支持唯一约束。始终创建索引以强制执行唯一约束;它们(约束)对数据库的正确操作至关重要。
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.
如果您可以选择如何在多个列上创建索引,请将查询中始终引用的列放在其他字段之前 - 通常。如果前导列也有一定的选择性,这是最好的。
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.
在获得唯一性所需的约束之后,请考虑实施参照完整性所需的约束。它们通常也由DBMS强制执行。同样,你不能让你的数据库处于不完整的状态 - 它是一个逻辑系统,如果它包含谬误,你可以从中证明任何东西,这是没有用的。
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).
在处理(索引)唯一性和参照完整性约束之后,您可能会或可能不会从其他一些人那里获益。仔细选择,并添加尽可能少的额外内容(零是一个很好的数字)。每个索引都会减慢更新操作(UPDATE,INSERT,DELETE)并使用存储空间。目的是通过加快查询来赢得它的位置。但是,不要忘记优化器必须考虑每个索引以及它是否有助于回答查询,因此索引也会降低优化器的速度(尽管您可能很难测量该效果)。
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).
当你添加索引时,将它们添加到选择性列上(不是'性'包含'M'和'F',但可能'dob'包含1900年到2010年之间的出生日期,或者甚至可能是更明显的值。考虑是否额外的列将有助于回答更多查询。某些DBMS(例如DB2)为索引提供了额外的列,这些列不是唯一性约束的一部分,但提供了查询中经常使用的列。这些列可以允许仅索引扫描(一个不需要访问表数据,因为所需的值都在索引中)。
There is much more that could be said, but this covers a lot of the territory.
还有更多可以说的,但这涵盖了很多领域。
- As few indexes as necessary - and preferably no extras.
尽可能少的索引 - 最好没有额外的。
#2
5
There is a balance that needs to be struck. If you KNOW that a table will be queried and FieldA will be part of the where clause and it is a field which is highly selectable (google cardinality) then it makes a good candidate for pre-emptive tuning.
需要达成平衡。如果您知道将查询一个表并且FieldA将成为where子句的一部分,并且它是一个高度可选的字段(谷歌基数),那么它就成为先发制人调整的良好候选者。
DONT throw indexes on all kinds of fields because you THINK it makes sense, one must KNOW these things. Premature tuning/optimizations are the root of all evil a wise man once said. In this case indexes can hurt the insert/update performance because not only does the table data need to be updated but the index as well.
不要在所有类型的字段上抛出索引,因为你认为它是有道理的,你必须知道这些事情。过早的调整/优化是智者曾经说过的所有邪恶的根源。在这种情况下,索引会损害插入/更新性能,因为不仅需要更新表数据,还需要更新索引。
Side note - for some large data loads people will often drop indexes, do the load and then re-create the indexes so that the load performs more quickly.
旁注 - 对于某些大型数据加载,人们通常会删除索引,执行加载,然后重新创建索引,以便加载执行速度更快。
As far as the advantages and disadvantages - well that is a huge topic. I suggest you start here.
至于优缺点 - 那是一个很大的话题。我建议你从这里开始。
#3
1
In general, you set up the indexes during table creation. In this phase you should already have a good indication of how your data will be queried, and which fields will be used most as query criteria.
通常,您在表创建期间设置索引。在此阶段,您应该已经很好地指示了如何查询数据,以及哪些字段将最常用作查询条件。
During performance tuning, it is generally recommended to have the obvious indexes in place. During this phase, you will be able to understand if there are indexes that are not being used efficiently, or if there are queries where an index can increase performance. There is nothing stopping you for dropping or adding new indexes during performance tuning.
在性能调优期间,通常建议使用明显的索引。在此阶段,您将能够了解是否存在未被有效使用的索引,或者是否存在索引可以提高性能的查询。在性能调优期间,没有什么能阻止您删除或添加新索引。
#4
1
I'd add the 'obvious' indexes, ie on fields you know will be queried at table create time, and then add others as necessary as part of a tune. It's probably better to have fewer indexes at first and then get a feel for how the system is performing and being used - the profile is your friend hear.
我会添加“明显的”索引,即在表创建时查询的字段上,然后根据需要添加其他字段作为曲调的一部分。最初可以更少地使用较少的索引,然后了解系统的执行和使用方式 - 您的朋友可以听到该配置文件。
Advantages - faster access (when the index is being used), and the ability to enforce certain business logic like no duplicates.
优点 - 更快的访问(使用索引时),以及强制执行某些业务逻辑的能力,如无重复。
Disadvantages - table takes more space, inserting rows is slower (can be much slower), updates that touch the key field(s) are slower
缺点 - 表占用更多空间,插入行更慢(可能慢得多),触摸键字段的更新速度较慢