MySQL/Tips/MRG_MyISAM


MRG_MyISAM について

InnoDB のパーティショニングの性能向上により、利用頻度は下がった感はあるが、まだ使用している現場もあるため、覚書がわりに記載。

DROP順序による挙動の変化

下記のようなテーブルがあった場合、削除(drop)する順番による挙動の変化

+-----------------+
| TABLE_NAME      |
+-----------------+
| log_item        |
| log_item_201408 |
| log_item_201409 |
+-----------------+
  • マージテーブル(マージ定義)
    Everything is expanded.Everything is shortened.
      1
      2
      3
      4
      5
    
    -
    |
    |
    !
     
    
    CREATE TABLE `log_item` (
      ・
      ・
    ) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
    UNION=(`log_item_201408`,`log_item_201409`);
  • 分割テーブル1
    Everything is expanded.Everything is shortened.
      1
      2
      3
      4
    
    -
    |
    |
    !
    
    CREATE TABLE `log_item_201408` (
      ・
      ・
    ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  • 分割テーブル2
    Everything is expanded.Everything is shortened.
      1
      2
      3
      4
    
    -
    |
    |
    !
    
    CREATE TABLE `log_item_201409` (
      ・
      ・
    ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Case-1 分割テーブルを先に削除

分割テーブル1を削除

Everything is expanded.Everything is shortened.
  1
  2
 
 
mysql> drop table log_item_201408;
Query OK, 0 rows affected (0.00 sec)
+-----------------+
| TABLE_NAME      |
+-----------------+
| log_item        |
| log_item_201409 |
+-----------------+

マージテーブルに対して検索

Everything is expanded.Everything is shortened.
  1
  2
 
 
mysql> select id from log_item;
ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist

マージテーブルで log_item_201408 と log_item_201409 を対象にしていたが、log_item_201408 が無くなったためマージ状態が破綻した状態。
log_item のマージテーブルに対して SELECT の発行エラーとなった。

マージを定義修正(ALTER)してもエラー

Everything is expanded.Everything is shortened.
  1
  2
 
 
mysql> ALTER TABLE log_item UNION=(`log_item_201409`);
ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist

いったん削除(DROP)し再定義(CREATE)が必要。

Everything is expanded.Everything is shortened.
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
-
mysql> DROP TABLE log_item;
Query OK, 0 rows affected (0.00 sec)
 
mysql> CREATE TABLE `log_item` (
 ・
 ・
   -> ) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
   -> UNION=`log_item_201409`);
Query OK, 0 rows affected (0.03 sec)
 
mysql> select id from log_item;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)


Case-2 マージテーブルを先に削除

Everything is expanded.Everything is shortened.
  1
  2
 
 
mysql> drop table log_item;
Query OK, 0 rows affected (0.00 sec)
+-----------------+
| TABLE_NAME      |
+-----------------+
| log_item_201408 |
| log_item_201409 |
+-----------------+

マージの定義だけが削除されているため、分割テーブルは通常の MyISAM となるため、個々のテーブル(元分割テーブル)に SQL は発行可能。
言い換えれば、マージテーブルを組む前の状態 になっただけ。
このあたりの挙動が InnoDB のパーティショニングと違うところなので注意。

DUMPの挙動について

マージテーブルの仕組みを理解していれば、問題のないところ。
マージテーブルを組んだ際、mysqldump で dump すると実データはどうなるのだろうか? という挙動について検証。
テーブル構成は上記と同じ。

+-----------------+
| TABLE_NAME      |
+-----------------+
| log_item        |
| log_item_201408 |
| log_item_201409 |
+-----------------+
  • マージテーブル(マージ定義)
    Everything is expanded.Everything is shortened.
      1
      2
      3
      4
      5
    
    -
    |
    |
    !
     
    
    CREATE TABLE `log_item` (
      ・
      ・
    ) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
    UNION=(`log_item_201408`,`log_item_201409`);
  • 分割テーブル1
    Everything is expanded.Everything is shortened.
      1
      2
      3
      4
    
    -
    |
    |
    !
    
    CREATE TABLE `log_item_201408` (
      ・
      ・
    ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  • 分割テーブル2
    Everything is expanded.Everything is shortened.
      1
      2
      3
      4
    
    -
    |
    |
    !
    
    CREATE TABLE `log_item_201409` (
      ・
      ・
    ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
    個々のテーブルに1件ずつデータを挿入。
    Everything is expanded.Everything is shortened.
      1
      2
      3
      4
      5
      6
      7
      8
      9
     10
     11
     12
     13
     14
    
     
     
     
     
     
     
     
     
     
     
     
     
     
    -
    
    mysql> INSERT INTO log_item_201408 (`id`) VALUES (1);
    Query OK, 1 row affected (0.00 sec)
     
    mysql> INSERT INTO log_item_201409 (`id`) VALUES (1);
    Query OK, 1 row affected (0.00 sec)
     
    mysql> SELECT id FROM log_item;
    +----+
    | id |
    +----+
    |  1 |
    |  1 |
    +----+
     2 rows in set (0.00 sec)
    各テーブルのレコード件数は…
    +-----------------+------------+
    | TABLE_NAME      | TABLE_ROWS |
    +-----------------+------------+
    | log_item        |          2 |
    | log_item_201408 |          1 |
    | log_item_201409 |          1 |
    +-----------------+------------+
    この状態になる。

予想パターン

(1)マージテーブルの箇所にデータ 2件、分割テーブルの箇所にデータが 各1件 のSQLが生成される。
(2)マージテーブルの箇所にデータ 2件、分割テーブルの箇所にデータが 0件 のSQLが生成される。
(3)マージテーブルの箇所にデータ 0件、分割テーブルの箇所にデータが 各1件 のSQLが生成される。

マージテーブルの挙動を理解してる方、実際経験したことがある方は、正解できるだろう。
そうでない方に改めて、上記の3パターンを出されると深読みして戸惑うかもしれない。

結果

結果から言うと
(3)マージテーブルの箇所にデータ 0件、分割テーブルの箇所にデータが 各1件 のSQLが生成される。
である。
実際に dump してみると…。

Everything is expanded.Everything is shortened.
  1
 
$ mysqldump -u (ユーザー名) -p (データベース名) > TEST_MERGE.sql

出力された結果(必要箇所のみ記載)

Everything is expanded.Everything is shortened.
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
-
|
|
!
 
 
-
|
|
!
 
 
 
 
-
|
|
!
 
 
CREATE TABLE `log_item` (
  ・
  ・
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci UNION=(`log_item_201408`,`log_item_201409`);
 
 
CREATE TABLE `log_item_201408` (
  ・
  ・
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
INSERT INTO `log_item_201408` VALUES (1);
 
 
CREATE TABLE `log_item_201409` (
  ・
  ・
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
INSERT INTO `log_item_201409` VALUES (1);

マージテーブルを使ったデータベースに対して、正しく dump できていると言える。
(1)、(2)の場合、mysqldump を使ってバックアップしてもリカバリの際、正常にリカバリができないことになる。



Last-modified: 2015-04-23 (木) 18:46:48 (946d)