在多于1列的B树索引是什么样子？ [英] what does a B-tree index on more than 1 column look like?

问题描述

所以我正在阅读索引及其实现，我偶然发现这个网站有一个b-tree索引的简要说明：

http://20bits.com/articles/interview-questions-database-indexes/ p>

b-tree索引对于只在单个列上的索引非常有意义，但是假设我创建了一个包含多个列的索引，那么b-tree是如何工作的？ b树中每个节点的值是多少？

例如，如果我有这个表：

  ：
 id number 
 name varchar 
 phone_number varchar 
 city varchar 
  

$ b b

，然后在：（id，name，city）

上创建索引，然后运行以下查询：

  SELECT id，name 
 FROM customer 
 WHERE city ='My City'; 
 

此查询如何利用多列索引，或者它不使用它，除非创建索引

解决方案

想象一下，键代表的是（city，id，name）一个Python元组（col1，col2，col3）...索引操作涉及比较 tuple_a 和 tuple_b ...如果你不知道col1和col2的哪个值你感兴趣，但只有col3，那么它将不得不读取整个索引（全索引扫描），这不是那么有效。

如果你有一个索引（col1，col2，col3），那么你可以期望任何RDBMS将使用索引（以直接方式），当WHERE子句包含引用1）所有3列（2）col1和col2（3）只有col1。

否则（例如WHERE子句中的col3），RDBMS不会使用所有索引（例如SQLite），或将做一个完整的索引扫描（例如Oracle）[如果没有其他索引更好]。

在你的具体例子中，该id是客户的唯一标识符，将其显示在索引中（除了DBMS应为主键或列标记为UNIQUE设置的索引之外）是无意义的。

So I was reading up on indexes and their implementation, and I stumbled upon this website that has a brief explanation of b-tree indexes:

http://20bits.com/articles/interview-questions-database-indexes/

The b-tree index makes perfect sense for indexes that are only on a single column, but let's say I create an index with multiple columns, how then does the b-tree work? What is the value of each node in the b-tree?

For example, if I have this table:

table customer:
id    number
name   varchar
phone_number   varchar
city   varchar

and I create an index on: (id, name, city)

and then run the following query:

SELECT id, name 
  FROM customer
 WHERE city = 'My City';

how does this query utilize the multiple column index, or does it not utilize it unless the index is created as (city, id, name) or (city, name, id) instead?

解决方案

Imagine that the key is represented by a Python tuple (col1, col2, col3) ... the indexing operation involves comparing tuple_a with tuple_b ... if you have don't know which value of col1 and col2 that you are interested in, but only col3, then it would have to read the whole index ("full index scan"), which is not as efficient.

If you have an index on (col1, col2, col3), then you can expect that any RDBMS will use the index (in a direct manner) when the WHERE clause contains reference to (1) all 3 columns (2) both col1 and col2 (3) only col1.

Otherwise (e.g. only col3 in the WHERE clause), either the RDBMS will not use that index at all (e.g. SQLite), or will do a full index scan (e.g. Oracle) [if no other index is better].

In your specific example, presuming that id is a unique identifier of a customer, it is pointless to have it appear in an index (other than the index that your DBMS should set up for a primary key or column noted as UNIQUE).

这篇关于在多于1列的B树索引是什么样子？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！