ちょっと仕事の関係で、とあるデータベースからIBM DB2からMySQLに移行する案件があり、その中で、 一部のデータベースがFEDEARATIONのみで構成されている特殊なものがあり、一応このままでMySQLの FEDERATION構成に移行したのですが。。。なんか気持ち悪い・・何が気持ち悪いって、 作成したこんな感じでFEDERATIONの数だけコネクションを張っている。怖い・・ 同じ筐体で今後も運用していくのであれば、FEDERARTION表を作成するより、VIEW表で対応したほうが
リソースの無駄遣いを軽減することができると思い、ちょっとFEDERARTIONをVIEWに切り替えて運用でき るか検証してみました。
[SQL]
mysql> show processlist; +-------+--------+-----------------+----------+---------+--------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +-------+--------+-----------------+----------+---------+--------+-------+------------------+ | 7320 | fedusr | 127.0.0.1:38964 | db1 | Sleep | 4592 | | NULL | | 7321 | fedusr | 127.0.0.1:38965 | db1 | Sleep | 4592 | | NULL | | 7322 | fedusr | 127.0.0.1:38966 | db1 | Sleep | 4592 | | NULL | | 7323 | fedusr | 127.0.0.1:38967 | db1 | Sleep | 4592 | | NULL | | 7324 | fedusr | 127.0.0.1:38968 | db1 | Sleep | 4592 | | NULL | | 7325 | fedusr | 127.0.0.1:38969 | db1 | Sleep | 4592 | | NULL | | 7326 | fedusr | 127.0.0.1:38970 | db1 | Sleep | 4592 | | NULL | | 7327 | fedusr | 127.0.0.1:38971 | db1 | Sleep | 4592 | | NULL | | 7328 | fedusr | 127.0.0.1:38972 | db1 | Sleep | 4592 | | NULL | | 7329 | fedusr | 127.0.0.1:38973 | db1 | Sleep | 4592 | | NULL | | 7330 | fedusr | 127.0.0.1:38974 | db1 | Sleep | 4592 | | NULL | | 7331 | fedusr | 127.0.0.1:38975 | db1 | Sleep | 4592 | | NULL | | 7332 | fedusr | 127.0.0.1:38976 | db1 | Sleep | 4592 | | NULL | | 7333 | fedusr | 127.0.0.1:38977 | db2 | Sleep | 4592 | | NULL | | 7334 | fedusr | 127.0.0.1:38978 | db3 | Sleep | 4592 | | NULL | | 7335 | fedusr | 127.0.0.1:38979 | db3 | Sleep | 4592 | | NULL | | 7336 | fedusr | 127.0.0.1:38980 | db1 | Sleep | 4592 | | NULL | | 7337 | fedusr | 127.0.0.1:38981 | db1 | Sleep | 4592 | | NULL | | 7338 | fedusr | 127.0.0.1:38982 | db1 | Sleep | 4592 | | NULL | | 7339 | fedusr | 127.0.0.1:38983 | db1 | Sleep | 4592 | | NULL | | 7340 | fedusr | 127.0.0.1:38984 | db1 | Sleep | 4592 | | NULL | | 7341 | fedusr | 127.0.0.1:38985 | db1 | Sleep | 4592 | | NULL | | 7342 | fedusr | 127.0.0.1:38986 | db1 | Sleep | 4592 | | NULL | [/SQL] 以下、検証の証跡です * 参照元のスキーマを作成し、サンプルのテーブルも作成してみる [SQL] mysql> create database viewtest; Query OK, 1 row affected (0.00 sec) mysql> mysql> use viewtest; Database changed mysql> create table t1 (id int,detail varchar(100)); Query OK, 0 rows affected (0.06 sec) mysql> show create table t1; +-------+----------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `detail` varchar(100) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+----------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) [/SQL] * 参照先のデータベースを作成し、参照元のテーブルt1をview表にする、また、view表に対して、INSERT VIEWを発行してみる [SQL] mysql> create database testdb; Query OK, 1 row affected (0.00 sec) mysql> use testdb; Database changed mysql> create view vt1 as select * from viewtest.t1; Query OK, 0 rows affected (0.06 sec) mysql> insert into vt1 values (1,'A'),(2,'B'),(3,'C'); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from vt1; +------+--------+ | id | detail | +------+--------+ | 1 | A | | 2 | B | | 3 | C | +------+--------+ 3 rows in set (0.01 sec) [/SQL] ※ これがいわゆる「更新可能VIEW表」ってやつです。もっと細かい設定ができると思いますが、 今回は未検証です。 * 当然参照用のスキーマテーブルにも値は入る [SQL] mysql> use viewtest; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from t1; +------+--------+ | id | detail | +------+--------+ | 1 | A | | 2 | B | | 3 | C | +------+--------+ 3 rows in set (0.00 sec) [/SQL] * では、参照元のテーブルをDROPした場合はどうなるのか? [SQL] mysql> use viewtest Database changed mysql> drop table t1; Query OK, 0 rows affected (0.02 sec) mysql> show tables; Empty set (0.00 sec) [/SQL] * 参照先のview表はこんなエラーになります。 [SQL] mysql> use testdb Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +------------------+ | Tables_in_testdb | +------------------+ | vt1 | +------------------+ 1 row in set (0.00 sec) mysql> select * from vt1; ERROR 1356 (HY000): View 'testdb.vt1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysql> show create table vt1; [/SQL] * では、この状態で、参照元のテーブルを再度構築しなおしたどうなるのか? [SQL] mysql> create table viewtest.t1 (id int,detail varchar(100)); Query OK, 0 rows affected (0.01 sec) mysql> insert into vt1 values (1,'A'),(2,'B'),(3,'C'); Query OK, 3 rows affected (0.03 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from vt1; +------+--------+ | id | detail | +------+--------+ | 1 | A | | 2 | B | | 3 | C | +------+--------+ 3 rows in set (0.01 sec) [/SQL] ※ db2だと、参照元の表をdropした場合、参照しているviewは、”作動不能マーク”をつけられるのですが、 MySQLの場合は特に問題ないみたいです。 * おまけ(view表で参照元に設定したインデクスは適用されるのか?) [SQL] mysql> use testdb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> explain select * from vt1 where id = 2; +----+-------------+-------+------+---------------+-------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+-------+---------+-------+------+-------------+ | 1 | SIMPLE | t1 | ref | t1idx | t1idx | 5 | const | 1 | Using where | +----+-------------+-------+------+---------------+-------+---------+-------+------+-------------+ 1 row in set (0.02 sec) [/SQL] ※参照元のテーブルのインデクスが適用されました。軽く感動!! 結果からいうと、 ・同じ筐体 ・同じインスタンス内 ・複数のスキーマ(データベース) の条件下でシノニムっぽいことをするのであれば、FEDERATIONよりVIEWで対応したほうがよさげ。ちなみに、 これは、DB2のFEDERATIONがイケてないという話ではなく、DB2の場合は、そもそも同一インスタンス内に 複数のデータベースがあっても、基本スキーマ間のJOINはできない仕様なので、これはこれでしょうがない 仕様なんです。