Melhorando a performance de consultas MySQL/MongoDB
À medida que uma base de dados vai crescendo e a quantidade de registros for aumentando, consultas sobre a mesma podem se tornar mais lentas e levar à queda de desempenho de aplicações que as consomem. Neste texto procuro apresentar algumas soluções adotadas e dicas para resolver alguns problemas de performance em consultas de dados que tivemos no Elliot.
Temos uma página de intranet utilizada pelos operadores para consultar informações relativas aos clientes e transações financeiras. Essa página consome dados de duas bases diferentes: uma em MongoDB e outra com o SGBD MySQL, utilizando o ORM Entity Framework com consultas em LINQ. Em algumas das pesquisas realizadas, são mostradas tabelas que cruzam informações dessas duas bases. O constante aumento da quantidade de registros fez com que as consultas às bases ficassem mais lentas e impactassem no trabalho dos operadores. Para solucionar o problema foram feitas algumas alterações apresentadas a seguir.
Revisão dos índices utilizados nas tabelas
Os índices são estruturas opcionais associadas à tabela, organizadas mediante alguma propriedade configurada, e ordenando os registros segundo algum critério. A utilização dos mesmos aumenta o desempenho na recuperação dos dados.
Quando uma consulta é feita em uma tabela sem uma indexação configurada corretamente, todos os registros são lidos para buscar o resultado esperado. O uso de índices aumenta a eficiência de operações de leitura ao reduzir a quantidade de dados que as consultas precisam processar. Percorrer um índice é muito mais rápido que percorrer uma tabela inteira.
Nesse contexto, é necessário avaliar se as coleções estão indexadas, bem como as consultas escritas fazem uso dos mesmos. É recomendável a criação de índices para as cláusulas de filtro (WHERE), ordenação (ORDER BY), agrupamento (GROUP BY) e de junção entre tabelas (Table1 JOIN Table2 ON Table1.colA = Table2.colB).
Também é importante considerar a cardinalidade da coluna, ou seja, o número de valores diferentes que ela pode conter. A indexação é mais eficaz para colunas com cardinalidade alta em relação ao número de linhas da tabela, com poucos valores duplicados. Uma coluna que só contenha valores ‘V’ ou ‘F’, por exemplo, não é uma boa candidata para índice.
Mesmo criando índices em todos os campos utilizados é necessário confirmar se eles realmente serão utilizados nas consultas. Com o comando EXPLAIN, tanto no MySQL quanto no MongoDB, é possível visualizar o plano de execução das consultas para certificar-se de que o servidor está tirando proveito dos índices.
Em um caso específico, tínhamos uma página que faz a consulta de uma lista de clientes de forma paginada em uma coleção do MongoDB. O retorno da mesma não era necessariamente lento, mas ao avançar pelas páginas, por vezes ocorria estouro de memória do servidor na pesquisa. O erro ocorria em virtude de os índices utilizados na tabela abrangerem apenas as cláusulas do WHERE utilizada na consulta, e não os de ordenação. A solução foi criar índices nos campos usados na cláusula de ordenação.
Com a revisão dos índices foi possível a correção de alguns erros e uma melhora de performance em algumas consultas. As pesquisas realizadas no MongoDB já apresentavam um resultado imediato, mas as consultas no MySQL feitas pelo Entity Framework estavam longe do ideal. O próximo passo agora era analisar as consultas em si e as ferramentas utilizadas.
Entity Framework
O Entity Framework é um ORM com a finalidade de simplificar a conversão de informações estruturadas em objetos, auxiliando na construção de aplicações que utilizam bancos de dados relacionais. Mesmo sendo bastante popular e útil em vários cenários, apresenta alguns contratempos em situações mais específicas. Consultas que envolvem tabelas com um grande número de registros constituem um bom exemplo disto: a solução executada nem sempre o faz da forma mais eficiente e performática, ainda que os índices estejam corretamente configurados como visto anteriormente.
A solução então pode estar em outras ferramentas, como o micro ORM Dapper.
Utilização do Dapper
O Dapper é um micro ORM Open Source para .NET. Funciona de forma simples, auxiliando no mapeamento de objetos a partir de consultas SQL. Seu foco é performance, por isso é considerada uma library de alto desempenho para acesso a dados.
O Dapper surgiu em 2011, desenvolvido por Marc Gravel e Sam Saffron quando trabalhavam no StackOverflow. Para enfrentar vários problemas de performance, causados principalmente pela indexação do Google e à utilização do LINQ-2-SQL, a dupla resolveu escrever seu próprio ORM. Assim, nasceu o Dapper.
Com esta ferramenta é possível escrever as consultas SQL, assim removendo a complexidade gerada por outras soluções. Além da simplicidade de utilização, o custo de performance é muito baixo. No github do Dapper também é possível encontrar algumas ferramentas de benchmark para realizar o comparativo de diferentes bibliotecas.
Ainda que não possua todas as funcionalidades de um ORM convencional, este framework disponibiliza Extension Methods que estendem a interface IdbConnection. Com isso, o Dapper pode ser utilizado com qualquer tecnologia relacional compatível com o .NET. SQL Server, Oracle, PostgreSQL e MySQL são alguns exemplos de SGDBs possíveis. As funcionalidades do Dapper se resumem a três pontos principais: busca, parametrização da consulta e mapeamento do resultado.
A seguir um exemplo de consulta utilizando o Dapper:
Recomenda-se também a passagem de parâmetros por meio de um objeto anônimo. Esta prática tem por finalidade evitar falhas que levem a ataques do tipo SQL Injection, algo bastante possível em instruções geradas via concatenação de strings.
No trabalho de otimização feito no Elliot, várias pesquisas antes feitas com Entity Framework foram substituídas por consultas escritas usando Dapper e o resultado foi bastante positivo. Operações que antes demoravam mais de 10s agora executam em centésimos de segundo.
Enquanto o Entity Framework torna mais fácil lidar com relacionamentos entre coleções, inserções, atualizações e exclusão de dados, o Dapper é mais rápido na execução e materialização de consultas.
Referências
Dapper: exemplos de utilização em ASP.NET Core e .NET Full
Acompanhe o Elliot nas redes sociais (@eusouelliot) ou através do nosso site eusouelliot.com
