Como projetar schemas de banco de dados para alta leitura

1. Fundamentos da modelagem orientada a leitura

Projetar schemas para alta leitura exige uma mudança fundamental de mentalidade. Em sistemas OLTP tradicionais, a normalização é priorizada para evitar anomalias de escrita. Já em cenários de leitura intensa — como dashboards analíticos, feeds de conteúdo ou APIs de alto tráfego — o objetivo é minimizar o número de acessos ao disco e reduzir a complexidade das consultas.

Identificação de padrões de acesso é o primeiro passo. É necessário mapear quais consultas respondem por 80% da carga do sistema. Por exemplo, em um sistema de e-commerce, a consulta "buscar produtos por categoria com preço e estoque" pode ocorrer 10.000 vezes por minuto, enquanto a inserção de um novo produto ocorre 10 vezes por hora.

A diferença entre esquemas normalizados (3FN) e desnormalizados pode ser ilustrada com um exemplo prático:

-- Esquema normalizado (3FN)
CREATE TABLE pedidos (
    id INT PRIMARY KEY,
    cliente_id INT,
    data_pedido DATE
);

CREATE TABLE itens_pedido (
    id INT PRIMARY KEY,
    pedido_id INT,
    produto_id INT,
    quantidade INT,
    preco_unitario DECIMAL(10,2)
);

CREATE TABLE produtos (
    id INT PRIMARY KEY,
    nome VARCHAR(100),
    categoria VARCHAR(50)
);

-- Consulta para obter total por pedido (2 JOINs)
SELECT p.id, SUM(ip.quantidade * ip.preco_unitario) AS total
FROM pedidos p
JOIN itens_pedido ip ON p.id = ip.pedido_id
JOIN produtos pr ON ip.produto_id = pr.id
WHERE p.data_pedido >= '2024-01-01'
GROUP BY p.id;

-- Esquema desnormalizado para leitura
CREATE TABLE pedidos_com_totais (
    id INT PRIMARY KEY,
    cliente_id INT,
    data_pedido DATE,
    total_pedido DECIMAL(12,2),
    quantidade_itens INT,
    categorias TEXT -- 'eletronicos,livros'
);

-- Consulta sem JOINs
SELECT id, total_pedido FROM pedidos_com_totais
WHERE data_pedido >= '2024-01-01';

Métricas de desempenho relevantes incluem:
- Latência de query: tempo entre envio e recebimento dos dados
- Throughput: queries por segundo (QPS)
- Taxa de cache hit: percentual de dados servidos do buffer pool sem acesso a disco

2. Estratégias de desnormalização controlada

A desnormalização não significa abandonar toda estrutura. Trata-se de duplicação seletiva de colunas que são frequentemente acessadas juntas.

Duplicação seletiva de colunas para evitar JOINs críticos:

-- Em vez de JOIN com tabela de clientes a cada consulta
CREATE TABLE pedidos_com_cliente (
    id INT PRIMARY KEY,
    cliente_id INT,
    cliente_nome VARCHAR(100),
    cliente_email VARCHAR(200),  -- duplicado
    data_pedido DATE,
    valor_total DECIMAL(12,2)
);

Tabelas de resumo (aggregate tables) são essenciais para consultas agregadas:

CREATE TABLE resumo_vendas_diarias (
    data DATE PRIMARY KEY,
    total_vendas DECIMAL(14,2),
    quantidade_pedidos INT,
    ticket_medio DECIMAL(10,2)
);

-- Atualização programada a cada 5 minutos
INSERT INTO resumo_vendas_diarias (data, total_vendas, quantidade_pedidos, ticket_medio)
SELECT 
    DATE(data_pedido),
    SUM(valor_total),
    COUNT(*),
    AVG(valor_total)
FROM pedidos
WHERE data_pedido >= CURDATE() - INTERVAL 1 DAY
GROUP BY DATE(data_pedido)
ON DUPLICATE KEY UPDATE
    total_vendas = VALUES(total_vendas),
    quantidade_pedidos = VALUES(quantidade_pedidos),
    ticket_medio = VALUES(ticket_medio);

Materialized views com refresh periódico são ideais para dados que não precisam de atualização em tempo real:

CREATE MATERIALIZED VIEW mv_vendas_por_categoria AS
SELECT 
    pr.categoria,
    DATE(p.data_pedido) AS data_venda,
    SUM(ip.quantidade * ip.preco_unitario) AS receita
FROM pedidos p
JOIN itens_pedido ip ON p.id = ip.pedido_id
JOIN produtos pr ON ip.produto_id = pr.id
GROUP BY pr.categoria, DATE(p.data_pedido)
WITH DATA;

-- Refresh a cada hora
REFRESH MATERIALIZED VIEW mv_vendas_por_categoria;

3. Indexação avançada para alta performance

Índices compostos devem ser projetados para cobrir consultas específicas:

-- Consulta frequente: busca por status + data + cliente
CREATE INDEX idx_pedidos_status_data_cliente 
ON pedidos (status, data_pedido, cliente_id)
INCLUDE (valor_total, quantidade_itens);  -- covering index

Índices parciais para filtros de alta cardinalidade:

