如何优雅更新MYSQL大表?

海鲨数据库架构师 2022-05-29

555

最近越南经济向荣,作为一个对外开放的城市深圳,自然需要对越南有所服务.

为此公司高层决定进军越南市场,这几周开发人员都天天加班.目的是在原有的业务系统上去做改进型,来支持越南市场.

因此订单表ORDER和客户表都增加了COUNTRY. 最近通过了测试,各个系统逐步上线.

作为DBA,不就是上线支持下嘛,有什么困难呢? 上篇文章的多字段添加的优化,就是其中一项.

新数据的增加自然写入了国家字段,可是老数据呢? 开发人员要求把老数据也同步更新下.

不就是两表关联更新而已,很EASYZ啊!

UPDATE
ORDER
X, COUSTOM_INFO Y

SET
X.COUNTRY=Y.COUNTRY

WHERE
X.COUSTOM_ID =Y.COUSTOM_ID ;

如果是这样的话,那就不是称职的DBA,毕竟订单表有2千万的旧数据.

这个UPDATE下去,要等2千万数据全更新完后才会提交, 假设需要10分钟的时间.如果这10分钟有业务去对旧数据访问,做些DML操作,那就是被阻塞10分钟.

很显然数据库不可用就会被领导知道, DBA在团队的价值就直线下降.

再说那么大的事务,会产生很多BINLOG的. 对BINLOG是没办法避免的,

会对BINLOG CACHE 消耗很大, 超过BINLOG CACHE的大小,BINLOG CACHE会隐藏把CACHE 放到磁盘上,作为临时文件,暂存下.

很显然 IO会受到很大的影响, 此时此刻除了BINLOG CACHE需要IO外, REDO LOG需要, UNDO LOG 也需要,其他事务的提交 BINLOG 需要写入文件里.

然后再说下UNDO ,大事务会把旧的数据放在UNDO里长时间被使用,一当超过上限,会触发清理线程,清理线程一看,哦! 还在使用中没办法清理,只好等到.把数据写入UNDO LOG吧 .

所以从ORACLE 带来的经验, 可以批量处理下, 哦 ! 不对应该叫分批提交.

ORACLE 处理起来有点麻烦,可以用游标,也可以用数组!

不过我们MYSQL 有大威天龙 LIMIT 大法 !!!

UPDATE
ORDER_INFO
X, COUSTOM_INFO Y

SET
X.COUNTRY=Y.COUNTRY

WHERE
X.COUSTOM_ID =Y.COUSTOM_ID

LIMIT 1000;

哈哈! 结果不支持, MYSQL 不支持两表关联更新的LIMIT,支持单表!

那只能使用数组功能, ORACLE有, SHELL 也有,唯独MYSQL 没有!

转转兜兜一天, 发现内存表可以做数组!

创建内存表

CREATE TEMPORARY TABLE IF NOT EXISTS MEM_ORDER_INFO (ID BIGINT,COUNTRY VARCHAR(30),PAY_TIME DATETIME ) ENGINE = MEMORY; 

复制

插入内存表

INSERT INTO MEM_ORDER_INFO (ID,COUNTRY)
SELECT X.ID,Y.COUNTRYFROM ORDER_INFO X,CUSTOR_INFO YWHERE X.COUSTOM_ID =Y.COUSTOM_ID LIMIT 1000;
复制

根据内存表分批更新订单表

UPDATE ORDER_INFO  X,MEM_ORDER_INFO Y
SET  X.COUNTRY= Y.COUNTRY
WHERE  X.ID=Y.ID;

复制

结果在测试库去更新500万的数据都花费了1个小时

不该啊! 慢, 咋有脸面对开发工程师呢? DBA的老脸往哪里搁啊?

不行得优化,先得计时统计,看哪步会慢?

SET @START_TIME=CURRENT_TIME();
##中间包围 要执行的语句
SELECT CONCAT(TIMEDIFF(CURRENT_TIME(),@START_TIME)) AS "UPDATE SPEND TIME";    

复制

