mysql - MySql 8.0.11 空间查询慢了 100000 倍

问题描述

我们已将 MySql 5.7 数据库的副本迁移到 Amazon RDS 上的 MySql 8.0.11。在可能的情况下，一切都是相同的。包含几何数据的表已被修改，因此几何列被限制为 SRID 0 并重建空间索引。两个数据库上的执行计划相同，表明查询正在使用空间索引。

在 MySql 5.7 上，以下查询需要 0.001 秒，在 MySql 8 上需要 108 秒。在 MySql 8 上查看执行统计信息“发送数据”占 100% 的时间。为什么？

SELECT r.roadid                             
FROM geocoder.osm_road r
WHERE mbrintersects(ST_Buffer(ST_GEOMETRYFROMTEXT('Point(-1.91289 52.58260)',0),0.0005), r.geometry)

我们有许多不同的空间查询，并且都表现得像这样，但特别是 MBRINTERSECTS 和 ST_INTERSECTS 似乎非常慢。更改为 ST_CONTAINS（在可能的情况下）会带来显着的改进（即上面需要 3 秒而不是 108 秒），但这不适用于许多查询，并且仍然比 5.7 慢很多。

Show Create Table for 5.7

CREATE TABLE `osm_road` (
  `roadid` bigint(20) NOT NULL,
  `reference` varchar(20) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `dictionary` longtext,
  `road_speed_limit` tinyint(4) unsigned NOT NULL,
  `road_speed_unitid` tinyint(1) NOT NULL,
  `road_type` tinyint(4) NOT NULL,
  `is_toll_road` bit(1) NOT NULL DEFAULT b'0',
  `is_one_way` bit(1) NOT NULL DEFAULT b'0',
  `countryid` smallint(6) DEFAULT NULL,
  `geometry` geometry NOT NULL,
  `datemodified` datetime DEFAULT CURRENT_TIMESTAMP,
  `datecreated` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`roadid`),
  UNIQUE KEY `id_roadid` (`roadid`),
  SPATIAL KEY `ix_road_geometry` (`geometry`),
  KEY `ix_road_reference` (`reference`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AVG_ROW_LENGTH=207


Show Create Table for 8.0.11

CREATE TABLE `osm_road` (
  `roadid` bigint(20) NOT NULL,
  `reference` varchar(20) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `dictionary` longtext,
  `road_speed_limit` tinyint(4) unsigned NOT NULL,
  `road_speed_unitid` tinyint(1) NOT NULL,
  `road_type` tinyint(4) NOT NULL,
  `is_toll_road` bit(1) NOT NULL DEFAULT b'0',
  `is_one_way` bit(1) NOT NULL DEFAULT b'0',
  `countryid` smallint(6) DEFAULT NULL,
  `geometry` geometry NOT NULL /*!80003 SRID 0 */,
  `datemodified` datetime DEFAULT CURRENT_TIMESTAMP,
  `datecreated` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`roadid`),
  UNIQUE KEY `id_roadid` (`roadid`),
  KEY `ix_road_reference` (`reference`),
  SPATIAL KEY `ix_road_geometry` (`geometry`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AVG_ROW_LENGTH=207

显示来自 osm_road 的索引 (5.7)

Table,Non_unique,Key_name,Seq_in_index,Column_name,Collation,Cardinality,Sub_part,Packed,Null,Index_type,Comment,Index_comment
osm_road,0,PRIMARY,1,roadid,A,18973144,NULL,NULL,,BTREE,,
osm_road,1,ix_road_geometry,1,geometry,A,18973154,32,NULL,,SPATIAL,,
osm_road,1,ix_road_reference,1,reference,A,199900,NULL,NULL,YES,BTREE,,

显示来自 osm_road (8.0.11) 的索引

Table,Non_unique,Key_name,Seq_in_index,Column_name,Collation,Cardinality,Sub_part,Packed,Null,Index_type,Comment,Index_comment,Visible
osm_road,0,PRIMARY,1,roadid,A,16194921,NULL,NULL,,BTREE,,,YES
osm_road,1,ix_road_reference,1,reference,A,86215,NULL,NULL,YES,BTREE,,,YES
osm_road,1,ix_road_geometry,1,geometry,A,16194921,32,NULL,,SPATIAL,,,YES

显示全局状态 (8.0.11)点击这里

显示全局变量 (8.0.11)点击这里

-- 添加于 2019-01-27 -----------------

EXPLAIN SELECT r.roadid FROM geocoder.osm_road r WHERE mbrintersects(ST_Buffer(ST_GEOMETRYFROMTEXT('Point(-1.91289 52.58260)',0),0.0005), r.geometry);

回报：

select_type:   Simple
table:         r
partitions:    null
possible_keys: ix_road_geometry
key:           ix_road_geometry
key_len:       34
ref:           null
rows:          1
filtered:      100
Extra:         Using where

标签： mysqlperformancespatial

mysql - MySql 8.0.11 空间查询慢了 100000 倍

问题描述

解决方案

推荐阅读