为何我的空间索引查询速度缓慢？附两张表结构信息

Hey there, let's break down why your spatial index queries are dragging their feet with those two tables. I’ve tackled similar issues before, so here are the key things to check step by step:

First: Verify if the Spatial Index is Actually Being Used #

A common pitfall is that MySQL’s optimizer might skip the spatial index and do a full table scan instead. To confirm this, run your query with EXPLAIN:

EXPLAIN SELECT * FROM device_locations 
WHERE ST_Distance_Sphere(location, ST_GeomFromText('POINT(your_lon your_lat)')) < 1000;

If the type column shows ALL instead of range or ref, the index isn’t being used. You can try forcing the index to see if that helps:

SELECT * FROM device_locations FORCE INDEX (spatial_index_2)
WHERE ST_Distance_Sphere(location, ST_GeomFromText('POINT(your_lon your_lat)')) < 1000;

1. You’re Using the Wrong Spatial Function (Or Not Filtering First) #

ST_Distance_Sphere is great for accurate distance calculations, but InnoDB can’t use spatial indexes directly with it. For your 1M+ row device_locations table, this means MySQL has to calculate the distance for every single row before filtering—slow work!

Fix this by adding a bounding box filter first to narrow down the dataset with the spatial index, then calculate precise distances only on the smaller subset:

-- Set your target point and radius (in meters)
SET @target = ST_GeomFromText('POINT(116.397428 39.90923)', 4326);
SET @radius = 1000;
-- Convert radius to degrees (1 degree ≈ 111319.9 meters for WGS84)
SET @bounding_box = ST_Buffer(@target, @radius / 111319.9);

SELECT * 
FROM device_locations
WHERE ST_Contains(@bounding_box, location)
  AND ST_Distance_Sphere(location, @target) < @radius;

ST_Contains will use the spatial index to quickly grab only points inside the bounding box, making the distance calculation fast.

2. Mixed Spatial Reference IDs (SRIDs) Are Breaking the Index #

If your location values use different SRIDs (coordinate systems), MySQL can’t properly use the spatial index. Check if all your data uses the same SRID:

-- Check a sample of rows from each table
SELECT ST_SRID(location) FROM city_landmark LIMIT 10;
SELECT ST_SRID(location) FROM device_locations LIMIT 10;

If results are inconsistent (e.g., some are 0, some are 4326), standardize them to a common SRID like 4326 (WGS84, the global GPS standard):

UPDATE device_locations SET location = ST_SetSRID(location, 4326);
UPDATE city_landmark SET location = ST_SetSRID(location, 4326);

3. Index Fragmentation is Killing Performance #

With 1M+ rows in device_locations, your spatial index might have become fragmented over time (from updates/deletes). Rebuilding the index can fix this:

-- Drop and recreate the spatial index for device_locations
ALTER TABLE device_locations DROP INDEX spatial_index_2;
ALTER TABLE device_locations ADD SPATIAL KEY spatial_index_2 (location);

-- Do the same for the smaller table if needed
ALTER TABLE city_landmark DROP INDEX spatial_index1;
ALTER TABLE city_landmark ADD SPATIAL KEY spatial_index1 (location);

4. Your Query Range is Too Large #

If your query returns a huge chunk of the table (e.g., 20%+ of all rows), MySQL will decide a full table scan is faster than using the index. Check how many rows your filter is returning:

SELECT COUNT(*) FROM device_locations WHERE ST_Contains(@bounding_box, location);

If the count is massive, try narrowing your radius or adding additional filters (like time ranges if you have that data) to reduce the result set size.

5. Outdated MySQL Version is Holding You Back #

Older MySQL versions (pre-8.0) have limited spatial index optimizations for InnoDB. Version 8.0 introduced better R-tree index handling, improved support for spatial functions with indexes, and overall faster spatial performance. If you’re on 5.7 or earlier, upgrading could make a huge difference.

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