SQL Índices

A Ana atende um cliente no balcão da Loja Aurora e quer ver os pedidos dele. O banco responde na hora, porque a loja tem só 90 pedidos. Mas e quando forem 90 mil? Ou 9 milhões? Sem ajuda, o banco precisa ler todos os pedidos, um por um, para achar os daquele cliente. A ajuda tem nome: índice. Nesta aula você vai criar índices com CREATE INDEX, entender por que a ordem das colunas importa num índice composto, usar o UNIQUE INDEX para proibir valores repetidos e apagar índices com DROP INDEX.

Os exemplos rodam de verdade no seu navegador, num SQLite com o banco da Loja Aurora. Cada exemplo parte do banco limpo: o índice que um exemplo cria não existe no seguinte. As sessões de terminal saíram do sqlite3 3.50.4 no computador fictício da Ana, com o arquivo ~/loja.db.

A ideia: o índice remissivo do livro

Pegue um livro de receitas grosso e procure "mandioca". Você tem dois jeitos:

1. Folhear o livro inteiro, página por página, olhando cada receita. Funciona sempre, mas demora, e demora mais quanto mais grosso o livro.
2. Ir ao índice remissivo, lá no fim: uma lista em ordem alfabética com cada ingrediente e as páginas onde ele aparece. Você acha "mandioca" em segundos (porque a lista está em ordem) e vai direto às páginas 37 e 112.

Macaxeira ......... veja Mandioca
Maionese .......... 15, 88
Mandioca .......... 37, 112
Manga ............. 140
Maracujá .......... 141, 150

Um índice de banco de dados é exatamente isso. É uma lista à parte, sempre em ordem, com os valores de uma coluna e, ao lado de cada valor, onde estão as linhas que o têm. Num índice da coluna cliente_id dos pedidos, o banco acha "cliente 3" depressa e pula direto para os pedidos 3, 29, 55 e 81, sem ler os outros 86.

Repare na última linha: o índice não é de graça. Ele ocupa espaço e dá trabalho a cada mudança. Você vai ver isso na seção 7.

1. O primeiro índice: CREATE INDEX

CREATE INDEX quer dizer "crie um índice". Este cria um índice na coluna cliente_id da tabela pedidos:

CREATE INDEX idx_pedidos_cliente ON pedidos (cliente_id);

CREATE INDEX

"Crie um índice". O comando que monta a lista ordenada.

idx_pedidos_cliente

O nome do índice. É obrigatório e não pode repetir o nome de outro índice, tabela ou view do banco. O costume é idx_ (de index, "índice") + a tabela + a coluna: quem lê o nome já sabe o que o índice guarda.

ON pedidos

"Na tabela pedidos". ON = "em", "sobre".

(cliente_id)

A coluna que o índice vai pôr em ordem, entre parênteses. Pode ser mais de uma, separadas por vírgula (seção 4).

O resultado é só um "ok": criar um índice não devolve linhas. O banco leu os 90 pedidos uma vez, montou a lista ordenada por cliente_id e a guardou no arquivo, ao lado da tabela.

Onde o índice fica guardado

Todo banco SQLite tem uma tabela especial, a sqlite_schema ("esquema do SQLite"), com uma linha para cada tabela, índice e view que existe nele. Dá para consultá-la como qualquer tabela:

CREATE INDEX idx_pedidos_cliente ON pedidos (cliente_id);

SELECT type, name, tbl_name, sql
FROM sqlite_schema
WHERE type = 'index';

type

O tipo do objeto: table, index, view ou trigger. O WHERE type = 'index' deixa só os índices.

name

O nome do índice.

tbl_name

A tabela a que ele pertence.

sql

O comando que o criou, guardado como texto.

Vieram dois índices, e você só criou um. O primeiro, sqlite_autoindex_itens_pedido_1, o SQLite criou sozinho, e a coluna sql dele é NULL porque nenhum CREATE INDEX o criou. Você vai entender de onde ele veio na seção 6.

-- crie o índice aqui:


