O MySQL é o segundo sistema de gerenciamento de banco de dados relacional de código aberto mais popular do mundo. É usado em todo o mundo devido ao seu desempenho consistentemente rápido, alta confiabilidade e facilidade de uso. Este artigo apresenta algumas das melhores práticas do MySQL.

Com eles, você pode praticar SQL e definir alguns exercícios de SQL, aprender sobre limite de SQL e prática de SQL online, bem como sobre os problemas mais comuns de prática de SQL.

Ele o ajudará a entender o SQL do número da linha, o SQL superior, o deslocamento do limite do MySQL e a prática online do SQL.

Se você está procurando uma maneira agradável e fácil de exibir um grande número de linhas e colunas para os visitantes do seu site WordPress com tabelas e gráficos interativos coloridos, informativos e responsivos, você pode usá-lo com o plug-in wpDataTables, verifique os preços e todos os funcionalidades que ele dá aqui.

1 Sempre use o tipo de dados adequado

Uma das práticas recomendadas mais importantes do MySQL é usar tipos de dados com base na natureza dos dados. O uso de tipos de dados irrelevantes pode consumir mais espaço ou causar erros.

Por exemplo: Usar varchar (20) em vez do tipo de dados DATETIME para armazenar valores de data e hora levará a erros nos cálculos relacionados à data e hora. Além disso, é possível que dados inválidos sejam armazenados.

2 Use CHAR (1) sobre VARCHAR (1)

VARCHAR (1) leva bytes extras para armazenar informações, portanto, se você inserir um único caractere, é melhor usar CHAR (1).

3 Use o tipo de dados CHAR para armazenar apenas dados de comprimento fixo

Por exemplo: Se o comprimento dos dados for menor que 1000, usar char (1000) em vez de varchar (1000) consumirá mais espaço.

4 Evite usar formatos de data regionais

Ao usar o tipo de dados DATETIME ou DATE, sempre use o formato de data YYYY-MM-DD ou o formato de data ISO adequado para seu SQL Engine. Formatos regionais como DD-MM-AAAA ou MM-DD-AAAA não serão armazenados corretamente.

5 Colunas de chave de índice

É desejável que a consulta retorne o resultado rapidamente, portanto, certifique-se de indexar as colunas que são usadas nas cláusulas JOIN.

Caso você use a instrução UPDATE envolvendo mais de uma tabela, certifique-se de indexar todas as colunas que são usadas para unir as tabelas.

6 Não use funções sobre colunas indexadas…

… porque então o índice perde sua finalidade.

Por exemplo, suponha que você queira obter dados em que os dois primeiros caracteres do código do cliente sejam AK. Escreva:

SELECT columns FROM table WHERE customer_code like 'AK%'

e não escreva

SELECT columns FROM table WHERE left (customer_code,2)='AK’

Por quê? O primeiro exemplo fará uso do índice, o que resultará em um tempo de resposta mais rápido.

**7 Use SQL SELECT * somente se necessário**

Seguindo a lista das melhores práticas do MySQL, não use cegamente SELECT * no código. Se a tabela tiver muitas colunas, todas serão retornadas. Isso diminuirá o tempo de resposta, especialmente se você enviar o resultado para um aplicativo front-end.

Em vez disso, digite explicitamente os nomes das colunas que são realmente necessários.

Nota: lembre-se de que todas as instruções SELECT requerem uma cláusula WHERE.

8 Use a cláusula ORDER BY somente se necessário

Se você quiser mostrar o resultado no aplicativo front-end, deixe-o ORDER no conjunto de resultados. Se você fizer isso no SQL, o tempo de resposta pode ser mais lento no ambiente multiusuário.

9 Escolha um Mecanismo de Banco de Dados adequado

Se você desenvolver um aplicativo que lê dados com mais frequência do que escreve (por exemplo, um mecanismo de pesquisa), escolha o mecanismo de armazenamento MyISAM.

Escolher o mecanismo de armazenamento errado afetará o desempenho.

10 Use a cláusula EXISTS sempre que necessário

Para verificar a existência de dados, use a cláusula EXISTS que é mais rápida no tempo de resposta. Por exemplo, use:

If EXISTS(SELECT * from Table WHERE col=’some value’)

Não use :

If (SELECT count(*) from Table WHERE col=’some value’)>0

11 EXPLIQUE suas consultas SELECT

Se você usar a palavra-chave EXPLAIN, poderá obter informações sobre o que o MySQL está fazendo para executar sua consulta. Isso pode ajudá-lo a detectar problemas com suas estruturas de consulta ou tabela (por exemplo, gargalos).

Uma consulta EXPLAIN resulta em mostrar quais índices estão sendo utilizados, como a tabela está sendo verificada, classificada, etc.

Tudo o que você precisa fazer é adicionar a palavra-chave EXPLAIN na frente de uma consulta SELECT (de preferência complexa com junções). Além disso, se você usar o phpmyadmin para isso, seus resultados serão mostrados em uma boa tabela.

