GA

2022/03/30

MySQL標準のEXPLAIN(format=TRADITIONAL)とEXPLAIN format=TREEを並べて見つめてみる

いつまで経ってもformat=TREEに慣れないので、メモしつつ勉強してみる。

  • MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.8.2 EXPLAIN ステートメント

    mysql80 8> EXPLAIN SELECT * FROM t1, t2, (SELECT SUBSTRING(val, 1, 1) AS f, SUM(num) FROM t3 GROUP BY f) AS tmp_t3 WHERE t1.num IN (SELECT num FROM t5 WHERE num = (SELECT MAX(num) FROM t4) AND val LIKE '%');
    +----+-------------+------------+------------+-------+---------------+------+---------+-------+--------+----------+-------------------------------+
    | id | select_type | table      | partitions | type  | possible_keys | key  | key_len | ref   | rows   | filtered | Extra                         |
    +----+-------------+------------+------------+-------+---------------+------+---------+-------+--------+----------+-------------------------------+
    |  1 | PRIMARY     | t1         | NULL       | const | num           | num  | 8       | const |      1 |   100.00 | NULL                          |
    |  1 | PRIMARY     | t5         | NULL       | const | num           | num  | 8       | const |      1 |   100.00 | NULL                          |
    |  1 | PRIMARY     | t2         | NULL       | index | NULL          | val  | 131     | NULL  | 982049 |   100.00 | Using index                   |
    |  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL | NULL    | NULL  | 996250 |   100.00 | Using join buffer (hash join) |
    |  4 | SUBQUERY    | NULL       | NULL       | NULL  | NULL          | NULL | NULL    | NULL  |   NULL |     NULL | Select tables optimized away  |
    |  2 | DERIVED     | t3         | NULL       | index | val           | val  | 131     | NULL  | 996250 |   100.00 | Using index; Using temporary  |
    +----+-------------+------------+------------+-------+---------------+------+---------+-------+--------+----------+-------------------------------+
    6 rows in set, 1 warning (0.00 sec)
    

いわゆるフツーのEXPLAIN。

  • t3を含んだサブクエリ (SELECT SUBSTRING(val, 1, 1) AS f, SUM(num) FROM t3 GROUP BY f) AS tmp_t3 が最初に処理
  • SELECT MAX(num) FROM t4 のサブクエリがテーブルアクセス抜き ( Select tables optimized away )で処理(optimized awayが発生するのはMIXまたはMAXなのでテーブル名まで決まり)
  • SUBQUERYが1か所しかないので、 (SELECT num FROM t5 WHERE num = (ここは処理済み) AND val LIKE '%') はJOINに書き換えられている
  • t1から1行フェッチして SELECT num FROM t5 WHERE num = (ここは処理済み) AND val LIKE '%' が1行フェッチされる -> t2が1行フェッチされる -> (SELECT SUBSTRING(val, 1, 1) AS f, SUM(num) FROM t3 GROUP BY f) AS tmp_t3 (これがid:2なので と表示される) が1行フェッチされる
  • ↑のステップがt1の全レコードが終わるまでループ

「idの数字の大小に関係なく、最後の行から解釈する(ただしDEPENDENT SUBQUERYが例外)」
「idが同じものはNested Loop Joinで、idが同じ行のうち先頭の行から解釈する」

あたりで一般化できるか。
OK、これは読める、日本語で説明できた。じゃあ format=TREE。

mysql80 8> EXPLAIN format=TREE SELECT * FROM t1, t2, (SELECT SUBSTRING(val, 1, 1) AS f, SUM(num) FROM t3 GROUP BY f) AS tmp_t3 WHERE t1.num IN (SELECT num FROM t5 WHERE num = (SELECT MAX(num) FROM t4) AND val LIKE '%')\G
*************************** 1. row ***************************
EXPLAIN: 
-> Inner hash join (no condition)  (cost=6556388.62 rows=0)
    -> Table scan on tmp_t3  (cost=2.50..2.50 rows=0)
        -> Materialize  (cost=2.50..2.50 rows=0)
            -> Table scan on <temporary>
                -> Aggregate using temporary table
                    -> Index scan on t3 using val  (cost=103631.25 rows=996250)
    -> Hash
        -> Index scan on t2 using val  (cost=104645.03 rows=982049)

1 row in set (0.00 sec)

全然読めない…が、t3を含んだサブクエリが最初なので、最初だったけどOracle流の読み方としてはここから読むので正しいらしい…(がそうするとますますMySQLのformat=TREEって間違ってるんじゃ…?) Index scan on t3 using val (cost=103631.25 rows=996250) が最初、これはderivedになるので Aggregate using temporary table と Table scan on <temporary> と Materialize が Using temporary の意味か。

Table scan on tmp_t3 はTRADITIONALの table: <derived2>, type: ALL に相当しそう。

あとはもう Inner hash join (no condition) でt1, t5に関する言及は無くてひとまとまり、 Index scan on t2 using val でt2に言及はあるけど、ここから t1, t5, t2, tmp_t3の順だと字面だけで判断するのは俺には無理な気がする…。

(t1, t5を自明なInnser hash joinだと割り切れば、tmp_t3とHashが同じインデント位置にいるから後ろから読めば良いってことなのかな…)

mysql80 18> EXPLAIN ANALYZE SELECT * FROM t1, t2, (SELECT SUBSTRING(val, 1, 1) AS f, SUM(num) FROM t3 GROUP BY f) AS tmp_t3 WHERE t1.num IN (SELECT num FROM t5 WHERE num = (SELECT MAX(num) FROM t4) AND val LIKE '%')\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (no condition)  (cost=6556388.62 rows=0) (actual time=4058.502..5291.463 rows=16000000 loops=1)
    -> Table scan on tmp_t3  (cost=2.50..2.50 rows=0) (actual time=0.001..0.004 rows=16 loops=1)
        -> Materialize  (cost=2.50..2.50 rows=0) (actual time=3334.956..3334.961 rows=16 loops=1)
            -> Table scan on <temporary>  (actual time=0.004..0.008 rows=16 loops=1)
                -> Aggregate using temporary table  (actual time=3334.335..3334.340 rows=16 loops=1)
                    -> Covering index scan on t3 using val  (cost=103631.25 rows=996250) (actual time=1.751..893.790 rows=1000000 loops=1)
    -> Hash
        -> Covering index scan on t2 using val  (cost=104645.03 rows=982049) (actual time=2.126..543.544 rows=1000000 loops=1)

1 row in set (8.78 sec)

EXPLAIN ANALYZEならテーブル名も出るかなと思ったけどそんなことは無かった。

2022/03/15

MySQL Shellのプロンプトをゼロからカスタマイズしてみんとす(変数の存在によって表示させるclassを変えるとか)

TL;DR


