MySQL インデックスの原理と最適化の詳細な説明

序文

この記事は Meituan の大物によって書かれました。とても素晴らしいので、皆さんと共有したいと思います。コード内の HTML に埋め込まれた SQL ステートメントは、Java フレームワークで記述されています。実行する SQL ステートメントを理解するだけで十分です。

背景

優れたパフォーマンス、低コスト、豊富なリソースを備えた MySQL は、ほとんどのインターネット企業に好まれるリレーショナル データベースになりました。優れた性能を持ちますが、「良い馬には良い鞍がふさわしい」ということわざがあるように、それをより良く使う方法は開発エンジニアにとって必修科目となっています。求人情報には「MySQLに精通している」「SQL文の最適化」「データベースの原則を理解している」といった要件がよく見られます。一般的なアプリケーション システムでは、読み取りと書き込みの比率は約 10:1 であり、挿入操作と一般的な更新操作でパフォーマンス上の問題が発生することはほとんどありません。最も一般的で最も問題となる操作は、いくつかの複雑なクエリ操作であるため、クエリ ステートメントの最適化が最優先事項であることは明らかです。

2013 年 7 月から、私は Meituan のコアビジネス システム部門で 10 以上のシステムをカバーし、スロー クエリの最適化に取り組んでおり、数百のスロー クエリのケースを解決して蓄積してきました。ビジネスが複雑になるにつれて、遭遇する問題は多様かつ奇妙なものになります。この記事では、開発エンジニアの観点から、データベースのインデックス作成の原則と、遅いクエリを最適化する方法を説明します。

<span class="hljs-keyword">選択</span> <span class="hljs-keyword">count</span>(*) <span class="hljs-keyword">から</span> タスク <span class="hljs-keyword">where</span> <span class="hljs-keyword">status</span>=<span class="hljs-number">2</span> <span class="hljs-keyword">および</span> operator_id=<span class="hljs-number">20839</span> <span class="hljs-keyword">および</span> operat_time><span class="hljs-number">1371169729</span> <span class="hljs-keyword">および</span> operat_time<<span class="hljs-number">1371174603</span> <span class="hljs-keyword">および</span> <span class="hljs-keyword">type</span>=<span class="hljs-number">2</span>;

システム ユーザーから、関数がだんだん遅くなってきているという報告があったため、エンジニアは上記の SQL を見つけました。

そして彼は興奮して私のところに来てこう言いました。「この SQL は最適化する必要があります。すべてのフィールドにインデックスを追加してください。」

私は驚いて、「なぜすべてのフィールドにインデックスを付ける必要があるのですか？」と尋ねました。

「すべてのクエリ フィールドにインデックスを追加すれば、より高速になります」とエンジニアは自信を持って言いました。

「この場合、結合インデックスを作成できます。左端のプレフィックス一致であるため、operation_time を最後に配置する必要があり、その他の関連クエリを取得して、包括的な評価を行う必要があります。」

「結合インデックス？左端のプレフィックス一致？包括的な評価？」エンジニアは深く考え込まざるを得ませんでした。

ほとんどの場合、インデックスによってクエリの効率が向上することはわかっていますが、インデックスはどのように作成すればよいのでしょうか?インデックスの順序は何ですか?大まかにしか知らない人も多いでしょう。実際、これらの概念を理解するのは難しくなく、インデックスの原則は想像するよりもはるかに複雑ではありません。

索引の目的

インデックスの目的は、クエリの効率を向上させることです。これは辞書に例えることができます。「mysql」という単語を検索する場合は、文字 m を見つけ、次に文字 y を下から下まで見つけ、次に sql の残りの部分を見つける必要があります。索引がない場合、必要な単語を見つけるにはすべての単語を調べる必要があるかもしれません。m で始まる単語を見つけたい場合はどうすればよいでしょうか。それともzeで始まる単語ですか?インデックスなしではこのタスクを完了できないと思いますか?

インデックスの原則

辞書以外にも、駅の電車の時刻表や本のカタログなど、生活のあらゆるところに索引の例が見られます。それらの原理は同じで、取得したいデータの範囲を継続的に絞り込むことで最終的な目的の結果をフィルタリングし、同時にランダムなイベントを連続したイベントに変換することです。つまり、常に同じ検索方法を通じてデータをロックします。

