Acervo.estudos
Trilhas Enviar material Entrar
mapa · Intermediário

2.24 SQL: Índices e Transações

Índices (CREATE INDEX, UNIQUE, composto, quando vale a pena) e transações: BEGIN, COMMIT, ROLLBACK, SAVEPOINT, propriedades ACID e o comportamento em erro no PostgreSQL e no MySQL.

por @julio · rev. 23 set · 5 telas

Achar rápido sem reordenar a tabela

Índices

clique nos círculos para abrir e fechar ramos · arraste para mover · role para dar zoom
CREATE INDEX idx_sobrenome ON funcionario (sobrenome);
CREATE INDEX idx_nome_completo ON funcionario (nome, sobrenome);
CREATE UNIQUE INDEX idx_cpf ON funcionario (cpf);   -- cpf não é a PK

DROP INDEX idx_sobrenome;                     -- PostgreSQL
DROP INDEX idx_sobrenome ON funcionario;      -- MySQL

⚠ O vídeo mostra só DROP INDEX nome_do_índice. No MySQL esse comando dá erro: é preciso DROP INDEX nome ON tabela.

Leitura mais rápida, escrita mais lenta

Quando criar índice

clique nos círculos para abrir e fechar ramos · arraste para mover · role para dar zoom

Exemplo do vídeo: 500 buscas por código de cliente por mês contra 50 cadastros novos. O ganho nas buscas compensa a pequena perda nos INSERTs. É sempre uma conta caso a caso.

Para ver se o índice é usado: EXPLAIN SELECT … (PostgreSQL e MySQL).

Tudo ou nada

Transações

clique nos círculos para abrir e fechar ramos · arraste para mover · role para dar zoom
BEGIN;
UPDATE conta_corrente SET saldo = saldo - 1000 WHERE conta = 'A';
UPDATE conta_corrente SET saldo = saldo + 1000 WHERE conta = 'B';
COMMIT;      -- ou ROLLBACK; se algo deu errado

Sem BEGIN, PostgreSQL e MySQL trabalham em autocommit: cada comando é uma transação sozinha.

⚠ O vídeo diz que o COMMIT "irá iniciar todo o processo". Quem inicia é o BEGIN; o COMMIT confirma e encerra a transação.

⚠ O vídeo diz que, na transação, os dados "não são escritos diretamente no banco" e ficam num repositório temporário. Na prática o SGBD grava as alterações nas tabelas (com versões de linha ou log de undo) e no log de transações; é o isolamento que impede as outras sessões de vê-las antes do COMMIT.

Voltar só um pedaço

Savepoint e erros

clique nos círculos para abrir e fechar ramos · arraste para mover · role para dar zoom
BEGIN;
UPDATE conta SET saldo = 120 WHERE id = 1;
SAVEPOINT sp1;
UPDATE conta SET saldo = saldo - 100 WHERE id = 2;
SELECT saldo FROM conta WHERE id = 2;   -- já 100 a menos
ROLLBACK TO sp1;                        -- desfaz só o 2º UPDATE
SELECT saldo FROM conta WHERE id = 2;   -- saldo original
COMMIT;                                 -- grava o 1º UPDATE

⚠ O vídeo chama o recurso de "Savepoint ou Checkpoint". São coisas diferentes: checkpoint é a gravação periódica em disco das páginas alteradas, feita pelo próprio SGBD.

⚠ O vídeo diz que, havendo erro, "o SGBD irá executar um ROLLBACK". Isso só é automático na recuperação após queda. Num erro de comando, o PostgreSQL exige que você envie ROLLBACK; o MySQL desfaz só aquele comando e mantém a transação aberta.

Teste antes de seguir

Revisão rápida

clique nos círculos para abrir e fechar ramos · arraste para mover · role para dar zoom
Por que não criar índice em todas as colunas?

Cada índice é atualizado em todo INSERT, UPDATE e DELETE e ocupa disco.

Qual propriedade ACID garante que a transferência não fica pela metade?

Atomicidade.

E qual impede outra sessão de ver o saldo no meio da transferência?

Isolamento.

Depois de ROLLBACK TO sp1, a transação terminou?

Não. Ela continua aberta; ainda é preciso COMMIT ou ROLLBACK.

← Voltar ao curso