MySQL8.0.20で追加されたヒント句について
今回はMySQL8.0.20で新たに導入されたヒント句について整理します。
ヒント句とは
オプティマイザに、「このキーを使え」とか「この順番でテーブルを結合しろ」ということを教えるための構文です。
オプティマイザにとってのヒントというわけですね。
基本的にはオプティマイザは統計情報に基づいて最も最適な(と思われる)パスを選択します。
ただし、長期間運用を続けていると統計情報が正確な値からかけ離れてしまい、望む結果ではない実行計画を選択してしまうこともあります。
他にもオプティマイザの精度が良くなくて最適な実行計画を選んでくれないといったケースもあります。
最近はオプティマイザの精度もよくなってきているので、昔ほど誤った選択をすることもなくなったそうです。
僕が体験したケースはレプリケーション環境でMasterとSlaveで統計情報が大きく変わってしまい、Masterでは高速で終わる更新がSlaveで長時間かかってしまい、
該当の更新クエリが連続で実行されると徐々にレプリケーション遅延が発生するといったものでした。(binlog_format=STATEMENT
環境でした)
この時は確かSlave側でANALYZE TABLE
を実行した気がします。
他にも、テーブルの行数が増えて今まで期待した通りの実行計画を選択していたものが、そうでない実行計画を選んでしまったり、 新しくインデックスを追加して既存のクエリが影響を受けてしまうといったこともあります。
オプティマイザにとって統計情報は非常に大切な要素なのです。
InnoDBであればある程度、自動で統計情報を更新してくれるのですが、どうしても完ぺきとは言えません。
統計情報にかかわらず、使用するキーを選択させたり、結合する順番を固定するための構文がヒント句です。
従来のヒント句
今回利用するのは公式ページのworldデータベースです。
キーを指定するヒント句には次のようなものがあります。
FORCE INDEX
指定したキーを強制的に使用させます。
mysql80-root@localhost:[world] >EXPLAIN SELECT CountryCode, District, Population FROM city FORCE INDEX(PRIMARY) WHERE id < 100 AND CountryCode LIKE 'A%'; +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | city | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 99 | 11.11 | Using where | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql80-root@localhost:[world] >EXPLAIN SELECT CountryCode, District, Population FROM city FORCE INDEX(CountryCode) WHERE id < 100 AND CountryCode LIKE 'A%'; +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | city | NULL | range | CountryCode | CountryCode | 16 | NULL | 107 | 33.33 | Using index condition | +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)
USE INDEX
USE INDEX
は基本的には指定したキーを選択しますが、テーブルフルスキャンのほうがコストが低いと判断されればテーブルフルスキャンを選択します。
つまり、USE INDEX
ではもしかしたら選択したキーを使ってくれない可能性があるというわけです。
例えばこんな感じです。
mysql80-root@localhost:[world] >EXPLAIN SELECT * FROM countrylanguage_cp USE INDEX(idx1) WHERE IsOfficial = 'F'; +----+-------------+--------------------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | countrylanguage_cp | NULL | ALL | idx1 | NULL | NULL | NULL | 984 | 99.90 | Using where | +----+-------------+--------------------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql80-root@localhost:[world] >EXPLAIN SELECT * FROM countrylanguage_cp FORCE INDEX(idx1) WHERE IsOfficial = 'F'; +----+-------------+--------------------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | countrylanguage_cp | NULL | ref | idx1 | idx1 | 1 | const | 983 | 100.00 | Using index condition | +----+-------------+--------------------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)
このテーブルはIsOfficial
カラムにTかFを指定します。1行を除いてすべてFにしているのでセカンダリインデックスであるidx1(IsOfficial)を使って
Fとなる行を選択する場合、テーブルフルスキャンのほうがコストが低いと判断されたようです。
そのためUSE INDEX
で指定しても上記の1番目の例のようにtypeがALLになってしまうわけです。
(注意 必ずしもFORCE INDEX
のほうがUSE INDEX
より優れているというわけではありません)
IGNORE INDEX
IGNORE INDEX
は逆に指定したキーを使わないようにするヒント句です。
mysql80-root@localhost:[world] >EXPLAIN SELECT CountryCode, District, Population FROM city IGNORE INDEX(PRIMARY, CountryCode) WHERE id < 100 AND CountryyCode LIKE 'A%'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | city | NULL | ALL | NULL | NULL | NULL | NULL | 4046 | 3.70 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
このように複数のキーを指定することも可能です。 (MySQLは一つのクエリで使えるインデックスが原則1つのみですので、複数指定するのはIGNORE INDEX相当の処理のみですね)
その他
その他、JOINやORDER BYに使用するキーをヒント句で指定することもできます。
mysql80-root@localhost:[world] >EXPLAIN SELECT country.Name, city.Name FROM city INNER JOIN country ON city.CountryCode = country.Code WHERE CountryCode = 'ABW'; +----+-------------+---------+------------+-------+---------------+-------------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+-------------+---------+-------+------+----------+--------------------------+ | 1 | SIMPLE | country | NULL | const | PRIMARY | PRIMARY | 12 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | city | NULL | ref | CountryCode | CountryCode | 12 | const | 1 | 100.00 | Using where; Using index | +----+-------------+---------+------------+-------+---------------+-------------+---------+-------+------+----------+--------------------------+ 2 rows in set, 1 warning (0.00 sec) mysql80-root@localhost:[world] >EXPLAIN SELECT country.Name, country.Name FROM city IGNORE INDEX FOR JOIN(CountryCode) INNER JOIN country ON city.CountryCode = country.Code WHERE CountryCode = 'ABW'; +----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | country | NULL | const | PRIMARY | PRIMARY | 12 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | city | NULL | ALL | NULL | NULL | NULL | NULL | 4046 | 0.43 | Using where | +----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)
これらの従来のヒント句(USE/FORCE/IGNORE INDEX)は将来のバージョンで非推奨になり、削除される可能性があるとのことです。
なお、僕が確認した限りMySQL8.0.20ではFORCE INDEX
とか使ってもWarningは出ませんでした。
MySQL8.0.20から追加されたヒント句
まず、MySQL8.0.20から導入されたヒント句についてです。
なお、今回追加されたヒント句には従来のUSE INDEX
に該当するものはありません。
いずれもFORCE INDEX
に該当するものです。
USE INDEX
を実現したい場合は後述するNO*INDEX
を組み合わせて使う必要があるようです。
FORCE INDEX系
選択したキーをオプティマイザに強制的に使用させます。
INDEX
従来のFORCE INDEX
に該当します。
mysql80-root@localhost:[world] >EXPLAIN SELECT /*+ INDEX(city PRIMARY) */ CountryCode, District, Population FROM city WHERE id < 100 AND CountryCode LIKE 'A%'; +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | city | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 99 | 11.11 | Using where | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql80-root@localhost:[world] >EXPLAIN SELECT /*+ INDEX(city CountryCode) */ CountryCode, District, Population FROM city WHERE id < 100 AND CountryCode LIKE 'A%'; +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | city | NULL | range | CountryCode | CountryCode | 16 | NULL | 107 | 33.33 | Using index condition | +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)
DELETEステートメントにも使えます。
mysql80-root@localhost:[world] >EXPLAIN DELETE /*+ INDEX(city PRIMARY) */ FROM city WHERE CountryCode LIKE 'A%' ORDER BY id LIMIT 10; +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | DELETE | city | NULL | index | NULL | PRIMARY | 4 | NULL | 4046 | 100.00 | Using where | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
ORDER_INDEX
ソートに利用するキーを指定します。
従来のFORCE INDEX FOR ORDER BY
に該当します。
mysql80-root@localhost:[world] >EXPLAIN SELECT CountryCode, District, Population FROM city WHERE countrycode LIKE 'A%' ORDER BY id LIMIT 5; +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------------------------+ | 1 | SIMPLE | city | NULL | range | CountryCode | CountryCode | 12 | NULL | 107 | 100.00 | Using index condition; Using filesort | +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------------------------+ 1 row in set, 1 warning (0.01 sec) mysql80-root@localhost:[world] >EXPLAIN SELECT /*+ ORDER_INDEX(city PRIMARY) */ CountryCode, District, Population FROM city WHERE countrycode LIKE 'A%' ORDER BY id LIMIT 5; +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | city | NULL | index | NULL | PRIMARY | 4 | NULL | 5 | 11.11 | Using where | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.01 sec)
GROUP_INDEX
集約に利用するキーを指定します。
従来のFORCE INDEX FOR GROUP BY
に該当します。
mysql80-root@localhost:[world] >EXPLAIN SELECT CountryCode,SUM(Population) FROM city WHERE id < 50 GROUP BY CountryCode; +----+-------------+-------+------------+-------+---------------------+---------+---------+------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------------+---------+---------+------+------+----------+------------------------------+ | 1 | SIMPLE | city | NULL | range | PRIMARY,CountryCode | PRIMARY | 4 | NULL | 49 | 100.00 | Using where; Using temporary | +----+-------------+-------+------------+-------+---------------------+---------+---------+------+------+----------+------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql80-root@localhost:[world] >EXPLAIN SELECT /*+ GROUP_INDEX(city CountryCode) */ CountryCode,SUM(Population) FROM city WHERE id < 50 GROUP BY CountryCode; +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | city | NULL | index | CountryCode | CountryCode | 12 | NULL | 4046 | 33.33 | Using where | +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
JOIN_INDEX
結合に使用するキーを指定します。
従来のFORCE INDEX FOR JOIN
に該当します。
なお、worldデータベースのテーブルではいい案が思いつかなかったので別途テーブルを用意しています。
mysql80-root@localhost:[db1] >SHOW INDEX FROM t1; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | t1 | 0 | PRIMARY | 1 | id | A | 27 | NULL | NULL | | BTREE | | | YES | NULL | | t1 | 1 | key1 | 1 | key1 | A | 30 | NULL | NULL | YES | BTREE | | | YES | NULL | | t1 | 1 | key2 | 1 | key2 | A | 30 | NULL | NULL | YES | BTREE | | | YES | NULL | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 3 rows in set (0.01 sec)
t1と全く同じ構成及び行のt2を用意しています。
mysql80-root@localhost:[db1] >EXPLAIN SELECT /*+ JOIN_INDEX(t1 key1) */ t1.key1, t2.key2 FROM t1 INNER JOIN t2 ON t1.key1 = t2.key1 AND t1.key2 = t2.key2; +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+ | 1 | SIMPLE | t2 | NULL | ALL | key1,key2 | NULL | NULL | NULL | 30 | 100.00 | Using where | | 1 | SIMPLE | t1 | NULL | ref | key1 | key1 | 5 | db1.t2.key1 | 1 | 5.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec) mysql80-root@localhost:[db1] >EXPLAIN SELECT /*+ JOIN_INDEX(t2 key1) */ t1.key1, t2.key2 FROM t1 INNER JOIN t2 ON t1.key1 = t2.key1 AND t1.key2 = t2.key2; +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | ALL | key1,key2 | NULL | NULL | NULL | 30 | 100.00 | Using where | | 1 | SIMPLE | t2 | NULL | ref | key1 | key1 | 5 | db1.t1.key1 | 1 | 5.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)
IGNORE INDEX系
指定したキーをオプティマイザに選択させないようにします。
NO_INDEX
従来のIGNORE INDEX
に該当します。
mysql80-root@localhost:[world] >EXPLAIN SELECT /*+ NO_INDEX(city PRIMARY,CountryCode) */ CountryCode, District, Population FROM city WHERE id < 100 ANDCountryCode LIKE 'A%'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | city | NULL | ALL | NULL | NULL | NULL | NULL | 4046 | 3.70 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
NO_OERDER_INDEX/NO_GROUP_INDEX/NO_JOIN_INDEX
それぞれ指定したキーをソート、集約、結合に選択させないようにします。
mysql80-root@localhost:[world] >EXPLAIN SELECT /*+ NO_ORDER_INDEX(city PRIMARY) */ CountryCode, District, Population FROM city WHERE countrycode LIKE 'A% %' ORDER BY id LIMIT 5; +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------------------------+ | 1 | SIMPLE | city | NULL | range | CountryCode | CountryCode | 12 | NULL | 107 | 100.00 | Using index condition; Using filesort | +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql80-root@localhost:[world] >EXPLAIN SELECT /*+ NO_GROUP_INDEX(city CountryCode) */ CountryCode,SUM(Population) FROM city WHERE id < 50 GROUP BY CounttryCode; +----+-------------+-------+------------+-------+---------------------+---------+---------+------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------------+---------+---------+------+------+----------+------------------------------+ | 1 | SIMPLE | city | NULL | range | PRIMARY,CountryCode | PRIMARY | 4 | NULL | 49 | 100.00 | Using where; Using temporary | +----+-------------+-------+------------+-------+---------------------+---------+---------+------+------+----------+------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql80-root@localhost:[db1] >EXPLAIN SELECT /*+ NO_JOIN_INDEX(t1 key1) */ t1.key1, t2.key2 FROM t1 INNER JOIN t2 ON t1.key1 = t2.key1 AND t1.key2 = t2.kkey2; +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | ALL | key2 | NULL | NULL | NULL | 30 | 100.00 | Using where | | 1 | SIMPLE | t2 | NULL | ref | key1,key2 | key1 | 5 | db1.t1.key1 | 1 | 5.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)
まとめ
今回はMySQL8.0.20で新しく導入されたヒント句について見てきました。
今後はおそらくこの構文がデフォルトになりそうなので、今のうちに覚えておいたほうがよさそうですね。
ちなみに
新しいヒント句だと、指定したキーが無くてもエラーにはならないようです・・・(Warningは出ます)
mysql80-root@localhost:[world] >EXPLAIN SELECT /*+ INDEX(city NO_EXISTS_KEY) */ CountryCode, District, Population FROM city WHERE id < 100 AND CountryCodde LIKE 'A%'; +----+-------------+-------+------------+-------+---------------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | city | NULL | range | PRIMARY,CountryCode | PRIMARY | 4 | NULL | 99 | 2.64 | Using where | +----+-------------+-------+------------+-------+---------------------+---------+---------+------+------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec) mysql80-root@localhost:[world] >show warnings; +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 3128 | Unresolved name `city`@`select#1` `NO_EXISTS_KEY` for INDEX hint | | Note | 1003 | /* select#1 */ select `world`.`city`.`CountryCode` AS `CountryCode`,`world`.`city`.`District` AS `District`,`world`.`city`.`Population` AS `Population` from `world`.`city` where ((`world`.`city`.`ID` < 100) and (`world`.`city`.`CountryCode` like 'A%')) | +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
もちろん従来のヒント句であれば存在しないキーを指定するとエラーになります。
mysql80-root@localhost:[world] >EXPLAIN SELECT CountryCode, District, Population FROM city FORCE INDEX(NO_EXISTS_KEY) WHERE id < 100 AND CountryCode LIK KE 'A%'; ERROR 1176 (42000): Key 'NO_EXISTS_KEY' doesn't exist in table 'city'
気を付けないと、typoで指定したはずのキーが使われずパフォーマンスが出ない・・・なんてことがありそうですねぇ😇