jmaxzh/sdlc-mysql-design

Design de Tabelas e SQL para MySQL

Aplicabilidade

• Aplique quando a tarefa ou projeto estabelecer o MySQL como banco de dados alvo e envolver modelagem de dados, design de tabelas, SQL, índices, transações ou controle de concorrência; invocação explícita é desnecessária.
• Use independentemente do fluxo de trabalho SDLC. Não selecione MySQL para trabalho genérico de banco de dados nem aplique a tarefas que envolvam apenas outros bancos de dados.

Uso

• Use a semântica oficial do MySQL 8.4 como linha de base de validação sem restringir a versão implantada. Verifique suporte a recursos, aplicação de constraints e depreciação na versão alvo real quando diferenças forem relevantes.
• Estabeleça o significado de negócio, requisitos de integridade, principais padrões de acesso, escala e crescimento de dados, concorrência e requisitos de consistência relevantes para a tarefa. Use designs existentes e evidências de carga de trabalho quando disponíveis; declare suposições e seu impacto quando informações-chave estiverem faltando.
• Aplique regras de transação e concorrência a transações InnoDB locais. Identifique explicitamente as proibições de consultas dentro de transações, constraints CHECK, chaves estrangeiras e procedimentos armazenados como convenções do projeto, não limitações do MySQL.
• Assuma que um registro é modificado no máximo uma vez por milissegundo. Essa suposição não garante que updated_at seja um identificador de versão não reutilizado; aplique as condições sob verificações de timestamp antes de usá-lo para controle de concorrência.
• Aplique apenas as regras relevantes para a tarefa. Leia o tópico correspondente em exemplos de design MySQL quando uma regra precisar de esclarecimento; não carregue todos os exemplos por padrão.

Modelagem de dados

Entidades, relacionamentos e propriedade de campos

• Granularidade da linha: defina o objeto de negócio, relacionamento ou evento representado por uma linha; mantenha a granularidade consistente dentro de uma tabela. Veja exemplos.

• Relacionamentos e unicidade: defina cardinalidade mínima e máxima em ambos os lados. Armazene referências um-para-muitos no lado muitos, use referências únicas para limitar relacionamentos um-para-um a no máximo uma correspondência e use tabelas de junção para relacionamentos muitos-para-muitos. Para associações repetidas dos mesmos objetos, defina o significado de negócio, identidade e escopo de unicidade de cada associação. Veja exemplos.

• Propriedade de campos: coloque um campo com o objeto, relacionamento ou evento que determina seu valor; evite duplicar fatos atuais. Mantenha instantâneos históricos em valores de tempo de evento, inalterados por atualizações de valor atual. Veja exemplos.

• Integridade referencial: defina se um alvo referenciado deve existir e quais estados ele deve satisfazer ao criar ou alterar uma referência. Defina como as referências existentes são tratadas quando o alvo é excluído ou se torna inválido. Reforce esses requisitos através de fluxos de escrita de negócio sob chaves e constraints e controle de concorrência. Veja exemplos.

Design de tabelas

Nomes e comentários

• Nomeie bancos de dados, tabelas e colunas usando letras minúsculas, dígitos e underscores; não comece com dígitos, use palavras reservadas ou abreviações pouco claras.

• Nomeie índices por suas colunas ou propósito. uniq_ e idx_ podem identificar índices únicos e comuns; o nome do índice primário é PRIMARY.

• Atualize comentários de coluna e definições relacionadas quando significados, valores de estado ou constraints mudarem.

Tipos de dados

• Mantenha colunas usadas para referenciar ou comparar os mesmos valores compatíveis em tipo de dados e sinalização; use definições consistentes quando possível.

• Represente valores monetários exatos com DECIMAL de precisão e escala explícitos, ou inteiros em uma unidade mínima definida; defina a moeda e regras de arredondamento. Preserve a representação exata através de armazenamento, parâmetros e cálculos; não calcule em ponto flutuante antes da conversão.

• Não defina UNSIGNED depreciado em FLOAT, DOUBLE ou DECIMAL.

• Use is_xxx e TINYINT UNSIGNED para booleanos, com validação da aplicação restringindo valores a 0 ou 1.

• Ao usar CHAR, verifique se seu tratamento de espaços à direita corresponde ao significado de negócio do campo.

• TIMESTAMP converte entre fuso horário da sessão e UTC; DATETIME não. Armazene nomes ou offsets de fuso horário originais separadamente quando necessário.

