はじめに
この記事は NTTテクノクロス Advent Calendar 2021 の７日目の記事です。
こんにちはＮＴＴテクノクロスで最近はＤＢ関連のお仕事をしている田島です。
Oracle Databaseは業務で使用しているのですが、MySQLは使用したことがなかったので今回調べてみました。
SQLのオプティマイザの制御について
SQLのオプティマイザを制御する方法は以下の３つの方法があります。今回は細かく制御のできる、オプティマイザヒント、インデックスヒントについて調べていきます。
- optimizer_switch システム変数
- オプティマイザヒント
- インデックスヒント
前提条件
使用するMySQLのバージョン
mysql> select version() ;
+-----------------+
| version() |
+-----------------+
| 8.0.19-0ubuntu5 |
+-----------------+
1 row in set (0.01 sec)
使用するテーブル
create table t1_tbl(id int primary key,data varchar(100),flag int) ;
create table t2_tbl(id int primary key,data varchar(100),flag int) ;
create index t1_flag on t1_tbl(flag);
create index t2_flag on t2_tbl(flag);
インデックスヒント
オプティマイザヒントはあとから追加された機能で、もとからあるヒントがこちらになります。
SQLの構文として記述できるのはちょっと違和感があります。
ただし、他RDBとソースコードを共有するためにコメントとしての利用もできます。『/*! */』
普通にインデックスを使用しています。
keyがt1_flagになっていることで確認できます。
mysql> explain select * from t1_tbl where flag = 1;
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t1_tbl | NULL | ref | t1_flag | t1_flag | 5 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
ヒントを確認するため、インデックスを使用しないようにします。
keyがNULLとなっており、インデックスが使用されていないことが確認できます。
mysql> explain select * from t1_tbl ignore index(t1_flag) where flag = 1;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1_tbl | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
ヒントをコメントアウトしています。
ヒントが有効でインデックスが使用されていません。
mysql> explain select * from t1_tbl /*! ignore index(t1_flag) */ where flag = 1;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1_tbl | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
オプティマイザヒント
通常のテーブル結合でSQLに記述した結合順に動作します。
t2_tblのkeyがPRIMARYとなっており、refがt1_tbl.idとなっているため、t1_tblからt2_tblに結合されていることが確認できます。
mysql> explain select t1_tbl.id, t1_tbl.data, t2_tbl.data, t1_tbl.flag, t2_tbl.flag from t1_tbl join t2_tbl on t1_tbl.id = t2_tbl.id;
+----+-------------+--------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
| 1 | SIMPLE | t1_tbl | NULL | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | SIMPLE | t2_tbl | NULL | eq_ref | PRIMARY | PRIMARY | 4 | testdb.t1_tbl.id | 1 | 100.00 | NULL |
+----+-------------+--------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
ヒントを利用して結合順を変えてみます。（t2_tblを駆動表とする）
t1_tblのkeyがPRIMARYとなっており、refがt2_tbl.idとなっているため、t2_tblからt1_tblに結合されていることが確認できます。
mysql> explain select /*+ join_order(t2_tbl, t1_tbl) */ t1_tbl.id, t1_tbl.data, t2_tbl.data, t1_tbl.flag, t2_tbl.flag from t1_tbl join t2_tbl on t1_tbl.id = t2_tbl.id;
+----+-------------+--------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
| 1 | SIMPLE | t2_tbl | NULL | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | SIMPLE | t1_tbl | NULL | eq_ref | PRIMARY | PRIMARY | 4 | testdb.t2_tbl.id | 1 | 100.00 | NULL |
+----+-------------+--------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
ヒントのコメントは最初の１つのみ有効なため、以下のように何もしないヒントをつけ足すと後のヒントは利用されません。
mysql> explain select /*+ */ /*+ join_order(t2_tbl, t1_tbl) */ t1_tbl.id, t1_tbl.data, t2_tbl.data, t1_tbl.flag, t2_tbl.flag from t1_tbl join t2_tbl on t1_tbl.id = t2_tbl.id;
+----+-------------+--------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
| 1 | SIMPLE | t1_tbl | NULL | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | SIMPLE | t2_tbl | NULL | eq_ref | PRIMARY | PRIMARY | 4 | testdb.t1_tbl.id | 1 | 100.00 | NULL |
+----+-------------+--------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
2 rows in set, 2 warnings (0.00 sec)
ヒントの誤りは以下の様に直後の『show warnings;』で確認できます。
mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1064 | Optimizer hint syntax error near '*/' at line 1 |
| Note | 1003 | /* select#1 */ select `testdb`.`t1_tbl`.`id` AS `id`,`testdb`.`t1_tbl`.`data` AS `data`,`testdb`.`t2_tbl`.`data` AS `data`,`testdb`.`t1_tbl`.`flag` AS `flag`,`testdb`.`t2_tbl`.`flag` AS `flag` from `testdb`.`t1_tbl` join `testdb`.`t2_tbl` where (`testdb`.`t2_tbl`.`id` = `testdb`.`t1_tbl`.`id`) |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
おわりに
今回はMySQLのヒント句ついて調べてみました。RDBでも異なる製品では、ANSI SQL以外ではかなり異なっており勉強になりました。
それでは引き続きNTTテクノクロス Advent Calendar 2021 の８日目もお楽しみください。