mysql-patterns

Padrões MySQL

Use este skill ao trabalhar com design de esquemas MySQL ou MariaDB, migrações, investigação de consultas lentas, transações no estilo de fila, pools de conexão ou configuração de banco de dados em produção. Prefira verificações exatas de versão antes de aplicar um padrão específico de funcionalidade porque MySQL e MariaDB divergiram em vários detalhes SQL.

Ativação

• Projetar tabelas, índices e restrições MySQL ou MariaDB
• Revisar migrações antes de executá-las em tabelas grandes de produção
• Depurar consultas lentas, esperas de bloqueio, impasses ou esgotamento de conexão
• Adicionar paginação por chave, upserts, busca textual completa, colunas JSON ou filas
• Configurar pools de conexão da aplicação, réplicas de leitura, TLS ou logs lentos

Verificação de Versão

Comece identificando o mecanismo e a versão:

SELECT VERSION();
SHOW VARIABLES LIKE 'version_comment';

Mantenha as orientações do MySQL e MariaDB separadas quando a sintaxe diferir:

• A documentação do MySQL indica aliases de linha como substitutos para VALUES(col) em ON DUPLICATE KEY UPDATE; VALUES(col) está obsoleto lá.
• A documentação do MariaDB indica VALUES(col) como a forma suportada de referenciar valores inseridos em ON DUPLICATE KEY UPDATE; use-a para compatibilidade entre mecanismos.
• SKIP LOCKED é apropriado apenas para trabalho no estilo de fila. Ele pula linhas bloqueadas e pode retornar uma visão inconsistente, portanto não o use para contabilidade geral ou leituras sensíveis à integridade.

Padrões de Esquema

