SQL Quando o Índice Ajuda

Na aula Índices (CREATE INDEX) você aprendeu a criar um índice, e na EXPLAIN QUERY PLAN a ver se o banco usa ele. Agora a pergunta é outra: vale a pena? Nesta aula a Ana monta um banco com um milhão de vendas, mede o tempo de uma consulta antes e depois do índice, descobre quanto o índice custa e vê os casos em que ele é ignorado, ou até deixa tudo mais lento.

A ideia: o índice do livro tem um preço

O índice remissivo no fim do livro de receitas economiza muito tempo de quem procura "cenoura". Mas ele não é de graça:

• ocupa páginas a mais no livro;
• a cada receita nova, alguém tem de atualizar o índice, além de escrever a receita;
• e para uma palavra que aparece em metade das páginas (como "sal"), ele não ajuda nada: é mais fácil folhear o livro do que ir e voltar do índice quinhentas vezes.

No banco é igual. Um índice compensa quando a consulta quer poucas linhas de muitas, e quando a tabela é mais lida do que escrita. O resto desta aula mostra isso com números.

1. Um banco grande para medir

Com os 90 pedidos da Loja Aurora, qualquer consulta é instantânea, com ou sem índice. Para ver diferença, a Ana precisa de muitas linhas. Ela cria, num arquivo separado (vendas.db, para não mexer na loja), uma tabela de vendas e a enche com um milhão de linhas inventadas, geradas por uma CTE recursiva (aula CTE Recursiva):

