ApsaraDB for SelectDB:Indeks terbalik

Cara kerja

Di Alibaba Cloud SelectDB, indeks terbalik memperlakukan baris tabel sebagai dokumen dan kolom sebagai bidang. Pendekatan ini memungkinkan indeks terbalik menemukan baris yang berisi kata kunci tertentu dengan cepat, sehingga meningkatkan performa kueri yang menggunakan klausa WHERE.

Berbeda dengan indeks biasa, indeks terbalik disimpan dalam file terbalik terpisah. File ini dipetakan secara logis ke file segmen tetapi tidak diintegrasikan dengannya. Pendekatan ini menghindari penulisan ulang file segmen saat pembaruan atau penghapusan indeks, sehingga secara signifikan mengurangi overhead pemrosesan.

Skenario penggunaan

• Mempercepat pencarian teks penuh pada tipe data string.

• Mempercepat penyaringan =, !=, >, >=, <, <= untuk tipe data string, numerik, dan datetime.

Keuntungan

• Dukungan komprehensif untuk operator logika.

  • Ditambahkan dukungan untuk index pushdown logika OR dan NOT.

  • Mendukung kombinasi kondisi ganda apa pun menggunakan AND, OR, dan NOT.

• Manajemen indeks yang fleksibel dan cepat.

  • Buat indeks terbalik saat membuat tabel.

  • Tambahkan indeks terbalik ke tabel yang sudah ada.

  • Hapus indeks terbalik dari tabel.

Keterbatasan

• Tipe data floating-point FLOAT dan DOUBLE tidak mendukung indeks terbalik karena masalah presisi. Sebagai gantinya, gunakan tipe data fixed-point DECIMAL yang mendukung indeks terbalik.

• Beberapa tipe data kompleks tidak mendukung indeks terbalik, termasuk MAP, STRUCT, JSON, HLL, BITMAP, QUANTILE_STATE, dan AGG_STATE. Untuk menggunakan indeks terbalik dengan data JSON, ubah kolom tersebut ke tipe data VARIANT.

• Anda dapat membuat indeks terbalik pada bidang bertipe numerik, tetapi tidak dapat menentukan parser, seperti english, chinese, atau unicode.

• Model DUPLICATE dan model UNIQUE dengan fitur Merge on Write yang diaktifkan mendukung indeks terbalik pada kolom apa pun. Sebaliknya, model AGGREGATE dan model UNIQUE dengan Merge on Write dinonaktifkan hanya mendukung indeks terbalik pada kolom Key. Karena model-model ini harus membaca dan menggabungkan semua data, sistem tidak dapat menggunakan indeks untuk pre-filtering.

Buat indeks saat pembuatan tabel

Ini adalah operasi sinkron. Pembuatan indeks selesai ketika tabel berhasil dibuat.

Sintaks

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

Parameter

Parameter pembuatan tabel

index_definition_list

Anda dapat mendefinisikan satu atau beberapa indeks saat membuat tabel. Gunakan format berikut: index_definition[, index_definition][, index_definition]....

index_definition

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

Parameter

Contoh: Buat tabel dengan indeks

-- Buat tabel dan indeks terbalik bernama idx_comment pada kolom comment.
-- USING INVERTED menetapkan jenis indeks menjadi indeks terbalik.
-- PROPERTIES("parser" = "english") menetapkan pemisah kata menjadi "english". Pemisah kata lain yang didukung termasuk "chinese" untuk teks Tiongkok dan "unicode" untuk teks campuran. Jika properti "parser" dihilangkan, tidak ada tokenisasi yang dilakukan.
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;

Tambahkan indeks

Operasi ini merupakan operasi asinkron. Anda dapat memeriksa progres pembuatan indeks dengan menggunakan SHOW ALTER TABLE COLUMN;.

Sintaks

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

Parameter

Parameter-parameter ini sama dengan parameter saat membuat tabel.

Contoh

Tambahkan indeks tanpa tokenisasi.

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

Tambahkan indeks yang menggunakan english untuk tokenisasi.

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

Lihat indeks terbalik

Lihat progres perubahan indeks