CREATE TABLE orders (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    account_id BIGINT UNSIGNED NOT NULL,
    status VARCHAR(32) NOT NULL,
    total DECIMAL(15, 2) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    PRIMARY KEY (id),
    KEY idx_orders_account_status_created (account_id, status, created_at),
    KEY idx_orders_active (account_id, deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Escolhas padrão:

Caso de Uso	Preferir	Evitar
Chaves primárias substitutas	BIGINT UNSIGNED AUTO_INCREMENT	INT para tabelas que podem crescer além de 2B linhas
Chaves de busca UUID	BINARY(16) com funções auxiliares de conversão	VARCHAR(36) como chave primária em tabelas quentes
Dinheiro e quantidades exatas	DECIMAL(p, s)	FLOAT ou DOUBLE
Texto voltado ao usuário	Tabelas e índices utf8mb4	Padrões MySQL utf8 / utf8mb3
Carimbos de data/hora da aplicação	DATETIME com UTC gerenciado pela aplicação	Assumir que DATETIME armazena metadados de fuso horário
Exclusão lógica	deleted_at DATETIME NULL mais índices com escopo	Filtrar linhas com exclusão lógica sem um índice
Valores de status extensíveis	tabela de consulta ou VARCHAR restrito	ENUM quando os valores mudam com frequência

Indexação

A ordem do índice composto geralmente segue primeiro os predicados de igualdade, depois as colunas de intervalo ou ordenação:

CREATE INDEX idx_orders_account_status_created
    ON orders (account_id, status, created_at);

SELECT id, total
FROM orders
WHERE account_id = ?
  AND status = 'pending'
  AND created_at >= ?
ORDER BY created_at DESC
LIMIT 50;

Use EXPLAIN antes de adicionar ou alterar um índice:

EXPLAIN
SELECT id, total
FROM orders
WHERE account_id = 123 AND status = 'pending'
ORDER BY created_at DESC
LIMIT 50;

Sinais a serem investigados:

Campo	Sinal de Risco
type	ALL em uma tabela grande
key	NULL quando existe um predicado seletivo
rows	Estimativa de linhas muito alta para um caminho interativo
Extra	Using temporary, Using filesort ou Using where amplo

Evite adicionar índices cegamente. Cada índice aumenta o custo de escrita, tempo de migração, tamanho do backup e pressão no pool de buffers.

Padrões de Consulta

Upsert

Forma compatível entre mecanismos:

INSERT INTO user_settings (user_id, setting_key, setting_value)
VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE
    setting_value = VALUES(setting_value),
    updated_at = CURRENT_TIMESTAMP;

Forma de alias de linha MySQL:

INSERT INTO user_settings (user_id, setting_key, setting_value)
VALUES (?, ?, ?) AS new
ON DUPLICATE KEY UPDATE
    setting_value = new.setting_value,
    updated_at = CURRENT_TIMESTAMP;

Use a forma de alias de linha somente após confirmar que o destino é MySQL. Use VALUES(col) para MariaDB ou frotas mistas MySQL/MariaDB.

Paginação por Chave

SELECT id, name, created_at
FROM products
WHERE (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Apoie-a com um índice que corresponda ao cursor:

CREATE INDEX idx_products_created_id ON products (created_at, id);

Não use paginação com OFFSET profundo em tabelas grandes; isso faz o servidor escanear e descartar linhas antes de retornar a página.

Campos JSON

Use colunas JSON para dados de extensão, não para campos que precisam de filtragem relacional pesada ou restrições.

CREATE TABLE events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    payload JSON NOT NULL,
    event_type VARCHAR(64)
        GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(payload, '$.type'))) STORED,
    KEY idx_events_type (event_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Para caminhos JSON consultados com frequência, exponha uma coluna gerada e indexe essa coluna. Mantenha chaves estrangeiras, propriedade, locação e campos de ciclo de vida relacionais.

Busca Textual Completa

ALTER TABLE articles ADD FULLTEXT KEY ft_articles_title_body (title, body);

SELECT id, title, MATCH(title, body) AGAINST (? IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE MATCH(title, body) AGAINST (? IN NATURAL LANGUAGE MODE)
ORDER BY score DESC
LIMIT 20;

Use busca externa quando precisar de tolerância a erros de digitação, classificação complexa, facetas entre tabelas ou análise específica de idioma além do comportamento interno de texto completo.

Transações

Mantenha as transações curtas e bloqueie linhas em uma ordem consistente:

START TRANSACTION;

SELECT id, balance
FROM accounts
WHERE id IN (?, ?)
ORDER BY id
FOR UPDATE;

UPDATE accounts SET balance = balance - ? WHERE id = ?;
UPDATE accounts SET balance = balance + ? WHERE id = ?;

COMMIT;

Lista de verificação de impasse e espera de bloqueio:

• Bloqueie linhas em uma ordem determinística em todos os caminhos de código.
• Faça chamadas de API externas antes de abrir a transação, não dentro dela.
• Adicione índices para predicados usados em UPDATE, DELETE e leituras com bloqueio.
• Em caso de impasse, reverta e tente novamente toda a transação com um orçamento de repetição limitado.
• Capture SHOW ENGINE INNODB STATUS\G logo após um impasse; ele é sobrescrito por eventos posteriores.

Reivindicação de trabalhador no estilo de fila:

START TRANSACTION;

SELECT id
FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

UPDATE jobs
SET status = 'processing', started_at = CURRENT_TIMESTAMP
WHERE id = ?;

COMMIT;

Use SKIP LOCKED apenas para cargas de trabalho no estilo de fila onde pular uma linha bloqueada é aceitável. Não é um substituto para a consistência transacional normal.

Pools de Conexão

Exemplo SQLAlchemy:

from sqlalchemy import create_engine

engine = create_engine(
    "mysql+mysqlconnector://app:[email protected]/app",
    pool_size=10,
    max_overflow=5,
    pool_timeout=30,
    pool_recycle=240,
    pool_pre_ping=True,
    connect_args={"connect_timeout": 5},
)

Exemplo Node.js mysql2:

import mysql from 'mysql2/promise';

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 30000,
});

const [rows] = await pool.execute(
  'SELECT id, total FROM orders WHERE account_id = ? LIMIT 50',
  [accountId],
);

Mantenha a reciclagem do pool da aplicação abaixo do wait_timeout do servidor. Se o servidor usa wait_timeout = 300, um pool_recycle em torno de 240 segundos é coerente; pool_pre_ping ainda ajuda a se recuperar de eventos de rede e failover.

Diagnóstico

Comandos úteis de primeira passagem:

SHOW FULL PROCESSLIST;
SHOW ENGINE INNODB STATUS\G;
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

Ative o log lento em um ambiente controlado:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

Use EXPLAIN ANALYZE apenas quando for seguro executar a consulta. Ela executa a instrução e pode ser cara em dados de tamanho de produção.

Replicação

Réplicas de leitura podem sofrer lag. Não direcione caminhos de leitura-após-escrita-própria, fluxos de checkout, verificações de permissão ou leituras de chave de idempotência para uma réplica imediatamente após uma escrita.

-- Terminologia legada MySQL, ainda comum em frotas existentes
SHOW SLAVE STATUS\G;

-- Terminologia mais recente onde suportado
SHOW REPLICA STATUS\G;

Verifique o mecanismo/versão antes de padronizar em um comando. Monitore a saúde do thread SQL da réplica, a saúde do thread IO e o lag, não apenas se a conexão TCP está ativa.

Segurança

CREATE USER 'app'@'%' IDENTIFIED BY 'use-a-secret-manager';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app'@'%';

ALTER USER 'app'@'%' REQUIRE SSL;

SELECT user, host
FROM mysql.user
WHERE user = '';

DROP USER IF EXISTS ''@'localhost';
DROP USER IF EXISTS ''@'%';

Pontos de revisão de segurança:

• Não conceda ALL PRIVILEGES ou *.* para usuários da aplicação.
• Exija TLS para usuários da aplicação quando o tráfego cruza hosts ou redes.
• Armazene credenciais no gerenciador de segredos da plataforma, não em exemplos, scripts ou arquivos de repositório.
• Separe usuários de migração/admin de usuários de aplicação em tempo de execução.
• Audite a exposição de rede pública e endereços de bind antes de ajustar o desempenho.

Configuração

Exemplo de ponto de partida para um host de banco de dados dedicado:

[mysqld]
innodb_buffer_pool_size = 4G
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

max_connections = 300
thread_cache_size = 50

wait_timeout = 300
interactive_timeout = 300
innodb_lock_wait_timeout = 10

slow_query_log = ON
long_query_time = 1
log_queries_not_using_indexes = ON

log_bin = mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800

Trate os valores de configuração como um prompt para revisão, não uma predefinição universal. Dimensione memória, conexões, retenção de log e configurações de durabilidade com base na carga de trabalho, hardware, política de backup e objetivos de recuperação.

Anti-Padrões

Anti-Padrão	Risco	Padrão Melhor
SELECT * em caminhos quentes	Super-busca e clientes frágeis	Selecionar colunas explícitas
Paginação com OFFSET profundo	Varreduras lineares e páginas lentas	Paginação por chave
Sem índice em junções de chave estrangeira	Junções lentas e exclusões pesadas de bloqueio	Indexar colunas FK intencionalmente
Transações longas	Esperas de bloqueio e histórico de desfazer grande	Confirmar pequenas unidades de trabalho
DML direto contra mysql.user	Risco de corrupção da tabela de concessões	Usar CREATE USER, ALTER USER, DROP USER
Usuário da aplicação com concessões de admin	Alto raio de explosão	Usuário de tempo de execução com privilégios mínimos
Reciclagem do pool acima de wait_timeout	Conexões de pool obsoletas	Reciclar abaixo do tempo limite e pré-verificar
Leituras de réplica após escritas	Estado desatualizado voltado ao usuário	Fixar fluxos de leitura-após-escrita no primário

Expectativas de Saída

Quando esta skill for usada para revisão, retorne:

1. Suposições de mecanismo/versão.
2. Problemas de correção, bloqueio, segurança e migração de maior risco.
3. Mudanças exatas de SQL ou código para o caminho seguro.
4. Plano de validação: EXPLAIN, execução simulada de migração, verificação de bloqueio/impasse e critérios de reversão.
5. Quaisquer diferenças de sintaxe MySQL/MariaDB que afetem a recomendação.

Relacionados

• Skill: postgres-patterns - Padrões específicos de esquema e consulta para PostgreSQL
• Skill: database-migrations - Planejamento de migração e segurança de implantação
• Skill: backend-patterns - Padrões de API e camada de serviço
• Skill: security-review - Manuseio de segredos, autenticação e privilégio mínimo
• Agente: database-reviewer - Fluxo de trabalho mais amplo de revisão de banco de dados