我在MySQL数据库中更新和插入数百万行时遇到了一些问题。我需要在表A中标记5000万行,将标记的5000万行中的一些数据插入到表B中,然后再次更新表A中的这些5000万行。表A中约有1.3亿行,表B中约有8000万行。
这需要在实时服务器上进行,而不会拒绝访问网站上的其他查询。问题是,当这个存储过程运行时,来自网站的其他查询最终被锁定,HTTP请求超时。
以下是SP的要点,为了便于说明,稍作简化:
CREATE DEFINER=`user`@`localhost` PROCEDURE `MyProcedure`(
totalLimit int
)
BEGIN
SET @totalLimit = totalLimit;
/* Prepare new rows to be issued */
PREPARE STMT FROM 'UPDATE tableA SET `status` = "Being-Issued" WHERE `status` = "Available" LIMIT ?';
EXECUTE STMT USING @totalLimit;
/* Insert new rows for usage into tableB */
INSERT INTO tableB (/* my fields */)
SELECT /* some values from TableA */
FROM tableA
WHERE `status` = "Being-Issued";
/* Set rows as being issued */
UPDATE tableB SET `status` = 'Issued' WHERE `status` = 'Being-Issued';
END$$
DELIMITER ;
无论您在做什么,处理50M行三次都会很慢。
确保您的更新影响到较小的、不相交的集合。并逐个执行它们中的每一个,而不是在同一事务中执行每一个。
如果你已经在做这件事了,并且MySQL行为不端,那么试着对你的代码进行一下小小的调整:
create a temporary table
begin
insert into tmp_table
select your stuff
limit ?
for update
do your update on A using tmp_table
commit
begin
do your insert on B using tmp_table
do your update on A using tmp_table
commit
这样可以将锁保持最短的时间。
这个怎么样?它基本上是在循环中调用原始存储过程,直到达到所需的总量,并且在调用之间有一个睡眠期(如2秒),以允许处理其他查询。
increment
是一次要做的量(在这种情况下使用10000)totalLimit
是要处理的总金额sleepSec
是调用之间的休息时间
BEGIN
SET @x = 0;
REPEAT
SELECT SLEEP(sleepSec);
SET @x = @x + increment;
CALL OriginalProcedure( increment );
UNTIL @x >= totalLimit
END REPEAT;
END$$
显然,如果增量不能被整除,它可以使用一点数学来确保增量不会超过总限制,但它似乎是有效的(我指的是允许其他查询仍然从web请求中处理),而且总体上似乎也更快。
这里有什么见解吗?这是个好主意吗?坏主意?