主页 / dba / 问题 / 84825
Accepted
Mike Purcell
Asked: 2014-12-11 10:06:01 +0800 CST

SELECT INTO OUTFILE 与 INSERT INTO ... SELECT

  • 772

在我粗略的研究中，我无法找到关于SELECT INTO OUTFILE提供的好处的明确答案INSERT INTO ... SELECT。在阅读与InnoDB 表上有关锁相关的文档时，它指出：INSERT INTO ... SELECT

对插入到 T 的每一行设置一个不带间隙锁的独占索引记录。如果事务隔离级别为 READ COMMITTED 或启用了 innodb_locks_unsafe_for_binlog，并且事务隔离级别不是 SERIALIZABLE，则 InnoDB 将 S 上的搜索作为一致读取（no锁）。否则，InnoDB 在 S 中的行上设置共享的 next-key 锁。

为了避免锁定INSERT INTO ... SELECT似乎我必须确保隔离级别是READ COMMITTED避免在查询期间锁定源表。

但是，我还没有找到任何关于 locks 和 usingSELECT INTO OUTFILE的权威答案，甚至没有 MySQL文档参考 lock 信息。

我的目标是避免在查询运行时锁定源表以避免连接堆叠。

mysql innodb

1 个回答

  1. Best Answer

    您应该使用SELECT ... LOCK IN SHARE MODE。为什么 ？

    SELECT ... LOCK IN SHARE MODE 在读取的任何行上设置共享模式锁。其他会话可以读取这些行，但在您的事务提交之前不能修改它们。如果其中任何行被另一个尚未提交的事务更改，您的查询将等待该事务结束，然后使用最新值。

    在你的情况下，你可以尝试这个

    START TRANSACTION;
