ITPub博客

首页 > 数据库 > MySQL > MySQL数据库DDL操作三种方法

MySQL数据库DDL操作三种方法

原创 MySQL 作者:rtt8387 时间:2015-11-18 18:38:42 0 删除 编辑
    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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-07-02

  • 博文量
    43
  • 访问量
    79954