mysql存储过程加速

首先看一个存储过程,这个存储过程是为了构造数据使用的。当然贴出来的存储过程简化了一些不必要的表结构。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE PROCEDURE modifyRootEntry()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE userId INT;
DECLARE userIdIter CURSOR FOR SELECT DISTINCT user_id from entries;
OPEN userIdIter;
read_loop: LOOP
FETCH userIdIter INTO userId;
IF done THEN
LEAVE read_loop;
END IF;
INSERT INTO entries (id, name, user_id, parent_id) VALUES(0, 'root_parent', userId, 0);
UPDATE entries SET parent_id=0 where user_id=userId AND name='file_root' AND parent_id is NULL;
END LOOP;
CLOSE userIdIter;
END;

这个存储过程运行了10分钟,好像仅插入了6w行记录。这个真是太慢了。首先一个问题,就是这里使用了隐式提交,所以这里需要写明transaction开始和commit.修改如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE PROCEDURE modifyRootEntry()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE userId INT;
DECLARE userIdIter CURSOR FOR SELECT DISTINCT user_id from entries;
START TRANSACTION; // here !
OPEN userIdIter;
read_loop: LOOP
FETCH userIdIter INTO userId;
IF done THEN
LEAVE read_loop;
END IF;
INSERT INTO entries (id, name, user_id, parent_id) VALUES(0, 'root_parent', userId, 0);
UPDATE entries SET parent_id=0 where user_id=userId AND name='file_root' AND parent_id is NULL;
END LOOP;
CLOSE userIdIter;
COMMIT;// here !
END;

还有一个问题,就是

1
UPDATE entries SET parent_id=0 where user_id=userId AND name='file_root' AND parent_id is NULL;

这句话应该放在LOOP的外面。因为每次执行的都一样。修改为:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE PROCEDURE modifyRootEntry()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE userId INT;
DECLARE userIdIter CURSOR FOR SELECT DISTINCT user_id from entries;
START TRANSACTION; // here !
OPEN userIdIter;
read_loop: LOOP
FETCH userIdIter INTO userId;
IF done THEN
LEAVE read_loop;
END IF;
INSERT INTO entries (id, name, user_id, parent_id) VALUES(0, 'root_parent', userId, 0);
END LOOP;
UPDATE entries SET parent_id=0 where user_id=userId AND name='file_root' AND parent_id is NULL; // here!
CLOSE userIdIter;
COMMIT;// here !
END;

其实上面的语句仍然不够快,原因在于这个是一行行插入的。可以使用辅助表进行加速。从网上直接copy过来的。这里因为每次插入的是2^n个行,所以速度大大提高。辅助表的写法:

1
2
3
4
5
6
7
8
9
10
11
12
CREATE PROCEDURE pFastCreateNums (cnt INT UNSIGNED)
BEGIN
DECLARE s INT UNSIGNED DEFAULT 1;
TRUNCATE TABLE Nums;
INSERT INTO Nums SELECT s;
WHILE s*2 <= cnt DO
BEGIN
INSERT INTO Nums SELECT a+s FROM Nums;
SET s = s*2;
END;
END WHILE;
END;

上面的是生成一个数字列。当然我们没有必要生成这样一个数字列,但是思想完全可以通用。

  • 本文作者: 帐前卒
  • 本文链接: http://chillyc.info/2013/17389153/
  • 版权声明: 本博客所有文章除特别声明外,只能复制超链接地址,且必须注明出处!