使用大型3D点数据集提高SELECT查询的性能

问题描述：

我有一个很大的数据集(大约190万行)，可以从中选择.我最常使用的语句类似于:

I have a large dataset (around 1.9 million rows) of 3D points that I'm selecting from. The statement I use most often is similar to:

SELECT * FROM points 
WHERE x > 100 AND x < 200 
AND   y > 100 AND y < 200 
AND   z > 100 AND z < 200 
AND otherParameter > 10

我在x，y和z以及otherParameter上都有标记.我还尝试过将多部分索引添加到x，y，z，但这没有帮助.

I have indicies on x, y, and z as well as the otherParameter. I've also tried adding a multi-part index to x,y,z but that hasn't helped.

关于如何使此SELECT查询更快的任何建议?

Any advice on how to make this SELECT query quicker?

答

B-Tree索引对于此类查询没有多大帮助.

B-Tree indexes won't help much for such a query.

作为R-Tree索引所需的内容，以及对其进行最小限度的平行六面体查询.

What you need as an R-Tree index and the minimal bounding parallelepiped query over it.

不幸的是，MySQL不支持超过3d点的R-Tree索引，仅支持2d.但是，您可能会一起在X和Y上创建索引，这将比单独在X和Y上的任何B-Tree索引更具选择性:

Unfortunately, MySQL does not support R-Tree indexes over 3d points, only 2d. However, you may create an index over, say, X and Y together which will be more selective that any of the B-Tree indexes on X and Y alone:

ALTER TABLE points ADD xy POINT;

UPDATE  points
SET     xy = Point(x, y);

ALTER TABLE points MODIFY xy POINT NOT NULL;


CREATE SPATIAL INDEX sx_points_xy ON points (xy);

SELECT  *
FROM    points
WHERE   MBRContains(LineString(Point(100, 100), Point(200, 200), xy)
        AND z BETWEEN 100 and 200
        AND otherParameter > 10;

这仅在您的表为MyISAM时可用.

This is only possible if your table is MyISAM.

使用大型3D点数据集提高SELECT查询的性能

相关推荐