MYSQL の 10 の典型的な最適化ケースとシナリオ

1. SQL最適化の一般的な手順

スローチェックログなどにより実行効率の低い SQL 文を見つけます。

1. SQL実行計画の分析を説明する

type 、 rows 、 filtered 、 extraには特に注意する必要があります。

上から下へ入力すると、効率がどんどん高まります

• ALL フルテーブルスキャン
• インデックスフルスキャン
• 範囲インデックス範囲スキャン、よく使用される用語 <、<=、>=、between、in およびその他の操作
• refは、非一意インデックススキャンまたは一意インデックスプレフィックススキャンを使用して、リレーショナルクエリによく表示される単一のレコードを返します。
• eq_ref は ref と似ていますが、関連するクエリに一意のインデックスと主キーを使用する点が異なります。
• const/system 単一レコードの場合、システムは一致する行の他の列を主キーや一意のインデックスクエリなどの定数として扱います。
• null MySQLはテーブルやインデックスにアクセスせず、結果を直接返します

上から下に向かって効率はどんどん高くなっていますが、コスト モデルによると、2 つのインデックス idx1(a, b, c) と idx2(a, c) があるとします。SQL は「select * from t where a = 1 and b in (1, 2) order by c」です。idx1 を使用する場合、タイプは range で、idx2 を使用する場合、タイプは ref です。スキャンする行数が idx1 の約 5 倍の場合は idx1 が使用され、それ以外の場合は idx2 が使用されます。

余分な

• filesort の使用: MySQL では、ソートされた順序で行を取得する方法を見つけるために追加のパスが必要です。ソートは、結合タイプに従ってすべての行を調べ、WHERE 句に一致するすべての行のソート キーと行ポインターを保存することによって実行されます。次にキーがソートされ、ソートされた順序で行が取得されます。
• 一時テーブルの使用: 一時テーブルは中間結果を保存するために使用されます。パフォーマンスが特に低いため、最適化する必要があります。
• インデックスの使用:対応するselect操作でCoveing Indexが使用され、テーブルのデータ行へのアクセスが回避されることを示します。これは非常に効率的です。 where の使用が同時に出現する場合、条件を満たすデータをインデックス検索を通じて直接照会することはできないことを意味します。
• インデックス条件の使用: ICP MySQL5.6以降に追加されました。 using index condtion 、サービス レイヤーでフィルタリングするのではなく、 ICP (インデックス プッシュ ダウン) を使用してストレージ エンジン レイヤーでデータをフィルタリングし、インデックス内の既存のデータを使用してテーブルに返されるデータを減らすことを意味します。

2. プロフィール分析を表示

SQL 実行スレッドのステータスと消費時間を把握します。
デフォルトではオフになっています。ステートメント「set profiling = 1;」をオンにします。

プロフィールを表示;
クエリ #{id} のプロファイルを表示します。

3. トレース

トレース アナライザーは、トレース ファイルを使用して、オプティマイザーが実行プランを選択する方法を分析します。トレース ファイルを使用すると、クーポンが実行プラン B ではなく実行プラン A を選択する理由をさらに理解できます。

optimizer_trace を「enabled=on」に設定します。
optimizer_trace_max_mem_size を 1000000 に設定します。
information_schema.optimizer_trace から * を選択します。

4. 問題を特定し、適切な措置を講じる

• インデックスを最適化する
• SQL ステートメントの最適化: SQL の変更、IN クエリのセグメンテーション、時間クエリのセグメンテーション、最後のデータに基づくフィルター
• 他の実装方法を使用します: ES、データ ウェアハウスなど。
• データ断片化処理

2. シナリオ分析（ケーススタディ）

1. 左端の一致

索引

キー `idx_shopid_orderno` (`shop_id`,`order_no`)

SQL ステートメント

orderno='' の _t から * を選択

クエリは左から右に一致します。order_no インデックスを使用するには、クエリ条件に shop_id が含まれるか、インデックス (shop_id、order_no) が入れ替わる必要があります。

2. 暗黙的な変換

索引

キー `idx_mobile` (`mobile`)

SQL ステートメント

_user から * を選択、モバイル = 12345678901

暗黙的な変換は、インデックスに対して操作を実行することと同じであり、インデックスが無効になります。 Mobile は文字型です。数値を使用する場合は、文字列の一致を使用する必要があります。そうしないと、MySQL は暗黙的な置換を使用し、インデックスが失敗します。

3. 大規模なページング

索引

