online ddl
ddl不是对某一个数据行进行操作,是对整个表进行操作,或者整个表的所有的行、列。
ddl往往意味着很多坑。坑往往是灾难性的。
- 1.整个表的操作
- 2.ddl往往意味着会短暂的加上 exclusive锁(排他锁)或shared锁
- 3.产生海量的IO、长时间的锁
如何对ddl风险测试?
- 1.要意识到这是一个ddl
- 2.确认这个ddl是否会产生大量的IO。
- 在测试系统里面,建立一张大表,执行ddl。观察时间、iostat。如果时间长、合并读或者合并写非常大,则说明产生了很大的ddl操作。
alter table t1 add column d6 varchar(10),algorithm in place,lock=none
#给表t1加一个列
DDL的两种方式:
- ①inplace方式:(建议)。在表上加列的时候,分裂出来,在原来的位置上操作,搁不下时,再加数据块。
- ②copy方式:新建另一个表同时增加列,然后把原来所有的数据读出来,插入到新表。(意味着海量的io)
lock意味着:
- ①lock=none表示对表不加锁。
- ②shared 共享锁
- ③exclusive 排他锁
online中lock=none并不是完全的none!在增量应用期间,也会产生shared或者exclusive锁!
mysql5.5以前,
shared:建索引会触发;
exclusive:添加列,删除列会触发;
即:
- 5.5版本不支持online ddl!
- 5.6版本开始支持在线dll;lock可以等于none
- 5.7继续支持。
(注:oracle不支持在线ddl)
online ddl小结:
- ①尽量in place
- ②尽量lock=none
- ③对于ddl期间产生海量io的ddl操作来说,即使支持online dll,生产期间尽量不要做!
开发经常会对表进行ddl,如何规范ddl操作,写出一个规范的流程。
- ①提交ddl需求给DBA。限制开发人员对数据库的权限(grant)。
- ②DBA测试:io测试、时间测试、是否支持in place和none,显式的加上lock none
- ③DBA形成操作规范和风向评估报告,走流程审批。
- ④进行相关的操作。
分区表
将一个大表分成多个分区,对一个分区进行ddl,便于管理。
分区表四大好处:
- ①还是一个表名字,不影响SQL
- ②每一个分区都有自己的名字,可以独立于各分区进行操作
- ③便于数据的归档(把对应的分区置换出去)
- ④提升性能
分区类型:范围分区、列表分区、哈希分区、key分区、子分区等
(一)范围分区
- ①指定分区列;
- ②指定分区范围;
- ③分区范围是小于;
- ④最好指定maxvalue列;
- ⑤最长使用的列:数字、日期
例子1:以年和天分区的表,且带上maxvalue:
create table employeed (
id int not null,
fname varchar(30),
lname varchar(30),
hired date not null default '1970-01-01',
separated date not null default '9999-12-31',
job_code int,
store_id int
)
partition by range (year(separated)) (
parttion p0 values less than (1991), #小于