Banco de dados X Índices + Particionamento de Tabelas
comentada em 15/12/2020 21:49

Não sou DBA de MySQL, mas uso como dev há um bom tempo. Vai aí o que aprendi com a experiência:

- Índices são necessários pra melhorar as buscas, mas se vc tem uma query que combine 3 campos, vc precisa de um índice com esses 3 campos, se tiver um índice pra cada um, o MySQL vai escolher e usar só um deles. Uma boa forma de ver o que o MySQL está utilizando é o comando EXPLAIN antes da query.

- A forma de escrever as queries também afeta a performance, evite sempre que possível utilizar subqueries em joins por exemplo, pois internamente  é criada uma tabela temporária sem índices é os joins ficam bem pesados.

- Particionamento vale quando vc precisa só de uma porção de dados em uma tabela muito grande, vc pode especificar a partição na query ou deixar pro mysql identificar, mas tem que ser muito bem pensadas as partições, não é tão fácil de criar partições quanto é criar um índice.



 

1. Nao, o indice é indicado pra encontrar dados mais rapidamente se o dado ao ser encontrado é diferente dos restantes 96% dos dados da tabela. E outra: o seu indice e tipo dele vai depender do sql q sera executado na tabela. Nada adianta criar um indice na coluna X, e o seu sql ser "where Y < 10" , sacou?
Ja vi tabelas com centenas de indices e a performance era um lixo, a solução foi apagar e recriar alguns indices.

2. Depende. O particionamento de tabelas e indices serve pra juntar grupos de dados em um bloco pra q o scan fique mais rapido, mas de novo analise o sql e entenda o caso de uso primeiro pra otimizar a criação do particionamento. Exemplo: tabela de vendas, seria interessante particionar pela coluna data da venda, ou codigo do produto pra otimizar geração de relatórios.
Nada tem sim ou nao. Cada caso é um caso e pode pedir um estratégia diferente. :)

Você já ouviu falar de hashtable?

TUDO ABAIXO REFERE-SE A BANCOS EM GERAL, PROCURE SABER PARA O MYSQL

O índice funciona da mesma forma:

Toda vez que os campos envolvidos na busca (where) estão no índice, o banco monta um hash e olha na tabela do índice quais são as linhas (Ids) envolvidos. Tudo isso em memória.

Quando um índice não dá resultado?

1. Quando ele é incompleto. Voc? tem 3 campos no índice e usou 4 na query
2. Quando ele é desnecessário. A tabela é muito pequena.
3. Quando você está fazendo join e não consultando uma depois a outra.

No caso 3 acima, por exemplo, você tem TabelaA com campos aa,ab,ac e TabelaB com campos ba, bb, bc.

Você quer todos de A que em B bb = 1.

e imagine que você tem um índice em TabelaB.bb 

Se fizer:

Select A.aa, A.ab, A.ac from TabelaA, TabelaB
where A.aa = B.ba and B.bb = 1  

Você está fazendo um join para depois filtrar. Aí vai da inteligência do banco, se ele for esperto o suficiente vai transformar sua query em:

Select A.aa, A.ab, A.ac from TabelaA where A.aa in (Select B.ba from TabelaB where BB.bb = 1);

Se não for esperto o suficiente, vai carregar A toda, depois B toda, aplicar o join e filtrar.

Entendeu?

Do exemplo que dei, você mesmo pode forçar o uso de um índice, reescrevendo sua query e não confiando inteiramente ao banco essa tarefa.

Nesse exemplo, a inner query Select B.ba from TabelaB where BB.bb = 1 é executada primeiro, lendo o índice, depois a TabelaA é consultada, mas agora já pelo ID, para pegar todos os dados que você precisa.

Bem menos custoso para o banco.

O analisador de queries pode rearranjar sua consulta pelo plano de execução, mas pode ser que ele se perca.

Aí é onde separam-se os programadores mais experientes: saber conversar direito com o banco

Sobre partition:

Melhora quando o banco tem alguma funcionalidade de paralelismo. Ex: acessa discos em paralelo.

Melhora quando seu dado pode ser consultado de forma particionada. Ex: você tem uma tabela com 50 anos de dados, particionada mês a mês e quer consultar dezembro de 1990. É muito mais tranquilo consultar uma partição pequena do que um monstro.

Melhora quando suas partições são muito acessadas e acabam indo pra memória. Mesmo caso do mês-a-mês ali... imagine que o mês corrente está em memória! Cacheado.

Melhora quando você usa esquemas de compactação diferenciados. Ex: dados antigos como read-only, com compression etc, facilitando a vida do banco.

E piora com o inverso...

Seu banco é serial, sua máquina é uma porcaria, só tem 1 disco ou não aguenta o I/O, a tabela é pequena, só vai adicionar overhead....

A infra é até legal mas você nunca filtra pela partição e o banco não tem bom plano de execução...

Enfim. É analisar caso a caso.

Em TI não existe NADA, absolutamente NADA que se diga: faça isso que sempre melhora.

Nem processamento!

Por exemplo, um sistema real time embarcado, você adiciona mais "processamento" por que é a bala de prata certo? Sempre que a coisa tá ruim, melhore o clock, Só que isso caga todo o relógio do sistema. Como é real-time, vai ter que ajustar, a aplicação se ferrou inteira, deu crash, você perdeu uma grana preta e seu chefe quer sua caveira.

Só um exemplo. Não existe bala de prata.

Quando crescer quero ter 1% do conhecimento do @FlyCavok

@Bastter me sinto mais burro que o normal lendo os comentários dele

vou aguardar o comentario do @oxe

@Veit eu? Entendo é nada disso kkk

Deixa os feras falarem aí, eu sou só um menino querendo aprender também.