欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

MySQL的Online DDL语句

程序员文章站 2022-04-09 20:42:53
...

微信搜索“coder-home”或扫一扫下面的二维码,
关注公众号,第一时间了解更多干货分享,还有各类****资源。扫描它,带走我

MySQL的Online DDL语句


MySQL的Online DDL语句

概念

什么是Online DDL?在了解Online DDL之前,我们先来了解一下什么是DDL
说到DDL,就不得不提一下它的另外两个兄弟:DMLDCL。他们三者的区别如下:

  • DDL:data definition language,数据定义语言,用来定义数据的语句。例如我们平时的create table,create index,create function等。
  • DML:data manipulation language,数据修改语言,用来操作数据的语句。例如我们平时的select,insert,update,delete语句。
  • DCL:data control language,数据控制语言,用来定义数据访问控制的语句。例如我们平时的grant,revoke等。

Online DDL是指在线的数据定义语言,所谓的在线是指:在数据表在不停止服务的情况下来应用我们的DDL语句,即表在应用DDL语句的时候,仍然可以提供读写的服务。


诉求背景

我们平时所说的在线的DDL操作一般情况是指以下操作:

  • 在表中增加字段
  • 在表中增加索引
  • 在表中修改字段类型
  • 删除表中的字段
  • 删除表中的索引

如果是一张小表,只有几百行或者几千行的数据表,我们要增加字段或增加索引,基本不用考虑什么,一个字:干,就完了。

但如果是一个大表呢?表中几千万,上亿条数据。怎么办?

我们之所以不敢直接干的原因,大概是担心以下问题的发生,这也是我们经常锁的MySQL的online DDL要格外注意的原因。

  • 表被锁上,不能进行正常的读写或者写。
  • 不能写还好一下,最怕是连读都不能读。
  • 如果有主从复制架构,担心主从同步出现大幅度的延迟。

解决方法

原生支持的方式

在5.5之前的版本,这些都是我们比较担心的问题。但是,在MySQL5.6和5.7版本中,默认情况下,MySQL就是支持online的DDL操作的,在online的DDL语句执行的过程中,MySQL会尽量少使用锁的限制,我们不需要特殊的操作来启用它。

MySQL在选择的时候,尽量少使用锁,但是不排除它会选择使用锁。而如果我担心它选择了锁而导致我们的表不能读也不能写,显然这不是我们想要的结果,我们希望:如果选择了锁就不要执行,直接退出执行;如果没有选择锁就执行。想要达到我们希望的这个效果,该怎么做呢?

可以在执行我们的online DDL语句的时候,使用ALGORITHMLOCK关键字,这两个关键字在我们的DDL语句的最后面,用逗号隔开即可。
示例如下:

ALTER TABLE tbl_name ADD COLUMN col_name col_type, ALGORITHM=INPLACE, LOCK=NONE;
  1. 其中的ALGORITHM有如下选项
    • INPLACE:替换:直接在原表上面执行DDL的操作。
    • COPY:复制:使用一种临时表的方式,克隆出一个临时表,在临时表上执行DDL,然后再把数据导入到临时表中,在重命名等。这期间需要多出一倍的磁盘空间来支撑这样的 操作。执行期间,表不允许DML的操作。
    • DEFAULT:默认方式,有MySQL自己选择,优先使用INPLACE的方式。
  2. 其中的LOCK有如下选项
    • SHARE:共享锁,执行DDL的表可以读,但是不可以写。
    • NONE:没有任何限制,执行DDL的表可读可写。
    • EXCLUSIVE:排它锁,执行DDL的表不可以读,也不可以写。
    • DEFAULT:默认值,也就是在DDL语句中不指定LOCK子句的时候使用的默认值。如果指定LOCK的值为DEFAULT,那就是交给MySQL子句去觉得锁还是不锁表。不建议使用,如果你确定你的DDL语句不会锁表,你可以不指定lock或者指定它的值为default,否则建议指定它的锁类型。

执行DDL操作时,ALGORITHM选项可以不指定,这时候MySQL按照INSTANT、INPLACE、COPY的顺序自动选择合适的模式。也可以指定ALGORITHM=DEFAULT,也是同样的效果。如果指定了ALGORITHM选项,但不支持的话,会直接报错。

注意:
在执行Online DDL之前,要在非业务高峰期去执行,并要确认待执行的表上面没有未提交的事务、锁等信息。可以通过如下的SQL语句查看是否有事务和锁等信息。