• Use created_at para criação e updated_at para última modificação. Registros de negócio mutáveis precisam de ambos; registros somente anexados precisam apenas do tempo de criação. Adicione-os a tabelas de junção ou outras conforme necessidades de auditoria.

• Use DATETIME(3), precisão de milissegundos e horário de Pequim para timestamps de auditoria, com fuso horário da conexão +08:00.

• Defina timestamps de auditoria como NOT NULL DEFAULT CURRENT_TIMESTAMP(3) e adicione ON UPDATE CURRENT_TIMESTAMP(3) para updated_at. Deixe o banco de dados mantê-los; não atribua redundantemente na aplicação.

Chaves e constraints

• Defina explicitamente chaves primárias estáveis e curtas para tabelas InnoDB. Registros de índice secundário contêm colunas da chave primária, então chaves primárias largas aumentam o custo do índice secundário.

• Marque campos obrigatórios como NOT NULL.

• Não use CHECK; valide intervalos, enums e combinações de campos em todo ponto de entrada de escrita da aplicação, rejeitando escritas inválidas. Veja exemplos.

• Reforce unicidade de negócio com índices únicos, não consulta-antes-inserção. Índices únicos permitem múltiplos valores NULL.

• Reforce unicidade para identificadores de idempotência dentro do escopo da operação de negócio e locatário aplicável, com todas as colunas participantes NOT NULL.

• Não use constraints de chave estrangeira ou cascatas; preserve integridade referencial através de fluxos de escrita de negócio.

Conjuntos de caracteres e collations

• Use utf8mb4 para texto.

• Mantenha colunas de texto usadas para referenciar ou comparar os mesmos valores compatíveis em conjunto de caracteres e collation; use definições consistentes quando possível.

• Antes de alterações de collation, verifique mudanças de igualdade e novos valores duplicados que violem unicidade.

Programação SQL

• Não use procedimentos armazenados. O código da aplicação controla ramificações, commits e rollbacks.

Consultas

• Selecione explicitamente colunas necessárias em consultas de negócio estáveis; evite SELECT *.

• Quando paginação ou seleção de linha única exigir ordem determinística, use uma combinação de chaves de ordenação que ordene linhas unicamente.

• Inclua todas as chaves de ordenação em cursores. Mudanças concorrentes na chave de ordenação podem causar omissões/duplicatas entre páginas; paginação por cursor não garante um instantâneo consistente entre páginas.

Modificação de dados

• Não oculte erros que deveriam falhar usando INSERT IGNORE ou UPDATE IGNORE.

• Evite que UPDATE/DELETE degenerem em operações de tabela completa devido a parâmetros vazios ou predicados dinâmicos faltantes. LIMIT não substitui condições de escopo de negócio.

• Sob a definição de timestamp de auditoria acima, updated_at muda automaticamente quando o conteúdo da linha muda; não registra o tempo de execução de cada instrução de atualização.

• Não substitua atualizações comuns por REPLACE INTO: em conflito de chave primária/única, sua semântica de substituição pode redefinir campos não especificados para padrões e acionar comportamento de exclusão/inserção.

Expressões e funções

• Corresponda tipos de comparação; não deixe que conversão implícita determine comparações críticas de negócio.

• Distinga bytes, caracteres e grafemas visíveis ao usuário em verificações de comprimento. LENGTH conta bytes e CHAR_LENGTH caracteres, não grafemas visíveis.

• Se o conjunto usado por NOT IN contiver NULL, um valor que não corresponda a nenhum membro pode resultar em UNKNOWN; não interprete isso como não-membro confirmado.

• COUNT(DISTINCT col) exclui NULL; sua forma de múltiplas colunas conta combinações distintas apenas quando todas as colunas participantes são não-NULL.

• SUM retorna NULL para entradas vazias ou todas-NULL. Se GROUP BY não formar grupo, nenhuma linha é retornada para ele. Substitua por zero apenas quando regras de negócio definirem agregação vazia como zero.

• Prefira intervalos de tempo semiabertos: inclua o início e exclua o fim. Calcule limites no fuso horário de negócio com a mesma base de tempo dos valores armazenados.

Transações e consistência

Gerenciamento de transações

• Instruções dentro de transações: realize consultas, validação de negócio e preparação de escrita fora de transações explícitas. Dentro da transação, permita apenas modificações de dados necessárias e controle de transação. Proíba consultas comuns, consultas de bloqueio e componentes de consulta dentro de escritas, incluindo subconsultas. Veja exemplos.

• Conexão de transação: execute escritas que devem ser bem-sucedidas juntas na mesma conexão dentro da mesma transação. Troca de conexão, execução assíncrona e configurações de propagação devem preservar esse limite. Veja exemplos.

