プレフィックスインデックス

説明

テーブル作成時に、ソートキーを構成する1つ以上のカラムを指定します。テーブル内のデータ行はソートキーに基づいてソートされ、その後ディスクに保存されます。

データ書き込み時に、プレフィックスインデックスが自動的に生成されます。指定されたソートキーに従ってデータがソートされた後、1024行ごとに1つの論理データブロックに含まれます。その論理データブロックの最初のデータ行のソートキーカラムの値で構成されるインデックスエントリがプレフィックスインデックステーブルに追加されます。

これらの2層のソート構造により、クエリはバイナリ検索を使用してクエリ条件を満たさないデータを迅速にスキップでき、クエリ中の追加のソート操作を回避できます。

ヒント

プレフィックスインデックスはスパースインデックスであり、そのサイズはデータ量の少なくとも1024分の1です。したがって、通常はメモリに完全にキャッシュされ、クエリパフォーマンスを加速します。

使用上の注意

v3.0以降、主キーテーブルはORDER BYを使用してソートキーを定義することをサポートしています。v3.3以降、重複キーテーブル、集計テーブル、およびユニークキーテーブルはORDER BYを使用してソートキーを定義することをサポートしています。

重複キーテーブルのデータは、ソートキーORDER BYに従ってソートされます。ソートキーは任意のカラムの組み合わせにできます。

備考
ORDER BYとDUPLICATE KEYの両方が指定されている場合、DUPLICATE KEYは効果を発揮しません。
集計テーブルのデータは、まず集計キーAGGREGATE KEYに従って集計され、その後ソートキーORDER BYに従ってソートされます。ORDER BYとAGGREGATE KEYのカラムは同じである必要がありますが、カラムの順序は同じである必要はありません。
ユニークキーテーブルのデータは、まずユニークキーUNIQUE KEYに従って置き換えられ、その後ソートキーORDER BYに従ってソートされます。ORDER BYとUNIQUE KEYのカラムは同じである必要がありますが、カラムの順序は同じである必要はありません。
主キーテーブルのデータは、まず主キーPRIMARY KEYに従って置き換えられ、その後ソートキーORDER BYに従ってソートされます。

重複キーテーブルを例にとります。ソートキーはORDER BYを使用してuidとnameとして定義されています。

CREATE TABLE user_access (
    uid int,
    name varchar(64),
    age int, 
    phone varchar(16),
    last_access datetime,
    credits double
)
ORDER BY (uid, name);

ヒント

テーブル作成後、SHOW CREATE TABLE <table_name>;を使用して、指定されたソートカラムとORDER BY句内のこれらのカラムの順序を返された結果から確認できます。

プレフィックスインデックスエントリの最大長は36バイトであるため、超過部分は切り捨てられます。したがって、このテーブルのプレフィックスインデックスの各エントリは、uid (4バイト) + name (最初の32バイトのみ)であり、プレフィックスフィールドはuidとnameです。

注意

プレフィックスフィールドの数は3を超えてはならず、プレフィックスインデックスエントリの最大長は36バイトです。

プレフィックスフィールド内では、CHAR、VARCHAR、またはSTRING型のカラムは1回しか現れず、最後に配置されなければなりません。

次のテーブルを例にとります。最初の3つのカラムがソートキーカラムです。このテーブルのプレフィックスフィールドはname (20バイト)です。これは、このプレフィックスインデックスがVARCHAR型のカラム（name）で始まり、36バイトに達していないにもかかわらず、さらにカラムを含まずに直接切り捨てられるためです。したがって、このプレフィックスインデックスはnameフィールドのみを含みます。

MySQL [example_db]> describe user_access2;
+-------------+-------------+------+-------+---------+-------+
| Field       | Type        | Null | Key   | Default | Extra |
+-------------+-------------+------+-------+---------+-------+
| name        | varchar(20) | YES  | true  | NULL    |       |
| uid         | int         | YES  | true  | NULL    |       |
| last_access | datetime    | YES  | true  | NULL    |       |
| age         | int         | YES  | false | NULL    |       |
| phone       | varchar(16) | YES  | false | NULL    |       |
| credits     | double      | YES  | false | NULL    |       |
+-------------+-------------+------+-------+---------+-------+
6 rows in set (0.00 sec)

