[MySQL]複合キーAUTO_INCREMENTのMyISAMをInnoDBに移行する

今回は敬語で書きます。ちょっと弱気です。深い理由はありません。
間違っているところがあったらビシバシご指摘ください。

やっとInnoDBが使える

MyISAMよりInnoDBのほうが優位な点が多くなってからだいぶ経つのですが、ウチでは夜間のバックアップをOSファイルコピーでやっていたため「MyISAM縛り」がありました。
最近になってmysqldumpを利用するよう変更するなどして、やっと縛りを取り払うことができました。
本格的にInnoDBを使い始めようと思ってるところです。

しかし、ただ単純にALTER TABLEしてしまえばよいわけではありません。
MyISAMからInnoDBへの移行についてはnippondanji氏のブログでも触れられていますが、いくつか注意すべき点があります。

漢(オトコ)のコンピュータ道: MyISAMからInnoDBへ切り替えるときの注意点

1年半以上まえのエントリですが大いに役に立つ情報です。
もちろんひとつずつおさらいしても面白いのですが、今回はここに記載のないネタについて。

突っ込まれる前に

MyISAMからInnoDBに変更すると色々できることも変わるため、どうせならアプリケーションは当然モデリングからしなおした方がベターなのはもちろん理解しています。
が、今回は「既存ですでにMyISAMで複合キーのAUTO_INCREMENTを使用して運用しているアプリケーションをいかに工数を少なくInnoDBに移行させるか」というところに主眼を置いた話です。
アプリケーションやテーブル構造の大幅な変更、さらにはモデリングしなおしなどはしない方向です。
「それじゃInnoDBにする意味がない（半減する）じゃないか」というご指摘はごもっともですね。
ただまぁ構造を変えることなく検索スピードが上がったり、必要に応じて外部キー制約をつけられるようになったりと、単純なメリットもありますからその辺は見逃してください。

今回のサンプルテーブル

以下のような3列のテーブルを想像してください。
列1： `key` varchar(10) PK
列2： `id` int(10) PK
列3： `data` varchar(100)

`key`(文字列)と`id`(数値)の組み合わせで主キーとします。
InnoDBで主キーにvarcharは・・・などの突っ込みは上記のとおりナシでお願いします。

複合キーでAUTO_INCREMENT

MyISAMではこのような複合キーでAUTO_INCREMENTが使用できます。
列2のidにAUTO_INCREMENTを指定したとして、例えば

+--------+----+------+ 
| key    | id | data |
+--------+----+------+
| iPad   |  1 |  aaa |
+--------+----+------+
| iPad   |  2 |  bbb |
+--------+----+------+
| iPhone |  1 |  aaa |
+--------+----+------+

この状態のテーブルに対して、

INSERT INTO `TABLE` (`key`, `id`, `data`) VALUES ('iPhone', null, 'ccc');

というSQLを投げると、

+--------+----+------+ 
| key    | id | data |
+--------+----+------+
| iPad   |  1 |  aaa |
+--------+----+------+
| iPad   |  2 |  bbb |
+--------+----+------+
| iPhone |  1 |  aaa |
+--------+----+------+
| iPhone |  2 |  ccc | ← 追加された行
+--------+----+------+

こうなります。

SELECT MAX(`id`) FROM `table` WHERE `key` = 'iPhone';

で取れる値に+1した値を自動的に使ってくれるのです。

ところがInnoDBではそもそも複合キーのテーブルではAUTO_INCREMENTが使用できません。
・複合キーを使ったInnoDBのテーブルにALTER TABLEでAUTO_INCREMENTを追加しようとするとエラーではじかれる。
・複合キー+AUTO_INCREMENTを使ったMyISAMのテーブルをALTER TABLEでInnoDBに変更しようとするとエラーではじかれる。

こうなるとInnoDBに移行する際にAUTO_INCREMENTをはずさなければなりません。
そして先ほどの

INSERT INTO `TABLE` (`key`, `id`, `data`) VALUES ('iPhone', null, 'ccc');

というSQLは当然無効になります。
nullではなく、リテラルを指定してあげる必要があるからです。

さて、モデリングや挙動をできるだけ変更しないとしたらどんな風に直せばよいでしょうか。

SQLだけ変更する

スムーズに移行するために自分が考えたのがSQLの変更です。
（O/Rマッパー的なものを使うからSQLを書かない人、規約・ポリシーでSQLを書けない人もいるかもしれませんがあしからず）

変更後のSQL文はこうです。

INSERT INTO `table` (`key`, `id`, `data`)
VALUES ('iPhone'
       ,(SELECT `tmp`.`max_id`
           FROM (SELECT COALESCE(MAX(`id`), 0) AS `max_id`
                   FROM `table`
                  WHERE `key` = 'iPhone'
                ) AS `tmp`
        ) + 1
       ,'ccc');

INSERTをすべてこのSQLにすれば擬似AUTO_INCREMENTを実装できます。

ポイントが二つ
・更新のサブクエリで同名のテーブルを指定するとエラーになるので、テンポラリのテーブルに囲んでます。
・MAX(`id`)がnullになることも当然ありえますので、COALESCE()でnullを0に変換しておきます。

SQL文１つの変更だけで済ませることができました。
（変数のバインドが2箇所に増えてますが、同じ変数を使う箇所なので大きな変更は不要でしょう）

削除済みの最大値の再利用

「ちょっと待て、MyISAMの場合AUTO_INCREMENTの次回値を保持していて、最大値が削除されても再利用されない仕組みだからこのSQLだと動きが違うじゃないか」
と思った方は鋭いですね。
鋭いけどちょっと違います。
確かにMyISAMのAUTO_INCREMENTでは次回値を別に保持しているので最大値が削除されても次回値は変わりません。
ところが、複合キーでAUTO_INCREMENTを使うとこの次回値が保持されないのです。
そして毎回最大値+1の値が使われるので、上記のSQLで同等の動きを実装できるのです。

おわりに

途中何度も「ニッチなネタかも」と思いながらも折角書き始めたので最後まで書き上げました。
まだまだ勉強不足ですのでおかしな記述や誤解などがあるかもしれません。
繰り返しますが、間違いの指摘は大歓迎です。（コメントやTwitterのmentionでいただければ幸いです。）

では素敵なMySQLライフを。