mysql8 经纬度匹配效率问题

12次阅读

共计 3861 个字符，预计需要花费 10 分钟才能阅读完成。

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

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()

正文完