PolarDB:Guia do usuário de índices de texto completo do IMCI

O PolarDB IMCI permite criar, modificar ou excluir um índice invertido ao especificar um comment de coluna durante a criação de uma tabela ou ao modificar o comment de uma coluna por meio de DDL.

1. Crie um índice de texto completo

Crie um índice de texto completo em cada coluna que exija recuperação de texto e selecione um tokenizador com base nos requisitos do seu negócio.

1. Crie uma tabela de teste: crie uma tabela que contenha um índice colunar.

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

2. (Opcional) Modifique o índice de texto completo: use a instrução ALTER TABLE para adicionar um índice de texto completo à coluna title e especifique o modo de mecanismo de pesquisa do tokenizador jieba.

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

3. (Opcional) Verifique o resultado da tokenização: antes de selecionar um tokenizador, use a função dbms_imci.fts_tokenize para visualizar como diferentes tokenizadores processam o texto.

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

2. Execute consultas de pesquisa de texto completo

Após a criação do índice, o IMCI o constrói em segundo plano. Após a conclusão da construção, execute consultas de texto usando MATCH...AGAINST ou instruções LIKE otimizadas.

1. Consulte usando MATCH...AGAINST:

   -- Insert sample data
   INSERT INTO t1 VALUES 
   (16, 'polarDB full-text index feature title'), 
   (17, 'Database title performance tuning');
   -- Query records whose title column contains "index"
   SELECT * FROM t1 WHERE MATCH(title) AGAINST("title");

   Use EXPLAIN para visualizar o plano de execução. O operador FtsTableScan indica que a consulta atingiu o índice de texto completo.

   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 indica que o sistema usa automaticamente LIKE para realizar uma varredura suplementar em dados incrementais ainda não indexados, garantindo a integridade dos resultados.

2. Acelere consultas LIKE: para manter a compatibilidade com o código de negócio existente, o IMCI converte automaticamente consultas LIKE específicas em MATCH...AGAINST para acelerar o processo.

   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%")             |
   +----+------------------------+------+-----------------------------------------------------------------+

   O plano de execução também exibe o operador FtsTableScan, confirmando a aceleração da consulta LIKE.

3. Conversão de match para like: quando não há índice de texto completo disponível, o IMCI pode converter MATCH em LIKE para garantir a execução correta da consulta, utilizando ao máximo os índices existentes. O plano de execução mostra uma varredura completa da tabela.

   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. Gerencie e monitore índices

Consulte o status de construção e os metadados de um índice. Exclua o índice quando não precisar mais dele.

1. Monitore o progresso e o status da construção do índice:

   -- View all inverted indexes
   SHOW imci indexes fulltext;
   SELECT * FROM information_schema.imci_fts_indexes;
   
   -- View a specified inverted index
   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]';
   
   -- View the metadata of a specified inverted index
   SELECT * FROM information_schema.imci_fts_index_metas 
   WHERE schema_name='[db_name]' AND table_name='[table_name]' AND column_name='[column_name]';
   
   -- View the segment data of a specified inverted index
   SELECT * FROM information_schema.imci_fts_index_segs 
   WHERE schema_name='[db_name]' AND table_name='[table_name]' AND column_name='[column_name]';
   
   -- View the columnar data of a specified inverted index
   SELECT * FROM information_schema.imci_fts_index_packs 
   WHERE schema_name='[db_name]' AND table_name='[table_name]' AND column_name='[column_name]';

2. Exclua um índice de texto completo: caso não precise mais do índice de texto completo em uma coluna, modifique o COMMENT da coluna para excluí-lo.

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

4. Parâmetros de configuração principais

Crie uma tabela de dicionário personalizado