データベースでも同じことが言えますが、等価クエリだけでなく、範囲クエリ (>、<、between、in)、あいまいクエリ (like)、結合クエリ (or) なども発生するため、明らかにはるかに複雑です。データベースは、すべての問題に対処するためにどのように選択すればよいでしょうか?辞書の例をもう一度考えてみましょう。データをセグメントに分割し、セグメントごとにクエリを実行することはできますか?最も簡単な方法は、1,000 個のデータを 1 から 100 までの数字を含む最初のセクション、101 から 200 までの数字を含む 2 番目のセクション、201 から 300 までの数字を含む 3 番目のセクションに分割することです。次に、250 番目のデータを確認するには、3 番目のセクションを見つけるだけでよいため、無効なデータの 90% を一度に排除できます。しかし、レコードが 1,000 万件ある場合は、いくつのセグメントに分割すればよいのでしょうか?アルゴリズムの基礎知識を持つ学生は、平均複雑度が lgN でクエリ パフォーマンスが優れている検索ツリーについて考えるでしょう。しかし、ここで重要な問題を見落としています。複雑性モデルは、毎回同じ操作コストに基づいています。データベースの実装は比較的複雑で、データはディスクに保存されます。パフォーマンスを向上させるために、データの一部を毎回メモリに読み込んで計算することができます。ディスクへのアクセスコストはメモリへのアクセスコストの約 10 万倍であることがわかっているため、単純な検索ツリーでは複雑なアプリケーション シナリオに対応することが困難です。

ディスクIOと先読み

ディスクアクセスについては先ほど触れたので、ここではディスクIOと事前読み取りについて簡単に紹介します。ディスクの読み取りは機械的な動きに依存しています。各データの読み取りにかかる時間は、シーク時間、回転遅延、転送時間の3つに分けられます。シーク時間は、磁気アームが指定されたトラックに移動するのに必要な時間を指し、主流のディスクは通常5ms以下です。回転遅延は、よく耳にするディスクの回転速度です。たとえば、7200rpmのディスクは、1分間に7200回回転できることを意味し、1秒間に120回回転できることを意味します。回転遅延は1/120/2 = 4.17msです。転送時間は、ディスクからデータを読み取ったり書き込んだりするのにかかる時間を指し、通常は10分の1ミリ秒程度で、最初の2回と比較すると無視できます。したがって、ディスクにアクセスする時間、つまりディスク IO の時間は、約 5+4.17 = 9 ミリ秒になります。これは良いように思えますが、命令は電気の性質に依存するため、500 MIPS マシンは 1 秒あたり 5 億命令を実行できることを知っておく必要があります。言い換えると、IO の実行時間で 40 万命令を実行できるということです。データベースには、数十万、数百万、さらには数千万のデータが含まれることがよくあります。毎回 9 ミリ秒というのは明らかに大惨事です。次の図は、参考までにコンピューター ハードウェアの遅延の比較です。

さまざまなシステムソフトウェアハードウェアの遅延

ディスク IO は非常にコストのかかる操作であることを考慮して、コンピューターのオペレーティング システムではいくつかの最適化が行われています。IO 中は、現在のディスク アドレスのデータだけでなく、隣接するデータもメモリ バッファーに読み込まれます。これは、ローカル事前読み取りの原理により、コンピューターがアドレスのデータにアクセスすると、そのアドレスに隣接するデータにもすばやくアクセスされるためです。 IO によって毎回読み取られるデータはページと呼ばれます。ページの具体的なサイズはオペレーティング システムによって異なりますが、通常は 4k または 8k です。つまり、ページ内のデータを読み取る場合、実際に発生する IO は 1 つだけです。この理論は、インデックス データ構造の設計に非常に役立ちます。

インデックスデータ構造

先ほど、インデックスの実例、インデックスの基本原則、データベースの複雑さ、オペレーティング システムに関する関連知識について説明しました。目的は、データ構造がどこからともなく現れるわけではなく、その背景と使用シナリオがあるはずだということを皆さんに理解してもらうことです。では、このデータ構造に何が必要なのかをまとめてみましょう。実際、それは非常に単純です。つまり、データを検索するたびに、ディスク IO 回数を非常に小さな桁、できれば一定の桁に制御するのです。では、高度に制御可能な多方向検索ツリーがニーズを満たすことができるかどうか疑問に思います。このようにして、b+ツリーが誕生しました。

b+ツリーの詳細な説明

b+ツリー

上の図に示すように、これは b+ ツリーです。b+ ツリーの定義については、B+ ツリーを参照してください。ここでは、いくつかの重要な点についてのみ説明します。水色のブロックはディスク ブロックと呼ばれます。各ディスク ブロックには、いくつかのデータ項目 (濃い青で表示) とポインター (黄色で表示) が含まれていることがわかります。たとえば、ディスク ブロック 1 にはデータ項目 17 と 35 が含まれており、ポインター P1、P2、および P3 が含まれています。P1 は 17 未満のディスク ブロックを表し、P2 は 17 から 35 の間のディスク ブロックを表し、P3 は 35 より大きいディスク ブロックを表します。実際のデータは、リーフ ノード 3、5、9、10、13、15、28、29、36、60、75、79、90、および 99 に存在します。非リーフ ノードには実際のデータは保存されず、検索方向を指示するデータ項目のみ保存されます。たとえば、17 と 35 は実際にはデータ テーブルに存在しません。

