mysql - 在 SQL 中有效地连接区间范围

假设我有如下两个表(数据取自 SO post ):

表 d1 :

 x start end
 a     1   3
 b     5  11
 c    19  22
 d    30  39
 e     7  25

表 d2 :

 x pos
 a   2
 a   3
 b   3
 b  12
 c  20
 d  52
 e  10

两个表中的第一行都是列标题。我想提取 d2 中的所有行，其中列 x 与 d1 和 pos1 位于(包括边界值)d1 的 start 和 end 列之内。也就是说，我想要结果:

 x pos start  end
 a   2     1    3
 a   3     1    3
 c  20    19   22
 e  10     7   25

到目前为止，我看到这样做的方式是:

SELECT * FROM d1 JOIN d2 USING (x) WHERE pos BETWEEN start AND end

但我不清楚的是，这个操作是否尽可能高效(即内部优化)。例如，恕我直言，首先计算整个连接并不是真正的可扩展方法(就速度和内存而言)。

是否有任何其他有效的查询优化(例如:使用 interval trees )或其他算法可以有效地处理 SQL 中的范围(同样，在速度和内存方面)我可以使用？它是否使用 SQLite、PostgreSQL、mySQL 等都没有关系。

在 SQL 中执行此操作的最有效方法是什么？

非常感谢你。

最佳答案

不知道这一切如何在内部运作，但根据情况，我建议使用一个表格来“推出”来自 d1 的所有值，然后加入该表格。通过这种方式，查询引擎可以“准确地”查明正确的记录，而不必找到与要查找的值匹配的边界组合。

例如

x value
a  1
a  2
a  3
b  5
b  6
b  7
b  8
b  9
b 10
b 11
c 19 etc..

给定值列 (**) 上的索引，这应该比在原始 d1 表 IMHO 上加入 BETWEEN 开始和结束要快得多。

当然，每次对 d1 进行更改时，您也需要调整推出的表(触发器？)。如果这种情况经常发生，您将花费比最初获得的更多的时间来更新推出的表!此外，如果某些间隔非常大，这可能会很快占用相当多的(磁盘)空间；而且，这假设我们不需要查找非整数(例如，如果我们查找值 3.14 呢？)

(您可以考虑在此处尝试在 (value, x) 上使用唯一的一个...)

关于mysql - 在 SQL 中有效地连接区间范围，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/27433474/