$ sqlite3 vendas.db
SQLite version 3.50.4 2025-07-30 19:33:53
Enter ".help" for usage hints.
sqlite> CREATE TABLE vendas (
(x1...>   id INTEGER PRIMARY KEY,
(x1...>   cliente_id INTEGER,
(x1...>   produto_id INTEGER,
(x1...>   data TEXT,
(x1...>   pago INTEGER,
(x1...>   valor REAL
(x1...> );
sqlite> .timer on
sqlite> INSERT INTO vendas
   ...> WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 1000000)
   ...> SELECT i, (i * 7919) % 50000 + 1, (i * 31) % 45 + 1,
   ...>        date('2021-01-01', '+' || (i % 2000) || ' days'),
   ...>        i % 2, round((i * 37) % 50000 / 100.0, 2)
   ...> FROM n;
Run Time: real 1.479 user 1.383718 sys 0.035837
sqlite> SELECT COUNT(*) FROM vendas;
1000000
Run Time: real 0.012 user 0.004625 sys 0.007986

(x1...> e ...>

O sqlite3 mostra esses prompts de continuação enquanto o comando não terminou (falta o ;). O (x1 avisa que há um parêntese aberto.

.timer on

Comando de ponto (aula Comandos de Ponto): depois de cada comando, mostra quanto tempo ele levou.

WITH RECURSIVE n(i) AS (…)

Gera os números de 1 a 1.000.000, um por linha.

(i * 7919) % 50000 + 1

Um cliente_id entre 1 e 50.000, "embaralhado" por uma conta. Não é sorteio: rodando de novo, sai igual, e os números desta aula podem ser conferidos.

date('2021-01-01', '+' || (i % 2000) || ' days')

Uma data entre 2021 e 2026 (aula Funções de Data).

i % 2

A coluna pago: 0 ou 1, metade das vendas de cada.

Run Time: real 1.479

O tempo: real é o tempo de relógio, em segundos; user e sys são o tempo de processador gasto pelo programa e pelo sistema. Olhe o real.

Um milhão de linhas em um segundo e meio. Cada cliente tem 20 vendas: 1.000.000 dividido por 50.000 clientes.

No editor, a mesma ideia funciona com dez mil linhas (cada Rodar tem um limite de cerca de 4 segundos, e um milhão de linhas não cabe nele). É assim que os exemplos desta aula mostram os planos:

CREATE TABLE vendas (id INTEGER PRIMARY KEY, cliente_id INTEGER, produto_id INTEGER,
                     data TEXT, pago INTEGER, valor REAL);
INSERT INTO vendas
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 10000)
SELECT i, (i * 7919) % 50000 + 1, (i * 31) % 45 + 1,
       date('2021-01-01', '+' || (i % 2000) || ' days'),
       i % 2, round((i * 37) % 50000 / 100.0, 2)
FROM n;

SELECT * FROM vendas LIMIT 3;

2. Antes e depois do índice

A Ana quer o total comprado pelo cliente 4242. Sem índice, o plano é SCAN vendas: ler o milhão de linhas para achar as 20 dele. Ela roda duas vezes, para ter certeza do tempo:

sqlite> SELECT SUM(valor) FROM vendas WHERE cliente_id = 4242;
7128.6
Run Time: real 0.046 user 0.037990 sys 0.007902
sqlite> SELECT SUM(valor) FROM vendas WHERE cliente_id = 4242;
7128.6
Run Time: real 0.047 user 0.042404 sys 0.004080

Uns 46 milésimos de segundo. Parece pouco, mas é para uma busca; um site que faz essa consulta mil vezes por minuto gasta uns 46 segundos de processador por minuto só nela. Agora, com o índice:

$ sqlite3 vendas.db
SQLite version 3.50.4 2025-07-30 19:33:53
Enter ".help" for usage hints.
sqlite> .timer on
sqlite> CREATE INDEX idx_vendas_cliente ON vendas(cliente_id);
Run Time: real 0.400 user 0.365545 sys 0.008074
sqlite> SELECT SUM(valor) FROM vendas WHERE cliente_id = 4242;
7128.6
Run Time: real 0.000 user 0.000130 sys 0.000003
sqlite> SELECT SUM(valor) FROM vendas WHERE cliente_id = 4242;
7128.6
Run Time: real 0.000 user 0.000028 sys 0.000001
sqlite> .timer off
sqlite> EXPLAIN QUERY PLAN SELECT SUM(valor) FROM vendas WHERE cliente_id = 4242;
QUERY PLAN
`--SEARCH vendas USING INDEX idx_vendas_cliente (cliente_id=?)

real 0.000: menos de um milésimo de segundo, abaixo do que o .timer consegue mostrar. Pelo tempo de processador (user: 0,038 s antes, 0,00013 s depois), a busca ficou centenas de vezes mais rápida. O plano mudou de SCAN para SEARCH … USING INDEX: em vez de ler um milhão de linhas, o SQLite achou no índice as 20 do cliente e leu só essas.

Esse é o caso ideal do índice: uma tabela grande e uma busca que quer poucas linhas dela (20 de um milhão).

3. O preço do índice

Espaço no disco

O índice é uma cópia ordenada da coluna, com o endereço de cada linha. Ele fica no mesmo arquivo do banco:

$ ls -lh vendas.db
-rw-r--r-- 1 ana ana 33M out 18 11:17 vendas.db
$ ls -lh vendas.db
-rw-r--r-- 1 ana ana 44M out 18 11:19 vendas.db

O primeiro ls foi feito antes de criar o idx_vendas_cliente; o segundo, logo depois. O arquivo cresceu de 33 para 44 megabytes. Quanto ocupa cada parte, a tabela virtual dbstat do SQLite conta (aqui já com mais dois índices, que você vai ver nas seções seguintes):

sqlite> SELECT name, round(SUM(pgsize) / 1048576.0, 1) AS mb FROM dbstat GROUP BY name ORDER BY mb DESC;
vendas|32.9
idx_vendas_data|18.2
idx_vendas_cliente|10.8
idx_vendas_pago|8.6
sqlite_schema|0.0

Três índices juntos (37,6 MB) já ocupam mais que a própria tabela (32,9 MB). O dbstat também funciona no editor, com a tabela de dez mil linhas:

CREATE TABLE vendas (id INTEGER PRIMARY KEY, cliente_id INTEGER, produto_id INTEGER,
                     data TEXT, pago INTEGER, valor REAL);
INSERT INTO vendas
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 10000)
SELECT i, (i * 7919) % 50000 + 1, (i * 31) % 45 + 1,
       date('2021-01-01', '+' || (i % 2000) || ' days'),
       i % 2, round((i * 37) % 50000 / 100.0, 2)
FROM n;
CREATE INDEX idx_vendas_pago ON vendas(pago);

SELECT name, SUM(pgsize) AS bytes
FROM dbstat
WHERE name LIKE '%vendas%'
GROUP BY name;

INSERT mais lento

A cada linha nova, o SQLite grava a linha na tabela e uma entrada em cada índice, no lugar certo da ordem. A Ana copiou o milhão de vendas para duas tabelas vazias: uma sem índice e outra com três índices (em cliente_id, data e pago):

sqlite> CREATE TABLE sem_indice AS SELECT * FROM vendas WHERE 0;
sqlite> CREATE TABLE com_indices AS SELECT * FROM vendas WHERE 0;
sqlite> CREATE INDEX ci_cliente ON com_indices(cliente_id);
sqlite> CREATE INDEX ci_data ON com_indices(data);
sqlite> CREATE INDEX ci_pago ON com_indices(pago);
sqlite> .timer on
sqlite> INSERT INTO sem_indice SELECT * FROM vendas;
Run Time: real 0.309 user 0.219081 sys 0.041150
sqlite> INSERT INTO com_indices SELECT * FROM vendas;
Run Time: real 7.627 user 4.260789 sys 3.274614

De 0,31 segundo para 7,63 segundos: quase vinte e cinco vezes mais lento, na máquina da Ana. (O WHERE 0 é um truque: copia só a estrutura da tabela, sem nenhuma linha.) Por isso, quem vai carregar muitos dados de uma vez costuma apagar os índices, carregar e criar os índices de novo no fim: criar um índice numa tabela cheia (0,40 s, na seção 2) é bem mais rápido que mantê-lo linha por linha.

4. Quando o índice não é usado

O índice guarda os valores da coluna como estão escritos, em ordem. Ele só serve quando o WHERE compara a coluna pura com um valor. Os casos abaixo são os mais comuns de "criei o índice e nada mudou".

4.1. Função na coluna

A Ana quer as vendas de março de 2022 e escreve, de um jeito bem natural, "os 7 primeiros caracteres da data são 2022-03":

CREATE TABLE vendas (id INTEGER PRIMARY KEY, cliente_id INTEGER, produto_id INTEGER,
                     data TEXT, pago INTEGER, valor REAL);
INSERT INTO vendas
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 10000)
SELECT i, (i * 7919) % 50000 + 1, (i * 31) % 45 + 1,
       date('2021-01-01', '+' || (i % 2000) || ' days'),
       i % 2, round((i * 37) % 50000 / 100.0, 2)
FROM n;
CREATE INDEX idx_vendas_data ON vendas(data);

EXPLAIN QUERY PLAN
SELECT COUNT(*), SUM(valor) FROM vendas WHERE substr(data, 1, 7) = '2022-03';

EXPLAIN QUERY PLAN
SELECT COUNT(*), SUM(valor) FROM vendas WHERE data >= '2022-03-01' AND data < '2022-04-01';

Com o substr(): SCAN vendas. O índice tem as datas inteiras em ordem ("2022-03-01", "2022-03-02"…), não os pedaços que o substr() calcula. Para saber o substr() de cada linha, o SQLite tem de ler todas. Com a faixa de datas (de 1º de março até antes de 1º de abril), a coluna fica pura e o plano é SEARCH … (data>? AND data<?). As duas consultas dão o mesmo resultado:

CREATE TABLE vendas (id INTEGER PRIMARY KEY, cliente_id INTEGER, produto_id INTEGER,
                     data TEXT, pago INTEGER, valor REAL);
INSERT INTO vendas
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 10000)
SELECT i, (i * 7919) % 50000 + 1, (i * 31) % 45 + 1,
       date('2021-01-01', '+' || (i % 2000) || ' days'),
       i % 2, round((i * 37) % 50000 / 100.0, 2)
FROM n;

SELECT COUNT(*), SUM(valor) FROM vendas WHERE substr(data, 1, 7) = '2022-03';
SELECT COUNT(*), SUM(valor) FROM vendas WHERE data >= '2022-03-01' AND data < '2022-04-01';

No milhão de vendas, na máquina da Ana:

sqlite> CREATE INDEX idx_vendas_data ON vendas(data);
Run Time: real 0.381 user 0.318901 sys 0.028327
sqlite> SELECT COUNT(*), SUM(valor) FROM vendas WHERE substr(data, 1, 7) = '2022-03';
15500|3847665.0
Run Time: real 0.081 user 0.068671 sys 0.012100
sqlite> SELECT COUNT(*), SUM(valor) FROM vendas WHERE data >= '2022-03-01' AND data < '2022-04-01';
15500|3847665.0
Run Time: real 0.026 user 0.018327 sys 0.007816

O mesmo resultado, três vezes mais rápido. O mesmo vale para lower(cidade) = 'teresina', strftime('%Y', data) = '2025' e qualquer outra função em volta da coluna.

CREATE INDEX idx_pedidos_data ON pedidos(data);

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM pedidos WHERE substr(data, 1, 4) = '2025';

SELECT COUNT(*) FROM pedidos WHERE substr(data, 1, 4) = '2025';

CREATE INDEX idx_pedidos_data ON pedidos(data);

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM pedidos WHERE data >= '2025-01-01' AND data < '2026-01-01';

SELECT COUNT(*) FROM pedidos WHERE data >= '2025-01-01' AND data < '2026-01-01';

CREATE INDEX idx_pedidos_ano ON pedidos(substr(data, 1, 4));

EXPLAIN QUERY PLAN
SELECT * FROM pedidos WHERE substr(data, 1, 4) = '2025';

4.2. LIKE com % no começo (e o problema das maiúsculas)

Com um índice no nome dos clientes, veja três buscas:

CREATE INDEX idx_clientes_nome ON clientes(nome);

EXPLAIN QUERY PLAN SELECT * FROM clientes WHERE nome = 'Tiago Fontes';
EXPLAIN QUERY PLAN SELECT * FROM clientes WHERE nome LIKE '%Lima';
EXPLAIN QUERY PLAN SELECT * FROM clientes WHERE nome LIKE 'Ana%';

1. nome = 'Tiago Fontes': SEARCH, como esperado.
2. LIKE '%Lima': SCAN. Um nome que termina com "Lima" pode começar com qualquer letra; num índice em ordem alfabética, ele pode estar em qualquer lugar. É como procurar no dicionário todas as palavras que terminam com "ção": o dicionário não ajuda.
3. LIKE 'Ana%': também SCAN, e esse surpreende. O começo é conhecido, então o índice deveria ajudar. O problema é que, no SQLite, o LIKE ignora maiúsculas (aula LIKE): 'ana%' também acha "Ana". E o índice comum está em ordem que diferencia maiúsculas ("Ana" e "ana" ficam longe um do outro). Por isso o SQLite não confia no índice.

Há dois consertos. Um é escrever a faixa à mão, que diferencia maiúsculas: nome >= 'Ana' AND nome < 'Anb'. O outro é criar o índice na mesma regra do LIKE, com COLLATE NOCASE ("sem diferenciar maiúsculas"):

CREATE INDEX idx_clientes_nome_nc ON clientes(nome COLLATE NOCASE);

EXPLAIN QUERY PLAN SELECT * FROM clientes WHERE nome LIKE 'Ana%';
SELECT nome FROM clientes WHERE nome LIKE 'ana%';

Agora o plano é SEARCH … (nome>? AND nome<?): o SQLite transformou o LIKE 'Ana%' numa faixa do índice. E a busca com 'ana%' minúsculo continua achando a Ana Beatriz e a Ana Carvalho. O LIKE '%Lima', com % no começo, não tem conserto com índice comum; para isso existe a busca de texto (aula Busca de Texto (FTS5)).

4.3. Conta na coluna

CREATE INDEX idx_produtos_preco ON produtos(preco);

EXPLAIN QUERY PLAN SELECT * FROM produtos WHERE preco * 1.1 > 50;
EXPLAIN QUERY PLAN SELECT * FROM produtos WHERE preco > 50 / 1.1;

"Os produtos que, com 10% de aumento, passam de 50 reais." Escrito como preco * 1.1 > 50, a conta está na coluna, e é SCAN. Passando a conta para o outro lado (preco > 50 / 1.1), a coluna fica pura e é SEARCH. É a mesma regra da função: a coluna sozinha de um lado, o valor do outro.

4.4. Índice composto fora de ordem

Um índice pode ter várias colunas. O índice (estado, cidade) é como uma lista telefônica ordenada por estado e, dentro de cada estado, por cidade:

CREATE INDEX idx_clientes_estado_cidade ON clientes(estado, cidade);

EXPLAIN QUERY PLAN SELECT * FROM clientes WHERE estado = 'CE' AND cidade = 'Fortaleza';
EXPLAIN QUERY PLAN SELECT * FROM clientes WHERE estado = 'CE';
EXPLAIN QUERY PLAN SELECT * FROM clientes WHERE cidade = 'Fortaleza';

A regra: o índice composto serve para buscas que usam as colunas a partir da primeira, da esquerda para a direita. Na hora de criar, ponha primeiro a coluna que aparece em mais consultas com =.

5. Quando o índice atrapalha

A coluna pago tem só dois valores, 0 e 1, cada um em metade das vendas. A Ana criou um índice nela e mediu o total das vendas pagas, com o índice e proibindo o índice (NOT INDEXED, "não indexado", obriga o SQLite a ler a tabela direto):

sqlite> CREATE INDEX idx_vendas_pago ON vendas(pago);
Run Time: real 0.269 user 0.213390 sys 0.026817
sqlite> SELECT SUM(valor) FROM vendas WHERE pago = 1;
125000000.0
Run Time: real 0.140 user 0.119608 sys 0.019961
sqlite> SELECT SUM(valor) FROM vendas NOT INDEXED WHERE pago = 1;
125000000.0
Run Time: real 0.055 user 0.051250 sys 0.003889

Com o índice: 0,140 s. Sem: 0,055 s. O índice deixou a consulta duas vezes e meia mais lenta, na máquina da Ana. O motivo é o "sal" do livro de receitas: para 500 mil linhas, o SQLite vai ao índice, pega o endereço, pula para a linha na tabela, volta ao índice… quinhentas mil vezes. Ler a tabela de uma vez, em ordem, é mais rápido.

E o SQLite escolheu o índice mesmo assim:

sqlite> EXPLAIN QUERY PLAN SELECT SUM(valor) FROM vendas WHERE pago = 1;
QUERY PLAN
`--SEARCH vendas USING INDEX idx_vendas_pago (pago=?)

Sem informação sobre os dados, ele supõe que um índice com = pega poucas linhas. Nem depois de um ANALYZE (que junta estatísticas sobre os índices; aula VACUUM e ANALYZE) o plano mudou, na versão 3.50.4. A lição é simples: não crie índice em coluna com poucos valores diferentes (sim/não, 0/1, sexo, status com 3 opções), a não ser que você busque quase sempre o valor raro.

No editor, você confere os dois planos. Além do NOT INDEXED, um + na frente da coluna também faz o SQLite ignorar o índice (a coluna deixa de ser "pura"):

CREATE TABLE vendas (id INTEGER PRIMARY KEY, cliente_id INTEGER, produto_id INTEGER,
                     data TEXT, pago INTEGER, valor REAL);
INSERT INTO vendas
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 10000)
SELECT i, (i * 7919) % 50000 + 1, (i * 31) % 45 + 1,
       date('2021-01-01', '+' || (i % 2000) || ' days'),
       i % 2, round((i * 37) % 50000 / 100.0, 2)
FROM n;
CREATE INDEX idx_vendas_pago ON vendas(pago);

EXPLAIN QUERY PLAN SELECT SUM(valor) FROM vendas WHERE pago = 1;
EXPLAIN QUERY PLAN SELECT SUM(valor) FROM vendas NOT INDEXED WHERE pago = 1;
EXPLAIN QUERY PLAN SELECT SUM(valor) FROM vendas WHERE +pago = 1;

CREATE TABLE vendas (id INTEGER PRIMARY KEY, cliente_id INTEGER, produto_id INTEGER,
                     data TEXT, pago INTEGER, valor REAL);
INSERT INTO vendas
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 10000)
SELECT i, (i * 7919) % 50000 + 1, (i * 31) % 45 + 1,
       date('2021-01-01', '+' || (i % 2000) || ' days'),
       i % 2, round((i * 37) % 50000 / 100.0, 2)
FROM n;
CREATE INDEX idx_vendas_cli_data ON vendas(cliente_id, data);

EXPLAIN QUERY PLAN
SELECT * FROM vendas WHERE cliente_id = 4242 AND data >= '2024-01-01';

6. Roteiro: criar ou não criar?

1. A tabela é grande? Com algumas centenas de linhas, qualquer consulta é instantânea; o índice só ocupa espaço.
2. A consulta é lenta e frequente? Comece pela consulta, não pelo índice. Rode-a com .timer on e peça o plano.
3. O plano mostra SCAN numa tabela grande, buscando poucas linhas? Crie o índice na coluna do WHERE (ou do JOIN).
4. A coluna tem muitos valores diferentes? cliente_id, data, email: sim. pago, vip: não.
5. O WHERE usa a coluna pura? Tire funções e contas de cima da coluna, e o % do começo do LIKE.
6. Meça de novo e confira o plano. Se não ficou mais rápido, apague o índice (DROP INDEX): ele vai deixar todo INSERT mais lento à toa.

Formas diferentes de fazer a mesma coisa

Para a mesma busca, "nomes que começam com Ana", e para o mesmo controle do índice, há várias escritas:

Quando dá errado

CREATE INDEX idx_c ON clientes(cidade);
CREATE INDEX idx_c ON clientes(cidade);

SELECT * FROM clientes INDEXED BY idx_cidade WHERE cidade = 'Picos';

CREATE UNIQUE INDEX idx_clientes_cidade ON clientes(cidade);

-- PostgreSQL (ilustrativo)
CREATE INDEX idx_clientes_cidade_min ON clientes (lower(cidade));

-- MySQL (ilustrativo)
CREATE INDEX idx_clientes_cidade_min ON clientes ((lower(cidade)));

Referência rápida

Exercícios

Missão final: o LIKE que usa índice

CREATE INDEX idx_clientes_nome ON clientes(nome);

EXPLAIN QUERY PLAN
SELECT nome FROM clientes WHERE nome LIKE 'ana%';

SELECT nome FROM clientes WHERE nome LIKE 'ana%';

CREATE INDEX idx_clientes_nome ON clientes(nome COLLATE NOCASE);

EXPLAIN QUERY PLAN
SELECT nome FROM clientes WHERE nome LIKE 'ana%';

SELECT nome FROM clientes WHERE nome LIKE 'ana%';

Resumo

• Para medir, gere muitas linhas com uma CTE recursiva e use .timer on. Compare os tempos entre si; o número exato depende da máquina.
• Na máquina da Ana, achar 20 vendas em um milhão caiu de 0,046 s (SCAN) para menos de 0,001 s (SEARCH) com o índice. Esse é o caso ideal: tabela grande, poucas linhas.
• O índice custa espaço (33 MB → 44 MB) e deixa o INSERT mais lento (0,31 s → 7,63 s com três índices).
• O índice só é usado com a coluna pura: nada de função (substr, lower), conta ou LIKE '%…'. Troque por faixas (data >= … AND data < …).
• LIKE 'x%' só usa índice criado com COLLATE NOCASE. O índice composto vale a partir da primeira coluna.
• Em coluna com poucos valores (como pago), o índice pode deixar a consulta mais lenta; NOT INDEXED e +col desligam o índice.