読者です 読者をやめる 読者になる 読者になる

VPストレージエンジンをいじめる

mysql spider

 テーブルが複数になった場合に気になるのがその整合性。子テーブル側のデータを直接いじって、
VPストレージエンジンがどのような挙動をするか、確認してみました。
とりあえず今回は、子テーブル2つに分割されたものを例にしていますが基本的には3つ以上でも同様かと思います。


重複カラムが別々の値を持っている場合

 カラム c02 はテーブル a1, a2 両方にあるので、片方の c02カラムの値だけを変更してみました。
以下は a1 の c02 を "NEWCOL2" に変更したもの。

mysql> UPDATE a1 SET c02="NEWCOL2" WHERE id=10;

mysql> SELECT * FROM aall; SELECT * FROM a1; SELECT * FROM a2;
+----+------+---------+------+------+------+
| id | c01  | c02     | c03  | c04  | c05  |
+----+------+---------+------+------+------+
| 10 | col1 | NEWCOL2 | col3 | col4 | col5 |
| 20 | colA | colB    | colC | colD | colE |
+----+------+---------+------+------+------+

+----+------+---------+------+
| id | c01  | c02     | c03  |
+----+------+---------+------+
| 10 | col1 | NEWCOL2 | col3 |
| 20 | colA | colB    | colC |
+----+------+---------+------+

+----+------+------+------+
| id | c02  | c04  | c05  |
+----+------+------+------+
| 10 | col2 | col4 | col5 |
| 20 | colB | colD | colE |
+----+------+------+------+

 親テーブル(VPテーブル)では変更したほうの値が id=10 の c02 として表示されていますね。
念のため、逆を見るために id=20 の テーブルa2 のほうの c02 を変更してみます。

mysql> UPDATE a2 SET c02="NEWCOLB" WHERE id=20;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM aall; SELECT * FROM a1; SELECT * FROM a2;
+----+------+---------+------+------+------+
| id | c01  | c02     | c03  | c04  | c05  |
+----+------+---------+------+------+------+
| 10 | col1 | NEWCOL2 | col3 | col4 | col5 |
| 20 | colA | colB    | colC | colD | colE |
+----+------+---------+------+------+------+

+----+------+---------+------+
| id | c01  | c02     | c03  |
+----+------+---------+------+
| 10 | col1 | NEWCOL2 | col3 |
| 20 | colA | colB    | colC |
+----+------+---------+------+

+----+---------+------+------+
| id | c02     | c04  | c05  |
+----+---------+------+------+
| 10 | col2    | col4 | col5 |
| 20 | NEWCOLB | colD | colE |
+----+---------+------+------+

 子テーブルでは "NEWCOLB" になっていますが、親テーブルでは colB のまま(a1テーブルのほうの値)になっています。
VPを構成するテーブルリストが a1 a2 の順なので、先勝ちだと思って良さそうですね。
特に warning 等は出ていませんでした。


PKに対応するカラムが片方のテーブルにしかない場合

 3レコードあったほうが対照しやすいので、まず以下を追加します。

INSERT INTO aall VALUES (30, "AAA", "BBBB", "CCCCC", "DD", "EEEEE");

 テーブルa1から ID=20 を、テーブルa2から ID=30 を削除して、内容を確認します

mysql> DELETE FROM a1 WHERE id=20;
mysql> DELETE FROM a2 WHERE id=30;
mysql> SELECT * FROM aall; SELECT * FROM a1; SELECT * FROM a2;
+----+------+---------+------+------+------+
| id | c01  | c02     | c03  | c04  | c05  |
+----+------+---------+------+------+------+
| 10 | col1 | NEWCOL2 | col3 | col4 | col5 |
+----+------+---------+------+------+------+

+----+------+---------+-------+
| id | c01  | c02     | c03   |
+----+------+---------+-------+
| 10 | col1 | NEWCOL2 | col3  |
| 30 | AAA  | BBBB    | CCCCC |
+----+------+---------+-------+

+----+---------+------+------+
| id | c02     | c04  | c05  |
+----+---------+------+------+
| 10 | col2    | col4 | col5 |
| 20 | NEWCOLB | colD | colE |
+----+---------+------+------+

 あらー。両方そろっていないと親テーブルを構成できないのですね。
パーティションを構成する先頭のテーブルを基軸として結果が構成されているのかなと思っていました。


PKをはずした場合

 削除したレコードをまた全部戻します(全削除してから再度2件をINSERTしました)。
子テーブルa2のプライマリーキーを削除して、テーブルの内容を見てみます。

mysql> ALTER TABLE a2 DROP PRIMARY KEY;
Query OK, 2 rows affected (0.23 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM aall; SELECT * FROM a1; SELECT * FROM a2;
ERROR 14514 (HY000): Can't correspond PK 'a2'

+----+------+------+------+
| id | c01  | c02  | c03  |
+----+------+------+------+
| 10 | col1 | col2 | col3 |
| 20 | colA | colB | colC |
+----+------+------+------+

+----+------+------+------+
| id | c02  | c04  | c05  |
+----+------+------+------+
| 10 | col2 | col4 | col5 |
| 20 | colB | colD | colE |
+----+------+------+------+

ERROR 14514 が表示されました。PKがあかん、と。


ALTER TABLE a2 ADD PRIMARY KEY (id); で再度PKを設定すれば、また親テーブル aall の内容も見えるようになります。

一対多

 一対多をやってみたらどうなるかな、とわくわくしていたのですが、冷静に考えてみれば PK で結合している以上は1:1にしかなり得ないのですね。
(いじめるという観点で)ちょっと残念。


まとめっぽいこと

複数の子テーブルに重複した値を持つときには、子テーブルの更新は注意が必要(同じ値に更新する必要があるので、事情がない限り、親テーブルで更新しておいたほうが無難)
・万一複数子テーブルにもたれる同一カラムのうち、ひとつのテーブルの値を変更して不整合にしてしまった場合は、VPの構成テーブルリストとして前側で指定したテーブルの値が採用されるっぽい。
・1カ所にしかない子テーブルのカラムは自由に更新できそう
・子テーブルのPKは基本的にいじっちゃだめ。
・親テーブルでPK変更するのはOK((データ整合性の観点から。パフォは別の話))。子テーブルすべてのPKも一緒に更新されます。ここで「Changed: 1」がなにげに渋いですね。

mysql> UPDATE aall SET id=15 WHERE id=10;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0


追記:
 余談ですが、久々に Windows コマンドライン上でさくっと mysqld を立ち上げたら終了方法がすぐに判らなくて焦りました。
 mysqladmin -uroot stop
だと思い込んでいて、レプを止める(もともと動いていない)だけの指示をしたり、stop のかわりに kill とやってmysql内の接続プロセスを停止させようとしたり(idの指定が必要)。正解は
mysqladmin -uroot shutdown
でした。

.