Tencent インタビュー: SQL ステートメントの実行が非常に遅くなる理由は何ですか? ---後悔シリーズは見ないで（推奨）

正直に言うと、この質問には MySQL のコア知識がかなり必要で、コンピュータ ネットワークの知識をテストして「URL を入力して Enter キーを押すと何が起きますか?」と尋ねられ、どれだけ答えられるかを確認されるのと同じように、多くのトピックが取り上げられる可能性があります。

正直に言うと、テンセントでの以前の面接でこの質問をされたのですが、とてもまずい答えをしました。関連する理由についてこれまで考えたことがなかったので、当分の間答えることができませんでした。そこで今日は、その理由について詳しくお話ししたいと思います。これを読めば何か得られると思いますが、そうでなければ私を殴ってください。

ふりを始めましょう: 分類の議論

SQL ステートメントの実行が非常に遅い場合、実行されるたびに遅くなることを意味しますか?それとも、ほとんどの場合は正常ですが、時々非常に遅くなるのでしょうか?したがって、次の 2 つの状況について議論する必要があると思います。

1. ほとんどの場合は正常ですが、時々非常に遅くなる場合があります。

2. データ量が変わらない場合、この SQL ステートメントは常に非常に遅く実行されます。

これら 2 つの状況について、考えられる理由を分析してみましょう。

時々スローな状況に

SQL 文はほとんどの場合正常に実行されますが、時々非常に遅く実行されることがあります。この場合、SQL 文の記述自体には問題はないと思いますが、他の原因が考えられます。それは何でしょうか?

データベースが汚いページを更新していて、私は無力です

データベースにデータを挿入したり、データを更新したりすると、データベースがメモリ内の対応するフィールドのデータを更新することはわかっています。ただし、更新後、これらの更新されたフィールドは同期されず、すぐにディスクに保持されません。代わりに、これらの更新されたレコードは redo ログに書き込まれます。アイドル状態のときは、最新のデータが redo ログのログを通じてディスクに同期されます。

ただし、REDO ログの容量には制限があります。データベースが常にビジーで、頻繁に更新される場合、REDO ログはすぐにいっぱいになります。このとき、アイドル時間まで待ってデータをディスクに同期する方法はありません。他の操作を一時停止し、データをディスクに同期することに集中することしかできません。このとき、通常の SQL ステートメントの実行が突然非常に遅くなります。したがって、データベースがデータをディスクに同期しているときに、SQL ステートメントの実行が非常に遅くなる可能性があります。

ロックが解除できない場合はどうすればいいですか?

これは比較的簡単に考えられます。実行したいステートメントには、他のユーザーが使用していてロックされているテーブルが関係しています。ロックを取得できず、他のユーザーがロックを解除するのを待つしかありません。あるいは、テーブルがロックされているのではなく、使用する行がロックされている。 現時点では、何もできません。

本当にロックを待機しているかどうかを確認したい場合は、show processlist コマンドを使用して現在のステータスを表示できます。ここで、いくつかのコマンドを記録しておくのが最善であることを思い出してください。とにかく、いくつかのコマンドを尋ねられましたが、どのように記述すればよいかわかりません、ハハ。

次に、2 番目の状況を分析します。2 番目の状況の分析が最も重要だと思います。

いつもとても遅い状況のために

データ量が同じなのにこの SQL 文の実行が毎回非常に遅い場合は、SQL の記述を慎重に検討する必要があります。SQL 文の実行が非常に遅くなる原因を分析してみましょう。

まず、主キー id と 2 つの共通フィールド c と d の 2 つのフィールドを持つテーブルがあると仮定します。

mysql> テーブル `t` を作成します (
 `id` int(11) NULLではない、
 `c` int(11) デフォルト NULL,
 `d` int(11) デフォルト NULL,
 主キー (`id`)
)ENGINE=InnoDB;

申し訳ありませんが、インデックスは使用されません。

インデックスが使用されていない。この理由は多くの人が思いつくものだと思います。たとえば、このステートメントをクエリする場合

100 <c かつ c < 100000 となる t から * を選択します。

フィールドはインデックス化されていません

