ApsaraDB for SelectDB:転置インデックス

インデックスの仕組み

ApsaraDB for SelectDB では、テーブル内の各行が 1 つのドキュメントに対応し、各カラムがそのドキュメント内のフィールドに対応します。転置インデックスを利用すると、キーワードを含む行を高速に特定でき、WHERE 句によるフィルター処理の効率が向上します。

通常のインデックスとは異なり、転置インデックスは独立した「転置ファイル」にデータを格納します。これらのファイルは、主データファイル（セグメント）との間に論理的な関係のみがあり、物理的な統合は行われません。この設計により、転置インデックスの更新または削除時に主データファイルを再書き込みする必要がなく、処理オーバーヘッドが大幅に低減されます。

ユースケース

• 文字列型カラムにおける全文検索の高速化。

• 文字列型、数値型、日時型カラムにおける =, !=, >, >=, <, <= を用いたフィルター処理の高速化。

メリット

• 豊富な論理演算の組み合わせをサポート。

  • OR 演算および NOT 演算をインデックス層にプッシュダウン可能。

  • AND、OR、NOT を複数の条件にわたって任意に組み合わせ可能。

• 柔軟かつ高速なインデックス管理。

  • テーブル作成時に転置インデックスを作成可能。

  • 既存のテーブルに転置インデックスを追加可能。

  • テーブルから既存の転置インデックスを削除します。

制限事項

• 浮動小数点精度の問題により、FLOAT 型および DOUBLE 型はサポートされていません。代わりに DECIMAL 型をご利用ください。DECIMAL 型は転置インデックスをサポートしています。

• MAP、STRUCT、JSON、HLL、BITMAP、QUANTILE_STATE、AGG_STATE などの複雑なデータ型はサポートされていません。JSON 型を利用する場合は、VARIANT 型をご利用ください。

• 数値型カラムには転置インデックスを作成できますが、english、chinese、unicode などのトークナイザーパーサーを指定することはできません。

• Merge-on-Write が有効化された DUPLICATE テーブルおよび UNIQUE テーブルでは、任意のカラムに転置インデックスを作成できます。一方、AGGREGATE テーブルおよび Merge-on-Write が無効な UNIQUE テーブルでは、キーとなるカラム（key columns）にのみ転置インデックスを作成できます。これらのモデルでは、非キーとなるカラムには転置インデックスを作成できません。これは、マージ処理前に全データを読み込む必要があるため、早期フィルター処理にインデックスを利用できないためです。

テーブル作成時にインデックスを作成

これは同期操作です。テーブル作成完了と同時にインデックスが利用可能になります。

構文

CREATE TABLE  [IF NOT EXISTS] [db_name.]<table_name>
(
  <column_definition_list>,
  [<index_definition_list>] 
)
table_properties;

パラメーター

テーブル作成時のパラメーター

index_definition_list の構文

テーブル作成時に複数のインデックスを定義できます。書式： index_definition[, index_definition][, index_definition]...。

index_definition の定義

INDEX <index_name>(<column_name>) <index_type> [PROPERTIES("<key>" = "<value>")] [COMMENT '<comment>']

index_definition のパラメーター

テーブルおよびインデックス作成の例

-- comment カラムに idx_comment という名前の転置インデックスを作成
-- USING INVERTED でインデックスの種類を転置インデックスに設定
-- PROPERTIES("parser" = "english") で英語トークン化を指定。"chinese" および "unicode" もサポート。"parser" を省略するとトークン化は無効化されます。
CREATE TABLE hackernews_1m
(
    `id` BIGINT,
    `deleted` TINYINT,
    `type` String,
    `author` String,
    `timestamp` DateTimeV2,
    `comment` String,
    `dead` TINYINT,
    `parent` BIGINT,
    `poll` BIGINT,
    `children` Array<BIGINT>,
    `url` String,
    `score` INT,
    `title` String,
    `parts` Array<INT>,
    `descendants` INT,
    INDEX idx_comment (`comment`) USING INVERTED PROPERTIES("parser" = "english") COMMENT 'comment の転置インデックス'
)
DUPLICATE KEY(`id`)
DISTRIBUTED BY HASH(`id`) BUCKETS 10;

既存のテーブルにインデックスを追加

これは非同期操作です。SHOW ALTER TABLE COLUMN; を使用して進捗状況を確認できます。

構文

ALTER TABLE <table_name> ADD INDEX <index_name>(<column_name>) <index_type> [PROPERTIES("<key>" = "<value>")];

パラメーター

テーブル作成時と同じです。

例

トークン化を行わないインデックスを追加します。

ALTER TABLE user_tb ADD INDEX index_userId(user_id) USING INVERTED ;

「english」トークン化メソッドを使用するインデックスを追加します。

