说实话，关于存储过程的博客还真的不多，有几个是值得看一下的
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代码
- call search_around_user(31.000700,120.000099,'2013-03-11 00:00:00',0,20,0)
所耗费的时间和上述直接写SQL的时间是几乎一样的。想来，这也是因为distance的计算不能被优化而导致的。。。于是乎，放弃用存储过程