テーブルでORDER BYを使用してソートキーが指定されている場合、プレフィックスインデックスはソートキーに基づいて形成されます。ソートキーがORDER BYを使用して指定されていない場合、プレフィックスインデックスはキーのカラムに基づいて形成されます。

クエリを加速するプレフィックスインデックスを形成するためにソートキーを適切に設計する方法

ビジネスシナリオにおけるクエリとデータの分析は、適切なソートキーカラムを選択し、それらを適切な順序で配置してプレフィックスインデックスを形成するのに役立ち、クエリパフォーマンスを大幅に向上させることができます。

ソートキーカラムの数は通常3であり、4を超えることは推奨されません。カラムが多すぎるソートキーはクエリパフォーマンスを向上させることはできず、データロード中のソートオーバーヘッドを増加させます。
以下の順序でカラムを優先してソートキーを形成することをお勧めします:
1. クエリフィルター条件で頻繁に使用されるカラムをソートキーカラムとして選択します。 ソートキーカラムの数が複数ある場合、それらをクエリフィルター条件での使用頻度の降順に配置します。これにより、クエリフィルター条件がプレフィックスインデックスのプレフィックスを含む場合、クエリパフォーマンスが大幅に向上します。そして、フィルター条件がプレフィックスインデックスの全体のプレフィックスを含む場合、クエリはプレフィックスインデックスを完全に活用できます。もちろん、フィルター条件がプレフィックスの一部を含む限り、プレフィックスインデックスはクエリを最適化できます。しかし、フィルター条件に含まれるプレフィックスの長さが短すぎると、プレフィックスインデックスの効果は弱まります。ソートキーが(uid,name)であるユニークキーテーブルを例にとります。クエリフィルター条件が全体のプレフィックスを含む場合、例えばselect sum(credits) from user_access where uid = 123 and name = 'Jane Smith';、クエリはプレフィックスインデックスを完全に利用してパフォーマンスを向上させることができます。クエリ条件がプレフィックスの一部のみを含む場合、例えばselect sum(credits) from user_access where uid = 123;、クエリはプレフィックスインデックスを利用してパフォーマンスを向上させることができます。しかし、クエリ条件がプレフィックスを含まない場合、例えばselect sum(credits) from user_access where name = 'Jane Smith';、クエリはプレフィックスインデックスを使用して加速することはできません。
- 複数のソートキーカラムがクエリフィルター条件として似た頻度を持つ場合、これらのカラムの基数を測定できます。
- カラムの基数が高い場合、クエリ中により多くのデータをフィルタリングできます。基数が低すぎる場合、例えばブール型のカラムでは、そのフィルタリング効果は理想的ではありません。
  
  ヒント
  しかし、実際のビジネスシナリオにおけるクエリ特性を考慮すると、通常、やや低い基数のカラムが高い基数のカラムよりもクエリ条件として頻繁に使用されます。これは、フィルタリングが高い基数のカラムに頻繁に基づくクエリ、または極端なシナリオではUNIQUE制約を持つカラムに基づくクエリは、OLAPデータベースの複雑な分析クエリよりもOLTPデータベースのポイントクエリに似ているためです。
- また、ストレージ圧縮要因も考慮してください。低基数カラムと高基数カラムの順序によるクエリパフォーマンスの違いが明らかでない場合、低基数カラムを高基数カラムの前に配置すると、ソートされた低基数カラムのストレージ圧縮率が大幅に向上します。したがって、低基数カラムを前に配置することをお勧めします。

テーブル作成時にソートキーカラムを定義する際の考慮事項

ソートカラムのデータ型:
- 主キーテーブルのソートカラムは、数値型（整数、ブールを含む）、文字列、日付/日時型をサポートします。
- 重複キーテーブル、集計テーブル、およびユニークキーテーブルのソートカラムは、数値型（整数、ブール、小数を含む）、文字列、日付/日時型をサポートします。
集計テーブルおよびユニークキーテーブルでは、ソートカラムは他のカラムの前に定義されなければなりません。