概要 データベースでは、ツリー ディレクトリと同様に、インデックスを使用してデータ検索を高速化します。SQL クエリ操作では、インデックスを使用して、要件を満たさないデータをすばやく除外し、要件を満たすデータを検索できるため、必要なデータを取得するためにテーブル全体をスキャンする必要がなくなります。 InnoDB ストレージ エンジンでは、インデックスは主に B+ ツリーに基づいています。インデックス キーワードは非リーフ ノードに格納され、データ レコードまたは主キー インデックス (またはクラスター化インデックス) 内の主キー値はリーフ ノードに格納されます。すべてのデータ レコードは同じレイヤーにあり、リーフ ノード、つまりデータ レコードはポインターによって直接接続されて双方向リンク リストを形成し、すべてのデータ レコードまたは一定範囲のデータ レコードを簡単にトラバースできます。 B ツリー、B+ ツリー B ツリーと B+ ツリーはどちらも多方向バランス検索ツリーであり、各ノードにより多くのキーワードを格納し、回転と分割操作を通じてツリーのバランスを維持することでツリーの高さを削減し、データ取得のためのディスク アクセスの量を削減します。 B+ ツリーと B ツリーの主な違いは、B+ のリーフ ノードがポインター、具体的には二重リンク リストを介して前後に接続されているため、範囲検索を実行するのに非常に適していることです。詳細については以下を参照してください。 データ構造 - ツリー (III): 多方向検索ツリー B ツリー、B+ ツリー InnoDB ストレージ エンジンのクラスター化インデックスと非クラスター化インデックスは、B+ ツリーに基づいて実装されています。 InnoDB ストレージ エンジンは、主キー インデックスをテーブルのクラスター化インデックスとして使用します。クラスター化インデックスの特徴は、非リーフ ノードが主キーを検索キーワードとして保存し、リーフ ノードが実際のデータ レコード自体 (データ ページとも呼ばれます) を保存することです。データ レコードは、キーワードの順序で左から右に保存されます。したがって、クラスター化インデックスは、実際にはデータを保存する方法です。したがって、各テーブルには 1 つのクラスター化インデックスしか設定できません。InnoDB ストレージ エンジンのデータ テーブルは、インデックス構成テーブルとも呼ばれます。構造は次のとおりです: (画像は「MySQL Technology Insider: Innodb Storage Engine」より) クエリでは、主キーでデータを検索する場合、つまり、Explain Analysis SQL キーが PRIMARY を示している場合、リーフ ノードがデータ レコード自体を格納するため、非クラスター化インデックスのようにデータ レコードを取得するための追加のテーブル クエリ (主キー インデックス内) を必要とせず、直接返すことができるため、検索効率が最も高くなります。 次に、ORDER BYソート操作の場合、ASCかDESCかに関係なく、ORDER BY列が主キーであれば、主キーインデックスに対応するB+ツリーが順序付けられるため、ストレージエンジンから返されるデータはすでに主キーに従って順序付けられており、MySQLサーバーレベルでソートする必要がなく、パフォーマンスが向上します。explainを通じてSQLを分析し、extraがfilesortを使用していると表示された場合、それはMySQLサーバーレベルでソートが必要であることを意味します。このとき、一時テーブルまたは外部ファイルソートを使用する必要があるかもしれません。この場合、一般的には最適化する方法を見つける必要があります。 主キーに基づく範囲検索では、クラスター化インデックスのリーフノードが主キーの順序に従って双方向リンクリストで接続されるため、特定の範囲のデータレコードを素早く見つけることができます。 補助索引 補助インデックスはセカンダリ インデックスとも呼ばれ、非クラスター化インデックスであり、通常は特定のクエリの効率を向上させるために設計されています。つまり、インデックス列を使用してクエリを実行する場合、補助インデックスは完全なテーブル スキャンを回避するために使用されます。補助インデックスはクラスター化インデックスではないため、各テーブルには次の構造を持つ複数の補助インデックスを含めることができます。 補助インデックスの非リーフノードにはインデックス列のキーワードが格納され、リーフノードには対応するクラスター化インデックス(または主キーインデックス)の主キー値が格納されます。つまり、補助インデックスを通じて必要なデータを検索した後、必要な列がインデックスでカバーできない場合、つまり、クエリに必要なすべてのデータ列を補助インデックス列を通じて取得するには、対応するクラスター化インデックスの主キー値を通じてクラスター化インデックス内の主キーを検索し、次に主キー値を通じてクラスター化インデックス内の対応するリーフ ページを見つけて、対応するデータ レコードを取得する必要があります。したがって、全体のプロセスには、最初に補助インデックスを検索し、次にクラスター化インデックス (つまり、主キー インデックス) を検索するという 2 つのプロセスが含まれます (バック テーブル クエリ)。 例えば:
上記のプロセスには合計 6 つのディスク IO が必要です。したがって、クエリする必要があるデータの行数が多い場合、必要なディスク IO は指数関数的に増加し、クエリのパフォーマンスが低下します。したがって、フィルタリング度の高い列、つまり重複データの少ない列に補助インデックスを作成する必要があります。 カーディナリティ: インデックス列のデータの重複 上記の分析から、補助インデックスを介してクエリを実行する場合、テーブルをクエリして戻す必要があり、クエリするデータの行数が多いと、データを取得するために大量のディスク IO が必要になることがわかります。そのため、このインデックスはクエリのパフォーマンスを向上させるだけでなく、クエリのパフォーマンスを低下させます。さらに、MySQL オプティマイザが多数のデータ行を返す必要がある場合、インデックスの使用をあきらめて、直接フル テーブル スキャンを実行します。したがって、補助インデックスによって選択される列は重複の少ない列である必要があります。つまり、一般的なクエリの後に返される必要があるのは 1 行または 2 行のデータのみです。この列に重複する値が多すぎる場合は、この列にセカンダリ インデックスを作成することをあきらめることを検討する必要があります。 具体的には、SHOW INDEX FROM を使用してカーディナリティ値を決定できます。 mysql> store_order からインデックスを表示します。 +---------------+-------------+-------------+--------------+--------------+-------------+-----------+---------+----------+----------+----------+---------------+ | テーブル | 非一意 | キー名 | インデックス内のシーケンス | 列名 | 照合 | カーディナリティ | サブパート | パック | Null | インデックス タイプ | コメント | インデックス コメント | +---------------+-------------+-------------+--------------+--------------+-------------+-----------+---------+----------+----------+----------+---------------+ | store_order | 0 | PRIMARY | 1 | store_id | A | 201 | NULL | NULL | | BTREE | | | | store_order | 1 | idx_expire | 1 | expire_date | A | 68 | NULL | NULL | YES | BTREE | | | | store_order | 1 | idx_ul | 1 | ul | A | 22 | NULL | NULL | はい | BTREE | | | +---------------+-------------+-------------+--------------+--------------+-------------+-----------+---------+----------+----------+----------+---------------+ セット内の 3 行 (0.01 秒) カーディナリティは、インデックス列内の一意の値の推定数を示します。データ行数に近い場合、列内の重複値が少なく、列のフィルタリング性能が優れていることを意味します。差が大きすぎる場合、つまり、カーディナリティ/データ行の合計数の値が小さすぎる場合、たとえば、性別列に「男性」と「女性」の 2 つの値しか含まれていない場合は、列に多数の重複値があることを意味し、インデックスを削除するかどうかを検討する必要があります。 カバーインデックス
ジョイントインデックスは、一番左の前のスタンプと一致します
複合インデックス最適化ソート順 さらに、MySQL サーバー レベルでのソートを減らすために、ジョイント インデックスの使用を検討することもできます。たとえば、ユーザー注文テーブルには、ジョイント インデックス (user_id、buy_date) と単一列インデックス (user_id) が含まれています。(これはジョイント インデックスのデモンストレーションのみを目的としていることに注意してください。実際のプロジェクトでは、ジョイント インデックスのみが必要です。前述のように、(a,b) は 2 つのインデックス a と (a,b) に相当します。) キー `idx_user_id` (`user_id`)、 キー `idx_user_id_buy_date` (`user_id`,`buy_date`) ユーザーの注文をクエリするだけの場合、InnoDB は次のように user_id インデックスを使用します。 mysql> explain select user_id, order_id from t_order where user_id = 1; +----+-------------+----------+-----------+---------+----------------------------------+------------+---------+-----------+------------+-------------+ | id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 | +----+-------------+----------+-----------+---------+----------------------------------+------------+---------+-----------+------------+-------------+ | 1 | SIMPLE | t_order | NULL | ref | idx_user_id、idx_user_id_buy_date | idx_user_id | 4 | const | 4 | 100.00 | インデックスを使用 | +----+-------------+----------+-----------+---------+----------------------------------+------------+---------+-----------+------------+-------------+ セットに 1 行、警告 1 件 (0.00 秒) ただし、購入日 buy_date に基づいてソートし、過去 3 日間のユーザーの購入記録を取得する必要がある場合は、単一列インデックス user_id と結合インデックス (user_id、buy_date) の両方を使用できます。buy_date はすでに結合インデックスでソートされているため、InnoDB は結合インデックスの使用を選択します。そのため、MySQL サーバー レベルで再度ソートする必要がなく、次のようにパフォーマンスが向上します。 mysql> explain select user_id, order_id from t_order where user_id = 1 order by buy_date limit 3; +----+-------------+----------+-----------+--------+----------------------------------+------------------------+--------+--------+----------+---------------------------+ | id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 | +----+-------------+----------+-----------+--------+----------------------------------+------------------------+--------+--------+----------+---------------------------+ | 1 | SIMPLE | t_order | NULL | ref | idx_user_id、idx_user_id_buy_date | idx_user_id_buy_date | 4 | const | 4 | 100.00 | where を使用; index を使用 | +----+-------------+----------+-----------+--------+----------------------------------+------------------------+--------+--------+----------+---------------------------+ セットに 1 行、警告 1 回 (0.01 秒) 結合インデックス idx_user_id_buy_date が削除されると、「Using filesort」が表示されます。 mysql> テーブル t_order を変更し、インデックス idx_user_id_buy_date を削除します。 クエリは正常、影響を受けた行は 0 行 (0.02 秒) レコード: 0 重複: 0 警告: 0 mysql> explain select user_id, order_id from t_order where user_id = 1 order by buy_date limit 3; +----+-------------+----------+-----------+--------+-------+-------+--------+---------+-----------------------------+-----+-------+---------+----------+-----------------------------+ | id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 | +----+-------------+----------+-----------+--------+-------+-------+--------+---------+-----------------------------+-----+-------+---------+----------+-----------------------------+ | 1 | SIMPLE | t_order | NULL | ALL | idx_user_id | NULL | NULL | NULL | 4 | 100.00 | where を使用; filesort を使用 | +----+-------------+----------+-----------+--------+-------+-------+--------+---------+-----------------------------+-----+-------+---------+----------+-----------------------------+ セットに 1 行、警告 1 件 (0.00 秒) 上記は、編集者が紹介したInnodbストレージエンジンインデックスの実装の詳細な説明と統合です。皆様のお役に立てれば幸いです。ご質問がある場合は、メッセージを残してください。編集者がすぐに返信します。また、123WORDPRESS.COM ウェブサイトをサポートしてくださっている皆様にも感謝申し上げます。 以下もご興味があるかもしれません:
|
<<: Vue 3 で Vue Router リンクを拡張する方法
>>: Linux 上の MariaDB は root ユーザーで起動します (推奨)
jar パッケージを Docker コンテナに変換する方法1.まずJavaイメージをダウンロードする...
MySQL 8.0.20 のインストールおよび設定方法は、参考としてグラフィック チュートリアルにま...
CentOS 8 に Jenkins をインストールするには、root アカウントまたは sudo ...
目次1 テスト環境1.1 サーバーハードウェア1.1.1 t2.マイクロ1.1.2 c5.large...
Nginx は同じドメイン名で構成されており、http と https の両方でアクセスできます。証...
場合によっては、ジャンプを完了するために href の代わりにハイパーリンク <a> を...
目次序文標準的なSFCの書き方スクリプト設定可変露出部品の取り付け小道具カスタムイベント要約する序文...
目次背景データ型を決定する方法は何ですか? 1. typeof を使用して基本データ型を決定します。...
テーブル作成コマンドには以下が必要です。 テーブルの名前フィールド名各フィールドを定義します(タイ...
今日、研究室のプロジェクトを見ていたとき、私にとって「難しい」問題に遭遇しました。実は、それは私があ...
1. ソフトウェアのダウンロードMySQL のダウンロードとインストール:公式サイトのダウンロード...
目次序文:システム要件:インストール環境:古いバージョンをアンインストールします: yum は具体的...
コンパイル後にAngularプロジェクトをNginxにデプロイする方法をオンラインで検索すると、ほと...
目的リクエスト アクセス ボリュームを制御するための Nginx ngx_http_limit_co...
CSS では、要素タグは、要素の表示モードの違いに応じて、インラインレベル要素とブロックレベル要素の...