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 ログ出力のリクエスト後パラメータを設定する方法

推薦する

シンプルな CSS テキストアニメーション効果

成果を達成する 実装コードhtml <div id=コンテナ> いらっしゃいませ <...

スライダー検証コードを実装するJavaScript

この記事では、スライダー検証コードを実装するためのJavaScriptの具体的なコードを参考までに共...

フロントエンド HTML+CSS+JS を使用してシンプルな TODOLIST 関数を開発する (メモ帳)

目次1. 簡単な紹介2. スクリーンショットを実行する3. コードの紹介4. まとめ1. 簡単な紹介...

複数のドメイン名に対する Nginx リバース プロキシを使用した HTTP および HTTPS サービスの実装

現在、Nginx は、Web サービスを提供するために、Windows ベースの IIS と Lin...

Linux (Centos7) での redis5 クラスターの構築と使用方法の詳細な説明

目次1. 簡単な説明2. クラスターを作成する手順2.1. ディレクトリを作成する2.2. ソースコ...

HTMLフォームのいくつかの送信方法の概要

最も一般的で、最もよく使用され、最も一般的な方法は、submit タイプを使用することです。コードを...

MySQL 8.0.22 圧縮パッケージの完全なインストールと構成のチュートリアル図 (テスト済みで効果的)

1. zipインストールパッケージをダウンロードするMySQL サーバー 8.0.22 の圧縮パッ...

JSコードコンパイラMonacoの使い方

序文私が必要としているのは、構文の強調表示、関数プロンプト、自動行折り返し、およびコードの折りたたみ...

MySQL の詳細な単一テーブルの追加、削除、変更、クエリの CRUD ステートメント

MySQL の追加、削除、変更、クエリステートメント1. 練習シートを作成するここでの練習表は3つの...

CSS3 で虫眼鏡効果を模倣するいくつかの方法の原理の分析

記事のタイトルが「模造虫眼鏡」なのはなぜですか?今日お話ししたいのは、一般的に言われているような、マ...

非常に便利なオープンソース Docker ツール 5 つを強くお勧めします

導入Docker コミュニティは、さまざまなユースケースを処理するのに役立つ多くのオープンソース ツ...

Linux が Sudo 権限昇格の脆弱性を公開、どのユーザーでも root コマンドを実行可能

Linux で最も一般的に使用される重要なユーティリティの 1 つである Sudo は、ほぼすべての...

CSS 複数 3 列適応レイアウト実装の詳細な説明

序文従来のWEBレイアウトに沿うため、すべてヘッダーとフッターモードの左・中央・右レイアウトで書かれ...

Ubuntu の Docker で mysql5.6 をインストールする方法

1. mysql5.6をインストールする docker 実行 mysql:5.6すべてのアイテムのダ...

Docker ベースの Redis クラスターの構築方法

Redisイメージをダウンロードする docker pull yyyyttttwww/redis を...