Otimização da taxa de amostragem de dados

6.1 Introdução

Agora vamos passar da teoria para a prática.

Vivemos no mundo real e todos os produtos de software são criados para pessoas vivas. E essas pessoas vivas ficam muito incomodadas com sites que carregam lentamente e programas que ficam lentos.

E se uma consulta ao banco de dados demorar mais de um segundo, isso é inaceitável . Os usuários simplesmente não usarão um produto com páginas/funcionalidades tão lentas.

Mas muitas vezes, para exibir uma página, você precisa executar várias dezenas de consultas ao banco de dados. E se eles forem executados sequencialmente, você não terá mais um segundo limite, mas digamos 100ms por solicitação.

Aqui estão as 5 principais maneiras pelas quais os programadores aceleram as consultas ao banco de dados:

1. Adicionando índices a tabelas no banco de dados.
2. Reescrever e otimizar consultas.
3. Habilite (e configure) o cache no lado do banco de dados.
4. Ative o cache no lado do cliente.
5. Executando a desnormalização do banco de dados.

Você já está familiarizado com todas essas coisas em sua maior parte, portanto, o que se segue será apenas um conselho prático.

6.2 Índices

Não é segredo que trabalhar com um banco de dados ocupa a maior parte do trabalho de quase todos os sites. E é trabalhar com o banco de dados que costuma ser o gargalo dos aplicativos da web.

Neste artigo, gostaria de dar conselhos práticos sobre o uso do MySQL.

direi logo:

• este artigo é escrito sobre o MySQL, embora as coisas gerais provavelmente sejam verdadeiras para qualquer DBMS.
• tudo o que está escrito no artigo é meu ponto de vista pessoal e não é a verdade absoluta.
• conselhos não pretendem ser novos e são o resultado de uma generalização da literatura lida e da experiência pessoal.
• dentro da estrutura deste artigo, não abordarei questões de configuração do MySQL.

Os problemas ao usar o MySQL podem ser divididos nos três grupos a seguir (em ordem de importância):

1. Não uso ou uso indevido de índices.
2. Estrutura de banco de dados incorreta.
3. Consultas SQL incorretas \ abaixo do ideal.

Vamos dar uma olhada em cada um desses grupos.

Usando índices

Não usar ou fazer mau uso de índices é o que mais frequentemente atrasa as consultas. Para quem não conhece o mecanismo de funcionamento dos índices ou ainda não leu sobre isso no manual, aconselho fortemente a leitura.

Dicas para usar índices:

• Você não precisa indexar tudo . Muitas vezes, sem entender o significado, as pessoas simplesmente indexam todos os campos de uma tabela. Os índices aceleram as buscas, mas diminuem as inserções e atualizações de linhas, portanto, a escolha de cada índice deve ser significativa.
• Um dos principais parâmetros que caracteriza o índice é a seletividade, que é o número de elementos diferentes no índice. Não faz sentido indexar um campo que possui dois ou três valores possíveis. Haverá pouco benefício de tal índice.
• A escolha dos índices deve começar com uma análise de todas as consultas em uma determinada tabela. Muitas vezes, após essa análise, em vez de três ou quatro índices, você pode fazer um composto.
• Ao usar índices compostos, a ordem dos campos no índice é crítica.
• Não se esqueça de cobrir os índices. Se todos os dados em uma consulta puderem ser recuperados de um índice, o MySQL não acessará a tabela diretamente. Tais solicitações serão executadas muito rapidamente. Por exemplo, para uma consulta SELECT name FROM user WHERE login='test'com um índice (login, nome), o acesso à tabela não é necessário. Às vezes, faz sentido adicionar um campo adicional a um índice composto, o que fará com que o índice cubra e acelere as consultas.
• Para índices de linha, muitas vezes é suficiente indexar apenas parte da linha. Isso pode reduzir significativamente o tamanho do índice.
• Se %estiver no início, LIKE(SELECT * FROM table WHERE field LIKE '%test')os índices não serão usados.
• O índice FULLTEXT é usado apenas com a sintaxe MATCH ... AGAINST .

6.3 Estrutura do banco de dados

Um banco de dados bem projetado é a chave para um trabalho rápido e eficiente com o banco de dados. Por outro lado, um banco de dados mal projetado é sempre uma dor de cabeça para os desenvolvedores.

Dicas de design de banco de dados:

1. Use os menores tipos de dados possíveis. Quanto maior o tipo de dados, maior a tabela, mais acessos ao disco são necessários para obter os dados. Use um procedimento muito conveniente: SELECT * FROM table_name PROCEDURE ANALYSE();determinar os tipos de dados mínimos possíveis.
2. Observe as formas normais durante a fase de projeto. Freqüentemente, os programadores recorrem à desnormalização já neste estágio. No entanto, na maioria dos casos, no início do projeto, está longe de ser óbvio como isso pode resultar. Desnormalizar uma tabela é muito mais fácil do que sofrer com uma desnormalizada abaixo do ideal. E JOINàs vezes funciona mais rápido do que tabelas desnormalizadas incorretamente.
3. Não use NULLcolunas a menos que você precise delas conscientemente.

6.4 Consultas SQL.

Com a mesma frequência, há o desejo de reescrever todas as consultas no SQL nativo para que a consulta seja o mais rápida possível. Se você decidir fazer isso, aqui estão algumas dicas:

1. Evite solicitações em um loop. SQL é uma linguagem de conjuntos, e escrever consultas deve ser abordado não na linguagem de funções, mas na linguagem de conjuntos.
2. Evite *(asteriscos) nas consultas. Sinta-se à vontade para listar exatamente os campos que você escolher. Isso reduzirá a quantidade de dados buscados e enviados. Além disso, não se esqueça de cobrir os índices. Mesmo se você selecionar todos os campos da tabela, é melhor listá-los. Primeiro , melhora a legibilidade do código. Ao usar asteriscos, é impossível descobrir quais campos estão na tabela sem examiná-los. Em segundo lugar , hoje sua tabela tem cinco colunas INT , e um mês depois foram adicionados mais TEXT e BLOB , e o asterisco permaneceu como estava.
3. Quando paginado, para obter o número total de registros, use SQL_CALC_FOUND_ROWSe SELECT FOUND_ROWS();Quando usado SQL_CALC_FOUND_ROWS MySQL, armazena em cache o número de linhas selecionado (antes de LIMIT ser aplicado) e, quando usado, SELECT FOUND_ROWS()retorna apenas esse valor em cache sem precisar executar novamente a consulta.
4. Não esqueça que existe INSERTuma sintaxe para múltiplas inserções. Uma consulta executará uma ordem de grandeza mais rápida do que várias consultas em um loop.
5. Use LIMITonde você não precisa de todos os dados.
6. Use INSERT… ON DUPLICATE KEY UPDATE…no lugar de e INSERT/ou UPDATEapós a seleção e frequentemente no lugar de REPLACE.
7. Não se esqueça deste recurso incrível GROUP_CONCAT. Pode ajudar com consultas complexas.