b+ツリーの探索プロセス

図に示すように、データ項目 29 を検索する場合、最初にディスク ブロック 1 がディスクからメモリにロードされます。このとき、IO が発生します。メモリ内でバイナリ検索を使用して、29 が 17 と 35 の間にあることを判別します。ディスク ブロック 1 の P2 ポインターはロックされています。メモリ時間は非常に短いため (ディスク IO と比較して) 無視できます。ディスク ブロック 3 は、ディスク ブロック 1 の P2 ポインターのディスク アドレスを介してディスクからメモリにロードされます。2 番目の IO が発生します。29 は 26 と 30 の間にあります。ディスク ブロック 3 の P2 ポインターはロックされています。ディスク ブロック 8 は、ポインターを介してメモリにロードされます。3 番目の IO が発生します。同時に、メモリ内でバイナリ検索を実行して 29 を見つけ、クエリが終了します。合計 3 つの IO が実行されます。実際には、3 層の B+ ツリーは数百万のデータを表すことができます。数百万のデータの検索に 3 つの IO しか必要ない場合、パフォーマンスは大幅に向上します。インデックスがない場合、各データ項目に IO が必要になり、合計で数百万の IO が必要になりますが、これは明らかに非常にコストがかかります。

B+ツリーのプロパティ

以上の分析から、IO回数はb+numberの高さhに依存することがわかります。現在のデータテーブルのデータがNで、各ディスクブロックのデータ項目数がmであると仮定すると、h=㏒(m+1)Nとなります。データ量Nが一定の場合、mが大きいほどhは小さくなり、m = ディスクブロックサイズ/データ項目サイズとなります。ディスクブロックサイズはデータページのサイズで、固定されています。データ項目が占めるスペースが小さく、データ項目数が多いほど、ツリーの高さは低くなります。このため、各データ項目、つまりインデックス フィールドは、できるだけ小さくする必要があります。たとえば、int は 4 バイトを占めますが、これは bigint の 8 バイトの半分です。このため、b+ ツリーでは、実際のデータを内部ノードではなくリーフ ノードに配置する必要があります。内部ノードに配置すると、ディスク ブロックのデータ項目が大幅に減少し、ツリーの高さが増加します。データ項目が 1 に等しい場合、線形リストに退化します。

b+ツリーのデータ項目が（名前、年齢、性別）などの複合データ構造である場合、b+ツリーは左から右の順に検索ツリーを構築します。たとえば、（張三、20、F）などのデータが取得されると、b+ツリーは最初に名前を比較して次の検索方向を決定します。名前が同じ場合は、年齢と性別が順番に比較され、最終的に取得されたデータを取得します。ただし、（20、F）などの名前のないデータが来ると、b+ツリーは次にどのノードをチェックすればよいかわかりません。これは、名前が検索ツリーを構築するときの最初の比較要素であり、次にどこを照会するかを知るために最初に名前に基づいて検索する必要があるためです。たとえば、(Zhang San, F) のようなデータを取得する場合、b+ ツリーは名前を使用して検索方向を指定できますが、次のフィールド age が欠落しているため、名前が Zhang San と同じデータのみを検索し、その後、性別が F のデータと一致させます。これは非常に重要なプロパティであり、インデックスの最も左の一致機能です。

MySQL インデックスの原理は比較的退屈です。知覚的に理解するだけでよく、徹底的かつ深く理解する必要はありません。冒頭でお話ししたスロークエリを振り返ってみましょう。インデックスの原則を理解した上で、何かアイデアはありますか?まず、インデックスの基本原則をまとめてみましょう。