• Commits implícitos: não simule transações aninhadas com START TRANSACTION ou alterne autocommit de 0 para 1 entre escritas de negócio; ambos fazem commit implícito. Mantenha DDL como ALTER TABLE e TRUNCATE TABLE fora de transações de escrita de negócio. Veja exemplos.

• Tratamento de falhas: em erros de escrita obrigatória ou condições de negócio não atendidas, pare escritas posteriores e reverta toda a transação. Não assuma que uma instrução com falha desfaz escritas anteriores. Após um deadlock ou timeout de bloqueio, garanta reversão completa e repita toda a transação apenas quando seguro, com um limite finito de repetições. Para commits incertos, verifique primeiro usando identificadores de idempotência. Veja exemplos.

• Operações externas: transações locais não fazem commit/rollback atômico de chamadas externas ou envio de mensagens. Veja exemplos.

Controle de concorrência

• Atualizações condicionais de negócio: prefira predicados de campo de negócio no momento da escrita. Ao calcular novos valores a partir de dados lidos fora da transação, inclua predicados verificando os valores originais relevantes ou um identificador de versão válido para evitar sobrescrever alterações concorrentes. Não adicione transações explícitas quando uma instrução condicional for suficiente. Veja exemplos.

• Verificações de timestamp: se campos de negócio adequados estiverem ausentes, corresponda ao updated_at original apenas se toda modificação relevante o alterar e o valor antigo não puder ser reutilizado durante a validação. Preserve milissegundos em leituras, escritas, serialização e vinculação. Caso contrário, use um identificador de versão não reutilizado ou outro mecanismo de concorrência. Veja exemplos.

• Integridade referencial: use atualizações condicionais, unicidade e transações necessárias para coordenar criação/alteração de referência contra exclusão/invalidação de alvo. Veja exemplos.

• Resultados de atualização: determine se o driver relata linhas correspondidas ou alteradas e, em seguida, avalie se a escrita necessária foi bem-sucedida. Um predicado não atendido não é sucesso; zero linhas alteradas sozinho não prova um predicado não atendido. Veja exemplos.

• Ordem de escrita: transações concorrentes devem usar ordem consistente de tabela/registro. A ordem do texto SQL não estabelece a ordem real de bloqueio. Veja exemplos.

Segurança do banco de dados

• Vincule dados da aplicação como parâmetros em vez de concatená-los no SQL.

• Mapeie nomes de tabela/coluna dinâmicos e direções de ordenação através de listas de permissão de código na estrutura SQL; placeholders de valor não podem substituí-los.

Otimização de performance

• Viagens de ida e volta de requisição: reduza requisições ao banco de dados por operação de negócio, preservando correção e performance geral.

Otimização de índices

• Reuso e alterações de índices: reuse índices entre consultas primárias. Não adicione índices meramente para novos SQL/predicados quando caminhos existentes atenderem às necessidades de performance. Verifique outras consultas e constraints únicas antes de mesclar ou substituir. Veja exemplos.

• Campos de baixa cardinalidade: não rejeite índices unicamente por poucos valores distintos. Considere distribuição alvo, filtros combinados, ordenação, benefícios de cobertura, lookups de tabela e custos de escrita. Veja exemplos.

• Índices de prefixo: selecione comprimentos de prefixo de string a partir de dados reais. Índices únicos de prefixo podem rejeitar valores completos distintos que compartilham um prefixo e não podem substituir unicidade de valor completo. Veja exemplos.

• Índices compostos: equilibre filtragem, reuso de prefixo esquerdo, ordenação e agrupamento. Colunas de igualdade comuns precedem colunas de intervalo; não ordene universalmente por seletividade. Veja exemplos.

• Custo de ordenação: filesort sozinho não significa um índice ruim. Compare ordenação, varreduras e custos de manutenção de índices adicionados. Veja exemplos.

• Colunas e expressões indexadas: prefira predicados diretos de intervalo de tempo para filtragem diária. Se expressões forem necessárias, avalie índices funcionais ou de coluna gerada correspondentes. Veja exemplos.

• Wildcards iniciais: índices B-tree comuns geralmente não podem transformar buscas com wildcard inicial em varreduras de intervalo eficazes. Pesquisa de texto completo ou dedicada deve reter semântica de correspondência original. Veja exemplos.

Otimização de consultas

• Consultas em lote: substitua consultas por linha combináveis por joins ou subconsultas correlacionadas.