如何在MySQL中对空间索引进行分区或将其与BTREE索引组合

Question

我在MySQL中有一个表，其中包含我在交互式地图上显示的地理线字符串的各个点。除其他外，该表包含以下字段：

`pos` point NOT NULL,
`zoom` int(10) unsigned NOT NULL

pos包含点的坐标，而zoom是从1到20的数字，需要加载该特定点的地图缩放级别。当地图缩小到很远(低缩放级别(时，需要加载线的大部分，但分辨率较低(需要加载几个点(。放大地图(高缩放级别(时，需要加载一小段线，但分辨率较高。通常，这意味着，与缩放级别较低的点相比，缩放级别较高的表包含更多的点。

当用户移动或缩放地图时，将使用以下查询加载当前地图视图的线点：

SELECT * FROM LinePoints WHERE zoom <= 7 AND MBRContains(LINESTRING(POINT(4.8449,49.8804), POINT(27.0373,54.6038)), pos);

我想优化这个查询，使它即使在有很多行点的情况下也能快速运行。

我已经在pos字段上添加了SPATIAL索引，在zoom字段上添加BTREE索引。这适用于非常高和非常低的缩放级别：

• 在非常高的缩放级别下，边界框很小，因此使用空间索引只选择少量点。然后通过缩放级别快速过滤这少量点
• 在非常低的缩放级别下，此缩放级别仅存在少量点，因此使用BTREE索引。然后通过边界框快速过滤这少量点
• 然而，在中等缩放级别，这取决于索引MySQL选择的数据，但这两种情况都很慢。如果使用BTREE索引，则会为中等缩放级别返回许多点，并且通过边界框过滤这些点需要很长时间。如果它使用空间索引，那么相当大的边界框会返回很多点，并且按缩放级别过滤这些点需要很长时间。在这两种情况下，查询当前数据库中的数据都需要几秒钟的时间，这对于交互式地图来说是不可接受的

如何优化我的数据库？

• 有什么方法可以组合SPATIAL和BTREE索引吗
• 由于缩放级别的数量是固定的，我是否会从对表进行分区中受益？我尝试失败了，因为MySQL似乎不支持对包含空间列的表进行分区。将数据拆分为20个不同的表，我会从中受益吗
• 我是否可以通过切换到不同的数据库系统(例如PostgreSQL(来获得更好的结果

Answer 1

由于您对查询的方式有很强的了解，请从两个查询中选择一个。

SELECT * FROM LinePoints WHERE zoom <= 3 
HAVING  MBRContains(LINESTRING(POINT(4.8449,49.8804), POINT(27.0373,54.6038)), pos);
SELECT * FROM LinePoints
WHERE MBRContains(LINESTRING(POINT(4.8449,49.8804), POINT(27.0373,54.6038)), pos)
HAVING  zoom <= 10;

(如果优化器决定将HAVING折叠到WHERE中，那么可能需要一个子查询。或者可能需要某种IF()函数。(