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になります

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

たとえば、上記の 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 秒)

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

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

たとえば、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 秒)

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

（３）列プレフィックスの一致は、列の値の先頭部分のみ一致します。

たとえば、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 秒)

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

（５）ある列と別の列との範囲一致を正確に行うと、最初の列は完全に一致し、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ツリーの限界

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

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

（３）マッチングはインデックスで定義された順序で左から右にのみ実行でき、インデックス内の列をスキップすることはできません。
たとえば、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 の他の関連記事に注目してください。