使用经纬度进行距离计算的部分问题

欢乐小黄人

已于 2024-08-09 15:55:47 修改

阅读量512

点赞数 12

文章标签： oracle 数据库 mysql spring

于 2024-08-09 11:22:53 首次发布

本文链接：https://blog.csdn.net/qq_45872037/article/details/141057226

版权

使用经纬度进行距离计算

问题原因

本人在使用自写sql对数据进行数据查询, 当查询条件使用经纬度处理距离长短并使用计算的距离进行分页排序的时候, 即使数据量为0, 界面查询速度也需要700~1000ms, 这就是异常问题,当我把实际的sql放入sql工具中直接查询的时候发现, 数据库中的运行速度需要几毫秒, 后来又使用断点验证的方式查看自己写入的代码, 发现当带经纬度查询的时候, 速度会变慢, 不带的时候, 40~100ms内查询无问题

处理经纬度的sql函数

 ROUND(
        6378.138 * 2 * ASIN(
            SQRT(
                POW(
                    SIN(
                        (
                           纬度 * PI() / 180 - lat * PI() / 180
                        ) / 2
                    ),
                    2
                ) + COS(纬度 * PI() / 180) * COS(lat * PI() / 180) * POW(
                    SIN(
                        (
                            经度 * PI() / 180 - lon * PI() / 180
                        ) / 2
                    ),
                    2
                )
            )
        ) * 1000
    ) AS juli

有兴趣可以参考这位大佬的mysql相关查询: http://www.ibloger.net/article/3261.html

优化

这边请教了以为开发10年的大佬, 他说这地方函数用的太复杂了, 处理需要很多时间, 先优化下函数

身为小白的我开始了优化之旅

优化一

由于此处使用了三角函数, 计算上很复杂, 数据库查询的时候需要对这部分函数需要判断, 增加了耗时.

利用数学的思维, 此处使用三角函数, 使用曲面的方式计算距离, 但是所有的曲面都是由平面的方式推导而出, 那么是不是可以直接将数据用平面的方式解决呢?

此处将上面的复杂函数解决为:

 ABS(纬度 - ifnull(lat,0)) + ABS(经度 - ifnull(lon,0)) as juli

此处再次查询验证猜想, 当前查询数据量为0的时候,查询时间恢复到40~50ms, 看来是函数的问题导致查询速度变慢了, 随即再次进入生产环境
问题:

此处查询速度虽然快了, 但是距离的精度却下降了, 所以被投诉了, 如果对数据精度要求不高,可以使用此方法处理数据,随即开始优化二的处理之路

优化二

因为优化一是牺牲精度的方式而加快了速度,但是实际需要高精度的场景会让使用者很无奈, 随即我开始了研究函数优化, 三角函数是一定要用的, 此时对部分参数开始优化操作, 我将内置函数的PI()(π), 修改成了3.14, 将所有的使用非数值的数据,转化为精度可确定的数值数据.

 ROUND(
        6378.138 * 2 * ASIN(
            SQRT(
                POW(
                    SIN(
                        (
                           纬度 * 3.14 / 180 - lat * 3.14 / 180
                        ) / 2
                    ),
                    2
                ) + COS(纬度 * 3.14 / 180) * COS(lat * 3.14 / 180) * POW(
                    SIN(
                        (
                            经度 * 3.14 / 180 - lon * 3.14 / 180
                        ) / 2
                    ),
                    2
                )
            )
        ) * 1000
    ) AS jul

修改完成后验证数据, 此处的查询速度恢复180~300ms内. 精度较优化一要提升很多, 后来又去查看PI()函数在sql中是多少时,是这么说的:

在 SQL 中，PI() 函数返回圆周率 π 的值。具体数值会因数据库系统的实现而有所不同，但通常精确到小数点后 15 到 16 位。

常见数据库中 PI() 的值通常为：

MySQL: PI() 返回 3.141592653589793
PostgreSQL: PI() 返回 3.141592653589793
SQL Server: PI() 返回 3.14159265358979
Oracle: Oracle 没有内置的 PI() 函数，但可以使用 3.141592653589793 或通过 ACOS(-1) 来计算 π。

在不同的数据库中, PI()函数小数位很长, 过长的小数位计算对数据库性能有很大的影响

此处验证后,能满足精度和查询速度的需求(先到这, 后面再看怎么来优化这个)

此处回答三个问题

为什么在代码中操作带函数的sql和数据库工具直接使用sql查询区别会这么大?

因为数据库的工具对sql函数有特别的处理, 如果数据库工具对某类数据库中的函数进行了优化处理,那么它就比实际的代码来的快, 因为代码获取数据库中的数据,需要创建数据库连接,有数据库连接池的还需要到连接池中获取对应的连接后才能去查询sql,相对而言会慢一点,其次使用springboot+mybatis或mybatisplus获取数据库时还需要对查询条件的解读也需要一定的时间,虽然感觉不大.但是也是有一定的原因

为什么数据量为0的时候,能确定是函数导致的慢,而不是因为sql写的太差而导致的?

首先就是剔除查询中的比较耗时的查询条件, 确定数据正确性以后, 再将距离的sql放到整个查询语句中验证,发现没有距离的时候,速度很快,整个接口的响应速度只需要20~50ms, 自然就确定是距离计算的问题;

有些优化为什么在本地有效果, 在服务器上的效果却不大?

可能的原因:

 	1. 数据量太大,已经不足以支撑带计算的服务查询
 	2. 整个数据中带有隐式数据转换, 如将varchar修改为数值型或其他类型
 	3. 数据库逻辑设计存在问题,在连接多表后,sql执行过程中产生了几何倍数的增长,数据库响应不了
 	4. 服务器实在是太老了(亲身经历操作05年生产的服务器, 优化要老命了)
 	5. 使用的查询表是视图数据