My SQL - Otimização

Março 2017

A otimização do MaySQL passa por três componentes:
  • Otimização do servidor MySQL
  • Otimização do banco de dados
  • Otimização dos pedidos

Otimização da configuração do servidor MySQL


A otimização du servidor pode incluir uma enormidade de métodos, o que nós tentaremos de apresentar, mas é somente uma introdução relativa aos métodos de base :
  • Compilação do servidor
  • Tuning dos parâmetros do servidor
  • Tuning de outros parâmetros


Para fazer uma boa otimização, é preciso proceder por uma metodologia empírica como fazer as modificações uma por uma e testar cada vez a reação do sistema para ter uma idéia do resultado.

Uma medida de desempenho antes e depois de ter efetuado a otimização permite ver se o sistema foi otimizado ou não.

Compilação do servidor


É preconizado de utilizar a versão código do servidor MySQL et de la compilar considerando os diferentes parâmetros do sistema como o jogo de caracteres a utilizar, o micro-processador no qual ele vai rodar e de utilizar um compilador adaptado (ex : pgcc para os micro-processadores Pentium).

Tuning dos parâmetros do servidor


E possível otimizar o funcionamento do MySQL trocando os valores dos parâmetros do servidor.
Para lembrar, para afixar os parâmetros é preciso utilizar o comando:

show variables;    


Para ver o efeito dos parâmetros no servidor é preciso lançar o comando:

show status;    


Existe inúmeras ferramentas de monitoring que permitem ver as repercussões das mudanças efetuadas parâmetros no servidor MySQL como Mytop equivalente ao comando top de linux.

O arquivo my.cnf contém o conjunto dos parâmetros a otimizar.

Numa primeira etapa, é possível começar com os parâmetros gerenciando a memória. O princípio a seguir é que quanto mais o servidor beneficia de memória mais rápido ele é, no entanto; é preciso que a memória esteja disponível.

MySQL contém um conjunto de buffers et cache internos possíveis de configurar o espaço dada a cada um a partir das variáveis do arquivo my.cnf. As duas variáveis as mais importantes são key_buffer_size e table_cache, pois elas são partilhadas por todos os threads que giram em torno do servidor e influenciam de maneira considerável os desempenhos.

Um exemplo de variável:
  • key_buffer_size: memória utilizada para salvar os índices MyISAM.
  • table_cache: Número de tabelas que podem ser abertas simultaneamente
  • read_buffer_size: memória utilizada para salvar os dados provenientes dos full escaners das tabelas.
  • sort_buffer: memória utilizada para salvar os dados de tabelas que serão classificadas por um ORDER BY.

Tuning de outros parâmetros


O servidor MySQL é preconizado para um funcionamento excelente no SOLARIS, no entanto, é possível de otimizá-lo nos outros OS para se aproximar de seu rendimento ideal.
A utilização do RAID-RAID 0 é preconizada para a otimização das operações de leitura escrita. Bem como a utilização dos discos SCSI antes dos IDE.

A utilização das redes rápidas otimiza os tempos de respostas e otimiza a comunicação entre cliente/escravo para a replicação.

Otimização do banco de dados


Geralmente para a otimização dos bancos de dados nós tomaremos as boas práticas e as metodologias de concepção de bancos de dados que permitem criar esquemas de bancos de dados com bom desempenho e normalizados. Mas, para tanto é preciso:
  • Compreender o que é lento ao nível dos bancos de dados
  • Fazer a boa escolha de metodologia e fazer as boas escolhas conceituais.
  • Utilizar os índices
  • Utilizar OPTIMIZE TABLE

O que deixa lento o banco de dados


