MySQLにおける(JOIN/ORDER BY)文のクエリ処理と最適化方法

MySQLにおける(JOIN/ORDER BY)文のクエリ処理と最適化方法

EXPLAIN ステートメントは、MySQL クエリ ステートメント プロセスと EXPLAIN ステートメントの基本概念および最適化で紹介されており、遅いクエリの例が示されています。

上記のクエリでは、10,000 件を超えるレコードをチェックする必要があり、一時テーブルとファイルソートを使用していることがわかります。このようなクエリは、ユーザー数が急増すると悪夢になります。

このステートメントを最適化する前に、まず SQL クエリの基本的な実行プロセスを理解しましょう。

1. アプリケーションはMySQL APIを介してMySQLサーバーにクエリコマンドを送信し、それが解析されます。

2. 権限を確認し、MySQL オプティマイザーで最適化します。解析と最適化の後、クエリ コマンドは CPU で実行でき、キャッシュできるバイナリ クエリ プランにコンパイルされます。

3. インデックスがある場合は、まずインデックスをスキャンします。データがインデックスでカバーされている場合は、追加の検索は必要ありません。そうでない場合は、インデックスに基づいて対応するレコードを見つけて読み取ります。

4. 関連するクエリがある場合、クエリの順序は、最初のテーブルをスキャンして条件を満たすレコードを見つけ、次に2番目のテーブルをスキャンして、最初のテーブルと2番目のテーブルの関連するキー値に従って条件を満たすレコードを見つけ、この順序でループします。

5. クエリ結果を出力し、バイナリログを記録する

当然のことながら、適切なインデックスを使用すると、検索が大幅に簡素化され、高速化されます。上記のクエリ ステートメントを見てみましょう。条件付きクエリに加えて、関連クエリと ORDER BY (並べ替え操作) もあります。

それでは、結合と ORDER BY がどのように機能するかを詳しく見てみましょう。MySQL には、結合とデータの並べ替えを処理する 3 つの方法があります。

最初の方法はインデックスに基づき、2 番目は最初の非定数テーブルをファイルソート (クイックソート) し、3 番目は結合クエリの結果を一時テーブルに入れてからファイルソートを実行する方法です。

注 1: 定数テーブルの詳細については、『MySQL 開発者マニュアル: Consts and Constant Tables』を参照してください。
注 2: filesort とは何ですか? これは文字通りのファイル ソートではありません。filesort には 2 つのモードがあります。
1. モード 1: ソートされた要素が出力されるデータをカバーします。ソート結果は順序付けられたシーケンス要素の文字列であり、追加のレコードの読み取りは必要ありません。
2. モード 2: ソート結果はキーと値のペアのシーケンス <sort_key, row_id> であり、レコードはこれらの row_id を通じて読み取られます (ランダム読み取り、非効率的)。
注3: 一時テーブルの詳細については、MySQL開発マニュアル「MySQLが内部一時テーブルを使用する方法」を参照してください。

最初の方法は、ORDER BY が依存する列のインデックスが最初の非定数テーブルに存在する場合に使用されます。この場合、すでに順序付けされたインデックスを直接使用して、関連付けられたテーブルのデータを見つけることができます。この方法は、追加の並べ替えアクションが不要なため、最高のパフォーマンスを発揮します。

2 番目の方法は、ORDER BY が依存するすべての列が最初のクエリ テーブルに属し、インデックスがない場合に使用されます。この場合、最初に最初のテーブルのレコードに対してファイルソートを実行し (モードはモード 1 またはモード 2 のいずれか)、順序付けされた行インデックスを取得してから、関連するクエリを実行します。ファイルソートの結果は、システム変数 sort_buffer_size (通常は約 2M) に応じて、メモリ内またはハード ディスク上に存在する場合があります。

3 番目の方法は、ORDER BY の要素が最初のテーブルに属していない場合に使用されます。関連付けられているクエリの結果を一時テーブルに格納し、一時テーブルでファイルソートを実行する必要があります。

3 番目の方法の一時テーブルは、メモリ内テーブルまたはハードディスク上にある場合があります。通常、ハードディスク (ディスク上のテーブル) は次の 2 つの状況で使用されます。

(1)BLOBとTEXTデータ型の使用

(2)メモリテーブルの占有量がシステム変数tmp_table_size/max_heap_table_sizeの制限(通常16M程度)を超えているため、ハードディスク上にしか配置できない。

上記のクエリ実行プロセスと方法から、filesortの使用とtemporaryの使用がクエリのパフォーマンスに重大な影響を与える理由を明確に理解できるはずです。データ型やフィールド設計に問題がある場合、

