优化带ORDER BY的MySQL查询性能问题求助

针对你的MySQL查询性能问题，核心原因是优化器在ORDER BY和空间筛选的索引选择上做出了低效决策，以下是具体优化手段：

1. 强制使用空间索引 #

MySQL优化器优先选择了date_date索引，但空间索引能快速过滤出符合地理范围的小数据集，后续排序成本会大幅降低。在查询中指定强制使用空间索引：

SET lc_time_names = 'fr_FR';
SET @j = '{"type":"Feature","properties":{},"geometry":{"type":"Polygon","coordinates":[[[1.31321,48.024146],[1.63456,48.024146],[1.63456,48.150283],[1.31321,48.150283],[1.31321,48.024146]]]}}';
SET @zone = ST_GeomFromGeoJson(@j);
-- 替换为你的实际空间索引名称
create temporary table A as 
select id, field1, field2, ... 
from mybase FORCE INDEX(你的空间索引名)
WHERE val > 0 
  and ST_CONTAINS(@zone, pt) 
  and date_date BETWEEN '2020-01-01' AND '2022-12-31'
order by date_date DESC 
limit 200;
select * from A;

2. 优化日期条件，避免函数调用 #

原查询中year(date_date) IN ('2022','2021','2020')会导致date_date的索引失效，改用范围查询让索引能正常发挥作用：

date_date BETWEEN '2020-01-01' AND '2022-12-31'

这样优化器能同时结合空间筛选和日期范围的过滤逻辑，进一步缩小数据集。

3. 拆分查询逻辑（备选方案） #

如果强制索引效果不佳，可以先筛选出符合地理和条件的所有数据，再进行排序取前200：

SET lc_time_names = 'fr_FR';
SET @j = '{"type":"Feature","properties":{},"geometry":{"type":"Polygon","coordinates":[[[1.31321,48.024146],[1.63456,48.024146],[1.63456,48.150283],[1.31321,48.150283],[1.31321,48.024146]]]}}';
SET @zone = ST_GeomFromGeoJson(@j);
-- 先筛选数据，不排序
create temporary table A as 
select id, field1, field2, ... 
from mybase
WHERE val > 0 
  and ST_CONTAINS(@zone, pt) 
  and date_date BETWEEN '2020-01-01' AND '2022-12-31';
-- 从临时表排序取前200
select * from A 
order by date_date DESC 
limit 200;

此方案适合空间筛选后数据集较小的场景，临时表的排序成本远低于全表基于日期索引的扫描。

验证优化效果 #

执行EXPLAIN查看优化后的执行计划，确认是否使用了空间索引（type列显示range或ref，key列显示空间索引名），同时检查rows列预估的扫描行数是否大幅减少。

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