select * from information_schema.innodb_locks;
select * from information_schema.innodb_trx;
select * from information_schema.innodb_lock_waits;
select * from information_schema.processlist;

使用第三方工具

首选,推荐使用这种方式,因为这种工具已经很早大家都在用,效果还不错,不易出现问题。

第三方工具:pt-online-schema-change,Percona工具PT-OSC(Percona Toolkit Online Schema Change),那么这个工具是做什么用的?它是何方神圣?

看下官方对pt-online-schema-change的解释:ALTER tables without locking them,在不锁定表的情况下修改它。言外之意就是在修改表的时候,不对表增加任何锁。表在被修改期间任然可对外提供读写。

特别适合对一些特别大的表进行修改的时候,这个需改的操作可能比较耗时,需要几分钟甚至几十分钟才能修改完成,而在此期间,如果这个表因为修改了表结构或增加索引而被锁住,不能提供正常的读写,这对于我们的生产环境是不能接受的。
此时pt-online-schema-change工具就可以帮我们解除这个烦恼。使得我们在修改表结构的同时,这个被修改的表仍然可以对外提供正常的读写功能。

下载安装Percona Toolkit
下载二进制压缩包

下载的时候,可以选择各种格式的安装文件,我们这里使用下载编译好的二进制压缩包,解压后即可直接使用里面的命令。

[aaa@qq.com ~]$ wget https://www.percona.com/downloads/percona-toolkit/3.2.1/binary/tarball/percona-toolkit-3.2.1_x86_64.tar.gz
--2020-11-09 11:06:56--  https://www.percona.com/downloads/percona-toolkit/3.2.1/binary/tarball/percona-toolkit-3.2.1_x86_64.tar.gz
Resolving www.percona.com (www.percona.com)... 74.121.199.234
Connecting to www.percona.com (www.percona.com)|74.121.199.234|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 15502943 (15M) [application/x-gzip]
Saving to: ‘percona-toolkit-3.2.1_x86_64.tar.gz’

100%[======================================================================================================================================================================================================================================>] 15,502,943  2.56MB/s   in 6.9s

2020-11-09 11:07:03 (2.16 MB/s) - ‘percona-toolkit-3.2.1_x86_64.tar.gz’ saved [15502943/15502943]

[aaa@qq.com ~]$ ls -lstr percona-toolkit*
15140 -rw-rw-r-- 1 sysop sysop 15502943 Aug 13 05:06 percona-toolkit-3.2.1_x86_64.tar.gz
[aaa@qq.com ~]$

解压下载后的安装包即可。

  • 解压安装示例:
