亿级大表在线不锁表变更字段与索引

摘要：在业界中有一个比较成熟的工具，针对大表的场景，可以在线进行Alter变更，且不会出现锁表的风险。除此之外，它还有其他的一些优点，让我们开始探索吧。

背景

大家在日常工作中，往往需要对数据库的表结构做变更，一般涉及到增删字段，修改字段属性等ALTER的操作。然而，在大表场景下，特别是千万级、亿级的大表，如果处理不当。这些操作往往会引发锁表的巨大隐患，特别是在生产环境中，一旦在变更表结构过程中，出现了长时间锁表，会导致用户产生的数据长时间无法正常变更到表中，进而导致服务功能异常，结果将是灾难性的。

一般执行这种Alter类型的变更，我们可能有以下的想法：

1、停服，在停服期间做表结构的变更，自然就可以防止对用户产生影响。但是，很多场景是不允许停服的。并且如果表的数据量达到上亿，那么需要停服时间可能需要十几个小时，甚至更长，这是极不现实的；

2、凌晨执行，在用户较少的时间段内，做变更，尽量减少对用户产生影响。但是如果出现锁表的话，万一有用户使用服务，服务将不可用；

3、使用换表，但是缺点是复制数据到新表期间，如果用户在这期间做了update或delete操作，且数据发生在已经复制完成的部分，那么将无法感知到这部分数据，导致丢失掉用户的操作数据，风险太大；

4、使用存储过程，缺点是执行时间会很久，且有可能影响到用户的DDL操作。因为为了防止每次循环修改时，锁住太多数据行，我们需要控制每次更新数据的行数，粒度不能太大，否则很有可能会锁住用户正在操作的数据行。

那么针对以上实际的需求，就没有一个很好的工具，来解决我们的痛点吗？其实在业界中，就有一个比较成熟的工具，针对大表的场景，可以在线进行Alter变更，且不会出现锁表的风险。除此之外，它还有其他的一些优点，让我们开始探索吧。

一、pt-osc是什么

pt-online-schema-change是Percona-toolkit一员，通过改进原生ddl的方式，达到不锁表在线修改表结构的效果。在Percona的官网中，关于pt-osc工具，也特别提到了ALTER表不会出现锁表的特性。

针对上面谈到的避免锁表、感知用户更新删除动作等，ps-osc工具是怎么解决的呢？

pt-osc主要执行步骤如下：

1、创建一个跟原表一模一样的新表，命名方式为'_正式表名_new';

2、使用alter语句将要变更的内容在新创建的新表上做变更，避免了对原表的alter操作；

3、在原表中创建3个触发器，分别是insert、update和delete，主要是用于原表在往新表复制数据时，如果用户有DDL操作，触发器能够将在这期间出现的DDL操作数据也写入到新表中，确保新表的数据是最新的，不会丢失掉用户的新操作数据；

4、按块拷贝数据到新表，拷贝过程对数据行持有S锁；

5、重命名，将原表重命名为老表，命名为“_正式表名_old”，将新表重命名为正式表，可通过配置决定执行完成后是否删除掉老表；

6、删除3个触发器；

二、pt-osc的安装

在linux系统中安装步骤：

  

   

    

     

    
    

     

      --下载安装包
     
    
   

    

     

    
    

     

      wget  http://szxge1-sw.artifactory.cd-cloud-artifact.tools.huawei.com/artifactory/CommonComponent/common/tool/percona-toolkit-3.1.0.tar.gz
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      --解压安装包
     
    
   

    

     

    
    

     

      tar -zxvf percona-toolkit-3.1.0.tar.gz
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      --安装依赖环境
     
    
   

    

     

    
    

     

      yum 
      install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker
     
    
   

    

     

    
    

     

      yum -y 
      install perl-Digest-
      MD5
     
    
   

    

     

    
    

     

      cd percona-toolkit
      -3.1
      .0
     
    
   

    

     

    
    

     

      perl Makefile.PL
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      --编译
     
    
   

    

     

    
    

     

      make
     
    
   

    

     

    
    

     

      make 
      install
     
    
   

    

     

    
    

     

      yum 
      install mariadb
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      --安装Mysql
     
    
   

    

     

    
    

     

      yum 
      install perl-DBD-MySQL
     
    
  

