加入空间mysql索引

4 回答

• 6

  我相信这是因为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 range其中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
  

  回复于 2024-05-05T16:59:04+08:00
• 3

  我已成功使用类似的查询，数据模型只有一个区别：点数据库上的空间键 . 就我而言：

  CREATE TABLE geopoints (
    pid int(11) NOT NULL AUTO_INCREMENT,
    description varchar(255) NOT NULL DEFAULT '',
    geopoint point NOT NULL,
    PRIMARY KEY (pid),
    SPATIAL KEY geopoint (geopoint) 
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
  

  在这样的查询中一切顺利：

  SELECT pt.pid, x(geopoint), Y(geopoint), pl.pid, AsText(geopolygon) 
    FROM geopoints pt INNER JOIN geopolygons pl ON MBRCONTAINS(geopolygon, geopoint)
   WHERE pt.pid IN (1,2,4,5) AND pl.pid BETWEEN 1 AND 5;
  

  我的两分钱，

  回复于 2024-05-05T16:59:04+08:00
• 0

  如果您正在处理的只是正方形，我只需处理表格中的4个数字，这些数字可以被编入索引，代表顶部，左侧，高度，宽度，然后运行您的查询，其中您的点在左侧之间具有“X”坐标，顶部，顶部高度之间的左宽度和“Y”坐标 .

  回复于 2024-05-05T16:59:04+08:00
• 0

  您可以通过将subselect封装在函数中来强制MySQL使用索引 .

  例如：

  DELIMITER $$
  
  DROP FUNCTION IF EXISTS `GetMyPolygon`$$
  CREATE DEFINER=`root`@`localhost` FUNCTION `GetMyPolygon`(p POINT) RETURNS INTEGER
  BEGIN
  
  DECLARE ret INTEGER;
  
  SET ret = (SELECT range_id FROM ranges WHERE ST_CONTAINS(poly, p) ;
  
  RETURN ret;
  
  END$$
  

  如果多边形不重叠，则可以：

  SELECT *, GetMyPolygon(point) FROM points
  

  如果它们重叠，但有一些，你可以做一个类似的功能，使group_concat ...

  回复于 2024-05-05T16:59:04+08:00