インデックスを構築するためのいくつかの主要な原則：左端のプレフィックス一致の原則、非常に重要な原則。MySQL は、範囲クエリ (>、<、between、like) に遭遇するまで右に一致を続け、その後一致を停止します。たとえば、a = 1、b = 2、c > 3、d = 4 の場合、(a、b、c、d) の順序でインデックスを構築すると、d はインデックスで使用されません。(a、b、d、c) の順序でインデックスを構築すると、すべてが使用でき、a、b、d の順序は任意に調整できます。 = および in は任意の順序にすることができます。たとえば、a = 1、b = 2、c = 3 などです。(a、b、c) インデックスは任意の順序で作成でき、MySQL クエリ オプティマイザはインデックスが認識できる形式に最適化するのに役立ちます。インデックスとして、識別度の高い列を選択するようにしてください。識別度の計算式は count(distinct col)/count(*) で、重複しないフィールドの比率を示します。比率が大きいほど、スキャンする必要があるレコードが少なくなります。一意のキーの識別度は 1 ですが、ビッグ データでは、ステータス フィールドや性別フィールドの識別度は 0 になる場合があります。この比率に経験的な価値があるのか​​と疑問に思う人もいるかもしれません。この値は、使用シナリオが異なるため、決定するのが困難です。通常、結合するフィールドの値は 0.1 を超える必要があります。つまり、各フィールドで平均 10 件のレコードがスキャンされることになります。インデックス列は計算に参加できません。列を「クリーン」な状態に保ってください。たとえば、from_unixtime(create_time) = '2014-05-29' の場合、インデックスは使用できません。理由は簡単です。b+ ツリーはフィールド値をデータ テーブルに格納します。ただし、検索時にはすべての要素を関数と比較する必要があり、明らかにコストがかかりすぎます。したがって、ステートメントは create_time = unix_timestamp('2014-05-29') と記述する必要があります。インデックスを可能な限り拡張し、新しいインデックスを作成しないようにしてください。たとえば、テーブルにすでにインデックス a があり、インデックス (a,b) を追加する場合は、元のインデックスを変更するだけで済みます。最初に遅いクエリに戻る

左端一致の原則によれば、最初の SQL ステートメントのインデックスは、status、operator_id、type、operate_time の結合インデックスである必要があります。status、operator_id、type の順序は逆になる可能性があるため、このテーブルの関連するすべてのクエリを見つけて包括的に分析する必要があると述べました。たとえば、次のクエリもあります。

<span class="hljs-keyword">選択</span> * <span class="hljs-keyword">から</span> タスク <span class="hljs-keyword">場所</span> <span class="hljs-keyword">ステータス</span> = <span class="hljs-number">0</span> <span class="hljs-keyword">かつ</span> <span class="hljs-keyword">タイプ</span> = <span class="hljs-number">12</span> <span class="hljs-keyword">制限</span> <span class="hljs-number">10</span>;

<span class="hljs-keyword">選択</span> <span class="hljs-keyword">カウント</span>(*) <span class="hljs-keyword">から</span> タスク <span class="hljs-keyword">場所</span> <span class="hljs-keyword">ステータス</span> = <span class="hljs-number">0</span>;

その場合、すべての状況をカバーできるため、(status、type、operator_id、operate_time) のインデックスを作成するのが非常に適切です。これはインデックスの左端一致の原則です。

クエリ最適化ツール – explain コマンド

explain コマンドは皆さんもよくご存知だと思います。具体的な使用方法とフィールドの意味については、公式 Web サイトの explain-output を参照してください。ここで強調しておきたいのは、行がコア インジケータであるということです。行数が小さいステートメントのほとんどは、非常に高速に実行する必要があります (例外もありますが、これについては後述します)。したがって、最適化ステートメントは基本的に行を最適化します。

低速クエリの最適化の基本手順: 最初にクエリを実行して、本当に遅いかどうかを確認します。単一テーブル クエリに SQL_NO_CACHEwhere 条件を設定し、最小の戻りレコード テーブルをロックする必要があることに注意してください。この文は、返されるレコード数が最も少ないテーブルにクエリ ステートメントの where 句を適用することを意味します。まず、単一のテーブルの各フィールドをクエリして、どのフィールドが最も識別力が高いかを確認します。実行プランがステップ 1 の期待と一致しているかどうかを確認する方法について説明します (レコード数が少ないテーブルからクエリを開始します)。order by limit 形式の SQL ステートメントにより、ソートされたテーブルを最初に検索できます。ビジネス側の使用シナリオを理解します。インデックスを追加するときは、インデックス作成の主要な原則を参照してください。結果を観察します。期待どおりでない場合は、ステップ 0 からいくつかの遅いクエリ ケースを分析し続けます。

次の例では、遅いクエリを分析して最適化する方法を詳しく説明します。

複雑な文章の書き方

多くの場合、関数を実装するためだけに SQL を記述します。これは最初のステップにすぎません。ステートメントの記述方法が異なると、効率に本質的な違いが生じることがよくあります。このため、MySQL の実行プランとインデックスの原則を明確に理解する必要があります。次のステートメントをご覧ください。

