如何加速使用BETWEEN条件的SQL查询

Question

我有一个如下所示的表：

ID    StartRange    EndRange
----------------------------
1        1            3
2        4            8
3        9           12

等等，所以有超过500万张记录。最后一张唱片看起来像这样：

ID        StartRange    EndRange
---------------------------------
5235976   9894727374   9894727378

换句话说，对于每个记录，StartRange和EndRange将永远不会重叠。

我需要做一个查询，找到与范围匹配的号码的相应ID：

SELECT ID FROM BigTable WHERE '5000000' BETWEEN StartRange AND EndRange;

遗憾的是，此查询需要几秒钟才能完成。我需要对它进行优化，以使它占用最少的执行时间。我做了一点研究，添加索引似乎没有帮助，因为只有当数字正好是StartRange或EndRange值时，它才适用，但如果介于两者之间，则不适用。

有人有什么技巧或窍门可以用来降低执行时间吗？理想情况下，如果可能的话，我希望它在1秒以下。

Answer 1

我在一个ip地址范围表上遇到了类似的问题，下面的内容真的帮了我一把。你至少需要一个StartRange的索引。

SELECT ID
FROM BigTable
INNER JOIN
(SELECT MAX(StartRange) AS start
FROM BigTable
WHERE StartRange <= @Target) AS s
ON StartRange = s.start
WHERE EndRange >= @Target;

Answer 2

向表中添加一个复合索引。该索引必须由StartRange和EndRange字段组成：

ALTER TABLE `BigTable` ADD INDEX ( `StartRange` , `EndRange` );

然后在查询中使用EXPLAIN来检查是否使用了新索引：

EXPLAIN SELECT ID FROM BigTable WHERE '5000000' BETWEEN StartRange AND EndRange;

输出显示MySQL无法将新索引用于此查询。然后，您可以重写您的初始查询：

SELECT ID FROM BigTable WHERE StartRange>='5000000' AND EndRange<='5000000'
OR EndRange>='5000000' AND StartRange<='5000000'

此新查询将返回与初始查询相同的结果。好消息是EXPLAIN:

EXPLAIN SELECT ID FROM BigTable WHERE StartRange>='5000000' AND EndRange<='5000000'
OR EndRange>='5000000' AND StartRange<='5000000'

现在的输出显示MySQL能够使用新的索引。

Answer 3

即使值与StartRange和EndRange不匹配，索引也应该能很好地处理此查询。

Answer 4

索引不会加快此查询的速度。索引可以用于BETWEEN搜索，但仅当它们"正确"时(例如StartRange BETWEEN 10000 AND 20000)。

为了加快这个查询的速度，您将不得不使用一些技巧。

首先，如果范围表是静态的或增长不快，并且范围值实际上是整数，则可以生成一个额外的表，其中包含从最低StartRange到最高EndRange的所有值以及匹配的id。然后，您可以搜索所需的确切值。

或者，计算EndRange-StartRange的最大值，并将其称为MaxRange。在StartRange上创建索引并将查询更改为：

SELECT ID FROM BigTable 
WHERE StartRange BETWEEN ('5000000' - MaxRange) AND '5000000' 
AND '5000000' BETWEEN StartRange AND EndRange;

现在，第一个BETWEEN子句是可索引的，并且应该返回少量行。然后，第二个BETWEEN子句将仅应用于那一小部分行。显然，这取决于您是否能够提前计算MaxRange的安全值。希望这个范围有一个实际的最大可能值，可以告诉你这个数字。