MySQL的Online DDL语句
微信搜索“coder-home”或扫一扫下面的二维码,
关注公众号,第一时间了解更多干货分享,还有各类****资源。扫描它,带走我
文章目录
MySQL的Online DDL语句
概念
什么是Online DDL
?在了解Online DDL
之前,我们先来了解一下什么是DDL
。
说到DDL
,就不得不提一下它的另外两个兄弟:DML
和DCL
。他们三者的区别如下:
- 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语句的时候,使用ALGORITHM
和LOCK
关键字,这两个关键字在我们的DDL语句的最后面,用逗号隔开即可。
示例如下:
ALTER TABLE tbl_name ADD COLUMN col_name col_type, ALGORITHM=INPLACE, LOCK=NONE;
- 其中的
ALGORITHM
有如下选项- INPLACE:替换:直接在原表上面执行DDL的操作。
- COPY:复制:使用一种临时表的方式,克隆出一个临时表,在临时表上执行DDL,然后再把数据导入到临时表中,在重命名等。这期间需要多出一倍的磁盘空间来支撑这样的 操作。执行期间,表不允许DML的操作。
- DEFAULT:默认方式,有MySQL自己选择,优先使用INPLACE的方式。
- 其中的
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
下载二进制压缩包
下载的时候,可以选择各种格式的安装文件,我们这里使用下载编译好的二进制压缩包,解压后即可直接使用里面的命令。
-
下载地址:
https://www.percona.com/downloads/percona-toolkit/LATEST/ -
如下图所示:
-
下载示例,使用
wget
命令下载后的文件如下:
[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 | 打印出执行的SQL语句 | ||
9 | –execute | 显示的声明要执行DDL语句 | |
10 | –recursion-method | 获取slave节点信息的方式。它的值可以为processlist 、hosts 、dsn=DSN 、none ,分别表示使用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
语句的时候,避免锁表现象的发生。所以,还得需要使用ALGORITHM
和LOCK
关键字来避免DDL
语句运行期间,表不能读写的问题。
- 示例:
set sql_log_bin=off;
alter table mytab add colmn mycol varchar(32) after id;
我们可以使用下面的这样一个测试的方式,来预测我们的DDL
是否会导致严重的组从延迟。注意,实验的数据量不要太大,否则就会直接导致主从延迟现象的发生了。
在真正的执行DDL
语句之前,可以先创建一个临时表,这个临时表的结构和你要操作的表结构相同,包括索引等信息,在这个临时表中也要存在。然后,想这个临时表中插入少量的数据。把我们想要执行的online DDL
语句,稍微修改一下,然后在这个临时表中先运行一下,看下效果是什么样子的,应该会有如下3种情况。
-
Query OK, 1671168 rows affected (1 min 35.54 sec)
:这样的语句暑促:执行速度明显慢很多,并且表中的数据被重新构建。 -
Query OK, 0 rows affected (21.42 sec)
:执行速度有点慢,但是没有copy表中的数据到临时表中,也就是没有用到临时表。 -
Query OK, 0 rows affected (0.07 sec)
:执行速度很快,没有影响到表中已经存在的数据。
上述的3种情况中,第一种是最不理想的,最后一种是最理想的。
如果是上诉情况第1种情况,我们就需要好好的衡量一下我们的online DDL
语句,指定一个可行的执行计划,避免主从不同步的问题发现。此时可以考虑在session
级别关闭binlog
日志的记录功能,然后在每一个数据库节点上都执行一遍DDL
语句,避免用binlog
去做数据同步而产生的主从延迟。
微信搜索“coder-home”或扫一扫下面的二维码,
关注公众号,第一时间了解更多干货分享,还有各类****资源。扫描它,带走我