Início / dba / Perguntas / 16284
Accepted
Jérôme Verstrynge
Asked: 2012-04-10 16:27:47 +0800 CST

Quão caras são as instruções SELECT FROM LIKE do ponto de vista do design?

  • 772

Tenho uma situação fora de controle em relação a um número cada vez maior de pequenos arquivos estáticos que preciso acessar aleatoriamente em uma grande estrutura de diretórios. DEVO reduzir drasticamente o número desses arquivos muito em breve. Estou investigando soluções rápidas para liberar a pressão.

Uma opção é mover o conteúdo do arquivo (texto UTF8) para um banco de dados e executar SELECTs para substituir a pesquisa do arquivo (por nome). As instruções selecionadas seriam as seguintes:

SELECT TOP(1) MyContent FROM MyTable WHERE MyContentName LIKE 'criteria%';
SELECT TOP(1) MyContent FROM MyTable WHERE MyContentName LIKE '%othercriteria';
SELECT TOP(1) MyContent FROM MyTable WHERE MyContentName LIKE '%andanothercriteria%';

Estamos falando de um total de 200 mil solicitações por dia em uma tabela de 800 mil linhas (que eu poderia facilmente dividir em duas se ajudar). MyContentNamefaz parte da chave e será indexado. Existe uma entrada que corresponde aos critérios na tabela ou nenhuma.

Não sou especialista em administrador de banco de dados. Isso é algo que uma instância do MySQL em um servidor compartilhado pode suportar ou minhas expectativas são muito altas?

Eu sei que a resposta típica é: eu deveria testar. Infelizmente, por causa da emergência, não tenho tempo para fazer exames. Preciso encontrar uma solução rápida, mesmo que temporária, mesmo que prejudique um pouco a demora na resposta do serviço.

Estou procurando a opinião de um administrador de banco de dados experiente sobre essa estratégia. Dicas e sugestões também são bem vindas.

mysql performance

2 respostas

  1. Best Answer

    Se você não conseguir ajustar o sistema de arquivos (por exemplo, usando um tamanho de bloco menor) e realmente precisar usar um banco de dados, sugiro as seguintes leituras:

    A primeira explicará a estrutura de dados mais comumente utilizada pelos índices, a B-Tree. A segunda explica como o MySQL usa o B-Tree. O terceiro falará sobre o comando EXPLAIN SELECT ..., que é como o MySQL descreve o plano de consulta (ele informará qual índice (se houver) que está usando, se estiver fazendo varreduras de tabela -- o que você deve evitar a todo custo).

    Para criar um índice otimizado, você deve primeiro pensar sobre a estrutura da consulta (ou consultas) de que precisará. Por exemplo, pode ser algo como: select content from files where firstParameter = XXX and secondParameter like 'xxx%'.

    Você deve analisar a cardinalidade de cada coluna (ou seja, quantos valores diferentes a coluna pode ter).

    Você escolheu as colunas com maior cardinalidade para serem as primeiras em um índice, deixando aquelas com menor cardinalidade para o final. Exemplo: suponha que você tenha 2M linhas e firstParameterseja um número entre 1 e 1M, distribuído aleatoriamente, e secondParameterseja o nome completo do proprietário do arquivo. Nessa situação, você quer o index (firstParameter, secondParameter), nessa mesma ordem, já que a cláusula firstParameter = XXXvai te deixar, em média, com apenas 2 linhas. A cardinalidade de secondParameter, por outro lado, é muito menor: há muito menos do que 1 milhão de possibilidades para nomes de pessoas. Portanto, se o seu índice for (secondParameter, firstParameter), a consulta where firstParameter = 1 and secondParameter like 'bruno%'primeiro procurará cada linha em que secondParametercomeça com bruno(que provavelmente serão dezenas ou centenas de milhares) e só então procurará a outra condição.

    Além disso, observe que o índice é usado da esquerda para a direita. Ou seja, se você tiver 3 colunas, Ae Bvocê Cindexar (A, B, C), o índice será praticamente inútil em uma consulta como where A = 1 and C = 2. Ele provavelmente será usado para encontrar linhas correspondentes A = 1, mas depois disso cada linha será verificada C = 2. Se a maioria de suas consultas for como essa (e algumas podem especificar B também), seu índice deve ser (A, C, B).

    Por fim, observe que like 'xxx%'pode usar um índice, enquanto like '%xxx'(ou like '%xxx%') não pode. Novamente, isso ocorre porque o índice é lido da esquerda para a direita. Para corresponder xxx%, ele sabe por onde começar a procurar; para corresponder %xxx, deve verificar cada linha.

    Tudo o que disse sobre índices, eu sugiro fortemente que você retrabalhe seus critérios para que você tenha algo mais estruturado. Como você disse, você pode tentar pré-computar algo.

    Há outras considerações, como o tamanho do conteúdo. Se você puder fazer com que caiba abaixo de 8 KB (o que equivale a 3.000 caracteres se você usar UTF-8), o InnoDB armazenará os dados na mesma página que a chave primária; caso contrário, ele armazenará os dados em outro lugar. Se você consultar por chave primária, no primeiro caso terá uma única operação de leitura; se você consultar por outro índice, no segundo caso você tem 3 operações de leitura: uma para encontrar a chave primária da linha correspondente, uma para encontrar a linha pela chave primária (para ler o endereço dos dados) e uma para ler os dados .

    Ah, verifique a quantidade de RAM do seu servidor. Idealmente, seus dados (ou pelo menos seus índices) devem caber na RAM.

    Considerando todos esses pontos, você não deve ter nenhum problema: não conheço o hardware do seu servidor ou sua carga (já que você disse que é compartilhado), mas 800k linhas é quase nada se você ajustar seus índices ; Estou muito longe de ser um especialista e, fazendo todas as coisas acima, trabalho diariamente com tabelas (muito otimizadas) com 10M, algumas linhas de 100M, e as consultas são ultrarrápidas.

    Espero que ajude. Depois de ter sua(s) tabela(s), você pode fazer outra pergunta mostrando a create tabledeclaração e descrevendo um pouco sobre seus dados (tamanhos, cardinalidades, etc) e as consultas de seleção que você usará, para que alguém possa ajudá-lo a criar um índice otimizado.

    • 6

  2. Sugiro que você use MyISAMtable e adicione FULLTEXTINDEX se estiver observando lentidão. A pesquisa de texto completoéo tipo de pesquisa baseada num tipo especial de índice (texto completo, obviamente), O desempenhoéóptimo neste caso Enquanto que %% provoca sempre uma pesquisa completa da tabela que pode ser terrivelmente lenta (quando tem 100k e mais linhas).

    Você pode consultar este link: http://www.gammelsaeter.com/programming/mysql-fulltext-search-example/

    • 2

relate perguntas