mysql - 大型表上的 SP 调用期间遇到错误 2013

Question

我在 SP 调用期间遇到连接丢失错误，从表 A 中的“有效自”和“有效至”列之间拆分日期，并将它们与其他列一起插入到新表 B 中。

表 A 大约有 300 万行，因此我预计表 B 可能会超过 1000 万行。

SP 运行了 600 秒，然后显示 2013 错误。

我多次尝试调用此 SP，但均未成功。

我该如何解决这个问题？对表 A 进行分区会有帮助吗？

索引呢？

如下表A:

     CREATE TABLE `test_data`.`offer_master` (
      `off_id` INT NOT NULL primary key,
      `hot_id` INT NOT NULL,
      `curr_id` INT NOT NULL,
      `price_dollar` FLOAT NULL DEFAULT NULL,
      `price_local` FLOAT NOT NULL,
      `curr_code` VARCHAR(35) NOT NULL,
      `valid_from` DATETIME NOT NULL,
      `valid_to` DATETIME NOT NULL,
      `breakfast_included` TINYINT(1) NOT NULL,
      `valid_offer` TINYINT(1) NOT NULL)
       ENGINE=InnoDB DEFAULT CHARSET=utf8;

表 B:

     CREATE TABLE `test_data`.`split_by_date` (
    `id` int NOT NULL, 
    `hotel_id` int not NULL,
    `original_price` float not NULL,
    `currency_id` int not NULL,
    `dates` date DEFAULT NULL)
    ENGINE=InnoDB DEFAULT CHARSET=utf8;

从 A 中分离出日期并插入到 B 中的 SP:

delimiter //

CREATE PROCEDURE `split_dates`()
BEGIN
DECLARE aid INT;
DECLARE avalid_from_date DATE;
DECLARE avalid_to_date DATE;
DECLARE sp_hotel_id INT;
DECLARE local_price float;
DECLARE sp_currency_id int;
DECLARE tempdt DATE;
DECLARE done INT DEFAULT FALSE;
DECLARE getdates CURSOR FOR 
SELECT off_id,valid_from,valid_to,hot_id,price_local,curr_id 
from offer_master;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN getdates;

read_loop: LOOP
FETCH getdates 
INTO aid,avalid_from_date,avalid_to_date,sp_hotel_id,local_price,sp_currency_id;

    IF done THEN
  LEAVE read_loop;  
END IF;

set tempdt=date(avalid_from_date);

WHILE (tempdt <= date(avalid_to_date)) do
  insert into split_by_date(id,hotel_id,original_price,currency_id,dates) 
  values(aid,sp_hotel_id,local_price,sp_currency_id,tempdt);
  set tempdt=tempdt+INTERVAL 1 DAY;
end while;


 END LOOP;
 CLOSE getdates;
 END
 //

Answer 1

快速修复可能是

START TRANSACTION;
CALL ... ;
COMMIT;

更好的解决方法是认识到 CURSORs 几乎总是错误的 SQL 编写方式。我们可以通过一次对所有 300 万行进行操作来摆脱游标。但我不知道如何摆脱 WHILE 循环。

SELECT @n := 0;  -- Or "1"?  (depends on whether you want to start with `valid_from`.
myloop:  LOOP
    INSERT INTO split_by_date(id, hotel_id, original_price, currency_id, dates)
        SELECT            off_id, hot_id,   price_local,    curr_id,
                          valid_from + INTERVAL n DAY
            FROM offer_master
            WHERE valid_from + INTERVAL @n DAY < valid_to;  -- maybe "<=" ?
    IF ROW_COUNT = 0 THEN
        LEAVE myloop;    -- when a pass over the data has no rows to add
    END IF
END LOOP myloop;

INDEX 或 PARTITION 都无济于事；它只需要重复进行表扫描。

但是，您真的应该在每个表上至少有一个 PRIMARY KEY。

用FLOAT 换钱是不明智的。请参阅 DECIMAL。