发现插入内存表要慢

加入非空条件还是有慢

INSERT INTO MEM_ORDER_INFO (ID,COUNTRY)SELECT X.ID,Y.COUNTRYFROM ORDER_INFO X,COUSTOM_INFO YWHERE X.COUSTOM_ID =Y.COUSTOM_ID AND X.COUNTRY IS NULL
LIMIT 1000;
复制

看了下执行计划

要扫描全表,全分区. 丢! 每次都要来一次 500W/1K=5千次.

总体时间远远大于单语句的更新.

既然要扫描,重复地扫描,何必呢? 订单表有分区,那么就按分区扫描

指定分区 ORACLE有,MYSQL必然也有

INSERT INTO MEM_ORDER_INFO (ID,COUNTRY)
SELECT X.ID,Y.COUNTRY
FROM ORDER_INFO PARTITION(PARTITION_NAME_202205) X,COUSTOM_INFO Y
WHERE X.COUSTOM_ID =Y.COUSTOM_ID AND X.COUNTRY IS NULL
LIMIT 1000;

复制

这样重复性5前次扫描,就限制于单分区范围内! 哈哈开心下

很自然地我们从INFORMATION里搞到订单表的分区名字,

然后放进变量里, 再把变量放进查询语句里如下:

SET @SCHEMA='SHARK';
SET @TABLENAME='ORDER_INFO';
SELECT PARTITION_NAME INTO @PARTION_NAME
     FROM INFORMATION_SCHEMA.PARTITIONS 
    WHERE TABLE_SCHEMA = ''@SCHEMA ''
      AND TABLE_NAME = '' @TABLENAME ''
      AND PARTITION_NAME IS NOT NULL
      AND TABLE_ROWS > 0;

INSERT INTO MEM_ORDER_INFO (ID,COUNTRY)
SELECT X.ID,Y.COUNTRY
FROM ORDER_INFO PARTITION(@PARTION_NAME) X,CUSTOR_INFO Y
WHERE X.CUSTOR_ID=Y.CUSTOR.ID
AND X.COUNTRY IS NULL
LIMIT 1000;

复制

啊太气人了 MYSQL 存储过程指定分区不支持变量,必须写清

PARTITION(PARTION_NAME_2005)

调整下心情