SELECT name, tbl_name
FROM sqlite_schema
WHERE type = 'index';

-- crie o índice aqui:
CREATE INDEX idx_produtos_categoria ON produtos (categoria_id);

SELECT name, tbl_name
FROM sqlite_schema
WHERE type = 'index';

2. O índice não muda o resultado

Esta é a regra mais importante da aula: um índice nunca muda o que a consulta devolve, só como o banco chega lá. Você não precisa citar o índice no SELECT; o próprio banco decide se vale a pena usá-lo. Compare as duas consultas, uma sem e outra com o índice:

SELECT id, data, frete
FROM pedidos
WHERE cliente_id = 3;

CREATE INDEX idx_pedidos_cliente ON pedidos (cliente_id);

SELECT id, data, frete
FROM pedidos
WHERE cliente_id = 3;

As mesmas 4 linhas, na mesma ordem. Com 90 pedidos, a diferença de tempo nem dá para medir. Então como saber se o banco usou o índice? Existe um comando para perguntar ao SQLite qual caminho ele vai seguir: o EXPLAIN QUERY PLAN ("explique o plano da consulta"). Só uma espiada aqui; ele tem aula própria, EXPLAIN QUERY PLAN:

EXPLAIN QUERY PLAN
SELECT id, data, frete FROM pedidos WHERE frete = 0;

CREATE INDEX idx_pedidos_cliente ON pedidos (cliente_id);

EXPLAIN QUERY PLAN
SELECT id, data, frete FROM pedidos WHERE cliente_id = 3;

Olhe só a coluna detail ("detalhe"). Na primeira consulta, o frete não tem índice, e o plano diz SCAN pedidos: vou ler a tabela inteira (folhear o livro). Na segunda, diz SEARCH pedidos USING INDEX idx_pedidos_cliente (cliente_id=?): vou buscar usando o índice, pelo valor de cliente_id (ir ao índice remissivo). As outras colunas são números internos do SQLite e podem ser ignoradas.

No terminal, o programa sqlite3 desenha o mesmo plano em forma de árvore, sem os números:

$ sqlite3 loja.db
SQLite version 3.50.4 2025-07-30 19:33:53
Enter ".help" for usage hints.
sqlite> EXPLAIN QUERY PLAN SELECT id, data, frete FROM pedidos WHERE frete = 0;
QUERY PLAN
`--SCAN pedidos
sqlite> CREATE INDEX idx_pedidos_cliente ON pedidos (cliente_id);
sqlite> EXPLAIN QUERY PLAN SELECT id, data, frete FROM pedidos WHERE cliente_id = 3;
QUERY PLAN
`--SEARCH pedidos USING INDEX idx_pedidos_cliente (cliente_id=?)
sqlite> .quit

Medindo num banco grande

Para sentir a diferença, a Ana fez um teste no terminal, num arquivo à parte (teste.db, para não mexer na loja): uma tabela com um milhão de números. O comando .timer on do sqlite3 mostra quanto tempo cada consulta levou:

$ sqlite3 teste.db
SQLite version 3.50.4 2025-07-30 19:33:53
Enter ".help" for usage hints.
sqlite> CREATE TABLE numeros (n INTEGER, dobro INTEGER);
sqlite> WITH RECURSIVE c(x) AS (SELECT 1 UNION ALL SELECT x + 1 FROM c WHERE x < 1000000) INSERT INTO numeros SELECT x, x * 2 FROM c;
sqlite> .timer on
sqlite> SELECT dobro FROM numeros WHERE n = 777777;
1555554
Run Time: real 0.030 user 0.029716 sys 0.000144         « sem índice: leu 1 milhão de linhas
sqlite> CREATE INDEX idx_numeros_n ON numeros (n);
Run Time: real 0.236 user 0.189177 sys 0.027303         « montar o índice também leva tempo
sqlite> SELECT dobro FROM numeros WHERE n = 777777;
1555554
Run Time: real 0.000 user 0.000060 sys 0.000007         « com índice: menos de 1 milésimo
sqlite> .quit

