MySQL B-Tree インデックスの簡単な分析

MySQL B-Tree インデックスの簡単な分析

Bツリーインデックス

異なるストレージ エンジンでは、異なるストレージ構造を使用する場合もあります。たとえば、NDB クラスター ストレージ エンジンは、名前が BTREE であるにもかかわらず、内部的には T ツリー構造を使用してこのインデックスを格納しますが、InnoDB は B+ ツリーを使用します。

B ツリーは通常、すべての値を順番に格納し、各リーフ ページはルートまで同じ距離を持ちます。次の図は、InnoDB インデックスがどのように機能するかを大まかに示しています。

MySQL がインデックスに B ツリーや赤黒ツリーではなく B+ ツリーを使用するのはなぜですか?

上記の記事を読めば、B-Tree インデックスがデータに素早くアクセスできる理由がわかります。ストレージ エンジンは必要なデータを取得するためにテーブル全体のスキャンを実行する必要がなくなり、リーフ ノードにはすべての要素情報が含まれ、各リーフ ノード ポインターは次のノードを指すため、範囲データの検索に非常に適しています。

インデックスは、CREATE TABLE ステートメントで定義された順序に従って複数の値を並べます。

すると、インデックスのソート規則は last_name、first_name、dob の順序になります。

Bツリーインデックスを使用できるクエリの種類
B ツリー インデックスは、完全なキー値、キー値の範囲、またはキー プレフィックスの検索に適しています。
キープレフィックス検索は、左端のプレフィックスに基づいて検索する場合にのみ使用されます。

粒子を取り上げます:

テーブル People を作成 (
  last_name VARCHAR ( 50 ) NOT NULL,
  first_name VARCHAR ( 50 ) NOT NULL,
  生年月日がNULLではない、
  性別列挙型 ( 'm', 'f' ) NOT NULL、
キー ( 姓、名、生年月日 ) 
);

この表のインデックスは次のとおりです。

結果を入力する

タイプ結果の値は最良から最悪まで次のとおりです。

システム > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般的に、クエリが少なくとも範囲レベル、できれば参照レベルに到達するようにする必要があります。そうしないと、パフォーマンスの問題が発生する可能性があります。

possible_keys: SQL で使用されるインデックス

key: MySQL が実際に使用することを決定したキー (インデックス) を表示します。インデックスが選択されていない場合、キーはNULLになります

(1)完全な値一致完全な値一致とは、インデックス内のすべての列が一致することを指します。

たとえば、上記の People テーブルのインデックス (last_name、first_name、dob) を使用すると、last_name = 'Cuba Allen'、first_name = 'Chuang'、dob = '1996-01-01' の人物を見つけることができます。これは、インデックス内のすべての列がマッチング、つまり完全な値マッチングに使用されることを意味します。

mysql> EXPLAIN select * from People where last_name = 'aaa' and first_name = 'bbb' and dob='2020-11-20' \G;
************************** 1. 行 ****************************
     id: 1
 選択タイプ: シンプル
    表: 人々
 パーティション: NULL
    タイプ: ref
可能なキー: 姓
     key: last_name <-----このキーは定義したインデックスであることがわかります。key_len: 307
     参照: const、const、const
    行数: 1
  フィルター: 100.00
    追加: NULL
セットに 1 行、警告 1 件 (0.00 秒)

エラー: 
クエリが指定されていません

(2)左端のプレフィックスを一致させるには、インデックスの最初の列のみを使用できます。

たとえば、last_name='aaa' の人、つまり姓が Zeng の人を見つけるために使用できます。ここでは、インデックスの左端の列のみが一致、つまり左端のプレフィックスの一致に使用されます。

mysql> EXPLAIN select * from People where last_name = 'aaa' \G;
************************** 1. 行 ****************************
     id: 1
 選択タイプ: シンプル
    表: 人々
 パーティション: NULL
    タイプ: ref
可能なキー: 姓
     key: last_name <---- 使用されるインデックス key_len: 152
     参照: 定数
    行数: 3
  フィルター: 100.00
    追加: NULL
セットに 1 行、警告 1 件 (0.00 秒)

エラー: 
クエリが指定されていません

(3)列プレフィックスの一致は、列の値の先頭部分のみ一致します。

たとえば、last_name LIKE 'a%' を持つ人、つまり、姓が Z で始まるすべての人を検索する場合に使用できます。ここでは、インデックスの左端の列のプレフィックスのみが一致に使用され、つまり、列プレフィックスが一致します。

