PolarDB:IMCI full-text index

Requisitos de versão

Pré-requisitos

Antes de começar, confirme que você possui:

• Um cluster PolarDB for MySQL compatível com as versões listadas acima

• Uma tabela com o column store ativado (COMMENT 'columnar=1')

• A variável de sistema imci_enable_fts_query definida como ON na sessão em que as consultas serão executadas

Escolha um tokenizer

Selecione o tokenizer conforme o tipo de dado e o padrão de busca desejado. Cada tokenizer segmenta o texto de forma distinta e atende a diferentes cenários de consulta.

Não sabe qual escolher? Visualize como cada tokenizer processa seu texto antes de criar o índice:

CALL dbms_imci.fts_tokenize("I am PolarDB");
-- Result: ["i", "am", "polardb"]  (default: token, type=0)

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

CALL dbms_imci.fts_tokenize("I am PolarDB", "type=2");
-- Result: ["PolarDB"]  (jieba, accurate mode)

CALL dbms_imci.fts_tokenize("I am PolarDB", "type=2,mode=1");
-- Result: ["polardb"]  (jieba, full mode)

Crie um índice de texto completo

Defina o COMMENT no nível da tabela como columnar=1 e o COMMENT no nível da coluna como imci_fts(type=VALUE) para configurar um índice invertido nessa coluna.

Crie o índice durante a criação da tabela:

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

Adicione ou modifique um índice em uma coluna existente:

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

Após a conclusão do comando ALTER, o IMCI constrói o índice em segundo plano. Execute consultas somente após o término da construção — acompanhe o progresso pelos comandos de monitoramento descritos em Monitorar status de construção do índice.

Remova um índice:

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

Execute consultas de texto completo

Ative o suporte a consultas de texto completo na sessão antes de executar as consultas:

SET imci_enable_fts_query = ON;

-- Insert sample data
INSERT INTO t1 VALUES
    (16, 'polarDB full-text index feature title'),
    (17, 'database title performance optimization');

-- Find rows where the title column contains "title"
SELECT * FROM t1 WHERE MATCH(title) AGAINST("title");

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

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

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

Monitore o status de construção do índice

Após criar ou modificar um índice de texto completo, acompanhe o progresso da construção com estes comandos:

-- List all inverted indexes
SHOW imci indexes fulltext;
SELECT * FROM information_schema.imci_fts_indexes;

-- Filter to a specific table
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 index metadata
SELECT * FROM information_schema.imci_fts_index_metas
    WHERE schema_name = '[db_name]' AND table_name = '[table_name]' AND column_name = '[column_name]';

-- View segment data
SELECT * FROM information_schema.imci_fts_index_segs
    WHERE schema_name = '[db_name]' AND table_name = '[table_name]' AND column_name = '[column_name]';

-- View column store data
SELECT * FROM information_schema.imci_fts_index_packs
    WHERE schema_name = '[db_name]' AND table_name = '[table_name]' AND column_name = '[column_name]';

Variáveis de sistema

Essas variáveis de sistema controlam o comportamento global de construção de índices e a conversão de consultas. Defina as variáveis globais no console do PolarDB — elas não podem ser alteradas pela linha de comando. Já as variáveis com escopo de sessão podem ser configuradas por conexão.

FAQ

Minha consulta não está usando o índice de texto completo. O que houve?

Na maioria dos casos, isso acontece porque imci_enable_fts_query ainda está definido como OFF. Execute SET imci_enable_fts_query = ON; e tente novamente.

Se o índice continuar sem ser utilizado, verifique a saída do EXPLAIN. Caso FtsTableScan não apareça, o padrão da consulta pode não corresponder ao índice, ou o otimizador de consultas optou por uma varredura completa da tabela devido ao custo estimado menor.

Qual tokenizer devo usar?

Comece com o padrão type=0 (token) para textos em inglês ou estruturados. Para conteúdo em chinês, utilize type=2 (jieba) no modo preciso para busca semântica, ou type=3 (IK Analyzer) como alternativa. Para colunas JSON, prefira type=4 (json) com o parâmetro expr para especificar o JSONPath a ser indexado.

Para validar sua escolha antes de criar o índice, visualize os resultados da tokenização com dbms_imci.fts_tokenize. Consulte Escolha um tokenizer.

Qual é a diferença entre LIKE e MATCH...AGAINST?

O uso de LIKE '%keyword%' sem um índice de texto completo provoca uma varredura completa da tabela. Já MATCH...AGAINST aproveita o índice invertido para buscas rápidas, além de oferecer suporte a consultas booleanas e pontuação de relevância. Para novas aplicações, use MATCH...AGAINST diretamente. Se o código existente já utilizar LIKE, ative imci_convert_like_to_match para obter aceleração por índice sem precisar reescrever as consultas.

Próximos passos

• Análise dos recursos de recuperação de texto completo em índices columnstore