Memodifikasi indeks terbalik menggunakan perintah ALTER dan DROP adalah proses asinkron. Untuk melihat progresnya, gunakan pernyataan berikut.

SHOW ALTER TABLE COLUMN;

Lihat semua indeks tabel terbalik

Sintaks

SHOW INDEXES FROM <table_name>;

Contoh

SHOW INDEXES FROM user_tb;

Hapus indeks

Menghapus indeks adalah proses asinkron. Untuk memeriksa progresnya, lihat Kueri informasi tentang indeks terbalik.

Sintaks

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

Contoh

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

Pencarian teks penuh

Sintaks

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

Parameter

Contoh

-- Ambil baris di mana logmsg berisi keyword1.
SELECT * FROM log_tb WHERE logmsg MATCH_ANY 'keyword1';

-- Ambil baris di mana logmsg berisi keyword1 atau keyword2.
SELECT * FROM log_tb WHERE logmsg MATCH_ANY 'keyword1 keyword2';

-- Ambil baris di mana logmsg berisi keyword1 dan keyword2.
SELECT * FROM log_tb WHERE logmsg MATCH_ALL 'keyword1 keyword2';

-- Ambil baris di mana logmsg berisi frasa persis "keyword1 keyword2".
SELECT * FROM log_tb WHERE logmsg MATCH_PHRASE 'keyword1 keyword2';

Kueri kesetaraan dan rentang untuk numerik dan tanggal

Sintaks kueri menggunakan sintaks SQL standar.

Contoh

-- Kueri kesetaraan, rentang, IN, dan 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');

Perbandingan performa kueri

Topik ini menggunakan set data hackernews dengan 1 juta baris untuk membandingkan performa kueri dengan dan tanpa indeks terbalik.

Fungsi Tokenize

Fungsi TOKENIZE memecah string teks menjadi urutan istilah. Tokenisasi merupakan komponen inti dalam membangun dan menggunakan indeks terbalik. Kualitas tokenisasi secara langsung memengaruhi performa indeks.

Untuk melihat bagaimana string teks ditokenisasi, gunakan fungsi TOKENIZE untuk melihat hasilnya. Fungsi TOKENIZE memiliki dua parameter utama: parser dan parser_mode. Tabel berikut menjelaskan parameter-parameter tersebut.

Contoh:

-- Hasil tokenisasi bahasa Inggris.
SELECT TOKENIZE('I love CHINA','"parser"="english"');
+------------------------------------------------+
| tokenize('I love CHINA', '"parser"="english"') |
+------------------------------------------------+
| ["i", "love", "china"]                         |
+------------------------------------------------+
1 row in set (0.02 sec)

-- Hasil tokenisasi fine-grained dari pemisah kata Tiongkok.
SELECT TOKENIZE('武汉长江大桥','"parser"="chinese","parser_mode"="fine_grained"');
+-----------------------------------------------------------------------------------+
| tokenize('武汉长江大桥', '"parser"="chinese","parser_mode"="fine_grained"')       |
+-----------------------------------------------------------------------------------+
| ["武汉", "武汉长江大桥", "长江", "长江大桥", "大桥"]                              |
+-----------------------------------------------------------------------------------+
1 row in set (0.02 sec)

-- Hasil tokenisasi coarse-grained dari pemisah kata Tiongkok.
SELECT TOKENIZE('武汉市长江大桥','"parser"="chinese","parser_mode"="coarse_grained"');
+----------------------------------------------------------------------------------------+
| tokenize('武汉市长江大桥', '"parser"="chinese","parser_mode"="coarse_grained"')        |
+----------------------------------------------------------------------------------------+
| ["武汉市", "长江大桥"]                                                                 |
+----------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

-- Hasil tokenisasi bahasa campuran.
SELECT TOKENIZE('I love CHINA 我爱我的祖国','"parser"="unicode"');
+-------------------------------------------------------------------+
| tokenize('I love CHINA 我爱我的祖国', '"parser"="unicode"')       |
+-------------------------------------------------------------------+
| ["i", "love", "china", "我", "爱", "我", "的", "祖", "国"]        |
+-------------------------------------------------------------------+
1 row in set (0.02 sec)