[aaa@qq.com ~]$ tar -zxvf percona-toolkit-3.2.1_x86_64.tar.gz
percona-toolkit-3.2.1/
percona-toolkit-3.2.1/run-tests.sh
percona-toolkit-3.2.1/INSTALL
percona-toolkit-3.2.1/runtests.sh
percona-toolkit-3.2.1/docker-compose.yml
percona-toolkit-3.2.1/CONTRIBUTE.md
percona-toolkit-3.2.1/Changelog
percona-toolkit-3.2.1/Gopkg.toml
percona-toolkit-3.2.1/README.md
percona-toolkit-3.2.1/COPYING
percona-toolkit-3.2.1/Makefile.PL
percona-toolkit-3.2.1/bin/
percona-toolkit-3.2.1/bin/pt-kill
percona-toolkit-3.2.1/bin/pt-fk-error-logger
percona-toolkit-3.2.1/bin/pt-diskstats
percona-toolkit-3.2.1/bin/pt-mongodb-summary
percona-toolkit-3.2.1/bin/pt-table-sync
percona-toolkit-3.2.1/bin/pt-fifo-split
percona-toolkit-3.2.1/bin/pt-pg-summary
percona-toolkit-3.2.1/bin/pt-stalk
percona-toolkit-3.2.1/bin/pt-online-schema-change
percona-toolkit-3.2.1/bin/pt-mysql-summary
percona-toolkit-3.2.1/bin/pt-slave-restart
percona-toolkit-3.2.1/bin/pt-table-checksum
percona-toolkit-3.2.1/bin/pt-secure-collect
percona-toolkit-3.2.1/bin/pt-variable-advisor
percona-toolkit-3.2.1/bin/pt-show-grants
percona-toolkit-3.2.1/bin/pt-fingerprint
percona-toolkit-3.2.1/bin/pt-visual-explain
percona-toolkit-3.2.1/bin/pt-archiver
percona-toolkit-3.2.1/bin/pt-find
percona-toolkit-3.2.1/bin/pt-mongodb-query-digest
percona-toolkit-3.2.1/bin/pt-mext
percona-toolkit-3.2.1/bin/pt-summary
percona-toolkit-3.2.1/bin/pt-upgrade
percona-toolkit-3.2.1/bin/pt-pmp
percona-toolkit-3.2.1/bin/pt-duplicate-key-checker
percona-toolkit-3.2.1/bin/pt-ioprofile
percona-toolkit-3.2.1/bin/pt-align
percona-toolkit-3.2.1/bin/pt-config-diff
percona-toolkit-3.2.1/bin/pt-table-usage
percona-toolkit-3.2.1/bin/pt-slave-find
percona-toolkit-3.2.1/bin/pt-index-usage
percona-toolkit-3.2.1/bin/pt-query-digest
percona-toolkit-3.2.1/bin/pt-slave-delay
percona-toolkit-3.2.1/bin/pt-sift
percona-toolkit-3.2.1/bin/pt-deadlock-logger
percona-toolkit-3.2.1/bin/pt-heartbeat
percona-toolkit-3.2.1/CONTRIBUTING.md
percona-toolkit-3.2.1/docs/
percona-toolkit-3.2.1/docs/percona-toolkit.pod
percona-toolkit-3.2.1/lib/
percona-toolkit-3.2.1/MANIFEST
percona-toolkit-3.2.1/Gopkg.lock
[aaa@qq.com ~]$ 
[aaa@qq.com ~]$ cd percona-toolkit-3.2.1/
[aaa@qq.com percona-toolkit-3.2.1]$ ll -lstr
total 144
 4 -rwxrwxr-x 1 sysop sysop   320 Aug 13 05:06 runtests.sh
 4 -rwxrwxr-x 1 sysop sysop   354 Aug 13 05:06 run-tests.sh
 4 -rw-rw-r-- 1 sysop sysop  1842 Aug 13 05:06 README.md
 4 -rw-rw-r-- 1 sysop sysop   852 Aug 13 05:06 MANIFEST
 4 -rw-rw-r-- 1 sysop sysop   567 Aug 13 05:06 Makefile.PL
 0 drwxrwxr-x 2 sysop sysop     6 Aug 13 05:06 lib
 4 -rw-rw-r-- 1 sysop sysop  1527 Aug 13 05:06 INSTALL
 4 -rw-rw-r-- 1 sysop sysop   523 Aug 13 05:06 Gopkg.toml
12 -rw-rw-r-- 1 sysop sysop 10803 Aug 13 05:06 Gopkg.lock
12 -rw-rw-r-- 1 sysop sysop  9242 Aug 13 05:06 docker-compose.yml
20 -rw-rw-r-- 1 sysop sysop 18092 Aug 13 05:06 COPYING
 4 -rw-rw-r-- 1 sysop sysop   224 Aug 13 05:06 CONTRIBUTING.md
 8 -rw-rw-r-- 1 sysop sysop  7142 Aug 13 05:06 CONTRIBUTE.md
56 -rw-rw-r-- 1 sysop sysop 54987 Aug 13 05:06 Changelog
 0 drwxrwxr-x 2 sysop sysop    33 Aug 13 05:06 docs
 4 drwxrwxr-x 2 sysop sysop  4096 Aug 13 05:06 bin
[aaa@qq.com percona-toolkit-3.2.1]$

解压后,在percona-toolkit-3.2.1/bin目录下面就是所有的可以执行的命令文件,这里很多中可执行的二进制文件,我们主要使用的是pt-online-schema-change这个命令。如下所示:

[aaa@qq.com bin]$ pwd
/home/sysop/percona-toolkit-3.2.1/bin
[aaa@qq.com bin]$ ll -lstr
total 42720
  108 -rwxrwxr-x 1 sysop sysop   108113 Aug 13 05:06 pt-mysql-summary
