数据库表中索引的缺点是什么？ [英] What are the disadvantages to Indexes in database tables?

问题描述

我是否有任何理由不应该为我的每个数据库表创建索引，以此来提高性能？似乎必须有一些原因，否则所有表默认都会自动具有一个。

Is there any reason I shouldn't create an index for every one of my database tables, as a means of increasing performance? It would seem there must be some reason(s) else all tables would automatically have one by default.

我使用MS SQL Server 2016。

I use MS SQL Server 2016.

推荐答案

表上的一个索引并不重要。您会自动在作为主键或声明为唯一的列（或列组合）上建立索引。

One index on a table is not a big deal. You automatically have an index on columns (or combinations of columns) that are primary keys or declared as unique.

索引有一些开销。索引本身占用磁盘和内存的空间（使用时）。因此，如果空间或内存成为问题，那么索引过多可能会成为问题。插入/更新/删除数据时，索引和原始数据都需要维护。这样会减慢更新速度并锁定表（或表的部分），从而影响查询处理。

There is some overhead to an index. The index itself occupies space on disk and memory (when used). So, if space or memory are issues then too many indexes could be a problem. When data is inserted/updated/deleted, then the index needs to be maintained as well as the original data. This slows down updates and locks the tables (or parts of the tables), which can affect query processing.

每个表上的少量索引是合理的。这些设计时应考虑典型的查询负载。如果您索引每个表中的每一列，那么数据修改将变慢。如果您的数据是静态的，那么这不是问题。但是，用索引耗尽所有内存可能是一个问题。

A small number of indexes on each table are reasonable. These should be designed with the typical query load in mind. If you index every column in every table, then data modifications would slow down. If your data is static, then this is not an issue. However, eating up all the memory with indexes could be an issue.

这篇关于数据库表中索引的缺点是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！