MySQL 8の新機能である降順インデックスの基礎となる実装の詳細な説明

降順インデックスとは何ですか?

インデックスについてはよくご存知かもしれませんが、降順インデックスについてはよくご存知ないかもしれません。実際、降順インデックスはインデックスのサブセットです。

通常、インデックスを作成するには次のステートメントを使用します。

t1(b,c,d)にインデックスidx_t1_bcdを作成します。

上記の SQL は、t1 テーブルの 3 つのフィールド b、c、d の結合インデックスを作成することを意味します。

しかし、誰もが知らないのは、上記の SQL が実際には次の SQL と同等であるということです。

t1(b asc,c asc,d asc) にインデックス idx_t1_bcd を作成します。

asc は昇順を意味します。この構文を使用して作成されたインデックスは昇順インデックスと呼ばれます。つまり、通常、インデックスを作成するときは昇順のインデックスを作成します。

インデックスを作成するときに、フィールドに asc を設定できると思うかもしれませんが、desc も設定できるのでしょうか?

もちろん、例えば次の 3 つのステートメントも可能です。

t1(b desc,c desc,d desc) にインデックス idx_t1_bcd を作成します。
t1(b asc,c desc,d desc) にインデックス idx_t1_bcd を作成します。
t1(b asc,c asc,d desc) にインデックス idx_t1_bcd を作成します。

この構文は MySQL でもサポートされています。この構文を使用して作成されたインデックスは降順インデックスと呼ばれます。重要な問題は、MySQL 8.0 より前では、構文レベルでのみサポートされており、最下位レベルでは実際にはサポートされていなかったことです。

以下の例では、Mysql7 と Mysql8 を使用します。

Mysql7 と Mysql8 でそれぞれ 5 つのフィールド (a、b、c、d、e) を持つテーブルを作成します。

テーブルt1を作成（
int主キー、
b 整数、
c 整数、
d 整数、
e varchar(20)
) エンジン=InnoDB;

次に、降順のインデックスを別途作成します。


t1(b desc,c desc,d desc) にインデックス idx_t1_bcd を作成します。

作成が成功したら、次の SQL を使用してインデックス情報を表示します。


t1 からのインデックスを表示します。

MySQL 7 では次の結果が得られます:

MySQL 8 では次の結果が得られます:

ここで注目するのは、Key_name idx_t1_bcd の 3 行のレコードのみです。注意深く見れば、次の 2 つの結果の Collat​​ion フィールドの結果が異なることがわかるはずです。

• MySQL 7では、照合フィールドの結果はA、A、Aであり、これは3つのフィールドb、c、dのソート方法がascであることを意味します。
• MySQL 8では、照合フィールドの結果はD、D、Dであり、これは3つのフィールドb、c、dのソート方法がdescであることを意味します。

しかし、インデックスを作成する際に、3 つのフィールド b、c、d のソート方法が desc であることを構文レベルで明確に指定しました。これは、MySQL 7 では降順インデックスは構文レベルでのみサポートされており、最下位レベルでは実質的なサポートがなく、インデックスは昇順に固定されていることを示しています。 MySQL 8 では、降順インデックスが実際に下から上へサポートされます。

ここまでで、昇順インデックスと降順インデックスについて大まかに理解できたはずですが、最下層で昇順インデックスと降順インデックスがどのように実装されているかを知らないため、実際には理解できていません。

昇順インデックスの実装

インデックスはクエリ速度を向上させるために使用されることはわかっていますが、インデックスによってクエリ速度が向上するのはなぜでしょうか?

[1,3,7,9,2,5,4,6,8] のような順序付けられていないシーケンスまたは配列である数値のシーケンスが与えられた場合、このシーケンスのクエリ速度を上げたい場合、まず何を行いますか? ほとんどの人は、まずソートを思いつくと思います。この順序なしのシーケンスを昇順にソートして、たとえば [1,2,3,4,5,6,7,8,9] を取得するのです。この順序付きシーケンスを取得したら、バイナリ検索などのアルゴリズムを使用して、このシーケンスのクエリ速度を向上させることができます。

