IN句に大量の要素を渡すと死ぬ理由の補足メモ

https://zenn.dev/nasu/articles/410bdb9739cd35

MySQLでIN句に大量の要素を渡した場合消費されるメモリがrange_optimizer_max_mem_sizeを超えてしまうと、rangeアクセスではなくフルスキャンになる理由を教えて。
↓↓↓

MySQLにおいて、IN句に多数の要素を含めると、特定のメモリ制限に達する可能性があります。そのメモリ制限の一つがrange_optimizer_max_mem_sizeです。この設定は、範囲最適化プロセスで使用できる最大メモリ量を制御します。範囲最適化は、範囲条件（BETWEEN、>、<、INなど）を使ってクエリの実行計画を決定するプロセスです。

IN句に多くの要素が含まれると、その範囲の評価に必要なメモリ量が増加します。range_optimizer_max_mem_sizeの値を超えると、MySQLは以下の理由で範囲アクセスからフルテーブルスキャンにフォールバックすることを選択します：

メモリ制限の超過:
range_optimizer_max_mem_sizeは、範囲最適化によるメモリ使用を制限するために存在します。これにより、大規模なINリストや複雑な範囲条件がシステムのリソースを過度に消費するのを防ぎます。
パフォーマンスへの影響:
範囲アクセスに必要なメモリが利用可能なリミットを超える場合、範囲最適化プロセスが非効率的または実行不可能になる可能性があります。MySQLはパフォーマンスを維持するため、より単純で予測可能なフルテーブルスキャンに切り替えることがあります。
実行計画の単純化:
フルテーブルスキャンは、範囲クエリの実行計画を計算するためのリソースが限られている場合の安全な代替手段です。計算リソースを節約しながら、データ全体を確実にスキャンすることで、必要な結果を得ることができます。
この挙動は、システムが不適切なリソース消費を避けるための自動的な最適化の一環です。IN句に非常に多くの要素を含める場合、そのクエリがフルテーブルスキャンになることは、性能に大きな影響を与える可能性があるため、このような設計が適用されています。

クエリの性能を改善するためには、IN句の要素数を合理的な範囲に保つか、range_optimizer_max_mem_sizeの値を調整することを検討すると良いでしょう。ただし、後者の方法ではシステム全体のリソース使用に影響を与える可能性があるため、注意が必要です。

なぜフルテーブルスキャンの方が計算リソースが節約できるのですか？

↓↓↓

フルテーブルスキャンが範囲アクセスやインデックス利用と比較して計算リソースを節約すると言うと少し誤解を招くかもしれません。正確には、フルテーブルスキャンは「リソースの使用を最も単純化する方法」であると言えます。ここでいう「節約」とは、主にクエリ最適化や実行計画の決定におけるリソースの消費を指します。

フルテーブルスキャンのリソース使用に関する説明
単純なアプローチ:
フルテーブルスキャンはデータベースのすべての行を単純に読み込むため、実行計画の複雑さが少なくなります。インデックスを使用する場合や範囲クエリを評価する場合は、どのインデックスが最適かを決定するための追加の計算が必要です。これには、インデックスの選択、範囲の計算、メモリ内での一時データ構造の管理などが含まれます。
最適化プロセスの省略:
インデックスや範囲クエリを利用する場合、MySQLは複数の実行計画を考慮し、それぞれのコストを評価して最適なものを選択します。しかし、フルテーブルスキャンでは、この種の詳細な評価が不要であり、実行計画の決定が速やかに行われます。
リソース制限の回避:
range_optimizer_max_mem_size のようなパラメータが制限するメモリ使用量を超える場合、フルテーブルスキャンにフォールバックすることで、メモリオーバーヘッドやその他のリソース制約によるパフォーマンスの低下を避けることができます。メモリ不足が発生すると、データベースのパフォーマンスが著しく低下することがあります。
総括
フルテーブルスキャンは通常、インデックスや範囲クエリよりも遅く、I/O負荷が高いですが、実行計画の複雑さを減らし、システムの計算リソースの消費を抑える面では単純化を提供します。ただし、データ量が大きい場合やクエリのパフォーマンスが重要な場面では、フルテーブルスキャンの使用は避けるべきです。そのため、クエリの最適化やインデックスの適切な使用が推奨されます。

つまり、

1. IN句が大量に渡されると要素の展開にメモリが必要になり、メモリlimitに達するのでrangeスキャンではなくフルスキャンをする
2. フルスキャンは実行計画が単純であり、クエリオプティマイザーによる実行計画の決定コストが低いため早く決定できるので結果的にメモリが節約できる

クエリオプティマイザーが制限メモリに達したらフルスキャンをしてしまうのはなぜでしょうか？また、この場合パフォーマンスを早くするにはどうすればいいでしょうか？
クエリオプティマイザーが制限メモリに達した際にフルスキャンを実行するのは、利用可能なメモリがインデックススキャンや他のメモリ集約型の操作に必要な量よりも少ないためです。インデックススキャンなどのより効率的なアプローチは、しばしば追加のメモリを必要とします。もし利用可能なメモリがこれらの操作をサポートするには不十分である場合、オプティマイザーはより少ないメモリを使用するフルスキャンにフォールバックすることがあります。