Não se preocupe com o WITH RECURSIVE: é só um jeito de gerar um milhão de linhas, e ele tem aula própria (CTE Recursiva). O que importa são os tempos: a mesma consulta, com a mesma resposta (1555554), caiu de 30 milésimos de segundo para menos de 1. Parece pouco, mas um site faz essa consulta milhares de vezes por minuto, e numa tabela de 100 milhões de linhas os 30 milésimos virariam 3 segundos. Repare também que criar o índice levou 0,236 s: o banco precisou ler a tabela inteira e pôr tudo em ordem. A aula Quando o Índice Ajuda faz essas medições com calma e mostra quando o índice não ajuda.

3. Vendo os índices de uma tabela

Além da sqlite_schema, o SQLite tem dois comandos PRAGMA ("instrução especial") que respondem perguntas sobre índices. Eles funcionam no editor também:

CREATE INDEX idx_pedidos_cliente ON pedidos (cliente_id);

PRAGMA index_list('pedidos');
PRAGMA index_info('idx_pedidos_cliente');

Os PRAGMAs têm aula própria em PRAGMAs Úteis. No terminal do sqlite3, existe um atalho ainda mais curto, o comando de ponto .indexes (só do programa sqlite3, não do SQL; veja Comandos de Ponto). E o .schema de uma tabela mostra os índices dela embaixo do CREATE TABLE:

$ sqlite3 loja.db
SQLite version 3.50.4 2025-07-30 19:33:53
Enter ".help" for usage hints.
sqlite> .indexes
sqlite_autoindex_itens_pedido_1
sqlite> CREATE INDEX idx_pedidos_cliente ON pedidos (cliente_id);
sqlite> .indexes
idx_pedidos_cliente              sqlite_autoindex_itens_pedido_1
sqlite> .indexes pedidos                       « só os índices da tabela pedidos
idx_pedidos_cliente
sqlite> .schema pedidos
CREATE TABLE pedidos (
    id             INTEGER PRIMARY KEY,
    cliente_id     INTEGER NOT NULL REFERENCES clientes(id),
    funcionario_id INTEGER NOT NULL REFERENCES funcionarios(id),
    data           TEXT NOT NULL,
    entregue_em    TEXT,
    frete          REAL NOT NULL DEFAULT 0
);
CREATE INDEX idx_pedidos_cliente ON pedidos (cliente_id);

Diferente do editor, no terminal o índice fica gravado no arquivo loja.db: na próxima vez que a Ana abrir o banco, ele continua lá. Índice é parte do banco, como as tabelas.

4. Índice composto: várias colunas

A Ana quer os pedidos de um cliente a partir de uma data: "os pedidos da cliente 3 desde 2025". Um índice pode ter mais de uma coluna. Esse é o índice composto:

CREATE INDEX idx_pedidos_cliente_data ON pedidos (cliente_id, data);

SELECT id, cliente_id, data
FROM pedidos
WHERE cliente_id = 3 AND data >= '2025-01-01';

idx_pedidos_cliente_data

O nome, com as duas colunas, na ordem.

(cliente_id, data)

Primeiro o índice ordena por cliente_id. Entre os pedidos do mesmo cliente, ordena por data.

A ordem das colunas importa: a lista telefônica

Pense numa lista telefônica antiga, ordenada por sobrenome e, dentro do mesmo sobrenome, por nome. Ela é ótima para achar "Sousa, Ana" e ótima para achar todos os "Sousa". Mas é inútil para achar todas as pessoas chamadas "Ana": as Anas estão espalhadas pela lista inteira, uma em cada sobrenome. Você teria de ler a lista toda.

Um índice composto funciona igual. O (cliente_id, data) ajuda quando a consulta filtra pela primeira coluna (com ou sem a segunda), mas não ajuda quem filtra só pela segunda. O plano confirma:

CREATE INDEX idx_pedidos_cliente_data ON pedidos (cliente_id, data);

EXPLAIN QUERY PLAN
SELECT id, data, frete FROM pedidos WHERE cliente_id = 3 AND data >= '2025-01-01';

