コード標準では、SQL ステートメントに結合が多すぎないようにする必要があるのはなぜですか?

無料ポイント

インタビュアー：Linuxを使ったことはありますか？

私：はい

インタビュアー：メモリ使用量を確認したいのですが、どのようなコマンドを使用すればよいでしょうか？

私： freeまたはtop

インタビュアー：では、freeコマンドでどのような情報を見ることができるのか教えてください。

私：下の図に示すように、メモリとキャッシュの使用状況を確認できます

合計合計メモリ
使用済みメモリ使用済み
空きメモリ
バフ/キャッシュ使用キャッシュ
利用可能なメモリ

インタビュアー：使用済みのキャッシュ（バフ/キャッシュ）をクリーンアップする方法をご存知ですか？

私：えーっと…分からない

インタビュアー: sync; echo 3 > /proc/sys/vm/drop_caches buff/cacheをクリーンアップできます。このコマンドをオンラインで実行するのは良いことだと思いますか?

私: (無料ポイント、とても嬉しいです) 大きなメリットがあります。キャッシュをクリアすると、使用可能なメモリ領域が増えます。PC の xx ガードの小さなロケットのように、クリックすると大量のメモリが解放されます。

インタビュアー：えーっと…戻って通知を待ちましょう

SQL 結合

インタビュアー：話題を変えて、Join についてのあなたの理解についてお話ししましょう。

私：OK（また間違えたら終わりなのでチャンスを逃さない）

レビュー

SQLのjoin特定の条件に従って指定されたテーブルを結合し、データをクライアントに返すことができます。

join方法は

inner join結合

left join結合

right join

full join

インタビュアー: プロジェクト開発でjoinステートメントを使用する必要がある場合、どのように最適化してパフォーマンスを向上させることができますか?

私：小規模なデータ規模と大規模なデータ規模の 2 つの状況があります。

インタビュアー：それから？

私：

データサイズは小さいので、すべてメモリに格納します。
データサイズが大きい

インデックスを追加することで、 joinステートメントの実行速度を最適化できます。冗長情報を使用することで、 joinの数を減らすことができます。テーブル結合の数を減らすようにしてください。1 つの SQL ステートメント内のテーブル結合の数は 5 を超えてはなりません。

インタビュアー: つまり、 joinステートメントは比較的パフォーマンスを重視すると言えますね?

私：はい

インタビュアー：なぜですか？

バッファ

私：結合ステートメントを実行するときは、比較プロセスが必要です。

インタビュアー：はい

私: 2 つのテーブルを 1 つずつ比較するのは遅いので、2 つのテーブルからデータを 1 つずつ內存塊に読み込むことができます。MySQL の InnoDB エンジンを例にとると、次のステートメントを使用して関連するメモリ領域を確実に見つけることができます: show variables like '%buffer%'

下の図に示すように、 join_buffer_sizeのサイズはjoinステートメントの実行パフォーマンスに影響します。

インタビュアー：それ以外に何かありますか？

大前提

私：どんなプロジェクトでも最終的にはオンラインになり、データの生成は避けられません。そして、データの規模が小さすぎることはあり得ません。

インタビュアー：その通りです。

私: データベース内のデータのほとんどは、最終的には硬盤に保存され、ファイルの形で保管されます。

MySQLのInnoDBエンジンを例に挙げる

InnoDBは頁基本的なIO単位として使用し、各ページのサイズは16KBです。
InnoDBはデータを格納するために各テーブルごとに.ibdファイルを作成します。

確認する

私：これは、テーブルに接続するために必要な数のファイルを読み取る必要があることを意味します。インデックスを使用できますが、それでもハードディスクのヘッドを頻繁に移動する必要があります。

インタビュアー：つまり、頭を頻繁に動かすとパフォーマンスに影響が出るということですね？

私：そうですね、 hbaseやkafkaなど、現在のオープンソースフレームワークは、シーケンシャルな読み書きによってパフォーマンスが大幅に向上したと言いたがりませんか？

インタビュアー: そうです。Linux Linuxこれを最適化していると思いますか? ヒント: もう一度freeコマンドを実行して確認してみてください。

私: なぜキャッシュが 1.2G 以上を占有しているのですか?

インタビュアー：考えたことはありますか？

buff/cacheには何が保存されますか?
availlableメモリがまだ1.1Gなのに、 buff/cache大量のメモリを占有するのはなぜですか?
なぜbuff/cacheによって使用されたメモリを 2 つのコマンドでクリーンアップできるのに、 usedを解放できるのはプロセスを終了した場合だけなのでしょうか?

味わう、じっくり味わう

数分間考えた後

私： buff/cacheが使用しているメモリを気軽に解放するということは、そのメモリが重要ではないということであり、それをクリアしてもシステムの動作には影響しません。

インタビュアー：必ずしもそうとは限りません。

私：そうなんですか？「CSAPP」（コンピュータシステムの詳細な理解）の一文を覚えています

メモリ階層の本質は、各ストレージ層が下位層のデバイスのキャッシュであるということです。

人間の言葉に翻訳すると、 Linuxはメモリをハードディスクのキャッシュとして扱うことになる。

インタビュアー：これで、その簡単な質問に答える方法がわかりましたね？

私：私は…

結合アルゴリズム

インタビュアー: もう一度チャンスをください。Join アルゴリズムを実装するように頼まれたら、どうしますか?

私: インデックスがない場合は、ネストされたループが役立ちます。インデックスがある場合は、それを使用してパフォーマンスを向上させることができます。

インタビュアー: join_bufferには何が格納join_bufferれていると思いますか?

私：スキャンプロセス中に、データベースはテーブルを選択し、返したいデータと他のテーブルと比較したいデータをjoin_bufferに格納します。

インタビュアー：インデックスがある場合はどのように対処しますか？

私：これは比較的簡単です。2つのテーブルのインデックスツリーを読み取って比較するだけです。インデックスがない場合の対処方法を紹介します。

ネストループ結合

ネストされたループは、毎回テーブル内の 1 行のデータのみを読み取ります。つまり、outerTable に 100,000 行のデータがあり、innerTable に 100 行のデータがある場合、 10000000回読み取る必要があります (これら 2 つのテーブルのファイルがオペレーティングシステムによってメモリにキャッシュされていないと仮定すると、これらをコールドデータテーブルと呼びます)。

もちろん、現在このアルゴリズムを使用しているデータベースエンジンはありません（遅すぎるため）。

ブロックネストループ

Block 、つまり、I/Oオーバーヘッドを削減するためにデータがメモリに取り込まれるたびに

MySQL InnoDB は、インデックスが利用できない場合にこのアルゴリズムを使用します。

次の2つのテーブルt_aとt_bを考えてみましょう。

インデックスを使用して結合操作を実行できない場合、InnoDB は自動的にBlock nested loopアルゴリズムを使用します。

要約する

学生時代、データベースの先生はデータベースのパラダイムについてよくテストしてくれました。働き始めて初めて、パフォーマンスがすべての基礎であるべきだということを学びました。冗長性が可能な場合はそれを使用します。それが本当に不可能で、 joinパフォーマンスに本当に影響する場合はjoin 。 join_buffer_sizeを増やすか、SSD を変更してみてください。

これで、コード標準で SQL ステートメントに結合が多すぎないようにする必要がある理由に関する記事は終了です。SQL ステートメントに結合が多すぎないようにする必要がある理由の詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません: