mysqlシステムデータベースのコスト係数を調整するだけでなく、MySQLはどのように実行計画が選択されるかを「上書き」する方法も提供しています。クエリーヒントを利用するのは次の2つの明確な理由からとても有用です。
EXPLAINは利用できたメタデータに基づいて決定された結果を表示します。ヒントを追加することによって、他の実行計画を強制的に実行させ、実際の実行時間を比較することができます。私はデバッグではヒントをよく利用しますが、本番環境に導入するときにはいつも気をつけています。ヒントはある時点で選択された特定の実行計画に固定するものであるため、データの分布やインデックスが時とともに変化した場合に維持管理上の重荷になりえます。
ベストプラクティスとしては、MySQLのメジャーバージョンアップをした後(例えば、MySQL 9.0にアップグレードするときに)には毎回ヒントを見直すと良いでしょう1。新しい最適化が導入されると、それに伴いたくさんのヒントを無効化できることがお分かりになることでしょう。
以前のMySQLバージョンではSQL文法を直接的に拡張した一連のヒントのみをサポートしていました。例えば次のとおりです。
# テーブルを指定順序で結合させる
SELECT STRAIGHT_JOIN Country.Name as CountryName, City.Name AS City
FROM Country INNER JOIN City ON City.CountryCode=Country.Code;
# インデックスの利用を強制する
SELECT * FROM Country FORCE INDEX (p)
WHERE continent='Asia' and population > 5000000;
# インデックスを無視させる
SELECT * FROM Country IGNORE INDEX (p)
WHERE continent='Asia' and population > 5000000;
# 他のインデックスが存在する状況であるインデックスを優先させる
SELECT * FROM Country USE INDEX (p)
WHERE continent='Asia' and population > 5000000;
これらのヒントはMySQL 8.0でも継続してサポートされますが、一部はコメント形式のヒントに置き換えられます。例5では、FORCE INDEXヒントを利用することで、例2で利用されなかったp(population) インデックスが選択されていることがわかります。EXPLAINを参照することで、テーブルスキャンのコストがおよそ53であるのに対し、FORCE INDEXを利用しても、そのコストは152.21であることがわかります。
EXPLAIN FORMAT=JSON
SELECT * FROM Country FORCE INDEX (p) WHERE continent='Asia' and population > 5000000;
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "152.21" # テーブルスキャンより高コスト
},
"table": {
"table_name": "Country",
"access_type": "range",
"possible_keys": [
"p"
],
"key": "p",
"used_key_parts": [
"Population"
],
"key_length": "4",
"rows_examined_per_scan": 108,
"rows_produced_per_join": 15,
"filtered": "14.29",
"index_condition": "(`world`.`Country`.`Population` > 5000000)",
"cost_info": {
"read_cost": "149.12",
"eval_cost": "3.09",
"prefix_cost": "152.21",
"data_read_per_join": "3K"
},
"used_columns": [
"Code",
"Name",
"Continent",
"Region",
"SurfaceArea",
"IndepYear",
"Population",
"LifeExpectancy",
"GNP",
"GNPOld",
"LocalName",
"GovernmentForm",
"HeadOfState",
"Capital",
"Code2"
],
"attached_condition": "(`world`.`Country`.`Continent` = 'Asia')"
}
}
}
MySQL 8.0ではMySQL 5.7で導入された新しいコメント形式のヒントを拡張し、テーブルの結合順序を(STRAIGHT_JOINと同様に)制御できるようになりました。私は次の3つの理由から、古いSQLの文法拡張したヒントよりこの新しいヒントの方が好みです。
FORCE INDEXなどのヒントとは対照的で、この場合はインデックスが存在しないとエラーを発生させます。これは、例えばOracleデータベースのヒントと似た挙動です。| ヒント名称 | 概要 |
|---|---|
| BKA, NO_BKA | 指定したテーブルに対してBatched Key Accessアルゴリズムの最適化を有効化するか、無効化するか(この最適化はデフォルトで無効ですが、 optimizer_switch変数によって制御可能です) |
| BNL, NO_BNL | 指定したテーブルに対してBlock Nested Loopアルゴリズムの最適化を有効化するか、無効化するか |
| MAX_EXECUTION_TIME | クエリーに対する最大実行時間をミリ秒単位で設定します。このヒントは現在はSELECT文に対してのみ有効です |
| MRR, NO_MRR | Multi-Range Readの最適化に影響します |
| NO_ICP | インデックスコンディションプッシュダウンの最適化に影響します |
| NO_RANGE_OPTIMIZATION | 指定されたテーブルあるいはインデックスにrangeの最適化を無効化します |
| QB_NAME | クエリーブロックに名前をつけます |
| SEMIJOIN, NO_SEMIJOIN | サブクエリーを実行する際の準結合戦略(DUPSWEEDOUT, FIRSTMATCH, LOOSESCAN, MATERIALIZATIONなど) |
| SUBQUERY | SEMIJOIN/NO_SEMIJOINと類似しており、IN-to-EXISTSなども含めたサブクエリー実行の際の戦略を制御できます |
例6では、あるテーブルに対してrange最適化が無効化されています。p(population)インデックスを使用した方がずっと良いにも関わらず、無視されてしまいます。population > 10億であるものはテーブル中に2行しかありません。
EXPLAIN FORMAT=JSON
SELECT /*+NO_RANGE_OPTIMIZATION(Country) */ * FROM Country
WHERE Population > 1000000000 AND Continent='Asia';
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "56.80"
},
"table": {
"table_name": "Country",
"access_type": "ALL", # アクセス方法はテーブルスキャン
"possible_keys": [ # 利用可能なインデックスは
"p" # 無効化されている範囲スキャンのみでしか使えない
],
"rows_examined_per_scan": 239,
"rows_produced_per_join": 11,
"filtered": "4.76",
"cost_info": {
"read_cost": "54.52",
"eval_cost": "2.28",
"prefix_cost": "56.80",
"data_read_per_join": "2K"
},
"used_columns": [
"Code",
"Name",
"Continent",
"Region",
"SurfaceArea",
"IndepYear",
"Population",
"LifeExpectancy",
"GNP",
"GNPOld",
"LocalName",
"GovernmentForm",
"HeadOfState",
"Capital",
"Code2"
],
"attached_condition": "((`world`.`Country`.`Continent` = 'Asia') and (`world`.`Country`.`Population` > 1000000000))"
}
}
}