mysql8 经纬度匹配效率问题

[wozhidaole]

目前有一个需求是这样子的 计算一个网格里面有几个点。 总共是两张表

CREATE TABLE `city` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `poi` geometry NOT NULL,
  PRIMARY KEY (`id`),
  SPATIAL KEY `city_poi_IDX` (`poi`)
) ENGINE=InnoDB AUTO_INCREMENT=10100024 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


CREATE TABLE `diy` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `poi` geometry DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INTO diy (poi) VALUES (ST_GeomFromText('POLYGON((121.578 29.9177, 121.527 29.9508, 121.47 29.9763, 121.415 29.9984, 121.407 29.9978, 121.403 29.9976, 121.401 29.993, 121.402 29.9905, 121.401 29.9898, 121.399 29.9898, 121.397 29.9925, 121.393 29.9943, 121.389 29.9918, 121.387 29.9845, 121.385 29.973, 121.379 29.9675, 121.359 29.9633, 121.371 29.9589, 121.377 29.9569, 121.383 29.9562, 121.391 29.9583, 121.402 29.9521, 121.387 29.9279, 121.383 29.9136, 121.375 29.9066, 121.37 29.9039, 121.361 29.906, 121.35 29.9098, 121.356 29.9138, 121.355 29.9173, 121.344 29.9123, 121.338 29.9041, 121.326 29.9105, 121.33 29.9157, 121.327 29.9188, 121.326 29.9231, 121.326 29.9285, 121.323 29.9287, 121.301 29.9088, 121.282 29.9104, 121.258 29.891, 121.255 29.8844, 121.248 29.8844, 121.244 29.881, 121.248 29.872, 121.24 29.8611, 121.237 29.8493, 121.203 29.8434, 121.194 29.8211, 121.181 29.8167, 121.18 29.7989, 121.182 29.7879, 121.168 29.7863, 121.17 29.7927, 121.167 29.7946, 121.162 29.7935, 121.154 29.7867, 121.158 29.7631, 121.156 29.7545, 121.157 29.75, 121.172 29.7463, 121.18 29.7431, 121.181 29.7362, 121.192 29.7335, 121.208 29.7343, 121.222 29.7306, 121.233 29.7333, 121.238 29.7389, 121.247 29.7381, 121.249 29.7391, 121.253 29.7319, 121.256 29.7364, 121.258 29.7396, 121.26 29.7427, 121.266 29.7426, 121.265 29.7351, 121.27 29.7352, 121.29 29.7355, 121.333 29.7375, 121.373 29.7455, 121.38 29.7352, 121.384 29.7353, 121.386 29.7383, 121.388 29.7388, 121.389 29.7367, 121.392 29.7371, 121.393 29.7394, 121.391 29.7439, 121.394 29.745, 121.398 29.7443, 121.404 29.7439, 121.405 29.7454, 121.402 29.7475, 121.404 29.7517, 121.415 29.7476, 121.416 29.7432, 121.424 29.7457, 121.427 29.7467, 121.43 29.7479, 121.432 29.7499, 121.432 29.7545, 121.43 29.759, 121.432 29.761, 121.434 29.762, 121.439 29.7623, 121.437 29.7662, 121.437 29.7709, 121.441 29.7776, 121.456 29.7768, 121.466 29.7665, 121.478 29.7597, 121.477 29.7489, 121.474 29.7339, 121.48 29.7171, 121.485 29.7131, 121.489 29.7083, 121.499 29.7079, 121.502 29.7064, 121.505 29.7108, 121.511 29.7098, 121.511 29.7162, 121.518 29.7132, 121.527 29.7088, 121.532 29.7107, 121.543 29.7051, 121.543 29.7003, 121.551 29.6971, 121.561 29.6964, 121.572 29.7048, 121.584 29.6952, 121.577 29.6833, 121.582 29.6758, 121.569 29.6752, 121.568 29.6503, 121.571 29.6354, 121.586 29.6384, 121.629 29.64, 121.646 29.6342, 121.657 29.6261, 121.725 29.6396, 121.756 29.6446, 121.781 29.623, 121.789 29.6115, 121.819 29.6396, 121.859 29.67, 121.937 29.6996, 122.015 29.7709, 122.189 29.9079, 122.068 29.9079, 121.952 29.8999, 121.936 29.9299, 121.836 29.956, 121.806 29.971, 121.769 29.981, 121.73 29.9537, 121.708 29.9513, 121.692 29.9292, 121.669 29.9218, 121.656 29.9084, 121.649 29.9006, 121.642 29.9069, 121.638 29.9142, 121.62 29.9097, 121.6 29.9112, 121.578 29.9177, 121.578 29.9177, 121.578 29.9177))'))

