Como lidar com instruções SQL lentas causadas por configurações de índice composto inapropriadas?
Cenário
Em sua instância, uma consulta SQL executada às 11:00 e que deveria levar 8 segundos levou mais de 30 segundos.
Possíveis causas
- Verifique o uso da CPU. Neste exemplo, durante esse período, o uso da CPU da instância não aumentou acentuadamente e permaneceu baixo, portanto, sabemos que a consulta lenta não foi causada pelo alto uso da CPU.
Figura 1 Uso da CPU
- Analise logs de consulta lentos gerados durante esse período. Neste exemplo, mostrado abaixo, havia várias instruções SQL que envolviam milhões de linhas sendo verificadas. Essas foram as instruções lentas. Mas nenhuma grande quantidade de dados foi inserida na tabela durante esse tempo, então sabemos que a execução lenta foi causada por configurações de índice ausentes ou incorretas. Ao executar EXPLAIN, você pode descobrir que o plano de execução da instrução SQL era a verificação completa da tabela.
Figura 2 Logs de consulta lenta
- Execute SHOW INDEX FROM na tabela da instância para verificar a cardinalidade das três colunas.
Figura 3 Cardinalidade do índice
O campo query_date com a menor cardinalidade estava no primeiro lugar do índice composto, e o campo group_id com a maior cardinalidade estava no último lugar do índice composto. Além disso, a instrução SQL continha a consulta de intervalo do campo query_date. Como resultado, apenas o campo query_date foi indexado.
A instrução SQL só podia usar o índice da coluna query_date. Além disso, o otimizador pode ter selecionado a varredura de tabela completa durante a estimativa de custo porque a cardinalidade era muito pequena.
Um novo índice composto foi criado com o campo group_id no primeiro lugar e o campo query_date no último lugar. O tempo de consulta atendeu à expectativa.
Solução
- Verifique se a consulta lenta foi causada por recursos insuficientes da CPU.
- Verifique se a estrutura da tabela foi projetada corretamente e se as configurações de índice estão corretas.
- Execute a instrução ANALYZE TABLE periodicamente para evitar planos de execução incorretos, pois a execução de um grande número de operações INSERT ou DELETE para dados de tabela pode resultar em estatísticas desatualizadas.
