Replication遅延なしでPrimary Keyを変更する #MySQL

概要

テーブルサイズがある程度大きくなるとALTER TABLE(DDL)に時間がかかるようになり、負荷分散を目的としてReplicaを用意していれば、DDLを適用した際にReplication遅延が発生するようになります。
サービスによってはレプリケーション遅延を許容できず、さりとてメンテナンスの時間を取ることも難しい、ということもあるかと思います。

そんな時に使える、レプリケーション遅延を回避しつつDDLを適用する手法として、下記の方法が挙げられます。
↓こちらでも紹介されておりますので、簡単な紹介に留めます。
MySQLのオンラインDDLでレプリケーション遅延を回避する方法

INSTANT DDL
- MySQL 8.0.12~
- 特定のDDLで可能
gh-ost, pt-online-schema-change等のツール
- 対象のテーブルが外部キー制約で参照されている場合に使えない、もしくは使いづらい
- UNIQUE KEY制約があるカラムに対しての適用でデータ損失のリスクあり
Rolling Schema Upgrade (RSU)
- ReplicaにDDLを先に適用し、あとからSourceに適用する手法
- オンラインDDLのみ
- Sourceからのbinlogを受け付けられるDDLのみ
- binlog_format = ROW or MIXED

今回は、表題にあるようにPrimary KeyをReplication 遅延なしで変更できるかの検証を行いましたので、その結果を記載します。

前提

Primary Keyの削除と追加を一度に行う場合はオンラインDDLがサポートされます
- https://dev.mysql.com/doc/refman/8.0/ja/innodb-online-ddl-operations.html#online-ddl-primary-key-operations
Primary Key変更のため、INSTANT DDLおよびツールを利用した適用はできず、RSUが候補となります
設定等
- version = 8.0.28
- binlog_format = ROW
- binlog_row_image = minimal
- 対象テーブルのPKにはAUTO_INCREMENTが設定されている
- Primary Keyの変更先のカラムはNULLを含まない

結論

既存のPrimary KeyをUNIQUE KEYに降格させることでRSUの手法が可能です。

検証

各種定義

Database

mysql >CREATE DATABASE `rsu_test` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql >SHOW CREATE DATABASE rsu_test\G
*************************** 1. row ***************************
       Database: rsu_test
Create Database: CREATE DATABASE `rsu_test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
1 row in set (0.00 sec)

テーブル

mysql >CREATE TABLE sample_table (current_pk int AUTO_INCREMENT, new_pk int NOT NULL, val varchar(64) CHARSET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, created_at datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (current_pk)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_general_ci;
Query OK, 0 rows affected (0.01 sec)
 
mysql >SHOW CREATE TABLE sample_table\G
*************************** 1. row ***************************
       Table: sample_table
Create Table: CREATE TABLE `sample_table` (
  `current_pk` int NOT NULL AUTO_INCREMENT,
  `new_pk` int NOT NULL,
  `val` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`current_pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

データのINSERT

mysql >INSERT INTO sample_table (new_pk,val) VALUES (1,'one'),(2,'two'),(3,'three'),(4,'four');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
 
mysql >SELECT * FROM sample_table;
+------------+--------+-------+---------------------+
| current_pk | new_pk | val   | created_at          |
+------------+--------+-------+---------------------+
|          1 |      1 | one   | 2024-03-25 21:50:55 |
|          2 |      2 | two   | 2024-03-25 21:50:55 |
|          3 |      3 | three | 2024-03-25 21:50:55 |
|          4 |      4 | four  | 2024-03-25 21:50:55 |
+------------+--------+-------+---------------------+
4 rows in set (0.00 sec)

検証① Primary Key → KEY への降格

単純にPrimary Keyを入れ替えるだけでRSUはできるのでしょうか。
AUTO_INCREMENTカラムにはインデックスを貼る必要があるため、KEYに降格させます。

ALTER TABLE sample_table
  DROP PRIMARY KEY,
  ADD KEY (current_pk),
  ADD PRIMARY KEY (new_pk),
  ALGORITHM=INPLACE, LOCK=NONE;

Replica のテーブル定義を変更

Replicaに対して実行、current_pkをKEYに、new_pk をPrimary Keyにします。
sql_log_bin=OFF にしてbinlogを吐かないようにして、GTIDのズレを防止することを忘れてはいけません。

mysql >SET SESSION sql_log_bin=OFF;
Query OK, 0 rows affected (0.00 sec)
 
mysql >SELECT @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)
 