ALTER TABLE user_tb ADD INDEX index_city(city) USING INVERTED PROPERTIES("parser" = "english");

インデックスの確認

インデックス変更の進捗状況の確認

ALTER または DROP を用いたインデックス変更は非同期です。以下のステートメントで進捗状況を確認できます。

SHOW ALTER TABLE COLUMN;

テーブルのすべてのインデックスを表示

構文

SHOW INDEXES FROM <table_name>;

例

SHOW INDEXES FROM user_tb;

インデックスの削除

インデックスの削除は非同期操作です。進捗状況の確認については、「インデックスの確認」をご参照ください。

構文

-- 構文 1
DROP INDEX <index_name> ON <table_name>;
-- 構文 2
ALTER TABLE <table_name> DROP INDEX <index_name>;

例

DROP INDEX index_userId ON user_tb;
ALTER TABLE user_tb DROP INDEX index_city;

全文検索

構文

SELECT * FROM <table_name> WHERE <column_name> <conditional_logic> '<keywords>';

パラメーター

例

-- log_tb の logmsg カラムに keyword1 を含むすべての行を検索します。
SELECT * FROM log_tb WHERE logmsg MATCH_ANY 'keyword1';

-- log_tb の logmsg カラムに keyword1 または keyword2 を含むすべての行を検索します。
SELECT * FROM log_tb WHERE logmsg MATCH_ANY 'keyword1 keyword2';

-- log_tb の logmsg カラムに keyword1 および keyword2 の両方を含むすべての行を検索します。
SELECT * FROM log_tb WHERE logmsg MATCH_ALL 'keyword1 keyword2';

-- log_tb の logmsg カラムに keyword1 および keyword2 をこの順序で含むすべての行を検索します。
SELECT * FROM log_tb WHERE logmsg MATCH_PHRASE 'keyword1 keyword2';

数値型および日付型カラムにおける等価および範囲クエリ

クエリ構文は標準 SQL と同一です。

例

-- 等価、範囲、IN、NOT IN クエリ
SELECT * FROM user_tb WHERE id = 123;
SELECT * FROM user_tb WHERE ts > '2023-01-01 00:00:00';
SELECT * FROM user_tb WHERE op_type IN ('add', 'delete');

クエリパフォーマンスの比較

100 万行の hackernews データセットを用いて、転置インデックスあり／なしのクエリパフォーマンスをテストしました。

トークン化関数

トークン化関数は、連続したテキストを個別のトークンまたはフレーズに分割します。これは、転置インデックスの構築および利用の中心となる機能です。トークン化の品質および方法は、インデックスの品質およびパフォーマンスに直接影響します。

テキスト文字列がどのようにトークン化されるか不明な場合は、TOKENIZE 関数で確認してください。TOKENIZE 関数は、parser および parser_mode の 2 つの主要なパラメーターを受け取ります。以下にそれぞれの説明を示します。

以下に例を示します。

-- 英語トークン化の結果。
SELECT TOKENIZE('I love CHINA','"parser"="english"');
+------------------------------------------------+
| tokenize('I love CHINA', '"parser"="english"') |
+------------------------------------------------+
| ["i", "love", "china"]                         |
+------------------------------------------------+
1 row in set (0.02 sec)

-- 中国語トークナイザーによる細粒度トークン化の結果。
SELECT TOKENIZE('武汉长江大桥','"parser"="chinese","parser_mode"="fine_grained"');
+-----------------------------------------------------------------------------------+
| tokenize('武汉长江大桥', '"parser"="chinese","parser_mode"="fine_grained"')       |
+-----------------------------------------------------------------------------------+
| ["武汉", "武汉长江大桥", "长江", "长江大桥", "大桥"]                              |
+-----------------------------------------------------------------------------------+
1 row in set (0.02 sec)

-- 中国語トークナイザーによる粗粒度トークン化の結果。
SELECT TOKENIZE('武汉市长江大桥','"parser"="chinese","parser_mode"="coarse_grained"');
+----------------------------------------------------------------------------------------+
| tokenize('武汉市长江大桥', '"parser"="chinese","parser_mode"="coarse_grained"')        |
+----------------------------------------------------------------------------------------+
| ["武汉市", "长江大桥"]                                                                 |
+----------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

-- 多言語混合トークン化の結果。
SELECT TOKENIZE('I love CHINA 我爱我的祖国','"parser"="unicode"');
+-------------------------------------------------------------------+
| tokenize('I love CHINA 我爱我的祖国', '"parser"="unicode"')       |
+-------------------------------------------------------------------+
| ["i", "love", "china", "我", "爱", "我", "的", "祖", "国"]        |
+-------------------------------------------------------------------+
1 row in set (0.02 sec)