mysqldump 行为和相关的 InnoDB 性能

Question

我有以下 mysqldump 文件。我有 3 个问题:

1. /* */ 中的命令是否被注释掉了，或者它们是否真的被执行了？

2. 根据官方MySQL documentation它说:

The mysqldump option --opt creates dump files that are fast to import into an InnoDB table, even without wrapping them with the SET autocommit and COMMIT statements. opt is enabled by default

但是 mysqldump 文件没有包含 SET auto commit 和 COMMIT 语句，我想知道这些是不是隐含的？还是我需要添加它们。

3. 即使我设置了 unique_checks=0 和 foreign_key_checks=0，如果我有非唯一 key ，我也应该删除这些键在批量插入之前，然后在之后重新添加它们，以获得性能，对吧？

-- Server version   5.6.10-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;



LOCK TABLES `xxx` WRITE;
/*!40000 ALTER TABLE `xxx` DISABLE KEYS */;

Answer 1

1. Are the commands within /* */ commented out or are they actually getting executed?

是的，它们实际上正在执行，但仅适用于等于或高于 /*!40101 版本号的 MySQL 版本，如您的示例所示。根据 the MySQL manual on comment syntax :

If you add a version number after the “!” character, the syntax within the comment is executed only if the MySQL version is greater than or equal to the specified version number.

然后就 mysqldump 和 InnoDB 性能而言:

2. But mysqldump file didn’t include SET auto commit and COMMIT statements, I’m wondering if those are implicit? Or do I need to add them.

如解释in the official MySQL performance tips :

When importing data into InnoDB, make sure that MySQL does not have autocommit mode enabled because that requires a log flush to disk for every insert. To disable autocommit during your import operation, surround it with SET auto commit and COMMIT statements.

这与此完全不同:

If you use the mysqldump option --opt, you get dump files that are fast to import into an InnoDB table, even without wrapping them with the SET autocommit and COMMIT statements.

它实际上并没有禁用SET auto commit 和COMMIT 语句。它只是作用于 InnoDB 设置的默认值，这意味着它是隐式设置的。

3. Even when I set unique_checks=0 and foreign_key_checks=0, if I have non-unique key, I should probably remove these keys prior to the bulk insert, and then re-add them afterwards, in order to gain performance, right?

我非常有信心，通过 mysqldump 导出然后再次导入的行为会像这样自动进行房屋清洁。好的，再挖一些，似乎是“随机”键 can negatively impact InnoDB import performance . Paul Dixon 的回答(粗体强调是我的)给出了明确的建议，“你的‘真实’关键字段仍然可以被索引，但是对于批量插入你最好在插入后一次性删除并重新创建该索引完整的。”