Índices e transações

A loja procura as encomendas de Ana num intervalo de datas. Um índice pode localizar essas linhas sem percorrer toda a tabela.

Ao transferir dinheiro entre contas, a preocupação é outra: debitar e creditar em conjunto. Uma transação define essa unidade de alteração, para a confirmar ou desfazer.

O que um índice guarda

Um índice é uma estrutura persistente que relaciona valores de uma chave de pesquisa com a localização das linhas. Não muda a resposta lógica de uma consulta.

O motor pode usá-lo para encontrar correspondências, percorrer intervalos ou obter uma ordem útil sem ordenar tudo.

Numa árvore equilibrada, a procura da primeira correspondência costuma crescer com o logaritmo do número de entradas; depois há o custo de percorrer e recuperar as correspondências. Uma tabela hash é adequada a igualdade, mas não fornece diretamente a mesma ordenação para intervalos. SQLite usa índices baseados em árvores, não uma opção USING HASH no comando CREATE INDEX.

Não confundas chave de pesquisa do índice com chave candidata. Podemos indexar stock apesar de vários produtos terem o mesmo stock.

CREATE UNIQUE INDEX acrescenta também uma regra de unicidade. Um índice comum só acrescenta um método de acesso.

Índices compostos

Para consultar encomendas de um cliente num intervalo de datas, um índice composto pode ser útil:

CREATE INDEX idx_encomenda_cliente_data
ON Encomenda(idCliente, data);

As entradas ficam ordenadas primeiro por cliente e, dentro dele, por data. A igualdade no primeiro atributo permite procurar o intervalo relevante no segundo. O índice também pode ajudar consultas apenas por cliente. Uma procura apenas por data não beneficia da mesma forma desse prefixo, embora certos planos e distribuições permitam outras estratégias do otimizador.

Com os dados da loja, as entradas de (idCliente, data) ficam assim ordenadas:

Fixar o cliente 1 isola as duas primeiras entradas; o intervalo de datas percorre só esse bloco.

A ordem (data, idCliente) tem comportamento diferente. Se procurarmos um intervalo amplo de datas, a igualdade do cliente já não restringe da mesma maneira um único intervalo ordenado. Escolhe a ordem pelo conjunto de consultas mais frequentes e pelas distribuições reais.

Índices também ajudam junções, por exemplo encontrar as encomendas que referenciam um cliente. SQLite não cria automaticamente um índice para cada estrangeira. As primárias e unicidades já têm mecanismos de acesso próprios, com a particularidade de INTEGER PRIMARY KEY corresponder ao rowid.

Ler um plano

EXPLAIN QUERY PLAN
SELECT id, data FROM Encomenda
WHERE idCliente = 1 AND data >= '2026-01-01';
CREATE INDEX idx_encomenda_cliente_data ON Encomenda(idCliente, data);
EXPLAIN QUERY PLAN
SELECT id, data FROM Encomenda
WHERE idCliente = 1 AND data >= '2026-01-01';

PRAGMA foreign_keys = ON;
CREATE TABLE Cliente (
  id INTEGER PRIMARY KEY,
  nome TEXT NOT NULL,
  email TEXT UNIQUE
);
CREATE TABLE Produto (
  id INTEGER PRIMARY KEY,
  nome TEXT NOT NULL,
  precoCentimos INTEGER NOT NULL CHECK (precoCentimos >= 0),
  stock INTEGER NOT NULL CHECK (stock >= 0)
);
CREATE TABLE Encomenda (
  id INTEGER PRIMARY KEY,
  data TEXT NOT NULL,
  idCliente INTEGER NOT NULL REFERENCES Cliente(id)
);
CREATE TABLE Item (
  idEncomenda INTEGER NOT NULL REFERENCES Encomenda(id) ON DELETE CASCADE,
  idProduto INTEGER NOT NULL REFERENCES Produto(id),
  qtd INTEGER NOT NULL CHECK (qtd > 0),
  precoUnitario INTEGER NOT NULL CHECK (precoUnitario >= 0),
  PRIMARY KEY (idEncomenda, idProduto)
);
INSERT INTO Cliente VALUES
  (1, 'Ana', 'ana@example.test'),
  (2, 'Rui', NULL),
  (3, 'Mia', NULL);
INSERT INTO Produto VALUES
  (10, 'Teclado', 4500, 20),
  (11, 'Rato', 2500, 50),
  (12, 'Monitor', 18000, 5);
INSERT INTO Encomenda VALUES
  (100, '2026-01-05', 1),
  (101, '2026-01-06', 1),
  (102, '2026-01-06', 2);
INSERT INTO Item VALUES
  (100, 10, 2, 4500),
  (100, 11, 1, 2500),
  (101, 12, 1, 18000),
  (102, 11, 3, 2500);

O primeiro plano percorre Encomenda e o segundo pode procurar usando o índice, por isso confere sobretudo SCAN, SEARCH e o nome do índice.

Um índice cobridor contém os valores de que a consulta precisa, permitindo evitar acessos adicionais à tabela em certos planos. Nos índices comuns de uma tabela SQLite com rowid, esse identificador também está nas entradas; isso ajuda a devolver o id de uma INTEGER PRIMARY KEY.