-- Apenas 5% dos pedidos estão com status 'pendente'
CREATE INDEX idx_pedidos_pendentes 
ON pedidos (data_pedido, prioridade)
WHERE status = 'pendente';

Estratégias de manutenção devem ser programadas em janelas de baixa carga:

-- Reorganizar índice (menos intensivo)
ALTER INDEX idx_pedidos_data REORGANIZE;

-- Reconstruir índice (mais intensivo, requer bloqueio)
ALTER INDEX idx_pedidos_data REBUILD WITH (ONLINE = ON);

4. Particionamento e sharding de dados

Particionamento horizontal por range para consultas por data:

CREATE TABLE logs_acesso (
    id BIGINT,
    usuario_id INT,
    data_acesso DATETIME,
    pagina VARCHAR(200),
    PRIMARY KEY (id, data_acesso)
) PARTITION BY RANGE (YEAR(data_acesso)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_futuro VALUES LESS THAN MAXVALUE
);

-- Partition pruning automático
SELECT * FROM logs_acesso 
WHERE data_acesso BETWEEN '2024-01-01' AND '2024-03-31';
-- Acessa apenas partição p2024

Sharding geográfico para redução de latência:

-- Aplicação roteia para o shard correto
-- Shard 1: servidor em São Paulo (clientes SP, RJ, MG)
-- Shard 2: servidor em Fortaleza (clientes NE)
-- Shard 3: servidor em Manaus (clientes NO)

-- Roteamento por hash do ID do cliente
shard_id = cliente_id % 3 + 1;

5. Uso de caches e camadas de leitura intermediárias

Cache de resultados frequentes com Redis:

-- Verificar cache antes de consultar banco
function buscarProduto(produtoId) {
    cacheKey = "produto:" + produtoId;
    produto = redis.get(cacheKey);

    if (produto == null) {
        produto = db.query("SELECT * FROM produtos WHERE id = ?", [produtoId]);
        redis.setex(cacheKey, 3600, JSON.stringify(produto)); // TTL de 1 hora
    }

    return JSON.parse(produto);
}

Cache de read models em vez de queries brutas:

-- Read model pré-formatado para a API
cacheKey = "dashboard:vendas:2024-03";
dashboardData = redis.get(cacheKey);

if (dashboardData == null) {
    dashboardData = db.query("SELECT * FROM resumo_vendas_diarias WHERE data BETWEEN ? AND ?", 
        ["2024-03-01", "2024-03-31"]);
    redis.setex(cacheKey, 300, JSON.stringify(dashboardData)); // TTL de 5 minutos
}

Estratégias de invalidação:

  • Write-through: atualiza cache e banco simultaneamente
  • Write-behind: atualiza cache primeiro, banco de forma assíncrona
  • TTL adaptativo: itens mais acessados têm TTL mais longo

6. Otimização de queries e plano de execução

Análise de EXPLAIN para identificar scans sequenciais:

EXPLAIN ANALYZE
SELECT p.id, p.valor_total 
FROM pedidos p 
WHERE p.data_pedido >= '2024-01-01' 
  AND p.status = 'entregue';

-- Saída esperada:
-- -> Index Range Scan on idx_pedidos_status_data (cost=12.3 rows=5000)
--    Index Cond: ((status = 'entregue') AND (data_pedido >= '2024-01-01'))

Uso de CTEs para reduzir processamento repetido:

WITH top_clientes AS (
    SELECT cliente_id, SUM(valor_total) as total_gasto
    FROM pedidos
    WHERE data_pedido >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    GROUP BY cliente_id
    ORDER BY total_gasto DESC
    LIMIT 100
)
SELECT c.nome, tc.total_gasto
FROM top_clientes tc
JOIN clientes c ON tc.cliente_id = c.id;

Ajuste de parâmetros no MySQL:

-- Aumentar buffer pool para 80% da RAM disponível
SET GLOBAL innodb_buffer_pool_size = 8589934592; -- 8GB

-- Aumentar sort buffer para consultas com ORDER BY
SET GLOBAL sort_buffer_size = 4194304; -- 4MB

7. Monitoramento e evolução contínua do schema

Métricas-chave a monitorar:

-- Cache hit ratio no MySQL
SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';

-- Cálculo: (read_requests - reads) / read_requests * 100
-- Ideal: > 99%

Identificação de slow queries:

-- Ativar slow query log no MySQL
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- queries acima de 1 segundo
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-queries.log';

-- Analisar com pt-query-digest
pt-query-digest /var/log/mysql/slow-queries.log

Migração de schema sem downtime:

-- Passo 1: Adicionar nova coluna como NULL
ALTER TABLE pedidos ADD COLUMN novo_status VARCHAR(20) NULL;

-- Passo 2: Preencher dados em lote (janela de baixa carga)
UPDATE pedidos SET novo_status = status WHERE novo_status IS NULL LIMIT 10000;

-- Passo 3: Criar trigger para manter sincronizado
CREATE TRIGGER sync_status
BEFORE INSERT ON pedidos
FOR EACH ROW
SET NEW.novo_status = NEW.status;

-- Passo 4: Migrar aplicação para usar novo_status

-- Passo 5: Remover coluna antiga (em manutenção programada)
ALTER TABLE pedidos DROP COLUMN status;

Referências