No MySQL/MariaDB, o desempenho dos índices diminui à medida que eles se tornam cada vez maiores?

Para um bilhão de linhas, ou para 100 bilhões de linhas, um bom índice é sempre melhor do que partições, em termos de desempenho?

Há várias coisas que posso dizer sobre isso.

• Não podemos fazer essa generalização, pois depende da consulta. Em geral, todo tipo de otimização é uma grande ajuda para o tipo certo de consulta, em detrimento de outros tipos de consulta. Portanto, você deve ser muito específico sobre qual consulta deseja otimizar antes de escolher o método de otimização.

• Não é uma escolha ou um ou outro. Você pode particionar uma tabela e também definir um índice, para que as pesquisas sejam otimizadas em uma determinada partição.

• Não acho que você tenha 100 bilhões de linhas. Se o fizesse, você não estaria fazendo esta pergunta no Stack Exchange, você estaria atribuindo à sua equipe de arquitetos de banco de dados em tempo integral a tarefa de otimizá-lo. Sem dúvida, eles voltariam com um design que usa muitos servidores. É impraticável armazenar 100 bilhões de linhas em uma única tabela. Como você faria o backup? Como você adicionaria uma coluna?

O InnoDB usa índices de árvore B (também de texto completo e índices espaciais, mas para esta discussão assumimos o tipo padrão de índice).

Índices de árvore B têm complexidade O(log ₂ n ) tanto para inserção quanto para pesquisa, onde n é o número de entradas na estrutura de dados. Inserir ou pesquisar, portanto, fica mais caro à medida que o índice aumenta.

A E/S exigida por uma pesquisa de índice é uma função da profundidade da árvore B. Ou seja, quantos níveis de nós não terminais devem ser percorridos para chegar ao nó folha. A profundidade depende de quantas entradas de índice existem e também de quão grande é o tipo de dados de uma determinada entrada, porque os tamanhos de página do InnoDB são fixos, portanto, apenas alguns nós de índice podem caber em uma página. Veja: https://www.percona.com/blog/2009/04/28/the_depth_of_a_b_tree/

O custo de E/S pode ser mitigado mantendo subconjuntos das páginas de índice na RAM, no buffer pool do InnoDB. Mas se o índice crescer muito mais do que a RAM, não haverá buffer pool suficiente para manter todo o índice, portanto, se você fizer pesquisas aleatoriamente em todo o índice, o InnoDB provavelmente removerá as páginas que você precisará novamente em breve. Essas páginas serão recarregadas do armazenamento quando você precisar delas, mas isso pode levar a uma sobrecarga extra, pois as páginas são trocadas dentro e fora da RAM.

Os índices de cache só se aplicam ao MyISAM. O InnoDB armazena em cache as páginas sob demanda, que podem incluir um subconjunto de um determinado índice. Esqueça qualquer comando manual para carregar índices no cache. Para ser sincero, recomendo esquecer o MyISAM para qualquer finalidade. Não o vejo usado adequadamente desde os anos 2000.

Você perguntou sobre o armazenamento NVMe. O NVMe é obviamente mais rápido que as interfaces SATA antigas, mas como ele se compara à RAM? Depende do que você mede, mas tanto para o tempo de acesso quanto para a taxa de transferência (MB/segundo), você pode contar com uma RAM várias vezes mais rápida que a última geração de NVMe. Além disso, o código InnoDB foi escrito para assumir que as páginas devem estar na RAM antes de serem lidas. Ainda é uma vitória manter os dados e as páginas de índice em cache na RAM.

Concordo com a afirmação geral de Rick de que o particionamento geralmente não ajudará no desempenho tanto quanto você pensa. É útil no cenário certo, mas não é uma solução mágica "tudo vai rápido". Isso também vale para qualquer outro tipo de otimização!

(Além dos comentários de Bill...)

Outra forma de analisar o desempenho -- "Contar as ocorrências do disco".

• O InnoDB armazena em cache blocos de 16 KB (dados e índice) em seu buffer_pool.
• O cache é sob demanda e é o menos usado recentemente.
• Todas as atividades (leitura, gravação, pesquisa etc.) são feitas no buffer_pool, não diretamente no disco.
• Uma regra simples é que o fanout de qualquer InnoDB BTree é de cerca de 100. (Percona usou 128 - essencialmente o mesmo). Um bilhão de linhas (de dados ou índice) terá 5 níveis. Isso significa que 5 blocos devem ser buscados (se ainda não estiverem em cache) para fazer uma consulta de ponto simples. Ou 10 blocos para uma pesquisa por meio de uma chave secundária. (6 e 12 para 100 bilhões de linhas.)
• Se você estiver buscando 101 linhas 'consecutivas' desse BTree, precisará de 2 nós folha (aquele com o qual você começa mais o próximo). Outra regra prática: todos os nós não folha provavelmente são armazenados em cache.
• Se sua atividade for aleatória (como com um UUID ou hash para o PK) e o buffer_pool não puder conter todo o BTree, o cache se tornará praticamente inútil. Isso faz com que cada consulta de ponto leve pelo menos 1 hit de disco. Nesse caso, "muito grande é péssimo para o desempenho".
• Se, por outro lado, você estiver apenas tocando nas linhas "recentes" (e o PK for AUTO_INCREMENT ou baseado em tempo), o cache buffer_pool terá os dados desejados 99 vezes em 100. Ou seja, não há degradação, pois o tabela cresce, mesmo quando muito maior que o buffer_pool.