<span class = "hljs-keyword"> select </span> <span class = "hljs-keyword">個別</span> cert.emp_id <span class = "hljs-keyword"> /span> emp.id <span class = "hljs-keyword"> as </span> emp_id、emp_cert.id <span class = "hljs-keyword"> cert <span class = "hljs-keyword"> on </span> emp.id = emp_cert.emp_id <span class = "hljs-keyword"> where </span> emp.is_deleted = "hljs-number"> 0 </span>） 「従業員」</span> <span class = "hljs-keyword"> and </span> cl.ref_oid = cert.emp_id）<span class = "hljs-keyword">または</span>（cl.ref_table = <span class = "hljs-string"> 'empcepiftate id）<span class = "hljs-keyword">ここで、</span> cl.last_upd_date> = <span class = "hljs-string"> '2013-11-07 15:03:00' </span> <span class = "hljs-keyword"> and </span '</span>;

まず実行すると、1.87秒で53件のレコードが処理され、集計ステートメントは使用されていないため、処理が遅くなります。

<span class="hljs-keyword">セット</span>に 53 行あります (<span class="hljs-number">1.87</span> 秒)

説明する

+クラス= ------------+-----------------------------------------+--------------------------------行|+<span class = --------+----------------+----------+----------------- AST_UPD_DATE | null | ------------+-----------+-------+----------------------------------------------------------------------------------+---------------+-------+----------------------------------+</span>

実行プランを簡単に説明すると、まずMySQLはidx_last_upd_dateインデックスに基づいてcm_logテーブルをスキャンし、379件のレコードを取得します。次にテーブルを検索し、2つの部分に分かれた63,727件のレコードをスキャンします。派生とは、構築されたテーブル、つまり存在しないテーブルを意味し、これは単にステートメントによって形成された結果セットとして理解でき、その後ろの数字はステートメントIDを表します。 Derived2 は、ID = 2 のクエリによって仮想テーブルが構築され、63727 件のレコードが返されたことを示します。 ID = 2 のステートメントが大量のデータを返すために何を行うかを見てみましょう。まず、従業員テーブルが完全にスキャンされ、13,317 件のレコードが検索されます。次に、emp_certificate テーブルがインデックス emp_certificate_empid に関連付けられます。行 = 1 は、各関連付けで 1 つのレコードのみがロックされることを意味し、より効率的です。取得後、ルールに従ってcm_logの379件のレコードと関連付けます。実行プロセスから、返されるデータが多すぎることがわかります。また、cm_log は 379 レコードのみをロックするため、返されたデータのほとんどは cm_log によって使用されません。

どのように最適化するのでしょうか?実行後も cm_log と結合する必要があることがわかりますが、実行前に cm_log と結合できますか?ステートメントを注意深く分析すると、基本的な考え方は、cm_log の ref_table が EmpCertificate の場合、emp_certificate テーブルに関連付けられ、ref_table が Employee の場合、employee テーブルに関連付けられるというものであることがわかります。これを 2 つの部分に完全に分割し、union を使用して接続することができます。元のステートメントには一意のレコードを取得するために「distinct」があり、union にはたまたまこの機能があるため、ここで union all ではなく union が使用されていることに注意してください。元のステートメントに distinct がなく、重複排除が不要な場合は、union all を直接使用できます。これは、union を使用すると重複排除が必要になり、SQL のパフォーマンスに影響するためです。

最適化されたステートメントは次のとおりです。

<span class = "hljs-keyword"> select </span> emp.id <span class = "hljs-keyword"> from </span> cm_log cl <span class = "hljs-keyword"> inner </span> <span class = "hljs-keyword"> tring "> 'Employee' </span> <span class =" hljs-keyword "> and </span> cl.ref_oid = span class =" hljs-keyword "> cl.last_upd_date> = <span class =" hljs-string "> .last_upd_date <= <span class = "hljs-string"> '2013-11-08 16:00:00' </span> <span class = "hljs-keyword">および</span> is_deleted = <span class = "hljs-number"> /span> emp.id <span class = "hljs-keyword"> from </span> cm_log cl <span class = "hljs-keyword"> span class = "hljs-keyword"> emp_certificate ec <span class = "hljs-keyword "> </span> <span class = "hljs-keyword"> and </span> cl.ref_oid = ec.id <span class = "hljs-keyword"> inner </span class = "hljs-keyword" AST_UPD_DATE> = <SPAN CLASS = "HLJS-STRING"> '2013-11-07 15:03:00' </span> <span class = "hljs-keyword"> cl.last_upd_date <= <span class = "hljs-string "> emp.is_deleted = <span class = "hljs-number"> 0 </span>