12496 -rwxrwxr-x 1 sysop sysop 12795904 Aug 13 05:06 pt-mongodb-summary
12064 -rwxrwxr-x 1 sysop sysop 12353536 Aug 13 05:06 pt-mongodb-query-digest
   24 -rwxrwxr-x 1 sysop sysop    21913 Aug 13 05:06 pt-mext
  252 -rwxrwxr-x 1 sysop sysop   257732 Aug 13 05:06 pt-kill
   32 -rwxrwxr-x 1 sysop sysop    32405 Aug 13 05:06 pt-ioprofile
  224 -rwxrwxr-x 1 sysop sysop   229128 Aug 13 05:06 pt-index-usage
  224 -rwxrwxr-x 1 sysop sysop   225493 Aug 13 05:06 pt-heartbeat
  136 -rwxrwxr-x 1 sysop sysop   135870 Aug 13 05:06 pt-fk-error-logger
   68 -rwxrwxr-x 1 sysop sysop    67304 Aug 13 05:06 pt-fingerprint
  152 -rwxrwxr-x 1 sysop sysop   152724 Aug 13 05:06 pt-find
   52 -rwxrwxr-x 1 sysop sysop    50157 Aug 13 05:06 pt-fifo-split
  168 -rwxrwxr-x 1 sysop sysop   172014 Aug 13 05:06 pt-duplicate-key-checker
  164 -rwxrwxr-x 1 sysop sysop   166450 Aug 13 05:06 pt-diskstats
  168 -rwxrwxr-x 1 sysop sysop   168891 Aug 13 05:06 pt-deadlock-logger
  168 -rwxrwxr-x 1 sysop sysop   171696 Aug 13 05:06 pt-config-diff
  268 -rwxrwxr-x 1 sysop sysop   272656 Aug 13 05:06 pt-archiver
   44 -rwxrwxr-x 1 sysop sysop    41747 Aug 13 05:06 pt-align
  104 -rwxrwxr-x 1 sysop sysop   103460 Aug 13 05:06 pt-visual-explain
  176 -rwxrwxr-x 1 sysop sysop   179330 Aug 13 05:06 pt-variable-advisor
  328 -rwxrwxr-x 1 sysop sysop   334033 Aug 13 05:06 pt-upgrade
  244 -rwxrwxr-x 1 sysop sysop   248658 Aug 13 05:06 pt-table-usage
  400 -rwxrwxr-x 1 sysop sysop   406401 Aug 13 05:06 pt-table-sync
  452 -rwxrwxr-x 1 sysop sysop   461937 Aug 13 05:06 pt-table-checksum
   92 -rwxrwxr-x 1 sysop sysop    90816 Aug 13 05:06 pt-summary
   76 -rwxrwxr-x 1 sysop sysop    76186 Aug 13 05:06 pt-stalk
  184 -rwxrwxr-x 1 sysop sysop   186580 Aug 13 05:06 pt-slave-restart
  132 -rwxrwxr-x 1 sysop sysop   133040 Aug 13 05:06 pt-slave-find
  148 -rwxrwxr-x 1 sysop sysop   147869 Aug 13 05:06 pt-slave-delay
   40 -rwxrwxr-x 1 sysop sysop    37784 Aug 13 05:06 pt-sift
   80 -rwxrwxr-x 1 sysop sysop    79159 Aug 13 05:06 pt-show-grants
 5148 -rwxrwxr-x 1 sysop sysop  5271552 Aug 13 05:06 pt-secure-collect
  520 -rwxrwxr-x 1 sysop sysop   529350 Aug 13 05:06 pt-query-digest
   28 -rwxrwxr-x 1 sysop sysop    24598 Aug 13 05:06 pt-pmp
 7328 -rwxrwxr-x 1 sysop sysop  7503872 Aug 13 05:06 pt-pg-summary
  428 -rwxrwxr-x 1 sysop sysop   434678 Aug 13 05:06 pt-online-schema-change
[aaa@qq.com bin]$
使用yum来安装

除了上面下载方式,还可以下载源码包,然后自己在服务器上面编译安装。这里就不介绍编译安装了。
当然也可以现在rpm安装包,使用sudo yum install -y xxx的方式来安装。下面我们使用yum来安装。

  • 下载安装命令:
sudo yum install -y https://www.percona.com/downloads/percona-toolkit/3.2.1/binary/redhat/8/x86_64/percona-toolkit-3.2.1-1.el8.x86_64.rpm

注意,执行上面命令的用户需要有sudo的权限


  • yum命令安装示例:
