MariaDB下基于ST_WITHIN的跨库多表关联查询执行过慢如何优化

• 空间索引缺失：ST_WITHIN属于空间计算函数，未建立空间索引时，每次关联都会触发全表逐行匹配，双表关联时15万行600行的计算量尚可运行，新增第三张表后计算量攀升至15万600*70的量级，远超数据库实时计算能力，就会出现无响应卡住的情况。
• JOIN逻辑触发了笛卡尔积：你当前用RIGHT JOIN + LEFT JOIN的写法，MariaDB空间关联优化能力较弱，会先计算多表笛卡尔积再做空间过滤，进一步放大了计算量。
• 实时生成坐标对象无优化：每次关联都现场基于经纬度生成Point对象，没有预存和索引优化，额外增加了单次计算的耗时。

方案1：数据库侧优化（推荐，改动最小） #

1. 先为空间字段建立空间索引（核心优化点）

-- 给table2的几何字段建空间索引
CREATE SPATIAL INDEX idx_table2_geo ON database_2.table_2(geometry);
-- 给table3的几何字段建空间索引
CREATE SPATIAL INDEX idx_table3_geo ON database_2.table_3(geometry);
-- 给table1预存坐标点并建索引（可选，性能提升更明显）
ALTER TABLE database_1.table_1 ADD COLUMN coord_point POINT NOT NULL SRID 4326;
UPDATE database_1.table_1 SET coord_point = ST_SRID(Point(Latitude, Longitude), 4326);
CREATE SPATIAL INDEX idx_table1_coord ON database_1.table_1(coord_point);

注意：SRID取值需要和table2、table3中geometry字段的SRID保持一致，否则索引无法生效

2. 改写查询语句，改用子查询逐行匹配，避免多表JOIN的计算爆炸，同时直接从table1出发查询，无需写RIGHT JOIN：

SELECT
  `t1`.`Code`,
  `t1`.`price`,
  `t1`.`Latitude`,
  `t1`.`Longitude`,
  (SELECT `t2`.`tier_name` FROM `database_2`.`table_2` t2 WHERE ST_WITHIN(t1.coord_point, t2.geometry) LIMIT 1) AS tier_name,
  (SELECT `t2`.`tier2_name` FROM `database_2`.`table_2` t2 WHERE ST_WITHIN(t1.coord_point, t2.geometry) LIMIT 1) AS tier2_name,
  (SELECT `t3`.`id` FROM `database_2`.`table_3` t3 WHERE ST_WITHIN(t1.coord_point, t3.geometry) LIMIT 1) AS id
FROM `database_1`.`table_1` t1
WHERE t1.`Code` != "TEST"

如果不想修改table1结构加coord_point字段，也可以把查询里的t1.coord_point替换为ST_SRID(Point(t1.Latitude, t1.Longitude), 4326)，性能会略低于预存坐标的方案。
加LIMIT 1是为了避免单个坐标点落在多个多边形内时返回重复行，保证最终结果行数和table1的行数一致。

方案2：Python侧计算（无需修改数据库结构，适合无数据库修改权限的场景） #

用geopandas做空间匹配，性能远高于未建索引的数据库关联：

import pandas as pd
import geopandas as gpd

# 分别拉取三张表数据
t1 = pd.read_sql("SELECT Code, price, Latitude, Longitude FROM database_1.table_1 WHERE Code != 'TEST'", cnx)
t2 = gpd.read_postgis("SELECT tier_name, tier2_name, geometry FROM database_2.table_2", cnx, crs="EPSG:4326")
t3 = gpd.read_postgis("SELECT id, geometry FROM database_2.table_3", cnx, crs="EPSG:4326")

# 将t1转为空间DataFrame，坐标参考系和另外两张表保持一致
t1_gdf = gpd.GeoDataFrame(
    t1,
    geometry=gpd.points_from_xy(t1.Longitude, t1.Latitude),
    crs="EPSG:4326"
)

# 两次左连接完成匹配
t1_with_t2 = gpd.sjoin(t1_gdf, t2, how="left", predicate="within").drop(columns=["index_right"])
final_result = gpd.sjoin(t1_with_t2, t3, how="left", predicate="within").drop(columns=["index_right", "geometry"])

这个方案即使不建索引，也能在几秒内完成15万点和七百多面的匹配。

内容的提问来源于stack exchange，提问作者tareenmj