Desempenho do índice de texto completo do MySQL

Resumo : neste tutorial, você aprenderá como otimizar o desempenho do índice de texto completo do MySQL durante a criação de índice e operações de pesquisa.

O índice de texto completo do MySQL permite realizar pesquisas eficientes baseadas em texto em textos grandes. Se quiser obter o desempenho ideal, você precisa entender como criar e utilizar índices de texto completo de maneira eficaz.

Criação de índices de texto completo

1) Escolha o mecanismo de armazenamento de tabelas certo

MySQL suporta índice de texto completo para tabelas MyISAM e InnoDB .

MyISAM é tradicionalmente mais rápido para operações de leitura pesada, enquanto o InnoDB fornece melhor suporte transacional geral.

Dependendo do seu caso de uso, você pode escolher o mecanismo que se alinha aos seus requisitos de desempenho:

ALTER TABLE your_table
    ADD FULLTEXT index_name (column1, column2);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

2) Otimize para InnoDB

Se você usar tabelas InnoDB, considere ajustar a innodb_ft_sort_pll_degreevariável para controlar o grau de paralelismo da operação de classificação durante a criação do índice. Você pode experimentar valores diferentes para encontrar a configuração ideal para sua carga de trabalho específica.

-- Example: Setting the parallelism degree
SET GLOBAL innodb_ft_sort_pll_degree = 4;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

3) Indexação em lote

Se você tiver grandes conjuntos de dados, considere dividir o processo de indexação em lotes menores. Isso pode ajudar a gerenciar os recursos do sistema com mais eficiência e evitar possíveis tempos limite:

CREATE FULLTEXT INDEX index_name
    ON your_table (column1, column2)
    WITH PARSER ngram;

Melhorando o desempenho da pesquisa de texto completo

1) Otimize a estrutura da consulta

Ao realizar uma pesquisa de texto completo, você deve criar suas consultas que utilizem prefixos e pesquisas de frase para obter melhores resultados:

SELECT * FROM your_table
    WHERE MATCH(column1, column2) 
    AGAINST ('"your keyword"' IN BOOLEAN MODE);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

2) Use pesquisas booleanas de texto completo

O modo BOOLEAN permite usar operadores como AND, OR e NOT. Ajuda você a ter consultas de pesquisa mais precisas e flexíveis.

-- Example: Boolean full-text search
SELECT * FROM your_table
    WHERE MATCH(column1, column2) 
    AGAINST ('+keyword1 -keyword2' IN BOOLEAN MODE);Linguagem de código:  JavaScript  ( javascript )

3) Ajuste o comprimento mínimo da palavra

Modifique a ft_min_word_lenvariável de configuração para controlar o comprimento mínimo da palavra considerado no índice de texto completo. Valores menores podem resultar em resultados de pesquisa mais relevantes.

-- Example: Setting minimum word length
SET GLOBAL ft_min_word_len = 3;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

4) Monitore e otimize o tamanho do índice

Você deve monitorar regularmente o tamanho dos índices de texto completo porque índices grandes podem afetar o desempenho da pesquisa.

Para verificar o tamanho do índice, você pode usar a SHOW TABLE STATUSinstrução. Por exemplo, a instrução a seguir exibe os tamanhos dos índices da poststabela:

SHOW TABLE STATUS LIKE 'posts'\GLinguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

*************************** 1. row ***************************
           Name: posts
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 10
 Avg_row_length: 1638
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 11
    Create_time: 2023-11-25 11:10:23
    Update_time: 2023-11-25 11:10:28
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)Linguagem de código:  CSS  ( css )

Às vezes, você deve considerar reconstruir os índices periodicamente ou ajustar a ft_max_word_lenvariável de configuração:

SET GLOBAL ft_max_word_len = 20;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Resumo

• Crie um índice de texto completo nas colunas corretamente e ajuste as variáveis ​​de configuração para aprimorar o desempenho das pesquisas de texto completo.
• Forme consultas de pesquisa de texto completo que usem efetivamente o índice de texto completo para melhorar o desempenho da pesquisa.