{"id":3648,"date":"2014-11-03T11:51:19","date_gmt":"2014-11-03T13:51:19","guid":{"rendered":"http:\/\/blog.turbosite.com.br\/?p=3648"},"modified":"2014-11-03T11:51:19","modified_gmt":"2014-11-03T13:51:19","slug":"dicas-para-otimizar-seu-mysql","status":"publish","type":"post","link":"https:\/\/www.turbosite.com.br\/blog\/dicas-para-otimizar-seu-mysql\/","title":{"rendered":"Dicas para otimizar seu MySQL"},"content":{"rendered":"<p>Suas consultas est\u00e1o demorando muito tempo? Est\u00e1 gerando filas de conex\u00f5es? Todas as opera\u00e7\u00f5es de banco est\u00e3o sofrendo lentid\u00e3o? Se respondeu sim para qualquer uma das perguntas, ent\u00e3o est\u00e1 na hora de otimizar seu Banco de Dados.\u00a0\u00a0A equipe da <a title=\"Turbosite\" href=\"https:\/\/www.turbosite.com.br\/\" target=\"_blank\">Turbosite<\/a>\u00a0compartilha nesse artigo algumas dicas para otimiza\u00e7\u00e3o de seu Banco de Dados. Utilizando poucas fun\u00e7\u00f5es <strong>MySQL<\/strong>, o resultado no desempenho de seu sistema pode ser surpreendente.<\/p>\n<p>Dos tr\u00eas modos de otimiza\u00e7\u00e3o (configura\u00e7\u00e3o de hardware do servidor, configura\u00e7\u00e3o de rede do servidor e otimiza\u00e7\u00e3o de c\u00f3digo), apresentamos aqui m\u00e9todos para otimiza\u00e7\u00e3o por\u00a0c\u00f3digo MySQL.<\/p>\n<p>&nbsp;<\/p>\n<h2>MYSQLREPORT<\/h2>\n<p>Como primeiro passo,\u00a0voc\u00ea pode usar o comando <strong>MYSQLREPORT<\/strong>\u00a0para gerar relat\u00f3rios sobre bancos de dados que est\u00e3o atingindo o limite de sua capacidade de uso \u00a0e estabelecer um patamar para opera\u00e7\u00f5es e usos adicionais. Talvez tamb\u00e9m voc\u00ea se veja obrigado a modificar arquivo de configura\u00e7\u00e3o (my.cnf) para registrar o log de consultas lentas. Identifique as consultas que est\u00e3o processando muito devagar e as poss\u00edveis causas.<\/p>\n<p>Para acessar a\u00a0documenta\u00e7\u00e3o completa do\u00a0MYSQLREPORT, recomendamos esse link:\u00a0<a title=\"mysqlreport\" href=\"http:\/\/man.he.net\/man1\/mysqlreport\" target=\"_blank\">man.he.net\/man1\/mysqlreport<\/a>.<\/p>\n<p>&nbsp;<\/p>\n<h2>EXPLAIN<\/h2>\n<p>Aplique o comando <strong>EXPLAIN<\/strong>\u00a0antes do script <em>SELECT<\/em> para o MySQL retornar\u00a0um registro contendo a an\u00e1lise do script para aquela tabela consultada. Esse m\u00e9todo retorna algumas informa\u00e7\u00f5es, como <em>ID<\/em>, <em>SELECT_TYPE<\/em>, <em>TABLE<\/em>, <em>TYPE<\/em>, <em>KEY_LEN<\/em>, <em>REF<\/em>, sendo que as informa\u00e7\u00f5es que voc\u00ea vai querer para avaliar\u00a0o desempenho do c\u00f3digo s\u00e3o:<\/p>\n<ul>\n<li><strong><em>POSSIBLE_KEYS<\/em>:\u00a0<\/strong>mostra os \u00edndices dispon\u00edveis para serem utilizados naquela consulta;<\/li>\n<li><strong><em>KEY<\/em>:<\/strong>\u00a0mostra o \u00edndice escolhido pelo MySQL para realizar a consulta;<\/li>\n<li><strong><em>ROW<\/em>:<\/strong>\u00a0n\u00famero estimado de linhas percorridas para encontrar o resultado do\u00a0<em>SELECT;<\/em><\/li>\n<li><strong><em>EXTRA<\/em>:\u00a0<\/strong>sugest\u00f5es de otimiza\u00e7\u00e3o em \u00edndices da consultas, como usar <em>DISTINCT<\/em>, <em>NOT<\/em> <em>EXIST<\/em>, <em>USING<\/em> <em>INDEX<\/em>, entre outras.<\/li>\n<\/ul>\n<pre class=\"lang:mysql decode:true\">EXPLAIN SELECT * FROM funcionarios;<\/pre>\n<p>&nbsp;<\/p>\n<h2><strong>ANALYZE<\/strong><\/h2>\n<p>Use o comando <strong>ANALYZE<\/strong> para gerar uma distribui\u00e7\u00e3o de chaves, para a tabela a ser utilizada pelo otimizador de consultas do MySQL, para decidir quais \u00edndices ser\u00e3o melhores se utilizados em uma consulta.<\/p>\n<pre class=\"lang:mysql decode:true\">ANALYZE TABLE funcionarios;<\/pre>\n<p>&nbsp;<\/p>\n<h2><strong>OPTIMIZE\u00a0<\/strong><\/h2>\n<p>Se h\u00e1 grandes quantidades de inser\u00e7\u00f5es\/exclus\u00f5es\u00a0em uma tabela, principalmente se a tabela conter muitos campos do tipo <em>varchar<\/em>, <em>text<\/em> e <em>blob<\/em>, o disco ser\u00e1 comprometido \u00e0 v\u00e1rias fragmenta\u00e7\u00f5es. O comando <strong>OPTIMIZE<\/strong> deve ser usado com freq\u00fc\u00eancia para esse tipo de situa\u00e7\u00e3o.\u00a0O comando ira otimizar a movimenta\u00e7\u00e3o da cabe\u00e7a de leitura e grava\u00e7\u00e3o do disco durante a recupera\u00e7\u00e3o de dados, implementando\u00a0a desfragmenta\u00e7\u00e3o do disco causada por campos de comprimento vari\u00e1vel.<\/p>\n<p>&nbsp;<\/p>\n<pre class=\"lang:mysql decode:true\">OPTIMIZE TABLE funcionarios;<\/pre>\n<p>&nbsp;<\/p>\n<h2><strong>LOAD DATA INFILE<\/strong><\/h2>\n<p>Para a importa\u00e7\u00e3o de dados para uma tabela, o comando\u00a0<strong>LOAD DATA INFILE<\/strong>, no lugar do comando <em>SELECT<\/em>, ir\u00e1 apresentar um desempenho at\u00e9 20 vezes melhor.<\/p>\n<pre class=\"lang:mysql decode:true\">LOAD DATA INFILE 'filedata.dat' INTO TABLE funcioanrios (col1,col2,...,colM) \n     FIELDS TERMINATED BY '|'\");<\/pre>\n<p>&nbsp;<\/p>\n<h2><strong>TRUNCATE<\/strong><\/h2>\n<p>O comando <em>DELETE<\/em> deleta linha por linha, enquanto <strong>TRUNCATE<\/strong> deleta todas as linhas de uma s\u00f3 vez. Portanto, se voc\u00ea n\u00e3o estiver interessado no n\u00famero de linhas suprimidas de uma tabela, como resultado do comando <em>DELETE<\/em>, utilize o comando <em>TRUNCATE<\/em> com a seguinte sintaxe:<\/p>\n<pre class=\"lang:php decode:true \">TRUNCATE TABLE funcionarios;<\/pre>\n<p>&nbsp;<\/p>\n<h2><strong>Prioridade\u00a0dos comandos<\/strong><\/h2>\n<p>Se h\u00e1 mais consultas do que inser\u00e7\u00f5es de dados, voc\u00ea pode ter uma <strong>prioridade<\/strong> mais baixa do comando <em>INSERT<\/em> utilizando<\/p>\n<pre class=\"lang:mysql decode:true \">INSERT LOW_PRIORITY<\/pre>\n<p>ou<\/p>\n<pre class=\"lang:mysql decode:true\">SELECT HIGH_PRIORITY<\/pre>\n<p>Se o cliente n\u00e3o est\u00e1 interessado nos resultados do comando <em>INSERT<\/em>, ent\u00e3o esta opera\u00e7\u00e3o j\u00e1 pode imediatamente utilizar menos os recursos do sistema, utilizando-se o seguinte comando:<\/p>\n<pre class=\"lang:mysql decode:true \">INSERT DELAYED<\/pre>\n<p>Isso torna o sistema mais r\u00e1pido porque acumula as inser\u00e7\u00f5es em lotes.<\/p>\n<p>&nbsp;<\/p>\n<h2>Permiss\u00f5es no MySQL<\/h2>\n<p>Al\u00e9m de\u00a0incrementar na\u00a0seguran\u00e7a da conex\u00e3o, criar usu\u00e1rios com permiss\u00f5es espec\u00edficas\u00a0reduzem o <em>overhead<\/em> das verifica\u00e7\u00f5es das permiss\u00f5es em todos os comandos realizados no MySQL, melhorando\u00a0assim no desempenho do sistema. Como exemplo, um leitor do blog apenas tem interesse na leitura de postagem, leitura e inser\u00e7\u00e3o de\u00a0coment\u00e1rios. Criar um usu\u00e1rio de nome &#8220;leitor&#8221;, com acesso apenas\u00a0a leitura da tabelas &#8216;postagens&#8217;, e acesso a leitura e escrita \u00e0 tabela &#8216;comentarios&#8217;, ser\u00e1 de fato uma boa pr\u00e1tica. A seguran\u00e7a ser\u00e1 incrementada, j\u00e1 que aquele usu\u00e1rio n\u00e3o ter\u00e1 permiss\u00e3o de acessar, modificar ou deletar outros registros que n\u00e3o s\u00e3o de seu respeito, como tamb\u00e9m o desempenho, j\u00e1 que o <em>overhead<\/em> do usu\u00e1rio constar\u00e1\u00a0suas propriedades de acessos de antem\u00e3o.<\/p>\n<pre class=\"lang:mysql decode:true\">GRANT Oper1,...,OperN ON db_name.tb_name TO user_name@computer_name IDENTIFIED BY password;<\/pre>\n<p>&nbsp;<\/p>\n<h2><strong>BENCHMARK<\/strong><\/h2>\n<p>Por fim, para saber quanto tempo uma determinada fun\u00e7\u00e3o ou express\u00e3o MySQL est\u00e1 demorando, use a fun\u00e7\u00e3o MySQL embutida:<\/p>\n<pre class=\"lang:mysql decode:true \">SELECT BENCHMARK ( 10000, ( SELECT col1 FROM funcionarios LIMIT 1 ) );<\/pre>\n<p>Lembrando que, para passar consultas\u00a0na fun\u00e7\u00e3o\u00a0<em>BENCHMARK<\/em>, obrigatoriamente a <em>query<\/em> deve retornar 1 linha com 1 coluna.<\/p>\n<p>&nbsp;<\/p>\n<h2>\u00a0Outras dicas corriqueiras<\/h2>\n<ul>\n<li>Utilize <strong>ORDER BY\u00a0<\/strong>sempre na primeira tabelas, de prefer\u00eancia em \u00edndices, quando estiver realizando uma consulta em v\u00e1rias tabelas interligadas;<\/li>\n<li>Utilize <strong>LIMIT<\/strong>, o MySQL ir\u00e1 primeiro avaliar o comando <em>LIMIT<\/em> para depois executar fun\u00e7\u00f5es como <em>ORDER BY<\/em> e <em>GROUP BY<\/em>;<\/li>\n<li>Realize <strong>UPDATE <\/strong>e<strong> INSERT\u00a0<\/strong>em um lote inteiro de registros, de uma s\u00f3 vez, al\u00e9m\u00a0de realizar uma a\u00e7\u00e3o\u00a0por cada registro;<\/li>\n<li>Remova par\u00eanteses desnecess\u00e1rios na cl\u00e1usula WHERE.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>Refer\u00eancias: <a href=\"http:\/\/www.comofazertudo.com.br\/computadores-e-internet\/como-otimizar-consultas-mysql\">Como otimizar consultas MySQL<\/a>, <a href=\"http:\/\/www.sitepoint.com\/using-explain-to-write-better-mysql-queries\/\">Using EXPLAIN to Write Better MySQL Queries<\/a>, <a href=\"http:\/\/www.webgoal.com.br\/como-otimizar-consultas-no-mysql\/\">Como otimizar consultas no MySQL-An\u00e1lise das queries<\/a>. <\/p>\n","protected":false},"excerpt":{"rendered":"<p>A equipe da Turbosite compartilha nesse artigo algumas dicas para otimiza\u00e7\u00e3o de seu Banco de Dados por meio de algumas fun\u00e7\u00f5es MySQL.<\/p>\n","protected":false},"author":1,"featured_media":3650,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,19],"tags":[185,433],"class_list":["post-3648","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-dicas","category-mysql","tag-dicas","tag-mysql"],"_links":{"self":[{"href":"https:\/\/www.turbosite.com.br\/blog\/wp-json\/wp\/v2\/posts\/3648","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.turbosite.com.br\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.turbosite.com.br\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.turbosite.com.br\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.turbosite.com.br\/blog\/wp-json\/wp\/v2\/comments?post=3648"}],"version-history":[{"count":0,"href":"https:\/\/www.turbosite.com.br\/blog\/wp-json\/wp\/v2\/posts\/3648\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.turbosite.com.br\/blog\/wp-json\/wp\/v2\/media\/3650"}],"wp:attachment":[{"href":"https:\/\/www.turbosite.com.br\/blog\/wp-json\/wp\/v2\/media?parent=3648"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.turbosite.com.br\/blog\/wp-json\/wp\/v2\/categories?post=3648"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.turbosite.com.br\/blog\/wp-json\/wp\/v2\/tags?post=3648"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}