バージョン: 3.2

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

説明

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

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

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

ヒント

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

使用上の注意

重複キーテーブルの場合、ソートキーカラムは DUPLICATE KEY で指定されます。集計テーブルまたはユニークキーテーブルの場合、ソートキーカラムは制約カラムと結合され、AGGREGATE KEY または UNIQUE KEY で指定されます。v3.0以降、主キーテーブルはソートキーカラムと主キーカラムを分離し、より柔軟な機能を提供します。ソートキーカラムは ORDER BY で指定され、主キーカラムは PRIMARY KEY で指定されます。

次の例では、ユニークキーテーブルを使用して、テーブル作成時にソートキーカラムを指定し、それらがどのようにプレフィックスインデックスを構成するかを示します。

ユニークキーテーブルを作成する際、DUPLICATE KEY で uid と name をソートキーカラムとして指定できます。

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

注記

ユニークキーテーブル、集計テーブル、またはユニークキーテーブルを作成した後、DESCRIBE <table_name>; を使用してそのソートキーカラムを表示できます。返された結果では、Key フィールドが true と表示されているカラムがソートキーカラムです。主キーテーブルを作成した後、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)

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

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

ヒント

主キーテーブルを除き、現在、他のタイプのテーブルのソートキーはテーブル作成後に変更できません。したがって、データとクエリの特性を慎重に分析して、適切なソートキーを設計することをお勧めします。

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

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

重複キーテーブル、集計テーブル、またはユニークキーテーブルのソートキーカラムを定義する際には、次の点に注意してください:

ソートキーカラムは、テーブル作成時に他のカラムの前に定義されなければなりません。
ソートキーカラムは、テーブル内の最初の1つ以上のカラムでなければならず、ソートキーカラムの順序はテーブル内のこれらのカラムの順序と一致していなければなりません。
ソートキーカラムのデータ型は、数値型（DOUBLE と FLOAT を除く）、文字列型、および日付型であることができます。