mysql >ALTER TABLE sample_table DROP PRIMARY KEY, ADD KEY (current_pk), ADD PRIMARY KEY (new_pk), ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql >SHOW CREATE TABLE sample_table\G
*************************** 1. row ***************************
       Table: sample_table
Create Table: CREATE TABLE `sample_table` (
  `current_pk` int NOT NULL AUTO_INCREMENT,
  `new_pk` int NOT NULL,
  `val` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`new_pk`),
  KEY `current_pk` (`current_pk`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.01 sec)

Primary Keyがcurrent_pkからnew_PKに変わり、current_pkにはインデックスが貼られました。

INSERT の実行

SourceへINSERTを実行してみます

/* Sourceで実行 */
mysql >INSERT INTO sample_table (new_pk,val) VALUES (5,'five'),(6,'six');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
 
/* Replicaで確認 */
mysql >SELECT * FROM sample_table;
+------------+--------+-------+---------------------+
| current_pk | new_pk | val   | created_at          |
+------------+--------+-------+---------------------+
|          1 |      1 | one   | 2024-03-25 21:50:55 |
|          2 |      2 | two   | 2024-03-25 21:50:55 |
|          3 |      3 | three | 2024-03-25 21:50:55 |
|          4 |      4 | four  | 2024-03-25 21:50:55 |
|          5 |      5 | five  | 2024-03-25 22:23:46 |
|          6 |      6 | six   | 2024-03-25 22:23:46 |
+------------+--------+-------+---------------------+
6 rows in set (0.00 sec)

無事Replicaまで伝播しました。

UPDATE の実行

同じようにSourceにUPDATEを行います

/* Sourceで実行 */
mysql >UPDATE sample_table SET val="current_pk_condition" WHERE current_pk IN (2,3);
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0
 
/* Replica で確認*/
mysql > SHOW REPLICA STATUS\G
                    /* 中略 */
              Master_Log_File: binary_log.000002
          Read_Master_Log_Pos: 83083
               Relay_Log_File: relay_log.000007
                Relay_Log_Pos: 5765
        Relay_Master_Log_File: binary_log.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
                    /* 中略 */
                   Last_Errno: 1032
                   Last_Error: Could not execute Update_rows event on table rsu_test.sample_table; Can't find record in 'sample_table', Error_code: 1032; Can't find record in 'sample_table', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log binary_log.000002, end_log_pos 83052
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 82622
              Relay_Log_Space: 6776
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1032
               Last_SQL_Error: Could not execute Update_rows event on table rsu_test.sample_table; Can't find record in 'sample_table', Error_code: 1032; Can't find record in 'sample_table', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log binary_log.000002, end_log_pos 83052
  Replicate_Ignore_Server_Ids:
                  /* 以下略 */

Errorとなり、SQL_Threadが停止してしまいました。Error内容は更新対象のrecord(KEY)を見つけられなかったというものです。

テーブル定義を戻して、START REPLICA; でReplicationを復旧させます。

検証② Primary Key → UNIQUE KEY への降格

では、UNIQUE KEYへ変更した場合はどうでしょうか。通常のインデックスとは異なり、UNIQUE KEYは名前の通りそのカラムの値からrecordが一意に決定されます。

ALTER TABLE sample_table
  DROP PRIMARY KEY,
  ADD UNIQUE KEY (current_pk),
  ADD PRIMARY KEY (new_pk),
  ALGORITHM=INPLACE, LOCK=NONE;

Replicaのテーブル定義の変更

※sample tableは作り直しています

mysql >SET SESSION sql_log_bin=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql >SELECT @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

mysql >ALTER TABLE sample_table DROP PRIMARY KEY, ADD UNIQUE KEY (current_pk), ADD PRIMARY KEY (new_pk), ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql >SHOW CREATE TABLE sample_table\G
*************************** 1. row ***************************
       Table: sample_table
