MySQL地理空间查询非常慢，尽管使用了索引

Question

我需要按距离从InnoDb表中提取记录(不一定准确)，并按距离进行排序。这张表有1000万份记录。

到目前为止，我最好的时间是8秒(3秒，没有按距离排序)，这使得它无法使用。我怎么能改进这个？

我有一个点列定义为SRID 4326。我使用的是MySQL 8.0.12。

SELECT mp.hash_id, 
ROUND(ST_Distance(ST_SRID(POINT(8.53955, 47.37706), 4326), mp.geo_pt), 2) AS distance
  FROM member_profile mp 
  WHERE
    MBRCONTAINS(ST_GeomFromText(
      CONCAT('POLYGON((', ST_X(POINT (8.53955, 47.37706)) - 0.43415340086831, ' ',
        ST_Y(POINT (8.53955, 47.37706)) - 0.43415340086831, ',',
        ST_X(POINT (8.53955, 47.37706)) + 0.43415340086831, ' ',
        ST_Y(POINT (8.53955, 47.37706)) - 0.43415340086831, ',',
        ST_X(POINT (8.53955, 47.37706)) + 0.43415340086831, ' ',
        ST_Y(POINT (8.53955, 47.37706)) + 0.43415340086831, ',',
        ST_X(POINT (8.53955, 47.37706)) - 0.43415340086831, ' ',
        ST_Y(POINT (8.53955, 47.37706)) + 0.43415340086831, ',',
        ST_X(POINT (8.53955, 47.37706)) - 0.43415340086831, ' ',
        ST_Y(POINT (8.53955, 47.37706)) - 0.43415340086831, ')) ')
           , 4326), geo_pt)
-- ST_Distance(ST_GeomFromText('POINT (8.53955 47.37706)', 4326), mp.geo_pt) <= 25000 -- need 16 sec
-- order by distance -- need 8 sec with MBRContains, 100 sec with ST_Distance
LIMIT 50;

建立了一个空间索引：

CREATE SPATIAL INDEX geo_pt_index ON mp (geo_pt);

解释显示我的geo_pt索引被使用了。

my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
innodb_buffer_pool_size = 12G
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
key_buffer_size = 1G
secure-file-priv = ""

此服务器只为此数据库分配，没有负载(除非我执行查询)。没有IOPS瓶颈。innodb_buffer_pool_size大小可容纳内存中的整个数据集。

服务器实例有16 GB内存，使用快速NVMe SSD (没有IOPS瓶颈)。服务器只承载这个数据库，除了Querys没有加载之外。使用了30%的磁盘。

SHOW GLOBAL STATUS输出：https://pastebin.com/EMeNL8yT

SHOW GLOBAL VARIABLES输出：https://pastebin.com/yxzYn10E

MySQL调谐器输出：https://pastebin.com/NRWFQDMQ

我今天更新了从8.0.11到8.0.12，但基本上遵循了所有相关的建议，以前的MySQL调谐器的建议。在速度相同之前，对一些带有空间搜索的固定Bug进行了MySQL更新。

显示警告(查询执行后)：

Level,Code,Message
Note,1003,/* select#1 */ select `***`.`mp`.`member_id` AS `member_id`,round(st_distance(st_pointfromtext('POINT(8.53955 47.37706)',4326),`***`.`mp`.`geo_pt`),2) AS `distance` from `***`.`member_profile` `mp` where mbrcontains(<cache>(st_geomfromtext(concat('POLYGON((',(st_x(point(8.53955,47.37706)) - 0.43415340086831),' ',(st_y(point(8.53955,47.37706)) - 0.43415340086831),',',(st_x(point(8.53955,47.37706)) + 0.43415340086831),' ',(st_y(point(8.53955,47.37706)) - 0.43415340086831),',',(st_x(point(8.53955,47.37706)) + 0.43415340086831),' ',(st_y(point(8.53955,47.37706)) + 0.43415340086831),',',(st_x(point(8.53955,47.37706)) - 0.43415340086831),' ',(st_y(point(8.53955,47.37706)) + 0.43415340086831),',',(st_x(point(8.53955,47.37706)) - 0.43415340086831),' ',(st_y(point(8.53955,47.37706)) - 0.43415340086831),')) '),4326)),`***`.`mp`.`geo_pt`) order by `distance` limit 50

解释：

id,select_type,table,partitions,type,possible_keys,key,
key_len,ref,rows,filtered,Extra
1,SIMPLE,mp,\N,range,geo_pt_index,geo_pt_index,34,\N,23,100.00,Using where; Using filesort

创建表：

CREATE TABLE `member_profile` (
  `member_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `hash_id` varchar(32)
        CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `geo_pt` point NOT NULL /*!80003 SRID 4326 */,
  PRIMARY KEY (`member_id`),
  UNIQUE KEY `hash_id` (`hash_id`),
  SPATIAL KEY `geo_pt_index` (`geo_pt`)
) ENGINE=InnoDB AUTO_INCREMENT=10498210
            DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

显示索引来自：

Table,Non_unique,Key_name,Seq_in_index,Column_name,Collation,
Cardinality,Sub_part,
Packed,Null,Index_type,Comment,Index_comment,Visible

member_profile,0,PRIMARY,1,member_id,A,9936492,\N,\N,,BTREE,,,YES
member_profile,0,hash_id,1,hash_id,A,9936492,\N,\N,YES,BTREE,,,YES
member_profile,1,geo_pt_index,1,geo_pt,A,9936492,32,\N,,SPATIAL,,,YES

Answer 1

“我有一个点列，定义为SRID 4326。我使用的是MySQL 8.0.12。”

我也有类似的问题，将SRID更改为0可以显著提高性能。我不知道副作用是否对你来说是无法忍受的，但至少你应该试试！如果你这样做的话，别忘了另一个命令: lat和lon；

KR皮特

Answer 2

关于my.cnf 米舍尔德部分应考虑的建议

max_connect_errors=10  # from 100, why give a hacker/cracker so many chances?
thread_cache_size=30  # from 9 since MySQL needs 8 to get started
innodb_io_capacity_max=60000  # from 2000  use that NVME for performance
innodb_io_capacity=30000  # from 200 why stick with a low limit with NVME
key_buffer_size=16M  # from 1G conserve RAM for more useful purpose
innodb_buffer_pool_dump_pct=90  # from 25 to reduce WARM up time
innodb_change_buffer_max_size=15  # from 25% for your low chg,del,ins need
innodb_lru_scan_depth=128  # from 1025 to conserve CPU every SECOND
innodb_read_io_threads=64  # from 4 see dba.stackexchange Question 5666
innodb_write_io_threads=64  # from 4 see 9/12/11 RolondaMySQLDBA info

请查阅个人资料，网络配置文件的联系方式，包括Skype的ID，并联系。

Answer 3

MySQL地理信息系统总是比较慢。考虑一下

1. 将此方法应用于建立点
2. 用WKT构建包围框，这样就简化了一些内容。我不知道这会有什么帮助，但它是MySQL！
3. 与其执行ST_Distance() < upperlimit，不如考虑执行ST_Buffer( polygon, upperlimit )，并在调用ST_Contains时使用它
4. 考虑迁移到PostgreSQL/PostGIS，并使用ST_DWithin(geom,geom,upperlimit)。PostGIS具有优越的索引。它实际上可以在索引因为它支持KNN.上完成整个过程。