22.MySQL実施計画:概要

22.MySQL実施計画：概要

DBMSのクエリ実行は、同じ結果を生成する唯一の方法ではありません.いろいろな方法がありますが、どの方法が最適なのか、どの方法が最もコストが低いのかを決めます.DBMSは、クエリーを最適化し、これらの基本データを比較することによって最適な実行計画を策定するために、各テーブルのデータがどのように分散されて格納されているかを統計を参照する必要があります.DBMSでは、オプティカル(光学式)ドライブがこれらの機能を担当します.
MySQLでは、EXPLAINというコマンドを使用して、クエリーの実行計画を決定できます.多くの情報が出力されます.ここでは、実行計画に表示される内容が何を意味するのか、MySQLサーバが内部で実行する操作について詳しく説明します.次に、どの実施計画が良いか悪いかを簡単に見てみましょう.
いずれのDBMSでも、クエリー実行プランを作成するオプティカル(光学式)ドライブは最も複雑な部分であり、オプティカル(光学式)ドライブが作成する実行プランを理解することは非常に困難な部分である.しかし、その実施計画を理解してこそ、実施計画の不合理な点を探し出し、より最適化された方法で実施計画を制定するように導くことができる.実行計画を表示する前に、いくつかの点について説明しましょう.

1.クエリー実行プロセス

MySQLサーバでクエリーを実行するプロセスは大きく3つに分けられます.

ユーザーが要求したSQL文をMySQLサーバが理解できるレベルに分割します.

SQLのパーティション情報(パスツリー)を確認し、どのテーブルから、どのインデックスを使用してテーブルを読み込むかを選択します.

は、第2のステップで決定されたテーブルの読み出し順序または選択されたインデックスを使用して、記憶エンジンからデータを取得する.

最初のステップは「SQL解析器」と呼ばれ、MySQLサーバの「SQL解析器」モジュールとして処理されます.SQL文に構文上のエラーがある場合は、このフェーズを終了します.また、この段階ではSQL Pastaツリーが作成されます.MySQLサーバは、SQL文自体ではなくSQL Passツリーを使用してクエリーを実行します.
ステップ2は、ステップ1で作成したSQL Passツリーを参照して、次の処理を行います.

不要な条件を排除し、複雑な演算を簡略化する

複数のテーブルの結合がある場合、テーブル

をどのような順序で読み出すかを決定する.

は、各テーブルで使用する条件およびインデックス統計を使用するインデックス

を決定する.

に導入するレコードをテンポラリテーブルに入れ、

を再加工する必要があるか否かを判定する.
それ以外にも多くの処理が行われているが,代表的にはこのような作業が可能である.2つ目のステップは、MySQLサーバのオプティカル(光学式)ドライブで完了する「最適化と実行計画」です.また、ステップ2が完了すると、クエリーの実行プランが作成されます.
ステップ3は、作成された実行計画に従ってストレージエンジンにレコードの読み取りを要求し、MySQLエンジンはストレージエンジンからレコードのチェックインまたはソートを実行します.
最初のステップと2番目のステップはほとんどMySQLエンジンで処理され、3番目のステップはMySQLエンジンとストレージエンジンが同時に処理に参加します.次の図は、MySQL全体のアーキテクチャにおける「SQL解析器」と「オプティカルドライブ」の位置を示しています.

2.光学ドライブの種類

オプティカル(光学式)ドライブは、データベース・サーバで脳と同じ役割を果たしています.オプティカル(光学式)ドライブは、現在ほとんどのDBMSで選択されているコストベースの最適化(Cost Optimizer.CBO)方法と、以前Oracleでよく使用されていたルールベースの最適化方法(Rule Based Optimizer,RBO)に大別されます.

