If you have UNIQUE constraints on secondary keys, you can speed up table imports by temporarily turning off the uniqueness checks during the import session:
SET UNIQUE_CHECKS=0;
... import operation ...
SET UNIQUE_CHECKS=1;
For big tables, this saves a lot of disk I/O because InnoDB can use its insert buffer to write secondary index records in a batch. Be certain that the data contains no duplicate keys. UNIQUE_CHECKS allows but does not require storage engines to ignore duplicate keys.
If you have FOREIGN KEY constraints in your tables, you can speed up table imports by turning the foreign key checks off for the duration of the import session:
SET FOREIGN_KEY_CHECKS=0;
... import operation ...
SET FOREIGN_KEY_CHECKS=1;
For big tables, this can save a lot of disk I/O.
这里面最后的 set XXX=1; 能加上原来的约束吗？
SET UNIQUE_CHECKS=0;
... import operation ...
SET UNIQUE_CHECKS=1;
For big tables, this saves a lot of disk I/O because InnoDB can use its insert buffer to write secondary index records in a batch. Be certain that the data contains no duplicate keys. UNIQUE_CHECKS allows but does not require storage engines to ignore duplicate keys.
If you have FOREIGN KEY constraints in your tables, you can speed up table imports by turning the foreign key checks off for the duration of the import session:
SET FOREIGN_KEY_CHECKS=0;
... import operation ...
SET FOREIGN_KEY_CHECKS=1;
For big tables, this can save a lot of disk I/O.
这里面最后的 set XXX=1; 能加上原来的约束吗？
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/25267658/viewspace-691486/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
安装Mysql5.5.10时成功
请登录后发表评论
登录
全部评论
最新文章
- MySQL的多表join中，子查询伤不起啊！
- mysql伤不起啊，游标循环遍历，报错了！
- Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND;？
- innodb表，800W左右数据，count（*）要12秒？
- 创建mysql function error
- MySQL的索引建立顺序与where条件后面的and顺序问题？
- MySQL Query Cache Hitrate命中率的计算方式汇总
- mysql初级中级DBA需要掌握的技能？
- mysqladmin: connect to server at 'localhost' failed？
- Xtrabackup-1.5 binary 安装备份恢复成功！