c フィールドにインデックスがないため、テーブル全体をスキャンすることしかできず、インデックスがないことの楽しさを体験できないため、今回はクエリ ステートメントが非常に遅くなります。

フィールドにインデックスがありますが、そのインデックスは使用されていません

さて、c フィールドにインデックスを追加して、別のクエリを実行します。

c - 1 = 1000 となる t から * を選択します。

質問させてください。このようにクエリを実行する場合、インデックス クエリが使用されますか?

答えはノーです。フィールドの左側で計算を実行すると、残念ながら、クエリ中にインデックスは使用されません。したがって、このフィールドにインデックスがあるが、自分の不注意によりシステムがインデックスを使用しない状況には誰もが注意を払う必要があります。

正しいクエリは次のようになります

c = 1000 + 1 の場合、t から * を選択します。

右側に演算がある場合は、インデックスを使用できるのではないか、と言う人もいるかもしれません。データベースは自動的に最適化し、c - 1=1000 を c = 1000+1 に自動的に変換するのではないでしょうか?

申し訳ありませんが、私はあなたを助けることができませんので、気をつけてください。

関数操作ではインデックスは使用されません

クエリ中にフィールドに対して関数操作を実行すると、インデックスは使用されません。たとえば、

pow(c,2) = 1000となるtから*を選択します。

ここでは、関数 pow が c を n 乗する関数であると仮定して、例を挙げているだけです。実際には、関数 pow(c,2) は存在しない可能性があります。実際、これは上記の左側の計算と非常によく似ています。

したがって、ステートメントの実行が非常に遅い場合、ステートメントがインデックスを使用していない可能性があります。ただし、インデックスが使用されない具体的な理由を分析できる必要があります。上記に挙げた 3 つの理由は、最も一般的な理由です。

ハハ、データベースが間違ったインデックスを選択しました。

たとえばクエリ操作を実行すると、

100 < c かつ c < 100000 の場合、t から * を選択します。

主キー インデックスと非主キー インデックスには違いがあることはわかっています。主キー インデックスに格納される値は行フィールド全体のデータですが、非主キー インデックスに格納される値は行フィールド全体のデータではなく、主キー フィールドの値です。理解できない場合は、主キー インデックスと非主キー インデックスの違いについて説明している私の記事「面接のヒント: MySQL インデックス関連」をお読みください。

つまり、c フィールドのインデックスを使用すると、最終的に対応する主キーの値が見つかり、次に主キーの値に基づく主キー インデックスを使用して、返されるデータの行全体が見つかります。

さて、ここまで長々と話しましたが、私が実際にお伝えしたいのは、c フィールドにインデックスがあっても、システムは必ずしも c フィールドのインデックスを使用するわけではなく、100 < c かつ c < 100000 を満たすすべてのデータを見つけるためにテーブル全体を直接スキャンする場合があるということです。

なぜこのようなことが起こるのでしょうか?

実際、システムがこのステートメントを実行すると、c インデックスによってスキャンされる行数が少なくなるか、それともテーブル全体を直接スキャンすることによってスキャンされる行数が少なくなるかが予測されます。当然ですが、スキャン ラインが少ないほど良いです。スキャン ラインが少ないと I/O 操作が少なくなるからです。

テーブル全体をスキャンする場合、スキャン回数はテーブル内の行の総数（n と仮定）になります。インデックス c を使用する場合、インデックス c を通じて主キーを見つけた後、主キー インデックスを使用して行全体のデータを見つける必要があります。つまり、インデックスを 2 回使用する必要があります。さらに、100 c < かつ c < 10000 という条件を満たすデータ行がいくつあるかはわかりません。このテーブル内のすべてのデータが条件を満たす場合はどうなるでしょうか?つまり、c インデックスを使用する場合、n 行をスキャンするだけでなく、データの行ごとにインデックスを 2 回スキャンする必要があります。

したがって、システムはインデックスの代わりに完全なテーブルスキャンを実行することが可能です。システムはどのように判断するのでしょうか?

判断はシステムの予測に基づいて行われます。つまり、c フィールド インデックスを使用する場合、システムは c フィールド インデックスを使用するためにスキャンする必要がある行数を予測します。多数の行がスキャンされると予測される場合は、インデックスを使用せず、テーブル全体を直接スキャンすることがあります。

