如何加速含经纬度矩形范围查询的SQL语句执行效率？

原查询与执行信息 #

原SQL语句 #

SELECT * 
FROM `pet` 
WHERE (`unite_id` IS NULL) 
AND ((`status`=1) OR ((`status`=4) AND (`delete_reason` IN (1, 2, 5))))
AND ((`pet`.`latitude` BETWEEN '55.750958149654984' AND '55.76893750736974') AND (`pet`.`longitude` BETWEEN '37.416257858276374' AND '37.432994842529304')) 
ORDER BY `premium_on` DESC, `date` DESC, `created_at` DESC LIMIT 100;

执行统计数据 #

• 查询耗时：2.921469秒
• 锁等待时间：0.000001秒
• 返回行数：28行
• 扫描行数：656110行
• 影响行数：0行
• 传输字节数：46395字节

执行计划（Explain） #

注：premium_on索引由premium_on、date、created_at三个字段组成

优化方案 #

1. 核心优化：提升经纬度矩形搜索效率 #

当前执行计划未命中地理相关索引，而是通过premium_on索引扫描大量数据，这是性能瓶颈的核心。推荐两种方案：

方案A：创建复合过滤+地理字段索引

将高频过滤条件与经纬度字段组合成索引，让数据库先缩小数据集范围，再筛选地理区域：

CREATE INDEX idx_pet_filter_geo ON pet(unite_id, status, latitude, longitude);

• 索引前缀unite_id（等值过滤IS NULL）和status（范围IN(1,4)）能快速过滤掉不符合条件的记录；
• 后续的latitude和longitude字段直接用于矩形范围筛选，大幅减少扫描行数。

方案B：使用空间索引（MySQL 5.7+支持）

将经纬度转换为空间点类型，利用空间索引的高效范围搜索能力：

-- 1. 添加空间字段（存储经纬度坐标）
ALTER TABLE pet ADD COLUMN location POINT SRID 4326;
-- 2. 批量更新空间字段数据
UPDATE pet SET location = ST_GeomFromText(CONCAT('POINT(', longitude, ' ', latitude, ')'), 4326);
-- 3. 创建空间索引
CREATE SPATIAL INDEX idx_pet_spatial ON pet(location);
-- 4. 修改查询中的地理条件为空间函数写法
AND MBRContains(
  ST_GeomFromText(
    'POLYGON((37.416257858276374 55.750958149654984, 
              37.432994842529304 55.750958149654984, 
              37.432994842529304 55.76893750736974, 
              37.416257858276374 55.76893750736974, 
              37.416257858276374 55.750958149654984))', 
    4326
  ), 
  location
)

空间索引对矩形范围搜索的效率远高于普通B树索引的BETWEEN查询。

2. 优化过滤条件写法，提升索引命中率 #

原OR条件可简化为更易被索引识别的形式：

-- 原条件等价改写
AND status IN (1, 4)
AND (status = 1 OR delete_reason IN (1, 2, 5))

这种写法逻辑清晰，数据库更容易匹配索引前缀规则。

3. 构建覆盖索引，减少回表与排序开销 #

如果需要同时满足过滤、地理搜索和排序需求，可以创建包含所有必要字段的覆盖索引：

CREATE INDEX idx_pet_full_opt ON pet(
  unite_id, 
  status, 
  latitude, 
  longitude, 
  premium_on, 
  date, 
  created_at
);

• 索引包含所有WHERE条件字段、排序字段；
• 如果业务允许，将SELECT *改为只查询需要的字段，可实现完全覆盖索引，避免回表读取原数据，进一步降低IO开销。

4. 避免SELECT *，只查询必要字段 #

SELECT *会强制数据库回表读取所有字段，若业务仅需部分字段，明确列出这些字段，结合覆盖索引可彻底消除回表操作，大幅提升查询速度。

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