1. Primeiro, defina uma tabela com a mesma estrutura da tabela my_fts_dict. Os nomes das colunas devem ser idênticos aos da tabela my_fts_dict.

   • type: o tipo de tokenizador. Há suporte apenas para os tokenizadores jieba (tipo 2) e ik (tipo 3).

   • word: a palavra da entidade.

   • is_added: adiciona ao (valor 1) ou remove do (valor 2) dicionário personalizado.

   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. O dicionário personalizado é uma tabela InnoDB. As operações de leitura e gravação são iguais às de uma tabela comum.

   1. Adicione uma entrada:

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

   2. Remova uma entrada:

      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";

Configure um dicionário personalizado

• Após criar a tabela de dicionário personalizado, configure o valor da opção imci_fts_user_dict_table. Por exemplo, test/my_fts_dict'.

  SET GLOBAL imci_fts_user_dict_table = 'test/my_fts_dict';

• Se imci_fts_user_dict_table já estiver definido, defina imci_fts_user_dict_update como ON para acionar o recarregamento do dicionário personalizado especificado por imci_fts_user_dict_table.

  SET GLOBAL imci_fts_user_dict_update = ON;

  Nota

  Um novo dicionário personalizado entra em vigor apenas para índices invertidos construídos posteriormente. Para aplicá-lo a um índice invertido já construído, reconstrua o índice.

Crie uma tabela de sinônimos personalizados

Primeiro, defina uma tabela com a mesma estrutura da tabela my_synonym_dict. Os nomes das colunas devem ser idênticos aos da tabela my_synonym_dict.

• word: a lista de sinônimos. Separe palavras diferentes com vírgulas.

• type: o tipo de sinônimo.

  • 0 indica que todas as palavras na lista são sinônimas entre si. Por exemplo, "db,database,polardb" significa "db" <=> "database" <=> "polardb". Ou seja, ao pesquisar por "db", "database" ou "polardb", o sistema retorna todos os dados correspondentes a "db", "database" ou "polardb".

  • 1 indica que a primeira palavra na lista é o termo principal e as demais são seus sinônimos. Por exemplo, "db,database,polardb" significa "db" => "database", "polardb". Ou seja, ao pesquisar por "db", o sistema retorna todos os dados correspondentes a "db", "database" ou "polardb". No entanto, ao pesquisar por "database", apenas os dados correspondentes a "database" são retornados.

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';

O dicionário personalizado é uma tabela InnoDB. As operações de leitura e gravação são iguais às de uma tabela comum.

• Adicione uma entrada:

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

• Remova uma entrada:

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

Configure sinônimos personalizados

Após criar a tabela de sinônimos personalizados, configure o valor da opção imci_fts_synonym_table. Por exemplo, test/my_synonym_dict, em que test é o nome do banco de dados e my_synonym_dict é o nome da tabela.

SET GLOBAL imci_fts_synonym_table = 'test/my_synonym_dict';

Se imci_fts_synonym_table já estiver definido, defina imci_fts_synonym_update como ON para acionar o recarregamento dos sinônimos personalizados especificados por imci_fts_synonym_table.

SET GLOBAL imci_fts_synonym_update = ON;

Crie uma tabela de palavras de parada personalizadas

Primeiro, defina uma tabela com a mesma estrutura da tabela my_stop_words. Os nomes das colunas devem ser idênticos aos de my_stop_words.

• word: a palavra de parada.

• is_added: adiciona ao (valor 1) ou remove do (valor 0) dicionário personalizado.

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';

O dicionário personalizado é uma tabela InnoDB. As operações de leitura e gravação são iguais às de uma tabela comum.

• Adicione uma entrada:

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

• Remova uma entrada:

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

Configure palavras de parada personalizadas

Após criar a tabela de palavras de parada personalizadas, configure o valor da opção imci_fts_stop_word_table. Por exemplo, test/my_stop_words, em que test é o nome do banco de dados e my_stop_words é o nome da tabela.

SET GLOBAL imci_fts_stop_word_table = 'test/my_stop_words';

Se imci_fts_stop_word_table já estiver definido, defina imci_fts_stop_word_update como ON para acionar o recarregamento das palavras de parada personalizadas especificadas por imci_fts_stop_word_table.

SET GLOBAL imci_fts_stop_word_update = ON;