Algumas boas praticas do uso do MySQL

Algumas boas praticas do uso do MySQL

Operações de banco de dados tendem a ser o principal gargalo para a maioria das aplicações web hoje em dia. Não são apenas os administradores de banco de dados que têm de se preocupar com esses problemas de desempenho. Nós, como programadores precisamos fazer a nossa parte, estruturando as tabelas corretamente e escrevendo consultas mais otimizadas . Aqui estão algumas técnicas de otimização do MySQL para programadores.

---

1- Use LIMIT 1 quando a query retornar apenas um resultado.

Às vezes, quando você estiver consultando suas tabelas, você já sabe que você está procurando apenas uma linha. Você pode estar buscando um registro único, ou você pode apenas ser apenas verificando a existência de qualquer número de registros que satisfaçam a cláusula WHERE.
Em tais casos, adicionando o limite de 1 a sua consulta pode aumentar o desempenho. Desta forma, o mecanismo de banco vai parar a verificação de registros depois que encontrar apenas um, em vez de atravessar toda a tabela ou índice.

 
// Existe algum usuário do estado de SP?
 
// de uma maneira mais lenta:
$r = mysql_query("SELECT * FROM usuarios WHERE estado = 'SP'");
if (mysql_num_rows($r) > 0) {
    // ...
}

// um modo melhor:
$r = mysql_query("SELECT 1 FROM user WHERE state = 'Alabama' LIMIT 1");
if (mysql_num_rows($r) > 0) {
    // ...
}

2- Não use ORDER BY RAND().

Este é um daqueles truques que parecem legais de começo, e muitos programadores novatos caem nessa armadilha. Você pode não perceber que tipo de gargalo terrível você pode criar uma vez que você começar a usar isso em suas consultas.
Se você realmente precisa de linhas aleatórias de seus resultados, há maneiras muito melhores de fazer isso. Apenas com um pouco de código adicional resolvemos isso.Dessa forma evitando um gargalo que exponencialmente fica pior conforme seu banco de dados cresce. O problema é que o MySQL tem de executar a operação RAND() para cada linha da tabela antes de classificá-la e retornar o resultado.

// O que é melhor evitar:
$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");
// O que é melhor usar:
$r = mysql_query("SELECT count(*) FROM user");
$d = mysql_fetch_row($r);
$rand = mt_rand(0,$d[0] - 1);

$r = mysql_query("SELECT username FROM user LIMIT $rand, 1");




Portanto, você escolhe um número aleatório menor do que o número de resultados e usa isso como o deslocamento em sua cláusula LIMIT.

3- Evite SELECT *.

Quanto mais dados são lidos a partir das tabelas, mais lenta se tornará a consulta.Além disso, quando o servidor de banco de dados é separado do servidor web, você terá mais atrasos devido à rede de dados que têm de ser transferidas entre os servidores.
É um bom hábito sempre especificar as colunas que você precisa quando você está fazendo o seu SELECT.

// não recomendado:
$r = mysql_query("SELECT * FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";

// recomendado:
$r = mysql_query("SELECT username FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";

/* as diferenças são mais significantes com consultas que retornam um grande número de resultados */


4- Prepared Statements.

Querys preparadas servem para filtrar as variáveis ​​que se ligam a ela, o que é ótimo para proteger seu aplicativo contra ataques de injeção SQL. Você pode, obviamente, filtrar as variáveis ​​manualmente também, mas esses métodos são mais propensos a erros humanos e esquecimento pelo programador. Este é um problema menor quando se utiliza algum tipo de quadro ou ORM.

Como nosso foco está no desempenho, eu deveria também mencionar os benefícios nessa área. Estes benefícios são mais significativas quando a mesma consulta está usando várias vezes em sua aplicação. Você pode atribuir valores diferentes para a mesma declaração preparada, mas o MySQL só terá que analisá-lo uma vez.

Também as últimas versões do MySQL transmitem instruções preparadas em forma binária nativa, que são mais eficientes e também podem ajudar a reduzir os atrasos de rede.

Houve uma época em que muitos programadores costumavam evitar declarações preparadas de propósito, por uma única razão importante. Eles não estavam sendo armazenados em cache pelo cache de consultas do MySQL. Mas, desde por volta da versão 5.1, o cache de consulta é suportada também.

Para utilizar comandos preparados em PHP você pode conferir a extensão mysqli ou usar uma camada de abstração de dados como PDO.

// criando o prepared statement
if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) {

    // parametros
    $stmt->bind_param("s", $state);

    // executa query
    $stmt->execute();
   
    $stmt->bind_result($username);

    $stmt->fetch();

    printf("%s is from %s\n", $username, $state);

    $stmt->close();
}

5- Cuidado com Persistent Connections.

Conexões persistentes são destinadas a reduzir a sobrecarga de recriar conexões ao MySQL. Quando uma conexão persistente é criada, ela permanecerá aberta mesmo depois que o script terminar de ser executado. Uma vez que o Apache reutiliza os processos filhos, da próxima vez que o processo for executado por um novo script, ele irá reutilizar a mesma conexão com o MySQL .

mysql_pconnect () em PHP

Parece ótimo na teoria. Mas pela minha experiência pessoal (e muitos outros), este recurso acaba por não valer a pena. Você pode ter sérios problemas com limites de conexão, problemas de memória e assim por diante.

O Apache é executado muito paralelas, e cria muitos processos filhos. Este é o principal motivo que as conexões persistentes não funcionam muito bem neste ambiente. Antes de você pensar em usar a função mysql_pconnect (), consulte o seu administrador do sistema.

E é isso ai, até a próxima.

Read more »

Posted in: SQL