三、pt-osc的使用

pt-osc工具使用起来很简单，直接在linux命令行输入pt-osc格式的命令，即可直接执行。

以Mysql数据库增加一个名字是MARK的字段为例：

pt-online-schema-change --user="root" --password="*****" --host="数据库IP" --port=3306 --alter "ADD COLUMN MARK TINYINT NULL DEFAULT 1 COMMENT 'mark source region is 1';" D=my_test,t=t_test --no-drop-old-table --execute --print --no-check-replication-filters --charset=utf8 --no-check-unique-key-change --max-load="Threads_running=100" --critical-load="Threads_running=300" --recursion-method=none;

在上面的语句中：

1、user和password分别为数据库执行变更操作的用户名、密码，需要高权限；

2、host为数据库的IP地址；

3、port为数据库的端口号；

4、alter后面跟上具体的alter语句；

5、D为database名字；

6、t为要执行变更的表名；

7、no-drop-old-table就是不要删除

8、charset，字符集，使用utf8；

9、max-load，在复制数据时，工具会监控数据库中正在运行的线程数，如果大于配置的Threads_running值，那么会暂停复制，直到小于该值。以此防止对数据库造成较大压力，影响现网业务正常使用；

10、critical-load，默认为50，在每个块之后检查SHOW GLOBAL STATUS，与max-load不同的是，如果负载太高，,直接中止，而不是暂停。可根据自己数据库情况斟酌配置阈值；