mysql> EXPLAIN select * from People where last_name = 'a%' \G;
************************** 1. 行 ****************************
     id: 1
 選択タイプ: シンプル
    表: 人々
 パーティション: NULL
    タイプ: ref
可能なキー: 姓
     key: last_name <--- 使用されるインデックス key_len: 152
     参照: 定数
    行数: 1
  フィルター: 100.00
    追加: NULL
セットに 1 行、警告 1 件 (0.00 秒)

エラー: 
クエリが指定されていません

(4)範囲値を一致させるには、インデックスの最初の列のみを使用して、特定の範囲内にあるデータを検索できます。

たとえば、last_name が 'aaa' から 'aaabbbccc' の間にある人を検索するために使用できます。ここでは、範囲一致、つまり範囲値の一致には、インデックスの左端の列のプレフィックスのみが使用されます。

mysql> EXPLAIN select * from People where last_name BETWEEN 'aaa' and 'aaabbbccc'\G;
************************** 1. 行 ****************************
     id: 1
 選択タイプ: シンプル
    表: 人々
 パーティション: NULL
    タイプ: 範囲
可能なキー: 姓
     key: last_name <--- 使用されるインデックス key_len: 152
     参照: NULL
    行数: 3
  フィルター: 100.00
    追加: インデックス条件の使用
セットに 1 行、警告 1 件 (0.00 秒)

エラー: 
クエリが指定されていません

(5)ある列と別の列との範囲一致を正確に行うと、最初の列は完全に一致し、2番目の列は範囲一致になります。

たとえば、last_name='aaa' AND first_name LIKE 'b%' の人、つまり姓が Zeng で名が C で始まる人を見つけるために使用できます。ここで、インデックスの左端の列は完全一致に使用され、2 番目の列は範囲一致に使用されます。

mysql> EXPLAIN select * from People where last_name = 'aaa' and first_name like 'b%'\G;
************************** 1. 行 ****************************
     id: 1
 選択タイプ: シンプル
    表: 人々
 パーティション: NULL
    タイプ: 範囲
可能なキー: 姓
     key: last_name <--- 使用されるインデックス key_len: 304
     参照: NULL
    行数: 1
  フィルター: 100.00
    追加: インデックス条件の使用
セットに 1 行、警告 1 件 (0.00 秒)

エラー: 
クエリが指定されていません

(6)インデックスのみにアクセスするクエリ:クエリはデータ行にアクセスせずにインデックスのみにアクセスする必要があります。

たとえば、select last_name, first_name where last_name='aaa'; の場合、インデックスに含まれる last_name 列と first_name 列のみが照会されるため、データ行を読み取る必要はありません。

mysql> explain select last_name,first_name,dob from People where last_name = 'aaa'
************************** 1. 行 ****************************
      id: 1
 選択タイプ: シンプル
    表: 人々
  パーティション: NULL
     タイプ: ref
可能なキー: 姓
     キー: last_name
   キーの長さ: 152
     参照: 定数
     行数: 1
   フィルター: 100.00
    追加: インデックスの使用
セットに 1 行、警告 1 件 (0.00 秒)

エラー: 
クエリが指定されていません

Bツリーの限界

(1)索引の左端の列からのみ検索できます。
たとえば、People テーブルのインデックスは、first_name が 'bbb' である人を検索するために使用することはできません。また、これらの列はどちらも左端の列ではないため、特定の誕生日を持つ人を検索するために使用することもできません。

(2)インデックスの左端の列の左端のプレフィックスのみが一致します。
たとえば、People テーブルのインデックスでは、last_name が '%b' に似ている人を見つけることができません。last_name はこのインデックスの左端の列ですが、MySQL インデックスでは last_name が 'b' で終わるレコードを見つけることができません。

(3)マッチングはインデックスで定義された順序で左から右にのみ実行でき、インデックス内の列をスキップすることはできません。
たとえば、MySQL はインデックス内の列をスキップできず、インデックス内の左端の列と最後の列を組み合わせて使用​​することができないため、People テーブルのインデックスを使用して last_name='a' AND bod='1996-01-01' を持つ人物を検索することはできません。インデックスの中央の列を指定しない場合、MySQL はインデックスの左端の列、つまり最初の列のみを使用できます。