この例で私が伝えたいのは、データ セットのクエリ速度を上げたい場合は、まずデータを並べ替えることができるということです。

したがって、Mysql テーブルに保存されているデータについても同じことが言えます。このテーブルのクエリ速度を上げたい場合は、まずこのテーブル内のデータを並べ替えます。すると、テーブル内のデータ行に多くのフィールドが含まれます。次に、これらのデータ行を並べ替えます。順序を決定するためにどのフィールドを使用すればよいでしょうか。これはインデックスです。インデックスを作成するときに指定した列は、テーブル内のデータ行を並べ替えるために使用されます。

たとえば、上記で作成した t1 テーブルを引き続き使用し、 t1 テーブルに 8 つのレコードを挿入します。

t1に値(4,3,1,1,'d')を挿入します。
t1に値(1,1,1,1,'a')を挿入します。
t1に値(8,8,8,8,'h')を挿入します。
t1に値(2,2,2,2,'b')を挿入します。
t1に値(5,2,3,5,'e')を挿入します。
t1に値(3,3,2,2,'c')を挿入します。
t1に値(7,4,5,5,'g')を挿入します。
t1に値(6,6,4,4,'f')を挿入します。

次に、データをファイルに保存する必要があります。ファイルにデータを保存する形式はおおよそ次のようになり、順序は挿入順序と一致します。

4311d
1111a
8888時間
2222b
5235e
3322c
7455グラム
6644f

t1 は Innodb ストレージ エンジンであり、a フィールドは主キーであるため、Innodb ストレージ エンジンは挿入されたデータを処理するときに主キーでソートすることに注意してください。つまり、上記のファイルにこれらのデータを保存する形式は正確ではありません。記事が長くなりすぎないようにするため、詳しく説明しません。興味のある学生は、公式アカウントをフォローできます: 1:25。B+ ツリーの生成方法など、Innodb でのインデックスの具体的な実装について説明する記事を具体的に書きます。

上記の保存方法に基づいてデータを検索する場合、たとえば、a=3 のレコード行を検索するには、レコードの最初の行から開始する必要があるため、6 回検索する必要があります。上記のデータを a フィールドのサイズに従って並べ替えると、次のようになります。

1111a
2222b
3322c
4311d
5235e
6644f
7455グラム
8888時間

ソート後、a=3 の行を検索する場合は、3 回検索するだけで済みます。これのもう 1 つの利点は、データ行 a=3.5 を見つける必要がある場合、ソート前のストレージ メソッドを使用すると、8 行のデータすべてをクエリして、データ行 a=3.5 が存在しないことを最終的に確認する必要があることです。ソート後のストレージ メソッドを使用すると、レコード行 4311d を見つけると、4>3.5 であることがわかるため、4 回だけ確認すれば済みます。つまり、レコード行 a=3.5 は存在しないということです。

そして、上記のインデックスの作成と同じように、次の SQL を記述して t1 のインデックスを作成するとします。

t1(b,c,d)にインデックスidx_t1_bcdを作成します。

この SQL 文は、t1 にインデックスを作成することを示しています。インデックス フィールドは b、c、d で、昇順になっています。したがって、元のデータは実際には 3 つのフィールド b、c、d に従ってソートされます。ソート後の結果は次のようになります。

1111a
2222b
5235e
4311d
3322c
7455グラム
6644f
8888時間

詳しく見てみましょう。上記のレコードは、3つのフィールドb、c、dでソートされています。例えば、1111aの3つのフィールドb、c、dの値は111で、2222bの3つのフィールドb、c、dの値は222です。111は222より小さいので、対応する行は1位になります。

では、このようにデータを並べ替える利点は何でしょうか?実際、その利点はフィールド a でソートする場合と似ています。たとえば、b=4、c=4、d=4 のデータを検索する場合、検索が速くなります。実際、これがインデックス作成の原理です。テーブルにインデックスを作成すると、テーブル内のデータがソートされ、ソートされたデータによって検索速度が向上します。

もう一つの注目すべき点は、ソートする方法がたくさんあること、またはバイナリツリー、赤黒ツリー、B +ツリーなどのデータ構造を使用できることです。これらのデータ構造は実際にはデータをソートしていますが、ソート形式は異なります。各データ構造には独自の特徴があり、MySQLで最もよく使用されるのはB +ツリーであることは誰もが知っているはずです。

