背景

业务中使用的一个 群组与用户的关系表，典型的查询场景是根据群组ID查询所有的用户ID，于是设计表结构如下：

CREATE TABLE `group_user_relation` (
  `user_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL COMMENT 'user id',
  `group_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'group id',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `bk_int1` int(11) DEFAULT '0',
  `bk_string1` varchar(255) COLLATE utf8_unicode_ci DEFAULT '',
  PRIMARY KEY (`user_id`, `group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

alter table group_user_relation partition by key (group_id) partitions 100;

设计初衷 group_id 和 user_id 做联合主键，预估到数据量比较大，然后按 group_id 进行分区。

但是，联合主键的顺序写反了！！！当数据量到达千万量级时，根据group进行查询的速度已经明显的慢了。所以需要进行补救措施，由于分区的存在，直接修改主键是不可能的。所以只能是新建表，进行数据迁移。

过程

  1. 原表停止写入

  2. 原表改名为 group_user_relation_bak

  3. 建新表 group_user_relation，调整主键顺序，并且将分区数量扩大。

    CREATE TABLE `group_user_relation` (
  `group_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'group id',
  `user_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL COMMENT 'user id',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `bk_int1` int(11) DEFAULT '0',
  `bk_string1` varchar(255) COLLATE utf8_unicode_ci DEFAULT '',
  PRIMARY KEY (`group_id`, `user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

alter table group_user_relation partition by key (group_id) partitions 500;
  4. 将原数据导入新表。

  5. 将写入流程放开。

主要卡在了 数据导入 这部分。着重记录下这块的处理过程。

数据导入

  1. 使用 insert into select 的方案。6000多万条记录，11 分钟完成全部数据迁移。结果如下： 
    mysql> insert into group_user_relation(group_id, user_id, created_at) select group_id, user_id, created_at from group_user_relation_bak;
Query OK, 62081694 rows affected (10 min 50.00 sec)
Records: 62081694  Duplicates: 0  Warnings: 0
  2. 使用 select into outfile + load data infile 的方式。对应的SQL 如下： 
    SELECT * INTO OUTFILE '/tmp/relation.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM group_user_relation;

LOAD DATA INFILE '/tmp/relation.txt' INTO TABLE group_user_relation FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

    这种方案需要在 mysql 实例运行的主机上进行操作，因为生成的文件是在实例运行的主机上的，远程连接的方式无法用这种方式进行数据的导入导出，对此 mysql 的文档中也有提及。

    The SELECT ... INTO OUTFILE statement is intended primarily to let you very quickly dump a table to a text file on the server machine. If you want to create the resulting file on some other host than the server host, you normally cannot use SELECT ... INTO OUTFILE since there is no way to write a path to the file relative to the server host’s file system.

    However, if the MySQL client software is installed on the remote machine, you can instead use a client command such as mysql -e "SELECT ..." > *file_name* to generate the file on the client host.

    由于所使用的是阿里的RDS实例，这种方式无法使用。

  3. 使用 mysqldump 进行数据的导入导出。

    没有进行尝试，mysqldump 方式是通过将 表结构和数据导出为SQL文件，然后在通过 source 的方式，将数据导入进去。

总结：

  1. 在同一个db中不同的表间进行数据迁移，那么数据迁移可以通过 insert into xxx select xxx 的方式。速度比较快。
  2. select into outfile + load data infile 的方式，比较适合跨实例的数据迁移。速度也比较快，但需要注意的是，这种方式需要在 mysql 实例运行的主机上进行操作。
  3. mysqldump，暂未调研，需要补充。

参考链接

MySQL :: MySQL 8.0 Reference Manual :: 13.2.10.1 SELECT … INTO Syntax