12 Use LIMIT 1 ao obter uma linha exclusiva

Às vezes, você sabe de antemão que está procurando apenas uma linha ao consultar suas tabelas. Por exemplo, você pode estar buscando um registro exclusivo ou pode estar apenas verificando a existência de qualquer número de registros que satisfaçam sua cláusula WHERE.

Nesses casos, você desejará usar a função limite do MySQL para aumentar o desempenho. Aqui está outra das melhores práticas do MySQL: simplesmente adicione LIMIT 1 à sua consulta. Dessa forma, o mecanismo de banco de dados não precisará percorrer toda a tabela ou índice. Ele irá parar de escanear quando encontrar apenas 1 registro do que você está procurando.

// do I have anyusersfrom Alabama?

// o que NÃO fazer:
$r = mysql_query(“SELECT * FROM user WHERE state = ‘Alabama’");
if (mysql_num_rows($r) > 0) {
// …
}

// muito melhor:
$r = mysql_query(“SELECT 1 FROM user WHERE state = ‘Alabama’ LIMIT 1");
if (mysql_num_rows($r) > 0) {
// …
}

13 Indexe e use os mesmos tipos de coluna para junções

Outra dica vital das melhores práticas do MySQL – se o seu aplicativo tiver muitas consultas JOIN, certifique-se de que as colunas pelas quais você une sejam indexadas em ambas as tabelas. Isso afeta a otimização interna da operação de junção pelo MySQL.

Além disso, as colunas unidas precisam ser do mesmo tipo. Por exemplo, se você unir uma coluna DECIMAL a uma coluna INT de outra tabela, o MySQL não poderá usar nenhum dos índices. Mesmo as codificações de caracteres precisam ser do mesmo tipo para colunas do tipo string.

// looking for companies in my state $r = mysql_query("SELECT company_name FROM users LEFT JOIN companies ON (users.state = companies.state) WHERE users.id = $user_id");

// ambas as colunas de estado devem ser indexadas
// e ambas devem ser do mesmo tipo e codificação de caracteres
// ou o MySQL pode fazer varreduras completas da tabela

14 Ocultar o MySQL da Internet

Administradores de banco de dados experientes e pessoal de segurança sabem disso – nunca hospede o banco de dados na raiz do servidor da Web.

Para aplicativos habilitados para Web, o MySQL deve estar oculto atrás de um firewall. A comunicação deve ser habilitada apenas entre servidores de aplicativos e seus servidores Web.

Outra opção é usar o MySQL skip-networking. Quando habilitado, o MySQL escuta apenas conexões de soquetes locais e ignora todas as portas TCP.

15 Use os menores tipos de dados possíveis

Deixe-me te contar uma historia. Quando eu estava na faculdade, a filosofia era que “a memória é escassa”. Aqueles eram os dias de discos rígidos de 256 MB. Hoje em dia, ninguém parece se importar nem um pouco com memória ou espaço no disco rígido. A nova filosofia é que “a memória é barata”. Pode ser verdade em termos de dólares, mas a leitura de tipos de dados grandes ainda leva mais tempo do que a leitura de tipos menores. Tipos de dados grandes exigem que mais setores de disco sejam lidos na memória.

A moral é: ignore a tentação de pular imediatamente para o maior tipo de dados ao projetar suas tabelas. Pense em usar um int em vez de um bigint.

Além disso, evite campos de texto largechar (255) quando um varchar ou um caractere menor for suficiente.

Se você usar o tipo de dados correto, mais registros caberão na memória ou no bloco de chave de índice. Isso leva a menos leituras e desempenho mais rápido.

16 Aproveite o cache de consultas

O cache de consulta é um dos métodos mais eficazes para melhorar o desempenho. A maioria dos servidores MySQL tem habilitado por padrão.

O cache de consulta armazena o texto de uma instrução SELECT junto com o conjunto de resultados correspondente. Se o servidor receber posteriormente uma instrução idêntica, ele recuperará os resultados do cache de consulta em vez de analisar e executar a instrução novamente. O cache de consulta é compartilhado entre as sessões, portanto, um conjunto de resultados gerado por um cliente pode ser enviado em resposta à mesma consulta emitida por outro cliente.

No entanto, por melhor que seja, o cache de consultas tem suas limitações. Tome a seguinte afirmação:

O problema aqui é que as consultas contêm certas funções não determinísticas, como NOW() e RAND(). O MySQL não pode calcular essas funções antecipadamente, então elas acabam não sendo armazenadas em cache.

Felizmente, existe uma solução fácil para isso: você pode armazenar os resultados da função em uma variável.

17 Não edite arquivos de despejo

Arquivos de despejo são muito enganadores e podem causar corrupção. Por quê? Se você já viu os arquivos de despejo criados pelo mysqldump, você concordará que eles se parecem com arquivos de texto normais e inofensivos. É por isso que a maioria das pessoas os edita em um editor de texto padrão, o que faz com que apareçam corrupções.

Se você já tentou editar arquivos de despejo, aprendeu rapidamente que eles são tudo menos um arquivo de texto. Portanto, a única maneira garantida de evitar problemas é deixar os arquivos de despejo em paz.

18 Use a configuração de tamanho de bloco MyISAM

A configuração de tamanhos de bloco nos índices das tabelas MyISAM é uma das melhores práticas do MySQL. Ele pode ser encontrado nos arquivos .MYI no buffer de chave, bem como no disco. A configuração fica assim: myisam_block_size.

Tem um valor padrão de 1k. É muito pequeno para ser ideal em um sistema moderno. Blocos de tamanho maior são usados pela maioria dos sistemas de arquivos. E sabemos que escrever um único bloco de índice requer uma leitura e depois uma escrita. O sistema operacional nunca precisará gravar no disco subjacente, desde que o tamanho do bloco seja igual ou maior que o tamanho do bloco do sistema de arquivos.

19 Ative delay_key_write

Delay_key_write está desativado por padrão. Há uma razão para isso. Se você tiver uma falha no meio do projeto, seu banco de dados pode ficar corrompido.

Então, por que você quer ativá-lo? A razão é simples. Porque o delay_key_write garante que o banco de dados não liberará o arquivo de chave MyISAM após cada gravação. Portanto, se você estiver fazendo outra gravação em um futuro próximo, economizará bastante tempo.

Aqui está outra dica legal que escolhemos das melhores práticas do MySQL: Ativar delay_key_write é diferente para cada versão. Para ver como ativá-lo em uma versão específica, consulte o manual oficial do site MySQL.

20 Use o Stack Trace para isolar bugs

Seguindo essas práticas recomendadas do MySQL, esta dica é emprestada do Sky SQL porque é muito simples e conveniente demais para ser deixada de fora.

O stack_trace do MySQL pode ser usado para isolar vários bugs. Em vez de se aprofundar em todos os usos possíveis, o programador pode querer observar a facilidade com que um ponteiro nulo pode arruinar seu código.

Com esta dica do Sky SQL, detectar, rastrear e corrigir se torna muito mais fácil.

21 MySQL alterando a senha ROOT

Alterar a senha do ROOT pode parecer básico, mas saber como fazê-lo é igualmente importante para o seu sistema operacional doméstico e também para os servidores MySQL.

Às vezes acontece que iniciantes superzelosos e usuários experientes distraídos não conseguem descobrir por que não podem alterar certas configurações e estão retornando erros. Pode ser tão simples quanto dar a si mesmo acesso ROOT. Um usuário nem deveria ter que pesquisar no Google.

Aqui está como configurar a senha ROOT e como alterar a senha ROOT de um usuário:

//Straightforward MySQL 101 $mysqladmin -u rootpassword [Type in selectedpassword] //Changingusers ROOT password $mysqladmin -u root -p [type oldpassword] newpass [hit enter and type new password. Pressenter] //Use mysqlsqlcommand $mysql -u root -p //prompt "mysql>" pops up. Enter: $use MySQL; //Enter usernameyouwant to change the password for $update user set password=PASSWORD (Type new PasswordHere) where User = 'username'; //Don'tforget the previoussemicolon, nowreload the settings for the user'sprivileges $flush privileges; $quit

22 Corrija seus arquivos de configuração

MySQL Tuner é um script Perl que pode de alguma forma otimizar seu desempenho sugerindo mudanças em seus arquivos de configuração.

Se algumas dicas e truques no MySQL são incrivelmente convenientes, ferramentas como o MySQL Tuner são uma dádiva de Deus que merece estar em uma categoria própria.

É algo que pode ser usado por novatos e profissionais. MySQL Tuner não é uma dica específica para MySQL, então há uma variedade de ajustes e mods que podem ser aplicados. Quanto mais você usa, mais ajustes você pode aprender a aplicar para seu próprio uso.

Pode parecer intimidante no começo, mas é por isso que você pode encontrar o manual –man db, bem como todas as notas, leituras e configurações necessárias na página oficial do projeto. Com tudo isso, o MySQL Tuner deve se tornar rapidamente sua ferramenta para acelerar o MySQL e testar seus arquivos de configuração.

Considerações finais sobre as melhores práticas do MySQL

No mundo da ciência da computação, o MySQL é sem dúvida um dos programas mais importantes e influentes que já apareceram. É tão envolvente e versátil que, quando parece que todos os truques e atalhos possíveis foram descobertos, alguém aparece com uma nova maneira de usar um novo argumento ou cenário.

Descrevemos apenas as melhores práticas do MySQL que todos devem conhecer. Mas, quanto mais você usa o MySQL, mais você os usará. Você pode praticar SQL online e definir testes de SQL. Você desenvolverá seus próprios truques e encontrará suas próprias dicas principais nas quais pode confiar, como limitar o SQL, o número da linha do SQL e entender por que você deve selecionar o MySQL em primeiro lugar.

Se você gostou de ler este artigo sobre as melhores práticas do MySQL, você também deve ler estes:

Fonte de gravação: wpdatatables.com