Índices e transações

Escolher e ler um índice

Um índice liga valores de pesquisa à localização das linhas: acelera igualdade, intervalos, junções e ordenação sem mudar o resultado lógico. Chave de pesquisa não é chave candidata; índice comum não impõe unicidade.

• Numa árvore, a primeira correspondência custa cerca de logaritmo das entradas; faltam percorrer e recuperar as restantes.
• Um composto (idCliente, data) ordena por cliente e depois data: igualdade no cliente isola um bloco para procurar o intervalo de datas. Só por data, o prefixo não serve.
• SQLite usa índices de árvore e não cria nenhum por cada estrangeira; primárias e unicidades já têm acesso próprio.
• Um índice cobridor tem os valores pedidos e evita voltar à tabela; em tabelas comuns, o rowid acompanha as entradas.

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);

Lê SCAN contra SEARCH/COVERING e o índice usado. Um plano mostra o método, não prova ganho de tempo para qualquer volume.

Índices custam espaço e manutenção em cada escrita; se o filtro devolve quase tudo, o percurso completo pode vencer. Decide por consultas, distribuição e carga; ANALYZE recolhe estatísticas. Índice em todas as colunas não é desenho.

Transação e ACID

Uma transação é a unidade de confirmação ou recuperação: ou confirma tudo ou desfaz-se.

Transferir exige débito e crédito, contas existentes e condições cumpridas; UPDATE de conta inexistente afeta zero linhas sem erro. Confere linhas afetadas antes de confirmar.

Confirmar, desfazer, marcar

• BEGIN abre; COMMIT confirma; ROLLBACK desfaz a transação. Fora de transação explícita, SQLite usa implícitas.
• SAVEPOINT s marca; ROLLBACK TO s regressa; RELEASE s retira o marcador sem confirmar nada fora.

Com valor 10: soma 5, marca, multiplica por 2, regressa ao marco (volta a 15) e confirma 15 no COMMIT exterior.

Um erro não desfaz sempre tudo: em SQLite, violar CHECK aborta normalmente a instrução, mantendo o anterior da transação; em PostgreSQL, a transação fica abortada até rollback ou savepoint. Trata o erro antes de confirmar e mantém transações curtas. Journaling e WAL ajudam a recuperar; não substituem backups.

Rollback e tratamento de erros.