Fundamentos de administração de banco de dados voltados a desempenho
Índices: quando ajudam e quando atrapalham
Índices são estruturas auxiliares (ex.: B-tree) que aceleram buscas e ordenações ao evitar varreduras completas (full scan) em tabelas grandes. Em provas, o ponto-chave é o trade-off: leitura mais rápida versus escrita mais lenta (INSERT/UPDATE/DELETE precisam manter o índice).
- Use índice em colunas muito filtradas (WHERE), usadas em JOIN, ORDER BY e em chaves estrangeiras (para acelerar joins e evitar locks longos).
- Evite excesso de índices em tabelas com alta taxa de escrita; cada índice adicional aumenta custo de manutenção e pode degradar desempenho.
- Índice composto: útil quando filtros combinam colunas. A ordem das colunas importa (regra do “prefixo à esquerda”).
- Baixa seletividade (ex.: coluna “sexo” com poucos valores) tende a não ajudar; o otimizador pode preferir full scan.
Passo a passo prático: diagnosticar se um índice é necessário
- 1) Identifique a consulta lenta: capture a SQL e parâmetros reais (valores típicos).
- 2) Observe o plano de execução: verifique se há full scan, custo alto, grandes leituras lógicas/físicas e operações de sort/hash.
- 3) Verifique seletividade: quantas linhas retornam versus total da tabela.
- 4) Proponha índice: escolha colunas do WHERE/JOIN/ORDER BY e avalie índice composto.
- 5) Reavalie o plano: compare custo, leituras e tempo; valide impacto em escrita.
-- Exemplo conceitual (SQL genérico): consulta com filtro e ordenação frequentes (pode pedir índice composto em (status, data_criacao))
SELECT *
FROM processos
WHERE status = 'EM_ANDAMENTO'
ORDER BY data_criacao DESC;Estatísticas do otimizador: por que importam
Estatísticas descrevem distribuição de dados (cardinalidade, histogramas, correlação) e orientam o otimizador a escolher planos eficientes (nested loops, hash join, index scan, etc.). Estatísticas desatualizadas levam a estimativas erradas e planos ruins.
- Sintomas: regressão de performance “do nada”, plano mudando após carga massiva, consultas que antes usavam índice e passam a fazer full scan.
- Boas práticas: atualizar estatísticas após grandes alterações de dados; manter rotinas automáticas; monitorar tabelas com alta volatilidade.
Particionamento (conceitos) para desempenho e manutenção
Particionamento divide uma tabela grande em partes menores (partições) com base em uma chave (ex.: data). Benefícios típicos: partition pruning (ler apenas partições relevantes), manutenção mais simples (arquivar partições antigas), e operações em lote mais rápidas.
- Por faixa (range): comum por data (mês/ano).
- Por lista (list): por valores discretos (ex.: unidade/órgão).
- Por hash: distribui uniformemente para reduzir hotspots.
- Armadilha de prova: particionamento não substitui índice; ele reduz o conjunto de dados lido, mas ainda pode exigir índices dentro das partições.
Concorrência: locks, isolamento e deadlocks
Locks: o que são e como afetam o sistema
Lock é um mecanismo para garantir consistência quando várias transações acessam os mesmos dados. Em geral, há locks de leitura e escrita (com variações por SGBD). O impacto prático é o bloqueio: uma transação pode esperar outra liberar o lock, aumentando latência e podendo gerar cascatas de espera.
- Bloqueio (blocking): sessão A segura lock; sessão B fica aguardando.
- Escalonamento de lock: lock em linha pode virar lock em página/tabela em cenários de muitos locks (depende do SGBD).
- Hotspot: muitas transações atualizando a mesma linha/intervalo (ex.: “contador” global) geram contenção.
Níveis de isolamento: relação com anomalias
Isolamento define o quanto uma transação “enxerga” alterações de outras. Em provas, relacione níveis com anomalias clássicas:
Continue em nosso aplicativo
Você poderá ouvir o audiobook com a tela desligada, ganhar gratuitamente o certificado deste curso e ainda ter acesso a outros 5.000 cursos online gratuitos.
ou continue lendo abaixo...
Baixar o aplicativo
- Read Uncommitted: permite dirty read (ler dado não confirmado).
- Read Committed: evita dirty read, mas pode ter non-repeatable read (mesma linha lida duas vezes com valores diferentes).
- Repeatable Read: evita non-repeatable read, mas pode ter phantom read (novas linhas aparecem em reconsulta por faixa).
- Serializable: evita phantoms; maior custo e maior chance de bloqueios.
Alguns SGBDs usam MVCC (controle de concorrência multiversão), reduzindo bloqueios de leitura ao ler “versões” consistentes, mas ainda pode haver conflitos de escrita.
Deadlocks: como surgem e como tratar
Deadlock ocorre quando duas (ou mais) transações ficam esperando recursos uma da outra, formando um ciclo. SGBDs normalmente detectam deadlock e abortam uma transação (vítima) para quebrar o ciclo.
- Causa comum: ordem diferente de atualização em tabelas/linhas (Transação 1 atualiza A depois B; Transação 2 atualiza B depois A).
- Mitigação: padronizar ordem de acesso aos objetos, reduzir tempo de transação, manter transações curtas, índices adequados para evitar varreduras com locks longos.
-- Exemplo conceitual de padrão que pode gerar deadlock
-- T1: atualiza tabela X e depois Y
-- T2: atualiza tabela Y e depois X
-- Solução típica: padronizar a ordem (sempre X depois Y) em todas as rotinas.Troubleshooting básico de performance e indisponibilidade
Checklist rápido: investigação de lentidão
- 1) Sintoma: é geral (tudo lento) ou específico (uma consulta/rotina)?
- 2) Recursos: CPU alta? I/O alto? memória insuficiente? saturação de disco?
- 3) Esperas/locks: há sessões bloqueadas? há longas transações abertas?
- 4) Plano de execução: mudou recentemente? estatísticas estão atualizadas?
- 5) Crescimento: tabela cresceu muito? houve carga/ETL recente?
- 6) Configuração: parâmetros de cache/buffer, logs, autovacuum/autoanalyze (dependendo do SGBD) estão adequados?
Checklist rápido: investigação de indisponibilidade
- 1) Escopo: instância não sobe? sobe mas não aceita conexões? apenas algumas aplicações falham?
- 2) Logs: erro de storage? corrupção? falta de espaço? falha de rede?
- 3) Últimas mudanças: patch, alteração de parâmetro, deploy, mudança de storage.
- 4) Integridade: sinais de corrupção física/lógica? erros de leitura?
- 5) Plano de ação: failover (se houver HA) ou restore (se necessário).
Backup e recuperação: estratégias e execução
Conceitos essenciais: RPO e RTO
- RPO (Recovery Point Objective): quanto de perda de dados é aceitável (ex.: 15 min).
- RTO (Recovery Time Objective): quanto tempo pode ficar indisponível (ex.: 1 hora).
Esses objetivos determinam frequência de backup, retenção de logs e necessidade de replicação/HA.
Tipos de backup: full, incremental e diferencial
- Full: cópia completa. Restore simples (restaura o full e aplica logs, se houver). Custo maior de tempo e armazenamento.
- Incremental: copia apenas mudanças desde o último backup (full ou incremental). Economiza espaço e tempo de backup, mas restore pode exigir cadeia longa (full + vários incrementais + logs).
- Diferencial: copia mudanças desde o último full. Restore costuma ser mais simples que incremental (full + último diferencial + logs), mas o diferencial cresce com o tempo.
Passo a passo prático: desenhar uma política de backup (nível de prova)
- 1) Defina RPO/RTO: ex.: RPO 15 min, RTO 60 min.
- 2) Escolha estratégia: full semanal + diferencial diário + retenção de logs (ou incremental) para atender RPO.
- 3) Garanta consistência: backups devem ser consistentes (online com logs ou offline). Em bancos transacionais, é comum combinar backup de dados + backup/arquivamento de logs.
- 4) Defina retenção: quantos dias/semanas manter; considerar exigências de auditoria.
- 5) Armazene com segurança: cópia fora do servidor (offsite), controle de acesso, criptografia em repouso e em trânsito.
- 6) Automatize e monitore: alertas de falha, verificação de integridade, relatórios.
Restore: recuperação completa e recuperação pontual (PITR)
Restore é o processo de restaurar dados a partir de backups. Em bancos com logs de transação, é possível fazer Point-in-Time Recovery (PITR), restaurando até um instante anterior a um erro (ex.: exclusão acidental às 10:05, restaurar para 10:04:59).
- Restore completo: restaurar full (e diferencial/incrementais) e aplicar logs até o ponto desejado.
- PITR: exige cadeia de backups + logs contínuos até o instante alvo.
- Armadilha de prova: sem logs (ou sem arquivamento/retention), não há PITR; apenas restauração até o último backup disponível.
Testes de restauração: o que validar
Backup “feito” não significa backup “restaurável”. Testes periódicos são parte do controle.
- Teste técnico: restaurar em ambiente isolado e verificar se o banco sobe e aceita consultas.
- Teste de integridade: checagens de consistência (ferramentas do SGBD), contagens e validações amostrais.
- Teste de tempo: medir se o restore cumpre o RTO.
- Teste de PITR: simular erro e restaurar para um ponto específico.
Noções de alta disponibilidade (HA) e replicação (nível de prova)
Alta disponibilidade: objetivo e padrões
HA busca reduzir indisponibilidade. Em geral, envolve redundância e failover.
- Ativo-passivo: um nó primário atende; secundário assume em falha. Failover pode ser automático ou manual.
- Ativo-ativo: mais de um nó atende simultaneamente (mais complexo; depende de arquitetura e do SGBD).
- Failover: troca de primário para secundário; requer mecanismos de detecção e promoção.
Replicação: síncrona vs assíncrona
- Síncrona: confirmação de commit depende do secundário. Menor perda de dados (melhor RPO), porém maior latência e risco de indisponibilidade se o secundário estiver lento.
- Assíncrona: primário confirma commit sem esperar secundário. Melhor desempenho e tolerância a latência, mas pode perder transações recentes em falha (RPO pior).
Em provas, associe: síncrona tende a RPO próximo de zero; assíncrona tende a melhor performance e maior risco de perda recente.
Checklists por cenário (queda de instância, corrupção lógica, restauração pontual)
Cenário 1: queda de instância (crash) e retorno do serviço
- 1) Confirmar escopo: banco não inicia? não aceita conexões? apenas um nó caiu?
- 2) Verificar recursos: disco cheio, falha de storage, memória, CPU, rede.
- 3) Consultar logs: mensagens de erro no startup, falhas de arquivos de dados/log.
- 4) Se houver HA: avaliar failover para nó saudável (priorizar RTO).
- 5) Se sem HA: reiniciar instância e permitir recuperação automática (crash recovery) se suportado; validar consistência.
- 6) Validar aplicação: conexões, pools, credenciais, latência.
Cenário 2: corrupção lógica (erro humano/aplicação)
Corrupção lógica é quando os dados “estão errados”, mas o banco está íntegro fisicamente (ex.: UPDATE sem WHERE, DELETE acidental, carga com valores incorretos).
- 1) Conter o dano: interromper rotina/usuário, isolar aplicação, evitar novas escritas.
- 2) Determinar janela: quando ocorreu? quais tabelas/linhas afetadas?
- 3) Escolher estratégia: PITR para antes do evento, ou restauração parcial em ambiente auxiliar e “reaplicação” seletiva (depende do SGBD e do caso).
- 4) Validar: conferência por amostragem, relatórios, reconciliação com fonte.
- 5) Prevenir recorrência: permissões mínimas, transações com validação, revisões, trilhas de auditoria.
Cenário 3: restauração pontual (PITR) após exclusão acidental
- 1) Identificar o instante-alvo: ex.: excluir às 10:05; alvo 10:04:59.
- 2) Garantir cadeia de restore: último full + diferencial/incrementais + logs até o alvo.
- 3) Restaurar em ambiente isolado: preferível para validar e evitar sobrescrever produção prematuramente.
- 4) Executar PITR: aplicar logs até o timestamp/SCN/LSN (conforme SGBD).
- 5) Validar dados: checar registros críticos, integridade referencial, relatórios.
- 6) Planejar retorno: substituir base, ou extrair apenas dados necessários e aplicar em produção (quando aplicável).
Perguntas situacionais (estilo prova) para fixação
Desempenho e modelagem física
- Uma consulta passou a fazer full scan após grande carga de dados. Qual hipótese é mais provável: falta de índice ou estatísticas desatualizadas? Que evidência no plano de execução ajudaria a decidir?
- Você tem uma tabela de eventos com bilhões de linhas por data. Qual benefício direto do particionamento por range em data para consultas do “último mês”?
- Uma tabela de alta escrita tem 12 índices e está com latência alta em INSERT. Qual medida tende a ajudar: criar mais índices, revisar índices redundantes, ou aumentar isolamento para serializable?
Concorrência
- Duas rotinas atualizam as mesmas tabelas em ordem diferente e há deadlocks frequentes. Qual correção é mais efetiva: aumentar timeout de lock, padronizar ordem de atualização, ou desabilitar índices?
- Em Read Committed, qual anomalia ainda pode ocorrer: dirty read, non-repeatable read ou nenhuma? Como isso impacta relatórios em transações concorrentes?
Backup/restore e continuidade
- Seu RPO é 5 minutos. Apenas backup full diário atende? O que precisa existir além do full para cumprir o RPO?
- Você tem full semanal e incrementais diários. No restore, qual é a sequência típica e qual risco operacional aumenta com cadeias longas de incrementais?
- Após um DELETE acidental, você precisa recuperar para 2 minutos antes do evento. Quais pré-requisitos de backup/log tornam o PITR possível?
- Em replicação assíncrona, qual risco aumenta em caso de queda do primário: perda de dados recente ou aumento de latência de commit?
