enum列へのalter table - walf443's blog

巨大なtableへのalter tableが必要になったのだけど、enumへのalterって確か最適化してくれたんじゃなかったっけな、ということで、一応確認のため検証してみる

次のschemaを用意します。

            CREATE TABLE `enum_test` (
              `id` int(11) NOT NULL AUTO_INCREMENT,
              `type` enum('foo','bar') DEFAULT NULL,
              PRIMARY KEY (`id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=105135 DEFAULT CHARSET=utf8

次のようなデータを大量にINSERTするプログラムを書き、入れておきます。

            use strict;
            use warnings;
            use DBI;

            my $dbh = DBI->connect('dbi:mysql:test;', 'xxx', 'xxxx', { RaiseError => 1 });
            my $sth  = $dbh->prepare(q{ INSERT INTO enum_test (type) VALUES (?) });
            for (1..100000) {
                for my $type ( qw(foo bar) ) {
                    $sth->execute($type);
                }
            }

            mysql> select count(*) from enum_test;
            +----------+
            | count(*) |
            +----------+
            |   200000 |
            +----------+
            1 row in set (0.05 sec)
            mysql> alter table enum_test modify column type enum('foo', 'bar', 'baz');
            Query OK, 0 rows affected (0.05 sec)
            Records: 0  Duplicates: 0  Warnings: 0

            mysql> alter table enum_test modify column type enum('foo');
            Query OK, 200000 rows affected, 65535 warnings (2.09 sec)
            Records: 200000  Duplicates: 0  Warnings: 100000

            mysql> alter table enum_test add index type(type);
            Query OK, 200000 rows affected (2.71 sec)
            Records: 200000  Duplicates: 0  Warnings: 0

            mysql> alter table enum_test modify column type enum('foo', 'bar', 'baz');
            Query OK, 0 rows affected (0.06 sec)
            Records: 0  Duplicates: 0  Warnings: 0

            mysql> alter table enum_test add index type_id(type, id);

            Query OK, 200000 rows affected (2.81 sec)
            Records: 200000  Duplicates: 0  Warnings: 0

            mysql> alter table enum_test modify column type enum('foo', 'bar', 'baz', 'hoge');
            Query OK, 0 rows affected (0.05 sec)
            Records: 0  Duplicates: 0  Warnings: 0

ということで、このとおり、データに全く入っていないtypeを追加するのは、((複合)INDEXはってようがいまいが)ほぼ一瞬で実行することができます。
一方、既にデータが入っている部分を変更すると、(当然でしょうが)全データに対して実行するので、時間が非常にかかります。

ちなむと、barのデータが入っていたところはどうなるかというと、

            mysql> select * from enum_test limit 10;
            +----+------+
            | id | type |
            +----+------+
            |  1 | foo  |
            |  2 |      |
            |  3 | foo  |
            |  4 |      |
            |  5 | foo  |
            |  6 |      |
            |  7 | foo  |
            |  8 |      |
            |  9 | foo  |
            | 10 |      |
            +----+------+
            10 rows in set (0.00 sec)

こんな感じで、歯脱けになってしまいますね。

追記

mysql> alter table enum_test modify column type enum('foo', 'bar', 'baz', 'hoge', 'fuga', 'fugo', 'gee', 'gaa', 'rere', 'rara', 'garr', 'goo', 'arew', 'rweee');
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table enum_test modify column type enum('foo', 'baz', 'bar', 'hoge', 'fuga', 'fugo', 'gee', 'gaa', 'rere', 'rara', 'garr', 'goo', 'arew', 'rweee');
Query OK, 200000 rows affected (3.08 sec)
Records: 200000  Duplicates: 0  Warnings: 0

データがたくさん増えてきたときは、特には問題ないようです。
順番を入れかえてしまうと、数字を振りなおすことになってしまうので、データの書きかえが発生し、時間がかかってしまいます。

まとめ

enumへのalter tableは要素を追加するときに限っては、ほぼ一瞬で変更することができる。
enumは数値で格納され、追加されるデータが非常に小さくなるので、alter tableに時間がかかりそうということを恐れて、varcharに入れとくといったことをする必要性はないと思われる。

enumのカラムは用途にもよるが、INDEXを貼っても要素へのSELECT文のカーディナリティは高くないことも多いので、多態的に用い複数のtypeをまたいで取得といったことをしない場合は、enumに対してlinear partitionなどを適用するとよさげ。

参考

http://dev.mysql.com/doc/refman/5.1/en/alter-table.htmlの"Changing the definition of an ENUM or SET ..."あたりに書いてあるようです。

またまた追記

どうもうまくいかない環境があるなぁ、と思ったら、5.1.40以前の5.1をつかっていたようだ。。。
マニュアルにもマイナーバージョンで追加された機能に関しては、その記述があるとうれしいなぁ。。。(ChangeLog全部嫁って話かもしれないですが。

http://dev.mysql.com/doc/refman/5.1/en/news-5-1-40.htmlの"Appending values to an ENUM or SET definition..."あたり。