PolarDB:Guía de uso del índice de texto completo de IMCI

PolarDB IMCI le permite crear, modificar o eliminar un índice invertido. Para ello, especifique un comment de columna al crear una tabla, o bien modifique el comment de la columna mediante DDL.

1. Crear un índice de texto completo

Cree un índice de texto completo en cada columna que requiera recuperación de texto y seleccione un tokenizer según sus necesidades de negocio.

1. Crear una tabla de prueba: cree una tabla que contenga un índice columnar.

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

2. (Opcional) Modificar el índice de texto completo: utilice la instrucción ALTER TABLE para agregar un índice de texto completo a la columna title y especifique el modo de motor de búsqueda del tokenizer jieba.

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

3. (Opcional) Comprobar el resultado de la tokenización: antes de seleccionar un tokenizer, use la función dbms_imci.fts_tokenize para previsualizar cómo procesan el texto los distintos tokenizers.

   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. Ejecutar consultas de búsqueda de texto completo

Una vez creado el índice, IMCI lo compila en segundo plano. Cuando finaliza la compilación, ejecute consultas de texto mediante instrucciones MATCH...AGAINST o LIKE optimizadas.

1. Consultar con 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");

   Utilice EXPLAIN para consultar el plan de ejecución. Ahí observará el operador FtsTableScan, que indica que la consulta utilizó el í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, para los datos incrementales aún no indexados, el sistema utiliza automáticamente LIKE para ejecutar un escaneo complementario. Esto garantiza que los resultados sean completos.

2. Acelerar consultas LIKE: para mantener la compatibilidad con el código de negocio existente, IMCI convierte automáticamente ciertas consultas LIKE en MATCH...AGAINST y así acelerarlas.

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

   El plan de ejecución también muestra el operador FtsTableScan, lo cual confirma que la consulta LIKE se aceleró.

3. Conversión de match a like: cuando no hay ningún índice de texto completo disponible, IMCI convierte MATCH en LIKE para que la consulta se ejecute correctamente y aproveche al máximo los índices existentes. El plan de ejecución muestra un escaneo completo de la tabla.

   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. Gestionar y supervisar índices

Consulte el estado de compilación y los metadatos de un índice, y elimínelo cuando ya no lo necesite.

1. Supervise el progreso y el estado de compilación del í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. Eliminar un índice de texto completo: si ya no necesita el índice de texto completo de una columna, modifique el COMMENT de dicha columna para eliminarlo.

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

4. Parámetros de configuración principales

Crear una tabla de diccionario personalizado

1. Primero, defina una tabla con la misma estructura que la tabla my_fts_dict. Los nombres de las columnas deben coincidir con los de dicha tabla.

   • type: el tipo de tokenizer. Solo se admiten el tokenizer jieba (tipo 2) y el tokenizer ik (tipo 3).

   • word: la palabra de la entidad.

   • is_added: agrega al diccionario personalizado (valor 1) o lo quita de este (valor 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. El diccionario personalizado es una tabla InnoDB. Las operaciones de lectura y escritura son iguales a las de una tabla normal.

   1. Agregar una entrada:

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

   2. Quitar una 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";

Configurar un diccionario personalizado

• Después de crear la tabla del diccionario personalizado, configure el valor de la opción imci_fts_user_dict_table. Por ejemplo, test/my_fts_dict'.

  SET GLOBAL imci_fts_user_dict_table = 'test/my_fts_dict';

• Si ya se estableció imci_fts_user_dict_table, también puede establecer imci_fts_user_dict_update en ON para activar la recarga del diccionario personalizado indicado en imci_fts_user_dict_table.

  SET GLOBAL imci_fts_user_dict_update = ON;

  Nota

  Un diccionario personalizado nuevo solo surte efecto en los índices invertidos que se compilen después. Para aplicarlo a un índice invertido ya compilado, vuelva a compilar el índice.

Crear una tabla de sinónimos personalizados

Primero, defina una tabla con la misma estructura que la tabla my_synonym_dict. Los nombres de las columnas deben coincidir con los de my_synonym_dict.

• word: la lista de sinónimos. Separe las distintas palabras con comas.

• type: el tipo de sinónimo.

  • 0 indica que todas las palabras de la lista son sinónimas entre sí. Por ejemplo, "db,database,polardb" significa "db" <=> "database" <=> "polardb". Es decir, cuando busca "db", "database" o "polardb", se devuelven todos los datos que coinciden con "db", "database" o "polardb".

  • 1 indica que la primera palabra de la lista es el término principal y que las demás son sus sinónimos. Por ejemplo, "db,database,polardb" significa "db" => "database", "polardb". Es decir, cuando busca "db", se devuelven todos los datos que coinciden con "db", "database" o "polardb". Sin embargo, cuando busca "database", solo se devuelven los datos que coinciden con "database".

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

El diccionario personalizado es una tabla InnoDB. Las operaciones de lectura y escritura son iguales a las de una tabla normal.

• Agregar una entrada:

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

• Quitar una entrada:

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

Configurar sinónimos personalizados

Después de crear la tabla de sinónimos personalizados, configure el valor de la opción imci_fts_synonym_table. Por ejemplo, test/my_synonym_dict, donde test es el nombre de la base de datos y my_synonym_dict es el nombre de la tabla.

SET GLOBAL imci_fts_synonym_table = 'test/my_synonym_dict';

Si ya se estableció imci_fts_synonym_table, también puede establecer imci_fts_synonym_update en ON para activar la recarga de los sinónimos personalizados indicados en imci_fts_synonym_table.

SET GLOBAL imci_fts_synonym_update = ON;

Crear una tabla de palabras vacías personalizadas

Primero, defina una tabla con la misma estructura que la tabla my_stop_words. Los nombres de las columnas deben coincidir con los de my_stop_words.

• word: la palabra vacía.

• is_added: agrega al diccionario personalizado (valor 1) o lo quita de este (valor 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';

El diccionario personalizado es una tabla InnoDB. Las operaciones de lectura y escritura son iguales a las de una tabla normal.

• Agregar una entrada:

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

• Quitar una 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";

Configurar palabras vacías personalizadas

Después de crear la tabla de palabras vacías personalizadas, configure el valor de la opción imci_fts_stop_word_table. Por ejemplo, test/my_stop_words, donde test es el nombre de la base de datos y my_stop_words es el nombre de la tabla.

SET GLOBAL imci_fts_stop_word_table = 'test/my_stop_words';

Si ya se estableció imci_fts_stop_word_table, también puede establecer imci_fts_stop_word_update en ON para activar la recarga de las palabras vacías personalizadas indicadas en imci_fts_stop_word_table.

SET GLOBAL imci_fts_stop_word_update = ON;