mysql-patterns

Padrões MySQL

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

Ativação

• Projetar tabelas, índices e restrições MySQL ou MariaDB
• Revisar migrações antes de executá-las em grandes tabelas de produção
• Depurar consultas lentas, esperas por bloqueio, deadlocks ou exaustão de conexão
• Adicionar paginação por keyset, upserts, busca em texto completo, colunas JSON ou filas
• Configurar pools de conexão de 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 do MariaDB separadas quando a sintaxe diferir:

• Os documentos MySQL usam aliases de linha como substitutos para VALUES(col) em ON DUPLICATE KEY UPDATE; VALUES(col) está obsoleto lá.
• Os documentos MariaDB usam VALUES(col) como a forma suportada de referenciar valores inseridos em ON DUPLICATE KEY UPDATE; use-o para compatibilidade entre mecanismos.
• SKIP LOCKED é apropriado apenas para trabalhos no estilo fila. Ele pula linhas bloqueadas e pode retornar uma visão inconsistente, portanto, não o use para contabilidade geral ou leituras críticas de 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 helpers de conversão	VARCHAR(36) como chave primária em tabelas quentes
Dinheiro e quantidades exatas	DECIMAL(p, s)	FLOAT ou DOUBLE
Texto voltado para o 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ões suaves	deleted_at DATETIME NULL mais índices com escopo	Filtrar linhas deletadas 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 para investigar:

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 gravação, o tempo de migração, o tamanho do backup e a pressão no buffer pool.

Padrões de Consulta

Upsert

Formulário 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;

Formulário 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 Keyset

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

Suporte com um índice que corresponda ao cursor:

CREATE INDEX idx_products_created_id ON products (created_at, id);

Não use paginação profunda com OFFSET 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 em Texto Completo

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 as 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 deadlock 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 deadlock, reverta e tente novamente a transação inteira com um orçamento de repetição limitado.
• Capture SHOW ENGINE INNODB STATUS\G logo após um deadlock; ele é sobrescrito por eventos posteriores.

Reivindicação de trabalhador no estilo 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 fila, onde pular uma linha bloqueada seja 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 usar 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ósticos

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 somente quando for seguro executar a consulta. Ele executa a instrução e pode ser caro em dados de tamanho de produção.

Replicação

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

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

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

Verifique o mecanismo/versão antes de padronizar 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 *.* a usuários da aplicação.
• Exija TLS para usuários da aplicação quando o tráfego cruzar 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 à rede pública e os 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 logs 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	Busca excessiva e clientes frágeis	Selecionar colunas explícitas
Paginação profunda com OFFSET	Varreduras lineares e páginas lentas	Paginação por keyset
Sem índice em joins de chave estrangeira	Joins lentos e exclusões com bloqueio pesado	Indexar colunas FK intencionalmente
Transações longas	Esperas por bloqueio e grande histórico de undo	Confirmar pequenas unidades de trabalho
DML direto contra mysql.user	Risco de corrupção da tabela de grants	Usar CREATE USER, ALTER USER, DROP USER
Usuário da aplicação com grants de admin	Alto raio de explosão	Usuário de tempo de execução com privilégio mínimo
Reciclagem do pool acima de wait_timeout	Conexões obsoletas no pool	Reciclar abaixo do timeout e pré-verificar
Leituras de réplica após gravações	Estado desatualizado voltado ao usuário	Fixar fluxos de leitura-após-gravação 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 maior risco de correção, bloqueio, segurança e migração.
3. Alterações exatas de SQL ou código para o caminho seguro.
4. Plano de validação: EXPLAIN, migração a seco, verificação de bloqueio/deadlock 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 de esquema e consulta específicos do 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 - Manipulação de segredos, autenticação e privilégio mínimo
• Agent: database-reviewer - Fluxo de trabalho mais amplo de revisão de banco de dados