Estou tentando melhorar o desempenho da seguinte consulta, que leva mais de 1 minuto para ser executada:
SELECT *
FROM test
WHERE ( created_at < '2023-3-31 06:10:20.871' )
AND ( ( id > '2a95048f' )
OR ( id = '2a95048f'
AND created_at > '2022-12-27 23:53:24.958' ) )
ORDER BY id ASC,
created_at ASC
LIMIT 1000;
Alterei a consulta trocando a ordem no ORDER BY e a consulta retornou resultados em 549ms:
SELECT *
FROM test
WHERE ( created_at < '2023-3-31 06:10:20.871' )
AND ( ( id > '2a95048f' )
OR ( id = '2a95048f'
AND created_at > '2022-12-27 23:53:24.958' ) )
ORDER BY created_at ASC,
id ASC
LIMIT 1000;
Aqui estão algumas informações sobre os índices:
- ID - Cardinalidade 6,8 milhões, único
- CREATED_AT - Cardinalidade 7,1 milhões, NÃO único
Por que um é drasticamente performático que o outro?
ATUALIZAÇÃO - "MOSTRAR ÍNDICE DE TESTE"
Para obter uma resposta mais precisa, você deve postar a definição de índices em sua tabela e o plano de consulta de ambas as consultas.
Mas, se eu tivesse que adivinhar, diria que a diferença de desempenho provavelmente se deve à combinação de ORDER BY e LIMIT.
Você provavelmente tem um índice em (created_at, id), mas não um em (id, created_at)
Se você tiver um índice que cubra a ordem por colunas na mesma ordem, o mysql pode pesquisar a tabela usando o índice, obter as linhas na ordem necessária e parar depois de encontrar 1000 linhas que satisfaçam a condição where.
Se não houver tal índice, o mysql deve selecionar todas as linhas que satisfaçam a condição where (provavelmente muitos milhares), então classificá-las e somente após a classificação pode manter as primeiras 1000 e descartar o restante.
Como Andrea mencionou, adicione suas definições de índice à sua postagem, para questões relacionadas ao desempenho.
Mas, supondo que Andrea esteja correto e seu índice esteja definido,
(created_at, id)
a resposta à sua pergunta " Por que a ordem das colunas indexadas em ORDER BY afeta o desempenho? " é porque a ordem em que as colunas são especificadas é a ordem pela qual os dados são classificados - e se essas ordenações não corresponderem entre suaORDER BY
cláusula e a definição do índice, o índice não poderá ser usado para eliminar uma operação de classificação para sua consulta.Ao definir um índice em
(created_at, id)
, você está dizendo ao índice para primeiro classificar os dadoscreated_at
e, em seguida, classificá-losid
dentro da classificação original.Portanto, com uma definição de índice, pense nisso como uma lista telefônica. A lista telefônica geralmente classifica
LastName
primeiro e depoisFirstName
segundo. Se suaORDER BY
cláusula deseja ordenar os dados dessa lista telefônica pelo entãoLastName, FirstName
nenhum trabalho precisa ser feito porque a lista telefônica já está pré-ordenada dessa forma (ou seja, o índice já armazena os dados dessa forma). Mas se você quiserORDER BY
compararFirstName, LastName
o catálogo telefônico, todo o catálogo telefônico precisa ser reordenado para conseguir isso (ou seja, seu índice(LastName, FirstName)
não pode ajudá-lo aqui).