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

説明

テーブル作成時に、ソートキーを構成する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に従ってソートされます。

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

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データベースのポイントクエリに近いからです。
- また、ストレージ圧縮要因も考慮してください。低基数カラムと高基数カラムの順序によるクエリパフォーマンスの違いが明確でない場合、低基数カラムを高基数カラムの前に配置すると、ソートされた低基数カラムのストレージ圧縮率が大幅に向上します。したがって、低基数カラムを前に配置することをお勧めします。

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

ソートカラムのデータ型：
- 主キーテーブルのソートカラムは、数値型（整数、ブールを含む）、文字列、日付/日時型をサポートしています。
- 重複キーテーブル、集計テーブル、ユニークキーテーブルのソートカラムは、数値型（整数、ブール、小数を含む）、文字列、日付/日時型をサポートしています。
集計テーブルとユニークキーテーブルでは、ソートカラムは他のカラムよりも前に定義する必要があります。