MySQL性能调优 – 使用更为快速的算法进行距离计算

最新推荐文章于 2022-03-09 11:33:49 发布

haodafeng2000

最新推荐文章于 2022-03-09 11:33:49 发布

阅读量1k

点赞数

文章标签： mysql 算法数学计算 sql 代码分析优化

13一/10 0

MySQL性能调优 – 使用更为快速的算法进行距离计算

最近遇到了一个问题,通过不断的尝试最终将某句原本占据近1秒的查询优化到了0.01秒,效率提高了100倍.

问题是这样的,有一张存放用户居住地点经纬度信息的MySQL数据表,表结构可以简化为:id(int),longitude(long),latitude()long. 而业务系统中有一个功能是查找离某个用户最近的其余数个用户,通过代码分析,可以确定原先的做法基本是这样的:

//需要查询的用户的坐标

1

2

3

4

 
        $lat 
        =20; 
       
 
        $lon 
        =20; 
       
 
        //执行查询,算出该用户与所有其他用户的距离,取出最近的10个 
       
 
        $sql 
        = 
        'select * from users_location order by ACOS(SIN((' 
        . 
        $lat 
        . 
        ' * 3.1415) / 180 ) *SIN((latitude * 3.1415) / 180 ) +COS((' 
        . 
        $lat 
        . 
        ' * 3.1415) / 180 ) * COS((latitude * 3.1415) / 180 ) *COS((' 
        . 
        $lon 
        . 
        ' * 3.1415) / 180 - (longitude * 3.1415) / 180 ) ) * 6380  asc limit 10' 
        ; 
       

而这条sql执行的速度却非常缓慢,用了近1秒的时间才返回结果,应该是因为order里的子语句用了太多的数学计算公式,导致整体的运算速度下降.

而在实际的使用中,不太可能会发生需要计算该用户与所有其他用户的距离,然后再排序的情况,当用户数量达到一个级别时,就可以在一个较小的范围里进行搜索,而非在所有用户中进行搜索.

所以对于这个例子,我增加了4个where条件,只对于经度和纬度大于或小于该用户1度(111公里)范围内的用户进行距离计算,同时对数据表中的经度和纬度两个列增加了索引来优化where语句执行时的速度.

最终的sql语句如下:

1

2

3

4

5

6

 
        $sql 
        ='select * from users_location where 
       
 
        latitude&gt; 
        '.$lat.' 
        -1  
        and 
       
 
        latitude&lt; 
        '.$lat.' 
        +1  
        and 
       
 
        longitude&gt; 
        '.$lon.' 
        -1  
        and 
       
 
        longitude&lt; 
        '.$lon.' 
        +1 
       
 
        order by  
        ACOS 
        (SIN(( 
        '.$lat.' 
         * 3.1415) / 180 ) *SIN((latitude * 3.1415) / 180 ) + 
        COS 
        (( 
        '.$lat.' 
         * 3.1415) / 180 ) *  
        COS 
        ((latitude * 3.1415) / 180 ) * 
        COS 
        (( 
        '.$lon.' 
         * 3.1415) / 180 - (longitude * 3.1415) / 180 ) ) * 6380  asc  limit 10'; 
       