易栈网-膘叔（Neatstudio.COM）

说实话，关于存储过程的博客还真的不多，有几个是值得看一下的

1、官方；http://dev.mysql.com/doc/refman/5.1/zh/stored-procedures.html

2、http://www.netingcn.com/tag/%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B

3、http://blog.why100000.com/?p=711

也发现，如果不做复杂查询，存储过程对我来说几乎没有，本来是想解决查找GEO相关的信息的，但发现这样的SQL：

SQL代码

SELECT userid,lat,lng,gender,
( 6371 * acos( cos( radians(31.000700) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(120.000099) ) + sin( radians(31.000700) ) * sin( radians( lat ) ) ) ) AS distance
FROM `user_geo` WHERE last_activity_time > '2013-03-11 00:00:00'
ORDER BY distance ASC limit 100

这其中的复杂度就在于distance每次都要计算，所以我尝试换成了存储过程：

SQL代码

DROP PROCEDURE IF EXISTS search_around_user;
DELIMITER //
CREATE PROCEDURE search_around_user
(
s_lat float(10,6),
s_lng float(10,6),
s_last_act datetime,
s_gender tinyint,
s_number tinyint,
s_page tinyint
)
LABEL_PROC:
BEGIN
if s_number <= 1 then
set s_number = 20;
end if;
if s_page <= 0 then
set s_page = 0;
end if;
if s_gender <= 0 then
set @genderQuery = "";
else
set @genderQuery = concat(" and gender = " , s_gender , " ");
end if;
set @limitQuery = concat("LIMIT " , s_page * s_number , " , " , s_number , " ");
set @strsql = CONCAT("select userid, ",
"( 6371 * acos( cos( radians(",s_lat,") ) * cos( radians( lat ) ) * cos( radians( lng ) - radians( ",s_lng," ) ) ",
"+ sin( radians( ",s_lat," ) ) * sin( radians( lat ) ) ) ) AS distance ",
" FROM user_geo where last_activity_time >= '", s_last_act , "' " , @genderQuery , " ORDER BY distance " , @limitQuery) ;
prepare stmtsql from @strsql;
execute stmtsql;
END LABEL_PROC;
//
DELIMITER ;

然后再次调用：

SQL代码

所耗费的时间和上述直接写SQL的时间是几乎一样的。想来，这也是因为distance的计算不能被优化而导致的。。。于是乎，放弃用存储过程

易栈网-膘叔