mysql的ddl维护技巧

深碍√TFBOYSˉ_ 2022-06-09 06:19 133阅读 0赞

author：skate  
time：2012/07/20

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

第一次遍历buffer pool LRU链表是为了查找属于adaptive hash的page，mysql为了快速并见少锁的时间，没查找1024个page，释放锁处理一批，然后再锁，再查找1024个这样的page。直到查完整个buffer pool LRu链表；在这次遍历buffer pool LRu的期间，是间断获得buf pool mutex，影响还是比较小的。  
   
第二次遍历buffer pool LRU链表是为了查找dirty page，并将其从flush list和lru list中删除，并添加到free list中去。在这期间是一直拥有buf pool mutex的，是长时间锁定。对系统影响很大。

通过上面的分析，我们drop table的时候，要在停机或空闲时间操作，避免出现异常

我测试发现  
情景1：  
在innodb\_file\_per\_table=0，即共享表空间时，我drop table很快  
table：tt5（有2列，带一个主键）  
record：31999680  
size：1.4G

mysql> drop table tt5;  
Query OK, 0 rows affected **(0.48 sec)**

情景2：  
在innodb\_file\_per\_table=1时，即独享表空间时，我drop table很慢  
mysql> drop table tt5;  
Query OK, 0 rows affected **(16.89 sec)**

情景3：  
在innodb\_file\_per\_table=1时，即独享表空间时，并在表对应的数据文件上做了硬链接，这是我drop table很快，和共享表空间速度差不多。

\[root@master1 skate\]\# ln tt5.ibd  tt5.ibd.hdlk  
\[root@master1 skate\]\# ll  
total 2722472  
\-rw-rw---- 1 mysql mysql         65 Jul  5 17:27 db.opt  
\-rw-rw---- 1 mysql mysql       8586 Jul  5 17:27 tt2.frm  
\-rw-rw---- 1 mysql mysql       8586 Jul  6 17:02 tt3.frm  
\-rw-rw---- 1 mysql mysql       8586 Jul  9 13:31 tt4.frm  
\-rw-rw---- 1 mysql mysql       8586 Jul 17 16:30 tt51.frm  
\-rw-rw---- 1 mysql mysql       8586 Jul 19 16:49 tt5.frm  
\-rw-rw---- 2 mysql mysql 1392508928 Jul 19 18:49 tt5.ibd  
\-rw-rw---- 2 mysql mysql 1392508928 Jul 19 18:49 tt5.ibd.hdlk

mysql> drop table tt5;  
Query OK, 0 rows affected (**0.49 sec)**

原因猜测分析  
1).情景1 和 情景2的不同是因为在情景1中，mysql在共享表空间中drop表时，是不删除数据文件所对应的磁盘文件  
2).情景2和情景3的不同是因为在情景3中，为单独的数据文件创建了硬链接，硬连接的作用是允许一个文件拥有多个有效路径名，即文件的索引节点有一个以上的连接。只删除一个连接并不影响索引节点本身和其它的连接，只有当最后一个连接被删除后，文件的数据块及文件的连接才会被释放。也就是说，文件真正删除的条件是与之相关的所有硬连接文件均被删除。  
3).情景1和情景3相同是因为，他们都更新了mysql的数据字典信息和删除了adaptive hash和buffer pool中对应的page，而没有真正删除表对应的数据文件，从这也可以理解mysql的数据文件是无法shrink，数据文件大小只增不减

**说明：**  
读取和修改一个文件的时候：  
 1)、我们首先根据文件名，找到这个文件的Inode-no(节点数)。  
 2)、当我们找到个文件的Inode-no时，就会根据这个number数在inodetable中找到对应的条目  
 3)、在inodetable中的信息：从左到右依次是：节点数、文件类型、文件的权限、硬链接数、用户ID、组ID、文件的大小、时间戳记， 最后为指向硬盘上存放数据的数据块的指针。

6.在双master结构中执行ddl操作  
如果在双master只有一个节点提供服务是，双master又可以随时切换的话，那可以在备库先维护ddl，步骤如下  
 1). 在备库先执行SET sql\_log\_bin=0; 然后执行DDL变更，这样备库只影响自己，日志没有传到主库。  
 2).备库更新完后，切换主备库，再在原来的主库上执行SET sql\_log\_bin=0; 然后执行DDL变更。这样就减少对业务影响程度及减少停机维护时间和次数。

当然在master/slave的环境也可采用这种方式维护。  
 1).先在master主库执行SET sql\_log\_bin=0; 然后执行DDL变更  
 2).再在只读slave库执行ddl变更。

\---------end--------