Um banco de dados refere-se ao conjunto de arquivos físicos gerenciados pelo sistema operacional, enquanto a instância consiste nos processos e estruturas de memória que interagem com esses arquivos. Por exemplo, o diretório /var/lib/mysql/ armazena os arquivos de banco de dados de uma instância MySQL, com cada subdiretório representando um banco de dados distinto. Os arquivos de tabelas do InnoDB utilizam a extensão .ibd.

Estrutura do MySQL

A arquitetura do MySQL é organizada em camadas:

• Conectores: Interfaces para linguagens como JDBC e ODBC.
• Camada do Servidor: Gerenciamento de conexões, autenticação, threads, cache e ferramentas de backup.
• Processamento SQL: Compilação e otimização de consultas.
• Motores de Armazenamento: Implementados como plugins, responsáveis pelo gerenciamento de memória, índices e armazenamento físico.
• Sistema de Arquivos: Armazenamento final dos dados, dependendo do motor escolhido.

Cada motor é otimizado para cenários específicos. Importante: os motores são configurados por tabela, não por banco de dados.

Motor InnoDB

• Suporte a transações ACID com bloqueio em nível de linha, ideal para OLTP.
• Controle de concorrência por versões múltiplas (MVCC).
• Quatro níveis de isolamento padrão SQL, com REPEATABLE READ como padrão.
• Armazenamento em arquivos .ibd com índice clusterizado (organizado pela chave primária).

Outros Motores

MyISAM:

• Sem suporte a transações; bloqueio a nível de tabela; adequado para OLAP.
• Suporta índices full-text.
• Dados em .MYD, índices em .MYI.

Memory:

• Dados armazenados em memória volátil, perdidos após reinicialização.
• Utilizado para resultados intermediários de consultas.

Archive:

• Suporta apenas SELECT e INSERT.
• Compactação zlib para armazenamento eficiente.
• Bloqueio de linha não transacional; ideal para logs e arquivamento.

Arquitetura do InnoDB

O InnoDB é o motor preferido para aplicações OLTP. Sua memória pool gerencia cache de dados, buffers de redo logs e estruturas internas. Threads de fundo incluem:

• Master Thread: Responsável por flush de páginas sujas, merge de buffers e recuperação de páginas UNDO.
• IO Threads: Gerenciam operações assíncronas (AIO), com tipos: leitura, escrita, buffer de inserção e log IO.
• Purge Thread: Limpa registros UNDO de transações confirmadas (antes da versão 1.1, parte do Master Thread).
• Page Cleaner Thread (InnoDB 1.2+): Separa o flush de páginas sujas do Master Thread.

Para verificar o status das IO Threads:

SHOW ENGINE INNODB STATUS\G;

Buffer Pool

O buffer pool armazena dados em memória para melhorar desempenho. Quando dados são modificados, são atualizados no buffer antes de serem escritos no disco via checkpoint. Configurações como innodb_buffer_pool_instances distribuem páginas entre múltiplas instâncias para reduzir contenção.

Exemplo de consulta para métricas do buffer pool:

SELECT 
  BUFFER_POOL_ID AS pool_id,
  POOL_SIZE AS total_pages,
  FREE_BUFFERS AS free_pages,
  DATABASE_PAGES AS used_pages
FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS\G;

A LRU (Least Recent Used) é otimizada pelo InnoDB. Páginas novas são inseridas no midpoint, não no início, para evitar que dados de varredura total desalojem páginas frequentemente usadas. Parâmetros como innodb_old_blocks_pct (padrão 37%) definem a porcentagem da LRU considerada "old".

Checkpoint e Redo Log

Checkpoints resolvem problemas como tempo de recuperação após falhas. O LSN (Log Sequence Number) é usado para rastrear versões. Dois tipos de checkpoints:

• Sharp Checkpoint: Executado durante o shutdown, flusha todas as páginas sujas.
• Fuzzy Checkpoint: Executado durante o funcionamento, flushando partes das páginas sujas.

Parâmetros como innodb_io_capacity e innodb_max_dirty_pages_pct controlam o comportamento do flush.

Características Chave do InnoDB

Change Buffer:

Extensão do Insert Buffer, suportando operações de inserção, exclusão e atualização. Parâmetros como innodb_change_buffering (valores: inserts, deletes, etc.) e innodb_change_buffer_max_size (máximo 50% do buffer pool) controlam seu comportamento.

Exemplo de tabela com índice secundário:

CREATE TABLE example_data (
  id INT AUTO_INCREMENT PRIMARY KEY,
  content VARCHAR(255),
  INDEX idx_content (content)
);

Double Write Buffer:

Protege conttra escritas parciais durante flush de páginas sujas. Dados são primeiro escritos no buffer de double write, depoiss no espaço de tabela. Pode ser desativado em sistemas com sistemas de arquivos que evitam escritas parciais, mas não recomendado para servidores mestre.

Adaptive Hash Index (AHI):

Cria índices hash automaticamente para páginas hot. Requer acesso repetido com o mesmo padrão. Desativado por padrão em alguns casos.

Async I/O (AIO):

Permite operações assíncronas e merge de I/O. Suporte nativo desde o InnoDB 1.1.x, dependendo do sistema operacional.

Flush Neighbors:

Quando flusha uma página suja, também flusha páginas adjacentes. Pode ser desativado via innodb_flush_neighbors (padrão desligado em sistemas modernos).