PolarDB:Panduan Pengguna Indeks Teks Penuh IMCI

PolarDB IMCI memungkinkan Anda membuat, memodifikasi, atau menghapus indeks terbalik dengan menentukan comment kolom saat membuat tabel atau memodifikasi comment kolom melalui DDL.

1: Membuat indeks teks penuh

Buat indeks teks penuh pada kolom yang memerlukan pencarian teks, dan pilih tokenizer berdasarkan kebutuhan bisnis Anda.

1. Buat tabel uji: Buat tabel dengan indeks penyimpanan kolom.

   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 menentukan mode mesin pencari tokenizer jieba.

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

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

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

2: Menjalankan kueri pencarian teks penuh

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

1. Gunakan kueri MATCH...AGAINST:

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

   Gunakan EXPLAIN untuk melihat rencana eksekusi. Jika operator FtsTableScan muncul, kueri telah mengenai 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%")             |
   +----+------------------------+------+-----------------------------------------------------------------+

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

2. Percepat kueri LIKE: Untuk kompatibilitas dengan kode aplikasi yang ada, IMCI mendukung konversi otomatis kueri LIKE tertentu menjadi 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 menampilkan operator FtsTableScan, yang mengonfirmasi bahwa kueri LIKE telah berhasil dipercepat.

3. Konversi MATCH ke LIKE: Ketika tidak ada indeks teks penuh, untuk memastikan kueri tetap berjalan dengan benar sambil memanfaatkan indeks yang ada sebanyak mungkin, IMCI dapat mengonversi MATCH menjadi LIKE, dan 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%")                                    |
   +----+----------------------+------+-----------------------------------------------------------------+

3: Mengelola dan memantau indeks

Kueri status pembangunan dan metadata indeks, serta hapus indeks ketika tidak lagi diperlukan.

1. Pantau progres dan status pembangunan indeks:

   -- Lihat semua indeks terbalik
   SHOW imci indexes fulltext;
   SELECT * FROM information_schema.imci_fts_indexes;
   
   -- Lihat indeks terbalik 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 indeks terbalik 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 indeks terbalik 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 penyimpanan kolom indeks terbalik 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 kolom, Anda dapat menghapusnya dengan memodifikasi COMMENT kolom tersebut.

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

4: Variabel sistem inti

Buat tabel kamus kustom

1. Pertama, definisikan tabel dengan struktur yang sama seperti tabel my_fts_dict. Nama kolom harus sesuai dengan nama kolom pada tabel my_fts_dict.

   • type: Jenis tokenizer. Hanya tokenizer jieba (type 2) dan tokenizer IK (type 3) yang didukung.

   • word: Kata dalam kamus.

   • is_added: Tambahkan kata ke kamus kustom (nilai 1) atau hapus (nilai 2).

   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 itu sendiri adalah tabel InnoDB. Operasi baca dan tulis sama seperti 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, konfigurasikan parameter imci_fts_user_dict_table. Misalnya, test/my_fts_dict'.

  SET GLOBAL imci_fts_user_dict_table = 'test/my_fts_dict';

• Jika imci_fts_user_dict_table sudah diatur, Anda juga dapat mengatur imci_fts_user_dict_update ke ON untuk memicu pemuatan ulang kamus kustom yang ditentukan oleh imci_fts_user_dict_table.

  SET GLOBAL imci_fts_user_dict_update = ON;

  Catatan

  Kamus kustom baru hanya berlaku untuk indeks terbalik yang dibangun setelahnya. Untuk menerapkannya pada indeks terbalik yang sudah ada, Anda harus membangun ulang indeks terbalik tersebut.

Buat tabel sinonim kustom

Pertama, definisikan tabel dengan struktur yang sama seperti tabel my_synonym_dict. Nama kolom harus sesuai dengan nama kolom pada tabel my_synonym_dict.

• word: Daftar sinonim. Kata berbeda dipisahkan dengan koma.

• type: Jenis sinonim.

  • 0 menunjukkan bahwa semua kata dalam daftar saling bersinonim. Misalnya, "db,database,polardb" berarti "db" <=> "database" <=> "polardb". Saat Anda mencari "db", "database", atau "polardb", semua data yang cocok dengan "db", "database", dan "polardb" dikembalikan.

  • 1 menunjukkan bahwa kata pertama dalam daftar adalah istilah utama dan yang lainnya adalah sinonimnya. Misalnya, "db,database,polardb" berarti "db" => "database", "polardb". Saat Anda mencari "db", semua data yang cocok dengan "db", "database", dan "polardb" dikembalikan. Namun, saat Anda mencari "database", hanya data yang cocok dengan "database" yang dikembalikan.

CREATE TABLE my_synonym_dict (
  `word` VARCHAR(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'comma-separated synonyms',
  `type` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0 for equivalent, 1 for mapping'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='imci fts synonym';

Tabel sinonim kustom itu sendiri adalah tabel InnoDB. Operasi baca dan tulis sama seperti tabel biasa.

• Tambahkan kata:

  INSERT INTO my_synonym_dict VALUES("db,database,polardb", 0);
  INSERT INTO my_synonym_dict VALUES("imci,clickhouse,duckdb", 1);

• Hapus kata:

  DELETE FROM my_synonym_dict WHERE type=1 AND word = "imci,clickhouse,duckdb";

Konfigurasi sinonim kustom

Setelah membuat tabel sinonim kustom, konfigurasikan parameter imci_fts_synonym_table. Misalnya, test/my_synonym_dict, di mana test adalah nama database dan my_synonym_dict adalah nama tabel.

SET GLOBAL imci_fts_synonym_table = 'test/my_synonym_dict';

Jika imci_fts_synonym_table sudah diatur, Anda juga dapat mengatur imci_fts_synonym_update ke ON untuk memicu pemuatan ulang sinonim kustom yang ditentukan oleh imci_fts_synonym_table.

SET GLOBAL imci_fts_synonym_update = ON;

Buat tabel stop word kustom

Pertama, definisikan tabel dengan struktur yang sama seperti tabel my_stop_words. Nama kolom harus sesuai dengan my_stop_words.

• word: Stop word.

• is_added: Tambahkan kata ke kamus kustom (nilai 1) atau hapus (nilai 0).

CREATE TABLE my_stop_words (
  `word` VARCHAR(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'stop word',
  `is_added` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1 for add, 0 for delete'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='imci fts stop word';

Tabel stop word kustom itu sendiri adalah tabel InnoDB. Operasi baca dan tulis sama seperti tabel biasa.

• Tambahkan kata:

  INSERT INTO my_stop_words VALUES("am", 1);
  INSERT INTO my_stop_words VALUES("chinese", 1);

• Hapus kata:

  INSERT INTO my_stop_words(word, is_added) VALUES("chinese", 0);
  -- or
  DELETE FROM my_stop_words WHERE is_added=1 AND word = "chinese";

Konfigurasi stop words kustom

Setelah membuat tabel stop word kustom, konfigurasikan parameter imci_fts_stop_word_table. Misalnya, test/my_stop_words, di mana test adalah nama database dan my_stop_words adalah nama tabel.

SET GLOBAL imci_fts_stop_word_table = 'test/my_stop_words';

Jika imci_fts_stop_word_table sudah diatur, Anda juga dapat mengatur imci_fts_stop_word_update ke ON untuk memicu pemuatan ulang stop words kustom yang ditentukan oleh imci_fts_stop_word_table.

SET GLOBAL imci_fts_stop_word_update = ON;