PolarDB:Indeks teks penuh IMCI

Di PolarDB IMCI, Anda dapat membuat, mengubah, atau menghapus inverted index dengan menentukan comment kolom saat membuat tabel atau dengan mengubah comment kolom menggunakan DDL.

Langkah 1: Buat indeks teks penuh

Buat indeks teks penuh pada kolom yang ingin Anda cari dan pilih tokenizer berdasarkan kebutuhan bisnis Anda.

1. Buat tabel uji dengan In-Memory Column Index (IMCI).

   CREATE TABLE t1 (
       id INT PRIMARY KEY,
       title VARCHAR(32) COMMENT "imci_fts(type=2)"
   )CHARSET utf8mb4 COMMENT 'columnar=1';

2. (Opsional) Modifikasi indeks teks penuh. Gunakan pernyataan ALTER TABLE untuk menambahkan indeks teks penuh ke kolom title dan tentukan tokenizer jieba dalam mode akurat.

   ALTER TABLE t1 MODIFY title VARCHAR(32) COMMENT "imci_fts(type=2,mode=0)";

3. (Opsional) Verifikasi efek tokenisasi. Sebelum memilih tokenizer, Anda dapat menggunakan fungsi dbms_imci.fts_tokenize untuk melihat pratinjau bagaimana tokenizer berbeda memproses teks Anda.

   -- Tokenizer token default
   CALL dbms_imci.fts_tokenize("I am PolarDB");
   -- Hasil: ["i", "am", "polardb"]  
   
   -- Tokenizer ngram
   CALL dbms_imci.fts_tokenize("I am PolarDB", "type=1");
   -- Hasil: ["i ", " a", "am", "m ", " p", "po", "ol", "ar", "rd", "db"]
   
   -- Tokenizer jieba
   CALL dbms_imci.fts_tokenize("I am PolarDB", "type=2");
   -- Hasil: ["PolarDB"]
   
   -- Tokenizer jieba, mode penuh
   CALL dbms_imci.fts_tokenize("I am PolarDB", "type=2,mode=1");
   -- Hasil: ["polardb"]

Langkah 2: Jalankan pencarian teks penuh

Setelah Anda membuat indeks, IMCI membangunnya di latar belakang. Setelah pembangunan selesai, Anda dapat menjalankan kueri teks menggunakan sintaks MATCH...AGAINST atau pernyataan LIKE yang dioptimalkan.

1. Gunakan kueri MATCH...AGAINST:

   -- Masukkan data sampel
   INSERT INTO t1 VALUES 
   (16, 'polarDB full-text index function title'), 
   (17, 'database title performance optimization');
   -- Kueri catatan di mana kolom title berisi "title"
   SELECT * FROM t1 WHERE MATCH(title) AGAINST("title");

   Jalankan perintah EXPLAIN untuk melihat rencana eksekusi. Operator FtsTableScan menunjukkan bahwa kueri menggunakan indeks teks penuh.

   EXPLAIN SELECT * FROM t1 WHERE MATCH(title) AGAINST("title") AND id > 10;
   +----+------------------------+------+-----------------------------------------------------------------+
   | ID | Operator               | Name | Extra Info                                                      |
   +----+------------------------+------+-----------------------------------------------------------------+
   |  1 | Select Statement       |      | IMCI Execution Plan (max_dop = 32, max_query_mem = 41230008320) |
   |  2 | └─Compute Scalar       |      |                                                                 |
   |  3 |   └─FILTER             |      | Cond：(t1.id > 10)                                              |
   |  4 |     └─FtsTableScan     | t1   | Term：("title") Fallback：(t1.title LIKE "%title%")             |
   +----+------------------------+------+-----------------------------------------------------------------+

   Bidang Fallback menunjukkan bahwa untuk data inkremental yang belum diindeks, sistem secara otomatis melakukan pemindaian tambahan menggunakan predikat LIKE untuk memastikan hasil lengkap.

2. Percepat kueri LIKE. Untuk memastikan kompatibilitas dengan kode aplikasi yang ada, IMCI dapat secara otomatis mengonversi kueri LIKE tertentu menjadi kueri MATCH...AGAINST untuk percepatan.

   SET imci_convert_like_to_match = on;
   EXPLAIN SELECT * FROM t1 WHERE title  LIKE "%title%";
   +----+------------------------+------+-----------------------------------------------------------------+
   | ID | Operator               | Name | Extra Info                                                      |
   +----+------------------------+------+-----------------------------------------------------------------+
   |  1 | Select Statement       |      | IMCI Execution Plan (max_dop = 32, max_query_mem = 41230008320) |
   |  2 | └─Compute Scalar       |      |                                                                 |
   |  3 |   └─FILTER             |      | Cond：(t1.title LIKE "%title%")                                 |
   |  4 |     └─FtsTableScan     | t1   | Term：("title") Fallback：(t1.title LIKE "%title%")             |
   +----+------------------------+------+-----------------------------------------------------------------+

   Rencana eksekusi juga menunjukkan operator FtsTableScan, yang menunjukkan bahwa IMCI berhasil mempercepat kueri LIKE.

