Amazon Aurora(MySQL)のALTER文のLockを調べてみた

本記事は、[Craft Egg Advent Calendar 2021](https://qiita.com/advent-calendar/2021/craftegg)の12/1の記事です。

# はじめに
MySQLを運用していく中で、ALTER文を流したいタイミングがたまにあります。
しかしLockがかかって大変なことになるのでは？とおもい、ALTER文ながすことをためらうことばかりでした。
Amazon Aurora MySQL3(MySQL8.0互換）がリリースされたこともあり、最近のMySQLのALTER文の改善をみていこうとおもいます。

# 調査対象のAmazon Auroa(MySQL)のバージョン
以下の３つのバージョンです。

###**Engine version**
#### (a) 5.6.mysql_aurora.1.23.4
#### (b) 5.7.mysql_aurora.2.10.1
#### (c) 8.0.mysql_aurora.3.01.0

# 調査するALTER文(LockがかかりそうなDDL）
次の４つのパターンを調査します。

#### (1)　カラム追加
次のようのなSQLです。

```sql
alter table `テーブル名` add `カラム名` '型';
```
#### (2) インデックスの作成
次のようなSQLです。

```sql
CREATE INDEX `インデックス名` ON `テーブル名` (`カラム名`) ;
```

#### (3) VARACHAR型のカラムサイズの伸長
次のようなSQLです。

```sql
ALTER TABLE `テーブル名` CHANGE COLUMN `カラム名`  `カラム名`  VARCHAR(6);
```

#### (4) パーティションの追加
次のようなSQLです(RANGE PARTITIONを想定)

```sql
ALTER TABLE `テーブル名` ADD PARTITION (PARTITION `パーティション名` VALUES LESS THAN (5000)) ;
```

### 選出理由
ALTER文を流そうと思った時にためらいそうなもののなかで、MySQLのバージョンがあがるにつれ、改善されたALTER文を選んでいます。

それぞれの期待する結果は、次の通りです。

||MySQL5.6|MySQL5.7|MySQL8.0|
|:-------|:-------:|:------:|:------:|
|(1)カラム追加| ○ | ○ | ○ |
|(2)インデックスの作成| ○ | ○ | ○ |
|(3)VARACHAR型のカラムサイズの伸長| × | ○ | ○ |
|(4)パーティションの追加| × | × | ○ |

※○はLockがかからないことを示します（Meta Data Lockをのぞく）

####参考
・[MySQL5.6に関してのLock](https://dev.mysql.com/doc/refman/5.6/ja/innodb-create-index-overview.html)
・[MySQL5.7に関してのLock](https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html)
・[MySQL8.0に関してのLock](https://dev.mysql.com/doc/refman/8.0/ja/innodb-online-ddl-operations.html)

# テスト対象のテーブルの準備
今回検証するにあたり、データの準備をしていきます。

公式ドキュメントの[オンライン DDL 実験のためのスキーマ設定コード](https://dev.mysql.com/doc/refman/5.6/ja/innodb-create-index-examples.html#online-ddl-ex-setup)
の手順にしたがってテーブルとデータを用意します。

せっかく検証するのであれば、データ量がおおいほうがいいよね、とおもい（後々の後悔しましたが）
上記のドキュメントを参考に
```big_table``` というテーブルに ```122,486,784``` レコード（約1億レコード）データをいれました。(mysqldumpの出力結果が ```22GB``` ほどになりました・・・）

```big_table``` の ```show create table``` の出力は次のとおりです。

```sql
Create Table: CREATE TABLE `big_table` (
`TABLE_CATALOG` varchar(512) CHARACTER SET utf8 NOT NULL DEFAULT '',
`TABLE_SCHEMA` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',
`TABLE_NAME` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',
`COLUMN_NAME` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',
`ORDINAL_POSITION` bigint(21) unsigned NOT NULL DEFAULT '0',
`COLUMN_DEFAULT` longtext CHARACTER SET utf8,
`IS_NULLABLE` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT '',
`DATA_TYPE` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',
`CHARACTER_MAXIMUM_LENGTH` bigint(21) unsigned DEFAULT NULL,
`CHARACTER_OCTET_LENGTH` bigint(21) unsigned DEFAULT NULL,
`NUMERIC_PRECISION` bigint(21) unsigned DEFAULT NULL,
`NUMERIC_SCALE` bigint(21) unsigned DEFAULT NULL,
`DATETIME_PRECISION` bigint(21) unsigned DEFAULT NULL,
`CHARACTER_SET_NAME` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
`COLLATION_NAME` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
`COLUMN_TYPE` longtext CHARACTER SET utf8 NOT NULL,
`COLUMN_KEY` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT '',
`EXTRA` varchar(30) CHARACTER SET utf8 NOT NULL DEFAULT '',
`PRIVILEGES` varchar(80) CHARACTER SET utf8 NOT NULL DEFAULT '',
`COLUMN_COMMENT` varchar(1024) CHARACTER SET utf8 NOT NULL DEFAULT '',
`id` bigint(21) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
```

# 実際に検証
それでは、データの準備もできたので、実際にLockの有無を検証していきます。
データ量がおおくなってしまったので、検証につかうインスタンスは、 ```db.r5.xlarge``` を使用しています。

また、Lockがかかっているか、いないかをわかりやすくするために、INSERT文を１秒毎に```big_table```に対してながしていきます。

## (1)　カラム追加 の検証
実際に流すのは次のSQLです。

```sql
alter table big_table add addcolumn int ,LOCK=NONE, ALGORITHM=INPLACE;
```
### (a) 5.6.mysql_aurora.1.23.4
INSERT文をながしながら、カラム追加を実行している様子です。
（左：INSERT文、右：カラム追加）
![MySQL5_6_ADD_COLUMN.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/67626/614847fa-71ee-b9f8-e4ce-facc0dbe0b03.gif)
こちらLockかかることなく実行できました。

### (b) 5.7.mysql_aurora.2.10.1
INSERT文をながしながら、カラム追加を実行している様子です。
（左：INSERT文、右：カラム追加）
![MySQL5_7_ADD_COLUMN.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/67626/cfab58dd-aeed-30f9-90a7-77641f463b46.gif)
こちらLockかかることなく実行できました。

### (c) 8.0.mysql_aurora.3.01.0
INSERT文をながしながら、カラム追加を実行している様子です。
（左：INSERT文、右：カラム追加）
![MySQL8_ADD_COLUMN.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/67626/513abcda-dc4b-87e6-901e-5421e7e5312a.gif)
こちらLockかかることなく実行できました。

## (2) インデックスの作成 の検証
実際に流すのは次のSQLです。

```sql
CREATE INDEX idx_1 ON big_table (`ORDINAL_POSITION`) ;
```
### (a) 5.6.mysql_aurora.1.23.4
INSERT文をながしながら、インデックスの作成をしている様子です。
（左：INSERT文、右：インデックスの作成）
![MySQL5_6_CREATE_INDEX.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/67626/10dcf3c6-3188-b8a7-0638-fc227ef6e10a.gif)
こちらLockかかることなく実行できました。

### (b) 5.7.mysql_aurora.2.10.1
INSERT文をながしながら、インデックスの作成をしている様子です。
（左：INSERT文、右：インデックスの作成）
![MySQL5_7_CREATE_INDEX.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/67626/c3d75b7f-2c93-611c-0cd8-ed5121f0d81e.gif)
こちらLockかかることなく実行できました。

### (c) 8.0.mysql_aurora.3.01.0
INSERT文をながしながら、インデックスの作成をしている様子です。
（左：INSERT文、右：インデックスの作成）
![MySQL8_CREATE_INDEX.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/67626/a2ed8d99-edeb-b5e1-9866-cf367cdde878.gif)
こちらLockかかることなく実行できました。

## (3) VARACHAR型のカラムサイズの伸長 の検証
実際に流すのは次のSQLです。

```sql
alter table big_table modify `TABLE_SCHEMA` varchar(128);
```
### (a) 5.6.mysql_aurora.1.23.4
INSERT文をながしながら、VARCHAR型のカラムサイズの伸長をしている様子です。
（左：INSERT文、右：VARCHAR型のカラムサイズの伸長）
![MySQL5_6_ALTER_VARCHAR_EXTEND.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/67626/fc317ed7-485e-755c-ebb2-659df519c47d.gif)
想定通り、Lockがかかりました(左のINSERT文がとまっている）。

### (b) 5.7.mysql_aurora.2.10.1
INSERT文をながしながら、VARCHAR型のカラムサイズの伸長をしている様子です。
（左：INSERT文、右：VARCHAR型のカラムサイズの伸長）
![MySQL5_7_ALTER_VARCHAR_EXTEND.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/67626/9a56a2db-cbf2-28d1-70c8-1bc5c79ea88c.gif)
！なんと、想定外に、Lockがかかってしまいました。
理由は後述します。(※１)

### (c) 8.0.mysql_aurora.3.01.0
INSERT文をながしながら、VARCHAR型のカラムサイズの伸長をしている様子です。
（左：INSERT文、右：VARCHAR型のカラムサイズの伸長）
![MySQL8_ALTER_VARCHAR_EXTEND.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/67626/38dd9b31-a851-a172-b26e-8a0df6e2be66.gif)
！なんと、想定外に、Lockがかかってしまいました。
こちらも理由は後述します。(※１)

### ※１ Lockがかかってしまった理由
正確な理由はわからなかったのですが、Lockがかからず実行できるケースもありました。
そもそもまず、SQLの見直しからです。

```sql
ALTER TABLE small_table CHANGE COLUMN `IS_NULLABLE`  `IS_NULLABLE`  VARCHAR(6), ALGORITHM=INPLACE, LOCK=NONE;
```
こちらのSQLはLockがかからず実行できました（テーブル名に注目）。
実は、テーブルのレコード件数が少ないときは（4000レコード）、Lockがかららず実行できたのですが、
今回の検証用のテーブル(1億レコード）では、エラーになってしまいました。
[マニュアル](https://dev.mysql.com/doc/refman/8.0/ja/innodb-online-ddl-operations.html#online-ddl-column-operations)をよむとVARCHARの伸長には、制約があることがわかります。
レコード件数が増えたことによって、制約にひっかかっていそうです。

## (4) パーティションの追加　の検証
実際に流すのは次のSQLです。

事前に次のSQLを実行してパーティションを作成します。

```sql
ALTER TABLE `small_table`  PARTITION BY RANGE (`id`)  (
PARTITION p1000 VALUES LESS THAN (1000),
PARTITION p4000 VALUES LESS THAN (4000)
) ;
```

その後、次のパーティションの追加のSQLを実行して検証します。

```sql
ALTER TABLE small_table LOCK=NONE, ALGORITHM=INPLACE, ADD PARTITION (PARTITION p5000 VALUES LESS THAN (5000)) ;
```

### (a) 5.6.mysql_aurora.1.23.4
INSERT文をながしながら、パーティションを追加している様子です。
（左：INSERT文、右：パーテシションの追加）
![MySQL5_6_ADD_PARTION_FAIL.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/67626/e0dd8e9f-701b-1e21-bf60-3c04a666e8bc.gif)
構文エラーとなり、SQLが実行できませんでした（想定通り）。

### (b) 5.7.mysql_aurora.2.10.1
INSERT文をながしながら、パーティションを追加している様子です。
（左：INSERT文、右：パーテシションの追加）
![MySQL5_7_ADD_PARTITION_FAIL.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/67626/0708997a-29b4-135c-e256-2ed876d5c8da.gif)
こちらも、サポートしていないLockというエラーがでて、実行できませんでした（想定通り）。

### (c) 8.0.mysql_aurora.3.01.0
INSERT文をながしながら、パーティションを追加している様子です。
（左：INSERT文、右：パーテシションの追加）
![MySQL8_ADD_PARTION_RIGHT.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/67626/2a7f0041-4200-1eb3-a9cd-6d6e46520a85.gif)
無事、構文エラーになることもなく、Lockもかかることもなく、実行できました。

### 補足
検証前は、マニュアルを読んで、次のSQL（パーティションの作成）がLockなしに実行できるものだと思っていました。

```sql
ALTER TABLE `small_table` ALGORITHM=INPLACE PARTITION BY RANGE (`id`)  (
PARTITION p10 VALUES LESS THAN (10),
PARTITION pmax VALUES LESS THAN MAXVALUE
) ;
```
しかし、サポートされているのは、あくまで ```ADD PARTITION``` のため、上記SQLは構文エラーとなります。

# まとめ
実際に検証した結果をまとめると次のようになります。

||MySQL5.6|MySQL5.7|MySQL8.0|
|:-------|:-------:|:------:|:------:|
|(1)カラム追加| ○ | ○ | ○ |
|(2)インデックスの作成| ○ | ○ | ○ |
|(3)VARACHAR型のカラムサイズの伸長| × | △ | △ |
|(4)パーティションの追加| × | × | ○ |

△：実行できる場合もある（制約によって実行に失敗する）

# おわりに
昔からのMySQLの先入観で、基本的にALTER文はLockがかかってしまうとおもっていました。
しかし今回あらためて、マニュアルを見て、LockのかからないALTER文が増えていることに気づきました。
実際に実行してみると、Lockは、かからず、MySQLに対する先入観が覆されました。

また、新しいバージョンでLockがかからないALTER文は増えているのは検証を進める中でわかったのですが、
制約があるために、使えそうな機会は狭そうというのが正直なところです。

今回の検証が皆様の役に立てればと思います。




明日は@ishiguro_takuyaの記事です！