database 什么是数据库索引？

What is a database index?

提问by Clay Wardell

I've heard them talked about since I started working in tech about 18 months ago. I know that they potentially improve performance, and they seem to be column specific -- ("We index the User table on the date_of_birth column").

自从我大约 18 个月前开始从事技术工作以来，我就听说过他们。我知道它们可能会提高性能，而且它们似乎是特定于列的——（“我们在 date_of_birth 列上索引 User 表”）。

Just looking for a quick overview of what exactly they are, what they are used for, and how they work.

只是想快速了解一下它们到底是什么、它们的用途以及它们是如何工作的。

回答by Markus Winand

I wrote a complete book about it! It's also available for free on the web: http://use-the-index-luke.com/

我写了一本关于它的完整书！它也可以在网络上免费获得：http: //use-the-index-luke.com/

I try to answer your questions shortly—which is not exactly what I'm good at. The last time I tried, I ended up writing a book...

我试着尽快回答你的问题——这不是我擅长的。最后一次尝试，我最终写了一本书......

Like tables, indexes consist of rows and columns but store the data in a logically sorted manner to improve search performance. Think of it like a telephone book (a printed one). They are usually sorted last_name, first_nameand potentially other criteria (e.g. zip code). This sorting makes it possible to find all entries for a specific last name quickly. If you know the first name too, you can even find the entries for the combination last name/first name very quickly.

与表一样，索引由行和列组成，但以逻辑排序的方式存储数据以提高搜索性能。把它想象成一本电话簿（印刷的）。它们通常被排序last_name，first_name可能还有其他标准（例如邮政编码）。这种排序可以快速找到特定姓氏的所有条目。如果您也知道名字，您甚至可以很快找到姓/名组合的条目。

If you just know the first name, however, the telephone book does not really help you. The very same is true for multi-column database indexes. So yes, an index can potentiallyimprove search performance. If you have the wrong index for your question (e.g. a phonebook when searching by first name) they might be useless.

但是，如果您只知道名字，电话簿并不能真正帮助您。多列数据库索引也是如此。所以是的，索引可以潜在地提高搜索性能。如果您的问题有错误的索引（例如按名字搜索时的电话簿），它们可能毫无用处。

You can have many indexes on the same table but on different columns. So, an index on last_name,first_nameis different from an index on first_nameonly (which you would need to optimize searches by first name).

您可以在同一个表上但在不同的列上有多个索引。因此，在一个指数last_name，first_name是从不同的指标上first_name只（你将需要优化搜索按名字）。

Indexes hold redundant data (ex: clustered indexes= telephone book). They have the same information as stored in the table (ex: function based indexes), but in a sorted manner. This redundancy is automatically maintained by the database for each write operation you perform (insert/update/delete). Consequently, indexed decreasewrite performance.

索引保存冗余数据（例如：聚集索引= 电话簿）。它们具有与存储在表中相同的信息（例如：基于函数的索引），但采用排序方式。对于您执行的每个写入操作（插入/更新/删除），数据库会自动维护此冗余。因此，索引会降低写入性能。

Besides finding data quickly, indexes can also be used to optimize sort operations(order by) and physically arrange related data closely together (clustering).

除了快速查找数据外，索引还可用于优化排序操作( order by) 并将相关数据物理地紧密排列在一起 (聚类)。

To get a better idea, look at the full table of contents of my book: http://use-the-index-luke.com/sql/table-of-contents

为了获得更好的主意，请查看我的书的完整目录：http: //use-the-index-luke.com/sql/table-of-contents

回答by Bjoern

Think of it as a table of contents for tables. If it's there, the database knows where to look more specific. If it ain't there, the database has to search through all the data to find it.

将其视为表格的目录。如果它在那里，数据库知道在哪里查看更具体。如果它不存在，数据库必须搜索所有数据才能找到它。

A way more detailed explanation can be found here in this Wikipedia article.

在这篇维基百科文章中可以找到更详细的解释。

回答by kiriloff

A database index is a datastructure aimed at improving the time complexity of lookup operation.

数据库索引是一种旨在提高查找操作时间复杂度的数据结构。

Lookup with no index is in worst case O(N)complexity. Efficient lookup with index enables logarithmic O(log(N))or even with some architechture O(1)complexity.

没有索引的查找在最坏情况下是O(N)复杂的。使用索引进行高效查找可以实现对数O(log(N))或什至具有某些架构O(1)复杂性。

A database index also make it possible to enforce DB constraints. Many DB systems set a index on a set of columns referred to as PRIMARY KEY. Some DB systems requires columns in a FOREIGN KEYto be indexed, so as to speed up operations (insert, update).

数据库索引还可以强制执行数据库约束。许多数据库系统在一组称为 的列上设置索引PRIMARY KEY。一些数据库系统需要对 a 中的列FOREIGN KEY进行索引，以加快操作（插入、更新）。

回答by Pamma

An index is an optional structure, associated with a table or table cluster, that can sometimes speed data access. By creating an index on one or more columns of a table, you gain the ability in some cases to retrieve a small set of randomly distributed rows from the table. Indexes are one of many means of reducing disk I/O.

索引是一种可选结构，与表或表簇相关联，有时可以加快数据访问速度。通过在表的一个或多个列上创建索引，您在某些情况下可以获得从表中检索一小组随机分布的行的能力。索引是减少磁盘 I/O 的众多方法之一。

If a heap-organized table has no indexes, then the database must perform a full table scan to find a value. For example, without an index, a query of location 2700 in the hr.departments table requires the database to search every row in every table block for this value. This approach does not scale well as data volumes increase.

如果堆组织表没有索引，则数据库必须执行全表扫描以找到值。例如，在没有索引的情况下，hr.departments 表中位置 2700 的查询要求数据库在每个表块中的每一行中搜索该值。随着数据量的增加，这种方法不能很好地扩展。

http://docs.oracle.com/cd/E11882_01/server.112/e10713/indexiot.htm

http://docs.oracle.com/cd/E11882_01/server.112/e10713/indexiot.htm

回答by Mayukh Roy

It has a very similar thread running here. Check, it is helpful.

它有一个非常相似的线程在这里运行。检查，它是有帮助的。

I know that they potentially improve performance

我知道它们可能会提高性能

Yes, it's true. But, please keep it in mind, sometimes indexing can be the reason of POOR Performanceas well. Example: Index all the columns of a database will undoubtedly affect the performance badly.

对，是真的。但是，请记住，有时索引也可能是性能不佳的原因。例子：索引一个数据库的所有列无疑会严重影响性能。