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

wsxa1bj1  于 2021-06-18  发布在  Mysql
关注(0)|答案(1)|浏览(407)

首先,我承认我在空间功能方面的经验很少。我在mysql中有一个表,包含20个字段和23549187条记录,其中包含地理数据。其中一个字段是“point”,它是点数据类型,上面有空间索引。我有一个查询,它选择了一个多边形中的所有点,看起来像这样,

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  | key_len | ref  | rows     | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | table_name | ALL  | NULL          | NULL | NULL    | NULL | 23549187 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+----------+-------------+

解释具有较小多边形的查询的sql输出,

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

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

5t7ly7z5

5t7ly7z51#

mysql使用r树索引空间数据。与b-树索引一样,这些索引对于以总数的一小部分为目标的查询是最佳的。当边界多边形变大时,可能的匹配数会增加,并且在某个时候,优化器会决定切换到全表扫描更有效。这里似乎就是这样,我看到了三种选择:
首先,尝试添加 LIMIT 你的问题。通常,如果优化器断定在一次完整表扫描中会出现较少的i/o查找,mysql会忽略索引。但是,至少有了b-tree索引,mysql就会短路这个逻辑,并且总是在需要的时候执行b-tree跳转 LIMIT 存在。我假设r树也有类似的短路。
第二,与第一个类似,尝试强制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`)

最后,如果这些都不起作用,那么你需要做的是打破你的边界多边形成更小的多边形。例如,如果您的边界多边形是每边500km的正方形,则将其分解为每边250km的4个正方形,或每边125km的16个正方形,依此类推 UNION 所有这些加在一起。索引将用于每一个,累积结果可能更快(注意,重要的是 UNION 它们结合在一起:mysql不能对一个空间查询应用多个范围扫描。)

相关问题