mysql - 为什么此查询对于某些数据范围较慢，而对于其他数据范围较快？

Question

我的 MySQL 表中有大约 100 亿行坐标为 (x double, y double, z double) 的点。我已经在 x、y 和 z 列上创建了索引，因此按范围进行过滤非常快捷。

查询模板:({} 中的内容替换为实际值)

select id from points_table where
    x between {x-5} and {x+5} and
    y between {y-5} and {y+5} and
    z between {z-5} and {z+5};

现在，奇怪的是，尽管返回的结果数量大致相同，但对于某些点，此查询始终需要大约 0.05 秒，而对于其他点则需要大约 1.5 秒。

其中一个例子是，

对于 (x,y,z) = (1,5,-6) 查询大约需要 0.04 秒并获取 45 个结果，但是，

对于 (x,y,z) = (-2,0,3) 查询大约需要 1.20 秒并获取 38 个结果

这对我来说似乎很奇怪。造成这种行为的原因是什么？

编辑:根据要求，这里对两个查询进行解释，

explain select id from points_table where x between 1-5 and 1+5 and y between 5-5 and 5+5 and z between -6-5 and -6+5; 
+----+-------------+--------------+-------+-------------------+-------+---------+------+-------+-----------------------------------------------+
| id | select_type | tab          | type  | possible_keys     | key   | key_len | ref  | rows  | Extra                                         |
+----+-------------+--------------+-------+-------------------+-------+---------+------+-------+-----------------------------------------------+
|  1 | SIMPLE      | points_table | range | pnt_x,pnt_y,pnt_z | pnt_y | 9       | NULL | 18748 | Using index condition; Using where; Using MRR |
+----+-------------+--------------+-------+-------------------+-------+---------+------+-------+-----------------------------------------------+


explain select id from points_table where x between -2-5 and -2+5 and y between 0-5 and 0+5 and z between 3-5 and 3+5; 
+----+-------------+--------------+-------+-------------------+-------+---------+------+--------+-----------------------------------------------+
| id | select_type | tab          | type  | possible_keys     | key   | key_len | ref  | rows   | Extra                                         |
+----+-------------+--------------+-------+-------------------+-------+---------+------+--------+-----------------------------------------------+
|  1 | SIMPLE      | points_table | range | pnt_x,pnt_y,pnt_z | pnt_y | 9       | NULL | 235748 | Using index condition; Using where; Using MRR |
+----+-------------+--------------+-------+-------------------+-------+---------+------+--------+-----------------------------------------------+

我认为仅使用了 y 的索引，行数也有很大差异，这可以解释时间差距。

但现在我的问题是，

如何让 mysql 使用所有索引？他们在那里是有原因的。

编辑:

因此，我创建了一个复合键(x、y、z)，MySQL 在执行范围过滤时似乎更喜欢它，而且查询持续花费的时间更少。

Answer 1

查询计划将告诉您真正正在使用哪个索引 - 但出于讨论目的，我们假设始终使用 Y。

您可能会看到类似于索引 X 选择 18,748 行并且 X 和 Z 范围过滤掉除 45 行之外的所有行的情况，但对于第二个查询，索引 X 选择 235,748 行并且 X 和 Z 范围将结果过滤为38 行。

相同的查询、相同的执行计划(不计算 I/O 量)，但在第二个查询中处理更多数据，以获得类似的总行数结果。

使用 zerkms 建议的实际查询计划信息更新了此答案。