- 1. Convenção de nomes
- 2. Sempre use o tipo de dados correto
- 3. Use CHAR (1) em vez de VARCHAR (1)
- 4. CHAR para dados fixos
- 5. Evite usar formatos de data regionais
- 6. Otimize suas solicitações para o cache
- 7. Evite usar "SELECT *" em suas consultas
- 8. Use LIMIT 1 quando você quiser apenas uma única linha
- 9. Uso de ***** BY
- 10. Escolha um motor de base adequado
- 11. Use a cláusula EXISTS sempre que necessário
- 12. Use EXPLAIN em suas consultas SELECT
- 13. Indexar e usar o mesmo tipo de coluna para junções
- 14. Use NOT NULL se puder
- 15. Tabelas de tamanho fixo (estáticas) são mais rápidas
- 16. Particionamento Vertical
- 17. Armazena endereços IP como UNSIGNED INT
- 18. Crie visualizações para simplificar o uso comum em tabelas
- 19. Não use ***** POR RAND ()
- 20. Otimize a cláusula WHERE
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 que ele nos oferece 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 tanto do ponto de vista do programador quanto do administrador do banco de dados.
Que ferramenta usaremos?Console de comando MySQL '] O MySQL possui um programa, chamado com o mesmo nome do banco de dados (mysql) que é usado para gerenciar o banco de dados por linha de comando.
janelasEle 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 tenhamos instalado o MySQL. Para acessar o console MySQL no Windows, teremos que estar localizados nesse diretório.
LinuxVeja o seguinte link:
Acesse MySQL no Linux
MacComputadores com sistema Mac OS X eles têm o terminal de linha de comando integrado entre os aplicativos disponíveis. Para acessar o mesmo comando é usado como no Linux.
phpMyAdminÉ uma ferramenta de software livre escrita em PHP, usada para administrar o MySQL através da Internet. Se você tiver um ambiente de desenvolvimento local instalado, como XAMPP ou WAMP, esta ferramenta já estará instalada. Se você possui um plano de hospedagem com painel administrativo, a maioria deles oferece esta ferramenta como administrador de banco de dados MySQL.
[color = rgb (169,169,169)] phpMyAdmin do XAMPP [/ color]
PROLONGAR
[color = # a9a9a9] phpMyAdmin do CPanel [/ color]
Tendo essas 2 ferramentas em mãos, podemos começar a testar todas essas boas práticas que mostramos a seguir.
Estes são As 20 melhores práticas de MySQL:
1. Convenção de nomes
Existem padrões de codificação para todas as linguagens de programação, mas em termos do MySQL não encontramos nenhuma prática de codificação universal que todos sigam. No entanto, ao revisar vários frameworks de código aberto baseados em PHP, filtramos algumas regras gerais de aplicação que nos ajudarão a escrever consultas SQL mais rapidamente, eliminando confusões e conflitos, tanto na consulta quanto na linguagem de programação que usamos.
Regras geraisLembre-se das seguintes regras para evitar problemas.
- Use letras minúsculas, pois ajuda na velocidade de digitação, evitará erros no caso de funções sensíveis a minúsculas e maiúsculas, etc.
- Não use espaço em branco, use o sublinhado (_).
- Não use números nos nomes, apenas caracteres do alfabeto inglês.
- Use nomes compreensíveis válidos.
- Os nomes devem ser autoexplicativos.
- Os nomes não devem conter mais de 64 caracteres.
- Evite usar prefixos.
Regras para nomes de banco de dadosSiga 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 singular tanto quanto possível.
- Evite prefixos sempre que possível.
Regras para nomes de tabelasUse letras minúsculas para nomes de tabelas: o MySQL é geralmente hospedado em servidores Linux, diferencia maiúsculas de minúsculas, então a melhor prática é colocar nomes de tabelas em letras minúsculas.
- Os nomes das tabelas devem estar no singular: a tabela é uma entidade única assim como o modelo, por isso é estranho ter o nome da tabela no plural.
- Prefixos no nome da tabela: Já vimos muitas vezes que as tabelas são prefixadas com o nome do banco de dados ou com 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 dos camposUse 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 sempre que possível.
- Os nomes dos campos devem ser compreensíveis, por exemplo: preço, nome_empresa, etc.
- Nome da coluna primária: a chave primária pode ter nome de id ou nome de tabela _id. Isso vai depender da escolha.
- Evite usar palavras reservadas nos campos: *****, data, etc. Nestes casos, é preferível usar prefixos como record_date, etc.
- Evite usar nomes de coluna com o mesmo nome de tabela. Isso pode causar confusão ao escrever suas consultas.
- Evite nomes em siglas abreviadas ou concatenadas.
2. Sempre use o tipo de dados correto
Use tipos de dados com base na natureza dos dados. O uso de tipos de dados irrelevantes pode ocupar mais espaço ou levar a erros.
ExemploUsar varchar (20) para armazenar valores de data e hora em vez de DATETIME pode causar erros durante os cálculos de hora relacionados à data e também é possível no caso de armazenamento de dados inválido.
3. Use CHAR (1) em vez de VARCHAR (1)
Se você armazenou uma única sequência de caracteres, usar CHAR (1) em vez de VARCHAR (1) porque VARCHAR (1) levará um byte extra para armazenar informações. Com o qual levar isso em conta ao gerenciar esses personagens.
4. CHAR para dados fixos
Use dados CHAR para armazenar apenas os 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
Ao usar tipos de dados:
- DATA HORA
- ENCONTRO: DATA
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 suas solicitações para o cache
A maioria de Os servidores MySQL têm o sistema de cache habilitado. É um dos métodos mais eficazes para melhorar o desempenho, que vem da mão do mecanismo de banco de dados. Quando a mesma solicitação é executada várias vezes, o resultado é obtido do cache, o que é muito mais rápido.
O exemplo a seguir está em PHP:
// Cache NÃO funciona $ r = mysql_query ("SELECT nome DE usuários WHERE registro> = CURDATE ()");
// Cache FUNCIONA $ today = date ("Y-m-d"); $ r = mysql_query ("SELECT nome DE usuários WHERE registro> = '$ hoje'"); A razão de não funcionar 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 desabilitar o cache nessa consulta.
7. Evite usar "SELECT *" em suas consultas
Como regra geral, quanto mais dados são lidos das tabelas, mais lenta é a consulta. Considerando que algumas tabelas de produção podem conter dezenas de colunas, algumas das quais são compostas de grandes tipos de dados, não seria aconselhável selecionar todas elas.
É um bom hábito especifique as colunas necessárias em 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 só precisa de uma única linha. Nestes casos, você deve solicitar um único resultado do banco de dados, ou então ele verificará cada uma das correspondências da cláusula WHERE.
Nesses casos, adicionar LIMIT 1 à sua consulta pode melhorar significativamente a velocidade. Dessa forma, o banco de dados interromperá a varredura dos resultados no momento em que encontrar um, em vez de percorrer toda a tabela ou um índice.
// 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[/Cor]
9. Uso de ***** BY
O uso de ***** DE pode diminuir o tempo de resposta em ambientes multiusuário. Portanto, recomendamos que a cláusula ***** BY seja usada somente quando necessário.
Não abuse de seu uso.
10. Escolha um motor de base adequado
Se você desenvolver um aplicativo que lê dados com mais freqüência do que escrever.
(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 ler
(por exemplo: banco em tempo real), escolha o mecanismo de armazenamento InnoDB.
A escolha do mecanismo de armazenamento incorreto afetará o desempenho de suas consultas.
11. Use a cláusula EXISTS sempre que necessário
Se você quiser verificar os dados, não use:
If (SELECT contagem (*) da tabela WHERE col = 'algum valor')> 0Em vez disso, use a cláusula EXISTS:
Se EXISTS (SELECT * da tabela WHERE col = 'algum valor')O que é mais rápido no tempo de resposta.
12. Use EXPLAIN em suas consultas SELECT
Usar a palavra-chave EXPLAIN fornecerá muitos detalhes internos sobre o que o MySQL faz para executar sua consulta. Isso pode ajudá-lo a identificar gargalos e outros problemas com sua consulta ou estrutura de tabela.
O resultado de uma consulta EXPLAIN mostrará os índices que estão sendo usados, como a tabela está sendo explorada, como está sendo ordenada, etc.
Selecione uma consulta SELECT (de preferência complexa, com junçõ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, isso nos mostraria a seguinte tela:
PROLONGAR
Depois de adicionar o índice à tabela de estado, ele ficaria assim:
PROLONGAR
13. Indexar e usar o mesmo tipo de coluna para junções
Se seu aplicativo contém muitas instruções JOIN, você deve se certificar de que as colunas que você juntou estão indexadas em ambas as tabelas. Isso afeta como MySQL otimiza internamente Operações JOIN.
Além disso, as colunas que você está unindo devem ser do mesmo tipo. Por exemplo, se você estiver juntando uma coluna do tipo DECIMAL com uma coluna do tipo INT de outra tabela, o MySQL não será capaz de usar pelo menos um dos dois índices. Até mesmo a codificação de caracteres precisa ser do mesmo tipo para as colunas de Tipo de corda.
// procurando empresas em minha cidade $ r = mysql_query ("SELECT nome_da_empresa FROM usuários LEFT JOIN companyias ON (users.city = companyias.city) WHERE users.id = $ user_id");Ambas as colunas de cidade devem ser indexadas e ambas devem ser do mesmo tipo e codificação de caracteres ou o MySQL terá que fazer uma varredura completa nas tabelas.
14. Use NOT NULL se puder
A menos que você tenha um motivo específico para usar o valor NULL, você sempre deve definir suas colunas como NOT NULL.
Em primeiro lugar, pergunte-se se haveria uma diferença entre ter uma string vazia e um valor NULL (ou para campos INT: 0 versus NULL). Se não houver nenhum problema entre os dois valores, você não precisa de um campo NULL. As colunas NULL requerem espaço adicional e podem adicionar complexidade às suas instruções de comparação. Apenas evite-os sempre que puder.
Em qualquer caso, entendemos que em alguns casos muito específicos há uma razão para usar colunas NULL, o que nem sempre é ruim.
15. Tabelas de tamanho fixo (estáticas) são mais rápidas
Quando cada coluna em uma tabela tem comprimento fixo, toda a tabela é considerada "estática" ou "comprimento fixo".
Alguns exemplos de tipos de coluna 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 deverá ser tratada de forma diferente pelo Motor MySQL.
Tabelas de tamanho fixo podem aumentar a produtividade porque é mais rápido para o mecanismo MySQL pesquisar seus registros. Quando você deseja ler uma linha específica da tabela, pode calcular rapidamente sua posição. Se o tamanho da linha não for fixo, cada vez que você tiver que pesquisar, deverá primeiro consultar o índice da chave primária.
Eles também são mais fáceis de pesquisar e reconstruir após um acidente. Por outro lado, eles também podem ocupar mais espaço.
16. Particionamento Vertical
O particionamento vertical é o ato de separar a estrutura da tabela verticalmente por motivos de otimização.
Exemplo 1:
Você certamente terá uma tabela de usuário que contém um endereço postal, o que não é usado com frequência. Aqui você pode dividir a tabela e armazenar os endereços em uma tabela separada. Dessa forma, sua mesa de usuário principal teria um tamanho mais compacto. Como você sabe, quanto menor, mais rápida é a mesa.
Exemplo 2:
Você tem um campo "last_access" em sua tabela. Ele é 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 este campo em outra tabela para que as modificações em sua tabela de usuário sejam mínimas.
Mas você também tem que se certificar de que não precisa unir constantemente as duas tabelas após o particionamento ou você sofrerá uma queda de 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 eles podem armazenar endereços IP como números inteiros. Quando você usa um INT você usa apenas 4 bytes na memória, e também tem um tamanho fixo na tabela.
Mas você tem que ter certeza de que o coluna é INT NÃO ASSINADA (inteiro sem sinal) porque os endereços IP usam todo o intervalo sem sinal de 32 bits.
Em suas consultas, você pode usar o Função INET_ATON () para converter um endereço IP em um inteiro, e INET_NTOA () para fazer o oposto. Existem também funções semelhantes em PHP chamadas ip2long () Y long2ip ().
18. Crie visualizações para simplificar o uso comum em tabelas
As visualizações ajudam a simplificar os esquemas complexos e a implementação de segurança. Uma forma de contribuir para a parte de segurança é permitir que você esconda os nomes dos campos dos desenvolvedores.
Também pode ser usado para filtrar colunas não indexadas, deixando apenas os campos que são exibidos mais rapidamente na pesquisa.
19. Não use ***** POR RAND ()
Esse é um daqueles truques que parecem ótimos à primeira vista e onde muitos programadores novatos tendem a cair. Você pode não ter percebido o incrível gargalo que pode ser causado pelo uso dessa técnica em suas solicitações.
Se você realmente precisa de tabelas aleatórias para o seu resultado, há maneiras muito melhores de fazer isso. É claro que eles irão ocupar mais código, mas você estará evitando um possível gargalo que aumenta exponencialmente conforme o seu conteúdo cresce.
O problema é que o MySQL terá que ser executado RAND () (o que requer poder de processamento) para cada uma das linhas antes de classificá-las e retornar uma única linha.
// a maneira de NÃO fazer isso: $ r = mysql_query ("SELECT username FROM user ***** BY RAND () LIMIT 1"); // muito melhor: $ r = mysql_query ("SELECT count (*) FROM user"); $ d = mysql_fetch_row ($ r); $ rand = mt_rand (0, $ d [0] - 1); $ r = mysql_query ("SELECT nome de usuário FROM nome de usuário LIMIT $ rand, 1");Portanto, você seleciona um número aleatório menor que o número de resultados e o usa como o deslocamento na cláusula LIMIT.
20. Otimize a cláusula WHERE
Estes são alguns dicas para otimizar a cláusula WHERE:
- Remova parênteses desnecessários. Por exemplo:
De para5 AND b = c AND a = 5
- CONTAR (*) ele é otimizado para retornar um SELECT muito mais rápido, desde que seja para uma tabela e sem usar WHERE. Por exemplo:
SELECIONE COUNT (*) DA tabela.
- O Opção SQL_SMALL_RESULT, pode ser usado com GRUPO POR ou DISTINTO para indicar que o conjunto de resultados é pequeno. Neste caso, o MySQL usa tabelas temporárias muito rápidas para armazenar a tabela resultante em vez de usar classificação.
Tutoriais de MySQL