そこで疑問になるのが、システムはどのように予測や判断を行うのかということです。たくさん書いて首が少し痛くなってきましたが、ここでシステムがどのように判断するかを説明しましょう。

システムはインデックスの識別力に基づいて判断を行います。インデックスの異なる値が多いほど、同じ値を持つインデックスが少なくなり、インデックスの識別力が高くなります。識別をカーディナリティとも呼びます。つまり、識別が高ければ、カーディナリティも大きくなります。したがって、カーディナリティが大きいということは、条件 100 < c および c < 10000 を満たす行が少なくなることを意味します。

したがって、インデックスのカーディナリティが大きいほど、インデックス クエリでの利点が大きくなります。

それで、問題は、このインデックスのカーディナリティをどうやって知るかということです。

もちろん、コストが高すぎるため、システムはインデックスのカーディナリティを取得するためにすべてのデータを走査することはありません。インデックス システムは、データの一部を走査して、つまりサンプリングすることによって、インデックスのカーディナリティを予測します。

ここまで話してきましたが、ここからが重要なポイントです。それはサンプリングです。エラーが発生する可能性があります。つまり、インデックス c のカーディナリティは実際には非常に大きいのですが、サンプリングすると、残念ながら、このインデックスのカーディナリティは非常に小さくなると予測されます。たとえば、サンプリングしたデータのカーディナリティが非常に小さい場合、インデックスのカーディナリティも非常に小さいと誤って考えてしまう可能性があります。すると、システムは c インデックスを使用せず、ファイル全体を直接スキャンすることになります。

ということで、ここまで述べた後、統計エラーのため、システムはインデックスを使用せず、完全なテーブルスキャンを使用し、これが SQL ステートメントの実行が非常に遅くなる理由でもあるという結論に達しました。

ここで明確にしておきたいのは、インデックスを使用するかどうかはシステムによって決定され、スキャン行数の予測は実際にはその理由の 1 つにすぎないということです。このクエリ ステートメントで一時テーブルを使用する必要があるかどうか、またはソートする必要があるかどうかも、システムの選択に影響します。

ただし、インデックスを強制的にクエリを実行することもできます。たとえば、

c < 100かつc < 100000の場合、t force index(a)から*を選択します。

また、

t からのインデックスを表示します。

インデックスのカーディナリティが実際のものと一致するかどうかを確認するには

テーブルtを分析する。

実際の状況と矛盾する場合は、インデックスのカーディナリティを再計算することができます。このコマンドを使用できます。

統計を再分析します。

インデックスのカーディナリティが誤って予測されるため、クエリ ステートメントに複数のインデックスがある場合、システムが間違ったインデックスを選択する可能性があり、これも SQL 実行が非常に遅くなる原因となる可能性があります。

はい、今のところはこれですべてです。こんなにたくさんのことを考え出してくれたのは素晴らしいと思います。以下に要約します。

### 要約

以上が私のまとめと理解です。最後の部分については、データベースをよく理解していない人が多く、間違ったインデックスを選択するのではないかと心配だったので、詳しく説明しました。ここで、上記をまとめます。

SQL ステートメントの実行が非常に遅い場合、次の 2 つの状況について説明します。

1. ほとんどの場合は正常ですが、時々遅くなることがあります。その理由は次のとおりです。

(1) データベースはダーティページを更新しています。たとえば、REDO ログがいっぱいで、ディスクに同期する必要があります。

（２）実行中に、テーブルロックや行ロックなどのロックが発生します。

2. この SQL 文の実行速度は常に非常に遅くなります。その理由は次のとおりです。

（1）インデックスが使用されていない：たとえば、フィールドにインデックスがないため、フィールドに対する計算や関数操作のためにインデックスを使用できません。

（2）データベースが間違ったインデックスを選択しました。

追加事項がある場合は、コメント欄に追加することもできます。

上記は、SQL ステートメントの実行が遅くなる理由の詳細な説明です。お役に立てれば幸いです。ご質問がある場合は、メッセージを残していただければ、すぐに返信いたします。また、123WORDPRESS.COM ウェブサイトをサポートしてくださっている皆様にも感謝申し上げます。