首先,我承认我在空间功能方面的经验很少。我在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 |
+----+-------------+------------+-------+---------------+----------+---------+------+------+-------------+
看起来最大的多边形没有使用索引。
1条答案
按热度按时间sg3maiej1#
mysql使用r树索引空间数据。与b-树索引一样,这些索引对于以总数的一小部分为目标的查询是最佳的。当边界多边形变大时,可能的匹配数会增加,并且在某个时候,优化器会决定切换到全表扫描更有效。这里似乎就是这样,我看到了三种选择:
首先,尝试添加
LIMIT
你的问题。通常,如果优化器断定在一次完整表扫描中会出现较少的i/o查找,mysql会忽略索引。但是,至少有了b-tree索引,mysql就会短路这个逻辑,并且总是在需要的时候执行b-tree跳转LIMIT
存在。我假设r树也有类似的短路。第二,与第一个类似,尝试强制mysql使用索引。这指示mysql表扫描比优化器决定的开销更大。要明白,优化器只有试探法,并不知道除了内部统计数据得出的结论之外,还有多少“昂贵”的东西。我们人类有直觉,有时-有时-更清楚。
最后,如果这些都不起作用,那么你需要做的是打破你的边界多边形成更小的多边形。例如,如果您的边界多边形是每边500km的正方形,则将其分解为每边250km的4个正方形,或每边125km的16个正方形,依此类推
UNION
所有这些加在一起。索引将用于每一个,累积结果可能更快(注意,重要的是UNION
它们结合在一起:mysql不能对一个空间查询应用多个范围扫描。)