クエリ対象のテーブルと結果に大きなデータ フィールドがあり、適切なインデックスが利用できない場合は、大量の IO 操作が生成される可能性があります。これがクエリ パフォーマンスの低下の根本的な原因です。

記事の冒頭のクエリ例に戻ると、明らかに最も効率の悪い 3 番目の方法が使用されています。実行して試す必要がある最適化方法は次のとおりです。

1. users.fl_noのインデックスを追加し、selectとwhereで使用されるフィールドのインデックスを作成します。

2. users.fl_noをuser_profileテーブルに転送するか、冗長フィールドとして追加します。

3. TEXT タイプのフィールドを削除します。TEXT は、中国語の場合は VARCHAR (65535) または VARCHAR (20000) に置き換えることができます。

4. filesort の使用を排除できない場合は、sort_buffer_size を増やして IO 操作の負荷を軽減します。

5. 最初のテーブルでカバーされているインデックスを使用してソートを試みます。それでもうまくいかない場合は、ソート ロジックを MySQL から PHP/Java プログラムに移動して実行します。

最適化方法 1、2、3 を実装した後、EXPLAIN の結果は次のようになります。

注: 簡単な PHP アプリケーションを作成し、Siege を使用してテストすると、クエリの効率が 3 倍以上向上します。

以上がこの記事の全内容です。皆様の勉強のお役に立てれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。

以下もご興味があるかもしれません:
  • MySQL の効率的なクエリの左結合とグループ化 (プラス インデックス)
  • MySQLがサブクエリと結合の使用を推奨しない理由
  • MySQL 結合クエリ構文と例
  • MySQL 結合テーブルクエリの基本操作 左結合のよくある落とし穴
  • MySQL におけるさまざまな一般的な結合テーブルクエリの例の概要
  • MySQL結合クエリの原理

<<:  Linux で Multitail コマンドを使用するチュートリアル

>>:  JavaScript 文字列操作の 4 つの実用的なヒント

推薦する

親ページの更新を制御するために HTML で iframe を実装するためのアイデアとコード

1. 応用シナリオ親ページ a.jspサブページ b.jsp (ページ a に埋め込まれた ifra...

MySQL における TIMESTAMPDIFF ケースの詳細な説明

1.構文TIMESTAMPDIFF(unit,begin,end); 単位に従って時間差を返します。...

Webフロントエンドベクターアイコンの使い方

序文フロントエンドページを書くとき、小さなアイコンなどの画像を使うことが多いです。画像を使うとコード...

Link と @import の違いを詳しく見る

ページで CSS を使用する主な方法は、スタイル属性値をインラインで追加する方法、ページ ヘッダーで...

Vue+Openlayerはグラフィックスのドラッグと回転変形効果を実現します

目次序文関連資料成果を達成する実装手順序文Openlayer には独自の拡張プラグイン ol-ext...

CSS フロントエンドの知識ポイントのまとめ(必読)

1. CSS の概念: (カスケーディング スタイル シート)利点: 1. コンテンツとプレゼンテ...

mysqlを使用して、URLから返されたhttp GETリクエストデータを記録します。

ビジネスシナリオの要件と実装ロジックの分析ビジネスでは、HTTP GET を使用してデータを要求する...

Linux システムでの nginx サーバーのインストールと負荷分散構成の詳細な説明

nginx (エンジン x) は、高性能な HTTP およびリバース プロキシ サーバー、メール プ...

CSS カウンターとコンテンツの概要

コンテンツ プロパティは CSS 2.1 で導入され、:before および :after 疑似要素...

Nginx でバージョン番号を隠す方法

Nginx はバージョン番号を非表示にする実稼働環境では、セキュリティ上の脆弱性の漏洩を避けるために...

JS がビデオ弾幕効果を実現

これを実現するには、ES6 モジュール開発とオブザーバー モードを使用します。オブザーバー パターン...

MySQL スケジュールバックアップタスクの簡単な分析

導入実稼働環境では、データの損失を回避するために、通常、データベースは定期的にバックアップされます。...

nginx ベースのブラウザネゴシエーションキャッシュプロセスの詳細な説明

この記事は主に、nginx に基づいてブラウザネゴシエーションキャッシュを設定する詳細なプロセスを紹...

Reactにおけるキーの役割の詳細な説明

目次質問: ボタンをクリックすると、スパンの色が赤に変わりますか?上記の問題を分析します。 2番目の...

フォームデータを取得するための Node.js メソッドの 3 つの例

序文Nodejs はサーバーサイド言語です。開発中、登録やログインなどでは、判断のためにフォームを通...