謎だったのだ。パッケージに一緒についてくる /usr/share/mysqlsh/prompt/ あたりに転がっているprompt.jsonのこのへんの記述。

  "segments": [
..
    {
      "classes": ["noschema%schema%", "schema"],
      "bg": 242,
      "fg": 15,
      "shrink": "ellipsize",
      "weight": -1,
      "padding" : 1
    },
..

https://github.com/mysql/mysql-shell/blob/8.0.28/samples/prompt/prompt_256pl.json#L103

↓こんな風に、スキーマがセットされてる時は灰色の、そうでない時はそもそもこのラベルそのものが表示されないような感じ。

しかし noschema と schema はクラスとして定義されているが、 noschemad1 なんてクラスは定義されていないし、とするとこのクラス名の中の %schema% はスキーマ名として展開される組み込み変数とは違うのか…? とか思ってたら、READMEに書いてあった。

README最高だな ドキュメントに書けよ 。

https://github.com/mysql/mysql-shell/blob/master/samples/prompt/README.prompt#L94-L96

クラスのリスト(たとえこの空振りを利用した打ち分けをしない場合でも、リストに入れないといけない)は、最初に存在した1つが採用される。

さっきのスキーマの例でいくと、d1スキーマがカレントスキーマの間は noschemad1 クラスを探して存在せず、 schema クラスにフォールバックする。
カレントスキーマが存在しない間は noschema クラスが存在するのでそっちを採用する。

というわけで、prompt.jsonをこんな風にしてみた。

{
  "classes":
  {
    "schema_mysql":
    {
      "text": "[[ !!SYSTEM_SCHEMA!! ]]",
      "fg": "white",
      "bg": "red"
    },
    "noschema":
    {
      "text": "[[ no database selected ]]"
    },
    "default_schema":
    {
      "text": "[[ %schema% ]]",
      "fg": "#b0c4de"
    }
  },
  "segments":
  [
    { "classes": ["noschema%schema%", "schema_%schema%", "default_schema"] }
  ],
  "prompt": { "text": "> " }
}

まあ取り敢えず、思ったようには動いているようだ( %schema% が空の時は “noschema” にマッチ、 mysqlの時は “schema_mysql” にマッチ、それ以外の時は “default_schema” にフォールバック)

variablesが matchif_trueif_false の2分割しかできないから、3つ以上の分岐をしたい時にはこっちのが良いのかな、と思いつつ。

2022/03/13

アプリケーションだけで垂直シャーディングのための思考実験

前提条件

  • 1つのテーブルをdb1からdb2に移動する
    • テーブルは数百GB~1桁TB、「メンテに入れてエクスポート」の手が取れないくらいを想定
    • またこのテーブルをレプリケーションする手も取れないとする
  • テーブルにはauto_incrementなPrimary Key、それとは別にUnique Keyがあるが、Foreign Keyはないものとする
    • FKあるとだいたい外に括り出すわけにいかないし
  • テーブルにはINSERTもUPDATEもDELETEも来るが、updated_at的なものはない

まずdb2に、オリジナルのテーブルと同じ空っぽのテーブルを作る。

CREATE TABLE `some_table` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `something_hash` varchar(32) COLLATE utf8mb4_ja_0900_as_cs NOT NULL,
  `create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `something_hash` (`something_hash`),
  UNIQUE KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ja_0900_as_cs;

アプリのこのテーブルからの検索部分を「db2から引く、なければdb1から引く」に変更。

my $dbh_old= DBI->connect("dbi:mysql:;host=db1");
my $dbh_new= DBI->connect("dbi:mysql:;host=db2");

my $ret= $dbh_new->selectrow_hashref("SELECT .. FROM some_table WHERE ..");
$ret= $dbh_old->selectrow_hashref("SELECT ..") if (!($ret));

この時点ではdb2のテーブルは空なので、常にdb1から引く。結果はかわらない。


次にINSERTを切り替える。書くときは両方に書く(でないとUNIQUE制約が満たせるかどうかがわからない)

$dbh_new->begin_work;
$dbh_old->begin_work;

eval
{
  $dbh_new->do("INSERT INTO some_table ..");
  $dbn_old->do("INSERT INTO some_table ..");
};

if ($@)
{
  ### 新側でユニーク制約違反、または旧側でユニーク制約違反になればロールバックして、普段のユニーク制約違反の時の処理に突入すれば良い。
  $dbh_new->rollback;
  $dbh_old->rollback;
}

$dbh_new->commit;
### ここでAppに何か起こるとズレる
$dbh_old->commit;

コミットする順番は重要で、先にコミットが終わってから次をコミットするまでの間にアプリケーションに何かあるとズレる。
そのため、先にdb2の方をコミットしておくようにすると、「ズレたとしてもdb2の方が正しいはず、そして読むのはdb2が先で、無かった時だけdb1を読みにいく」ようになる。

(ただし後者のコミットが失敗した時点でユーザーにはエラー画面が返りそうなので、エラーになったのに更新されているという嫌な状態であることには間違いはない)

この状態だと、新たにINSERTされたレコードはdb2からだけ読まれるようになるはず。


DELETEの切り替え。これもINSERTと同じように両方にかける。

$dbh_new->begin_work;
$dbh_old->begin_work;

eval
{
  $dbh_new->do("DELETE FROM some_table ..");
  $dbn_old->do("DELETE FROM some_table ..");
};

if ($@)
{
  $dbh_new->rollback;
  $dbh_old->rollback;
}

$dbh_new->commit;
### ここでAppに何か起こるとズレる
$dbh_old->commit;

これはnew, oldどっちを先に消しても、「何か起こるとズレる」ポイントで何か起こると消えてないように見える。まあ、ユーザーにはエラー画面が返っているはずなので自然ではある。よし。
(どちらか片方でも残っていれば、db2を読んで返すかdb2を空振りしてdb1を読んで返すかしちゃうから)


UPDATE。もともとのUPDATE文から INSERT INTO .. VALUES .. ON DUPLICATE KEY UPDATE .. に書き換える。

後から「ゆっくり更新されなかったデータをマージする」の処理(後述)が必要になるけれど、その時単に両方にUPDATEをかけるだけだと、「既に移動済みとマークされたレコードの重複判定が効かない」から。

mysql80 16> SELECT * FROM some_table;
+----+---------+----------------+---------------------+
| id | user_id | something_hash | create_at           |
+----+---------+----------------+---------------------+
|  1 |       1 | b              | 2022-03-13 15:56:42 |
|  2 |       2 | c              | 2022-03-13 15:57:35 |
+----+---------+----------------+---------------------+
2 rows in set (0.00 sec)

1インスタンスにある間、このレコードに対する UPDATE some_table SET something_hash = 'b' WHERE user_id = 2 は転けるので、id = 1がdb1, id = 2がdb2と分かれてしまった時でもコイツを保証してやりたい。

INSERT some_table VALUES(NULL, 2, 'b', NOW()) ON DUPLICATE KEY UPDATE something_hash = 'b' なら

### db1側
mysql80 16> SELECT * FROM t2;
+----+---------+----------------+---------------------+
| id | user_id | something_hash | create_at           |
+----+---------+----------------+---------------------+
|  1 |       1 | b              | 2022-03-13 15:56:42 |
+----+---------+----------------+---------------------+
1 row in set (0.00 sec)

mysql80 16> INSERT t2 VALUES(NULL, 2, 'b', NOW()) ON DUPLICATE KEY UPDATE something_hash = 'b';
Query OK, 0 rows affected (0.00 sec)    <-- エラーにはならないけど 0 rows affectedで成功しなかったことはわかる。

mysql80 16> SELECT * FROM t2;
+----+---------+----------------+---------------------+
| id | user_id | something_hash | create_at           |
+----+---------+----------------+---------------------+
|  1 |       1 | b              | 2022-03-13 15:56:42 |
+----+---------+----------------+---------------------+
1 row in set (0.00 sec)

### db2側
mysql80 16> SELECT * FROM t2;
+----+---------+----------------+---------------------+
| id | user_id | something_hash | create_at           |
+----+---------+----------------+---------------------+
|  2 |       2 | c              | 2022-03-13 15:57:35 |
+----+---------+----------------+---------------------+
1 row in set (0.00 sec)

mysql80 16> INSERT t2 VALUES(NULL, 2, 'b', NOW()) ON DUPLICATE KEY UPDATE something_hash = 'b';
Query OK, 2 rows affected (0.00 sec)   <-- non-zero rows affected

mysql80 16> SELECT * FROM t2;
+----+---------+----------------+---------------------+
| id | user_id | something_hash | create_at           |
+----+---------+----------------+---------------------+
|  2 |       2 | b              | 2022-03-13 15:57:35 |
+----+---------+----------------+---------------------+
1 row in set (0.00 sec)

とまだ区別がつく。
UPDATE2つだと、db1側は空振りするだけで「カブったからダメ」なのかどうかの区別がつかないのでこの INSERT INTO .. ON DUPLICATE KEY UPDATE .. が良いんじゃないかなと思う。

ちなみに REPLACE INTO .. に書き換えようとすると、複数のユニーク制約に同時にカブった時に容赦なく消される( INSERT INTO .. ON DUPLICATE KEY UPDATE .. は複数のユニーク制約に同時にカブるとエラーになる)ので期待と違う動きになるはず。

$dbh_new->begin_work;
$dbh_old->begin_work;
my ($new_affected_rows, $old_affectd_rows);

eval
{
  $new_affected_rows= $dbh_new->do("INSERT INTO some_table .. ON DUPLICATE KEY UPDATE ..");
  $old_affected_rows= $dbn_old->do("INSERT INTO some_table .. ON DUPLICATE KEY UPDATE ..");
};

### affected_rowsが0だったらユニーク制約違反だと思うのでロールバック
if ($@ || $new_affected_rows == 0 || $old_affected_rows == 0)
{
  $dbh_new->rollback;
  $dbh_old->rollback;
}

$dbh_new->commit;
### ここでAppに何か起こるとズレる
$dbh_old->commit;

このパターンで「何か起こるとズレる」ポイントはあまり放置できない。ユニークな要素が書き変わっている可能性があるので、db1とdb2の間で上手くリカバってやらないとおかしなことになるはず。

色々考えてみたけれど、「commitの代わりにkafkaに積む」くらいしか思いつかなかった。


最後に、INSERTもUPDATEもDELETEもされずにそのまま放っておくとdb1にしかレコードがないやつをdb2に動かすバッチ。
これは単に INSERT INTO .. (NOT IGNORE)とかで移して、移したあとに消せば良いと思う。

$dbh_new->begin_work;
$dbh_old->begin_work;

my $buff= $dbh_old->fetchall_arrayref("SELECT * FROM some_table WHERE user_id BETWEEN ? AND ?");

eval
{
  $dbh_new->do("INSERT INTO some_table ..");
  $dbn_old->do("DELETE FROM some_table ..");
};

if ($@ && $dbh_new->{mysqld_errno} == 1062 && $dbh_new->{mysql_error} =~ /for key 'PRIMARY'/)   ### ここのエラーメッセージは実物と違うかも
{
  ### Error: 1062がどのインデックスでぶつかってエラーになったのかはハンドリングが必要だと思う。
  $dbh_new->rollback;
  $dbh_old->rollback;
}

$dbh_new->commit;
### ここでAppに何か起こるとズレるが、次に同じ処理をした時にdb2を優先して転ぶだけなのでリトライすれば済むはず
$dbh_old->commit;
  • db2にINSERTしたやつはdb1で消す、SELECTはdb2で読めれば良いので問題ない。
  • db2にレコードがあってdb1にもレコードがあったやつは、INSERTかUPDATEでdb2にもINSERTされているやつなので、db2のレコードを優先してやれば良い
    • UPDATEから書き換えたINSERT .. ON DUPLICATE KEY UPDATEに限っては、「何か起こるとズレる」ポイントで何か起こっていた場合「ユニーク制約に引っかかってエラーになる」可能性があるので、IGNOREはせずに丁寧にハンドリングしてやった方が良さげ
  • db1にレコードがないやつはdb2からもDELETEで消えているはず、消えていないにしてもユーザーには「エラーが返って消えていない」なので、db2にあってdb1に無いパターンは気にしない。
  • UPDATEをINSERT INTO .. ON DUPLICATE KEY UPDATEに書き換えている関係で、この移動バッチが終わってもdb1側にレコードが残っていることは予想できる。
    • とはいえそのuser_idを処理し終えていることが確実なら無視して良いレコードなので、バッチがクラッシュせずに完走すれば無視して良い。

とか考えたんですがどうでしょう?