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

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

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

- 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
```sql
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)
```

テーブル
```sql
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
```sql
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に降格させます。
```sql
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のズレを防止することを忘れてはいけません。

```sql
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を実行してみます
```sql
/* 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を行います
```sql
/* 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が一意に決定されます。
```sql
ALTER TABLE sample_table
  DROP PRIMARY KEY,
  ADD UNIQUE KEY (current_pk),
  ADD PRIMARY KEY (new_pk),
  ALGORITHM=INPLACE, LOCK=NONE;
```


#### Replicaのテーブル定義の変更
※sample tableは作り直しています
```sql
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から確認していきます。
```sql
/* 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はどうでしょうか
```sql
/* 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も確認してみます。
```sql
/* ### 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遅延となってしまうので、忘れないように気を付けましょう。
```sql
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を受け付けられるようになります。

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

[公式のリファレンスマニュアル](https://dev.mysql.com/doc/refman/8.0/ja/replication-options-binary-log.html#sysvar_binlog_row_image)には、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が少しでも有効に使われる一助となれば幸いです。