I am creating a new DB and was wondering if there was any downside to adding numerous indexes to tables that I think may require one.
我正在创建一个新的DB，我想知道向表中添加许多索引是否有什么缺点，我认为可能需要一个索引。
If I create an index but end up not utilizing it will it cause any issues?
如果我创建了一个索引但最终没有使用它，会不会引起任何问题?
12 个解决方案
#1
27
Indexes make it faster to search tables, but longer to write to. Having unused indexes will end come causing some unnecessary slow down.
索引使搜索表更快，但写入表的时间更长。拥有未使用的索引将导致一些不必要的慢下来。
#2
16
Each Index :
每个索引:
- Takes some place, on disk, and in RAM
- 在磁盘和RAM中占据某个位置
- Takes some time to update, each time you insert/update/delete a row
- 每次插入/更新/删除一行时，都需要一些时间进行更新
Which means you should only define indexes that are useful for your application's needs : too many indexes will slow down the writes more than you'll gain from the reads.
这意味着您应该只定义对应用程序的需求有用的索引:太多的索引将会比您从读取中获得的更多地降低写操作的速度。
#3
5
Yes. You should only add those indexes, that are necessary.
是的。您应该只添加那些必要的索引。
An index requires extra space, and, when inserting / updating / deleting records, the DBMS needs to update those indexes as well. So, this means that it takes more time to update/add/delete a record, since the DBMS has to do some extra administration.
索引需要额外的空间，当插入/更新/删除记录时，DBMS也需要更新这些索引。因此，这意味着更新/添加/删除记录需要更多的时间，因为DBMS必须进行一些额外的管理。
adding numerous indexes to tables that I think may require one.
向我认为可能需要一个索引的表添加许多索引。
You should only add those indexes for which you're sure that they're necessary. To determine the columns where you could put indexes on, you could:
您应该只添加那些您确信必要的索引。要确定可以放置索引的列，您可以:
- add indexes to columns that are foreign keys
- 向外键列添加索引
- add indexes to columns that are often used in where clauses
- 向where子句中经常使用的列添加索引
- add indexes to columns that are used in order by clauses.
- 向按子句顺序使用的列添加索引。
Another -and perhaps better- approach, is to use SQL Profiler:
另一种可能更好的方法是使用SQL分析器:
- use SQL Profiler to trace your application / database for a while
- 使用SQL分析器跟踪应用程序/数据库一段时间
- save the trace results
- 保存跟踪结果
- use the trace results in the Index Tuning Wizard, which will tell you which indexes you should create, what columns should be in each index, and it will also tell you the order of those columns for the index.
- 在索引调优向导中使用跟踪结果，它将告诉您应该创建哪些索引，每个索引中应该包含哪些列，并且它还将告诉您这些列对于索引的顺序。
#4
4
Indexes cause an increase in database size and the amount of time to insert/update/delete records. Try not to add them unless you know you will use them.
索引会增加数据库大小和插入/更新/删除记录的时间。尽量不要添加它们，除非您知道您将使用它们。
#5
1
Having an index means INSERTs and UPDATEs take a bit longer. If you have too many indexes, then the benefit of faster search times can become not worth the extra INSERT and UPDATE time.
拥有索引意味着插入和更新要花费更长的时间。如果有太多索引，那么更快的搜索时间的好处就不值得额外的插入和更新时间了。
#6
1
Yes; having an index makes selects faster but potentially makes inserts slower, as the indexes must be updated for each insert. If your application does a lot of writing and not much reading (e.g. an audit log) you should avoid extra indexing.
是的,拥有索引可以使选择更快，但可能会使插入更慢，因为每个插入都必须更新索引。如果你的应用程序写了很多东西，读了很少(比如审计日志)，你应该避免额外的索引。
#7
1
- update and insert cost more as indexes need to be updated as well
- 更新和插入成本更高，因为索引也需要更新
- more space used
- 更多的空间使用
#8
1
Don't create any extra indices at the begining. Wait until you've at least partly developed the system so you can have an idea about the usage of the table. Generate query plans to see what gets queried (and how, and the performance costs) and THEN add new indices as needed.
不要在一开始就创建任何额外的索引。等到您至少对系统进行了部分开发之后，才能对该表的使用有一个概念。生成查询计划，查看查询内容(以及查询方式和性能成本)，然后根据需要添加新的索引。
#9
1
Do not index blindly! Take a look at your data to see which columns are actually being used in SELECT predicates and index those.
不要盲目指数!查看您的数据，看看在SELECT谓词和索引中实际使用了哪些列。
Also consider that indexes take room. Sometimes a lot of room. I have seen databases where the indexing data far outweighed the raw data in sheer volume.
还要考虑索引的使用空间。有时候房间很大。我曾见过一些数据库，索引数据在数量上远远超过原始数据。
#10
1
Extra space, Extra time to insert like everyone has said.
额外的空间，额外的时间插入像每个人说的。
Also, you should be certain of your indexes and your design because sometimes indexes can actually slow down queries if the query optimizer chooses the wrong index. This is uncommon but can happen if by optimizing an index for a particular join and causing another join to actually become slower. I don't know about SQL Server but you'll find lots of tricks around for hinting the mySQL optimizer to build queries in specific ways to get around this.
此外，您应该确定自己的索引和设计，因为如果查询优化器选择了错误的索引，有时索引实际上会减慢查询速度。这是不常见的，但如果通过优化特定连接的索引并导致另一个连接实际上变得更慢，就会发生这种情况。我不知道SQL Server，但是您会发现很多提示mySQL优化器构建查询的技巧。
DBA's get payed a lot of money to know about weird gotcha's like this with indexes(among other things) so yeah, there are downsides to adding lots of indexes so be careful. Lean heavily on your query profiler and don't just throw indexes blindly at problems.
DBA要花很多钱去了解这种奇怪的陷阱(包括索引)，所以添加很多索引也有缺点，所以要小心。大量依赖于查询分析器，不要盲目地对问题抛出索引。
#11
1
Take a look at the columns used in your where clauses, look at columns used in joins if any. Generally the very simplest rule of thumb. Extraneous indexes as pointed out before will slow down your DML statements and are generally not recommended. Ideally, what I have done is finish the entire module and during your unit testing phase, ensure that you can do load analysis on the module and then check whether or not you are seeing slow downs and after analyzing where the slow downs are, add indexes.
查看where子句中使用的列，查看连接中使用的列(如果有的话)。通常是最简单的经验法则。前面指出的无关索引会减慢DML语句的速度，通常不建议这样做。理想情况下，我所做的是完成整个模块，在单元测试阶段，确保您可以对模块进行负载分析，然后检查您是否看到了慢下来，在分析慢下来的地方之后，添加索引。
#12
1
I think it's been answered already, but basically indexes slow down inserts/updates as the index is updated when a new record is inserted (or an existing one updated).
我认为已经得到了回答，但是基本上索引会减慢插入/更新，因为当插入一个新记录(或者更新一个现有记录)时，索引会被更新。
Space is also a consideration, both memory and disk.
内存和磁盘也需要考虑空间。
So for databases where a large amount of transactions are occurring, this will definitely have a noticeable performance impact (which is why performance tuning includes adding and removing indexes to optimize certain activities performed against the database).
因此，对于正在发生大量事务的数据库，这肯定会产生显著的性能影响(这就是为什么性能调优包括添加和删除索引以优化针对数据库执行的某些活动)。
Good luck
祝你好运
#1
27
Indexes make it faster to search tables, but longer to write to. Having unused indexes will end come causing some unnecessary slow down.
索引使搜索表更快，但写入表的时间更长。拥有未使用的索引将导致一些不必要的慢下来。
#2
16
Each Index :
每个索引:
- Takes some place, on disk, and in RAM
- 在磁盘和RAM中占据某个位置
- Takes some time to update, each time you insert/update/delete a row
- 每次插入/更新/删除一行时，都需要一些时间进行更新
Which means you should only define indexes that are useful for your application's needs : too many indexes will slow down the writes more than you'll gain from the reads.
这意味着您应该只定义对应用程序的需求有用的索引:太多的索引将会比您从读取中获得的更多地降低写操作的速度。
#3
5
Yes. You should only add those indexes, that are necessary.
是的。您应该只添加那些必要的索引。
An index requires extra space, and, when inserting / updating / deleting records, the DBMS needs to update those indexes as well. So, this means that it takes more time to update/add/delete a record, since the DBMS has to do some extra administration.
索引需要额外的空间，当插入/更新/删除记录时，DBMS也需要更新这些索引。因此，这意味着更新/添加/删除记录需要更多的时间，因为DBMS必须进行一些额外的管理。
adding numerous indexes to tables that I think may require one.
向我认为可能需要一个索引的表添加许多索引。
You should only add those indexes for which you're sure that they're necessary. To determine the columns where you could put indexes on, you could:
您应该只添加那些您确信必要的索引。要确定可以放置索引的列，您可以:
- add indexes to columns that are foreign keys
- 向外键列添加索引
- add indexes to columns that are often used in where clauses
- 向where子句中经常使用的列添加索引
- add indexes to columns that are used in order by clauses.
- 向按子句顺序使用的列添加索引。
Another -and perhaps better- approach, is to use SQL Profiler:
另一种可能更好的方法是使用SQL分析器:
- use SQL Profiler to trace your application / database for a while
- 使用SQL分析器跟踪应用程序/数据库一段时间
- save the trace results
- 保存跟踪结果
- use the trace results in the Index Tuning Wizard, which will tell you which indexes you should create, what columns should be in each index, and it will also tell you the order of those columns for the index.
- 在索引调优向导中使用跟踪结果，它将告诉您应该创建哪些索引，每个索引中应该包含哪些列，并且它还将告诉您这些列对于索引的顺序。
#4
4
Indexes cause an increase in database size and the amount of time to insert/update/delete records. Try not to add them unless you know you will use them.
索引会增加数据库大小和插入/更新/删除记录的时间。尽量不要添加它们，除非您知道您将使用它们。
#5
1
Having an index means INSERTs and UPDATEs take a bit longer. If you have too many indexes, then the benefit of faster search times can become not worth the extra INSERT and UPDATE time.
拥有索引意味着插入和更新要花费更长的时间。如果有太多索引，那么更快的搜索时间的好处就不值得额外的插入和更新时间了。
#6
1
Yes; having an index makes selects faster but potentially makes inserts slower, as the indexes must be updated for each insert. If your application does a lot of writing and not much reading (e.g. an audit log) you should avoid extra indexing.
是的,拥有索引可以使选择更快，但可能会使插入更慢，因为每个插入都必须更新索引。如果你的应用程序写了很多东西，读了很少(比如审计日志)，你应该避免额外的索引。
#7
1
- update and insert cost more as indexes need to be updated as well
- 更新和插入成本更高，因为索引也需要更新
- more space used
- 更多的空间使用
#8
1
Don't create any extra indices at the begining. Wait until you've at least partly developed the system so you can have an idea about the usage of the table. Generate query plans to see what gets queried (and how, and the performance costs) and THEN add new indices as needed.
不要在一开始就创建任何额外的索引。等到您至少对系统进行了部分开发之后，才能对该表的使用有一个概念。生成查询计划，查看查询内容(以及查询方式和性能成本)，然后根据需要添加新的索引。
#9
1
Do not index blindly! Take a look at your data to see which columns are actually being used in SELECT predicates and index those.
不要盲目指数!查看您的数据，看看在SELECT谓词和索引中实际使用了哪些列。
Also consider that indexes take room. Sometimes a lot of room. I have seen databases where the indexing data far outweighed the raw data in sheer volume.
还要考虑索引的使用空间。有时候房间很大。我曾见过一些数据库，索引数据在数量上远远超过原始数据。
#10
1
Extra space, Extra time to insert like everyone has said.
额外的空间，额外的时间插入像每个人说的。
Also, you should be certain of your indexes and your design because sometimes indexes can actually slow down queries if the query optimizer chooses the wrong index. This is uncommon but can happen if by optimizing an index for a particular join and causing another join to actually become slower. I don't know about SQL Server but you'll find lots of tricks around for hinting the mySQL optimizer to build queries in specific ways to get around this.
此外，您应该确定自己的索引和设计，因为如果查询优化器选择了错误的索引，有时索引实际上会减慢查询速度。这是不常见的，但如果通过优化特定连接的索引并导致另一个连接实际上变得更慢，就会发生这种情况。我不知道SQL Server，但是您会发现很多提示mySQL优化器构建查询的技巧。
DBA's get payed a lot of money to know about weird gotcha's like this with indexes(among other things) so yeah, there are downsides to adding lots of indexes so be careful. Lean heavily on your query profiler and don't just throw indexes blindly at problems.
DBA要花很多钱去了解这种奇怪的陷阱(包括索引)，所以添加很多索引也有缺点，所以要小心。大量依赖于查询分析器，不要盲目地对问题抛出索引。
#11
1
Take a look at the columns used in your where clauses, look at columns used in joins if any. Generally the very simplest rule of thumb. Extraneous indexes as pointed out before will slow down your DML statements and are generally not recommended. Ideally, what I have done is finish the entire module and during your unit testing phase, ensure that you can do load analysis on the module and then check whether or not you are seeing slow downs and after analyzing where the slow downs are, add indexes.
查看where子句中使用的列，查看连接中使用的列(如果有的话)。通常是最简单的经验法则。前面指出的无关索引会减慢DML语句的速度，通常不建议这样做。理想情况下，我所做的是完成整个模块，在单元测试阶段，确保您可以对模块进行负载分析，然后检查您是否看到了慢下来，在分析慢下来的地方之后，添加索引。
#12
1
I think it's been answered already, but basically indexes slow down inserts/updates as the index is updated when a new record is inserted (or an existing one updated).
我认为已经得到了回答，但是基本上索引会减慢插入/更新，因为当插入一个新记录(或者更新一个现有记录)时，索引会被更新。
Space is also a consideration, both memory and disk.
内存和磁盘也需要考虑空间。
So for databases where a large amount of transactions are occurring, this will definitely have a noticeable performance impact (which is why performance tuning includes adding and removing indexes to optimize certain activities performed against the database).
因此，对于正在发生大量事务的数据库，这肯定会产生显著的性能影响(这就是为什么性能调优包括添加和删除索引以优化针对数据库执行的某些活动)。
Good luck
祝你好运