Estou tendo dificuldade em entender a ideia dos prós e contras do particionamento de tabelas. Estou prestes a começar a trabalhar em um projeto que teria 8 tabelas e uma delas será a tabela de dados principal que conterá 180-260 milhões de registros. Como vai ser tabela devidamente indexada, então estou pensando em limitar os registros da tabela para 20 milhões desta forma eu teria que criar de 9 a 13 tabelas.
Mas não tenho certeza de como isso melhorará o desempenho porque eles estarão na mesma máquina (32 GB de RAM)?
Estou usando o MySQL e as tabelas seriam MyISAM e a tabela grande teria um índice no campo id e não há mais complexidades como pesquisa de texto completo, etc.
Por favor, também esclareça o particionamento de tabela versus o particionamento de banco de dados.
O seguinte é apenas um discurso insano e delirante...
Se você deixar todos os dados em uma tabela (sem particionamento), terá tempos de pesquisa O(log n) usando uma chave. Vamos pegar o pior índice do mundo, a árvore binária. Cada nó de árvore tem exatamente uma chave. Uma árvore binária perfeitamente balanceada com 268.435.455 (2^28 - 1) nós de árvore teria uma altura de 28. Se você dividir essa árvore binária em 16 árvores separadas, obterá 16 árvores binárias cada uma com 16.777.215 (2^24 - 1) nós da árvore para uma altura de 24. O caminho de busca é reduzido em 4 nós, uma redução de altura de 14,2857%. Se o tempo de pesquisa for em microssegundos, uma redução de 14,2857% no tempo de pesquisa é de zero a insignificante.
Agora, no mundo real, um índice BTREE teria nós de árvore com várias chaves. Cada pesquisa BTREE realizaria uma pesquisa binária dentro da página com uma possível descida para outra página. Por exemplo, se cada página BTREE contivesse 1024 chaves, uma altura de árvore de 3 ou 4 seria a norma, uma altura de árvore curta de fato.
Observe que um particionamento de uma tabela não reduz a altura do BTREE que já é pequeno. Dado um particionamento de 260 milhões de linhas, existe até uma forte probabilidade de haver vários BTREEs com a mesma altura. A busca por uma chave pode passar por todas as páginas BTREE raiz todas as vezes. Apenas um preencherá o caminho do intervalo de pesquisa necessário.
Agora expanda isso. Todas as partições existem na mesma máquina. Se você não tiver discos separados para cada partição, terá E/S de disco e rotações do eixo como um gargalo automático fora do desempenho da pesquisa de partição.
Nesse caso, o particionamento por banco de dados também não compra nada se id for a única chave de pesquisa sendo utilizada.
O particionamento de dados deve servir para agrupar dados que estão de forma lógica e coesa na mesma classe. O desempenho da pesquisa de cada partição não precisa ser a consideração principal, desde que os dados estejam agrupados corretamente. Depois de obter o particionamento lógico, concentre-se no tempo de pesquisa. Se você estiver apenas separando dados apenas por id, é possível que muitas linhas de dados nunca sejam acessadas para leituras ou gravações. Agora, essa deve ser uma consideração importante: localize todos os ids acessados com mais frequência e particione por eles . Todos os IDs acessados com menos frequência devem residir em uma grande tabela de arquivo que ainda é acessível por pesquisa de índice para essa consulta 'uma vez na lua azul'.
O impacto geral deve ser ter pelo menos duas partições: uma partição para IDs acessados com frequência e outra partição para o restante dos IDs. Se o número de IDs acessados com frequência for bastante grande, você pode, opcionalmente, particionar isso.
200 milhões de linhas certamente estão no intervalo em que você pode se beneficiar do particionamento de tabelas. Dependendo da sua aplicação, você pode apostar em alguns dos benefícios listados abaixo:
Facilidade de limpeza de dados antigos Se você precisar limpar registros com mais de (digamos) 6 meses, você pode particionar a tabela na data e, em seguida, trocar as partições mais antigas. Isso é muito mais rápido do que excluir dados de uma tabela e geralmente pode ser feito em um sistema ativo. No caso do OP, isso pode ser útil para a manutenção do sistema.
Vários volumes de disco O particionamento permite dividir dados para distribuir o tráfego de disco em vários volumes de disco para obter velocidade. Com um controlador RAID moderno, isso provavelmente não será um problema para o OP.
Varreduras mais rápidas de tabelas e intervalos Na verdade, um sistema operacional não deveria fazer esse tipo de coisa, mas um data warehouse ou sistema semelhante fará esse tipo de consulta em quantidade. As varreduras de tabela usam principalmente tráfego de disco sequencial, portanto, geralmente são a maneira mais eficiente de processar uma consulta que retorna mais do que uma pequena porcentagem das linhas em uma tabela.
O particionamento por um filtro comum (normalmente baseado em tempo ou período) permite que grandes partes da tabela sejam eliminadas de tais consultas se o predicado puder ser resolvido em relação à chave de particionamento. Ele também permite que a tabela seja dividida em vários volumes, o que pode proporcionar ganhos significativos de desempenho para grandes conjuntos de dados. Normalmente, isso não é um problema para sistemas operacionais.
Para os propósitos do OP, o particionamento provavelmente não trará muitos benefícios de desempenho para consultas operacionais, mas pode ser útil para o gerenciamento do sistema. Se houver algum requisito significativo para relatar agregações em grandes volumes de dados, um esquema de particionamento apropriado pode ajudar nisso.
O particionamento permite reorganizações simultâneas por partição, se todos os seus índices forem particionados. Caso contrário, as partições ainda serão muito menores e usarão menos espaço de trabalho para reorganizar. E, internamente, qualquer DBMS "bom" pode fazer coisas em paralelo com tabelas particionadas. Isso provavelmente NÃO inclui MySQL ou MyISAM, embora ....