[aaa@qq.com bin]$ sudo yum install -y https://www.percona.com/downloads/percona-toolkit/3.2.1/binary/redhat/8/x86_64/percona-toolkit-3.2.1-1.el8.x86_64.rpm
Loaded plugins: fastestmirror, langpacks, priorities
percona-toolkit-3.2.1-1.el8.x86_64.rpm                                                                                                                                                                                                                   |  14 MB  00:00:11
Examining /var/tmp/yum-root-Mjkep0/percona-toolkit-3.2.1-1.el8.x86_64.rpm: percona-toolkit-3.2.1-1.el8.x86_64
Marking /var/tmp/yum-root-Mjkep0/percona-toolkit-3.2.1-1.el8.x86_64.rpm as an update to percona-toolkit-3.2.1-1.el7.x86_64
Resolving Dependencies
--> Running transaction check
---> Package percona-toolkit.x86_64 0:3.2.1-1.el7 will be updated
---> Package percona-toolkit.x86_64 0:3.2.1-1.el8 will be an update
--> Finished Dependency Resolution
Ceph/x86_64                                                                                                                                                                                                                                              | 2.9 kB  00:00:00
Ceph-noarch                                                                                                                                                                                                                                              | 2.9 kB  00:00:00
base/7/x86_64                                                                                                                                                                                                                                            | 3.1 kB  00:00:00
base/7/x86_64/primary_db                                                                                                                                                                                                                                 | 6.1 MB  00:00:00
ceph-source                                                                                                                                                                                                                                              | 2.9 kB  00:00:00
epel/x86_64/metalink                                                                                                                                                                                                                                     | 8.4 kB  00:00:00
epel/x86_64                                                                                                                                                                                                                                              | 4.7 kB  00:00:00
epel/x86_64/updateinfo                                                                                                                                                                                                                                   | 1.0 MB  00:00:00
epel/x86_64/primary_db                                                                                                                                                                                                                                   | 6.9 MB  00:00:08
extras/7/x86_64                                                                                                                                                                                                                                          | 2.5 kB  00:00:00
extras/7/x86_64/primary_db                                                                                                                                                                                                                               | 206 kB  00:00:00
mysql-connectors-community/x86_64                                                                                                                                                                                                                        | 2.6 kB  00:00:00
mysql-connectors-community/x86_64/primary_db                                                                                                                                                                                                             |  68 kB  00:00:00
mysql-tools-community/x86_64                                                                                                                                                                                                                             | 2.6 kB  00:00:00
mysql-tools-community/x86_64/primary_db                                                                                                                                                                                                                  |  83 kB  00:00:00
mysql57-community/x86_64                                                                                                                                                                                                                                 | 2.6 kB  00:00:00
mysql57-community/x86_64/primary_db                                                                                                                                                                                                                      | 247 kB  00:00:00
openlogic/7/x86_64                                                                                                                                                                                                                                       | 2.9 kB  00:00:00
percona-release-noarch/7                                                                                                                                                                                                                                 | 2.9 kB  00:00:00
percona-release-x86_64/7                                                                                                                                                                                                                                 | 2.9 kB  00:00:00
percona-release-x86_64/7/primary_db                                                                                                                                                                                                                      | 1.1 MB  00:00:01
prel-release-noarch/7                                                                                                                                                                                                                                    | 2.9 kB  00:00:00
updates/7/x86_64                                                                                                                                                                                                                                         | 2.6 kB  00:00:00
updates/7/x86_64/primary_db                                                                                                                                                                                                                              | 4.5 MB  00:00:00
zabbix/x86_64                                                                                                                                                                                                                                            | 2.9 kB  00:00:00
zabbix-non-supported/x86_64                                                                                                                                                                                                                              |  951 B  00:00:00

Dependencies Resolved

================================================================================================================================================================================================================================================================================
 Package                                                         Arch                                                   Version                                                       Repository                                                                           Size
================================================================================================================================================================================================================================================================================
Updating:
 percona-toolkit                                                 x86_64                                                 3.2.1-1.el8                                                   /percona-toolkit-3.2.1-1.el8.x86_64                                                  44 M

Transaction Summary
================================================================================================================================================================================================================================================================================
Upgrade  1 Package

Total size: 44 M
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Updating   : percona-toolkit-3.2.1-1.el8.x86_64                                                                                                                                                                                                                           1/2
  Cleanup    : percona-toolkit-3.2.1-1.el7.x86_64                                                                                                                                                                                                                           2/2
  Verifying  : percona-toolkit-3.2.1-1.el8.x86_64                                                                                                                                                                                                                           1/2
  Verifying  : percona-toolkit-3.2.1-1.el7.x86_64                                                                                                                                                                                                                           2/2

