关于mysql存储过程执行时间长，求优化思路？
关于mysql存储过程执行时间长，求优化思路？现有一张包含经(lon)纬(lat)度的A （c_pc_stake_info）表,在另一张B表（ b_pc_compaction_info）中也包含有经(lon)纬(lat)度字段，需要返回的数据是A表的经纬度、通过A表每两条数据的经纬度和B表的经纬度相比较，得到B表数据在A表这两条数据经纬度之间的个数
<p>CREATE <a href="mailto:DEFINER=root@%PROCEDUREcompaction">DEFINER=root@%PROCEDUREcompaction</a>(
out num INTEGER,
out startLon DOUBLE,
out startLat DOUBLE,
out startStakeNum varchar(100),
out endLon DOUBLE,
out endLat DOUBLE,
out endStakeNum varchar(100)
)
BEGIN
-- 定义变量
DECLARE s int DEFAULT 0;
DECLARE stakeId INTEGER;
DECLARE lons DOUBLE;
DECLARE lats DOUBLE;
DECLARE stakeNum VARCHAR(50);
DECLARE compaction_lon DOUBLE;
DECLARE compaction_lat DOUBLE;
-- 临时表
-- 封装最后所得数据
create temporary table if not exists stake_info_vo (
number INTEGER,
startLon DOUBLE,
startLat DOUBLE,
startStakeNum VARCHAR(50),
endLon DOUBLE,
endLat DOUBLE,
endStakeNum VARCHAR(50)
);
-- 经B表筛选过的数据插入此表
create temporary table if not exists lon_lat (</p><p>lon1 DOUBLE,
lat1 DOUBLE
);
BEGIN
-- B表筛选后的数据
declare lonLat CURSOR for
SELECT
lon,
lat
from b_pc_compaction_info
where 1=1
and SUBSTRING_INDEX(create_time," ",1) = SUBSTRING_INDEX((select max(create_time) from b_pc_compaction_info)," ",1)
-- ;
and LENGTH(SUBSTRING_INDEX(lon,".",-1)) = 8
and LENGTH(SUBSTRING_INDEX(lat,".",-1)) = 8;
-- insert into lon_lat VALUES (compaction_lon,compaction_lat);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
open lonLat;
fetch lonLat into compaction_lon,compaction_lat;
set s = 0;
while(s=0) do
insert into lon_lat VALUES (compaction_lon,compaction_lat);
fetch lonLat into compaction_lon,compaction_lat;
end while;
-- 关闭游标
close lonLat;
END;
BEGIN
-- 定义游标，并将sql结果集赋值到游标中
DECLARE report CURSOR FOR
-- 查询A表数据
select
id,lon,lat,stake_num
from c_pc_stake_info
ORDER BY id;
-- 声明当游标遍历完后将标志变量置成某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;</p><p>-- 打开游标
open report;</p><p>
-- 将游标中的值赋值给变量，注意：变量名不要和返回的列名同名，变量顺序要和sql结果列的顺序一致
fetch report into stakeId,lons,lats,stakeNum;
set s=0;</p><p> -- 当s不等于1，也就是未遍历完时，会一直循环
while (s<>1) do</p><p> -- 通过遍历获取A表的第二条数据
SELECT
lon,
lat,
stake_num
into endLon,endLat,endStakeNum
FROM c_pc_stake_info
WHERE id = (SELECT MIN(id) FROM c_pc_stake_info WHERE id > stakeId);</p><p> -- 获取B表数据在A表每两条数据经纬度之间的个数
SELECT count(1) into num
from lon_lat
where 1=1
and lon1 between lons and endLon
and lat1 between lats and endLat
;</p><p>
SET startLon= lons;
set startLat= lats;
set startStakeNum= stakeNum;
-- set num = 1;
INSERT INTO stake_info_vo VALUES (num,startLon,startLat,startStakeNum,endlon,endlat,endStakeNum);</p><p> -- 当s等于1时表明遍历以完成，退出循环
fetch report into stakeId,lons,lats,stakeNum;
end while;
-- 关闭游标
close report;
SELECT * from stake_info_vo;
-- SELECT * from lon_lat;
end;
truncate TABLE stake_info_vo;
truncate TABLE lon_lat;
END</p>
如果不改设计的话，应该就是排序算法的选择问题了。 如果改设计的话，需要的是获取区间的个数，那就尽量让他查到即得到结果。那可以每条数据变化时更新他所属区间这条数据所包含的个数；或者是延时更新，即每5分钟后台统计一次，每次查询获取的是上一个5分钟统计的结果 可以参考这篇文章：https://developer.aliyun.com/ask/289654?scm=20140722.184.2.173 从sql开始优化 https://developer.aliyun.com/ask/289654?scm=20140722.184.2.173参考下 一个点一个点的进行优化
页:
[1]