ビジネス シナリオを理解する必要はなく、変換後のステートメントの結果を変換前のステートメントと一貫性のある状態に保つことだけが必要です。

既存のインデックスでこの要件を満たすことができ、インデックスの作成は不要です。

変更されたステートメントの実験にはわずか 10 ミリ秒しかかかりません。これは、ほぼ 200 倍の高速化です。

+<span class="hljs-comment">----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+</span>| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+<span class="hljs-comment">----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+</span>| 1 | PRIMARY | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where || 1 | PRIMARY | emp | eq_ref | PRIMARY | PRIMARY | 4 | meituanorg.cl.ref_oid | 1 | Using where || 2 | UNION | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where || 2 | UNION | ec | eq_ref | PRIMARY,emp_certificate_empid | PRIMARY | 4 | meituanorg.cl.ref_oid | 1 | || 2 | UNION | emp | eq_ref | PRIMARY | PRIMARY | 4 | meituanorg.ec.emp_id | 1 | Using where || NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |+<span class="hljs-comment">----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+</span>53 rows in <span class="hljs-keyword">set</span> (<span class="hljs-number">0.01</span> sec)

アプリケーションシナリオを特定する

この例を挙げる目的は、列の独自性に関する私たちの理解を覆すことです。一般的に、列の独自性が高ければ高いほど、ロックするレコードの数が少なくなると考えられます。ただし、特殊なケースでは、この理論には限界があります。

<span class="hljs-keyword">選択</span> * <span class="hljs-keyword">から</span> stage_poi sp <span class="hljs-keyword">ここで</span> sp.accurate_result=<span class="hljs-number">1</span> <span class="hljs-keyword">かつ</span> ( sp.sync_status=<span class="hljs-number">0</span> <span class="hljs-keyword">または</span> sp.sync_status=<span class="hljs-number">2</span> <span class="hljs-keyword">または</span> sp.sync_status=<span class="hljs-number">4</span> );

実行にどれくらい時間がかかるか見てみましょう。951 個のデータに対して 6.22 秒と、非常に遅いです。

<span class="hljs-keyword">セット</span>内の行数は 951 行です (<span class="hljs-number">6.22</span> 秒)

まず、行数が 361 万に達し、type = ALL が完全なテーブルスキャンを示していることを説明します。

<clase = ---------+--------+-------+--------------- -comment "> ----+------------+-------+-----+--------------+-----+-------+-----+----+---------+</span> | 1 | sime | sp | null | null | null | null | null | 3613155 | +------------+---+-------+------------+------+-------+------+--------+-------+------+-------+</span>

すべてのフィールドをクエリしてレコード数を返します。単一テーブル クエリなので、951 件のレコードが作成されました。

説明される行数をできるだけ 951 に近づけます。

precise_result = 1 となるレコードの数を見てみましょう。

<span class="hljs-keyword">選択</span> <span class="hljs-keyword">count</span>(*),accurate_result <span class="hljs-keyword">from</span> stage_poi <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> precise_result;+<span class="hljs-comment">----------+----------------+</span>| count(*) | precise_result |+<span class="hljs-comment">----------+----------------+</span>| 1023 | -1 || 2114655 | 0 || 972815 | 1 |+<span class="hljs-comment">----------+----------------+</span>

Accuracy_result フィールドの識別性が非常に低いことがわかります。テーブル全体には、-1、0、1 の 3 つの値しかありません。インデックスを使用しても、特に少量のデータをロックすることはできません。

sync_status フィールドを見てみましょう。

<span class="hljs-keyword">選択</span> <span class="hljs-keyword">count</span>(*),sync_status <span class="hljs-keyword">から</span> stage_poi <span class="hljs-keyword">グループ</span> <span class="hljs-keyword">by</span> sync_status;+<span class="hljs-comment">----------+--------------+</span>| count(*) | sync_status |+<span class="hljs-comment">----------+-------------+</span>| 3080 | 0 || 3085413 | 3 |+<span class="hljs-comment">---------+-------------+</span>

同様の識別力も非常に低く、理論的にはインデックス作成には適していません。

ここまで問題を分析した結果、このテーブルは最適化できないという結論に達したようです。2 つの列の区別は非常に低く、インデックスを追加してもこの状況に適応することしかできず、一般的な最適化を行うことは困難です。たとえば、sync_status 0 と 3 が均等に分散されている場合、ロックされたレコードの数も数百万になります。

