MySQL optimizer_traceパッチ
はてな始めました。よろしくお願いします。
MySQLでSQLの実行計画が生成される様子を追いかけるパッチを書いてみました。
こんなふうにパラメータを設定してSQLを実行すると、
mysql> set @@session.optimizer_trace = 1; Query OK, 0 rows affected (0.00 sec) mysql> select d.d_w_id, d.d_id, h.h_data -> from district d, history h -> where d.d_w_id = h.h_w_id -> and d.d_id = h.h_d_id -> and d.d_id < 5 -> and h.h_data like 'a%'; +--------+------+--------------------------+ | d_w_id | d_id | h_data | +--------+------+--------------------------+ | 1 | 1 | aPnMCisCYn362js7mGZzYNmB | | 1 | 1 | AjpJKPa0YRV540UFkxh7MAz | 〜 | 1 | 4 | AwL430dTtOqhlqiWblgqy6 | +--------+------+--------------------------+ 394 rows in set (0.53 sec)
エラーログに以下のようなトレースが出力されます。
071214 15:09:47 [Note] T@1 >optimizer_trace -------------------- 071214 15:09:47 [Note] T@1 search depth : 0 071214 15:09:47 [Note] T@1 0 table alias : d 071214 15:09:47 [Note] T@1 0 key by range : d_idx01 071214 15:09:47 [Note] T@1 0 current read time : 0 + 1 = 1 071214 15:09:47 [Note] T@1 0 current record count : 1 * 4 = 4 071214 15:09:47 [Note] T@1 0 current cost : 1 + 4 / 5 = 2 071214 15:09:47 [Note] T@1 search depth : 1 071214 15:09:47 [Note] T@1 1 table alias : h 071214 15:09:47 [Note] T@1 1 key by ref : h_idx01 071214 15:09:47 [Note] T@1 1 current read time : 1 + 1932 = 1933 071214 15:09:47 [Note] T@1 1 current record count : 4 * 1885 = 7540 071214 15:09:47 [Note] T@1 1 current cost : 1933 + 7540 / 5 = 3441 071214 15:09:47 [Note] T@1 1 completed 071214 15:09:47 [Note] T@1 current join->best_read : 3441 071214 15:09:47 [Note] T@1 search depth : 0 071214 15:09:47 [Note] T@1 0 table alias : h 071214 15:09:47 [Note] T@1 0 key by range : h_idx02 071214 15:09:47 [Note] T@1 0 current read time : 0 + 2693 = 2693 071214 15:09:47 [Note] T@1 0 current record count : 1 * 2244 = 2244 071214 15:09:47 [Note] T@1 0 current cost : 2693 + 2244 / 5 = 3142 071214 15:09:47 [Note] T@1 search depth : 1 071214 15:09:47 [Note] T@1 1 table alias : d 071214 15:09:47 [Note] T@1 1 key by ref : PRIMARY 071214 15:09:47 [Note] T@1 1 current read time : 2693 + 2244 = 4937 071214 15:09:47 [Note] T@1 1 current record count : 2244 * 1 = 2244 071214 15:09:47 [Note] T@1 1 current cost : 4937 + 2244 / 5 = 5386 071214 15:09:47 [Note] T@1 1 pruned by cost : 5386 >= 3441 071214 15:09:47 [Note] T@1 join->best_read : 3441 071214 15:09:47 [Note] T@1 <optimizer_trace --------------------
RDBMSのSQLオプティマイザはアルゴリズムが複雑で理解するのは大変ですが、MySQLは今ならまだ追いつける気がします。
これから勉強していきたいと思います。途中でくじけたらごめんなさいm(_ _)m