MySQL数据库DDL操作三种方法

    DDL指的是CREATE、DROP、ALTER之类的操作。当在MySQL中使用它们时，比如说添加一个索引，一个字段，此时MySQL会锁住表（ 不像Oracle那样只更新字典表、很快，因为MySQLl采
用中间表的方式来实现 ），如果数据量很大的话，这个锁表的时间可能会持续很久，在此期间，任何其它的写操作都不能执行， 这无疑是个让人恼火的问题。

对此我总结了三种方法（以添加索引为例）
1、对于数据量比较小的表，可在业务空闲时间段直接添加索引
DDL操作工作原理：
① 对表加锁(表此时只读)
② 复制原表物理结构
③ 修改表的物理结构
④ 把原表数据导入中间表中，数据同步完后，锁定中间表，并删除原表
⑤ rename中间表为原表
⑥ 刷新数据字典，并释放锁
mysql > alter table tab add index idx_id(id );

2、对于数据量比较大的表，运用以上方式速度非常慢，采用下面的方式能有效的提升效率，并且可以方便的查看进度
（1）创建新表复制原表结构并修添加索引，例如原表是tab，新表则为tab_new
mysql > create table tab_new like tab;
mysql > alter table tab_new add index idx_id(id);
（2）临时调整mysql参数，提高导入导出数据的效率
mysql > set global innodb_flush_log_at_trx_commit=0;                    //innodb表需要做此设置 myISAM表无需
mysql > set global KEY_BUFFER_SIZE=1099511627776;
mysql > set session BULK_INSERT_BUFFER_SIZE=1099511627776;
mysql > set session MYISAM_SORT_BUFFER_SIZE=1099511627776;
（3）锁定要操作的表的写操作，只允许读操作
mysql > lock tables tab write,tab_new write;
（4）导出旧表数据
mysql > select * from tab into outfile  '/home/mysql/tab.txt';          //如果新加字段，需要在select *后加上新加字段的默认值
（5）数据写入新表
mysql > load data infile '/home/mysql/tab.txt' into table tab_new;      //过程中可以用show table status from DBNAME where name='tab_new' \G; 查看进度，关注Rows值
（6）重命名旧表到临时表
mysql > alter table tab rename tab_old;
（7）重命名新表到旧表
mysql > alter table tab_new rename tab;
（8）解锁表
mysql > unlock tables;
（9）恢复mysql关键系统参数
mysql > set global innodb_flush_log_at_trx_commit=1;

3、当时遇到千万级别的表就会影响前端应用对表的写操作，为此perconal推出pt-online-schema-change工具，其特点是修改过程中不会造成读写阻塞
工作原理：
操作的表必须有主键，如果表有外键，除非使用 --alter-foreign-keys-method 指定特定的值，否则工具不予执行。
① 创建一个和你要执行 alter 操作的表一样的空表结构
② 执行表结构修改，然后从原表中的数据到copy到表结构修改后的表
③ 在原表上创建触发器将 copy 数据的过程中，在原表的更新操作 更新到新表  *注意:如果表中已经定义了触发器这个工具就不能工作了
④ copy 完成以后，用rename table 新表代替原表，默认删除原表
//主从环境主库添加索引
# pt-online-schema-change --user=root --password=123456 --alter='add index idx_id(aid)' D=dbname,t=tab --no-check-replication-filters --recursion-method=none --print --execute
主要参数解释：
D：数据库名  t：表名  --recursion-method=none：主从环境，不在乎从库的延迟
--no-check-replication-filters：该工具在检测到服务器选项中有任何复制相关的筛选会退出，所以需要添加该参数
详细参数使用方法参考：http://www.it165.net/pro/html/201312/8928.html


<本文作者：rtt8387，专注Oracle、Mysql数据库技术>
<版权所有，请勿转载。如须转载请详细标明转载出处，否则追究法律责任!>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24113018/viewspace-1840727/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24113018/viewspace-1840727/