Create Table: CREATE TABLE `sample_table` (
  `current_pk` int NOT NULL AUTO_INCREMENT,
  `new_pk` int NOT NULL,
  `val` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`new_pk`),
  UNIQUE KEY `current_pk` (`current_pk`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

INSERT の実行

念のため、INSERTから確認していきます。

/* Sourceで実行 */
mysql >SELECT * FROM sample_table;
+------------+--------+-------+---------------------+
| current_pk | new_pk | val   | created_at          |
+------------+--------+-------+---------------------+
|          1 |      1 | one   | 2024-03-27 22:08:55 |
|          2 |      2 | two   | 2024-03-27 22:08:55 |
|          3 |      3 | three | 2024-03-27 22:08:55 |
|          4 |      4 | four  | 2024-03-27 22:08:55 |
+------------+--------+-------+---------------------+
4 rows in set (0.00 sec)

mysql >INSERT INTO sample_table (new_pk,val) VALUES (5,'five'),(6,'six');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
/* Replicaで確認 */
mysql >SELECT * FROM sample_table;
+------------+--------+-------+---------------------+
| current_pk | new_pk | val   | created_at          |
+------------+--------+-------+---------------------+
|          1 |      1 | one   | 2024-03-27 22:08:55 |
|          2 |      2 | two   | 2024-03-27 22:08:55 |
|          3 |      3 | three | 2024-03-27 22:08:55 |
|          4 |      4 | four  | 2024-03-27 22:08:55 |
|          5 |      5 | five  | 2024-03-27 22:15:45 |
|          6 |      6 | six   | 2024-03-27 22:15:45 |
+------------+--------+-------+---------------------+
6 rows in set (0.00 sec)

問題なくReplicateしているようです。

UPDATE の実行

では問題のUPDATEはどうでしょうか

/* Sourceで実行 */
mysql >UPDATE sample_table SET val="current_pk_condition" WHERE current_pk IN (2,3);
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0


/* Replicaで確認 */
mysql >SELECT * FROM sample_table;
+------------+--------+----------------------+---------------------+
| current_pk | new_pk | val                  | created_at          |
+------------+--------+----------------------+---------------------+
|          1 |      1 | one                  | 2024-03-27 22:08:55 |
|          2 |      2 | current_pk_condition | 2024-03-27 22:08:55 |
|          3 |      3 | current_pk_condition | 2024-03-27 22:08:55 |
|          4 |      4 | four                 | 2024-03-27 22:08:55 |
|          5 |      5 | five                 | 2024-03-27 22:15:45 |
|          6 |      6 | six                  | 2024-03-27 22:15:45 |
+------------+--------+----------------------+---------------------+
6 rows in set (0.00 sec)

UPDATEのbinlogが受け付けられました。

念のため他の条件のUPDATEも確認してみます。

/* ### new_pk ### */
/* Sourceで実行*/
mysql >UPDATE sample_table SET val="new_pk_condition" WHERE new_pk IN (1,2);
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql >UPDATE sample_table SET val="created_at_condition" WHERE created_at = "2024-03-27 22:15:45";
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

/* Replicaで確認 */
mysql >SELECT * FROM sample_table;
+------------+--------+----------------------+---------------------+
| current_pk | new_pk | val                  | created_at          |
+------------+--------+----------------------+---------------------+
|          1 |      1 | new_pk_condition     | 2024-03-27 22:08:55 |
|          2 |      2 | new_pk_condition     | 2024-03-27 22:08:55 |
|          3 |      3 | current_pk_condition | 2024-03-27 22:08:55 |
|          4 |      4 | four                 | 2024-03-27 22:08:55 |
|          5 |      5 | created_at_condition | 2024-03-27 22:15:45 |
|          6 |      6 | created_at_condition | 2024-03-27 22:15:45 |
+------------+--------+----------------------+---------------------+
6 rows in set (0.00 sec)

他の条件でも問題なさそうです。

Sourceのテーブル定義変更

最後にSource DBのテーブル定義を更新します。sql_log_bin=OFFにしないとbinlogに書かれてReplicaでも実行 → Replication遅延となってしまうので、忘れないように気を付けましょう。

mysql >SET SESSION sql_log_bin=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql >SELECT @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

mysql >ALTER TABLE sample_table DROP PRIMARY KEY, ADD UNIQUE KEY (current_pk), ADD PRIMARY KEY (new_pk), ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

補足1）binlogの内容

今回実行したUPDATEのbinlogを見てみます。

sudo mysqlbinlog --no-defaults -vv binary_log.000002 | less

(抜粋)

# at 87871
#240327  2:27:28 server id 176306146  end_log_pos 87942 CRC32 0xae58979a        Table_map: `rsu_test`.`sample_table` mapped to number 285
# at 87942
#240327  2:27:28 server id 176306146  end_log_pos 88034 CRC32 0xd441f82f        Update_rows: table id 285 flags: STMT_END_F

BINLOG '
gAUDZhPiN4IKRwAAAIZXAQAAAB0BAAAAAAEACHJzdV90ZXN0AAxzYW1wbGVfdGFibGUABAMDDxID
AAEACgEBAAIBLZqXWK4=
gAUDZh/iN4IKXAAAAOJXAQAAAB0BAAAAAAEAAgAEAQQABQAAAAAUAGNyZWF0ZWRfYXRfY29uZGl0
aW9uAAYAAAAAFABjcmVhdGVkX2F0X2NvbmRpdGlvbi/4QdQ=
'/*!*/;
### UPDATE `rsu_test`.`sample_table`
### WHERE
###   @1=5
### SET
###   @3='created_at_condition'
### UPDATE `rsu_test`.`sample_table`
### WHERE
###   @1=6
### SET
###   @3='created_at_condition'
# at 88034

### UPDATEから始まるコメントの部分が「疑似SQL」と呼ばれ、更新内容をSQLに起こしたものです。（@[番号]はカラムが何番目かを表しています。）
今回は binlog_format=ROW のため、WHERE条件がなんであれ、一行ずつの更新としてbinlogに記述されます。
行を特定するための条件として、@1つまりcurrent_pkの値が指定されていますので、Replica側でPKを変更した際、current_pkを一意に特定できるようNOT NULLのUNIQUE KEYで定義する必要がありました。

補足2) binlog_row_image = full について