キー `idx_a_b_c` (`a`, `b`, `c`)

SQL ステートメント

select * from _t where a = 1 and b = 2 order by c desc limit 10000, 10;

大規模なページング シナリオでは、製品の最適化要件を優先できます。最適化がない場合、次の 2 つの最適化方法があります。

1 つの方法は、最後のデータ、つまり上記の c を渡してから、「 c < xxx 」処理を実行することですが、これは通常、インターフェイス プロトコルを変更する必要があり、実行できない可能性があります。

もう 1 つの方法は、遅延関連付けを使用して SQL テーブルの戻りを減らすことですが、効果を上げるにはインデックスが完全にカバーされている必要があることに注意してください。SQLの変更は次のとおりです。

_t t1 から t1.* を選択します (_t から id を選択します。ここで、a = 1、b = 2、order by c、desc limit 10000、10)、t2 で t1.id = t2.id になります。

4. in + order by

索引

キー `idx_shopid_status_created` (`shop_id`、`order_status`、`created_at`)

SQL ステートメント

select * from _order where shop_id = 1 and order_status in (1, 2, 3) order by created_at desc limit 10

基盤となる MySQL では、 inクエリは n*m 方式で検索します。これはunionに似ていますが、 unionよりも効率的です。
inクエリがcostを計算するとき (コスト = タプル数 * 平均IO値)、in に含まれる値を 1 つずつクエリしてタプル数を取得します。そのため、この計算プロセスは比較的遅くなります。そのため、MySQL は臨界値 ( eq_range_index_dive_limit ) を設定します。5.6 以降では、この臨界値を超えると列のコストは計算されなくなります。これにより、実行プランの選択が不正確になる可能性があります。デフォルト値は 200 です。つまり、in 条件に 200 を超えるデータが含まれている場合、in のコスト計算で問題が発生し、MySQL によって選択されたインデックスが不正確になる可能性があります。

解決策: ( order_status, created_at )互換前后順序、SQL を遅延関連付けに調整できます。

5. 範囲クエリがブロックされ、後続のフィールドにインデックスを作成できない

索引

キー `idx_shopid_created_status` (`shop_id`、`created_at`、`order_status`)

SQL ステートメント

shop_id = 1 かつ created_at > '2021-01-01 00:00:00' かつ order_status = 10 である _order から * を選択

範囲クエリには「 IN、between 」も含まれます。

6. 等しくない、含まない、インデックスを使用できない高速検索

ICPは使用可能

shop_id=1 かつ order_status が (1,2) に該当しない _order から * を選択します。
shop_id=1 かつ order_status != 1 の場合、_order から * を選択します。

インデックスではNOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKEなどを使用しないでください。

7. オプティマイザがインデックスを使用しないことを選択した場合

アクセスする必要があるデータの量が少ない場合、オプティマイザは補助インデックスを選択します。ただし、アクセスされるデータがテーブル全体のデータの大部分 (通常は約20% ) を占める場合、オプティマイザはクラスター化インデックスを通じてデータを見つけることを選択します。

order_status = 1 の場合、_order から * を選択します。

未払いの注文をすべて照会します。通常、このような注文は非常に少ないため、インデックスを作成しても使用できません。

8. 複雑なクエリ

a = 1 かつ b が (1, 2, 3) の範囲内であり、c > '2020-01-01' である場合に、_t から sum(amt) を選択します。
a = 1 かつ b が (1, 2, 3) の範囲内であり、c > '2020-01-01' が制限 10 である _t から * を選択します。

特定のデータをカウントする必要がある場合は、データ ウェアハウスを使用して問題を解決できます。

ビジネス クエリが非常に複雑な場合は、SQL を引き続き使用するのではなく、ES を使用するなど、他の方法で解決することが推奨される場合があります。

9. 昇順と降順の混在

a=1 で _t から * を選択し、b 降順、c 昇順で並べ替えます

descとascを混在させるとインデックスエラーが発生します

10. ビッグデータ

プッシュ サービスのデータ ストレージの場合、データ量が非常に大きくなる可能性があります。ソリューションを選択する場合、最終的には MySQL に保存し、有効期間を 7 日間に設定して保存することを選択する場合があります。

次に、頻繁なデータクリーニングによってデータの断片化が発生することに注意し、データの断片化を処理するために DBA に連絡する必要があります。

これで、MYSQL の最適化の典型的なケース シナリオのトップ 10 に関するこの記事は終了です。より関連性の高い MYSQL の最適化のケース シナリオについては、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。