MySQL百万数据ST_Distance_Sphere函数的优化实践

78万+条订单数据，使用ST_Distance_Sphere函数判断两点距离进行比较

场景

78万+条订单数据，使用ST_Distance_Sphere函数逐个判断两点距离进行比较

待优化代码

待优化代码

每次接口收到请求，查询数据库需要完成一次全表扫描，逐个比对且对每一条数据都执行一次昂贵的球面距离计算。

typerowsExtra
ALL1685899Using where

优化前查询耗时约30秒，正在召唤测试跟前端的连环拷打ing~

表结构优化-添加Point索引

当前存储经纬度的结构，且只创建了一个B+数索引

    pre_destination_longitude    decimal(10, 6) null comment '预计目的地经度',
    pre_destination_latitude     decimal(10, 6) null comment '预计目的地纬度',
...
create index idx_order_info_dest
    on order_information (pre_destination_longitude, pre_destination_latitude);

于是添加空间索引

空间索引的作用，就是给数据库一张“地图”，让它能从“大海捞针”的全表扫描，变成“按图索骥”的精准打击。

由于表中不止网约车订单数据（筛选条件order_type IS NULL），因此可能有部分数据存在pre_destination_longitude、pre_destination_latitude数据为null的情况。但是创建空间索引必须要限制列为非空格式，就像地图上不应该存在不知道位置的点。故采用下面语句修改表结构，将为null的数据设置为“无害的默认值 ”

下面这段SQL耗时较长（共180万+条数据跑了10分钟多），运行时注意环境

-- 第1步：添加一个允许为NULL的列
ALTER TABLE order_information 
ADD COLUMN `coordinate` POINT NULL COMMENT '经纬度坐标点-索引加速';

-- 第2步：用现有数据填充这个新列
UPDATE order_information 
SET coordinate = POINT(pre_destination_longitude, pre_destination_latitude) 
WHERE pre_destination_longitude IS NOT NULL AND pre_destination_latitude IS NOT NULL;

-- 第3步：处理那些经纬度本身就是NULL的数据
UPDATE order_information 
SET coordinate = POINT(0, 0) 
WHERE coordinate IS NULL;

-- 第4步：修改列定义为 NOT NULL
ALTER TABLE order_information 
MODIFY COLUMN `coordinate` POINT NOT NULL COMMENT '经纬度坐标点-索引加速';

-- 第5步：创建空间索引
CREATE SPATIAL INDEX `idx_spatial_coordinate` ON `order_information`(`coordinate`);

空间索引创建完还需要优化SQL。

优化逻辑：

  1. 在java层以入参坐标（及半径）为中心，在地图中划出待查询范围，略去绝大部分区域
  2. 在正方形中比较坐标距离，最后获得圆形区域中包含的各点
画板

不止这一种SQL层优化方案，现在使用的是 边界框+空间索引的方案

还有包括但不限于：Partitioning (分区)、Z-Order(混合经纬度)

如果你需要了解更多 请看–>MySQL Techniques for “Find Nearest”

但是主播在与AI的搏斗中始终没能让它给出合适的、能在我的MySQL用上创建的Point类索引的SQL。如果看到这的你知道了可以教教我

≧ ﹏ ≦

而且数据库设计文档即将完工，不好意思让写文档的同学再修改，于是尝试其他方法

程序逻辑优化-整合Redis实现缓存

我以业务逻辑为主线实现的Redis缓存。业务要求打开页面获得选择框 /getNameTable，选定某一区域获得其详细信息并且详细信息分为三页。其中第二页 /showPage2 内容需要连接多表且数据量极大的表，返回数据时间基本上在十几秒，而其他页面的访问速度并不需要通过缓存优化。但是 /showPage2 的传参多样，因此并不是很合适在启动的时候全部预加载存入Redis。因此我选择通过调用第一页 /showPage1 时使用多线程技术实现后台预加载第二页数据，在缓存加载成功后切换页面的速度将有极大提升。

实现线程池

package com.name.project.config;

import com.google.common.util.concurrent.ThreadFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.ThreadFactory;

/**
 * @Description 自定义创建线程池
 */
@Configuration
public class ThreadPoolConfig {
    @Bean("preCacheExecutor")
    public ExecutorService preCacheExecutor() {
        ThreadFactory namedThreadFactory = new ThreadFactoryBuilder()
                .setNameFormat("precache-pool-%d").build();

        // 创建一个固定大小的线程池-根据业务需求量执行线程池大小
        return Executors.newFixedThreadPool(8, namedThreadFactory);
    }
}

实现类中整合Redis的加载方式

//新增整合了Redis的方法调用模式
@Override
public DataResult<MapShowPage2> getMapShowPage2WithCache(String longitude, String latitude, String name) {
    // 1. 生成缓存键
    String cacheKey = "mapShowPage2:" + longitude + ":" + latitude + ":" + name;

    // 2. 尝试从缓存获取
    MapShowPage2 cachedResult = (MapShowPage2) redisTemplate.opsForValue().get(cacheKey);
    if (cachedResult != null) {
        return DataResult.success(cachedResult);
    }

    // 3. 缓存未命中，执行业务逻辑获取数据，showPage2为业务代码
    DataResult<MapShowPage2> result = this.showPage2(longitude, latitude, name);

    // 4. 存入缓存并设置过期时间(24小时)
    if (result != null && result.getData() != null) {
        redisTemplate.opsForValue().set(cacheKey, result.getData(), 24, TimeUnit.HOURS);
    }

    // 5. 返回结果
    return result;
}

//修改showPage1方法
@Override
public DataResult<MapShowPage1> showPage1(String longitude,String latitude,String name) {

    //实现第二页预加载
    // 使用CompletableFuture.runAsync提交一个异步任务
    CompletableFuture.runAsync(() -> {
        // 这部分代码将在preCacheExecutor线程池中的某个线程上运行
        try {
            System.out.println("线程 [" + Thread.currentThread().getName() + "] 开始异步预热第二页缓存...");
            this.getMapShowPage2WithCache(longitude, latitude, name);
            System.out.println("线程 [" + Thread.currentThread().getName() + "] 异步预热任务结束。");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }, preCacheExecutor);

...原本业务逻辑...

}

Redis缓存后可以实现毫秒级响应

对各请求查询请求次数再搭配定时任务可以实现对热门需求的定时刷新。

程序逻辑优化-SpringBoot定时任务

@Scheduled(fixedRate = 10 * 24 * 60 * 60 * 1000)

This article was updated on 八月 8, 2025