(4)クエリに列の範囲クエリが含まれている場合、その列の右側にあるすべての列はインデックス最適化を使用して検索することはできません。
たとえば、次のようなクエリがあります: where last_name='a' AND first_name LIKE 'b%' AND dob='1996-01-01'; このクエリでは、LIKE が範囲条件であるため、インデックスの最初の 2 つの列のみを使用でき、first_name の後のインデックス列は無効になります。 (最適化のポイント: インデックス列では LIKE などの範囲条件を使用しないようにしてください。代わりに複数の等号条件を使用して、後続のインデックス列が有効になるようにしてください。)

上記は、MysQL B-Tree インデックスの詳細の簡単な分析です。MysQL B-Tree インデックスの詳細については、123WORDPRESS.COM の他の関連記事に注目してください。

以下もご興味があるかもしれません:
  • MySQL フルテキスト インデックス、ジョイント インデックス、Like クエリ、JSON クエリのうち、どれが高速ですか?
  • MySQL全文インデックスを使用して検索エンジンのサンプルコードの簡易版を実現する
  • MySQLが全文インデックス共有を実現
  • MySQL フルテキストインデックスアプリケーションに関する簡単なチュートリアル
  • MySQL全文インデックスに基づく詳細な理解
  • MySQLインデックスが失敗するいくつかの状況の詳細な分析
  • MySQL共通インデックスとユニークインデックスの選択に関する詳細な分析
  • MySQL 8.0 の降順インデックス
  • MySQL 8.0 のインデックス スキップ スキャン
  • MySQL 8.0 の非表示インデックスの詳細な説明
  • MySQL インデックスの一般的な問題の概要
  • MySql インデックスはクエリ速度を向上させる一般的な方法のコード例
  • MySQL全文インデックスの原理と欠点

<<:  フロントエンドの HTML 知識ポイントのまとめ (推奨)

>>:  Nginx ログ出力のリクエスト後パラメータを設定する方法

推薦する

MySQL のロック待機とデッドロック問題の分析

目次序文: 1. ロック待機とデッドロックを理解する2. 現象の再発と治療要約:序文: MySQL ...

画像ボタンをフォームのリセットボタンとして使用する方法

フォームを作成するときに、送信ボタンとリセットボタンを配置することがよくあります。ページの外観を考慮...

html ページ!--[IE の場合]...![endif]--使用方法の詳細な紹介

コードをコピーコードは次のとおりです。 <!--[IEの場合]><script t...

Centos7 で yum を使用して Mysql5.7.19 をインストールする詳細な手順

Centos7 の yum ソースには、mysql の代わりに mariaDB が使用されているため...

ブラウザ間の hr 区切り文字の違い

Webページを作るときに、区切り線hrを使うことがありますが、IE6やIE7で表示するのは非常に苦痛...

VMware での Ubuntu 16.04 イメージの完全インストール チュートリアル

この記事では、VMware 12でのUbuntu 16.04イメージのインストールチュートリアルを参...

vue3+threejs を使用して iView 公式サイトのビッグウェーブ特殊効果の例を模倣する

目次序文1. レンダリング2. コード3. 背景画像素材要約する序文Threejs は、Web ベー...

ソースコードから、Vue2がデータとメソッドを直接取得できる理由がわかる

目次1. 例: これはデータとメソッドを直接取得できます2. 環境を準備し、ソースコードをデバッグし...

js を使用して年カルーセル選択効果をネイティブに実装する例

序文js を使用して、年の回転選択効果を実現します。では早速、写真を見てみましょう。 1. アイデア...

Spark RDD をデータフレームに変換し、それを MySQL に書き込む例

DataframeはSpark 1.3.0で導入された新しいAPIで、Sparkで大規模な構造化デー...

nginx をベースにした Web クラスター プロジェクトをすばやく構築する方法を説明します。

目次1. プロジェクト環境2. プロジェクトの説明3. プロジェクトの手順1. インストール2. 構...

NginxはIP経由の直接アクセスを禁止し、カスタム500ページにリダイレクトします

設定ファイルに直接 サーバー{ listen 80 default; # IPへの直接アクセスを禁止...

MySQLでconcat関数を使用する方法

以下のように表示されます。 //managefee_managefee テーブルの年と月を照会し、c...

Vue ページ内の公開マルチタイプ添付画像アップロード領域と適用可能な折りたたみパネル (サンプルコード)

フロントエンド プロジェクトでは、添付ファイルのアップロードは非常に一般的な機能であり、ほぼすべての...

MySQL クラスター化インデックスのページ分割原理の分析例

この記事では、MySQL クラスター化インデックスのページ分割を例を使って説明します。ご参考までに、...