祭出我们的动态SQL

                            -- 分批入内存表 分区名,NULL,LIMIT
 SET @INSERT_SQL= CONCAT('
     INSERT INTO MEM_ORDER_INFO(ID,COUNTRY)   
            SELECT A.ID,A.COUNTRY
            FROM
            (       
               SELECT  S.ID,M.COUNTRY,S.PAY_TIME
                 FROM  ORDER_INFO PARTITION(',L_PARTITION_NAME,') S 
            LEFT JOIN COUSTOM_INFO M ON S.CUSTOM_ID=M.CUSTOM_ID
                WHERE S.SUBJECT_NAME IS NULL 
                ) A
             LIMIT 1000');  

    PREPARE STMT2 FROM @INSERT_SQL;
    EXECUTE  STMT2 ;            

SELECT CONCAT('INSERT_SQL TIME:',TIMEDIFF(CURRENT_TIME(),@START_TIME)) AS "INSERT SQL SPEND TIME";

复制

这下解决了扫描全表的问题,还有讨厌的MYSQL存储过程不支持指定分区变量.

还有没有优化的空间呢? 其实单独分区重复多次扫描, 这跟全表扫描没有区别.

把分区看做一个稍微小的表,不是大表,而是中等表. 如何让它不重复多次扫描呢?

对我们要利用上索引,分区是按PAY_TIME支付时间来分区的.

而且经过检查订单表有单独的PAY_TIME索引.

我们知道MYSQL的分区二级索引,也叫辅助索引都是分区索引,跟分区文件存储在一起的.

                            -- 分批入内存表 分区名,NULL,LIMIT
 SET @INSERT_SQL= CONCAT('
     INSERT INTO MEM_ORDER_INFO(ID,COUNTRY)   
            SELECT A.ID,A.COUNTRY
            FROM
            (       
               SELECT  S.ID,M.COUNTRY,S.PAY_TIME
                 FROM  ORDER_INFO PARTITION(',L_PARTITION_NAME,') S 
            LEFT JOIN COUSTOM_INFO M ON S.CUSTOM_ID=M.CUSTOM_ID                WHERE S.SUBJECT_NAME IS NULL 
                  AND S.PAY_TIME >= ''',@LAST_PAYTIME,'''
                  AND S.PAY_TIME <= ''',@PARTION_PAYTIME,'''
             ORDER BY S.PAY_TIME ASC
                ) A
             LIMIT 1000');  

    PREPARE STMT2 FROM @INSERT_SQL;
    EXECUTE  STMT2 ;            

SELECT CONCAT('INSERT_SQL TIME:',TIMEDIFF(CURRENT_TIME(),@START_TIME)) AS "INSERT SQL SPEND TIME";

复制

这样子执行时间为1秒钟时间,整体执行下来耗费了5分钟.

CREATE DEFINER=`SHARK`@`192.168.0.%` PROCEDURE `UPDATE_ORDER_INFO`()
    DETERMINISTIC
    COMMENT '分批更新订单老数据国家字段信息'
BEGIN
    CREATE TEMPORARY TABLE IF NOT EXISTS MEM_ORDER_INFO (ID BIGINT,COUNTRY VARCHAR(30),PAY_TIME DATETIME ) ENGINE = MEMORY;  -- 如果表已存在，则使用关键词 IF NOT EXISTS 可以防止发生错误
    CREATE TEMPORARY TABLE IF NOT EXISTS MEM_PARTITION (ID BIGINT,PARTITION_NAME VARCHAR(30) ) ENGINE = MEMORY;  -- 如果表已存在，则使用关键词 IF NOT EXISTS 可以防止发生错误

BEGIN
DECLARE  L_PARTITION_NAME VARCHAR(50);      
DECLARE  START_TIME DATETIME; 

        TRUNCATE TABLE MEM_ORDER_INFO ;               -- 使用 TRUNCATE TABLE 的方式来提升性能
        TRUNCATE TABLE MEM_PARTITION;

        SET @MAX_HEAP_TABLE_SIZE=67108864;                     -- 设置内存表最大值;
        SET @SCHEMA=   'SHARK'; 
        SET @TABLENAME='ORDER_INFO';    
        SET @START_TIME=CURRENT_TIME();

                               -- 我们把分区信息也插入内存表里去,这里使用@ROWNUM变量形成自增ID
 SET @INSERT_MEM_SQL=CONCAT('    
   INSERT INTO MEM_PARTITION(ID,PARTITION_NAME)
   SELECT @ROWNUM:=@ROWNUM+1 AS XNUM ,PARTITION_NAME
     FROM INFORMATION_SCHEMA.PARTITIONS ,(SELECT @ROWNUM:=0) T
    WHERE TABLE_SCHEMA = ''',@SCHEMA,'''
      AND TABLE_NAME = ''',@TABLENAME,'''
      AND PARTITION_NAME IS NOT NULL
      AND TABLE_ROWS > 0');

        #SELECT @INSERT_MEM_SQL;     
    PREPARE STMT11 FROM @INSERT_MEM_SQL;
    EXECUTE  STMT11 ;

SET @PARTIONCOUNT_SQL=CONCAT('
   SELECT COUNT(PARTITION_NAME) INTO @PARTITION_COUNT
     FROM INFORMATION_SCHEMA.PARTITIONS 
    WHERE TABLE_SCHEMA = ''',@SCHEMA,'''
      AND TABLE_NAME = ''',@TABLENAME,'''
      AND PARTITION_NAME IS NOT NULL
      AND TABLE_ROWS > 0');


    PREPARE STMT12 FROM @PARTIONCOUNT_SQL;
    EXECUTE  STMT12 ;

    -- 上面两个其实可以不用动态语句方法  

    # SELECT * FROM MEM_PARTITION;
   -- 初始化变量和外循环次数@J    
SET @J=1;
SET L_PARTITION_NAME='';
SET @LAST_PAYTIME='1982-01-01 00:00:00';
SET @PARTION_PAYTIME ='1982-01-01 00:00:00';


WHILE @J < @PARTITION_COUNT+1
DO      
        SELECT PARTITION_NAME INTO L_PARTITION_NAME  FROM MEM_PARTITION WHERE ID=@J;
        ## 根据@J找到对应的分区名

        SET @ALLROWS_SQL= CONCAT(' SELECT COUNT(*) ,MAX(PAY_TIME)INTO @ALLROWS,@PARTION_PAYTIME FROM ORDER_INFO PARTITION (',L_PARTITION_NAME,' ) WHERE COUNTRY IS  NULL');

        ## 获得本次分区总数,结束时间
    PREPARE STMT1 FROM @ALLROWS_SQL;
    EXECUTE  STMT1 ;


    SELECT CONCAT(L_PARTITION_NAME,':',@ALLROWS) AS 'PARTITION IS NULL COUNT'; 

    SET  @LOOPCOUNT=CEIL(@ALLROWS/1000);
    SET @I=0;


  WHILE @I < @LOOPCOUNT                                                 
    DO                                  -- 内循环
    SET @I=@I+1; 
    SELECT CONCAT('OUTER LOOP:',L_PARTITION_NAME,' INNER LOOP:',ROUND(@LOOPCOUNT-@I,0),' PARTION PAYTIME:',@PARTION_PAYTIME,' LAST_PAYTIME:',@LAST_PAYTIME) AS 'REMAINING TIMES';
                                        -- 分批入内存表 分区名,时间戳,NULL,LIMIT
       SET @INSERT_SQL= CONCAT('
            INSERT INTO MEM_ORDERY_INFO(ID,COUNTRY,PAY_TIME)   
                        SELECT A.ID,A.COUNTRY,A.PAY_TIME
                        FROM
                        (       
              SELECT  S.ID,M.COUNTRY,S.PAY_TIME
                FROM  ORDERY_INFO PARTITION(',L_PARTITION_NAME,') S 
            LEFT JOIN COUSTOM_INFO M ON S.CUSTOM_ID=M.CUSTOM_ID
                WHERE S.COUNTRY IS NULL 
                  AND S.PAY_TIME >= ''',@LAST_PAYTIME,'''
                  AND S.PAY_TIME <= ''',@PARTION_PAYTIME,'''
             ORDER BY S.PAY_TIME ASC
            ) A
                        LIMIT 1000');  

        #SELECT @INSERT_SQL;     
    PREPARE STMT2 FROM @INSERT_SQL;
    SET @START_TIME=CURRENT_TIME();
        EXECUTE  STMT2 ;            
        SELECT CONCAT('INSERT_SQL TIME:',TIMEDIFF(CURRENT_TIME(),@START_TIME)) AS "INSERT SQL SPEND TIME";

        SET @UPDATE_SQL= CONCAT
   ('
    UPDATE ORDERY_INFO PARTITION(',L_PARTITION_NAME,') X,MEM_ORDERY_INFO Y
    SET  X.COUNTRY = Y.COUNTRY 
    WHERE  X.ID=Y.ID'
    );


    PREPARE STMT3 FROM @UPDATE_SQL;
        EXECUTE  STMT3 ;    

    SELECT MAX(PAY_TIME) INTO  @LAST_PAYTIME  FROM MEM_SALES_PROFIT_INFO; -- 获取最大PAY_TIME 为下次做定位

        TRUNCATE TABLE MEM_ORDERY_INFO;               -- 使用 TRUNCATE TABLE 的方式来提升性能
  END WHILE;
   SET @J=@J+1;
 END WHILE;   
END;
    DROP TABLE MEM_ORDERY_INFO ;
    SELECT CONCAT(TIMEDIFF(CURRENT_TIME(),@START_TIME)) AS "UPDATE SPEND TIME";    
END

复制