ちょっと仕事の関係で、とあるデータベースから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はできない仕様なので、これはこれでしょうがない
仕様なんです。