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 つの実用的なヒント

推薦する

構造とプレゼンテーションの分離を理解するためのWeb標準の学習

Web 標準について議論するときに必ず話題になるのは、構造とプレゼンテーションを分離することの重要性...

MacBook 向け Python 3.7 インストール チュートリアル

MacBookにpython3.7.0をインストールする詳細な手順は、参考までに記録されています。具...

CSS3で実装されたサムネイルホバー効果

成果を達成する実装コードhtml <ヘッダー> <h1><em>...

制限およびオフセット ページング シナリオを使用すると速度が遅くなるのはなぜですか?

質問から始めましょう5 年前、私が Tencent にいたとき、ページング シナリオでは MySQL...

JavaScript における URL オブジェクトの素晴らしい使い方

目次序文解析パラメータURLパラメータを変更する要約する序文URLオブジェクトはページ側ではあまり使...

HTM と HTML の違いは何ですか? HTM と HTML の違いは何ですか?

Web デザインを学習する過程で、html と htm の関係など、遭遇した多くの問題について深く...

Ubuntu 16.04 mysql5.7.17 リモートポート 3306 を開く

MySQLへのリモートアクセスを有効にするデフォルトでは、MySQL ユーザーにはリモート アクセス...

Ubuntu 18.04にmysql5.7をインストールする

Ubuntu 18.04では参考までにmysql 5.7をインストールします。具体的な内容は以下のと...

Linuxのdateコマンドの使用

1. コマンドの紹介date コマンドは、現在の時刻または指定された時刻を指定された形式で表示するた...

Vue プロジェクトで axios リクエストを使用する方法

目次1. インストール2. カプセル化に問題はない3. ファイルを作成する4. アドレス設定をリクエ...

MySQLが中国語の文字を挿入する問題を永久に解決するコツを教えます

目次序文最初のステップ:ステップ2: このmy.iniを変更する要約する序文問題の説明:不正な文字列...

Linux で MySQL 5.6 X64 バージョンをインストールする詳細な手順

環境: 1. CentOS6.5 X64 2.mysql-5.6.34-linux-glibc2.5...

Vue Routerはバックグラウンドデータに応じて異なるコンポーネントをロードします

目次実際のプロジェクトで遭遇する要件実装が間違っているところもある私は個人的に、実装するより良い方法...

JS配列インデックス検出におけるデータ型の問題の詳細な説明

WeChat アプレット プロジェクトを書いていたとき、その中に「都市選択」機能がありました。作者は...

arcgis.js は、マップ本体の表示範囲を制御し、領域を超えた場合に自動的にバウンスするようにします (実装のアイデア)

目次背景効果アイデア背景少し前に、会社のプロジェクトで問題が発生しました。地図のベースマップ領域の範...