MySQL的DDL维护技巧

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的时间越长。