SQL 有哪些不同类型的索引，每种索引有什么好处？

What are the different types of indexes, what are the benefits of each?

提问by Brian G

What are the different types of indexes, what are the benefits of each?

有哪些不同类型的索引，每种索引有什么好处？

I heard of covering and clustered indexes, are there more? Where would you use them?

我听说过覆盖索引和聚集索引，还有更多吗？你会在哪里使用它们？

采纳答案by Chris Shaffer

• Unique - Guarantees unique values for the column(or set of columns) included in the index
• Covering - Includes all of the columns that are used in a particular query (or set of queries), allowing the database to use only the index and not actually have to look at the table data to retrieve the results
• Clustered - This is way in which the actual data is ordered on the disk, which means if a query uses the clustered index for looking up the values, it does not have to take the additional step of looking up the actual table row for any data not included in the index.

• 唯一 - 保证索引中包含的列（或列集）的唯一值
• 覆盖 - 包括在特定查询（或查询集）中使用的所有列，允许数据库仅使用索引而实际上不必查看表数据来检索结果
• 聚集 - 这是在磁盘上对实际数据进行排序的方式，这意味着如果查询使用聚集索引来查找值，则不必采取额外步骤来查找任何数据的实际表行未纳入指数。

回答by Kevin Fairchild

OdeToCode has a good article covering the basic differences

OdeToCode 有一篇很好的文章，涵盖了基本差异

As it says in the article:

正如文章中所说：

Proper indexes are crucial for good performance in large databases. Sometimes you can make up for a poorly written query with a good index, but it can be hard to make up for poor indexing with even the best queries.

正确的索引对于大型数据库中的良好性能至关重要。有时你可以用一个好的索引来弥补一个写得不好的查询，但即使是最好的查询也很难弥补糟糕的索引。

Quite true, too... If you're just starting out with it, I'd focus on clustered and composite indexes, since they'll probably be what you use the most.

也确实如此...如果您刚刚开始使用它，我会专注于聚集索引和复合索引，因为它们可能是您最常使用的索引。

回答by mmaibaum

I'll add a couple of index types

我将添加几个索引类型

BITMAP - when you have very low number of different possible values, very fast and doesn't take up much space

位图 - 当您有非常少的不同可能值时，速度非常快并且不占用太多空间

PARTITIONED - allows the index to be partitioned based on some property usually advantageous on very large database objects for storage or performance reasons.

PARTITIONED - 允许基于某些属性对索引进行分区，这些属性通常在非常大的数据库对象上出于存储或性能原因而有利。

FUNCTION/EXPRESSION indexes - used to pre-calculate some value based on the table and store it in the index, a very simple example might be an index based on lower() or a substring function.

FUNCTION/EXPRESSION 索引 - 用于根据表预先计算一些值并将其存储在索引中，一个非常简单的示例可能是基于 lower() 或子字符串函数的索引。

回答by dland

PostgreSQL allows partial indexes, where only rows that match a predicate are indexed. For instance, you might want to index the customer table for only those records which are active. This might look something like:

PostgreSQL 允许部分索引，其中只对与谓词匹配的行进行索引。例如，您可能只想为那些处于活动状态的记录索引客户表。这可能类似于：

create index i on customers (id, name, whatever) where is_active is true;

If your index many columns, and you have many inactive customers, this can be a big win in terms of space (the index will be stored in fewer disk pages) and thus performance. To hit the index you need to, at a minimum, specify the predicate:

如果您索引许多列，并且您有许多不活动的客户，这在空间（索引将存储在更少的磁盘页面中）和性能方面可能是一个巨大的胜利。要命中索引，您至少需要指定谓词：

select name from customers where is_active is true;

回答by jimmyorr

Conventional wisdom suggests that index choice should be based on cardinality. They'll say,

传统观点认为索引选择应该基于基数。他们会说，

For a low cardinalitycolumn like GENDER, use bitmap. For a high cardinalitylike LAST_NAME, use b-tree.

对于像 GENDER 这样的低基数列，请使用位图。对于像 LAST_NAME 这样的高基数，请使用 b-tree。

This is not the case with Oracle, where index choice should instead be based on the type of application (OLTP vs. OLAP). DML on tables with bitmap indexes can cause serious lock contention. On the other hand, the Oracle CBO can easily combine multiple bitmap indexes together, and bitmap indexes can be used to search for nulls. As a general rule:

这不是 Oracle 的情况，索引选择应该基于应用程序的类型（OLTP 与 OLAP）。具有位图索引的表上的 DML 会导致严重的锁争用。另一方面，Oracle CBO 可以轻松地将多个位图索引组合在一起，位图索引可用于搜索空值。作为基本规则：

For an OLTPsystem with frequent DML and routine queries, use btree. For an OLAPsystem with infrequent DML and adhoc queries, use bitmap.

对于具有频繁 DML 和例行查询的OLTP系统，请使用 btree。对于不经常使用 DML 和即席查询的OLAP系统，请使用位图。

I'm not sure if this applies to other databases, comments are welcome. The following articles discuss the subject further:

我不确定这是否适用于其他数据库，欢迎评论。以下文章进一步讨论了该主题：

• Bitmap Index vs. B-tree Index: Which and When?
• Understanding Bitmap Indexes

• 位图索引与 B 树索引：哪个和何时？
• 了解位图索引

回答by David Aldridge

Oracle has various combinations of b-tree, bitmap, partitioned and non-partitioned, reverse byte, bitmap join, and domain indexes.

Oracle 具有 B 树、位图、分区和非分区、反向字节、位图连接和域索引的各种组合。

Here's a link to the 11gR1 documentation on the subject: http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/data_acc.htm#PFGRF004

这是有关该主题的 11gR1 文档的链接：http: //download.oracle.com/docs/cd/B28359_01/server.111/b28274/data_acc.htm#PFGRF004

回答by skaffman

Different database systems have different names for the same type of index, so be careful with this. For example, what SQL Server and Sybase call "clustered index" is called in Oracle an "index-organised table".

不同的数据库系统对同一类型的索引有不同的名称，所以要小心。例如，SQL Server 和 Sybase 所谓的“聚集索引”在 Oracle 中被称为“索引组织表”。

回答by MarlonRibunal

I suggest you search the blogs of Jason Massie (http://statisticsio.com/) and Brent Ozar (http://www.brentozar.com/) for related info. They have some post about real-life scenario that deals with indexes.

我建议您搜索 Jason Massie ( http://statisticsio.com/) 和 Brent Ozar ( http://www.brentozar.com/)的博客以获取相关信息。他们有一些关于处理索引的真实场景的帖子。

回答by abu

1. Unique
2. cluster
3. non-cluster
4. column store
5. Index with included column
6. index on computed column
7. filtered
8. spatial
9. xml
10. full text

1. 独特的
2. 簇
3. 非集群
4. 列存储
5. 包含列的索引
6. 计算列上的索引
7. 过滤
8. 空间的
9. xml
10. 全文

回答by Christopher Karsten

To view the types of indexes and its meaning visits: https://msdn.microsoft.com/en-us/library/ms175049.aspx

查看索引的类型及其含义访问：https: //msdn.microsoft.com/en-us/library/ms175049.aspx