業務でCREATE文を本番環境で実施するにあたり、上長に作業承認をもらう必要がありました。
承認にあたり、ある質問を頂きました。

上司：『PRIMARY KEYがAUTO_INCREMENTになっているけれど、DB再起動によるAUTO_INCREMENTリセットなどは考慮できている？物理削除とかは発生しない？』

僕：『ふむ・・・？』

上長はどういう意図でその質問をしたのでしょうか。

意図を知ったことでAUTO_INCREMENTを使う際に気をつけるべきポイントに気づけましたので、まとめます！

サンプルテーブル

社員テーブルを新規作成するCREATE文をサンプルにします。

CREATE TABLE `EMPLOYEE` (
  `EMPLOYEE_ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `DEPERTMENT_ID` char(5) NOT NULL,
  `DIVISION_ID` char(5) NOT NULL,
  `EMPLOYEE_LASTNAME` varchar(32) NOT NULL,
  `EMPLOYEE_FIRSTNAME` varchar(32) NOT NULL,
  `JOIN_START_DATE` date NOT NULL,
  `JOIN_END_DATE` date NOT NULL,
  `IS_DELETED` tinyint(4) NOT NULL DEFAULT '0',
  `CREATED_AT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`EMPLOYEE_ID`),
  KEY `DEPERTMENT_IDX_IDS` (`DEPERTMENT_ID`,`DIVISION_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

AUTO_INCREMENTを使う際に気をつけるべきポイント

本テーブルのストレージエンジンとして利用するInnoDBでは、AUTO_INCREMENTのカウンターは永続的なデータとして持っておらず、メモリ上に保持しています。

そのため、DB再起動などが発生した場合、カウンターはリセットされます。

次のINSERTのときに、現存するレコードのAUTO_INCREMENTカラムの最大値を取ってきて、その＋１の値を設定してしまいます。

SELECT MAX(ai_col) FROM t FOR UPDATE;

レコードの物理削除を行うような環境においては、同一番号の再利用がされ、それによってデータの不整合が発生する可能性があるんですね。

AUTO_INCREMENTを使うときの対策

それではどういった対策が考えられるでしょうか？

MySQL8系にバージョンアップする

公式ドキュメントより、MySQL8系ではDB再起動が発生してもAUTO_INCREMENTのカウンターはリセットされないようになったみたいです！

AUTO_INCREMENTの値が変更されるたびにREDOログに書き込まれる仕組みになったということですね。

InnoDB enhancements. These InnoDB enhancements were added:
The current maximum auto-increment counter value is written to the redo log each time the value changes, and saved to an engine-private system table on each checkpoint. These changes make the current maximum auto-increment counter value persistent across server restarts.

今回は全社的なDB環境なのでバージョンアップはできませんでしたが、個人開発とかであれば、バージョンアップしてしまうのが良いと思います！

論理削除を採用する

物理削除が起こり得る状態をなくしてしまえばいいということで、論理削除を採用するのがもう一つの方法です。

今回、上長への申請は僕が実施しましたが、テーブル設計をしたのは他のベテランエンジニアの方で、事前に論理削除を想定したテーブル設計をしてくれていました。

以下のカラムが論理削除のためのカラムです。

`IS_DELETED` tinyint(4) NOT NULL DEFAULT '0',

物理削除は想定していない旨を上長に伝えたことで特にお咎めなしで承認されましたが、もし僕がテーブル設計をしていたら差し戻しを食らっていただろうなと思いました・・・

また一つ貴重な学びを得ることができました！！

AUTO_INCREMENTを使わないで採番するとしたら？

採番用テーブルを別途作ってしまうというのも一つの手かもしれません。

ただ排他制御とかトランザクション管理とか気をつけないといけないことはありそう・・・

なので、採番方法は要件やコストを鑑みて選定した方が良さそうですね！