sql - 如何在 MySQL 表中使用外键和空间索引？

Question

我们的项目在 MySQL 数据库的 INNODB 表中以树结构保存世界数据库。地球是根，然后是国家，然后是国家地区，地点是叶子。

外键用于为 child 提供快速访问(例如某个地区的城市)。

现在我们要在数据库中针对给定坐标实现快速地理搜索。一个明显的解决方案是使用 SPATIAL INDEX，这是 MyISAM 表的一个特性。但是 MyISAM 表不支持外键。并且 INNODB 表不支持 SPATIAL INDEX。

所以如果我们使用 MyISAM 表，我们必须放弃外键，这会使 child 搜索的时间太长。

我们如何结合树中的快速子搜索和表中的 SPATIAL INDEX？

Answer 1

How can we combine fast children search in tree and also have a SPATIAL INDEX in a table?

手动在表的 id 和 parentId 上创建索引:

CREATE INDEX ix_mytable_parentid ON mytable (parentid)

请注意，由于id 很可能是PRIMARY KEY，因此它 不需要显式索引(将隐式创建)。

顺便说一句，如果您拥有自然的基于地理的层次结构，那么使用父子关系进行搜索有什么意义？

您可以使用 SPATIAL 索引进行查询:

SELECT  *
FROM    mytable m1
JOIN    mytable m2
ON      MBRContains (m2.area, m1.area)
        AND m2.parentId = m1.id
WHERE   m1.name = 'London'

它将使用空间索引进行搜索，使用关系进行精细过滤。