SQL B 树与位图数据库索引

B-Tree vs Bitmap database indexes

提问by Luke101

Can someone explain the different between the bitmap and b tree indexes. in what situations will you use both of these? What are the advantages/disadvantages of each.

有人可以解释位图和 b 树索引之间的区别。在什么情况下你会使用这两种方法？每个的优点/缺点是什么。

回答by gpeche

From wikipedia: B-Treesand bitmap indexes. The use cases:

来自维基百科：B 树和位图索引。用例：

• B-Trees are the typical index type used when you do CREATE INDEX ...in a database:

  1. They are very fast when you are selecting just a small very subset of the index data (5%-10% max typically)
  2. They work better when you have a lot of distinct indexed values.
  3. Combining several B-Tree indexes can be done, but simpler approaches are often more efficient.
  4. They are not useful when there are few distinct values for the indexed data, or when you want to get a large (>10% typically) subset of the data.
  5. Each B-Tree index impose a small penalty when inserting/updating values on the indexed table. This can be a problem if you have a lot of indexes in a very busy table.

  
  This characteristics make B-Tree indexes very useful for speeding searches in OLTP applications, when you are working with very small data sets at a time, most queries filter by ID, and you want good concurrent performance.

• Bitmap indexes are a more specialized index variant:

  1. They encode indexed values as bitmaps and so are very space efficient.
  2. They tend to work better when there are few distinct indexed values
  3. DB optimizers can combine several bitmap indexed very easily, this allows for efficient execution of complex filters in queries.
  4. They are very inefficient when inserting/updating values.

  
  Bitmap indexes are mostly used in data warehouse applications, where the database is read only except for the ETL processes, and you usually need to execute complex queries against a star schema, where bitmap indexes can speed up filtering based on conditions in your dimension tables, which do not usually have too many distinct values.

• B-Trees 是CREATE INDEX ...在数据库中使用的典型索引类型：

  1. 当您只选择索引数据的一个很小的子集时，它们非常快（通常最大为 5%-10%）
  2. 当您有很多不同的索引值时，它们会更好地工作。
  3. 可以组合多个 B 树索引，但更简单的方法通常更有效。
  4. 当索引数据的不同值很少时，或者当您想要获得大的（通常>10%）数据子集时，它们没有用。
  5. 在索引表上插入/更新值时，每个 B 树索引都会施加一个小的惩罚。如果在一个非常繁忙的表中有很多索引，这可能是一个问题。

  
  这种特性使得 B 树索引对于加速 OLTP 应用程序中的搜索非常有用，当您一次处理非常小的数据集时，大多数查询都按 ID 过滤，并且您需要良好的并发性能。

• 位图索引是一种更专业的索引变体：

  1. 它们将索引值编码为位图，因此非常节省空间。
  2. 当不同的索引值很少时，它们往往会更好地工作
  3. DB 优化器可以很容易地组合多个索引的位图，这允许在查询中有效执行复杂的过滤器。
  4. 它们在插入/更新值时效率非常低。

  
  位图索引主要用在数据仓库应用中，除了ETL过程，数据库是只读的，你通常需要对星型模式执行复杂的查询，位图索引可以根据维度表中的条件加快过滤速度，通常没有太多不同的值。

As a very short summary: use B-Tree indexes (the "default" index in most databases) unless you are a data warehouse developer and knowyou will benefit for a bitmap index.

作为一个非常简短的总结：使用 B 树索引（大多数数据库中的“默认”索引），除非您是一名数据仓库开发人员并且知道您将从位图索引中受益。

回答by Pankaj Mandale

When using normal BTree indexes, rows where all the column values are null are excluded from the index. This means that queries with "column is null" conditions won't benefit from a normal index.

使用普通 BTree 索引时，所有列值为 null 的行将从索引中排除。这意味着具有“列为空”条件的查询不会从正常索引中受益。

By creating an index on (column_name, 1) (or some other constant) null valued columns are now included in it, allowing the optimizer to use the query when executing "is null" queries.

通过在 (column_name, 1)（或一些其他常量）空值列上创建索引，现在其中包含空值列，允许优化器在执行“is null”查询时使用该查询。

A bitmap index, unlike a B*Tree index, automatically includes null values. This means bitmap indexes may be used by the optimizer in the evaluation of "is null" predicates.

与 B*Tree 索引不同，位图索引会自动包含空值。这意味着位图索引可以被优化器用于评估“is null”谓词。

Bitmap indexes may lead to concurrency issues however, possibly blocking other DML on the same table. Therefore these should be avoided in an OLTP applications. Bitmap indexes also require Enterprise Edition, so there may be licensing implications to using these.

然而，位图索引可能会导致并发问题，可能会阻塞同一表上的其他 DML。因此，在 OLTP 应用程序中应该避免这些。位图索引也需要企业版，因此​​使用这些索引可能会涉及许可问题。