Padrões MySQL

Use esta habilidade ao trabalhar com design de schema MySQL ou MariaDB, migrações, investigação de consultas lentas, transações 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 SQL.

Ativação

• Projetando tabelas, índices e restrições MySQL ou MariaDB
• Revisando migrações antes que elas sejam executadas em grandes tabelas de produção
• Depurando consultas lentas, esperas de bloqueio, deadlocks ou esgotamento de conexão
• Adicionando paginação por conjunto de chaves, upserts, busca de texto completo, colunas JSON ou filas
• Configurando pools de conexão de aplicativos, réplicas de leitura, TLS ou logs lentos

Verificação de Versão

Comece identificando o engine e a versão:

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

Mantenha a orientação de MySQL e MariaDB separada quando a sintaxe diferir:

• Documentos MySQL apelidos de linha como substitutos para VALUES(col) em ON DUPLICATE KEY UPDATE; VALUES(col) é obsoleto lá.
• Documentos MariaDB VALUES(col) como a maneira suportada de referenciar valores inseridos em ON DUPLICATE KEY UPDATE; use-o para compatibilidade entre engines.
• SKIP LOCKED é apropriado apenas para trabalho semelhante a 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 Schema

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:

Indexação

A ordem do índice composto geralmente segue primeiro os predicados de igualdade, depois as colunas de intervalo ou de 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:

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

Padrões de Consulta

Upsert

Forma compatível entre engines:

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 apelido de linha do 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 apelido de linha apenas após confirmar que o destino é MySQL. Use VALUES(col) para MariaDB ou frotas mistas MySQL/MariaDB.

Paginação por Conjunto de Chaves

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

…