序文 MySQL は、強力なクエリ機能、高いデータ一貫性、高いデータ セキュリティ、およびセカンダリ インデックスのサポートを備えたリレーショナル データベースです。ただし、そのパフォーマンスは非リレーショナル データベースに比べてわずかに劣り、特にデータが 100 万を超えると、クエリが遅くなりやすくなります。このとき、クエリが遅い理由を分析する必要があります。一般的に、これはプログラマーの SQL の書き方が下手、キー インデックスが不足、またはインデックスが無効であることが原因で発生します。 このとき、MySQL が提供する EXPLAIN コマンドが特に重要です。このコマンドは、SELECT 文を分析し、SELECT 実行に関する詳細な情報を出力して、開発者がターゲットを絞って最適化できるようにします。 クエリ ステートメントの前に Explain を追加するだけです。 EXPLAIN SELECT * FROM customer WHERE id < 100; 準備する まず、2 つのテスト テーブルとデータを作成する必要があります。 テーブル「顧客」を作成します( `id` BIGINT(20) 符号なし NOT NULL AUTO_INCREMENT, `name` VARCHAR(50) NOT NULL デフォルト '' `age` INT(11) 符号なし DEFAULT NULL, 主キー (`id`)、 キー `name_index` (`name`) ) エンジン = InnoDB デフォルト文字セット = utf8mb4 顧客 (名前、年齢) に VALUES ('a'、1) を挿入します。 顧客(名前、年齢)にVALUES('b'、2)を挿入します。 顧客(名前、年齢)にVALUES('c'、3)を挿入します。 顧客(名前、年齢)にVALUES('d'、4)を挿入します。 顧客(名前、年齢)にVALUES('e'、5)を挿入します。 顧客(名前、年齢)にVALUES('f'、6)を挿入します。 顧客(名前、年齢)にVALUES('g'、7)を挿入します。 顧客 (名前、年齢) に VALUES ('h'、8) を挿入します。 顧客 (名前、年齢) に VALUES ('i'、9) を挿入します。 テーブル「orders」を作成します( `id` BIGINT(20) 符号なし NOT NULL AUTO_INCREMENT, `user_id` BIGINT(20) unsigned NOT NULL DEFAULT 0, `product_name` VARCHAR(50) NOT NULL DEFAULT '', `productor` VARCHAR(30) NOT NULL デフォルト '' 主キー (`id`)、 キー `user_product_detail_index` (`user_id`、`product_name`、`productor`) ) エンジン = InnoDB デフォルト文字セット = utf8mb4 注文に (user_id、product_name、productor) VALUES (1、'p1'、'WHH') を挿入します。 注文に (user_id、product_name、productor) VALUES (1、'p2'、'WL') を挿入します。 注文に (user_id、product_name、productor) VALUES (1、'p1'、'DX') を挿入します。 注文に (user_id、product_name、productor) VALUES (2、'p1'、'WHH') を挿入します。 注文に (user_id、product_name、productor) VALUES (2、'p5'、'WL') を挿入します。 注文に (user_id、product_name、productor) VALUES (3、'p3'、'MA') を挿入します。 注文に (user_id、product_name、productor) VALUES (4、'p1'、'WHH') を挿入します。 注文に (user_id、product_name、productor) VALUES (6、'p1'、'WHH') を挿入します。 注文に (user_id、product_name、productor) VALUES (9、'p8'、'TE') を挿入します。 EXPLAIN出力フォーマット EXPLAIN コマンドの出力は、おおよそ次のようになります。 mysql> id = 1\G の顧客から * を選択します。 ************************** 1. 行 **************************** id: 1 選択タイプ: シンプル テーブル: 顧客 パーティション: NULL 型: 定数 可能なキー: プライマリ キー: PRIMARY キーの長さ: 8 参照: 定数 行数: 1 フィルター: 100.00 追加: NULL セットに 1 行、警告 1 件 (0.00 秒) 各列の意味は次のとおりです。
次に、より重要なフィールドをいくつか見てみましょう。 選択タイプ
最も一般的なクエリ タイプは SIMPLE です。たとえば、クエリにサブクエリや UNION クエリがない場合、通常は SIMPLE タイプになります。次に例を示します。 mysql> explain select * from customer where id = 2\G ************************** 1. 行 **************************** id: 1 選択タイプ: シンプル テーブル: 顧客 パーティション: NULL 型: 定数 可能なキー: プライマリ キー: PRIMARY キーの長さ: 8 参照: 定数 行数: 1 フィルター: 100.00 追加: NULL セットに 1 行、警告 1 件 (0.00 秒) UNION クエリを使用する場合、EXPLAIN 出力は次のようになります。 mysql> EXPLAIN (SELECT * FROM customer WHERE id IN (1, 2, 3)) -> ユニオン -> (SELECT * FROM customer WHERE id IN (3, 4, 5)); +----+--------------+-------------+-----------+--------+---------------+---------+-------+------+------+-----------------+ | id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 | +----+--------------+-------------+-----------+--------+---------------+---------+-------+------+------+-----------------+ | 1 | PRIMARY | 顧客 | NULL | 範囲 | PRIMARY | PRIMARY | 8 | NULL | 3 | 100.00 | where の使用 | | 2 | UNION | customer | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 3 | 100.00 | where の使用 | | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | 一時を使用 | +----+--------------+-------------+-----------+--------+---------------+---------+-------+------+------+-----------------+ セットに 3 行、警告 1 件 (0.00 秒) テーブル クエリに関係するテーブルまたは派生テーブルを示します タイプ タイプ フィールドは、クエリが効率的かどうかを判断するための重要な基準を提供するため重要です。タイプ フィールドを通じて、クエリがフル テーブル スキャンであるかインデックス スキャンであるかを判断できます。 タイプ 一般的なタイプ type の一般的な値は次のとおりです。
mysql> explain select * from customer where id = 2\G ************************** 1. 行 **************************** id: 1 選択タイプ: シンプル テーブル: 顧客 パーティション: NULL 型: 定数 可能なキー: プライマリ キー: PRIMARY キーの長さ: 8 参照: 定数 行数: 1 フィルター: 100.00 追加: NULL セットに 1 行、警告 1 件 (0.00 秒) eq_ref: このタイプは通常、複数テーブルの結合クエリで使用され、前のテーブルの各結果に対して、次のテーブルの結果の 1 行のみが一致することを示します。クエリの比較演算は通常 = であり、クエリ効率が高くなります。例: mysql> EXPLAIN SELECT * FROM customer, order_info WHERE customer.id = order_info.user_id\G ************************** 1. 行 **************************** id: 1 選択タイプ: シンプル テーブル: order_info パーティション: NULL タイプ: インデックス 可能なキー: ユーザー製品詳細インデックス キー: user_product_detail_index キーの長さ: 314 参照: NULL 行数: 9 フィルター: 100.00 追加: where の使用; index の使用 ************************** 2. 行 **************************** id: 1 選択タイプ: シンプル テーブル: 顧客 パーティション: NULL タイプ: eq_ref 可能なキー: プライマリ キー: PRIMARY キーの長さ: 8 参照: test.order_info.user_id 行数: 1 フィルター: 100.00 追加: NULL セットに 2 行、警告 1 件 (0.00 秒) ref: このタイプは通常、複数テーブルの結合クエリ、一意でないキーまたは主キーでないインデックス、または左端のプレフィックス ルール インデックスを使用するクエリで使用されます。 たとえば、次の例では、ref 型クエリが使用されています。 mysql> EXPLAIN SELECT * FROM customer, order_info WHERE customer.id = order_info.user_id AND order_info.user_id = 5\G ************************** 1. 行 **************************** id: 1 選択タイプ: シンプル テーブル: 顧客 パーティション: NULL 型: 定数 可能なキー: プライマリ キー: PRIMARY キーの長さ: 8 参照: 定数 行数: 1 フィルター: 100.00 追加: NULL ************************** 2. 行 **************************** id: 1 選択タイプ: シンプル テーブル: order_info パーティション: NULL タイプ: ref 可能なキー: ユーザー製品詳細インデックス キー: user_product_detail_index キーの長さ: 9 参照: 定数 行数: 1 フィルター: 100.00 追加: インデックスの使用 セットに 2 行、警告 1 件 (0.01 秒) range: インデックス範囲クエリを使用して、インデックス フィールド範囲を通じてテーブル内の部分的なデータ レコードを取得することを示します。このタイプは通常、=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、IN() 操作で使用されます。タイプが範囲の場合、EXPLAIN によって出力される ref フィールドは NULL になり、key_len フィールドはこのクエリで使用される最長のインデックスになります。 たとえば、次の例は範囲クエリです。 mysql> EXPLAIN SELECT * FROM customer WHERE id BETWEEN 2 AND 8 \G ************************** 1. 行 **************************** id: 1 選択タイプ: シンプル テーブル: 顧客 パーティション: NULL タイプ: 範囲 可能なキー: プライマリ キー: PRIMARY キーの長さ: 8 参照: NULL 行数: 7 フィルター: 100.00 追加: where の使用 セットに 1 行、警告 1 件 (0.00 秒) index: 完全なインデックス スキャンを示します。これは ALL タイプに似ていますが、ALL タイプはテーブル全体をスキャンするのに対し、index タイプはデータをスキャンせずにすべてのインデックスのみをスキャンする点が異なります。 インデックス タイプは通常、クエリ対象のデータが、データをスキャンせずにインデックス ツリーで直接取得できる場合に表示されます。この場合、[追加] フィールドに [インデックスを使用] と表示されます。 例えば: mysql> EXPLAIN SELECT name FROM customer \G ************************** 1. 行 **************************** id: 1 選択タイプ: シンプル テーブル: 顧客 パーティション: NULL タイプ: インデックス 可能なキー: NULL キー: name_index キーの長さ: 152 参照: NULL 行数: 10 フィルター: 100.00 追加: インデックスの使用 セットに 1 行、警告 1 件 (0.00 秒) 上記の例では、クエリしている名前フィールドがインデックスであるため、テーブル内のデータをクエリしなくても、クエリ要件を満たすデータをインデックスから直接取得できます。したがって、この場合、type の値は index で、Extra の値は Using index です。
以下は、フル テーブル スキャンの例です。フル テーブル スキャンでは、possible_keys フィールドと key フィールドの両方が NULL であり、インデックスが使用されていないこと、行が非常に大きいこと、そのため全体的なクエリ効率が非常に低いことがわかります。 mysql> EXPLAIN SELECT age FROM customer WHERE age = 20 \G**************************** 1. 行 **************************** id: 1 選択タイプ: シンプル テーブル: 顧客 パーティション: NULL タイプ: すべて 可能なキー: NULL キー: NULL キー長さ: NULL 参照: NULL 行数: 10 フィルター: 10.00 追加: where の使用 セットに 1 行、警告 1 件 (0.00 秒) タイプ別の性能比較 一般的に言えば、さまざまなタイプのパフォーマンス関係は次のとおりです。
ALL タイプは、同じクエリ条件でテーブル全体をスキャンするため、最も低速です。 インデックスタイプのクエリはテーブル全体をスキャンしませんが、すべてのインデックスをスキャンするため、ALLタイプよりもわずかに高速です。次のタイプはすべてインデックスを使用してデータをクエリするため、データの一部または大部分をフィルタリングできるため、クエリ効率が比較的高くなります。 プログラマーの場合、クエリが少なくとも範囲レベル、できれば参照レベルに到達することを保証できれば、優秀で責任あるプログラマーであると見なされます。
可能なキー possible_keys は、MySQL がクエリ時に使用できるインデックスを示します。possible_keys にインデックスがいくつか表示されていても、このインデックスが実際に MySQL によって使用されるわけではないことに注意してください。クエリ時に MySQL が使用する特定のインデックスは、キー フィールドによって決まります。 鍵 このフィールドは、MySQL が現在のクエリで実際に使用するインデックスです。 キーの長さ クエリ オプティマイザーによって使用されるバイト数を示します。このフィールドは、複合インデックスが完全に使用されているか、左端のフィールドのみが使用されているかを評価できます。 key_len の計算規則は次のとおりです。
2つの簡単な例を見てみましょう。 mysql> EXPLAIN SELECT * FROM order_info WHERE user_id < 3 AND product_name = 'p1' AND productor = 'WHH' \G ************************** 1. 行 **************************** id: 1 選択タイプ: シンプル テーブル: order_info パーティション: NULL タイプ: 範囲 可能なキー: ユーザー製品詳細インデックス キー: user_product_detail_index キーの長さ: 9 参照: NULL 行数: 5 フィルタリング: 11.11 追加: where の使用; index の使用 セットに 1 行、警告 1 件 (0.00 秒) 上記の例では、テーブル order_info から指定されたコンテンツをクエリします。テーブル作成ステートメントから、テーブル order_info に結合インデックスがあることがわかります。 キー `user_product_detail_index` (`user_id`、`product_name`、`productor`) ただし、クエリ WHERE user_id < 3 AND product_name = 'p1' AND productor = 'WHH' では、user_id 範囲クエリが最初に実行されます。左端のプレフィックス マッチングの原則によると、範囲クエリに遭遇すると、インデックス マッチングは停止します。したがって、実際に使用するインデックス フィールドは user_id のみです。したがって、EXPLAIN では、表示される key_len は 9 です。user_id フィールドは BIGINT であるため、8 バイトを占め、NULL 属性は 1 バイトを占めるため、合計は 9 バイトになります。user_id フィールドを BIGINT(20) NOT NULL DEFAULT '0' に変更すると、key_length は 8 になります。 左端のプレフィックス一致の原則により、クエリでは結合インデックスの user_id フィールドのみが使用されるため、効率は高くありません。 次の例を見てみましょう。 mysql> EXPLAIN SELECT * FROM order_info WHERE user_id = 1 AND product_name = 'p1' \G; ************************** 1. 行 **************************** id: 1 選択タイプ: シンプル テーブル: order_info パーティション: NULL タイプ: ref 可能なキー: ユーザー製品詳細インデックス キー: user_product_detail_index キーの長さ: 161 参照: const、const 行数: 2 フィルター: 100.00 追加: インデックスの使用 セットに 1 行、警告 1 件 (0.00 秒) このクエリでは、範囲クエリを使用しておらず、key_len の値は 161 です。なぜでしょうか? クエリ条件 WHERE user_id = 1 AND product_name = 'p1' では、結合インデックスの最初の 2 つのフィールドのみが使用されるため、keyLen(user_id) + keyLen(product_name) = 9 + 50 * 3 + 2 = 161 となります。 行 行も重要なフィールドです。統計に基づいて、MySQL クエリ オプティマイザーは、結果セットを見つけるために SQL がスキャンする必要がある行数を推定します。 この値は SQL の効率を直接示します。原則として、行数が少ないほど効率が高くなります。 余分な EXplain の多くの追加情報は Extra フィールドに表示されます。一般的な情報は次のとおりです。
Extra に Using filesort が含まれている場合、MySQL では追加のソート操作が必要であり、インデックス順序によるソート効果を実現できないことを意味します。このようなクエリは CPU リソースを大量に消費するため、一般的に、Using filesort を最適化して削除することをお勧めします。 たとえば、次の例をご覧ください。 mysql> EXPLAIN SELECT * FROM order_info WHERE user_id = 1 AND product_name = 'p1' \G; ************************** 1. 行 **************************** id: 1 選択タイプ: シンプル テーブル: order_info パーティション: NULL タイプ: ref 可能なキー: ユーザー製品詳細インデックス キー: user_product_detail_index キーの長さ: 161 参照: const、const 行数: 2 フィルター: 100.00 追加: インデックスの使用 セットに 1 行、警告 1 件 (0.00 秒) 私たちのインデックスは キー `user_product_detail_index` (`user_id`、`product_name`、`productor`) ただし、上記のクエリは product_name でソートされているため、インデックスを最適化に使用することはできず、filesort を使用することになります。 ソート基準を ORDER BY user_id, product_name に変更すると、Using filesort は表示されなくなります。例: mysql> EXPLAIN SELECT * FROM order_info ORDER BY user_id, product_name \G ************************** 1. 行 **************************** id: 1 選択タイプ: シンプル テーブル: order_info パーティション: NULL タイプ: インデックス 可能なキー: NULL キー: user_product_detail_index キーの長さ: 253 参照: NULL 行数: 9 フィルター: 100.00 追加: インデックスの使用 セットに 1 行、警告 1 件 (0.00 秒)
「カバーリングインデックススキャン」とは、クエリがテーブルデータファイルをスキャンせずにインデックスツリー内の必要なデータを見つけることができることを意味し、多くの場合、良好なパフォーマンスを示します。
クエリは一時テーブルを使用します。これは通常、並べ替え、グループ化、および複数テーブルの結合の状況で発生します。クエリの効率は高くありません。最適化することをお勧めします。 要約する 以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。 以下もご興味があるかもしれません:
|
この記事では、SMS送信のカウントダウンを実装するためのJavaScriptの具体的なコードを参考ま...
IPツールを取得 lombok.extern.slf4j.Slf4j をインポートします。 org....
この記事では、フォームの隠しテキストを表示するためのJavaScriptの具体的なコードを参考までに...
この記事の例では、ログイン機能を実現するためのvue+springbootの具体的なコードを参考まで...
この記事の例では、参考までに簡単な虫眼鏡効果を実現するためのjsの具体的なコードを共有しています。具...
参考までに、win7システム上のVMware仮想マシンにlinux7.2インターネットアクセス構成を...
行内では、明るい境界線の色を個別に定義できます。基本的な構文<TR ボーダーカラーライト=co...
目次序文最適化派生的な質問: beforeDestroy はトリガーされませんか?序文タイマーをクリ...
目次クロスドメインの理由JSONP Nginxソリューションバックエンドソリューションクロスドメイン...
以下の情報はインターネットから収集したものです1. アンカーは、Web ページ作成におけるハイパーリ...
オリジナルリンクhttps://github.com/XboxYan/no…ボタンは、おそらく We...
ソケットオプション機能機能: ソケットファイル記述子の属性の読み取りと設定に使用されるメソッド #i...
ユニアプリアプレットはWeChatでも同様のドロップダウン問題を抱えることになる解決策は、app.v...
HTML img タグ: Web ページに導入される画像を定義します。興味深い usemap 属性も...
HTMLを学ぶとき、画像タグ<img>は画像を導入します <img src=&qu...