出典について
この記事はSveta Smirnova氏によるThe Percona Performance Blogの記事「Optimizer hints in MySQL 5.7.7 – The missed manual」(2015/4/30)を翻訳したものである。
MySQL 5.7.7ではOracle社は新しく頼もしい機能を明らかにした。オプティマイザヒントである。しかしこのヒントに関してはドキュメントが公開されていない。ヒントに関してユーザーマニュアルで見つけた記載は下記のみである。
- 今やオプティマイザにヒントを付けることが可能となり、
/*+ ... */のコメントをSQL文のSELECT、INSERT、REPLACE、UPDATE、またはDELETEキーワードに続けて付けることによってこれが実現できる。このようなSQL文はEXPLAINと共に利用することもできる。例は次の通り。
SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
FROM t3 WHERE f1 > 30 AND f1 < 33;
SELECT /*+ BKA(t1, t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ NO_ICP(t1) */ * FROM t1 WHERE ...;
3つのワークログもある。WL #3996、WL #8016、そしてWL #8017である。しかしこれらは一般的な概念について述べているだけで、どの最適化が使えてどのように使えるかに関する情報が多くはなかった。これに関する詳細はPercona LiveのØystein Grøvlenのセッションのスライド 59ページ目にある。これが全てである。"公式の"利用可能な最適化のリストがなく、使い方がなく... 何もない。
私は自分で整理を試みた。
最初は最初にスライド 59は可能なインデックスヒントの7つのうち6つがリストされていることを見つけた。これは新しい機能の為に作られた、MySQLのソースツリーのsqlディレクトリ下にある2つの新しいファイルののうちの1つで確認した。
$cat sql/opt_hints.h
...
/**
Hint types, MAX_HINT_ENUM should be always last.
This enum should be synchronized with opt_hint_info
array(see opt_hints.cc).
*/
enum opt_hints_enum
{
BKA_HINT_ENUM= 0,
BNL_HINT_ENUM,
ICP_HINT_ENUM,
MRR_HINT_ENUM,
NO_RANGE_HINT_ENUM,
MAX_EXEC_TIME_HINT_ENUM,
QB_NAME_HINT_ENUM,
MAX_HINT_ENUM
};
sql/opt_hints.ccファイルを見てみると、これらの最適化にはそんなに選択肢がないことが分かった。有効か、無効かのいずれかである。
$cat sql/opt_hints.cc
...
struct st_opt_hint_info opt_hint_info[]=
{
{"BKA", true, true},
{"BNL", true, true},
{"ICP", true, true},
{"MRR", true, true},
{"NO_RANGE_OPTIMIZATION", true, true},
{"MAX_EXECUTION_TIME", false, false},
{"QB_NAME", false, false},
{0, 0, 0}
};
SQL文にヒントを入れる入れ方: コメント内に"+"記号で /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ とし、これはOracleのオプティマイザヒントの文法と互換がある。
実のところ、これらのヒントは以前もoptimizer_switch変数を通じて利用することができた。少なくともBKA、BNL、ICP、MRRのような最適化に関しては。しかし新たな文法でこれらをグローバルまたはセッション毎に変更できるだけでなく、クエリのあるテーブルと列に特定の最適化を有効化したり無効化したりすることができる。これを下記の極めて人工的ではあるが常に実施できる例で実演する。
# NO_RANGE_OPTIMIZATION
mysql> use mysql
Database changed
mysql> explain select * from user where host in ('%', '127.0.0.1');
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | user | NULL | range | PRIMARY | PRIMARY | 180 | NULL | 2 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)
mysql> explain select /*+ NO_RANGE_OPTIMIZATION(user PRIMARY) */ * from user where host in ('%', '127.0.0.1');
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | PRIMARY | NULL | NULL | NULL | 5 | 40.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
私は以前は直接は有効化あるいは無効化ができなかったヒントを利用した。レンジの最適化である。
もう1つ"直感的に"ドキュメント化される機能は特定の最適化を有効化したり無効化したりするものだ。これは、BKA、BNL、ICP、そしてMRRにのみ有効である。JOINにおけるインデックスの利用または特定のテーブルに対するこれらのアルゴリズムの利用を避けたい場合、NO_BKA(table[[, table]...])、NO_BNL(table[[, table]...])、NO_ICP(table indexes[[, table indexes]...])、そしてNO_MRR(table indexes[[, table indexes]...])を指定する。
MAX_EXECUTION_TIMEはテーブル名やキー名を引数に必要としない。その代わり、クエリを実行する最大の時間をミリ秒で指定する必要がある。
# MAX_EXECUTION_TIMEの例
mysql> select /*+ MAX_EXECUTION_TIME(1000) */ sleep(1) from user;
ERROR 3024 (HY000): Query execution was interrupted, max_statement_time exceeded
mysql> select /*+ MAX_EXECUTION_TIME(10000) */ sleep(1) from user;
+----------+
| sleep(1) |
+----------+
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
+----------+
5 rows in set (5.00 sec)
QB_NAMEはより複雑である。WL #8017はこれがカスタムコンテキストであることを示している。しかしこれはいったい何であろうか?答えはMySQLのテストスイートにある。オプティマイザヒントへのテストは、t/opt_hints.test内にあり、QB_NAMEの最初のエントリは次のクエリである。
# QB_NAMEの例
EXPLAIN SELECT /*+ NO_ICP(t3@qb1 f3_idx) */ f2 FROM
(SELECT /*+ QB_NAME(QB1) */ f2, f3, f1 FROM t3 WHERE f1 > 2 AND f3 = 'poiu') AS TD
WHERE TD.f1 > 2 AND TD.f3 = 'poiu';
従って、どんなサブクエリに対してもカスタムのQB_NAMEを指定し、そのコンテクストのみに対するオプティマイザヒントを指定することができる。
この短い要旨を結論づける為に、クエリのヒントが本当に必要となる実用的な例を示したいと思う。先週、ある顧客がMySQLのバージョンを5.5から5.6にアップグレードしたところ、いくつかのクエリが以前より遅くなったという問題に取組んだ。面白い回答を導いたが、なお正しいものである。"このような挙動の1つの原因はオプティマイザの改善である。全体としては良いパフォーマンスになっているが、いくつかのクエリは、古いバージョンに対し最適化され、以前より遅くなりうるのだ。"
このようなクエリの公知の例を実演する為に、私はお気に入りの情報源を使う。MySQL Community Bugs Databaseである。オプティマイザのリグレッションバグでまだ改修されていないものを探す上で、我々はbug #68919を見つけ、これはLIMIT句のあるクエリでMRRアルゴリズムが使われるケースでのリグレッションである。クエリを実行する際に、バグレポートで示されている通り、大きな違いが発生することが分かるだろう。
# Bug #68919とNO_MRRのヒント
mysql> SELECT * FROM t1 WHERE i1>=42 AND i2<=42 LIMIT 1;
+----+----+----+----+
| pk | i1 | i2 | i3 |
+----+----+----+----+
| 42 | 42 | 42 | 42 |
+----+----+----+----+
1 row in set (6.88 sec)
mysql> explain SELECT * FROM t1 WHERE i1>=42 AND i2<=42 LIMIT 1;
+----+-------------+-------+------------+-------+---------------+------+---------+------+---------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+---------+----------+----------------------------------+
| 1 | SIMPLE | t1 | NULL | range | idx | idx | 4 | NULL | 9999958 | 33.33 | Using index condition; Using MRR |
+----+-------------+-------+------------+-------+---------------+------+---------+------+---------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT /*+ NO_MRR(t1) */ * FROM t1 WHERE i1>=42 AND i2<=42 LIMIT 1;
+----+----+----+----+
| pk | i1 | i2 | i3 |
+----+----+----+----+
| 42 | 42 | 42 | 42 |
+----+----+----+----+
1 row in set (0.00 sec)
MRRが使われている場合のクエリ実行は6.88秒かかっており、MRRが使われていない場合は0秒である!しかしバグレポート自体はoptimizer_switch="mrr=off";を利用することを一時的な対処として提案している。これはSET optimizer_switch="mrr=off";で実行してよければ完全にうまくいく。クエリを実行するたび毎回OFFにする恩恵に授かる。オプティマイザヒントを使えば、あるクエリの特定のテーブルに対する特定の最適化アルゴリズムをONにし、他のクエリに対してはOFFにできる。ここで再度、とても人為的な例で、これを実演する。
# 同一クエリ内のMRRのオンオフ
mysql> explain select /*+ MRR(dept_emp) */ * from dept_emp where to_date in (select /*+ NO_MRR(salaries)*/ to_date from salaries where salary >40000 and salary <45000) and emp_no >10100 and emp_no < 30200 and dept_no in ('d005', 'd006','d007');
+----+--------------+-------------+------------+--------+------------------------+------------+---------+----------------------------+---------+----------+-----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+-------------+------------+--------+------------------------+------------+---------+----------------------------+---------+----------+-----------------------------------------------+
| 1 | SIMPLE | dept_emp | NULL | range | PRIMARY,emp_no,dept_no | dept_no | 8 | NULL | 10578 | 100.00 | Using index condition; Using where; Using MRR |
| 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_key> | <auto_key> | 3 | employees.dept_emp.to_date | 1 | 100.00 | NULL |
| 2 | MATERIALIZED | salaries | NULL | ALL | salary | NULL | NULL | NULL | 2838533 | 17.88 | Using where |
+----+--------------+-------------+------------+--------+------------------------+------------+---------+----------------------------+---------+----------+-----------------------------------------------+
3 rows in set, 1 warning (0.00 sec)
