O banco de dados MySQL se tornou o banco de dados relacional de código aberto mais popular do mundo devido ao seu alto desempenho, consistência, alta confiabilidade e facilidade de uso. No entanto, esses benefícios oferecidos a nós geralmente são afetados pela maneira como trabalhamos nele.
Neste tutorial, você aprenderá uma série de dicas que serão muito úteis e nos permitirão tirar o máximo proveito do programador e do administrador do banco de dados.
Que ferramenta vamos usar?
MySQL command console '] O MySQL possui um programa chamado com o mesmo nome do banco de dados (mysql) usado para gerenciar o banco de dados por linha de comando.
Windows
Está em um diretório como:
C: \ Arquivos de programas \ MySQL \ MySQL Server 5.6 \ bin
O diretório pode variar, por exemplo, pode estar localizado na raiz do disco C: ou em qualquer outro lugar onde possamos ter instalado o MySQL. Para acessar o console do MySQL no Windows, teremos que estar localizados nesse diretório.
Linux
Veja o seguinte link:
Acesse o MySQL a partir do Linux
Mac
Os computadores com sistema Mac OS X possuem o terminal de linha de comando integrado entre os aplicativos disponíveis. O mesmo comando que no Linux é usado para acessar.
phpMyAdmin
É uma ferramenta de software livre escrita em PHP, usada para administrar o MySQL pela Internet. Se você tiver um ambiente de desenvolvimento local instalado como XAMPP ou WAMP, ele trará essa ferramenta instalada. Se você possui um plano de hospedagem com um painel administrativo, a maioria deles oferece essa ferramenta como administrador de banco de dados MySQL.
phpMyAdmin do XAMPP [/ color = rgb (169.169.169)]
[color = # a9a9a9] phpMyAdmin do CPanel [/ color]
Tendo essas duas ferramentas em mãos, podemos começar a experimentar todas essas boas práticas que mostramos abaixo.
Estas são as 20 melhores práticas do MySQL:
1. Convenção da Nomenclatura
Existem padrões de codificação para cada linguagem de programação, mas em termos de MySQL não encontramos nenhuma prática universal de codificação que todos sigam. No entanto, revisando várias estruturas de código-fonte aberto baseadas em PHP, filtramos algumas regras gerais de aplicativos que nos ajudarão a escrever consultas SQL mais rapidamente, a eliminar confusões e conflitos, tanto na consulta quanto na linguagem de programação que usamos.
Regras Gerais
Lembre-se das seguintes regras para evitar problemas.
- Use letras minúsculas, pois ajuda na velocidade de gravação, para evitar erros no caso de funções que diferenciam maiúsculas de minúsculas, etc.
- Não use espaços em branco, use o sublinhado (_).
- Não use números nos nomes, apenas caracteres alfabéticos em inglês.
- Use nomes válidos e compreensíveis.
- Os nomes devem se explicar.
- Os nomes não devem conter mais de 64 caracteres.
- Evite usar prefixos.
Regras para nomes de banco de dados
Siga todas as regras gerais acima.
- O nome pode ser singular e plural, mas o banco de dados representa um banco de dados; portanto, deve ser exclusivo, se possível.
- Evite prefixos, se possível.
Regras para nomes de tabelas
Use letras minúsculas para nomes de tabelas: o MySQL geralmente é hospedado em servidores Linux, faz distinção entre maiúsculas e minúsculas, portanto, é recomendável colocar os nomes das tabelas em letras minúsculas.
- Os nomes das tabelas devem estar no singular: a tabela é uma entidade única, como é o modelo, portanto, é estranho ter o nome da tabela no plural.
- Prefixos no nome da tabela: Vimos muitas vezes que as tabelas são prefixadas com o nome do banco de dados ou o nome do projeto. Às vezes isso é necessário quando, em nosso projeto, temos muitos bancos de dados para superar a limitação de alguns provedores de hospedagem. Mas se não for necessário e nosso projeto for pequeno, evite usar prefixos.
Nome do campo
Use todas as regras acima, ou seja, use letras minúsculas, não use espaços em branco, não use números e evite prefixos.
- Use uma ou duas palavras curtas, se possível.
- Os nomes dos campos devem poder ser entendidos, por exemplo: preço, nome da empresa, etc.
- Nome da coluna principal: a chave primária pode ter o nome da identificação ou o nome da tabela _id. Isso vai depender da escolha.
- Evite usar palavras reservadas nos campos: *****, data, etc. Nesses casos, é preferível usar prefixos como date_log etc.
- Evite usar nomes de colunas com o mesmo nome de tabela. Isso pode causar confusão ao escrever consultas.
- Evite nomes abreviados ou concatenados em siglas.
2. Sempre use o tipo certo de dados
Use tipos de dados com base na natureza dos dados. Se você usar tipos de dados irrelevantes, isso poderá consumir mais espaço ou levar a erros.
Exemplo
O uso de varchar (20) para armazenar valores de data e hora, em vez de DATETIME, isso pode gerar erros durante o cálculo dos horários relacionados à data e também é possível no caso de armazenamento de dados inválidos.
3. Use CHAR (1) sobre VARCHAR (1)
Se você armazenar uma única cadeia de caracteres, use CHAR (1) em vez de VARCHAR (1), porque VARCHAR (1) precisará de um byte adicional para armazenar informações. Portanto, leve isso em consideração ao gerenciar esses personagens.$config[ads_text6] not found
4. CHAR para dados fixos
Use dados do tipo CHAR para armazenar apenas dados de comprimento fixo
Exemplo:
Usar CHAR (1000) em vez de VARCHAR (1000) consome mais espaço se o comprimento dos dados for menor que 1000.
5. Evite usar formatos de data regionais
Quando tipos de dados são usados:
- DATETIME
- DATE
Sempre use o formato AAAA-MM-DD ou o formato de data ISO adequado ao seu mecanismo SQL. Outros formatos regionais como DD-MM-AAAA, DD-MM-AAAA não serão armazenados corretamente.
6. Otimize seus pedidos para o cache
A maioria dos servidores MySQL tem o sistema de cache ativado . É um dos métodos mais eficazes para melhorar o desempenho, provenientes das mãos do mecanismo de banco de dados. Quando a mesma solicitação é executada várias vezes, o resultado é obtido do cache, que é muito mais rápido.
O exemplo a seguir está no PHP:
// O cache NÃO funciona $ r = mysql_query ("SELECT name FROM users WHERE record> = CURDATE ()"); // Cache SIM funciona $ today = date ("Ymd"); $ r = mysql_query ("SELECT nome dos usuários WHERE record> = '$ today'"); O motivo pelo qual não funciona no primeiro caso é por causa do uso de CURDATE (). Pode ser aplicado a todas as funções não determinísticas, como NOW () e RAND (). Como o resultado retornado pela função pode mudar, o MySQL decide desativar o cache nessa consulta.$config[ads_text5] not found7. Evite usar "SELECT *" em suas consultas
Como regra geral, quanto mais você lê os dados nas tabelas, mais lenta é a consulta. Dado que algumas tabelas de produção podem conter dezenas de colunas, algumas das quais compostas por grandes tipos de dados, seria imprudente selecionar todas elas.
É um bom hábito especificar as colunas necessárias na sua instrução SELECT .
8. Use LIMIT 1 Quando você quiser apenas uma única linha
Às vezes, quando você está consultando suas tabelas, e sabe que precisa apenas de uma única linha. Nesses casos, você deve solicitar um único resultado do banco de dados, caso contrário, ele verificará todas as correspondências da cláusula WHERE.$config[ads_text6] not found$config[ads_text5] not found
Nesses casos, adicionar LIMIT 1 à sua consulta pode melhorar significativamente a velocidade. Dessa maneira, o banco de dados interromperá a verificação dos resultados no momento em que você encontrar um, em vez de passar por toda a tabela ou índice.
// Eu tenho usuários de Madrid? // o que NÃO fazer: $ r = mysql_query ("SELECT * FROM user WHERE city = 'Madrid'"); if (mysql_num_rows ($ r)> 0) {//} // muito melhor: $ r = mysql_query ("SELECT 1 FROM user WHERE city = 'Madrid' LIMIT 1"); if (mysql_num_rows ($ r)> 0) {//
}
[color = # a9a9a9] LIMIT Recomendação [/ color]9. Uso de ***** POR
O uso de ***** BY pode diminuir o tempo de resposta em ambientes multiusuários. Portanto, recomendamos que a cláusula ***** BY seja usada apenas quando necessário.$config[ads_text6] not found$config[ads_text5] not found
Não abuse do seu uso.
10. Escolha o motor base adequado
Se você desenvolver um aplicativo que lê dados com mais frequência do que escrevendo.
(por exemplo: mecanismo de pesquisa), selecione o mecanismo de armazenamento MyISAM.
Se você desenvolver um aplicativo que grava dados com mais frequência do que lendo
(por exemplo: transações bancárias em tempo real), escolha o mecanismo de armazenamento InnoDB.
A escolha do mecanismo de armazenamento errado afetará o desempenho de suas consultas.
11. Use a cláusula EXISTS sempre que necessário
Se você deseja verificar a existência de dados, não use:
If (SELECT count (*) da tabela WHERE col = "algum valor")> 0Em troca, use a cláusula EXISTS:
Se EXISTE (SELECT * da tabela WHERE col = "algum valor")O que é mais rápido no tempo de resposta.$config[ads_text6] not found$config[ads_text5] not found
12. Use EXPLAIN em suas consultas SELECT
O uso da palavra-chave EXPLAIN fornecerá muitos detalhes internos sobre o que o MySQL faz para executar sua consulta. Isso pode ajudá-lo a detectar gargalos e outros problemas com sua consulta ou a estrutura da tabela.
O resultado de uma consulta EXPLAIN mostrará os índices que estão sendo usados, como a tabela está sendo explorada, como está sendo classificada, etc.
Selecione uma consulta SELECT (de preferência um complexo, com uniões) e adicione a palavra EXPLAIN no início de tudo. Ele retornará os resultados em uma tabela simples. Por exemplo, suponha que eu esqueci de indexar uma coluna, ela nos mostraria a seguinte tela:
Depois de adicionar o índice à tabela de estados, ficaria assim:
13. Indexa e usa o mesmo tipo de coluna para o Join
Se o seu aplicativo contiver muitas instruções JOIN, você deverá garantir que as colunas que você ingressam sejam indexadas nas duas tabelas. Isso afeta como o MySQL otimiza internamente as operações JOIN .
Além disso, as colunas às quais você ingressará devem ser do mesmo tipo. Por exemplo, se você estiver ingressando em uma coluna do tipo DECIMAL com uma coluna do tipo INT de outra tabela, o MySQL não poderá usar pelo menos um dos dois índices. Até a codificação de caracteres precisa ser do mesmo tipo para colunas do tipo String .
// procurando empresas na minha cidade $ r = mysql_query ("SELECT company_name FROM users LEFT JOIN empresas ON (users.city = companyias.city) WHERE users.id = $ user_id"); Ambas as colunas da cidade devem ser indexadas e ambas devem ter o mesmo tipo e codificação de caracteres ou o MySQL terá que fazer uma varredura total das tabelas.14. Use NOT NULL se puder
A menos que você tenha um motivo específico para usar o valor NULL, sempre defina suas colunas como NOT NULL.
Primeiro, pergunte a si mesmo se haveria uma diferença entre ter uma string vazia e um valor NULL (ou para campos INT: 0 contra NULL). Se não houver problema entre os dois valores, você não precisará de um campo NULL. As colunas NULL precisam de espaço adicional e podem adicionar complexidade às suas instruções de comparação. Apenas evite-os sempre que puder.
De qualquer forma, entendemos que, em alguns casos muito específicos, há motivos para usar colunas NULL, o que nem sempre é uma coisa ruim.
15. As tabelas de tamanho fixo (estáticas) são mais rápidas
Quando cada coluna de uma tabela é de tamanho fixo ("comprimento fixo"), a tabela inteira é considerada "estática" ou "tamanho fixo".
Alguns exemplos de tipos de colunas que NÃO são de tamanho fixo são:
- VARCHAR
- TEXTO
- BLOB
Se você incluir apenas um desses tipos de coluna, a tabela não terá mais tamanho fixo e precisará ser tratada de maneira diferente pelo mecanismo do MySQL .
As tabelas de tamanho fixo podem aumentar a produtividade porque é mais rápido para o mecanismo MySQL pesquisar em seus registros. Quando você quiser ler uma linha específica na tabela, poderá calcular rapidamente a posição que ela ocupa. Se o tamanho da linha não for fixo, sempre que você precisar pesquisar, verifique primeiro o índice da chave primária.
Eles também são mais fáceis de revistar e reconstruir após um acidente. Por outro lado, eles também poderiam ocupar mais espaço.
16. Particionamento Vertical
Particionamento vertical é o ato de separar a estrutura de sua tabela verticalmente por motivos de otimização.$config[ads_text6] not found
Exemplo 1:
Você certamente terá uma tabela de usuário que contém um endereço postal, que não é usado com muita frequência. Aqui você pode dividir a tabela e armazenar os endereços em uma tabela separada. Dessa forma, sua tabela principal de usuários teria um tamanho menor. Como você sabe, quanto menor, mais rápidas as tabelas.
Exemplo 2:
Você tem um campo de "último acesso" em sua tabela. É atualizado sempre que um usuário acessa sua página. Mas cada acesso faz com que o cache de consulta dessa tabela seja liberado. O que você pode fazer é colocar esse campo em outra tabela para que as alterações em sua tabela de usuário sejam reduzidas ao mínimo.
Mas você também precisa se certificar de que não precisa ingressar nas duas tabelas constantemente após o particionamento ou sofrerá uma queda no desempenho, exatamente o oposto do que estávamos procurando.
17. Armazena endereços IP como UNSIGNED INT
Muitos programadores criariam um campo VARCHAR (15) sem perceber que podem armazenar endereços IP como números inteiros. Quando você usa um INT, você usa apenas 4 bytes na memória e ele também possui um tamanho fixo na tabela.
Mas você precisa garantir que a coluna seja UNSIGNED INT (número inteiro não assinado) porque os endereços IP usam todo o intervalo não assinado de 32 bits.
Em suas consultas, você pode usar a função INET_ATON () para converter um endereço IP inteiro e INET_NTOA () para fazer o oposto. Também existem funções semelhantes no PHP chamadas ip2long () e long2ip () .
18. Crie Views para simplificar o uso comum em tabelas
As visualizações ajudam a simplificar esquemas complexos e a implementação de segurança. Uma maneira pela qual eles contribuem para a parte de segurança é que ela permite que os desenvolvedores ocultem os nomes dos campos.
Também pode ser usado para filtrar colunas não indexadas, deixando apenas os campos que são mostrados mais rapidamente na pesquisa.
19. Não use ***** BY RAND ()
Esse é um daqueles truques que parecem ótimos à primeira vista e onde muitos programadores iniciantes geralmente caem. Você pode não ter percebido o incrível gargalo que pode ser causado se você usar essa técnica em suas solicitações.
Se você realmente precisa de tabelas aleatórias para o seu resultado, existem maneiras muito melhores de fazê-lo. É claro que eles ocuparão mais código, mas você evitará um possível gargalo que aumenta exponencialmente à medida que seu conteúdo cresce.
O problema é que o MySQL precisará executar RAND () (que requer poder de processamento) para cada uma das linhas antes de requisitá-las e retornar uma única linha.
// a maneira de NÃO fazer isso: $ r = mysql_query ("SELECT nome de usuário FROM usuário ***** BY RAND () LIMIT 1"); // muito melhor: $ r = mysql_query ("SELECT count (*) FROM usuário"); $ d = mysql_fetch_row ($ r); $ rand = mt_rand (0, $ d [0] - 1); $ r = mysql_query ("SELECT nome de usuário DO usuário LIMIT $ rand, 1"); Portanto, você seleciona um número aleatório menor que o número de resultados e o utiliza como deslocamento na cláusula LIMIT.20. Otimize a cláusula WHERE
Aqui estão algumas dicas para otimizar a cláusula WHERE :
- Remova parênteses desnecessários. Por exemplo:
DE: (a 5 AND b = c AND a = 5
- COUNT (*) é otimizado para retornar um SELECT muito mais rápido, desde que seja para uma tabela e sem usar WHERE. Por exemplo:
SELECIONE A CONTAGEM (*) DA tabela.
- A opção SQL_SMALL_RESULT pode ser usada com GROUP BY ou DISTINCT para indicar que o conjunto de resultados é pequeno. Nesse caso, o MySQL usa tabelas temporárias muito rápidas para armazenar a tabela resultante em vez de usar a classificação.
Tutoriais MySQL
Artigo