InnoDB por Dentro

Conceito

A hierarquia física

tablespace (arquivo .ibd)
└── extent (1 MB = 64 páginas)
    └── página (16 KB, padrão)      ← a unidade de I/O do InnoDB
        └── registros (linhas)

Tudo no InnoDB acontece em páginas de 16 KB. Ler uma linha significa ler 16 KB; sujar uma linha significa sujar 16 KB. Essa granularidade explica várias regras práticas — por exemplo, por que uma linha "gorda" (com muitas colunas grandes) prejudica a densidade e o cache.

O índice clusterizado, em detalhe

        ┌─────────── raiz (páginas internas: só chaves + ponteiros) ───────────┐
        │                                                                     │
   [nó interno]                                                        [nó interno]
        │                                                                     │
  ┌─────┴─────┐                                                        ┌──────┴─────┐
[FOLHA]     [FOLHA]  ← as folhas contêm A LINHA INTEIRA, ordenada pela PK
   └───────────┘        (e são ligadas entre si: varredura por faixa é sequencial)

Se você não declarar PK, o InnoDB usa o primeiro índice UNIQUE NOT NULL; se não houver, cria uma chave interna oculta de 6 bytes (DB_ROW_ID) — global ao servidor, o que a torna um ponto de contenção em escrita concorrente. Sempre declare a PK.

Índice secundário guarda (colunas do índice, valor da PK):

CREATE INDEX idx_email ON users (email);   -- guarda (email, id)

SELECT id   FROM users WHERE email = 'x';  -- 1 percurso: index-only, a PK já está lá
SELECT nome FROM users WHERE email = 'x';  -- 2 percursos: idx_email → PK → linha

O segundo percurso tem nome: bookmark lookup. Evitá-lo é o objetivo do covering index (4. Índices e o Otimizador).

Buffer pool: o único parâmetro que realmente importa

innodb_buffer_pool_size é o cache de páginas — e, diferente do Postgres, ele deve ser grande: 60–75% da RAM numa máquina dedicada. O InnoDB não conta com o page cache do SO como segundo nível; ele gerencia o próprio cache, com O_DIRECT.

O LRU do InnoDB tem uma sutileza importante, a inserção no ponto médio:

[ ─────── young (5/8) ─────── | ─────── old (3/8) ─────── ]
                              ↑
                 páginas novas entram AQUI, não no topo

Por quê: uma varredura sequencial de uma tabela de 500 GB traria 500 GB de páginas "novas", expulsando todo o cache quente. Entrando no meio, elas só são promovidas se forem acessadas de novo depois de innodb_old_blocks_time (1 s). Uma varredura acidental deixa de destruir o cache.

Change buffer: por que UPDATE no MySQL pode ser mais barato

Quando um INSERT/UPDATE/DELETE precisa alterar uma página de índice secundário não único que não está no buffer pool, o InnoDB não lê a página do disco. Ele registra a alteração no change buffer e a aplica depois (merge), quando a página for lida por outro motivo.

Isso torna escrita em tabelas grandes com muitos índices secundários muito mais barata do que seria — e é uma das razões pelas quais o InnoDB aguenta bem carga de escrita em tabelas maiores que a RAM. Não vale para índices únicos (é preciso verificar a unicidade na hora, e para isso a página tem que ser lida).

Row formats: o que acontece com colunas grandes

Regra da árvore B+: cada página precisa caber pelo menos 2 registros, ou seja, uma linha não pode passar de ~8 KB dentro da página. TEXT/BLOB/JSON grandes vão para páginas de overflow — o que significa um I/O extra sempre que você seleciona essas colunas. É o argumento técnico contra SELECT * numa tabela com JSON gordo.

Doublewrite buffer

Uma página de 16 KB não é gravada atomicamente pelo disco (setor de 512 B/4 KB). Uma queda no meio produziria uma página "rasgada". O InnoDB grava toda página duas vezes: antes no doublewrite buffer (área sequencial), depois no lugar definitivo. Na recuperação, se uma página estiver corrompida, ele a restaura da cópia.

É o equivalente ao full_page_writes do Postgres — e tem o mesmo efeito colateral: amplificação de escrita. Em hardware com escrita atômica de 16 KB, pode ser desligado (com cautela).

Trade-offs

Índice clusterizado otimiza leitura por PK e faz da PK uma decisão de performance. PK CHAR(36) são 36 bytes replicados em cada entrada de cada índice secundário; PK aleatória insere no meio da árvore, causando page splits (a página cheia é dividida, deixando as duas metades ~50% ocupadas, o que dobra o tamanho efetivo do índice e mata a densidade do cache).

Buffer pool grande é ótimo e demora a esquentar. Após um restart, o desempenho é ruim até o cache reencher. innodb_buffer_pool_dump_at_shutdown + innodb_buffer_pool_load_at_startup (padrão ligado nas versões modernas) salvam a lista de páginas quentes e a recarregam.

