MYSQL 函数逐渐变慢

Question

我创建了以下函数来通过解析字符串来输入记录:

DROP FUNCTION IF EXISTS RowPerRow ;
DELIMITER $$
CREATE FUNCTION RowPerRow()
RETURNS VARCHAR(300)
BEGIN
    DECLARE n INT DEFAULT 0 ;
    DECLARE m INT DEFAULT 0 ;
    DECLARE i INT DEFAULT 0 ;
    DECLARE j INT DEFAULT 0 ;
    SELECT count(*) FROM Temp0 INTO n ;
    WHILE i < n DO
        SELECT words INTO m FROM Temp0 LIMIT i, 1 ;
        SET j = 1 ;
        WHILE j <= m DO
            INSERT INTO Temp1 SELECT words,id,api,basin,play,drilltype,tradeflag,score,cnf,fracdate,
                fracdateend,state,county,operator,wellname,prodtype,latitude,
                longitude,datum,depth,water,nonwater,surfactant,tradename,TRIM(Split_Str(tradename, ',', j)) AS tradename_c,
                supplier,purpose,ingredients,cas,additive,fluid 
            FROM Temp0 LIMIT i,1 ;
            SET j = j + 1 ;
        END WHILE ;
        SET i = i + 1 ;
    END WHILE ;
    RETURN '' ;
END ;
$$

随着记录数量的增加，该函数逐渐变慢。

Answer 1

如果问题是“执行相同 RBAR(逐行痛苦)处理但不会逐渐变慢的替代方法的示例是什么”，我可以举一个例子来说明这会发生什么看起来像。

如果单词存在上限(最大值)，则可以通过单个语句实现相同的结果:

  INSERT INTO Temp1 
         ( words,  id,  api,  basin,  play,  drilltype,  tradeflag  ,score  ,cnf,  fracdate,  fracdateend,  state,  county,  operator,  wellname,  prodtype,  latitude,  longitude,  datum,  depth,  water,  nonwater,  surfactant,  tradename,  supplier,  purpose,  ingredients,  cas,  additive,  fluid
         , tradename_c )
  SELECT t.words,t.id,t.api,t.basin,t.play,t.drilltype,t.tradeflag,t.score,t.cnf,t.fracdate,t.fracdateend,t.state,t.county,t.operator,t.wellname,t.prodtype,t.latitude,t.longitude,t.datum,t.depth,t.water,t.nonwater,t.surfactant,t.tradename,t.supplier,t.purpose,t.ingredients,t.cas,t.additive,t.fluid
         , TRIM(Split_Str(t.tradename, ',', n.i)) AS tradename_c
    FROM Temp0 t
    JOIN (SELECT 1 AS i UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20) n
      ON n.i <= t.words

但这里是一个与原始过程等效的过程示例，只不过它使用游标循环而不是重复地重新查询 Temp0 表。请注意，变量的数据类型(接受 fetch 返回的行中的值)应与 SELECT 列表中相应表达式的数据类型匹配或兼容。在这里，我将它们全部声明为 VARCHAR(100) 作为占位符，需要更改它们以匹配 Temp0 表中的列。

DELIMITER $$

CREATE FUNCTION RowPerRow_alt()
RETURNS VARCHAR(300)
BEGIN
    -- variables for cursor fetch
    -- the datatype of each variable should match the datatype 
    -- of the expression in SELECT list of the query
    DECLARE v_words        INT;
    DECLARE v_id           VARCHAR(100);
    DECLARE v_api          VARCHAR(100);
    DECLARE v_basin        VARCHAR(100);
    DECLARE v_play         VARCHAR(100);
    DECLARE v_drilltype    VARCHAR(100);
    DECLARE v_tradeflag    VARCHAR(100);
    DECLARE v_score        VARCHAR(100);
    DECLARE v_cnf          VARCHAR(100);
    DECLARE v_fracdate     VARCHAR(100);
    DECLARE v_fracdateend  VARCHAR(100);
    DECLARE v_state        VARCHAR(100);
    DECLARE v_county       VARCHAR(100);
    DECLARE v_operator     VARCHAR(100);
    DECLARE v_wellname     VARCHAR(100);
    DECLARE v_prodtype     VARCHAR(100);
    DECLARE v_latitude     VARCHAR(100);
    DECLARE v_longitude    VARCHAR(100);
    DECLARE v_datum        VARCHAR(100);
    DECLARE v_depth        VARCHAR(100);
    DECLARE v_water        VARCHAR(100);
    DECLARE v_nonwater     VARCHAR(100);
    DECLARE v_surfactant   VARCHAR(100);
    DECLARE v_tradename    VARCHAR(100);
    DECLARE v_supplier     VARCHAR(100);
    DECLARE v_purpose      VARCHAR(100);
    DECLARE v_ingredients  VARCHAR(100);
    DECLARE v_cas          VARCHAR(100);
    DECLARE v_additive     VARCHAR(100);
    DECLARE v_fluid        VARCHAR(100);

    DECLARE j INT DEFAULT 0 ;
    DECLARE v_tradename_c  VARCHAR(100);
    DECLARE done TINYINT(1) DEFAULT 0;

    -- cursor for Temp0
    DECLARE csr CURSOR FOR
      SELECT words,id,api,basin,play,drilltype,tradeflag,score,cnf,fracdate,
             fracdateend,state,county,operator,wellname,prodtype,latitude,
             longitude,datum,depth,water,nonwater,surfactant,tradename,
             supplier,purpose,ingredients,cas,additive,fluid 
        FROM Temp0;
    -- setup NOT FOUND handler    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  

    OPEN csr;
    rowloop: LOOP
        FETCH csr INTO v_words,v_id,v_api,v_basin,v_play,v_drilltype,v_tradeflag,v_score,v_cnf,v_fracdate,v_fracdateend,v_state,v_county,v_operator,v_wellname,v_prodtype,v_latitude,v_longitude,v_datum,v_depth,v_water,v_nonwater,v_surfactant,v_tradename,v_supplier,v_purpose,v_ingredients,v_cas,v_additive,v_fluid;
        IF done THEN
            LEAVE rowloop;
        END IF;
        SET j = 1;
        WHILE j <= v_words DO
            -- split tradename and insert row ti Temp1
            SET v_tradename_c = TRIM(Split_Str(v_tradename, ',', j));
            INSERT INTO Temp1 ( words,  id,  api,  basin,  play,  drilltype,  tradeflag  ,score  ,cnf,  fracdate,  fracdateend,  state,  county,  operator,  wellname,  prodtype,  latitude,  longitude,  datum,  depth,  water,  nonwater,  surfactant,  tradename,  supplier,  purpose,  ingredients,  cas,  additive,  fluid,
              tradename_c )
                     VALUES ( v_words,v_id,v_api,v_basin,v_play,v_drilltype,v_tradeflag,v_score,v_cnf,v_fracdate,v_fracdateend,v_state,v_county,v_operator,v_wellname,v_prodtype,v_latitude,v_longitude,v_datum,v_depth,v_water,v_nonwater,v_surfactant,v_tradename,v_supplier,v_purpose,v_ingredients,v_cas,v_additive,v_fluid,
              v_tradename_c );
            SET j = j + 1 ;
        END WHILE;
    END LOOP rowloop;
    CLOSE csr;
    RETURN '' ;
END$$