MySQL“set unique_checks”，“set foreign_key_checks”对比“改变表格禁用键” [英] MySQL "set unique_checks", "set foreign_key_checks" vs. "alter table disable keys"

问题描述

我们遇到了一个问题，mysqldump脚本花费了90％的时间来处理它处理的一小部分表格。消除FK和索引消除了速度问题，但不是一个可接受的解决方案。

转储脚本确实：

  / *！40014 SET @OLD_UNIQUE_CHECKS = @@ UNIQUE_CHECKS，UNIQUE_CHECKS = 0 * /; 
 / *！40014 SET @OLD_FOREIGN_KEY_CHECKS = @@ FOREIGN_KEY_CHECKS，FOREIGN_KEY_CHECKS = 0 * /; 
  

我们可以从> ALTER TABLE foo DISABLE KEYS

此外，是禁用键会话作用域或者是永久的，直到我重新启用它？我可以从一个MySQL会话中禁用密钥，并使其从另一个会话发出的导入效果吗？

解决方案

是的，从 DISABLE KEYS 中获益。它不是会话范围的，它是一个table属性，所以你的密钥将会被所有人使用，直到你完成 ENABLE KEYS 。

We're having a problem where a mysqldump script is spending 90% of it's time populating a small handful of the tables it deals with. Eliminating FK's and indexes eliminates the speed problem, but is not an acceptable solution.

The dump script does have:

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

Can we expect any different behavior from ALTER TABLE foo DISABLE KEYS?

Also, is disable keys session-scoped or is it permanent until I re-enable it? Can I disable keys from one mysql session and have it effect the import issued from another session?

解决方案

Yes, you should get significant benefits out of DISABLE KEYS. It isn't session-scoped, it's a table property, so your keys will be dead for everybody until you do ENABLE KEYS.

这篇关于MySQL“set unique_checks”，“set foreign_key_checks”对比“改变表格禁用键”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！