![[Tutorial]10 Estratégias e boas práticas a aplicar em MySQL Empty](https://2img.net/i/empty.gif)
A utilização de MySQL é nos dias de hoje uma prática recorrente à grande maioria dos programadores e desenvolvedores web enquanto estão a desenvolver um projeto. Embora seja possível não lidar diretamente com MySQL, os utilizadores mais curiosos ou que simplesmente necessitam de fazer alterações através do MySQL irão fazê-lo recorrentemente. Para tal, além do conhecimento básico que lhe permita trabalhar com MySQL, é muito útil também saber quais os melhores procedimentos a ter enquanto o faz, evitando assim criar erros desnecessáriamente que poderão arruinar em pouco tempo o trabalho que tem vindo a desenvolver. Neste artigo vamos-lhe apresentar um conjunto de 10 estratégias e boas práticas a aplicar em MySQL que vão fazer de si um desenvolvedor mais competente, aumentando também o seu conhecimento nesta área.
[Tens de ter uma conta e sessão iniciada para poderes visualizar esta imagem]
1. OTIMIZE AS QUERIES PARA A CACHE DE QUERIES
A grande maioria dos servidores MySQl têm a cache de queries ativada, pelo que é um dos métodos mais efetivos para melhorar a performance, sendo processada pelo motor do banco de dados. Quando a mesma query é executada várias vezes, o resultado é obtido da cache e não através do resultado de uma nova consulta ao banco de dados, o que torna o processo extremamente rápido. A questão á volta deste ponto prende-se com o facto de que a grande maioria das pessoas se esquece dele. Então podemos fazer algo para evitar que a query seja executada e que seja obtido o resultado a partir da cache:
A razão pela qual a cache não funciona na primeira linha de código tem a ver com a utilização da função curdate(). Uma vez que o resultado desta função pode diferir, MySQL irá desativar a cache para aquela query. Então aquilo que é necessário é alterar apenas um pouco o código como esta demonstrado no segundo exemplo.
2. SEJA ESPECIFICO NAS QUERIES SELECT
Ao utilizar a palavra chave Explain pode ter um outro olhar sobre aquilo que está a ser executada na sua query. Isto pode ser extremamente benéfico quando se procuram problemas no banco de dados. O resultado do Explain irá mostrar-lhe quais os index que estão a ser utilizados, como a tabela está a ser processada, entre outros pontos importantes. Utilize uma query Select e introduza a palavra chave Explain. Desta forma poderá encontrar erros e problemas associados ao banco de dados e às tabelas.
3. DELIMITE AS SUAS QUERIES
A delimitação da query pode ser um método que poderá otimizar bastante o processo em si. Se sabe que está só à procura por exemplo de uma linha, poderá otimizar a query para procurar uma só linha, impedindo assim que procure X outras linhas sem necessidade, atrasando o processo. Então, introduzindo o LIMIT 1 na sua query irá aumentar a performance da mesma, impedindo que o motor do banco de dados pesquise mais resultados após ter encontrado 1, desta forma a tabela não será toda processada e sim processada até ser encontrado um resultado:
4. INDEXE OS CAMPOS DE PESQUISA
Os index não são apenas para serem utilizados como chaves primárias ou chaves únicas. Se existem colunas na sua tabela pelas quais poderá fazer uma pesquisa, indexe-as. A diferença na velocidade de pesquisa entre uma coluna indexada e uma coluna não indexada pode chegar aos 300%, o que num website com algum movimento poderá significar uma alteração bastante grande em termos de velocidade. Numa era em que os utilizadores e visitantes de websites gostam de velocidade de carregamento e velocidade de resposta dos websites, é imperativo que você otimize simples elementos como este que acabámos de citar.
5. INDEX E COLUMN TYPES NOS JOINS
Se por ventura o seu aplicativo utiliza o Join frequentemente, tem de se certificar que as colunas que está a fazer Join estão indexadas em ambas as tabelas. Este processo vai afetar a maneira como o MySQL lida com a operação Join e como esta vai estar otimizada. Além disso, as tabelas que vão ser alvo do Join necessitam de ser do mesmo gênero. Por exemplo, se fizer Join entre duas tabelas de gêneros diferentes apenas poderá ser utilizado o index de uma delas, o que por si só irá atrasar o processo.
6. EVITE UTILIZAR QUERIES COM SELECT *
A utilização de queries com o Select * irá pôr uma grande carga no motor do banco de dados, e quanto maior este for, mais lento o processo irá ser. O Select * irá seleccionar todas as colunas numa tabela, carregando uma quantidade provavelmente desnecessária de resultados. Então, em jeito de exemplo:
7. COLUNAS PEQUENAS SÃO MAIS RÁPIDAS
Nos motores dos bancos de dados o item provavelmente mais importante é o espaço em disco. Manter os dados pequenos e compactados é normalmente sinónimo de performance, ao reduzir a quantidade de dados transmitidas e transferidas pelo banco de dados. Se uma tabela vai ter poucas linhas, não existe razão para determinar uma chave primaria como INT, ao invés de utilizar MEDIUMINT, SMALLINT ou mesmo TINYINT. Se por ventura não necessitar do elemento de hora, pode utilizar DATE ao invés de DATETIME.
8. NÃO ORDENE COM O RAND()
O Rand() a principio poderá parecer algo caido do céu, e frequentemente os recém chegados ao MySQL caem no erro de utilizar de forma errada. Se realmente pretende obter linhas aleatórias dos seus resultados, existem métodos mais apropriados para o fazer. Embora seja necessário utilizar mais código para isso, você irá fazer com que não tenha um erro que não otimiza de todo as suas queries. Ao utilizar o Rand() o motor do banco de dados irá processar todas as linhas existentes no resultado da pesquisa, sendo um processo que pode levar demasiado tempo e utilizar carga de processamento sem necessidade. Então:
9. UTILIZE O CAMPO ID
A utilização do campo ID numa tabela é muito importante e bastante valiosa. Ao introduzir o campo ID, você não só poderá utilizá-lo como chave primária, com incremento automático como também poderá evitar conflitos com outras tabelas. Além disso, ao utilizar uma chave primária que seja por exemplo VARCHAR, o tempo de processamento entre um INT e um VARCHAR é diferente, sendo que é mais rápido o tempo de processamento para o campo que seja do tipo INT. Então à partida seja sempre útil e estará de certa forma a otimizar o seu banco de dados se utilizar um campo ID do tipo INT como chave primária.
10. UTILIZE ENUM E NÃO VARCHAR
A utilização do tipo ENUM para as colunas faz com que estas se tornem mais rápidas e compactas. Internamente serão guardadas como TINYINT, e no entanto podem conter e dar como resultado valores string. Se tiver um campo que tem apenas alguns valores diferentes, a utilização de ENUM é vantajosa quando comparada com VARCHAR. Imagine que tem uma coluna chamada Estado e que os valores dentro dela poderão ser apenas ligado ou desligado. Utilize ENUM e irá estar a otimizar este processo.
[Tens de ter uma conta e sessão iniciada para poderes visualizar esta imagem]
1. OTIMIZE AS QUERIES PARA A CACHE DE QUERIES
A grande maioria dos servidores MySQl têm a cache de queries ativada, pelo que é um dos métodos mais efetivos para melhorar a performance, sendo processada pelo motor do banco de dados. Quando a mesma query é executada várias vezes, o resultado é obtido da cache e não através do resultado de uma nova consulta ao banco de dados, o que torna o processo extremamente rápido. A questão á volta deste ponto prende-se com o facto de que a grande maioria das pessoas se esquece dele. Então podemos fazer algo para evitar que a query seja executada e que seja obtido o resultado a partir da cache:
Código:
// a cache nao funciona
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
// a cache funciona
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");
A razão pela qual a cache não funciona na primeira linha de código tem a ver com a utilização da função curdate(). Uma vez que o resultado desta função pode diferir, MySQL irá desativar a cache para aquela query. Então aquilo que é necessário é alterar apenas um pouco o código como esta demonstrado no segundo exemplo.
2. SEJA ESPECIFICO NAS QUERIES SELECT
Ao utilizar a palavra chave Explain pode ter um outro olhar sobre aquilo que está a ser executada na sua query. Isto pode ser extremamente benéfico quando se procuram problemas no banco de dados. O resultado do Explain irá mostrar-lhe quais os index que estão a ser utilizados, como a tabela está a ser processada, entre outros pontos importantes. Utilize uma query Select e introduza a palavra chave Explain. Desta forma poderá encontrar erros e problemas associados ao banco de dados e às tabelas.
3. DELIMITE AS SUAS QUERIES
A delimitação da query pode ser um método que poderá otimizar bastante o processo em si. Se sabe que está só à procura por exemplo de uma linha, poderá otimizar a query para procurar uma só linha, impedindo assim que procure X outras linhas sem necessidade, atrasando o processo. Então, introduzindo o LIMIT 1 na sua query irá aumentar a performance da mesma, impedindo que o motor do banco de dados pesquise mais resultados após ter encontrado 1, desta forma a tabela não será toda processada e sim processada até ser encontrado um resultado:
Código:
// método errado
$r = mysql_query("SELECT * FROM user WHERE country = 'Brazil'");
if (mysql_num_rows($r) > 0) {
// ...
}
// método otimizado
$r = mysql_query("SELECT 1 FROM user WHERE country = 'Brazil' LIMIT 1");
if (mysql_num_rows($r) > 0) {
// ...
}4. INDEXE OS CAMPOS DE PESQUISA
Os index não são apenas para serem utilizados como chaves primárias ou chaves únicas. Se existem colunas na sua tabela pelas quais poderá fazer uma pesquisa, indexe-as. A diferença na velocidade de pesquisa entre uma coluna indexada e uma coluna não indexada pode chegar aos 300%, o que num website com algum movimento poderá significar uma alteração bastante grande em termos de velocidade. Numa era em que os utilizadores e visitantes de websites gostam de velocidade de carregamento e velocidade de resposta dos websites, é imperativo que você otimize simples elementos como este que acabámos de citar.
5. INDEX E COLUMN TYPES NOS JOINS
Se por ventura o seu aplicativo utiliza o Join frequentemente, tem de se certificar que as colunas que está a fazer Join estão indexadas em ambas as tabelas. Este processo vai afetar a maneira como o MySQL lida com a operação Join e como esta vai estar otimizada. Além disso, as tabelas que vão ser alvo do Join necessitam de ser do mesmo gênero. Por exemplo, se fizer Join entre duas tabelas de gêneros diferentes apenas poderá ser utilizado o index de uma delas, o que por si só irá atrasar o processo.
6. EVITE UTILIZAR QUERIES COM SELECT *
A utilização de queries com o Select * irá pôr uma grande carga no motor do banco de dados, e quanto maior este for, mais lento o processo irá ser. O Select * irá seleccionar todas as colunas numa tabela, carregando uma quantidade provavelmente desnecessária de resultados. Então, em jeito de exemplo:
Código:
// Select *
$r = mysql_query("SELECT * FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Bem-Vindo {$d['username']}";
// Select <campo>
$r = mysql_query("SELECT username FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Bem-Vindo {$d['username']}";7. COLUNAS PEQUENAS SÃO MAIS RÁPIDAS
Nos motores dos bancos de dados o item provavelmente mais importante é o espaço em disco. Manter os dados pequenos e compactados é normalmente sinónimo de performance, ao reduzir a quantidade de dados transmitidas e transferidas pelo banco de dados. Se uma tabela vai ter poucas linhas, não existe razão para determinar uma chave primaria como INT, ao invés de utilizar MEDIUMINT, SMALLINT ou mesmo TINYINT. Se por ventura não necessitar do elemento de hora, pode utilizar DATE ao invés de DATETIME.
8. NÃO ORDENE COM O RAND()
O Rand() a principio poderá parecer algo caido do céu, e frequentemente os recém chegados ao MySQL caem no erro de utilizar de forma errada. Se realmente pretende obter linhas aleatórias dos seus resultados, existem métodos mais apropriados para o fazer. Embora seja necessário utilizar mais código para isso, você irá fazer com que não tenha um erro que não otimiza de todo as suas queries. Ao utilizar o Rand() o motor do banco de dados irá processar todas as linhas existentes no resultado da pesquisa, sendo um processo que pode levar demasiado tempo e utilizar carga de processamento sem necessidade. Então:
Código:
// errado
$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");
// otimizado
$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");9. UTILIZE O CAMPO ID
A utilização do campo ID numa tabela é muito importante e bastante valiosa. Ao introduzir o campo ID, você não só poderá utilizá-lo como chave primária, com incremento automático como também poderá evitar conflitos com outras tabelas. Além disso, ao utilizar uma chave primária que seja por exemplo VARCHAR, o tempo de processamento entre um INT e um VARCHAR é diferente, sendo que é mais rápido o tempo de processamento para o campo que seja do tipo INT. Então à partida seja sempre útil e estará de certa forma a otimizar o seu banco de dados se utilizar um campo ID do tipo INT como chave primária.
10. UTILIZE ENUM E NÃO VARCHAR
A utilização do tipo ENUM para as colunas faz com que estas se tornem mais rápidas e compactas. Internamente serão guardadas como TINYINT, e no entanto podem conter e dar como resultado valores string. Se tiver um campo que tem apenas alguns valores diferentes, a utilização de ENUM é vantajosa quando comparada com VARCHAR. Imagine que tem uma coluna chamada Estado e que os valores dentro dela poderão ser apenas ligado ou desligado. Utilize ENUM e irá estar a otimizar este processo.