Boas técnicas para acelerar a execução do banco de dados

4

Eu tenho um aplicativo ASP.Net que está usando um banco de dados MySQL. Minhas consultas não estão sendo executadas tão rápido quanto eu gostaria também. Existem algumas maneiras padrão de aumentar a velocidade de um banco de dados MySQL antes de colocá-lo em um PC mais rápido.

Por exemplo, (não tenho certeza se são verdadeiras ou não) usando instruções preparadas sobre SQL transacional. Usando chaves estrangeiras, se aplicável? Coisas nesse sentido.

EDITAR: O banco de dados está em uma máquina remota

    
por AngryBird 07.11.2011 / 21:27
fonte

5 respostas

10

O que você quer fazer é aprender a fazer o perfil de suas consultas usando o comando MySQL EXPLAIN ( doc ). Isso lhe dirá como a consulta está extraindo dados do banco de dados - seja suas linhas de varredura uma por uma ou usando índices. Usando isso, muitas vezes você pode acelerar drasticamente as consultas. Claro, se você não estiver usando índices, esse seria o primeiro lugar para começar ...

O efeito de declarações preparadas vs não preparadas ... isso é geralmente mínimo. Você também desejará criar um perfil disso em seu código, mas somente depois de otimizar os índices com base no que aprendeu na execução de EXPLAIN .

    
por 07.11.2011 / 21:35
fonte
4

Acelerar um banco de dados é um assunto importante. Você também precisa nos informar se tem problemas ao consultar o banco de dados, atualizar ou excluir dados. No entanto, existem fatos conhecidos que você pode verificar:

0 - Verifique se você está usando o driver apropriado para se conectar ao banco de dados e se está usando a maneira correta de se conectar (por exemplo, usar o ODBC pode ser mais lento que um driver nativo). Use pools de conexão.

1 - Tenha um design correto.

2 - Tenha o PK e o FK definidos com o mesmo tipo de dados para acelerar as junções.

3 - Crie índices em PK e FK para tabelas de tamanhos não triviais

4 - Escolha o tipo certo de índices

5 - Otimize seus seleções. Evite "SELECT *" e não junte-se a tabelas, a menos que você precise

6 - Qualifique seus selects adequadamente para que o número de linhas de dados retornadas seja suficiente para realizar a função de negócios. Não devolva todos os dados o tempo todo, a menos que você precise.

7 - Evite usar objetos binários grandes em consultas. Considere a possibilidade de remover fotos do banco de dados para o armazenamento do sistema de arquivos, se possível.

8 - Use funções agregadas e ORDER BY com sabedoria. Escolha seu índice de armazenamento em cluster para evitar classificações, se possível.

8a - Evite usar Não em WHERE e tente evitar operações de transformação complexas.

8b - Certifique-se de que índices são usados em suas consultas, ou ajuste as consultas para utilizar índices ou crie os índices necessários.

9 - Use uma única coluna para criar a chave em vez de várias colunas, quando possível.

10 - Verifique seu design físico de tabelas e índices. Veja como o seu espaço está alocado

11 - Considere a reconstrução do índice e a desfragmentação do sistema de arquivos

12 - Verifique as estratégias para a pesquisa de texto completo (se você estiver usando) - veja: FTS

13 - Determine se a velocidade da sua rede é boa o suficiente.

14 - Compare o tempo de transação em seu aplicativo ASP.NET versus a mesma consulta ou transação quando executado em um console. A diferença deve estar perto. Se você encontrar uma grande variação, o problema pode estar em como você se conecta, na rede ou em algum outro problema.

    
por 08.11.2011 / 08:54
fonte
3

O banco de dados escreve o único local onde você pode acelerar o seu programa. Sem mais informações sobre seu caso de uso, é possível usar uma topologia de um cache write-behind?

Um cache write-behind permitirá que você coloque elementos em um cache para serem gravados em um banco de dados posteriormente. Para entradas de banco de dados que não têm dependências adicionais (como serem lidas do banco de dados dentro de um período de tempo definido), isso seria ideal, uma vez que um elemento esteja no cache, ele estará no banco de dados no que diz respeito ao seu aplicativo.

Este é o ehcache sobre como implementar um registro por trás do cache e também vinculei a um vídeo decente apresentando como um cache write-behind pode remover um gargalo do banco de dados de um aplicativo. link

    
por 07.11.2011 / 22:00
fonte
1

Algumas coisas que geralmente podem estar acontecendo aqui:

  1. As consultas são lentas. Execute a consulta diretamente na instância do MySql. Hospede rápido? Se demorar alguns segundos, o ajuste da consulta é necessário com índices adicionais, etc.
  2. Existem muitos dados na rede. A consulta pode estar sendo executada instantaneamente, mas a quantidade de dados que chega pelo fio é muito grande. Retornar menos dados ou voltar os dados para o cliente seria alguma sugestão (s).
  3. O servidor está realmente ocupado. Qual é a aparência da CPU e da memória no servidor? É perto de 100%?

As instruções preparadas não fazem muita diferença até que o servidor tenha um grande volume de consultas. A velocidade preparada versus despreparada é praticamente a mesma, mas com instruções preparadas, o servidor não precisa trabalhar tanto, então pode usar mais recursos para fazer outras coisas.

    
por 07.11.2011 / 23:06
fonte
1

A primeira coisa a considerar é se sua indexação está correta. São suas consultas sargable (ou seja, eles podem usar o índice, se existir)? Seus campos de chave estrangeira são indexados?

Em seguida, você está fazendo as coisas de maneira baseada em conjuntos ou fazendo um loop por meio de registros que geralmente são mais lentos em um banco de dados.

Você está usando técnicas conhecidas de matança de desempenho (no sql server isto incluiria o uso de subconsultas correlacionadas, cursores, UDFs escalares, funções em junções etc., não tendo certeza de quais são os killers conhecidos no MySQL, mas há livros disponíveis) tenho certeza sobre o assunto.

Você está usando select * e retornando mais dados do que o necessário, especialmente em associações nas quais pelo menos os campos de junção são repetidos.

    
por 07.11.2011 / 23:15
fonte