EXPLAIN QUERY PLAN
SELECT id, data, frete FROM pedidos WHERE cliente_id = 3;

EXPLAIN QUERY PLAN
SELECT id, data, frete FROM pedidos WHERE data >= '2025-01-01';

A regra prática: no índice composto, ponha primeiro a coluna que aparece sempre no WHERE, comparada com =. Um índice (cliente_id, data) não é a mesma coisa que um (data, cliente_id). Por outro lado, ele já faz o trabalho de um índice só de cliente_id, então ter os dois seria desperdício.

5. UNIQUE INDEX: proibido repetir

Às vezes o índice tem uma segunda função: garantir que um valor não se repita. Dois clientes com o mesmo email são um problema (para qual dos dois vai a nota fiscal?). Com CREATE UNIQUE INDEX ("crie um índice único"), o banco passa a recusar qualquer email repetido:

CREATE UNIQUE INDEX idx_clientes_email ON clientes (email);

INSERT INTO clientes (nome, email)
VALUES ('Beatriz Sousa', 'ana.beatriz@exemplo.com.br');

CREATE UNIQUE INDEX

Igual ao CREATE INDEX, com uma promessa a mais: nunca vão existir duas linhas com o mesmo valor nessa coluna.

idx_clientes_email

O nome do índice.

ON clientes (email)

A tabela e a coluna que não podem repetir.

O índice foi criado sem problema, porque hoje nenhum email se repete. Mas o INSERT tentou cadastrar o email ana.beatriz@exemplo.com.br, que já é da cliente 3, e parou com o erro UNIQUE constraint failed: clientes.email: "a restrição de único falhou em clientes.email". Nada foi gravado. O UNIQUE também pode ser escrito direto no CREATE TABLE, como restrição da coluna; isso tem aula própria, UNIQUE. Por baixo, as duas formas dão no mesmo: um índice único.

E o NULL?

Três clientes da loja estão sem email (NULL): os clientes 7, 15 e 23. Mesmo assim, o índice único foi criado. Para o SQLite, um NULL não é igual a outro NULL ("valor desconhecido" não é igual a "valor desconhecido", veja O Valor NULL), então vários NULL convivem num índice único. Dá até para cadastrar mais um cliente sem email:

CREATE UNIQUE INDEX idx_clientes_email ON clientes (email);

INSERT INTO clientes (nome, email) VALUES ('Beatriz Sousa', NULL);

SELECT COUNT(*) AS sem_email
FROM clientes
WHERE email IS NULL;

Agora são 4 clientes sem email, e o índice único não reclamou. PostgreSQL e MySQL fazem igual; o SQL Server é diferente (veja a nota "Nos outros bancos", mais abaixo).

-- o índice único vem aqui:


INSERT INTO produtos (nome, categoria_id, fornecedor_id, preco)
VALUES ('Lápis Grafite HB', 1, 6, 1.20);

-- o índice único vem aqui:
CREATE UNIQUE INDEX idx_produtos_nome ON produtos (nome);

INSERT INTO produtos (nome, categoria_id, fornecedor_id, preco)
VALUES ('Lápis Grafite HB', 1, 6, 1.20);

Índice único composto

O UNIQUE também vale para várias colunas juntas: aí o que não pode repetir é a combinação. Um índice único em (cidade, nome) de clientes deixaria existir duas "Ana Lima", desde que em cidades diferentes. É o caso da tabela itens_pedido: o mesmo produto aparece em vários pedidos, e cada pedido tem vários produtos, mas o par (pedido, produto) nunca se repete. Como o SQLite garante isso? Com um índice que ele criou sozinho, o assunto da próxima seção.

6. Apagando índices e os índices automáticos

DROP INDEX

Um índice que não ajuda só dá trabalho. Para apagá-lo, use DROP INDEX ("descarte o índice") com o nome dele:

CREATE INDEX idx_pedidos_cliente ON pedidos (cliente_id);

DROP INDEX idx_pedidos_cliente;