O custo dos índices

Cada inserção, eliminação e alteração dos atributos indexados precisa de manter os índices. Eles ocupam espaço e têm custo de criação. Se uma condição devolve quase toda a tabela, um percurso completo pode ser mais barato que muitas visitas por índice.

A seletividade indica quanto a condição restringe os dados. Uma coluna com poucos valores pode ser útil ou inútil conforme a frequência dos valores procurados e o plano. “Há dois valores distintos” não chega para decidir: um deles pode corresponder a uma fração muito pequena das linhas.

O otimizador usa estatísticas para estimar custos. ANALYZE recolhe estatísticas em SQLite. A escolha final depende do tamanho, da distribuição, dos índices e da carga de leitura e escrita. Criar um índice em todas as colunas não é uma estratégia de desenho.

Uma transação e as propriedades ACID

Uma transação agrupa operações que têm de formar uma unidade. Transferir 25 euros exige debitar uma conta e creditar outra. Uma falha entre as duas operações não pode deixar uma transferência a meio.

• Atomicidade: as alterações da transação são confirmadas em conjunto ou desfeitas.
• Consistência: a transação leva um estado válido a outro válido segundo as regras do domínio.
• Isolamento: define o que transações concorrentes podem observar e como interferem.
• Durabilidade: as alterações confirmadas sobrevivem às falhas abrangidas pelas garantias e configuração do sistema.

O SGBD verifica as restrições declaradas, mas não adivinha todas as regras. Uma transação que debita duas contas sem creditar nenhuma pode ser atómica e ainda estar errada. Consistência exige código correto e restrições adequadas.

BEGIN, COMMIT e ROLLBACK

Em SQLite, fora de uma transação explícita, as instruções decorrem em transações implícitas. BEGIN inicia uma transação explícita, COMMIT confirma e ROLLBACK desfaz as alterações dessa transação.

CREATE TABLE Conta(id INTEGER PRIMARY KEY,
saldo INTEGER NOT NULL CHECK (saldo >= 0));
INSERT INTO Conta VALUES (1, 10000), (2, 5000);
BEGIN;
UPDATE Conta SET saldo = saldo - 2500 WHERE id = 1;
UPDATE Conta SET saldo = saldo + 2500 WHERE id = 2;
ROLLBACK;
SELECT id, saldo FROM Conta ORDER BY id;
BEGIN;
UPDATE Conta SET saldo = saldo - 2500 WHERE id = 1;
UPDATE Conta SET saldo = saldo + 2500 WHERE id = 2;
COMMIT;
SELECT id, saldo FROM Conta ORDER BY id;

Os estados das contas na transferência de 2500 cêntimos:

Depois do rollback, os saldos voltam ao início e depois do commit refletem a transferência, porque a primeira transferência não deixou efeitos guardados.

O código pressupõe que as duas contas existem e que há saldo suficiente. Um UPDATE de uma conta inexistente altera zero linhas e não dá automaticamente erro. Uma aplicação deve conferir o número de linhas afetadas e só confirmar quando todas as condições da transferência estiverem cumpridas.

Um erro não faz sempre rollback de tudo

Em SQLite, uma violação de CHECK com a política por omissão costuma abortar a instrução, mantendo anteriores instruções da transação. Se o primeiro débito ocorreu e a segunda operação falhou, não assumes que o débito já foi desfeito. Trata o erro e decide fazer rollback da transação inteira.

PostgreSQL tem outro comportamento: um erro deixa normalmente a transação em estado abortado até rollback, ou recuperação para um savepoint. A distinção importa ao ler código de aplicação e ao prever resultados.

Savepoints

Um savepoint marca um ponto de recuperação dentro de uma transação:

CREATE TABLE Valor(id INTEGER PRIMARY KEY, n INTEGER NOT NULL);
INSERT INTO Valor VALUES (1, 10);
BEGIN;
UPDATE Valor SET n = n + 5 WHERE id = 1;
SAVEPOINT tentativa;
UPDATE Valor SET n = n * 2 WHERE id = 1;
ROLLBACK TO tentativa;
RELEASE tentativa;
COMMIT;
SELECT n FROM Valor;

A primeira atualização dá 15. A segunda dá 30, mas ROLLBACK TO regressa a 15. RELEASE retira o marcador; o COMMIT exterior confirma 15. Libertar um savepoint interior não equivale a tornar as suas alterações definitivamente duráveis antes do commit exterior.

Falhas e duração

Um SGBD pode usar registos de escrita, journaling ou WAL para recuperar estados após falhas. Atomicidade desfaz trabalho não confirmado; durabilidade conserva trabalho confirmado dentro das condições assumidas. A recuperação não substitui cópias de segurança contra eliminação deliberada, corrupção fora dessas garantias ou perda de todos os suportes.

Mantém transações curtas. Pedir confirmação humana enquanto a transação está aberta pode conservar bloqueios por muito tempo. A página de concorrência explica por que atomicidade, por si só, não impede leituras inconsistentes nem decisões simultâneas erradas.