これを読んだ後、誰もがインデックスについて新たな理解を持つようになったと思います。唯一の違いは、上で示した例はすべて昇順であり、ソートされたデータはクエリ速度を向上させるだけでなく、order byにも役立つことです。たとえば、t1をb asc、c asc、d ascで並べ替えるとします。このソートでは、t1テーブルにb、c、dの昇順インデックスが設定されている場合、t1テーブルのデータはb、c、dに従って事前にソートされているため、order byステートメントは、filesortを使用して再度ソートすることなく、ソートされたデータを直接使用できます。

また、order by が order by b desc, c desc, d desc の場合、b、c、d の昇順インデックスも使用できます。これは、order by b asc、c asc、d asc の場合は上から下へ移動でき、order by b desc、c desc、d desc の場合は下から上へ移動できるためです。

では、b 昇順、c 降順、d 降順の場合はどうなるでしょうか?この order by ステートメントでは、b、c、d の昇順インデックスを利用できないというのは本当ですか?

このとき、降順のインデックスが必要になります。

降順インデックスの実装

昇順インデックスの実装原理について、長い時間をかけて説明してきました。要約すると、指定されたフィールドのサイズに応じて、テーブル内のデータを昇順に並べ替えることです。

昇順とは何ですか?データのサイズを比較すると、小さい方が上に、大きい方が下になります。B+ ツリーの場合は、小さい方が左側に、大きい方が右側になります。降順とは、大きいものが上にあり、小さいものが下にあることを意味します。B+ ツリーの場合は、大きいものが左側にあり、小さいものが右側にあります。

したがって、上記の元のデータの場合:

4311d
1111a
8888時間
2222b
5235e
3322c
7455グラム
6644f

このデータを降順で並べ替えると、次のようになります。

8888時間
7455グラム
6644f
5235e
4311d
3322c
2222b
1111a

とても簡単ですよね? データを b 降順、c 降順、d 降順で並べ替えると、次のようになります。

8888時間
6644f
7455グラム
3322c
4311d
5235e
2222b
1111a

これも非常に簡単です。データを b 降順、c 昇順、d 降順で並べ替えたい場合はどうすればよいでしょうか。ちょっとわかりにくいですね？

実際には、難しくはありません。ソートは実際にはデータのサイズを比較することです。次の 3 行のデータを使用してシミュレートしてみましょう。

3322c
7455グラム
4311d

まず、b 降順、c 降順、d 降順で並べ替えると、結果は次のようになります。

7455グラム
3322c
4311d

b 降順、c 昇順、d 降順で並べ替えると、結果は次のようになります。

7455グラム
4311d
3322c

すでに理解している方もいるかもしれませんが、実は b desc は、フィールド b の大きいデータが一番上に、フィールド c の小さいデータが一番下に表示されることを意味します。データが等しい場合は、フィールド c が比較され、フィールド c は昇順でソートされます。つまり、フィールド c の小さいデータが一番下に、フィールド c の大きいデータが一番上に表示されます。それで、上記の結果が得られました。

これは降順のインデックスです。

要約する

実際、昇順インデックスと降順インデックスは、単に異なるソート方法にすぎません。MySQL 8 で降順インデックスが実装されてからは、インデックスの作成がより柔軟になりました。ビジネスで必要なソート規則に従って適切なインデックスを作成できるため、クエリが高速化されます。

もちろん、この記事では原理のみを説明します。MySQL のソートに使われる B+ ツリーは、私が上で例として挙げたような非常に単純な方法ではないことは、皆さんもご存知でしょう。ただし、B+ ツリーが使われていても、原理は同じで、データのサイズを比較するだけです。

もう 1 つのポイントは、現在、降順インデックスをサポートしているのは Innodb ストレージ エンジンのみであるということです。

これで、MySQL 8 の新機能の基礎となる降順インデックスの詳細な実装に関するこの記事は終了です。MySQL 8 降順インデックスに関するより関連性の高いコンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。