Updated:
  percona-toolkit.x86_64 0:3.2.1-1.el8

Complete!
[aaa@qq.com bin]$
使用Percona Toolkit

Percona Toolkit里面有很多命令,它们各司其职来帮助我们管理维护MySQL数据库。我们下面只选择我们将要使用的pt-online-schema-change命令来示例。

它的使用方式很简单,大概使用如下的命令结构

pt-online-schema-change [OPTIONS] DSN
  • 其中的DSN用来指明要修改的数据库和表名称等各种连接信息。例如:D=my_db,t=my_tab表示要修改my_db数据库下面的my_tab表。他们的值使用使用key=value的方式给出,多个参数之间使用英文状态的逗号连接。使用k1=v1,k2=v2,k3=v3,...,kn=vn这样的方式给出。具体参数列表有如下几
    • h:主机名称hostname
    • u:表示用户
    • P:端口号
    • p:密码
    • D:数据库名称
    • t:表示表名称
    • A:编码格式
    • S:socket文件名称
  • 其中的OPTIONS参数,参考下面的表格中给出的具体含义。

在使用该命令之前,我们先来了解一下各个参数的含义,我们主要列出几个经常使用的参数,这里不把所有的参数一一列出。如果想进一步了解参数信息,可以参考如下链接:
https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html
或者使用pt-online-schema-change --help的命令查看帮助信息。

