优化mysql查询，使用空间索引选择多边形中的所有点

Question

首先，我承认我对空间函数的经验非常少。我在MySQL中有一个包含20个字段和23549187条记录的表，其中包含地理数据。其中一个字段是"点"，它是点数据类型，上面有空间索引

select * from `table_name` where ST_CONTAINS(ST_GEOMFROMTEXT('POLYGON((151.186 -23.497,151.207 -23.505,151.178 -23.496,151.174 -23.49800000000001,151.176 -23.496,151.179 -23.49500000000002,151.186 -23.497))'), `point`)

这在多边形较小的情况下效果良好。然而，如果多边形变得巨大，执行时间就会变得非常慢，并且迄今为止最慢的查询会运行15分钟。添加该指数确实有助于将其降至15分钟，否则将需要近一个小时。我能在这里做些什么来进一步改进吗。这个查询将由作为守护进程运行的PHP脚本运行，我担心这个缓慢的查询是否会导致MySQL服务器停机。

欢迎所有改进建议。谢谢

编辑：

show create table;
CREATE TABLE `table_name` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`lat` float(12,6) DEFAULT NULL,
`long` float(12,6) DEFAULT NULL,
`point` point NOT NULL,
PRIMARY KEY (`id`),
KEY `lat` (`lat`,`long`),
SPATIAL KEY `sp_index` (`point`)
) ENGINE=MyISAM AUTO_INCREMENT=47222773 DEFAULT CHARSET=utf8mb4

还有几个领域，我不应该在这里披露它，然而过滤器赢得了

解释慢速查询的sql输出：

+----+-------------+------------+------+---------------+------+---------+------+----------+-------------+|id|select_type|table|type|possible_keys|key_len|ref|rows|Extra|+----+-------------+------------+------+---------------+------+---------+------+----------+-------------+|1|SIMPLE|table_name|ALL|NULL|NULL |NULL |23549187|使用where|+----+-------------+------------+------+---------------+------+---------+------+----------+-------------+

解释使用较小多边形的查询的sql输出，

+----+-------------+------------+-------+---------------+----------+---------+------+------+-------------+|id|select_type|table|type|possible_keys|key_len|ref|rows|Extra|+----+-------------+------------+-------+---------------+----------+---------+------+------+-------------+|1|SIMPLE|table_name|range|sp_index|sp_index|34|NULL|1|使用where|+----+-------------+------------+-------+---------------+----------+---------+------+------+-------------+

看起来最大的多边形没有使用索引。

Answer 1

MySQL使用R-Trees对空间数据进行索引。与B-树索引一样，这些索引对于针对总数的一小部分的查询来说是最优的。随着边界多边形越来越大，可能的匹配数量也会增加，在某个时候，优化器会决定切换到全表扫描更有效。这似乎就是这里的场景，我看到了三个选项：

首先，尝试将LIMIT添加到查询中。通常，如果优化器得出结论，在全表扫描中会发生较少的I/O查找，MySQL会忽略索引。但是，至少有了B-Tree索引，MySQL将缩短该逻辑，并始终在LIMIT存在时执行B-Tree俯冲。我假设R-Tree也有类似的短路。

第二，在精神上与第一个类似，尝试强制MySQL使用索引。这指示MySQL，表扫描比优化器决定的要贵。要明白，优化器只有启发式方法，并不真正知道超出其内部统计结论的东西有多"昂贵"。我们人类有直觉，有时——有时——更清楚。

select * force index (`sp_index`) from `table_name` where ST_CONTAINS(ST_GEOMFROMTEXT('POLYGON((151.186 -23.497,151.207 -23.505,151.178 -23.496,151.174 -23.49800000000001,151.176 -23.496,151.179 -23.49500000000002,151.186 -23.497))'), `point`)

最后，如果这些都不起作用，那么你需要做的是将边界多边形分解成更小的多边形。例如，如果边界多边形是每侧500公里的正方形，则将其分解为每侧250公里的4个正方形，或每侧125公里的16个正方形，等等。然后将所有这些集合UNION。索引将用于每个索引，累积结果可能更快。(注意，将它们放在一起UNION很重要：MySQL不能对空间查询应用多个范围扫描。(