linux mysql 维护_MySQL的DDL维护技巧

MySQL的ddl维护技巧

mysql在做表的ddl维护时(如添加字段),是要锁表的,不像Oracle那样只更新字典表、巨快,这是因为mysql采用中间表的方式来实现

ddl操作流程:A.对表加锁(表此时只读)

B.复制原表物理结构

C.修改表的物理结构

D.把原表数据导入中间表中,数据同步完后,锁定中间表,并删除原表

E.rename中间表为原表

F.刷新数据字典,并释放锁

通过上面的过程可以了解到,表的数据量越大,这个ddl维护过程越耗时间(锁表时间越长)。

表ddl维护方法1.直接alter table操作eg:

alter table tt4 add column age2 tinyint unsigned  not null  default 0 ;

alter table tt4 modify column age2 tinyint unsigned  not null  default 0 ;

alter table tt4 drop column age2;

但是这种方法只适合表的比较下,业务不忙的环境,为ddl操作更快,可以更改session的环境变量

增加SESSION级别的 sort_buffer_size 值,以加速DDL过程需要做的排序操作;

增加SESSION级别的 read_buffer_size 值,增加顺序读的速度;

增加SESSION级别的 tmp_table_size 和 max_heap_table_size值,增加内存缓存数据的能力;

若是存在InnoDB引擎转MyISAM的操作,建议在线增加key_buffer_size的值,增加索引数据的缓存能力;

添加字段1).增加字段必须要有默认值,非NULL且有默认值的字段属性,有利于提高数据检索的性能,更加有利于索引结构的优化

2).增加的字段不能指定字段顺序,必须在尾部增加;否则会需要更多时间用于数据重整,并且在复制时也可能造成数据错乱

例如:

alter table tt4 add column age2 tinyint unsigned  not null  default 0 ;

绝对禁止:alter table tt4 add column age2 tinyint unsigned  not null  default 0 first;

同一表增加多个字段,不分多条语句,而应只用一条变更语句(在创建多条索引时,也采用这种方式)

alter table tt4

add column age1 tinyint unsigned  not null  default 0,

add column age2 tinyint unsigned  not null  default 0;

2.新增维度表用于添加字段不修改原表,把增加的字段放入维度表中,可以通过修改业务逻辑或表关联来读取数据,但这种方式对于性能和日维护是有有影响的。在设计之初,表上增加几个保留字段也是不错的想法,但毕竟有限。

3.通过中间表+触发器的方式实现比如更新表a

1).lock表a,创建触发器,把更新数据(insert,update,delete)放进中间表

2).释放锁

3).然后复制表a的物理结构a_bak,并修改ddl,然后copy数据

4).数据复制完后,再锁表a。然后把中间表的数据同步到a_bak中,然后锁住a_bak

5).然后重命名a_bak为原表a

6).检查无误后删除原表a

4.因应用而维护表的ddl操作注意事项 1).在优化索引时,要优先程序发布,而且待测试后,再删除多余索引

2).增加字段或扩大字段值域,必须优先程序发布

3).在更改字段的数据类型时,和程序同时发布因为这是是一个需要停机维护操作的。

4).在有停机条件,就停机维护;没停机条件要选择业务空闲维护ddl

5.drop table原理分析与操作技巧在InnoDB缓冲池很大且充满页面时,在drop table时,会使整个系统hang住,这是因为这期间mysql要两次遍历buffer pool LRU链表,遍历的时候会加锁导致系统hang住,而且buffer pool越大,hang的时间越长。0b1331709591d260c1c78e86d0c51c18.png

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值