Database JUNKY

MySQL,MariaDBを中心としたブログです

MySQL: VIEW表を利用してORACLEやDB2のシノニムっぽいものを実現する方法

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