(Eu fiz muitos 'acenos de mão' nessas duas últimas declarações.)

Espero ter lhe dado algumas pistas sobre como julgar por si mesmo se sua mesa sofrerá ou não com o crescimento. Se você quiser uma discussão mais aprofundada, forneça o arquivo CREATE TABLE. Quando alguém fala sobre tabelas de bilhões de linhas, gosto de reduzir os tipos de dados, reestruturar o esquema, normalizar, adicionar tabelas de resumo, considerar o sharding etc. Mas raramente recomendo o particionamento. Às vezes, recomendo "manter as tabelas de resumo, mas jogar fora a tabela de fatos". Isso elimina todos os tipos de problemas de dimensionamento e desempenho.

No MySQL/MariaDB, o desempenho dos índices diminui à medida que eles se tornam cada vez maiores?

Depende do que você entende por "desempenho"...

Se você quer dizer "encontrar uma linha ou um intervalo de linhas com base na chave indexada", a resposta seria "um pouco". Como os outros explicaram, desde que o conjunto de trabalho do índice permaneça no cache, "grande" pode ficar um pouco mais lento que "pequeno", mas é provável que seja inundado pelo restante do tempo de consulta usado por logística, rede , análise, etc. Se as páginas de folha não forem armazenadas em cache, isso adicionaria um IO aleatório, então você teria que perguntar ao seu sistema IO sobre quanto tempo isso levará.

Mas você pergunta sobre índice versus particionamento, portanto, neste caso, se os dados forem os mesmos no caso "particionado" e no caso "tabela única", o tamanho total dos índices nas partições seria praticamente o mesmo que o índice na mesa única. Com a mesma carga de consulta, não há motivo para que um seja armazenado em cache melhor que o outro, portanto, provavelmente não haveria diferença entre os dois. Se você acessar apenas as linhas mais recentes, ambos os cenários se beneficiariam da mesma forma por precisar armazenar em cache apenas as partes correspondentes dos índices.

No entanto, se você tiver uma visão global do desempenho e adicionar coisas como "excluir todas as linhas com mais de 12 meses" e tiver um bilhão de linhas para excluir ao executar esta operação de arquivamento, tabelas e índices enormes se tornarão uma ideia extremamente ruim (TM ). Se for um índice na data, talvez seja tolerável porque a exclusão atingirá um pedaço contíguo dele. No entanto, se for um índice em uma coluna bastante aleatória, cada linha excluída acionará gravações aleatórias em algum lugar do índice, em todo o lugar, e isso será interrompido para sempre.

Considerando que, se você usar o particionamento, "DROP PARTITION" é quase instantâneo porque, nos bastidores, está apenas excluindo os arquivos correspondentes. A menos que haja gatilhos ON DELETE a serem acionados, não há sentido em ler as linhas a serem excluídas se o banco de dados souber que estamos descartando toda a partição.

Se não houver gravações em partições mais antigas, isso pode tornar os backups muito mais rápidos, se a ferramenta de backup puder explorar o fato de que não há necessidade de fazer backup de uma partição que não foi alterada.

Estou editando para adicionar outras circunstâncias em que o particionamento pode tornar suas consultas mais rápidas:

Você não tem dinheiro suficiente para colocar toda a tabela em um SSD, então você coloca as partições antigas (e índices) que raramente são acessadas em um RAID desajeitado e lento, e as partições mais recentes (e índices) que veem a maior parte a ação em alguns SSDs extremamente rápidos. Essa é uma boa otimização "dinheiro versus desempenho", mas você terá que mover as partições de vez em quando. Talvez você possa até replicar apenas as partições recentes ou colocar uma partição por servidor e executá-las em paralelo, se o banco de dados suportar, coisas assim.

Além disso, se o otimizador de consulta estragar tudo e decidir fazer uma verificação completa da tabela ou algo do tipo, talvez se sua tabela for particionada e a consulta tiver uma condição na chave de partição, o tamanho da confusão pode ser limitado a apenas um algumas partições em vez de toda a tabela.