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.
Índices
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.
Quando criar índice
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).
Transações
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 erradoSem 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.
Savepoint e erros
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.
Revisão rápida
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.