我有一个相当大的表(约 170 万行)的优化问题。

选择行时使用了两列，我们称它们为 colA 和 colB。它们都是“ double ”类型(小数点后 5 位)，范围从:

colA: -90 ~ 90 colB: -180 ~ 180

没有索引，任何形式的查询:

SELECT * FROM table where colA BETWEEEN a and b AND colB BETWEEN c and d

无论 (a,b) 和 (c,d) 的范围如何(因为 MySQL 必须检查每一行)，运行时间大致相同(~ 1 秒)。

如果我向 colA 和 colB 添加索引，会发生两件事:查询 (a,b) & (c,d) 范围较小的地方，例如:

SELECT * FROM table where colA BETWEEEN -4 and 4 AND colB BETWEEN 3 and 7

运行非常快(~ 1/10 秒)。但是，执行时间会随着查询值之间的范围而增加。例如:

 SELECT * FROM table where colA BETWEEEN -80 and 80 AND colB BETWEEN -150 and 150

执行大约需要一分钟。

我知道 B 树如何处理字符串，但我不确定数据为数字且使用范围进行查询时的机制。

如果有人可以建议如何优化此查询，我将不胜感激。一种想法是将索引用于小范围并告诉 MySQL 不要将它用于更大的范围，但是我找不到允许这样做的命令。

谢谢

编辑:解释

有件事我愚蠢地忘了提。结果按 rand() 排序 - 我知道这是多么低效，但我看不出有其他方法可以从表中随机获取有限数量的行。

添加 rand() 不会影响没有索引时的执行时间，但会大大增加有索引时的执行时间。

EDIT2:这是使用复合索引。

小范围:

“解释 select * from table where colA between 35 and 38 and colB between -10 and 5 ORDER BY RAND() LIMIT 20”

9783行

无索引(快)

+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | table | ALL  | NULL          | NULL | NULL    | NULL | 1673784 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

WITH INDEX(非常快)

+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | table | range | test          | test | 18      | NULL | 136222 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+

大范围:

“解释 select * from table where colA between -80 and 80 and colB between -150 and 150 ORDER BY RAND() LIMIT 20;”

1631862 行

无索引(快)

+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | table | ALL  | NULL          | NULL | NULL    | NULL | 1673784 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

WITH INDEX(非常慢:> 60 秒)

+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | table | ALL  | test          | NULL | NULL    | NULL | 1673784 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

编辑3:

总结:(所有查询限制为返回 20 行)

带有索引的 rand() 的大范围:45 秒
没有 rand() 的大范围，索引:0.003 秒

rand 大范围，无索引:1 秒
没有 rand 的大范围，没有索引:0.003 秒

异常是:“带有索引的 rand() 的大范围，45 秒”。

最佳答案

I know how B-trees works for strings, but I'm not sure of the mechanism when the data is numeric and the query is conducted using a range.

它们对数字的处理方式与对字符串的处理方式相同。

Without the index the query takes approximately the same time to run (~ 1 second), regardless of the range of (a,b), and (c,d)

全表扫描的运行时间不会随着 WHERE 条件的内容而显着变化。索引访问路径所花费的时间与返回的行数成正比。如果查询选择表的重要部分，使用索引总是比不使用索引慢。

索引访问路径只有在索引选择性足够时才是有效的，即检索的行数很少(有人说最多 10%)。执行时间将大致与返回的行数成正比，最终可能比全表扫描慢。

One thought is to use the index for small ranges and tell MySQL not to use it for larger ones, however I couldn't find a command which allows this.

查询优化器必须使用统计和启发式方法来确定是否应该使用索引。也许您需要使用 OPTIMIZE TABLE 更新这些统计信息.如果它仍然无法做出正确的决定，你可以帮助它 hints .

SELECT * FROM table 
   IGNORE INDEX (the_index)
   where colA BETWEEEN -80 and 80 AND colB BETWEEN -150 and 150

其他选项可能是删除索引(如果您从未从中看到任何好处，恒定的一秒响应时间可能就足够了)，或者尝试在两列上使用复合索引(也只有当从查询很小)。

既然您提到了 LIMIT 20，它就变得更有意义了:

big range with rand() with index: 45 seconds

有很多结果的嵌套循环 + 排序

从索引中获取所有记录(在范围内)，从表中一条一条取出，然后排序，然后限制为20条

big range without rand(), with index: 0.003 seconds

嵌套循环在 20 条记录处中止

从索引中获取20条记录，从表中一条一条取出，返回。没有排序，实际上没有大范围。

big range with rand, no index: 1 second

全表扫描+排序

通读整个表，保持在范围内，然后排序，然后限制为20

big range without rand, no index: 0.003 seconds

全表扫描，在 20 条记录处中止

开始阅读表格，保持范围内的内容，当读到 20 时停止并返回。

关于数字列上的 MySQL 索引使查询变慢，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/4445854/