你们选择的例子就是少数几例,一只树木可以做这项工作(两个独立范围)。
然而,我在工作-进步电子-Book的第一章解释了B-Tree指数的内部工作:http://use-the-index-luke.com/anatomy/。
EDIT for more details why two indexes might be useful for the above example.
以上问题可由三个可能的指数组合予以支持:
concatenated index on AGE and then WEIGHT (in this order).
In case, the query would read all records WHERE AGE > 50 and then filter by WEIGHT.
concatenated index on WEIGHT and then AGE (the other order).
That goes the different way: read all records WHERE WEIGHT < 100 and then filter by AGE.
无论哪一种效率更高,都取决于你掌握的数据。 如果记录低于<代码>AGE > 50> 低于WEOW <100<>/code>,则第一个记录将更为有效,否则第二个记录将更有效。 然而,如果你问到不同的价值观,情况可能会改变。
压缩指数不能支持询问,其原因是,每个指数单上只一个轴。 每一指数条目在另一条目之前或之后,但从来都不在后面。 所有指数条目都有一链条。
有两个独立的范围问题需要两个轴心,而不是像一个链条,而是像一个焦炭板。 <代码>AGE的轴心,另一轴为WEless。 如果可能的话,您的询问只需要扫描一角的ches。
然而,树只有一个轴,因此,你必须选择哪些标准首先使用。 如果你选择<代码>AGE,这意味着从AGE 50开始,整个链条将作扫描直至尾为止。 只有在链条一侧储存的一些记录也符合以下条件:<条码>。
So, a long story to explain why one tree can not support a query with two independent range clauses. On the other hand, one concatenated index can do the following quite well:
WHERE age = 50 AND weight < 100
WHERE weight = 100 AND age > 50
WHERE age > 50 AND age < 70;
然而,如果在两个不同的栏目上使用两个不平等的操作者,就会产生问题。
因此,需要做些什么?
The third possible approach is to have two independent indexes on the two columns. That allows to have as many axes as you like (just create more indexes). However, there are a few huge problems with that. First of all, not all DB products support that. Whenever it is supported, it is a rather expansive operation. It works typically that way that each index is scanned, a bitmap index is built for each result. Those bitmap indexes are then joined to apply the AND operator. That s a lot of data munging--it is only worth the effort if each condition is not very selective for it s own, but both together are very selective.
我的建议吗?
If your query runs in an OLTP environment: use one concatenated index.
Two independent indexes are an option of last resort only. However, if you are working in an OLAP environment, you might anyways need bitmap indexes.
ps.:
Indexing AGE was an exercise in my book (with solution)--especially because storing AGE is a bad practice, you should store the date of birth instead.
