それはある日のこと。
とある事情で、テーブルを新しく作り直すことになった。
その際に、テーブルのデータを新しいテーブルに移す必要があったのだが、
その時には気が付かなく、後から気が付いたことがあったので、それのメモ。
テーブルのデータを移す
テーブル全体を他のテーブルに移すために、今回はinsert selectを使った。
insert into 新しいテーブル名 (新しいテーブルのカラム幾つか) select 元のテーブルの対応するカラム from 元のテーブル名;
数万程度のデータであれば、秒もかからずに移行できる。とても簡単。
auto_incrementの値が変わる
今回移行した時、auto_incrementを指定したカラムはinsert時に指定せず、insertにより自動で再度振り分けてもらった。
データ数は変わらず、データの削除は論理削除を行っていたので、auto_incrementの値は連番であった。
そのため、移行後のテーブルのauto_incrementの値は、移行前のテーブルと同じだった。
問題は、ここから新しくデータを追加した時。
auto_incrementの値は、移行前のテーブルの最後の値から振り分けられると思っていたが、そうではなかった。
テーブルによって値が変わっていたのだが、どれも最後の値の次に来る2の累乗の値になっていた。
例えば、データが20000件あったとすれば、20000の次に来る値は32768、という感じに飛んでいた。
原因
innodb_autoinc_lock_modeの値が1になっていたため。
insertの種類
innodb_autoinc_lock_modeの説明をする前に、insertの種類を説明する。
insertには、以下の3種類がある。
- 単純挿入 ... 挿入行数が事前に決まっているもの
- 連続挿入 ... 挿入行数が事前に決まっていないもの(insert selectはここ)
- 混在モード挿入 ... 単純挿入だが、データによってはinsertではなくupdateに変わるデータが存在するもの
innodb_autoinc_lock_mode とは
あんまり理解していないので、正確な説明は他の参考サイト等を見ていただきたいのだが、
ざっくり言えば、auto_incrementをロックする方法の指定方法。
0の場合は、AUTO-INCロックというロックを行い、
(ここから曖昧部分)auto_incrementを発行するinsertに対して、テーブルロックをかけ、予測可能な範囲で順次auto_incrementを発行する。
そのため、値は連番になる。
ただ、ステートメント間でロックするため、2つのinsert文が実行された場合は、もう片方は待たされる。
1の場合は、単純挿入ではAUTO-INCロックを行わず、軽量ロックにより、事前に必要な分だけauto_incrementを発行する。
連続挿入か混在モード挿入ではAUTO-INCロックを行う。
ただ、混在モード挿入では、挿入される行数よりも多くのauto_incrementを発行するため、余分なauto_incrementが発行される。
設定には2も存在するが、今回は範囲外だったので省略(決して内容が理解できていないから、といった理由では・・・)
今回の場合
SELECT @@innodb_autoinc_lock_mode;で現在の設定を確認できるが、今回は1になっていた。
ただ、insert selectは連続挿入で、innodb_autoinc_lock_modeの値が1の場合で値が飛ぶのは混在モード挿入の場合と記述されている。
ここがいまいち理解できていない部分なのだが、調べてみると他にもこの現象の人がいるため、おそらくこれが原因だと思われる。
終わりに
今回、この現象に気がついたのが、移行後そこそこ時間が経ってからだったので、今あるデータはそのままにすることにした。
次回以降insert selectを使う際は、気をつけようと思う。
参考サイト
SELECT+INSERTでAUTO_INCREMENTの値が増える (飛ぶ) - Qiita
INSERTステートメントの最適化について(MySQL InnoDBストレージエンジン) - Qiita
MySQL :: MySQL 8.0 リファレンスマニュアル :: 15.6.1.6 InnoDB での AUTO_INCREMENT 処理