mysql - 连接空间 mysql 索引

Question

我有两张表:一张是点，另一张是多边形。

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;

我想将范围连接到多边形内点上的点。查询看起来很简单:

SELECT * 
  FROM points 
    LEFT JOIN ranges 
      ON MBRCONTAINS(poly, point) 
  WHERE points.id = 2;

这个查询运行速度很快并且使用索引，解释的一部分:

table  | type  | possible_keys | key  | key_lenranges | range | poly          | poly | 34

但是，当我尝试加入表 points 中的几行时:

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程序):

//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)
          )))
    )');
}

Answer 1

我认为这是因为 MySQL 不支持合并空间索引。不确定它是否仍然是真的，但我过去在某个地方读过它。如果您有 OR 语句，则不使用空间索引

在你的例子中，你在哪里做 points.id = 1，这是一个直接选择，返回一个结果，在 mbrcontains 中使用。那使用索引。

当您添加 points.in (1,2,3) 时，会返回 3 个结果，每个结果都需要映射到范围表，因此不起作用

结果

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 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)))

范围

结果

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

看到在第二种情况下，您没有使用索引而只是扫描。

您可以通过为每个特定点创建 UNION 来强制查询使用索引，但我不确定这是否会更快。我在本地做了一些测试，它比你的第一个查询慢了一点。

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