ビジネス側とコミュニケーションを取り、使用シナリオを検討します。ビジネス側ではこの SQL 文を次のように使用します。5 分ごとに適格データをスキャンし、処理後に sync_status フィールドを 1 に変更します。5 分間の適格レコード数はそれほど多くなく、約 1,000 件です。ビジネス側の使用シナリオを理解すると、ビジネス側がデータの不均衡を保証するため、この SQL の最適化は簡単になります。インデックスを追加すると、不要なデータのほとんどを除外できます。

インデックス作成ルールに従って、次のステートメントを使用してインデックスを作成します。

<span class="hljs-keyword">変更</span> <span class="hljs-keyword">テーブル</span> stage_poi <span class="hljs-keyword">追加</span> <span class="hljs-keyword">インデックス</span> idx_acc_status(accurate_result,sync_status);

予想された結果を観察すると、わずか 200 ミリ秒しかかからず、30 倍以上高速であることがわかりました。

<span class="hljs-keyword">セット</span>内の行数は 952 です (<span class="hljs-number">0.20</span> 秒)

問題を分析するプロセスを見直してみましょう。単一テーブル クエリは比較的簡単に最適化できます。ほとんどの場合、ルールに従って where 条件のフィールドにインデックスを追加するだけで済みます。これが単なる「無神経な」最適化である場合、差別化が非常に低い列やインデックスを付けるべきではない列にもインデックスが付けられ、挿入と更新のパフォーマンスに重大な影響を及ぼし、他のクエリ ステートメントにも影響する可能性があります。したがって、SQL デバッグの 4 番目のステップの使用シナリオは非常に重要です。このビジネス シナリオを把握して初めて、クエリ ステートメントをより適切に分析および最適化できるようになります。

最適化できないステートメント

<span class = "hljs-keyword"> select </span> c.id、C.Name、C.Position、C.Sex、C.Phone、C.Office_Phone、C.Birthday、C.Creator_id、C.IS_Keyperson、C.GiveUp_Reason、C.Status、C.Data_Source、 span> created_time、from_unixtime（c.last_modified）<span class = "hljs-keyword"> as </span> last_modified、c.last_modified_user_id <span class = "hljs-keyword"> class = "hljs-keyword"> on </span> cb.contact_id <span class = "hljs-keyword"> inner </span> <span class = branch_user bu <span class = "hljs-keyword" .status <span class = "hljs-keyword"> in </span>（<span class = "hljs-number"> </span>、<span class = "hljs-number"> 2 </span>）<hljs-keyword "> inner </span> <span class =" hljs-keyword "> jon < 「> on </span> oei.data_id = bu.user_id <span class = "hljs-keyword">および</span> oei.node_left> = <span class = "hljs-number"> 2875 </span> <span class = "hljs-keyword"> /span> <span class = "hljs-keyword"> and </span> oei.org_category =  -  <span class = "hljs-number"> 1 </span class = "hljs-keyword"> span> <span class = "hljs-keyword"> s-keyword "> lime </span> <span class =" hljs-number "> 0 </span>、<span class =" hljs-number "> 10 </span>;

まだ数歩残っています。

まず、ステートメントの実行にどのくらいの時間がかかるか見てみましょう。10 レコードに 13 秒かかりました。これは耐えられないほど長い時間です。

<span class="hljs-keyword">セット</span>に 10 行あります (<span class="hljs-number">13.06</span> 秒)

説明する

+クラス= --------------------------------------------------------+-----+ |+<hljs-comment "> ---------------+---------------------- categry_cation | ID | 8 | meituancrm.cb.contact_id |

実行プランから、MySQL は最初に org_emp_info テーブルをチェックして 8849 件のレコードをスキャンし、次にインデックス idx_userid_status を使用して branch_user テーブルに関連付け、次にインデックス idx_branch_id を使用して contact_branch テーブルに関連付け、最後に主キーを使用して contact テーブルに関連付けます。

返される行数は非常に少なく、異常は見られません。ステートメントを見ると、最後に order by + limit の組み合わせがあることがわかります。ソートの量が多すぎるのでしょうか?そこで、SQL を簡素化し、最後の order by と limit を削除して、実際にソートに使用されるレコードの数を確認します。

<span class = "hljs-keyword"> select </span> <span class = "hljs-keyword"> count </span>（*）<span class = "hljs-keyword">連絡先c <span class = "hljs-keyword"> inner </span> <span class = "hljs-keyword" > c.id = cb.contact_id <span class = "hljs-keyword"> inner </span> <span class = "hljs-keyword"> branch_user bu <span class = "hljs-keyword"> </span> cb.branch_id = bu.branch_id > in </span>（<span class = "hljs-number"> 1 </span>、<span class = "hljs-number"> 2 </span>）<span class = "hljs-keyword"> span> <span class = "hljs-keyword"> = bu.user_id <span class = "hljs-keyword"> and </span> oei.node_left> = <span class = "hljs-number"> 2875 </span> <span class = "hljs-keyword"> span> oei.org_category =  -  <span class = "hljs-number"> </span> <span class = "hljs-comment"> 78 </span> |+<span class = "hljs-comment"> ----------+</span> <span class = "hljs-number"> 1 </span> <span class = span class = "hljs-keyword"> /span> sec）