今回は binlog_row_image = minimal が前提だったので利用できませんが、実はbinlog_row_image = fullに設定することで、Primary KeyをUNIQUE KEYにしなくてもRSUを行うことが可能です。

binlog_row_imageはbinlogに記録する行の更新内容のイメージを指定するオプションで、full に指定することで、更新前後の全カラムの情報を記録します。

疑似SQLは下記のようになります。

## (実行したDML)
# UPDATE sample_table SET val="current_pk_condition" WHERE current_pk IN (2,3)
## (疑似SQL)
### UPDATE `rsu_test`.`sample_table`
### WHERE
###   @1=2
###   @2=2
###   @3='two'
###   @4='2024-03-27 23:02:28'
### SET
###   @1=2
###   @2=2
###   @3='current_pk_condition'
###   @4='2024-03-27 23:02:28'
### UPDATE `rsu_test`.`sample_table`
### WHERE
###   @1=3
###   @2=3
###   @3='three'
###   @4='2024-03-27 23:02:28'
### SET
###   @1=3
###   @2=3
###   @3='current_pk_condition'
###   @4='2024-03-27 23:02:28'

このように、行の特定にcurrent_pkだけでなく全てのカラムの条件が記載され、UNIQUE KEYにしなくてもReplicaで行を特定できるため、下記のPrimary Key → KEY への降格でもbinlogを受け付けられるようになります。

ALTER TABLE sample_table
  DROP PRIMARY KEY,
  ADD KEY (current_pk),
  ADD PRIMARY KEY (new_pk),
  ALGORITHM=INPLACE, LOCK=NONE;

公式のリファレンスマニュアルには、minimalを使用する際にReplicaで削除、更新が正しく動作することを保証する条件として、

すべてのカラムが同じ順番で存在する必要があります。それぞれのカラムがもう一方のテーブル内の対応するものと同じデータ型を使用する必要があります
これらのテーブルの主キー定義が同じである必要があります

と記載があるので、Primary Keyの変更についてはこちらの方が公式には沿っていそうです。（デフォルト値もfullですし）
とはいえ、Serviceの規模によってはディスクやネットワーク使用量に余裕がなく、fullに設定するのは難しい場合もありますので、適宜使い分けるのが良さそうです。

まとめ

Rolling Schema Upgradeの手法でPrimary Keyを変更する場合、下記の二つの方法で実行できます。

既存のPrimary Key を単なるKEYではなく、UNIQUE KEYにする
binlog_row_image = fullとする

RSUの可否	PK → KEY	PK → UNIQUE KEY
binlog_row_image = minimal(noblob)	×	○
binlog_row_image = full	○	○

終わりに

いかがでしたか？

Primary Keyを変更したいという場面はあまり多くないかもしれませんが、やはりPrimary Keyとセカンダリインデックスとではパフォーマンス的にPrimary Keyが勝ります。

深く考えずテーブル固有のサロゲートキーをPrimary Keyにしてしまい、サービスのWHERE条件やJOIN条件にPrimary Keyが一切登場しない…。サービスが大きくなるにつれ、Primary Keyの優位性も大きくなってきて…ということもあるかもしれません。（私はありました。）

世のPrimary Keyが少しでも有効に使われる一助となれば幸いです。