MySQL性能调优 – 使用更为快速的算法进行距离计算
最近遇到了一个问题,通过不断的尝试最终将某句原本占据近1秒的查询优化到了0.01秒,效率提高了100倍.
问题是这样的,有一张存放用户居住地点经纬度信息的MySQL数据表,表结构可以简化为:id(int),longitude(long),latitude()long. 而业务系统中有一个功能是查找离某个用户最近的其余数个用户,通过代码分析,可以确定原先的做法基本是这样的:
//需要查询的用户的坐标
$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语句如下:
$sql='select * from users_location where
latitude>'.$lat.'-1 and
latitude<'.$lat.'+1 and
longitude>'.$lon.'-1 and
longitude<'.$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';
经过优化的sql大大提高了运行速度,在某些情况下甚至有100倍的提升.这种从业务角度出发,缩小sql查询范围的方法也可以适用在其他地方.
MySQL性能调优 – 使用UNION ALL代替UNION
最近遇到了一个MySQL性能调优的问题，在数据库中有一张主要用了2个UNION来联合3张表的视图，而所有对于这个视图的查询反应都很慢，经过一步步的调试（说句题外话，用SQLYog来调试MySQL真的很方便），最终确定问题出在这个视图里所用的UNION上。
查了查MySQL的文档，这么说到
The default behavior for UNION is that duplicate rows are removed from the result. The optional DISTINCT keyword has no effect other than the default because it also specifies duplicate-row removal. With the optional ALL keyword, duplicate-row removal does not occur and the result includes all matching rows from all the SELECT statements.
You can mix UNION ALL and UNION DISTINCT in the same query. Mixed UNION types are treated such that a DISTINCT union overrides any ALL union to its left. A DISTINCT union can be produced explicitly by using UNION DISTINCT or implicitly by using UNION with no following DISTINCT or ALL keyword.
举个例子：
如果union两个不同的select，最终的结果则是2个select的集合：
select 1 union select 2; +---+ | 1 | +---+ | 1 | | 2 | +---+ 2 rows in set (0.00 sec)
而如果union的对象包含重复的数据，例如下条语句包含了两个select 2，默认则会去除重复部分：
select 1 union select 2 union select 2; +---+ | 1 | +---+ | 1 | | 2 | +---+ 2 rows in set (0.00 sec)
而如果将上条语句改为union all的话，则可以显示所有的结果，包含重复部分：
select 1 union all select 2 union all select 2; </strong>+---+ | 1 | +---+ | 1 | | 2 | | 2 | +---+ 3 rows in set (0.00 sec)
可以看到，union和union all的差别就在于union会对数据做一个distanct的动作，而这个distanct动作的速度则取决于现有数据的数量，数量越大则时间也越慢。而对于几个数据集，要确保数据集之间的数据互相不重复，基本是O(n)的算法复杂度。
有了理论依据后，便动手更改view的结构，在确保数据逻辑上不会有重复情况出现后，将2个union都改成了union all，query的反应速度从1.7秒变成了300毫秒左右，耗费时间只有以前的17%。