序号 参数名称 默认值 解释
1 –user或-u 登录MySQL用户名,并用该用户名称去执行具体的DDL语句
2 –password或-p 登录MySQL用户密码
3 –ask-pass 执行命令的时候,弹出需要输入密码的终端,而不是把密码直接在命令行中以明文的方式贴出来。它和–password用任何一个都可以。
4 –port或-P MySQL服务的端口号
5 –host或-h MySQL服务所在的服务器IP地址
6 –alter 待执行的DDL语句,不需要包含alter table关键字,并且不能使用符号“`”来包裹字段名称。
7 –charset或-A 编码,连接到数据库的时候使用的编码,建议表和数据库是什么编码这里就指定对应点编码。
8 –print 打印出执行的SQL语句
9 –execute 显示的声明要执行DDL语句
10 –recursion-method 获取slave节点信息的方式。它的值可以为processlisthostsdsn=DSNnone,分别表示使用show processlist方式查找、使用hosts方式来查找从库、使用表格 tdsn存储从库信息、不查找从库。适用于有主从复制的集群环境的执行DDL语句。
11 –max-lag 1s 可以指定为m、h、d单位的数据,分别表示分钟、小时、天。该参数指定了主从延迟的最大阈值,当主从延迟的时间Seconds_Behind_Master超过该阈值后,则停止DDL语句引起的复制数据的动作,休息--check-interval参数指定的时间段之后,再次检查Seconds_Behind_Master的值是否小于该阈值,如果小于阈值,则继续拷贝数据,否则继续休息--check-interval参数所指定的时间后再次检查。该参数一般和--check-interval配合使用,适用于有主从复制的集群环境的执行DDL语句。
12 –check-interval 1s 可以指定为m、h、d单位的数据,分别表示分钟、小时、天。该参数指定了当发现主从延迟的时间大于--max-lag参数所设置的阈值后,copy数据暂停的时间间隔。适用于有主从复制的集群环境的执行DDL语句。

使用示例:

如意:如下使用示例是考虑到有主从同步的集群环境,所以在OPTIONS参数中使用了--recursion-method--check-interval--max-lag。如果没有主从同步的环境,那么就可以不考虑这3个参数。
另外user需要有远程连接到slave节点的权限,否则会出现Cannot connect to A=utf8,h=xxxx,p=...,u=xxxx的错误。原因就是当前指定的user,不能远程连接到slave节点。

  • 在某一个表上增加列,多个列逗号隔开即可。
pt-online-schema-change \
--user=dev \
--password="dev" \
--alter " add column taste_type_id varchar(100) DEFAULT NULL COMMENT '品味标签id' AFTER standard_code, add column taste_name varchar(100) DEFAULT NULL COMMENT '品味标签名称' AFTER taste_type_id " \
--recursion-method=processlist \
--check-interval=5s \
--max-lag=5s \
--charset=utf8mb4 \
--print \
--execute \
D=demo_db,t=list_detail

  • 在某一个表上增加索引
pt-online-schema-change \
--user=dev \
--password="dev" \
--alter " add index idx_taste_type_id (taste_type_id) " \
--recursion-method=processlist \
--check-interval=5s \
--max-lag=5s \
--charset=utf8mb4 \
--print \
--execute \
D=demo_db,t=list_detail

  • 在就一个表上删除列
pt-online-schema-change \
--user=dev \
--password="dev" \
--alter " drop column taste_type_id " \
--recursion-method=processlist \
--check-interval=5s \
--max-lag=5s \
--charset=utf8mb4 \
--print \
--execute \
D=demo_db,t=list_detail

  • 在某一个表上删除索引
pt-online-schema-change \
--user=dev \
--password="dev" \
--alter " drop index idx_taste_type_id " \
--recursion-method=processlist \
--check-interval=5s \
--max-lag=5s \
--charset=utf8mb4 \
--print \
--execute \
D=demo_db,t=list_detail

冗余字段

设计表的时候,配置冗余字段。

说道这个,想去刚参加工作的时候,看到数据库表几乎每张表的最后都有几个col1,col2,col3,col4,col5,col6之类的字段名称,并且字段类型各不相同。

当初我看到这个我心想,这是谁设计的表?怎么弄这么多没有任何意义的字段放在这里干嘛?后来请教了有经验的同事才明白这么做的目的是什么。这个有一个俗语叫做:冗余字段设计。为的就是以后业务变更或者扩展的时候,需要增加表中的字段。如果有了这个冗余的字段设计,直接表表名称修改一下就可以了。

而这个表名称修改的DDL语句,不会导致我们的数据库表被锁或hang住。因为这个属于是数据库中元数据的更该,不设计到数据库表中行的具体变动。

所以说,数据库表冗余的设计有时候可以在业务变更的时候帮助到我们,特别是一些大表,我们预计以后这个表会增长的很快的表,就适合做冗余字段的设计。

试想一下:你的业务要求在大表上增加一个字段,而你已经在之前做了冗余设计,只要找到对应冗余的表字段和类型,修改一下字段名称就可以满足你的需求了,并且也不用考虑
我们上面提到的Online DDL需要注意的事项。


主从延迟的规避

前面提到我们在做DDL语句修改的时候,也担心出现主从延迟的问题。那么如何避免这样的问题发生呢?

禁止记录bin-log,把DDL语句在主从上分别执行。这个动作仅能避免主从延迟的问题,但是不能解决在执行DDL语句的时候,避免锁表现象的发生。所以,还得需要使用ALGORITHMLOCK关键字来避免DDL语句运行期间,表不能读写的问题。

  • 示例:
set sql_log_bin=off;
alter table mytab add colmn mycol varchar(32) after id;

我们可以使用下面的这样一个测试的方式,来预测我们的DDL是否会导致严重的组从延迟。注意,实验的数据量不要太大,否则就会直接导致主从延迟现象的发生了。

在真正的执行DDL语句之前,可以先创建一个临时表,这个临时表的结构和你要操作的表结构相同,包括索引等信息,在这个临时表中也要存在。然后,想这个临时表中插入少量的数据。把我们想要执行的online DDL语句,稍微修改一下,然后在这个临时表中先运行一下,看下效果是什么样子的,应该会有如下3种情况。

  1. Query OK, 1671168 rows affected (1 min 35.54 sec):这样的语句暑促:执行速度明显慢很多,并且表中的数据被重新构建。
  2. Query OK, 0 rows affected (21.42 sec):执行速度有点慢,但是没有copy表中的数据到临时表中,也就是没有用到临时表。
  3. Query OK, 0 rows affected (0.07 sec):执行速度很快,没有影响到表中已经存在的数据。

上述的3种情况中,第一种是最不理想的,最后一种是最理想的。
如果是上诉情况第1种情况,我们就需要好好的衡量一下我们的online DDL语句,指定一个可行的执行计划,避免主从不同步的问题发现。此时可以考虑在session级别关闭binlog日志的记录功能,然后在每一个数据库节点上都执行一遍DDL语句,避免用binlog去做数据同步而产生的主从延迟。


微信搜索“coder-home”或扫一扫下面的二维码,
关注公众号,第一时间了解更多干货分享,还有各类****资源。扫描它,带走我

MySQL的Online DDL语句


相关标签: MySQL mysql ddl