加入空间mysql索引 [英] Joins on spatial mysql indexes
问题描述
我有两个表：一个有点，另一个有多边。
I have two tables: one with points, the other with polys.
CREATE TABLE `points` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`point` point NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;
CREATE TABLE `ranges` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`poly` polygon NOT NULL,
PRIMARY KEY (`id`),
SPATIAL KEY `poly` (`poly`)
) ENGINE=MyISAM;
我想将范围加到多边形内的点上。查询看起来很简单：
I want to join ranges to points on points inside polys. Queries look simple:
SELECT *
FROM points
LEFT JOIN ranges
ON MBRCONTAINS(poly, point)
WHERE points.id = 2;
此查询工作正常并使用索引，解释的一部分：
This query works fast and uses indexes, part of explain:
table | type | possible_keys | key | key_len
ranges | range | poly | poly | 34
但是，当我尝试从表中加入几行时：
SELECT *
FROM points
LEFT JOIN ranges
ON MBRCONTAINS(poly, point)
WHERE points.id IN (1,2,3);
一切都发生故障：
+----+-------------+------------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+------+--------+-------------+
| 1 | SIMPLE | points | range | PRIMARY | PRIMARY | 4 | NULL | 3 | Using where |
| 1 | SIMPLE | ranges | ALL | poly | NULL | NULL | NULL | 155183 | |
+----+-------------+------------+-------+---------------+---------+---------+------+--------+-------------+
添加 FORCE INDEX（poly）没有帮助。
测试查询的示例数据（对不起，只有php版本，我不熟悉SQL程序）：
Sample data to test queries (sorry, only php version, I'm not common with SQL procedures):
//points
for($i=0;$i<=500;$i++) {
$point = mt_rand();
mysql_query('INSERT INTO points (point) VALUES (POINTFROMWKB(POINT('.$point.', 0)))');
}
$qty = 20000;
$max = mt_getrandmax();
$add = $max / $qty
$end = 0;
//polys
while($end < $max) {
$start = $end;
$end = mt_rand($start, $start + $add);
mysql_query('INSERT INTO ranges (poly) VALUES (
GEOMFROMWKB(POLYGON(LINESTRING(
POINT('.$start.', -1),
POINT('.$end.', -1),
POINT('.$end.', 1),
POINT('.$start.', 1),
POINT('.$start.', -1)
)))
)');
}
推荐答案
我相信这是因为MySQL不支持合并空间索引。不确定它是否仍然是真的但我过去曾在某处读过它。如果你有一个OR语句，那么就不会使用空间索引
I believe that it's because MySQL doesn't support merging spatial indexes. Not sure if it's still true but I've read it somewhere in the past. If you have an OR statement, then the spatial indexes are not used
在你的情况下，你在哪里做points.id = 1，这是一个直接选择，只有一个结果返回，在mbrcontains中使用。那使用索引。
In your case, where are you doing points.id = 1, that's a straight select with one result returned that gets used in the mbrcontains. That uses the index.
当你添加points.in（1,2,3）时，返回3个结果，每个都需要映射到范围表，因此无法正常工作
When you add points.in (1,2,3), that returns 3 results and each needs to be mapped to the ranges table, therefore not working
结果
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE points range PRIMARY PRIMARY 4 NULL 3 100.00 Using where
1 SIMPLE ranges ALL poly NULL NULL NULL 6467418 100.00
您可以在没有点表的情况下简化测试：SELECT * FROM range其中mbrcontains（poly，GEOMFROMWKB（POINT（0,0）））
You can simplify your test without the the point table by doing this: SELECT * FROM ranges where mbrcontains( poly, GEOMFROMWKB(POINT(0, 0)))
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE ranges range poly poly 34 NULL 1 100.00 Using where
现在这个; SELECT * FROM范围，其中mbrcontains（poly，GEOMFROMWKB（POINT（0,0）））或mbrcontains（poly，GEOMFROMWKB（POINT（10,10）））
And now this; SELECT * FROM ranges where mbrcontains( poly, GEOMFROMWKB(POINT(0, 0))) OR mbrcontains( poly, GEOMFROMWKB(POINT(10, 10)))
结果
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE ranges ALL poly NULL NULL NULL 6467418 100.00 Using where
在第二种情况下，您没有使用索引而只是扫描。
See that in the second case, you are not using index and just scanning.
您可以通过为每个特定点创建UNION来强制查询使用索引，但我不确定这是否会更快。我在本地进行了一些测试，它比你的第一个查询慢一点。
You could force the query to use index by creating UNION for each specific point but I am not sure if that's going to be faster. I did some tests locally and it was a bit slower than your first query.
EXPLAIN EXTENDED
SELECT *
FROM points
FORCE INDEX (PRIMARY )
LEFT JOIN ranges
FORCE INDEX ( poly ) ON mbrcontains( poly, point )
WHERE points.id = 1
UNION DISTINCT
SELECT *
FROM points
FORCE INDEX (PRIMARY )
LEFT JOIN ranges
FORCE INDEX ( poly ) ON mbrcontains( poly, point )
WHERE points.id = 2
UNION DISTINCT
SELECT *
FROM points
FORCE INDEX (PRIMARY )
LEFT JOIN ranges
FORCE INDEX ( poly ) ON mbrcontains( poly, point )
WHERE points.id = 3
结果
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY points const PRIMARY PRIMARY 4 const 1 100.00
1 PRIMARY ranges range poly poly 34 NULL 1 100.00 Using where
2 UNION points const PRIMARY PRIMARY 4 const 1 100.00
2 UNION ranges range poly poly 34 NULL 1 100.00 Using where
3 UNION points const PRIMARY PRIMARY 4 const 1 100.00
3 UNION ranges range poly poly 34 NULL 1 100.00 Using where
NULL UNION RESULT <union1,2,3> ALL NULL NULL NULL NULL NULL NULL
这篇关于加入空间mysql索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
