433
社区成员
发帖
与我相关
我的任务
分享mysql – 存储过程需要很长时间才能完成
我们有一个包含2000行的csv文件和一个大约有200万行的数据库table1和另一个包含60000行的table2.
我们需要根据csv文件中的参数从table1进行查询,因此csv中的每一行都应该执行一个select查询.最初我们尝试在应用程序的for循环中执行以下查询：
SELECT table1.c1,
table1.c2,
ST_Distance_Sphere(point(csv.c2[i], csv.c3[i]),
point(table1.c3, table1.c4)) * 0.2 AS length,
table1.c5
FROM table1
WHERE table1.c1 IN (SELECT DISTINCT table2.c1
FROM table2
LEFT JOIN table2.c1=table3.c1
WHERE table2.c2=1
AND table2.c3 BETWEEN 1000 AND 2000)
HAVING length < csv.c4[i]
AND table1.c5 BETWEEN date("start_date") AND date("end_date")
ORDER BY table.c1
csv.c1 [i] i实际上是循环索引.由于2000次往返MySQL服务器,需要很长时间才能完成.通过查询大约需要16个小时.
所以,我写了下面的SP以避免循环并简单地调用这个SP所以可以在MySQL服务器中进行循环：
CREATE PROCEDURE sp()
BEGIN
DECLARE arg0 VARCHAR(255);
DECLARE arg1 FLOAT;
DECLARE arg2 FLOAT;
DECLARE arg3 FLOAT;
DECLARE cur1 CURSOR FOR SELECT * FROM csv_based_table;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO arg0, arg1, arg2, arg3;
SELECT col0,
col1,
ST_Distance_Sphere(point(arg1, arg2),point(col3,col4))*2 AS length,
arg0
FROM important_table1
WHERE col0 IN (SELECT DISTINCT c0
FROM important_table2
LEFT JOIN table3
ON col0 = c0
WHERE c1=1
and c2 BETWEEN 1000 AND 2000)
HAVING distance < arg3
AND col5 BETWEEN date("start_date") AND date("end_date")
ORDER BY arg0, col0;
END LOOP;
CLOSE cur1;
END;
因此,我们不是使用csv文件,而是为该csv文件创建数据库表并运行上述存储过程.问题是这比原来的2000迭代循环要慢.在超过100 csv行的测试中,基于循环的原始解决方案在317.6秒内完成,而存储过程仅花费321.5秒用于SP本身.为什么会发生这种情况,我该如何优化呢？
...全文
请发表友善的回复…
发表回复
weixin_38093507 2019-09-12
- 打赏
- 举报
这是一个建议：
CREATE TEMPORARY TABLE csv
( i int not null
, c1 ... not null
, c2 ... not null
, c3 ... not null
, c4 ... not null )
将csv文件加载到临时表中,并使用连接来获取结果.
SELECT csv.i
table1.c1,
table1.c2,
csv.st_dist_spere,
ST_Distance_Sphere(point(csv.c2[i], csv.c3[i]),
point(table1.c3, table1.c4)) * 0.2 AS length,
table1.c5
FROM table1
CROSS JOIN csv
WHERE table1.c1 IN (
SELECT DISTINCT table2.c1
FROM table2
LEFT JOIN table2.c1=table3.c1
WHERE table2.c2=1
AND table2.c3 BETWEEN 1000 AND 2000
HAVING length < csv.c4
AND table1.c5 BETWEEN date(start_date) AND date(end_date)
ORDER BY csv.i, table.c1
然后我会看看IN谓词(我假设这是它应该是什么)：
SELECT DISTINCT table2.c1
FROM table2
LEFT JOIN table3
ON table2.c1=table3.c1
WHERE table2.c2=1
AND table2.c3 BETWEEN 1000 AND 2000
可以消除对table3的外连接(智能优化器应该实现这一点,但我不知道这是否在MySQL中实现)：
SELECT DISTINCT table2.c1
FROM table2
WHERE table2.c2=1
AND table2.c3 BETWEEN 1000 AND 2000
这让我们：
SELECT csv.i
table1.c1,
table1.c2,
csv.st_dist_spere,
ST_Distance_Sphere(point(csv.c2[i], csv.c3[i]),
point(table1.c3, table1.c4)) * 0.2 AS length,
table1.c5
FROM table1
CROSS JOIN csv
WHERE table1.c1 IN (
SELECT DISTINCT table2.c1
FROM table2
WHERE table2.c2=1
AND table2.c3 BETWEEN 1000 AND 2000
)
HAVING length < csv.c4
AND table1.c5 BETWEEN date(start_date) AND date(end_date)
ORDER BY csv.i, table.c1
下一步,尝试用JOIN替换IN PREDICATE：
SELECT csv.i
table1.c1,
table1.c2,
csv.st_dist_spere,
ST_Distance_Sphere(point(csv.c2[i], csv.c3[i]),
point(table1.c3, table1.c4)) * 0.2 AS length,
table1.c5
FROM table1
CROSS JOIN csv
JOIN ( SELECT DISTINCT table2.c1
FROM table2
WHERE table2.c2=1
AND table2.c3 BETWEEN 1000 AND 2000
) as X
ON X.c1 = table1.c1
HAVING length < csv.c4
AND table1.c5 BETWEEN date(start_date) AND date(end_date)
ORDER BY csv.i, table.c1
在进行交叉连接之前将范围谓词推送到table1中可能是有利的：
SELECT csv.i
table1.c1,
table1.c2,
csv.st_dist_spere,
ST_Distance_Sphere(point(csv.c2[i], csv.c3[i]),
point(table1.c3, table1.c4)) * 0.2 AS length,
table1.c5
FROM (
select ... from table1
where table1.c5 BETWEEN date(start_date) AND date(end_date)
) as table1
CROSS JOIN csv
[....]