SELECT name FROM sqlite_schema WHERE type = 'index';

DROP INDEX

Apaga o índice. Só o índice: a tabela e os dados continuam intactos. As consultas também continuam funcionando e dando o mesmo resultado, só que sem o atalho.

idx_pedidos_cliente

O nome do índice. No SQLite, não precisa dizer de que tabela ele é, porque o nome é único no banco inteiro.

Apagar um índice é uma operação segura: se você se arrepender, basta criar de novo. Bem diferente de DROP TABLE, que leva os dados junto (aula DROP TABLE).

IF NOT EXISTS e IF EXISTS

Criar um índice que já existe é erro; apagar um que não existe também (você vai ver as mensagens em Quando dá errado). Em scripts que rodam várias vezes, use as formas "se":

CREATE INDEX IF NOT EXISTS idx_pedidos_cliente ON pedidos (cliente_id);
CREATE INDEX IF NOT EXISTS idx_pedidos_cliente ON pedidos (cliente_id);

DROP INDEX IF EXISTS idx_que_nunca_existiu;

Os três comandos deram "ok". O segundo CREATE viu que o índice já existia e não fez nada; o DROP viu que o índice não existia e também não fez nada. Nenhum erro.

Os índices que o SQLite cria sozinho

Lembra do sqlite_autoindex_itens_pedido_1? A tabela itens_pedido foi criada com PRIMARY KEY (pedido_id, produto_id), uma chave primária de duas colunas. Para garantir que o par não se repita (e achar um item depressa), o SQLite montou um índice único automático. O mesmo acontece com toda coluna marcada como UNIQUE no CREATE TABLE.

E as outras tabelas, que têm id INTEGER PRIMARY KEY? Elas não ganharam índice nenhum, e nem precisam: no SQLite, a coluna INTEGER PRIMARY KEY é a própria ordem em que a tabela é guardada. Buscar WHERE id = 42 já é rápido sem índice (veja PRIMARY KEY).

Os índices automáticos não podem ser apagados com DROP INDEX, porque a chave primária depende deles:

DROP INDEX sqlite_autoindex_itens_pedido_1;

"Um índice ligado a uma restrição UNIQUE ou PRIMARY KEY não pode ser apagado." Para tirar esse índice, seria preciso tirar a restrição da tabela. Pela mesma razão, você não pode dar a um índice seu um nome começado por sqlite_: esses nomes são reservados para o próprio SQLite.

7. O preço de um índice: quando criar

Se o índice deixa as buscas tão rápidas, por que não criar um índice em cada coluna? Porque ele custa:

• Espaço: o índice é uma cópia ordenada da coluna (mais o "endereço" de cada linha). Num banco grande, os índices podem ocupar tanto quanto os dados.
• Escritas mais lentas: cada INSERT, UPDATE ou DELETE precisa atualizar a tabela e cada índice dela. É como um livro em que cada receita nova obriga a corrigir o índice remissivo. Uma tabela com dez índices recebe dados bem mais devagar que uma com um.
• Tempo de criação: criar o índice lê a tabela inteira (você viu os 0,236 s no teste do milhão).

Não decore essas tabelas: na dúvida, meça. A aula Quando o Índice Ajuda ensina a usar o EXPLAIN QUERY PLAN e o .timer para decidir com números.

8. Formas diferentes de fazer

Há mais de um jeito de conseguir um índice, e o SQLite tem duas variações que valem conhecer de nome:

-- 1. no CREATE TABLE, com UNIQUE (o índice vem junto, automático)
CREATE TABLE cupons (codigo TEXT UNIQUE, desconto REAL);

-- 2. índice parcial: só as linhas que passam no WHERE
CREATE INDEX idx_pedidos_pendentes ON pedidos (data)
WHERE entregue_em IS NULL;

-- 3. índice numa expressão: a busca por lower(email) passa a usar o índice
CREATE INDEX idx_clientes_email_min ON clientes (lower(email));

SELECT name, tbl_name FROM sqlite_schema WHERE type = 'index';