统计语句

select
	count(1)
from
	diy
left join city on
	St_contains(diy.poi , city.poi) = 1

目前 city 表的数据是 100 多万 在我的 mac book 里面执行大概要 50s 请问 v 友们有什么优化策略。

city 造数的 python 脚本

import random
import mysql.connector

# 配置数据库连接信息
config = {
  'user': 'root',
  'password': '12345678',
  'host': 'localhost',
  'database': 'test',
  'port': '3306',  # 默认 MySQL 端口
  'raise_on_warnings': True,
}

def generate_random_coordinates():
    latitude = random.uniform(29.86, 53.55)
    longitude = random.uniform(90.66, 123.05)
    return latitude, longitude

try:
    # 连接到数据库
    conn = mysql.connector.connect(**config)
    # 创建游标对象，用于执行 SQL 语句
    cursor = conn.cursor()
    # 插入一些示例数据
    # 测试函数
    for i in range(10000000):
        latitude, longitude = generate_random_coordinates()
        print("随机生成的经纬度：")
        print("纬度:", latitude)
        print("经度:", longitude)
        cursor.execute("INSERT INTO city (poi) VALUES (ST_GeomFromText('POINT(%s %s)'))", (  longitude, latitude))
    # 提交事务
    conn.commit()
    # 查询数据
except mysql.connector.Error as err:
    print(f"Error: {err}")

finally:
    # 关闭游标和连接
    if 'cursor' in locals() and cursor:
        cursor.close()
    if 'conn' in locals() and conn:
        conn.close()

[StinkyTofus]

用 Geohash 算法， 把二维坐标转换成一维字符， 根据前缀去匹配， 即方便检索又方便控制进度。 如果需要更高性能，Redis 也直接支持 Geohash

[StinkyTofus]

几十万条算个啥，我有个两亿多条 ip 位置的 mysql 表，geohash 字段仅做了索引， 定位匹配查询一次也是毫秒级的。

[wozhidaole]

@StinkyTofus 大佬 能否根据我的案例 简单描述下 怎么使用 Geohash 算法吗?

[StinkyTofus]

@wozhidaole #3 搜索一下 geohash ， 把经纬度转换成字符串， 存到 mysql 里面就行了， 搜索的时候也是同理， 转换成 hash ， 然后用 rlike 查询

[xiangyuecn]

盲猜，mysql 的空间索引没有生效

我的做法是自己建个“索引”：你 diy 表里面加一个 geometry 类型的字段 poi_envelope 来自建索引: update xxx set poi_envelope=ST_Envelope(poi)，查询的时候先查 poi_envelope ，然后再查 poi 字段

St_contains(diy.poi_envelope , city.poi) = 1 and St_contains(diy.poi, city.poi) = 1

先查 4 个坐标点的矩形，快速过滤掉不在范围内的数据，再来精准匹配，效率估计能提升 10 倍以上

[xiangyuecn]

@xiangyuecn #5 参考自我的 AreaCity Geo 格式转换工具，https://xiangyuecn.github.io/AreaCity-JsSpider-StatsGov/assets/AreaCity-Geo-Transform-Tools.html ，可以将省市区三级坐标边界数据导出到数据库、geojson 、shp ，里面有专门针对 mysql 的空间数据查询优化介绍

另外硬推一下我写的另外一个 Java 查询工具，1 秒可查 1 万个以上坐标对应的城市信息 ：
https://github.com/xiangyuecn/AreaCity-Query-Geometry

[wozhidaole]

@StinkyTofus 我不是点对点是否相等 是要确认这个点是不是在这个范围内诶

[wozhidaole]

@xiangyuecn 这个我明天研究一下

[foxthree]

https://www.bilibili.com/video/BV1Zg4y1179b/?spm_id_from=333.999.0.0

昨天刚看到 geohash ，可以看下这个会不会用的上

[wozhidaole]

@xiangyuecn 确实有所提升,不过生产的数据大 效果不是特别显著。

[xiangyuecn]

@wozhidaole #10 mysql 的空间查询是很弱，这种边界范围内的查询是会很慢，geohash 之类的也很难利用到这上面