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

MySQL

Last updated at 2024-03-28Posted at 2024-03-28

概要

テーブルサイズがある程度大きくなると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が少しでも有効に使われる一助となれば幸いです。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up