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

仕組み

Alibaba Cloud SelectDB では、転置インデックスはテーブルの行をドキュメントとして、列をフィールドとして扱います。これにより、転置インデックスは特定のキーワードを含む行を迅速に特定できるようになり、WHERE 句を使用するクエリのパフォーマンスが向上します。

通常のインデックスとは異なり、転置インデックスは独立した転置ファイルに保存されます。このファイルは論理的にセグメントファイルにマッピングされていますが、統合はされていません。このアプローチにより、インデックスの更新や削除時にセグメントファイルを書き換える必要がなくなり、処理オーバーヘッドが大幅に削減されます。

シナリオ

• 文字列データ型の全文検索を高速化します。

• 文字列型、数値型、および日時型での =, !=, >, >=, <, <= によるフィルタリングを高速化します。

メリット

• 論理演算子の包括的なサポート。

  • OR と NOT ロジックのインデックスプッシュダウンをサポートするようになりました。

  • 複数の条件による AND、OR、NOT の任意の組み合わせをサポートしています。

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

  • テーブルの作成時に転置インデックスを作成します。

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

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

制限事項

• FLOAT および DOUBLE 浮動小数点データ型は、精度に問題があるため転置インデックスをサポートしていません。代わりに、転置インデックスをサポートする DECIMAL 固定小数点データ型を使用してください。

• 一部の複合データ型は転置インデックスをサポートしていません。これには、MAP、STRUCT、JSON、HLL、BITMAP、QUANTILE_STATE、および AGG_STATE などが含まれます。JSON データで転置インデックスを使用するには、列を VARIANT データ型に変換してください。

• 数値型のフィールドに転置インデックスを作成できますが、parser （english、chinese、unicode など） を指定することはできません。

• DUPLICATE モデル、およびマージオンライトが有効な UNIQUE モデルは、任意の列で転置インデックスをサポートしています。一方、AGGREGATE モデル、およびマージオンライトが無効な UNIQUE モデルでは、キー列でのみ転置インデックスをサポートしています。これらのモデルでは、すべてのデータを読み取ってマージする必要があるため、システムは事前フィルタリングにインデックスを使用できません。

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

同期操作です。インデックスの作成は、テーブルが正常に作成されると完了します。

構文

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

パラメータ

テーブル作成パラメータ

index_definition_list

テーブルを作成するときに、1 つ以上のインデックスを定義できます。次の形式を使用します： index_definition[, index_definition][, index_definition]...。

index_definition

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

パラメータ

例： インデックスを持つテーブルの作成

-- テーブルを作成し、comment 列に idx_comment という名前の転置インデックスを作成します。
-- USING INVERTED は、インデックスタイプを転置インデックスに設定します。
-- PROPERTIES("parser" = "english") は、トークナイザーを "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 'inverted index for 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>';

パラメーター

例

-- logmsg に keyword1 を含む行を取得します。
SELECT * FROM log_tb WHERE logmsg MATCH_ANY 'keyword1';

-- logmsg に keyword1 または keyword2 を含む行を取得します。
SELECT * FROM log_tb WHERE logmsg MATCH_ANY 'keyword1 keyword2';

-- logmsg に keyword1 と keyword2 の両方を含む行を取得します。
SELECT * FROM log_tb WHERE logmsg MATCH_ALL 'keyword1 keyword2';

-- logmsg に正確なフレーズ "keyword1 keyword2" を含む行を取得します。
SELECT * FROM log_tb WHERE logmsg MATCH_PHRASE 'keyword1 keyword2';

数値と日付に対する等価クエリと範囲クエリ

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

例

-- 等価、範囲、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 関数は、テキスト文字列を一連のタームに分割します。トークン化は、転置インデックスを構築して使用するためのコアコンポーネントです。トークン化の品質は、インデックスのパフォーマンスに直接影響します。

文字列がどのようにトークン化されるかを確認するには、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)