並べ替え前に 778,878 件のレコードがロックされていることがわかりました。700,000 件の結果セットを並べ替えると、悲惨な結果になります。遅いのも当然です。考え方を変えて、まず連絡先の created_time で並べ替えてから結合することはできますか? 速くなりますか?

したがって、これは次のステートメントに変換され、これも straight_join を使用して最適化できます。

Select C.Id、C.Name、C.Position、C.Sex、C.Phone、C.Office_Phone、C.Feature_Info、C.Creator_id、C.IS_Keyperson、C.Giveup_Reason、C.Data_Source、 ER_IDFROM CONTACT CHERESが存在する（contact_branch cbinnerから1を選択するbranch_user cb.branch_id = bu.branch_idand bu.status in（1、2）inner Join org_emp_info oeion ategory =  -  1where c.id = cb.contact_id）by c.created_time desc limit 0、10;

予想される効果を確認します

<span class = "hljs-number"> 1 </span> ms内で、<span class = "hljs-number"> 13000 </span>を超えて増加しました！ sql <span class = "hljs-number"> 10 </span> rows <span class = "hljs-keyword"> in </span> <span class = "hljs-keyword"> set </span>（<span class = "hljs-number"> 0.00 </span> sec）

私たちはこれを完了したと思いましたが、理論的には、最初に参加してから参加するのと同じです。一般的な実行プロセスは、最初の10のレコードを取得するためにインデックスで並べ替えて、10のレコードがあることを発見します。

異なるパラメーターでSQLをテストします：

<span class = "hljs-keyword"> select </span> sql_no_cache c.id、c.name、c.position、c.sex、c。phone、c.feature_info、c.birthday、c.creator_id、c.is_keyperson、c.giveup_reason、c.data_surce、c.data_source、c.data_source s-keyword "> as </span> created_time、from_unixtime（c.last_modified）<span class =" hljs-keyword "> as </span> last_modified、c.last_modified_user_id <span class =" hljs-keyword "> span>（<span class = "hljs-keyword"> select </span> <span class = "hljs-number"> </span> <span class = "hljs-keyword"> contact_branch cb <span class = "hljs-keyword"> inner </span <span <span = " /span> cb.branch_id = bu.branch_id <span class = "hljs-keyword"> and </span> bu.status <span class = "hljs-keyword"> in </span = "hljs番号" ljs-keyword "> Join </span> org_emp_info oei <span class =" hljs-keyword "> oei.data_id = bu.user_id <span class =" hljs-keyword "> and </span> oei.node_left>および</span> oei.node_right <= <span class = "hljs-number"> 2875 </span> <span class = "hljs-keyword"> and </span> oei.org_category = <span class = "hljs-number"> 1 </span <span < -KEYWORD ">注文</span> <span class =" hljs-keyword "> by </stan> word "> set </span>（<span class =" hljs-number "> 2 </span> <span class =" hljs-keyword "> min </span> <span class =" hljs-number "> 18.99 </span> sec）

2分18.99秒！以前よりもはるかに悪いことです。 MySQLのネストされたループメカニズムにより、この状況では基本的に最適化することは不可能です。最終的に、このステートメントは、独自のロジックを最適化するためにアプリケーションシステムにのみ引き渡すことができます。 この例を通じて、すべてのステートメントが最適化されるわけではないことがわかります。したがって、1つ目は、すべてのステートメントがSQLを使用して最適化されることを期待しないでください。

これはスロークエリケースの分析であり、上記はいくつかの典型的なケースです。最適化プロセスでは、16のテーブル結合を含む1,000列以上の「Garbage SQL」に遭遇し、オンラインおよびオフラインデータベースの違いに遭遇し、スロークエリによってアプリケーションが死に至りました。どれだけのケースがあっても、それは単なる経験の蓄積です。

この記事では、MySQLインデックスの原則と、ゆっくりとしたクエリケースを使用してスロークエリを最適化するためのいくつかの方法論を紹介します。実際、非常に長いステートメントの最適化を行った後、データベースレベルの最適化はアプリケーションシステムの最適化にも耐えられないことがわかりました。最近人気のあることわざを適用するには、「クエリが簡単ですが、最適化し、書いて大切にするのは簡単ではありません！」