MySQL 8 - MBRContains未使用空间索引

Question

MySQL 8 - MBRContains未使用空间索引

4

我在Ubuntu和Windows 10上使用MySQL 8.0.29，但无法理解为什么这个查询运行如此缓慢（大约15秒），并且没有利用到空间索引。我几乎完全按照MySQL文档中描述的方法使用MBRContains函数：11.4.11 使用空间索引。表location_coordinate共有735k行POINT类型的纬度/经度坐标，查询其是否包含在一个POLYGON（盒子）内。

SELECT long_lat_id, ST_Latitude(long_lat), ST_Longitude(long_lat)
FROM location_coordinate
WHERE MBRContains(ST_GeomFromText(
    'POLYGON((
        40.79607446677 -73.919978196147, 
        40.70923553323 -73.919978196147, 
        40.70923553323 -74.034611803853, 
        40.79607446677 -74.034611803853, 
        40.79607446677 -73.919978196147))', 4326)
    , long_lat);

解释查询操作

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: location_coordinate
   partitions: NULL
         type: ALL
possible_keys: long_lat
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 735118
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

表定义和解释

CREATE TABLE location_coordinate (
    long_lat_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
    long_lat POINT NOT NULL SRID 4326,  
    PRIMARY KEY (long_lat_id),
    SPATIAL INDEX (long_lat)
);


mysql> EXPLAIN location_coordinate;
+--------------+--------------------+------+-----+---------+----------------+
| Field        | Type               | Null | Key | Default | Extra          |
+--------------+--------------------+------+-----+---------+----------------+
| long_lat_id  | mediumint unsigned | NO   | PRI | NULL    | auto_increment |
| long_lat     | point              | NO   | MUL | NULL    |                |
+--------------+--------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

- Dickie

1

在 bugs.mysql.com 上提交一个错误报告，并在此处放置一个链接。 - Rick James

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Dickie · Accepted Answer

根据Rick James的评论，我打算提交一个错误报告，但事实证明这是MySQL 8.0.29中已经确认的错误。

参考https://bugs.mysql.com/bug.php?id=107320

Documented fix as follows in the MySQL 8.0.31 changelog:

Upgrading to MySQL 8.0.29 led to issues with existing spatial
indexes. The root cause of the problem was a change in how
geographic area computations were performed by the included
Boost library, which was upgraded to version 1.77.0 in MySQL
8.0.29. We fix this by ensuring that we accommodate the new
method whenever such computations are performed.

Closed.

一个临时解决方案是在查询中使用FORCE INDEX()函数。这将把查询时间从15秒缩短到0.062秒。

SELECT long_lat_id, ST_Latitude(long_lat), ST_Longitude(long_lat)
FROM location_coordinate FORCE INDEX(long_lat)
WHERE MBRContains(ST_GeomFromText(
    'POLYGON((
        40.79607446677 -73.919978196147, 
        40.70923553323 -73.919978196147, 
        40.70923553323 -74.034611803853, 
        40.79607446677 -74.034611803853, 
        40.79607446677 -73.919978196147))', 4326)
    , long_lat);

2022年10月3日更新：此漏洞已在MySQL 8.0.30版本中修复。