条件删除数据慢的问题(mysql)
SET autocommit=0;
DROP PROCEDURE IF EXISTS deleteTransfer1Scheme;
CREATE PROCEDURE deleteTransfer1Scheme()
BEGIN
DECLARE foriginStationId varchar(36);
DECLARE fterminalStationId varchar(36);
DECLARE fstationCount INT;
DECLARE num INT DEFAULT 0;
DECLARE rs_cursor CURSOR FOR SELECT originStationId,terminalStationId,MIN(stationCount) + 3 stationCount FROM tb_bus_scheme_286 WHERE transferCount=1 GROUP BY originStationId, terminalStationId;
OPEN rs_cursor;
cursor_loop:LOOP
FETCH rs_cursor INTO foriginStationId, fterminalStationId, fstationCount;
delete from tb_bus_scheme_286 where originStationId=foriginStationId AND terminalStationId=fterminalStationId AND stationCount=fstationCount;
if num=1000 then
commit;
SET num = 0;
else
SET num = num + 1;
end if;
END LOOP cursor_loop;
commit;
CLOSE rs_cursor;
END
CALL deleteTransfer1Scheme();
请问有优化的地方吗,或者条件删除大量数据的好的思路?