主页 / dba / 问题 / 165495
Accepted
Collin Dauphinee
Asked: 2017-02-25 17:32:47 +0800 CST

如何在不锁定的情况下将列的子集从一个 InnoDB 表复制到另一个表？

  • 772

我有一个在 Amazon 的 RDS 上运行的 MySQL 实例。这意味着我无法更改任何二进制日志设置。

我有一个source像这样创建的表，其中包含大约 20 亿行：

CREATE TABLE `source` (
   id INT PRIMARY KEY AUTO_INCREMENT,
   value1 VARCHAR(256),
   value2 VARCHAR(256)
);

我有另一个表destination，具有相同的列，但它id是BIGINT：

CREATE TABLE `destination` (
   id BIGINT PRIMARY KEY AUTO_INCREMENT,
   value1 VARCHAR(256),
   value2 VARCHAR(256)
);

该source表在我要压缩的 ID 中有间隙。我想将所有行复制source到destination，不带id列，如下所示：

INSERT INTO `destination` (value1, value2)
SELECT value1, value2 FROM `source`;

我怎样才能做到这一点，而不锁定source表？由于表的大小，副本本身将花费非常长的时间，而且我不能将它锁定那么久。

我基本上想READ UNCOMMITTED隔离地运行上面的语句，但由于我无法更改任何二进制日志设置，这是不可能的。

mysql innodb

3 个回答

  1. Best Answer

    如果您不更新或删除旧行，则使用最小锁定的方法是逐块进行。请参阅此处以进行分块删除；它可以适应您的“副本”。当您到达复制结束时，停止写入足够长的时间以复制最终块并重命名：

    然后，如果您的真正目标是更改为BIGINT，则完成

    RENAME TABLE source TO old,
             destination TO source;
DROP TABLE old;
    • 2

  2. 替代方式，如果您不担心旧行的更新（与其他答案相同的限制），则可以使用

    因为 MySQL 锁定表用于 INSERT FROM SELECT 和 SELECT INTO 表，但同时 - 不要锁定：

    • 普通 SELECT，比如

      SELECT t1.col1, t1.col2, t1.col3 FROM table1 t2 LEFT JOIN table2 t2 ON t1.id = t2.id WHERE t2.id IS NULL

    我使用 ETL 作业 (Talend)，它从源表打开流并写入目标。

    从 MySQL 的角度来看——它只是选择外部客户端

    实时使用示例- 每月，我们为过去的飞蛾准备数据集，原因 - 通过动态添加几列，我们将计算速度提高了一百倍以上。高常规 INSERT/UPDATE 加载下的源表，但仅限当前期间。

    “手动”替代方案- 将查询结果存储到本地（在客户端计算机或 S3 上）csv 文件，而不是从 csv 批量插入。

    ETL 只是自动执行此过程并排除中间 csv 文件。许多开发人员工具，如 MySQL Workbench、Navicat、DBVisualizer（任何重要的工具）都可以帮助您将查询结果存储到不同格式的本地文件中。

    这种方式的好处：

    • 它比块工作得更快，因为我们单步读取数据，而不是我们可以使用批量插入。
    • 设计时更容易对数据进行任何更改
    • 0

  3. 这是真正的问题：针对 InnoDB 表的普通 SELECT 语句不会锁定任何行，但 INSERT INTO...SELECT 会。解决这个问题的最简单方法是首先从源表中选择所需的行到一个文件中，然后将该文件导入到目标表中。

    一种方法是：

    SELECT... INTO OUTFILE，然后 LOAD DATA INFILE，例如：

    鉴于此表：

    mysql> select * from source_table;
+----+-----------+---------+----------+
| id | columnA   | columnB | columnC  |
+----+-----------+---------+----------+
|  1 | monkey    | mammal  | jungle   |
|  2 | alligator | reptile | swamp    |
|  3 | elephant  | mammal  | savannah |
+----+-----------+---------+----------+
3 rows in set (0.00 sec)

    提取 columnA 和 columnB，其中 columnC = 'swamp' 并将结果存储在架构子目录中的文件中：

    mysql> select NULL, columna, columnB into outfile 'swampanimals.txt' from source_table where columnC='swamp';

    NULL 是 id 列的占位符。稍后加载时，它将替换为 auto_increment 值。

    然后将生成的文件加载到目标表中：

    mysql> load data infile 'swampanimals.txt' into table target_table;

    假设 target_table 一开始是空的，它将如下所示：

    mysql> select * from target_table;
+----+-----------+---------+
| id | columnA   | columnB |
+----+-----------+---------+
|  1 | alligator | reptile |
+----+-----------+---------+
1 row in set (0.00 sec)

    鉴于您使用的是 RDS，可能无法使用 SELECT...INTO OUTFILE——我不知道。如果你不能使用它，你可以做这样的事情：

    mysql --host=<rdshost> -s -e "SELECT NULL, columnA, columnB FROM source_table WHERE columnC='swamp'" | sed -r "s/NULL/\\\N/" > swampanimals.txt

    sed 是我所知道的将 NULL 作为 NULL 字符而不是文字字符串“NULL”获取到输出中的唯一方法。然后在加载数据 infile 时使用 LOCAL：

    mysql> load data local infile "swampanimals.txt" into table target_table;

    由于您有 20 亿行数据，您可能遇到的一个问题是 LOAD DATA INFILE 可能会创建一个非常大的事务并导致存储撤消日志的 ibdata 文件被拉伸。您可以通过从文件中分块加载数据来解决此问题。这是一个很好的技巧，但它需要一个现代版本的拆分。给定一个名为“bigfile”的大型输入文件并将其加载到模式“target_schema”的表“target_table”中：

    split -C100M --filter="mysql target_schema -e \"LOAD DATA LOCAL INFILE '/dev/stdin' INTO TABLE target_table\"" bigfile

    这会将文件分成 100 MB 的块，但块不会跨行拆分。这样，您就不会有任何大小超过 100 MB 的事务，并且您的撤消日志也不会被拉长。

    参考： https ://dev.mysql.com/doc/refman/5.6/en/select-into.html和https://dev.mysql.com/doc/refman/5.6/en/load-data.html有关 SELECT...INTO OUTFILE 和 LOAD DATA INFILE 的完整详细信息。

    • 0

相关问题