O cupons ganhou o índice sqlite_autoindex_cupons_1 sem nenhum CREATE INDEX. O índice parcial guarda só os 5 pedidos não entregues: pequeno, e perfeito para a tela "pedidos pendentes". O índice em expressão guarda o resultado de lower(email) (o email em minúsculas; veja Funções de Texto), para buscas que ignoram maiúsculas.

Quando dá errado

Os erros de índice são poucos e as mensagens são claras. Em cada quadro abaixo, o SQLite do seu navegador roda o SQL errado e mostra a mensagem real.

CREATE INDEX idx_pedidos_cliente ON pedidos (cliente_id);
CREATE INDEX idx_pedidos_cliente ON pedidos (cliente_id);

$ sqlite3 loja.db
SQLite version 3.50.4 2025-07-30 19:33:53
Enter ".help" for usage hints.
sqlite> CREATE INDEX idx_pedidos_cliente ON pedidos (cliente_id);
sqlite> CREATE INDEX idx_pedidos_cliente ON pedidos (cliente_id);
Parse error: index idx_pedidos_cliente already exists

CREATE INDEX idx_pedidos_cliente ON pedido (cliente_id);

CREATE INDEX idx_pedidos_cliente ON pedidos (cliente);

CREATE UNIQUE INDEX idx_clientes_cidade ON clientes (cidade);

CREATE INDEX ON pedidos (cliente_id);

CREATE INDEX idx_pedidos_cliente ON pedidos cliente_id;

CREATE INDEX pedidos ON pedidos (cliente_id);

DROP INDEX idx_pedidos_cliente;

CREATE INDEX idx_pedidos_cliente ON pedido (cliente);

SELECT name FROM sqlite_schema WHERE type = 'index';

CREATE INDEX idx_pedidos_cliente ON pedidos (cliente_id);

SELECT name FROM sqlite_schema WHERE type = 'index';

-- PostgreSQL: igual ao SQLite
DROP INDEX idx_pedidos_cliente;

-- MySQL e SQL Server: com ON tabela
DROP INDEX idx_pedidos_cliente ON pedidos;

Referência rápida

Exercícios

Missão final: o índice dos vendedores

-- crie o índice aqui:


SELECT name FROM pragma_index_info('idx_pedidos_func_data');

-- crie o índice aqui:
CREATE INDEX idx_pedidos_func_data ON pedidos (funcionario_id, data);

SELECT name FROM pragma_index_info('idx_pedidos_func_data');

Repare na última linha: pragma_index_info(…) é o mesmo PRAGMA index_info, escrito como se fosse uma tabela. Assim dá para escolher só a coluna name. Se você inverteu a ordem das colunas, a saída mostra data em cima, e a missão não passa: apague com DROP INDEX (ou só corrija o CREATE, já que cada Rodar parte do banco limpo).

Resumo

• Um índice é uma lista à parte, sempre em ordem, com os valores de uma ou mais colunas e onde estão as linhas: o índice remissivo do livro. Sem ele, o banco lê a tabela inteira (SCAN); com ele, vai direto às linhas certas (SEARCH).
• CREATE INDEX nome ON tabela (coluna); cria; DROP INDEX nome; apaga (só o índice, nunca os dados). Use IF NOT EXISTS e IF EXISTS em scripts.
• O índice nunca muda o resultado, só a velocidade. O próprio banco decide quando usá-lo.
• No índice composto, a ordem importa: (cliente_id, data) ajuda quem filtra por cliente_id, mas não quem filtra só por data, como uma lista telefônica ordenada por sobrenome.
• CREATE UNIQUE INDEX proíbe valores repetidos (UNIQUE constraint failed), mas aceita vários NULL.
• O SQLite cria sozinho índices para UNIQUE e chaves primárias compostas (sqlite_autoindex_…), e não deixa apagá-los.
• Índice custa espaço e deixa as escritas mais lentas. Crie nas colunas muito buscadas e nas de JOIN, não em todas. Na dúvida, meça: Quando o Índice Ajuda.