3. Konversi MATCH ke LIKE. Jika indeks teks penuh tidak tersedia, IMCI dapat mengonversi kueri MATCH menjadi kueri LIKE untuk memastikan kueri tetap dapat dijalankan dan memanfaatkan indeks yang ada jika memungkinkan. Rencana eksekusi menunjukkan pemindaian tabel penuh.

   SET imci_enable_query_fts_like = on;
   EXPLAIN SELECT * FROM t1 WHERE MATCH(title) AGAINST("title");
   +----+----------------------+------+-----------------------------------------------------------------+
   | ID | Operator             | Name | Extra Info                                                      |
   +----+----------------------+------+-----------------------------------------------------------------+
   |  1 | Select Statement     |      | IMCI Execution Plan (max_dop = 32, max_query_mem = 41230008320) |
   |  2 | └─Compute Scalar     |      |                                                                 |
   |  3 |   └─Table Scan       | t1   | Cond：(title LIKE "%title%")                                    |
   +----+----------------------+------+-----------------------------------------------------------------+

Langkah 3: Kelola dan pantau indeks

Kueri status pembangunan dan metadata indeks. Anda juga dapat menghapus indeks ketika tidak lagi diperlukan.

1. Monitor progres dan status pembangunan indeks:

   -- Lihat semua inverted index
   SHOW imci indexes fulltext;
   SELECT * FROM information_schema.imci_fts_indexes;
   
   -- Lihat inverted index tertentu
   SHOW imci indexes fulltext FOR [db_name].[table_name];
   SELECT * FROM information_schema.imci_fts_indexes 
   WHERE schema_name='[db_name]' AND table_name='[table_name]';
   
   -- Lihat metadata inverted index tertentu
   SELECT * FROM information_schema.imci_fts_index_metas 
   WHERE schema_name='[db_name]' AND table_name='[table_name]' AND column_name='[column_name]';
   
   -- Lihat data segmen inverted index tertentu
   SELECT * FROM information_schema.imci_fts_index_segs 
   WHERE schema_name='[db_name]' AND table_name='[table_name]' AND column_name='[column_name]';
   
   -- Lihat data paket inverted index tertentu
   SELECT * FROM information_schema.imci_fts_index_packs 
   WHERE schema_name='[db_name]' AND table_name='[table_name]' AND column_name='[column_name]';

2. Hapus indeks teks penuh. Jika Anda tidak lagi memerlukan indeks teks penuh pada suatu kolom, Anda dapat menghapusnya dengan memodifikasi COMMENT kolom tersebut.

   ALTER TABLE t1 MODIFY title VARCHAR(255) COMMENT 'imci_fts(enable=0)';

Langkah 4: Parameter konfigurasi inti

Buat tabel kamus kustom

1. Pertama, buat tabel dengan struktur dan nama kolom yang sama seperti tabel my_fts_dict.

   • type: Jenis tokenizer. Hanya mendukung tokenizer jieba (type 2) dan ik (type 3).

   • word: Kata entitas.

   • is_added: Menentukan apakah akan menambahkan (nilai 1) atau menghapus (nilai 2) kata dari kamus kustom.

   CREATE TABLE my_fts_dict (
     `type` INT UNSIGNED NOT NULL COMMENT '2 for jieba, 3 for ik',
     `word` VARCHAR(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'dict word',
     `is_added` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1 for add, 2 for delete'
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='imci fts user dict';

2. Kamus kustom harus berupa tabel InnoDB. Anda dapat melakukan operasi baca dan tulis seperti pada tabel biasa.

   1. Tambahkan kata:

      INSERT INTO my_fts_dict VALUES(2, "PolarDB", 1)

   2. Hapus kata:

      INSERT INTO my_fts_dict(type, word, is_added) VALUES(2, "PolarDB", 2);
      or
      DELETE FROM my_fts_dict WHERE type=2 AND word = "PolarDB";

Konfigurasi kamus kustom

• Setelah membuat tabel kamus kustom, Anda dapat mengatur nilai opsi imci_fts_user_dict_table. Misalnya, test/my_fts_dict'.

  SET GLOBAL imci_fts_user_dict_table = 'test/my_fts_dict';

• Jika Anda telah mengatur parameter imci_fts_user_dict_table, Anda dapat memicu pemuatan ulang kamus dengan mengatur parameter imci_fts_user_dict_update ke ON.

  SET GLOBAL imci_fts_user_dict_update = ON;

  Catatan

  Kamus kustom baru hanya berlaku untuk inverted index yang dibangun setelah pembaruan. Untuk menerapkan perubahan pada inverted index yang sudah ada, Anda harus membangun ulang indeks tersebut.