Início / dba / Perguntas / 125401
Accepted
Alon Eitan
Asked: 2016-01-07 08:10:16 +0800 CST

Integre pesquisas de texto completo do MySql no CMS

  • 772

Estou desenvolvendo meu próprio CMS e usando MySql v5.5 DB para salvar os registros de itens de conteúdo.

Devido a limitações técnicas, não posso atualizar meu banco de dados para uma versão mais recente que suporte pesquisas de texto completo em tabelas InnoDB.

Seria uma boa prática usar tabelas MyISAM para pesquisas textuais enquanto os dados "reais" são salvos no mecanismo de armazenamento InnoDB ?

Exemplo:

  1. O usuário cria o item de conteúdo e o salva no banco de dados
  2. Todos os campos de itens de conteúdo são salvos em uma tabela InnoDB .
  3. Todos os campos de texto são empilhados juntos e salvos em um único campo dentro de uma tabela MyISAM que também inclui um campo de ID adicional com referência ao item de conteúdo original.

Quando o usuário realiza uma pesquisa baseada em texto, vou consultar a tabela MyISAM usando uma consulta de pesquisa de texto completo que me dará referências a todos os registros correspondentes em minha tabela InnoDB .

Conclusão - isso é considerado uma boa solução em termos de desempenho e para melhorar os recursos de pesquisa do meu CMS, ou devo continuar com o bom e velho operador LIKE e consultar usando apenas a tabela InnoDB?

mysql innodb

2 respostas

  1. Best Answer

    Funciona. Tem um desempenho razoavelmente bom.

    Você tem duas tabelas, uma é InnoDB e contém a maioria dos atributos de uma entidade. A outra é MyISAM, é 1:1 com a primeira tabela, e contém um TEXTcampo, mais FULLTEXTíndice.

    A consulta relevante se parece com:

    SELECT ...
   FROM inno_tbl i
   JOIN ft_tbl f ON i.id = f.id
   WHERE i.stuff...
     AND MATCH (f.text) AGAINST (...);

    Acho que MATCHsempre ocorrerá primeiro, mesmo que os testes ipossam ser mais seletivos. Essa é a natureza de FULLTEXT.

    jkavalik mencionou alguns problemas de consistência; mas isso pode ser evitado escolhendo cuidadosamente a ordem em que você INSERTnas duas tabelas e se você usa REPLACEou IODKUem vez de uma planície INSERTpara um dos `INSERTs.

    (Acredito ter feito o que você descreve em um ou dois projetos. Desde então, medi que o InnoDB FULLTEXTparece ser mais rápido.)

    Resumindo: vá em frente e faça.

    Adendo Como ordenar as instruções para minimizar problemas de integridade de dados.

    BEGIN;
INSERT into InnoDB table
$id = SELECT LAST_INSERT_ID(); -- assuming you are using an AUTO_INCREMENT
INSERT INTO MyISAM_table
    (id, text)  VALUES  ($id, '$escaped_text')
    ON DUPLICATE KEY UPDATE
        text = '$escaped_text';
COMMIT;

    Casos...

    • Não há problema de integridade se ambos forem INSERTsbem-sucedidos ou ambos falharem.
    • Se houver um erro no MyISAM INSERT, você deve detectá-lo e, ROLLBACKem vez de COMMIT. Daí uma boa integridade.
    • Se o MyISAM for bem-sucedido, mas o COMMITfalhar, haverá uma linha extra na tabela MyISAM, para a qual não há linha InnoDB. Dois casos...

    Se uma FULLTEXTpesquisa atingir essa linha, a JOINtabela InnoDB falhará, obtendo assim a resposta 'certa' (a um custo menor).

    Se você vier mais tarde e reutilizá-lo idpara reinserir a linha (ou inserir uma linha diferente), o IODKU "fará a coisa certa". Tudo está bem.

    Observação (destinada a outros leitores): Essa técnica de misturar uma tabela InnoDB com uma tabela não transacional INSERTfunciona em outros casos. Considere colocar uma imagem (.jpg) em um arquivo, enquanto coloca os 'metadados' da imagem em uma linha do InnoDB. Na pior das hipóteses, você pode ter uma imagem extra ou duplicada armazenada no sistema de arquivos.

    • 3

  2. Vamos começar comparando entre MyISAM e InnoDB:

    Visão geral

    MyISAM :

    • Mais rápido que o InnoDB, pois a estrutura é mais simples e consome menos recursos.
    • Ele oferece suporte à indexação de texto completo melhor que o InnoDB.
    • Bom para mesa de leitura intensiva.
    • Simplicidade

    InnoDB :

    • Integridade de dados e restrições de chave estrangeira.

    • O InnoDB é mais resistente à corrupção de tabelas do que o MyISAM.

    • Suporte para grande buffer pool para dados e índices.

    • Bloqueio em nível de linha.

    Desvantagens ou limitações

    MyISAMGenericName

    • Integridade de dados deficiente (sem chaves estrangeiras e exclusões/atualizações em cascata)
    • Recuperação de falha ruim.
    • Bloqueio de tabela
    • Limite de linhas de 4.284.867.296 linhas (232)
    • Máximo de 64 índices por linha

    InnoDB :

    • Sem indexação de texto completo (versão mysql abaixo de 5.6)
    • Maior complexidade.
    • Desempenho mais lento.

    Quando devo usar o MySIAM? Se a aplicação é simples e precisa ser rápida. No entanto, não deve ser usado quando a integridade dos dados for uma prioridade.

    Quando devo usar o InnoDB? O InnoDB é a melhor opção se você precisar criar um aplicativo da Web baseado em dados confiável.

    Compare entre os índices MyISAM e InnoDB FULLTEXT e consultas LIKE para pesquisa de texto completo, você pode ler o exemplo a seguir .

    P: Seria uma boa prática usar tabelas MyISAM para pesquisas textuais enquanto os dados "reais" são salvos no mecanismo de armazenamento InnoDB?

    R: sim, geralmente é feito por meio de texto completo para o InnoDB

    Solução alternativa de pesquisa de texto completo do InnoDB:

    Você pode fazer algum tipo de sincronização de dados usando gatilhos (se sua versão do mysql os suportar). Eles permitem que você execute pequenos trechos de SQL em determinados pontos, como depois que os dados são inseridos ou excluídos de uma tabela.

    create trigger TRIGGER_NAME after insert on INNODB_TABLE
insert into MYISAM_TABLE select * from INNODB_TABLE
where id = last_insert_id();

    Sempre que dados são inseridos na tabela INNODB, os mesmos dados são inseridos automaticamente na tabela MYISAM.

    Referências:

    Link adicional caso você queira ler a atualização para o MySQL 5.6: https://www.percona.com/blog/2013/07/31/innodb-full-text-search-in-mysql-5-6-part- 3/

    • 3

relate perguntas