blocks|key|1512193|text|通常，在创建表期间设置索引。在这个阶段中，您应该已经很好地指示了您的数据将如何被查询，以及哪些字段将作为查询标准使用最多。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1512194|在性能调优期间，通常建议设置明显的索引。在此阶段，您将能够了解是否存在没有有效使用的索引，或者是否存在索引可以提高性能的查询。在性能调优期间，没有什么可以阻止您删除或添加新的索引。|1512195|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

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.

blocks|key|1512277|text|有一种平衡需要实现。如果您知道一个表将被查询，FieldA将是where子句的一部分，并且它是一个高度可选的字段()，那么它将成为抢占调优的一个很好的候选。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1512278|不要对所有类型的字段进行索引，因为您认为这是有意义的，我们必须知道这些事情。智者曾说过，过早的调整/优化是万恶之源。在这种情况下，索引可能会损害insert/update性能，因为不仅表数据需要更新，而且索引也需要更新。|1512279|附带注意-对于一些大的数据负载，人们通常会删除索引，执行加载，然后重新创建索引，以便负载执行得更快。|1512280|就利弊而言--这是一个很大的话题。我建议你从这里开始。|1512281|http://odetocode.com/articles/70.aspx|offset|length|1512282|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|0|0|11|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|U|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|V|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|W|8|@]|9|@$J|X|K|Y|1|Z]]|A|$]]|$1|L|3|-4|5|6|7|10|8|@]|9|@]|A|$]]]|M|$N|$5|O|P|Q|A|$R|I]]]]

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.

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.

<a href="http://odetocode.com/articles/70.aspx" rel="nofollow noreferrer">http://odetocode.com/articles/70.aspx</a>

blocks|key|1512308|text|我会添加“明显的”索引，即在您知道将在表创建时被查询的字段上，然后根据需要添加其他索引作为调子的一部分。最好先少用一些索引，然后才能感觉到系统是如何运行和正在使用的--这是你的朋友听到的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1512309|的优点是--更快的访问(当使用索引时)，以及强制执行某些业务逻辑(如不重复)的能力。|offset|length|style|BOLD|1512310|缺点表占用更多空间，插入行速度更慢(可能要慢得多)，接触键字段的更新速度更慢。|1512311|entityMap^0|0|0|4|0|0|2|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@$D|N|E|O|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@$D|Q|E|R|F|G]]|9|@]|A|$]]|$1|J|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|K|$]]

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

blocks|key|1512373|text|很多(大多数？)DBMS使用索引来支持唯一的约束。始终创建索引以强制执行唯一约束；它们(约束)对于数据库的正确操作至关重要。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1512374|如果您可以选择如何在多个列上创建索引，则将查询中始终引用的列放在其他字段之前--通常是。如果前面的列也有一定的选择性，这是最好的。|1512375|在您有了唯一性所必需的约束之后，请考虑执行引用完整性所需的约束。它们通常也是由DBMS授权的。同样，你不能让你的数据库处于不完整的状态--它是一个逻辑系统，如果它包含谬误，你可以从它证明任何东西，这是没有帮助的。|1512376|在处理了唯一性和引用完整性约束(索引)之后，您可能受益于也可能不会受益于其他约束。仔细选择，增加尽可能少的额外(零是一个好的数字)。每个索引减慢更新操作(更新、插入、删除)并使用存储空间。其意图是通过加快查询来赢得自己的位置。但是，不要忘记优化器必须考虑每个索引以及它在回答查询时是否有用，所以索引也会减慢优化器的速度(尽管您可能很难衡量这种效果)。|1512377|在添加索引时，将它们添加到选择性列中(不是包含“M”和“F”的“性别”，而是包含1900年至2010年出生日期的“道布”，或者可能比这更明显。考虑额外的列是否有助于回答更多的查询。一些DBMS+(如DB2)提供具有额外列的索引，这些列不是唯一性约束的一部分，但提供了查询中经常使用的列。它们允许只进行索引扫描(不需要访问表数据，因为所需的值都在索引中)。|1512378|还有很多可以说的，但这涵盖了很多地区。|1512379|1512380|的索引尽可能少，最好没有额外的索引。|unordered-list-item|1512381|1512382|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|U|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|V|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|W|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|X|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|M|3|N|5|O|7|Z|8|@]|9|@]|A|$]]|$1|P|3|-4|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|R|$]]

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.
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.
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).
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.
<ul>
<li>As few indexes as necessary — and preferably no extras.</li>
</ul>

<ul>
<li>When to set index for a table, during table creation or on performance tuning? </li>
<li>What are the advantages and disadvantages of indexing?</li>
</ul>

When should I create database indexes?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

何时为表设置索引，在创建表期间还是在性能调优时？索引的优点和缺点是什么？

问什么时候应该创建数据库索引？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问什么时候应该创建数据库索引？EN