SELECT ... LOCK IN SHARE MODE;
SELECT ... INTO OUTFILE;
ROLLBACK;

    这将执行两个SELECT查询

    • 首先SELECT锁定您希望的表中的行
    • 二SELECT要执行SELECT ... INTO OUTFILE

    就个人而言，我认为您不必如此严厉。事务隔离应该足够聪明，以实现这个原子SELECT并为INSERT. 我知道我说should be这就是为什么你首先问你问题。

    无论您是SELECT ... INTO OUTFILE作为一个命令执行还是以我建议的严厉方式执行，源表的行数据都将是完全可读的。

    试试看 ！！！

    更新 2014-12-10 15:12 EST

    你的评论

    谢谢你的回答，它确实有帮助，但 OP 的主要观点是确定使用 SELECT INTO OUTFILE 而不是 INSERT INTO ... SELECT 是否有好处？

    它们在操作上是不同的

    • SELECT INTO OUTFILE创建一个文本文件
    • INSERT INTO SELECT从结果中加载一张表SELECT

    更新 2014-12-11 12:21 EST

    在这种情况下，我唯一能想到的是数据的时间点以及您使用它的时间。对于这两种类型的操作，都会有一些隐式共享锁定。

    使用SELECT INTO OUTFILE，您正在准备结果并将其保存在外部。在加载过程中使用将数据加载到表中LOAD DATA INFILE不会涉及任何共享锁定。请记住，这SELECT INTO OUTFILE会产生磁盘 I/O，并且在此过程中仍然会施加一些缓存。

    使用INSERT INTO SELECT，共享锁可能必须在 InnoDB 中存活更长时间，因为您正在锁定行并使用这些相同的行插入到另一个表中。

    因此，如果我正在寻找性能奖励，我会给予优势，INSERT INTO SELECT因为您正在执行相同数量的共享行锁定，单个操作的磁盘 I/O 必须小于单独SELECT INTO OUTFILE和后续的LOAD DATA INFILE. 当然，您必须将这两种方法与您的数据集进行比较。一个数据集的性能奖励可能是另一个数据集的性能成本。

    更新 2014-12-17 00:00 EST

    你的评论

    我没有收到通知你更新了你的答案，所以我假设它没有收到赏金。从理论上讲，您的解释确实有道理，但是我正在寻找更权威的回应，希望单独文件的开销（正如您正确提到的）可能值得在复杂性上进行权衡以提高性能。

    唯一的权威回应来自 MySQL 文档。

    首先，MySQL 文档LOAD DATA INFILE是怎么说的？

    LOAD DATA INFILE 语句以非常高的速度将文本文件中的行读取到表中。LOAD DATA INFILE 是 SELECT ... INTO OUTFILE 的补充。（请参阅第 13.2.9.1 节，“SELECT ... INTO 语法”。）要将数据从表写入文件，请使用 SELECT ... INTO OUTFILE。要将文件读回表中，请使用 LOAD DATA INFILE。

    两段后，它说

    有关 INSERT 与 LOAD DATA INFILE 的效率以及加速 LOAD DATA INFILE 的更多信息，请参阅第 8.2.2.1 节，“INSERT 语句的速度”。

    当您查看插入语句的速度时，它会说：

    为了优化插入速度，将许多小操作组合成一个大操作。理想情况下，您建立一个连接，一次发送许多新行的数据，并将所有索引更新和一致性检查延迟到最后。

    插入一行所需的时间由以下因素决定，其中数字表示近似比例：

    连接：（3）

    向服务器发送查询：(2)

    解析查询：（2）

    插入行：（1×行大小）

    插入索引：（1 × 索引数）

    结束： (1)

    这没有考虑打开表的初始开销，每个并发运行的查询都会执行一次。

    假设 B-tree 索引，表的大小会减慢 log N 的索引插入速度。

    您可以使用以下方法来加快插入速度：

    如果您同时从同一个客户端插入多行，请使用带有多个 VALUES 列表的 INSERT 语句一次插入多行。这比使用单独的单行 INSERT 语句要快得多（在某些情况下快很多倍）。如果要向非空表添加数据，则可以调整 bulk_insert_buffer_size 变量以使数据插入更快。请参见第 5.1.4 节，“服务器系统变量”。

    从文本文件加载表时，使用 LOAD DATA INFILE。这通常比使用 INSERT 语句快 20 倍。请参见第 13.2.6 节，“LOAD DATA INFILE 语法”。

    利用列具有默认值的事实。仅当要插入的值与默认值不同时才显式插入值。这减少了 MySQL 必须做的解析并提高了插入速度。

    有关 InnoDB 表的特定提示，请参阅第 8.5.4 节，“InnoDB 表的批量数据加载”。

    有关 MyISAM 表的特定提示，请参阅第 8.6.2 节，“MyISAM 表的批量数据加载”。

    此时事情开始看起来有点模糊，因为您必须根据存储引擎调整加载过程。MyISAM 在此语句中相当直接，因为大容量插入缓冲区仅用于 MyISAM，而 LOAD DATA INFILE 将利用大容量插入缓冲区。InnoDB 不会。

    看看这个 InnoDB 的图示（Percona CTO Vadim Tchachenko）

    InnoDB 架构

    调整选项还有其他考虑因素，但LOAD DATA INFILE实际上会将所有内容放入 InnoDB 缓冲池，通过日志缓冲区、双写缓冲区、插入缓冲区（如果目标表具有非唯一索引）、重做日志（ib_logfile0、ib_logfile1）和表的物理文件。这就是 LOAD DATA INFILE 的好处必须被取消的地方。

    我写过这个

    结语

    正如我在之前对这个答案的更新中已经说过的那样

    因此，如果我正在寻找性能奖励，我会给予优势，INSERT INTO SELECT因为您正在执行相同数量的共享行锁定，单个操作的磁盘 I/O 必须小于单独SELECT INTO OUTFILE和后续的LOAD DATA INFILE. 当然，您必须将这两种方法与您的数据集进行比较。一个数据集的性能奖励可能是另一个数据集的性能成本。

    基本上，您必须SELECT INTO OUTFILE/LOAD DATA INFILE针对INSERT INTO SELECT. 它可能是一个数据集的 6 个，一个数据集的六个，另一个数据集的陆上胜利。

    MySQL Docs 和我过去的帖子中所说的一切，我仍然对INSERT INTO SELECT. 您只需要测试这两种方法。

    • 5

相关问题