注意：在--alter后面跟着的变更语句中，列名不可以加`符号，否则会出现报错。如--alter "ADD COLUMN MARK TINYINT NULL DEFAULT 1 COMMENT 'mark source region is 1';"，MARK字段加了`符号，就会出现错误，COMMENT后面有`符号无影响。

下面是使用pt-osc工具，实际执行一个作业时，打印出来的信息。为了安全起见，部分日志信息做了隐藏忽略。

  

   

    

     

    
    

     

      [root@ttt ~]
      # `pt-online-schema-change --user="root" --password="*****" --host="数据库IP" --port=3306 --alter "ADD COLUMN MARK TINYINT NULL DEFAULT 1 COMMENT 'mark source region is 1';" D=my_test,t=t_test --no-drop-old-table --execute --print --no-check-replication-filters --charset=utf8 --no-check-unique-key-change --max-load="Threads_running=100" --critical-load="Threads_running=300" --recursion-method=none;`
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      No slaves found.  See 
      --recursion-method if host EulerOS-BaseTemplate has slaves.
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      Not checking slave lag because no slaves were found and 
      --check-slave-lag was not specified.
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      Operation, tries, wait:
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      analyze_table, 10, 1
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      copy_rows, 10, 0.25
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      create_triggers, 10, 1
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      drop_triggers, 10, 1
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      swap_tables, 10, 1
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      update_foreign_keys, 10, 1
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      Altering `my_test`.`t_test`...
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      Creating new table...
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      CREATE 
      TABLE 
      `my_test`.
      `_t_test_new` (
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     
      
      `ID` 
      int(
      11) 
      NOT 
      NULL AUTO_INCREMENT 
      COMMENT 
      '递增ID',
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

            .............建表语句数据................
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      Created 
      new 
      table my_test._t_test_new OK.
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      Altering 
      new table...
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      ALTER 
      TABLE 
      `my_test`.
      `_t_test_new` 
      ADD 
      COLUMN MARK 
      TINYINT 
      NULL 
      DEFAULT 
      1 
      COMMENT 
      'mark source region is 1';
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      Altered `my_test`.`_t_test_new` OK.
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      2020-10-14T11:14:48 Creating triggers...
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      2020-10-14T11:14:48 Created triggers OK.
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      2020-10-14T11:14:48 Copying approximately 346697 rows...
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      INSERT 
      LOW_PRIORITY 
      IGNORE 
      INTO 
      `my_test`.
      `_t_test_new` (
      `id`, ..建表语句信息.... 
      FROM 
      `my_test`.
      `_t_test_new` 
      FORCE 
      INDEX(
      `PRIMARY`) 
      WHERE ((
      `id` >= ?)) 
      AND ((
      `id` <= ?)) 
      LOCK 
      IN 
      SHARE 
      MODE 
      /*pt-online-schema-change 31340 copy nibble*/
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      SELECT 
      /*!40001 SQL_NO_CACHE */ 
      `id` 
      FROM 
      `my_test`.
      `t_test` 
      FORCE 
      INDEX(
      `PRIMARY`) 
      WHERE ((
      `id` >= ?)) 
      ORDER 
      BY 
      `id` 
      LIMIT ?, 
      2 
      /*next chunk boundary*/
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      2020
      -10
      -14T11:
      14:
      53 Copied 
      rows OK.
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      2020
      -10
      -14T11:
      14:
      53 Analyzing 
      new table...
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      2020
      -10
      -14T11:
      14:
      53 Swapping tables...
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      RENAME 
      TABLE 
      `my_test`.
      `t_test` 
      TO 
      `my_test`.
      `_t_test_old`, 
      `my_test`.
      `_t_test_new` 
      TO 
      `my_test`.
      `t_test`
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      2020
      -10
      -14T11:
      14:
      53 Swapped original 
      and 
      new 
      tables OK.
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      Not dropping 
      old 
      table because 
      --no-drop-old-table was specified.
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      2020
      -10
      -14T11:
      14:
      53 Dropping triggers...
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      DROP 
      TRIGGER 
      IF 
      EXISTS 
      `my_test`.
      `pt_osc_my_test_t_test_del`
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      DROP 
      TRIGGER 
      IF 
      EXISTS 
      `my_test`.
      `pt_osc_my_test_t_test_upd`
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      DROP 
      TRIGGER 
      IF 
      EXISTS 
      `my_test`.
      `pt_osc_my_test_t_test_ins`
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      2020
      -10
      -14T11:
      14:
      54 Dropped 
      triggers OK.
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      Successfully altered 
      `my_test`.
      `t_test`.
     
    
  

四、性能对比

前面介绍了很多pt-osc的优点，以及良好的特性。那么实际使用效果到底怎么样呢？在测试环境中，专门做了一个测试，让大家有更加直观的感受。

在测试库中，准备了一张1600万数据的大表，目标为对大表添加一个字段，分别使用存储过程和pt-osc工具，进行测试。

4.1 使用存储过程

首先使用存储过程做测试，为防止锁表，每次只更新200行。整个变更从开始到完成，需要耗费90分钟。其实，存储过程在执行过程中，如果恰好用户也在DDL操作存储过程正在变更的数据行，还有可能会锁住用户的数据，导致用户不能变更成功。

4.2 使用pt-osc工具

pt-osc从开始执行到变更完成，耗时7分钟左右，速度非常快。在执行的过程中，测试环境的服务连接到该数据库，并执行多个会操作该表的任务，整个过程中，任务能够正常执行，未出现异常情况。

5、结语

ps-osc的上述优点，在现网环境的不停服等要求下，能够优雅地帮助我们实施变更，且保证在变更期间，数据库不会受到锁表、过载等的影响，进而保证了业务能够正常运转。

本文分享自华为云社区《千万级、亿级大表在线不锁表变更字段与索引》，原文作者：active_zhao 。

点击关注，第一时间了解华为云新鲜技术~