Change buffer acelera escrita e adia leitura. O merge acontece depois — e num pico de leitura você paga a conta acumulada. Em SSD com muita RAM, innodb_change_buffer_max_size menor às vezes é melhor.

O trade-off central: o InnoDB é um motor otimizado para acesso por chave primária e faixas dela. Tudo que segue esse padrão é rapidíssimo; tudo que exige outra ordem física paga um percurso extra de árvore. Modelar para o InnoDB é escolher a PK como se ela fosse o layout do disco — porque é.

Exemplo prático

O custo real de uma PK aleatória

-- Simulação do padrão de inserção
-- A) AUTO_INCREMENT: sempre insere na página mais à direita
--    → páginas ~93% cheias, zero page split, I/O sequencial
-- B) UUID v4:        insere em posição aleatória
--    → page splits constantes, páginas ~50% cheias, I/O aleatório

CREATE TABLE bom (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uuid BINARY(16) NOT NULL,
  criado_em DATETIME(6) NOT NULL,
  UNIQUE KEY uk_uuid (uuid)            -- id externo opaco, se a API precisar
) ENGINE=InnoDB;

-- Alternativa moderna: UUID v7 como PK (ordenável por tempo)
CREATE TABLE tambem_bom (
  id BINARY(16) PRIMARY KEY,           -- UUID v7 em binário: 16 B, não 36
  criado_em DATETIME(6) NOT NULL
) ENGINE=InnoDB;

Inspecionar o motor

-- tamanho de dado vs índice por tabela
SELECT table_name,
       ROUND(data_length/1024/1024)  AS dados_mb,
       ROUND(index_length/1024/1024) AS indices_mb,
       ROUND(data_free/1024/1024)    AS livre_mb,   -- fragmentação
       table_rows
  FROM information_schema.tables
 WHERE table_schema = DATABASE()
 ORDER BY data_length + index_length DESC LIMIT 20;

-- eficiência do buffer pool (queira > 99%)
SELECT ROUND(100 * (1 - (
  SELECT variable_value FROM performance_schema.global_status
   WHERE variable_name = 'Innodb_buffer_pool_reads') / (
  SELECT variable_value FROM performance_schema.global_status
   WHERE variable_name = 'Innodb_buffer_pool_read_requests')), 2) AS hit_pct;

-- panorama do motor: deadlocks recentes, fila de I/O, LSN, change buffer
SHOW ENGINE INNODB STATUS\G

Baseline de configuração (16 GB RAM, SSD, dedicado)

[mysqld]
innodb_buffer_pool_size        = 10G     # 60-75% da RAM
innodb_buffer_pool_instances   = 8       # reduz contenção de mutex
innodb_redo_log_capacity       = 4G      # 8.0.30+ substitui log_file_size/files
innodb_flush_log_at_trx_commit = 1       # 1 = ACID. 2 = mais rápido, perde ~1s no crash do SO
innodb_flush_method            = O_DIRECT
innodb_io_capacity             = 2000    # SSD
innodb_io_capacity_max         = 4000
innodb_flush_neighbors         = 0       # 0 em SSD (não faz sentido agrupar vizinhos)
innodb_file_per_table          = ON

character_set_server           = utf8mb4
collation_server               = utf8mb4_0900_ai_ci
sql_mode = 'STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION'

max_connections                = 500     # thread por conexão: mais folga que no Postgres

Como isso falha em produção

Aprofundamento aplicado

O custo da chave primária se propaga

As entradas de índices secundários incluem a chave primária necessária para localizar a linha no índice clusterizado. Por isso uma PK larga pode aumentar vários índices, reduzir entradas por página e ampliar o conjunto que precisa caber em cache. Referência: índices clusterizados e secundários.

Considere 10 milhões de linhas e quatro índices secundários: acrescentar 16 bytes à chave representa, em uma estimativa apenas dos bytes extras, até 640 milhões de bytes nas quatro cópias. O tamanho físico real depende de formato, compressão, preenchimento e demais campos. A conta serve para entender a multiplicação.

Trade-off: uma chave curta e crescente ajuda localidade, mas não resolve identidade global por si só. Uma chave distribuída pode simplificar geração e exigir mais cuidado com representação e padrão de inserção.

Exercício: compare duas tabelas equivalentes com PKs de larguras diferentes e os mesmos índices. Resultado esperado: compare espaço e leituras sob a mesma carga; evite atribuir toda diferença apenas à ordem das chaves.

Relacionado

• 1. MySQL
• 3. Redo, Undo e Locks
• 4. Índices e o Otimizador
• 6. DDL Online e Operação
• 4. MVCC, Tuplas e VACUUM
• 1. PostgreSQL vs MySQL vs Oracle
• 5. Índices

---

Parte de MOC MySQL · InnoDB (docs)