ルール・ベースの最適化は、基本的には、ターゲット・テーブルのレコード数や選択を考慮せずに、オプティカル(光学式)ドライブに組み込まれている優先度に基づいて実行計画を立てることを意味します.この方法は統計情報(表のレコード数やコラム値の分布図)を調べるのではなく、実行計画を立てるので、同じクエリに対してほぼ常に同じ実行方法を生成します.しかし、ルールベースの最適化は、多くのDBMSにおいて、以前からほとんどサポートされていないか、更新されていない状態にあった.

コストの最適化に基づいて、クエリーを処理するための複数の可能な方法が作成され、各ユニット・ジョブのコスト(負荷)情報とターゲット・テーブルで予測された統計情報を使用して、各実行計画のコストが計算されます.計算された各実行方法に基づいて、最小コストの処理方法を選択して最終クエリーを実行します.

ルールベースの最適化は、各テーブルまたはインデックスの統計がほとんどなく、CPUの演算が比較的遅いため、コスト計算プロセスの負担が重すぎるため、最適化方法です.現在、ほとんどのRDBMSはコストベースのオプティカル(光学式)ドライブを採用しており、MySQLも同様です.

3.統計

費用ベースの最適化では,統計が最も重要である.統計が正確でない場合、クエリが完全に誤った方向に実行される可能性があるためです.たとえば、1億個のレコードが格納されているテーブルの統計が更新されていないため、レコードが10個未満の場合、実際にクエリーを実行すると、オプティカル(光学式)ドライブはインデックス範囲スキャンではなく、最初から最後までテーブルを読み込むように実行される場合があります.不正確な統計のため、0.1秒で終了するクエリーには1時間かかる場合があります.
MySQLも他のDBMSのようにコストベースの最適化を使用していますが、統計は他のDBMSほど豊富ではありません.基本的に、MySQLが管理する統計は、記録数とインデックスのユニークな値の約数しかありません.OracleのようなDBMSでは、統計はかなり静的であり、収集時間が長いため、統計のみがバックアップされます.しかしMySQLでは,統計情報はユーザが気づかない瞬間に自動的に変更されるため,かなりダイナミックである.ただし、記録数が少ないと統計がかなり不正確になることが多いため、「ANALYZE」コマンドを使用して統計を強制的に更新する必要がある場合があります.特に,記録数の少ない開発的MySQLサーバでは,このような現象がしばしば発生する.
MEMORYテーブルには他の統計はありません.MYISAMとInnoDBのテーブルとインデックスの統計を以下の方法で表示できます.ANALYZEコマンドは、インデックスキー値の分布度(選択度)のみを更新し、テーブル全体のカウントはテーブルの総ページ数で予測します.

SHOW TABLE STATUS LIKE 'tb_test'\G
SHOW INDEX FROM tb_test;

統計を更新するには、以下のANALYZEを実行します.

-- // 파티션을 사용하지 않는 일반 테이블의 통계 정보 수집
ANALYZE TABLE tb_test;

-- // 파티션을 사용하는 테이블에서 특정 파티션의 통계 정보 수집
ALTER TABLE tb_test ANALYZE PARTITION p3;

ANALYZEの実行中はMyISAMテーブルは読み込めますが書き込みはできません.ただしInnoDBテーブルは読み書きができないので、サービス中にANALYZEを実行しないほうがよい.MyISAMテーブルのANALZYEは、インデックス全体をスキャンして正確なキー値分布図を得るために時間がかかります.これとは異なり,InnoDBテーブルはインデックスページでランダムに8個程度を選択して解析し,結果をインデックスの統計情報に更新する.
MySQL 5.1.38以降のバージョンでは、統計を収集するために8つのインデックス・ページをランダムに読み込みますが、MySQL 5.1.38以降では、分析するインデックス・ページの数を「Innodbstatus sample pages」パラメータに指定できます.分析するページ数が増えるにつれて、より正確な統計を収集できますが、InnoDBの統計は他のDBMSよりも頻繁に収集され、サービス中に統計を収集することもできます.InnoDB統計収集のインデックスページ数は、デフォルト値の8～2～3倍以上に設定することが望ましい.
リファレンス

Real MySQL