MySQL は低速クエリを可能にします (EXPLAIN SQL ステートメントの使用の概要)

今日、データベース操作はますますアプリケーション全体のパフォーマンスのボトルネックになりつつあり、これは Web アプリケーションで特に顕著です。データベースのパフォーマンスに関しては、DBA が心配する必要があるだけでなく、私たちプログラマーも注意を払う必要があります。データベースのテーブル構造を設計し、データベースを操作する場合（特にテーブルを参照するときの SQL ステートメント）は、データ操作のパフォーマンスに注意を払う必要があります。

1. スロークエリを有効にする

1> スロークエリが有効になっているかどうかを確認する

「%quer%」のような変数を表示します。
slow_query_log = ON # 有効

2> 有効にする方法: my.cnf ディレクトリ構成

slow_query_log=on #有効にするかどうか slow_query_log_file=/opt/MySQL_Data/TEST1-slow.log #スロークエリファイルの場所 long_query_time=2 #クエリが記録されるまでに何秒かかるか

2. EXPLAINスロークエリログに表示されるSELECTクエリ

説明欄の説明

• テーブル: この行のデータがどのテーブルに関するものかを表示します

• type: これは重要な列であり、使用されている接続のタイプを示します。接続タイプは、最良から最悪まで、const、eq_reg、ref、range、index、allです。

• possible_keys: このテーブルに適用できるインデックスを表示します。空の場合、インデックスは作成できません。関連するドメインのwhere句から適切なステートメントを選択できます。

• key: 実際に使用されるインデックス。 null の場合、インデックスは使用されません。まれに、MySQL が十分に最適ではないインデックスを選択する場合があります。この場合、select ステートメントで use index (indexname) を使用してインデックスの使用を強制したり、 ignore index (indexname) を使用して MySQL にインデックスを無視させたりすることができます。

• key_len: 使用されるインデックスの長さ。長さが短いほど、精度を失わずに良くなります。

• ref: インデックスのどの列が使用されているかを示します（可能な場合は定数）。

• 行数: 要求されたデータを返すためにMySQLがチェックする必要があると考える行数

• extra: MySQL がクエリを解析する方法に関する追加情報。たとえば、temporary と filesort を使用すると、MySQL はインデックスをまったく使用できなくなるため、取得が遅くなります。

key_lenの計算

1. すべてのインデックス フィールドが null 以外に設定されていない場合は、1 バイトを追加する必要があります。

2. 固定長フィールド: int は 4 バイト、date は 3 バイト、char(n) は n 文字を占めます。

3. varchar(n) フィールドの場合、n 文字 + 2 バイトが存在します。

4. 文字セットによって、文字ごとに使用するバイト数が異なります。 latin1 エンコーディングでは 1 文字が 1 バイトを占め、gbk エンコーディングでは 1 文字が 2 バイトを占め、utf8 エンコーディングでは 1 文字が 3 バイトを占めます。

3. インデックス構築のいくつかの原則

• 左端のプレフィックス一致の原則は非常に重要な原則です。MySQL は範囲クエリ (>、<、between、like) に遭遇して一致を停止するまで、右方向に一致し続けます。たとえば、(a、b、c、d) の順序で a = 1 かつ b = 2 かつ c > 3 かつ d = 4 を作成した場合、d はインデックスで使用されません。(a、b、d、c) の順序でインデックスを作成した場合、すべて使用できます。a、b、d の順序は任意に調整できます。

• = および in は任意の順序にすることができます。たとえば、a = 1、b = 2、c = 3 などです。(a、b、c) インデックスは任意の順序で作成でき、MySQL クエリ オプティマイザはインデックスが認識できる形式に最適化するのに役立ちます。

• インデックスとして識別性の高い列を選択するようにしてください。識別の式は count(一意の列)/count(*) で、重複しないフィールドの比率を示します。比率が大きいほど、スキャンする必要があるレコードが少なくなります。一意のキーの識別は 1 ですが、ビッグ データでは、ステータス フィールドや性別フィールドの識別は 0 になる場合があります。この比率に経験的な価値があるのか​​と疑問に思う人もいるかもしれません。この値は、使用シナリオが異なるため、決定するのが困難です。通常、結合するフィールドの値は 0.1 を超える必要があります。つまり、各フィールドで平均 10 件のレコードがスキャンされることになります。

• インデックス列は計算や関数では使用できないため、列をクリーンな状態に保ってください。

• インデックスを可能な限り拡張し、新しいインデックスを作成しないようにしてください。たとえば、テーブルにすでにインデックス a があり、インデックス (a,b) を追加する場合は、元のインデックスを変更するだけで済みます。