目次
とある日
ツイッターでこんなの見つけた。
若者がMySQLのオプティマイザトレースのrecheck_reson: not_first_tableってなんじゃろねって呟いてたからソースコードのsql_optimizer.ccにそれっぽいのあるよってサジェストしておいた。私は強くないからわからないけど、tom_boさんやyoku0825さんとか呼吸するかのようにコードまで追いかけてそう。
— Shota Ito (@st_1t) 2022年11月8日
で、ソースコードまであまり見たことないなと思って頑張って調べてみたけど結果なにも分からなかった記録です。
はじめに
本記事はただただMySQLのソースコードを見ながら「not_first_table」が何しているのか探る内容になってます。
ただ、「not_first_table」がなにかについては判明してません。
- 個人スキル
- 本記事
調べてみた
元ツイートから分かる情報
- オプティマイザトレースにまつわる疑問
- recheck_reson: not_first_tableがわからない
- recheck_reasonの誤字だと思われる
- ソースコードのsql_optimizer.ccにそれっぽいのがある
Googleで各自を調べて出てきた内容
- 今日は、MySQL8.0.24の変更点オプティマイザーノートについての日。 - 今日はなにの日。
- 自分の過去記事
- recheck_reason: not_first_tableが載っているオプティマイザトレース結果があった
- mysql-server/sql_optimizer.cc at 8.0 · mysql/mysql-server
- 該当のソースコード
- MySQL: Welcome
- ソースコードドキュメントなるものができたらしい
- ドキュメント生成日: 2022-09-13
検証環境作る
調べると過去の自分の記事が出たのでテーブルとSQL、オプティマイザトレースの手順を丸パクリして検証結果用意。
長くなるので該当部分だけ切り出す。
実行するSQL
select * from sort10 where sort10.id < (select count(id) from sort);
オプティマイザトレース結果
{
"attaching_conditions_to_tables": {
"original_condition": "(`sort10`.`id` < `derived_1_2`.`count(id)`)",
"attached_conditions_computation": [
{
"table": "`sort10`",
"rechecking_index_usage": {
"recheck_reason": "not_first_table",
"range_analysis": {
"table_scan": {
"rows": 12,
"cost": 3.55
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": true,
"key_parts": [
"id"
]
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_single_table"
},
"skip_scan_range": {
"chosen": false,
"cause": "not_single_table"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "PRIMARY",
"chosen": false,
"cause": "depends_on_unread_values"
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
}
}
}
}
],
"attached_conditions_summary": [
{
"table": " `derived_1_2`",
"attached": null
},
{
"table": "`sort10`",
"attached": "(`sort10`.`id` < `derived_1_2`.`count(id)`)"
}
]
}
該当ソースコードを見る
↓から関係ありそうな部分のコードを切り出す。
mysql-server/sql_optimizer.cc at 8.0 · mysql/mysql-server
- 関数定義位置:9354行目
- 関数名:make_join_query_block
if ((tab->type() == JT_ALL || tab->type() == JT_RANGE || tab->type() == JT_INDEX_MERGE || tab->type() == JT_INDEX_SCAN) && tab->use_quick != QS_RANGE) { /* We plan to scan (table/index/range scan). Check again if we should use an index. We can use an index if: 1a) There is a condition that range optimizer can work on, and 1b) There are non-constant conditions on one or more keys, and 1c) Some of the non-constant fields may have been read already. This may be the case if this is not the first table in the join OR this is a subselect with non-constant conditions referring to an outer table (dependent subquery) or, 2a) There are conditions only relying on constants 2b) This is the first non-constant table 2c) There is a limit of rows to read that is lower than the fanout for this table, predicate filters included (i.e., the estimated number of rows that will be produced for this table per row combination of previous tables) 2d) The query is NOT run with FOUND_ROWS() (because in that case we have to scan through all rows to count them anyway) */ enum { DONT_RECHECK, NOT_FIRST_TABLE, LOW_LIMIT } recheck_reason = DONT_RECHECK; assert(tab->const_keys.is_subset(tab->keys())); const join_type orig_join_type = tab->type(); const AccessPath *const orig_range_scan = tab->range_scan(); if (cond && // 1a (tab->keys() != tab->const_keys) && // 1b (i > 0 || // 1c (join->query_block->master_query_expression()->item && cond->is_outer_reference()))) recheck_reason = NOT_FIRST_TABLE; else if (!tab->const_keys.is_clear_all() && // 2a i == join->const_tables && // 2b (join->query_expression()->select_limit_cnt < (tab->position()->rows_fetched * tab->position()->filter_effect)) && // 2c !join->calc_found_rows) // 2d recheck_reason = LOW_LIMIT; // Don't recheck if the storage engine does not support index access. if ((tab->table()->file->ha_table_flags() & HA_NO_INDEX_ACCESS) != 0) recheck_reason = DONT_RECHECK; if (tab->position()->sj_strategy == SJ_OPT_LOOSE_SCAN) { /* Semijoin loose scan has settled for a certain index-based access method with suitable characteristics, don't substitute it. */ recheck_reason = DONT_RECHECK; } if (recheck_reason != DONT_RECHECK) { Opt_trace_object trace_one_table(trace); trace_one_table.add_utf8_table(tab->table_ref); Opt_trace_object trace_table(trace, "rechecking_index_usage"); if (recheck_reason == NOT_FIRST_TABLE) trace_table.add_alnum("recheck_reason", "not_first_table"); else trace_table.add_alnum("recheck_reason", "low_limit") .add("limit", join->query_expression()->select_limit_cnt) .add("row_estimate", tab->position()->rows_fetched * tab->position()->filter_effect);
本記事だと41行目(9544行目)にNOT_FIRST_TABLEを代入している行がある。
recheck_reason = NOT_FIRST_TABLE;
その外側のIF文を見る。
if (cond && // 1a (tab->keys() != tab->const_keys) && // 1b (i > 0 || // 1c (join->query_block->master_query_expression()->item && cond->is_outer_reference()))) recheck_reason = NOT_FIRST_TABLE; else if (!tab->const_keys.is_clear_all() && // 2a i == join->const_tables && // 2b (join->query_expression()->select_limit_cnt < (tab->position()->rows_fetched * tab->position()->filter_effect)) && // 2c !join->calc_found_rows) // 2d recheck_reason = LOW_LIMIT;
関係ありそなコメントも切り出す。
/ We plan to scan (table/index/range scan). Check again if we should use an index. We can use an index if: 1a) There is a condition that range optimizer can work on, and 1b) There are non-constant conditions on one or more keys, and 1c) Some of the non-constant fields may have been read already. This may be the case if this is not the first table in the join OR this is a subselect with non-constant conditions referring to an outer table (dependent subquery) or, 2a) There are conditions only relying on constants 2b) This is the first non-constant table 2c) There is a limit of rows to read that is lower than the fanout for this table, predicate filters included (i.e., the estimated number of rows that will be produced for this table per row combination of previous tables) 2d) The query is NOT run with FOUND_ROWS() (because in that case we have to scan through all rows to count them anyway) /
↓翻訳
/ スキャン(テーブル/インデックス/レンジスキャン)を予定しています。 インデックスを使用するかどうか、もう一度確認してください。以下の場合、インデックスを使用することができる。 1a) 範囲オプティマイザが動作可能な条件がある。 1b) 1つ以上のキーに定数でない条件がある。 1c) 定数でないフィールドのいくつかは既に読み込まれている可能性がある。 をすでに読み込んでいる。これは、このテーブルが結合の最初のテーブルでない場合、あるいは テーブルでない場合、または、外側を参照する非定数条件のサブセレクトテーブルの場合です。 外部テーブルを参照する非定数条件 (依存サブクエリ) または 2a) 定数のみに依存する条件がある。 2b) これは最初の定数でないテーブルです。 2c) このテーブルのファンアウトより低い、読み込むべき行の制限がある。 このテーブルのファンアウト（述語フィルタを含む）より低い読み出し行数制限がある。 (すなわち、このテーブルの行の組み合わせごとに生成される行の推定数) の行の組み合わせごとに、このテーブルのために生成される推定行数）。 このテーブルに生成される推定行数) 2d) クエリはFOUND_ROWS()と共に実行されない（その場合、すべての行をスキャンしなければならないからです。 を使用して実行しない(その場合、行数を数えるためにすべての行をスキャンする必要があるため)。 /
ソースコードドキュメントでも見てみる
該当ソースコード部分に関係ありそな関数を見てみる。
右上Searchから探せばすぐ見つかる。
関数の簡単な概要もあるのでなにしているかわかりやすい。
make_join_query_block()
static bool make_join_query_block (JOIN * JOIN,Item * cond)
結合条件で述語を分離し、関係するすべてのテーブルが結合プレフィックスで使用できる結合ステップにそれらをプッシュします。
JOIN 式の ON 句も、最適なステップにプッシュされます。
パラメーター
join 述語がプッシュされる結合オブジェクト。
cond AND、OR、および XOR 項目を使用して結合された、任意の数の述語を含むことができる条件へのポインター。NULL の場合、すべての行の組み合わせに対して true を返す述語と同等です。
戻り値
true 不可能な WHERE 句、またはメモリ不足が見つかりました
false 他の
master_query_expression()
Query_expression * Query_block::master_query_expression()const
is_outer_reference()
bool Item::is_outer_reference () const
戻り値
この項目が外部参照である場合は true。通常は、外部クエリ ブロックの FROM 句にあるテーブルに含まれる列を参照することを意味します。
色々と試してみる
検証テーブルに対して色々と変更したSQLを実行してみる。
パターン1
- 外部結合にしてみる
- サブクエリをidからstr
- KEYがあるカラムからないカラムに変えてみる
SELECT * FROM sort10 left outer join sort on sort10.id = sort.id WHERE sort10.str < ( SELECT count(str) FROM sort );
結果
recheck_reasonを格納している配列の中身がなくなる。
"attached_conditions_computation": [
],
rechecking_index_usageとあることから、INDEXが使われないとそもそも生成されない説。
パターン2
- サブクエリに条件を追加してみた
- 「 1b) 1つ以上のキーに定数でない条件がある。」
- 定数じゃないものを生成してみたかった
SELECT * FROM sort10 LEFT OUTER JOIN sort ON sort10.id = sort.id WHERE sort10.id < ( SELECT count(id) FROM sort WHERE CEIL(RAND() * 100) > 50 );
結果
こちらも結果なしになる。
"attached_conditions_computation": [
],
パターン3
条件を追加してみる。
SELECT * FROM sort10 LEFT OUTER JOIN sort ON sort10.id = sort.id WHERE sort10.id < ( SELECT count(id) FROM sort ) and sort10.str like left( (SELECT str FROM sort where sort10.id = sort.id limit 1),10 ) ;
結果
recheck_reasonは出たが結果変わらず。
複雑なサブクエリにしないとだめ説。
{
"attaching_conditions_to_tables": {
"original_condition": "((`sort10`.`id` < `derived_1_2`.`count(id)`) and (`sort10`.`str` like left((/* select#3 */ select `sort`.`str` from `sort` where (`sort10`.`id` = `sort`.`id`) limit 1),10)))",
"attached_conditions_computation": [
{
"table": "`sort10`",
"rechecking_index_usage": {
"recheck_reason": "not_first_table",
"range_analysis": {
"table_scan": {
"rows": 72,
"cost": 9.55
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": true,
"key_parts": [
"id"
]
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_single_table"
},
"skip_scan_range": {
"chosen": false,
"cause": "not_single_table"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "PRIMARY",
"chosen": false,
"cause": "depends_on_unread_values"
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
}
}
}
}
],
"attached_conditions_summary": [
{
"table": " `derived_1_2`",
"attached": null
},
{
"table": "`sort10`",
"attached": "((`sort10`.`id` < `derived_1_2`.`count(id)`) and (`sort10`.`str` like left((/* select#3 */ select `sort`.`str` from `sort` where (`sort10`.`id` = `sort`.`id`) limit 1),10)))"
},
{
"table": "`sort`",
"attached": "<if>(is_not_null_compl(sort), (`sort`.`id` = `sort10`.`id`), true)"
}
]
}
},
まとめ
イマイチ挙動についてはわかりませんでした。
ソースコードを読むことはできても理解するところまではいかなかった。
ソースコードドキュメントはかなり便利だった。
〆
「なるほど、よくわからん」で終わりました。
ただ、ソースコードドキュメントを発見したことは良かったのではと思ってます。
ソースコードを直接読み解くよりまだわかりやすく検索しやすいので追いやすいですね。
参考記事
https://dev.mysql.com/doc/internals/en/optimizer-tracing.html
