B-Tree vs Bitmap数据库索引 [英] B-Tree vs Bitmap database indexes

问题描述

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

解决方案

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

• B-树是在执行 CREATE INDEX时使用的典型索引类型。 .. 在数据库中：

  
  
  1. 当你选择一个非常小的子集索引数据（通常为5％-10％）
  
  
  2. 当您有很多不同的索引值时，它们工作得更好。
  
  
  3. 可以组合使用多个B-Tree索引，但更简单的方法通常更有效。
  
  
  4. 当索引数据的值不同时，或者希望获取大量（通常> 10％）的数据子集时，它们不起作用。
  
  
  5. 在索引表上插入/更新值时，每个B树索引都会受到小的惩罚。如果在非常繁忙的表中有很多索引，这可能是个问题。
  
  

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




• 位图索引是更专门的索引变量：

  
  
  1. 它们将索引值编码为位图，因此空间效率非常高。
  
  
  2. 当存在几个不同的索引值时，它们往往工作更好
  
  
  3. 数据库优化器可以非常容易地组合几个位图索引，
  
  


< br>位图索引主要用于数据仓库应用程序，其中除ETL进程之外，数据库是只读的，您通常需要对星型模式，其中位图索引可以根据维度表中的条件加快过滤，这些维度表通常不会有太多不同的值。






作为一个非常简短的摘要：使用B-Tree索引（大多数数据库中的默认索引），除非您是数据仓库开发人员并且知道您将受益于位图索引。




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.

解决方案

From wikipedia: B-Trees and bitmap indexes. The use cases:

• 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.

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

这篇关于B-Tree vs Bitmap数据库索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！