使用MySQL 8.0进行空间查询

Question

我在MySQL 5.7中有一个数据库，其中我使用"POINT“创建一个Lat/Long列，然后在这个列上创建一个索引，主键作为第二列。所以我的数据库就像

PID, SurveyID, GeoPoint

基本上，这是一个总结表，PID和SurveyID是表的主键。我们对动物进行了调查，并在地图上的给定地理位置对动物进行了汇总记录。

我的索引在GeoPoint and PID栏上。这个表存储大约400 K的记录，这是从主表中总结出来的，主表有2米的记录，以便更快地执行。

现在，我们计划升级到MySQL 8.0，我们发现它不支持空间列是索引和非空间列，所以我们的索引没有被创建。这将导致我们现在的查询所用的时间为8秒，而不是旧版本的MySQL中的0.6秒。

更详细的是，当我只在空间列geopoint、ST_Contains和MBRContains上创建索引时，不要使用该索引。基本上，我们的查询非常简单，因为我们允许用户在Google地图上绘制一个正方形，然后我们使用这些坐标来查找该区域的所有动物。

我不知道如何解决这个问题，因为我没有找到有助于调整查询的文档。

查询：

SELECT  PID, count(distinct SurveyID) as totalsurvey
    FROM  locationsummary
    where  st_contains(ST_Envelope(ST_GeomFromText(
         'LineString(137.109375 21.47351753335, 87.890625 -22.411028521559)')),
              geopoint )
    group by  PID 

表：

CREATE TABLE locationsummary (
 PID bigint(20) NOT NULL,
 SurveyID bigint(20) NOT NULL,
 Sitelat float NOT NULL,
 sitelong float NOT NULL,
 geopoint point NOT NULL,
 PRIMARY KEY (`PID`,`SurveyID`),
 SPATIAL KEY `idx_geopoint` (`geopoint`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=COMPACT

编辑:2019年7月12日--今天，我试着重新运行查询，现在它在2秒内执行，虽然还不是很好，但是从最后一次运行开始，它变得更好了。

Answer 1

SELECT  PID, count(distinct SurveyID) as totalsurvey
    FROM  locationsummary
    where  st_contains(ST_Envelope(ST_GeomFromText(
         'LineString(137.109375 21.47351753335, 87.890625 -22.411028521559)')),
              geopoint )
    group by  PID 

这很慢，因为ST_Contains in MySQL现在没有使用索引，也从来没有使用过索引。为此，您必须使用MbrContains。除了空间数据，你永远不可能把任何东西放在空间索引上。

我看到你在你的问题中说明了MBRContains不使用那个索引。我不相信你。;)

注意，在PostGIS中，ST_Contains将使用索引。此外，您还可以创建一个空间索引，并将非空间数据添加到该索引中(多亏了btree_gist和btree_gin)。