Estou importando cerca de 50 milhões de linhas para MySQL 8, InnoDB. Está no AWS RDS com armazenamento GP3.
A chave exclusiva das linhas é uma string semelhante a uuid.
Ao consultar, nunca nos importaremos com essa chave exclusiva, exceto ao atualizar linhas novas/modificadas da fonte primária.
Normalmente, o ID exclusivo seria o PK. Mas eu li que o índice PK no MySQL é especial porque visa 'agrupar' os dados para valores semelhantes, para melhorar o desempenho.
Parece que ao usar uma string semelhante a uuid como PK, o clustering não ajudará em nossas consultas.
Se eu particionasse a tabela, faria isso por intervalo de datas.
Eu poderia imaginar definir uma PK sintética, ou uma PK composta, que combine o campo de data e o uuid para obter um cluster com maior probabilidade de suportar as consultas que realmente fazemos.
Minha pergunta é a seguinte: quão importante é ter um cluster PK que suporte as consultas típicas (ou seja, os resultados obtidos provavelmente estarão 'próximos' no índice)?
Presumivelmente, o caso típico de um ID de incremento automático para PK também resulta em clustering que tem pouca relação com consultas típicas (muitas vezes não há razão para selecionar IDs adjacentes).
Estou pensando especificamente se o armazenamento SSD moderno torna esse tipo de otimização menos importante, obsoleto... ou ainda mais importante?
Mais contexto
https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html
Como o índice clusterizado acelera as consultas
Acessar uma linha por meio do índice clusterizado é rápido porque a pesquisa do índice leva diretamente à página que contém os dados da linha. Se uma tabela for grande, a arquitetura de índice clusterizado geralmente salva uma operação de E/S de disco quando comparada a organizações de armazenamento que armazenam dados de linha usando uma página diferente do registro do índice.
Parece que o "agrupamento" do índice PK só tem valor para consultas selecionadas por PK.
Trata-se de co-localizar os dados da linha com o índice (?)
Então, se todas as consultas de aplicativos que me interessam usam índices secundários, acho que realmente não importa quais são as propriedades do PK? por exemplo, incluir uma coluna de partição de data no PK não vai acelerar magicamente as consultas usando um índice diferente.
Isso está certo?
Depende.
Para "consultas pontuais" por meio do PK, há 1 pesquisa BTree.
Para "consultas pontuais" por meio de um índice secundário, existem duas pesquisas BTree.
Para "consultas de intervalo", os UUIDs são praticamente inúteis - as linhas ficarão espalhadas. (No entanto, consulte UUIDs .)
Para informações agrupadas cronologicamente... Se você tiver, digamos, artigos de notícias e a maioria das solicitações for de artigos "recentes", seria benéfico ter os dados classificados cronologicamente.
AUTO_INCREMENT(principalmente) consegue isso; UUIDs não (novamente, veja o link acima)Se seus provedores externos fornecerem UUIDs e posteriormente se referirem a eles, use UUIDs como PKs. A sobrecarga de transformar os UUIDs em ids auto_inc provavelmente não vale a pena.
Se a tabela inteira couber
innodb_buffer_pool_size, um servidor de produção eventualmente obterá todos os blocos (16 KB cada) e serão armazenados em cache na RAM. Neste caso, não importa se o PK é auto_inc ou UUID ou qualquer outra coisa.Em algumas aplicações raras (índice 2D útil)
PARTITIONingpode ser útil. (Mas não vejo isso na pergunta.)Empacote UUIDs em
BINARY(16)(veja o link ou funções agora disponíveis no MySQL e MariaDB). (Economizar espaço diminui um pouco a E/S - ajudando assim no desempenho.)Qual é o índice em geral? Wу especificará alguma expressão. Este valor de expressão é calculado para cada linha em nosso conjunto de linhas. Em seguida, esses valores (acompanhados da referência à linha que produz o valor) são classificados. Agora, quando sabemos o valor da expressão, podemos facilmente encontrar a(s) linha(s) cuja(s) expressão(ões) tem o valor necessário, usando o método de meia divisão.
O que é índice secundário e não clusterizado no InnoDB? O servidor calcula a expressão do índice para cada linha, classifica e salva o resultado no índice que é uma estrutura de disco separada. Agora, quando especificamos o valor da expressão do índice, o servidor procura esse valor no índice, então obtém a referência para a própria linha (que está contida no arquivo do corpo da tabela no disco) e recupera a própria linha usando esta referência .
O que é índice clusterizado? O servidor calcula a expressão de índice para cada linha, classifica as linhas de acordo com os valores desta expressão e salva as linhas no disco nesta ordem. Agora, quando especificamos o valor da expressão do índice, o servidor procura esse valor no índice e, quando encontra esse valor do índice, também encontra a própria linha. Não precisamos pular para o corpo da mesa – já estamos nela.
E novamente sobre o índice secundário. Eu disse que o valor da expressão de índice nele é acompanhado pela referência na própria linha. Esta referência deve ajudar a encontrar a linha em si o mais rápido possível - portanto, esta referência é um valor de expressão de índice clusterizado. Ou seja, quando pesquisamos por índice secundário, usamos a pesquisa binária por seu valor de expressão, pegamos o valor da expressão de índice clusterizado de acordo e usamos a pesquisa binária por esse valor de expressão clusterizada para encontrar a própria linha.
Finalmente. Quando pesquisamos a linha por índice secundário, precisamos realizar 2 pesquisas. Quando pesquisamos por índice clusterizado, precisamos apenas de uma pesquisa.