Um certo número de fatores são geralmente a causa da lentidão dos bancos de dados. Citamos abaixo os mais freqüentes:
  • Poucos índices: A primeira causa de lentidão é a utilização de tabelas sem índices ou sem índices nas colunas que concernem as buscas. Isto quer dizer que todas as tabelas devem ter índices, mas que é preciso também estudar muito bem as necessidades em indexação.
  • Utilização excessiva de índices: para otimizar as consultas e as buscas, os índices são a solução, no entanto, o aumento do número de índices influencia os desempenhos das atualizações, pois no momento da atualização de uma tabela as operações de adição, modificação e supressão se repercutem geralmente nos índices.
  • Utilização privilégio das tabelas e das colunas das tabelas: cada acesso MySQL deve verificar os direitos nas tabelas e nas colunas o que provoca uma diminuição considerável nos desempenhos.
  • Não fazer as boas escolhas na concepção do banco dados.

Modelismo do banco de dados


Utilizar as boas práticas de modelismo e de concepção de banco de dados bem como a escolha da metodologia apropriada permite criar um banco de dados com bom desempenho.

Certo número de considerações devem ser observadas.
  • Escolha apropriada dos tipos de campos: sempre tentar escolher as variáveis as mais adaptadas à necessidade (exemplo para estocagem de um número que não ultrapassa os 10 números é mais acertado utilizar um tipo TINYINT). Os campos devem ser pequenos, para que um número maior de colunas possam ser carregadas em memória.
  • Utilização dos campos com comprimento fixo: a utilização dos comprimentos predeterminados permite otimizar o acesso às colunas, pois suas posições são predefinidas. Isto implica diminuir a utilização de VARCHAR, de TEXT e de BLOB (para TEXT e BLOB, é preconizado de quebrar a normalização do esquema do banco de dados e de salvar estes campos nas outras tabelas).
  • Aumentar a utilização das premissas NOT NULL quando for possível para otimizar o espaço de estocagem.
  • Escolher o bom tipo para as tabelas MySQL permite ter tabelas de tipos diferentes num mesmo esquema.
  • Fazer uma boa indexação das tabelas.

Utilizar os índices


Um índice é uma tabela de busca que permite encontrar rapidamente as linhas de uma tabela. O índice ajuda a determinar a posição do registro do pedido em uma tabela.
Se a tabela não possui um índice, no momento da busca todos os registros serão pesquisados.
Os índices do MySQL são armazenadas como b-tree (arvores binárias), o que representa uma estrutura de dados fácil e rápida de percorrer.
O índice pode incluir uma coluna única ou múltipla, o índice será chamado durante uma busca em colunas indexadas.
No MySQL, a indexação é automática em tabelas com campos com as premissas, PRIMARY, KEY, UNIQUE.
A idéia básica aqui é que se busca é freqüente, e que inclui uma ou mais colunas, é preciso criar o índice correspondente para otimizar o tempo de resposta através do comando CREATE INDEX.

Utilização do comando OPTIMIZE TABLE


Equivalente à desfragmentação do disco, o comando OPTIMIZE TABLE permite desfragmentar a tabela..

Otimização dos pedidos


MySQL permite analisar os pedidos e conhecer o tempo e o plano de execução. Estas informações permitem compreender o que torna os pedidos lentos e poder desta forma otimizar a execução

Detectar os pedidos lentos


Para detectar os pedidos lentos é possível:
  • ou observar a lentidão no momento da execução e dos tempos de resposta que se mostram anormais.
  • ou fazer benchmark: testar as aplicações para ver quais os componentes mais lentos.
  • ou verificar Slow query log: é possível de ativar esta opção no MySQL configurando a variável --log-slow-queries


Assim que os pedidos lentos forem detectados, lançar o comando EXPLAIN permite compreender a execução e assim conhecer ou intervir para otimizar.

Artigo original publicado por Wjaouadi

Veja também

Publicado por ninha25.
Este documento, intitulado 'My SQL - Otimização', está disponível sob a licença Creative Commons. Você pode copiar e/ou modificar o conteúdo desta página com base nas condições estipuladas pela licença. Não se esqueça de creditar o CCM (br.ccm.net) ao utilizar este artigo.