Otimizações para MySQL - parte 2

Otimizando consultas

Esse é o mais batido de todos… por isso mesmo estou tentando inovar, não vou dizer pra você indexar suas tabelas… se você ainda não faz isso leia este post sobre indices e esse sobre operações simples (se você não trata indices provavelmente vai precisar do segundo também… rsrs)

Conexões persistentes

Primeiro fato: Muitas regras de acesso, muitos usuários no mysql e muitos bancos (ou bancos com muitas tabelas), principalmente se houver usuários com permissões diferentes em tabelas de um mesmo DB vão afetar a performance!
Segundo o próprio manual disponibilizado no site da MySQLAB aconexão persistente realmente poupa tempo!

Estimando o tempo de execusão

Primeiro, para ter um parametro correto você deve estimar o tempo “padrão” do seu server:

mysql>; SELECT BENCHMARK(1000000,1+1);

+------------------------+

| BENCHMARK(1000000,1+1) |

+------------------------+

|                      0 |

+------------------------+

1 row in set (0.06 sec)

a função benchmark é extremamente útil, ela executa uma query n vezes.
Outro exemplo:

SELECT benchmark( 1000000, (SELECT count( artigo ) FROM dbfreemag ));

Dessa forma é possível estimar o tempo que sua aplicação gasta com o BD, e saber se não é aí que deve ser atacado o problema de lentidão.

VÁRIOS INSERTS e REPLACES

Prefira usar uma unica instrução com multiplos valores, traduzindo utilize

 INSERT in
table values(...),(...),(...)

ao invés de três inserts diferentes.Sempre prefira fazer várias operações importando-as de arquivos através do comando LOAD DATA INFILE, ele é cerca de 20 vezes mais rápido do que usar vários inserts.Você também pode ganhar MUITO tempo se durante o insert parar o uso dos indices dessa tabela (isso significa no mínimo uma pesquisa a menos para cada registro inserido, visto que o registro não será levado em conta) isso pode ser feito com

ALTER TABLE nome_tbl DISABLE KEYS

e log em seguida

ALTER TABLE nome_tbl ENABLE KEYS

.
O LOAD DATA INFILE é extremamente interessante, aprenda mais sobre ele.

Em tabelas transacionais (TST) você pode (e deve) usar begin … instruções …commit.Isso fará com que o mysql descarregue tudo de uma única vez.Caso você tenha de fazer inserts muito grandes prefira faze-los em horários de menor requisição para que o tempo de espera das outras requisições não seja muito alto.
Em tabelas não transacionais (NSTS) você pode usar o lock tables … unlock tables, isso terá o mesmo efeito do begin … commit
O lock trava a tabela, o que significa que outras requisições terão de esperar, o que aumenta o tempo de espera de outras requisições, mesmo assim, o ganho de tempo numa tabela travada gira em torno dos 40%!Claro que você deve usar isso com cuidado, afinal, dificilmente é aceitável parar milhares de requisições, por maior que seja a atualização de registros…

Exemplos:

LOCK TABLES a WRITE;

INSERT INTO a VALUES (1,23),(2,34),(4,33);
INSERT INTO a VALUES (8,26),(6,29);

UNLOCK TABLES;

No caso do lock … unlock você pode fazer uso dele a cada mil registros para não aumentar muito o tempo de espera das outras transações.Em geral o uso das travas (lock) pode ser utilizado com ganho de performance para 5 instruções ou mais.

Caso você tenha clientes que não podem esperar que as inserções se concluam para realizar buscas, faça uso do INSERT DELAYED, isso irá alocar o insert em buffer e as inserções serão gerenciadas para irem descarregando conforme o uso da tabela for diminuindo.Não confunda o INSERT DELAYED com o INSERT LOW_PRIORITY no low_priority a execução será atrasada até que NINGUÉM esteja lendo a tabela… o que num sistema muito utilizado pode significar esperar para sempe, isso não acontece no INSERT DELAYED.
Note que INSERT DELAYED é mais lento que um INSERT normal se a tabela não estiver em uso. Também há uma sobrecarga adicional para o servidor tratar uma thread separada para cada tabela na qual você utiliza INSERT DELAYED. Isto significa que você só deve usar INSERT DELAYED quando você estiver certo de que necessita dele!
Outra coisa, INSERT DELAYED só funciona em tabelas ISAM e MyISAM, como tabelas MyISAM suportam SELECT e INSERT concorrentes, se não houver blocos livres no meio do arquivo de dados, você raramente precisará utilizar INSERT DELAYED com MyISAM.

DELETES / UPDATES

Ao deletar todos os dados de uma tabela use TRUNCATE TABLE (TRUNCATE TABLE nome_tabela).
No uso de UPDATES você também deve fazer uso das instruções dentro de blocos begin … commit.
Uma coisa importante a se considerar sobre os deletes é que eles vão “zoar” seus indices.Isso porque é necessário um tempo razoavelmente grande para atualizar todos os indices de uma tabela.Isso vai consumir tempo principalmente em tabelas do tipo MyISAM, onde a função de ler e gravar na tabela ao mesmo tempo pode ficar comprometida (isso é uma das vantagens do MyISAM, que vou abordar num próximo POST).Para evitar que isso aconteça é importante que depois de uma deleção muito grande ou de tempos em tempos para tabelas que são muito atualizadas se execute o OPTIMIZE TABLE.

É importante resaltar que OPTIMIZE TABLE só funcionam para MyISAM e InnoDB.Basicamente ao rodar o OPTIMIZE você vai estar alocando o espaço de forma que não fiquem espaços inutilizados entre os blocos de dados, reordenando os indices e reparando a tabela (no caso do MyISAM) caso hajam registros deletados (Isso não quer dizer recuperação dos dados, e sim da estrutura da tabela).

Outras Dicas

Você pode alterar a ordenação da tabela para a ordem que você mais utiliza. Ex.: ALTER TABLE … ORDER BY expr1,expr2… se você na maioria das vezes recupera registros na ordem expr1,expr2…

Se você usa vários campos em uma ou mais tabelas para calcular alguma variável do seu sistema é interessante adicionar uma tabela (ou um campo) para fazer isso.Um simples

UPDATE table set count=count+1

é extremamente mais rápido do que um

SELECT count(*) from Artigos where Artigos.category = X 

Isso porque você vai evitar muitos processos de gerenciamento de tabela

O uso de valores padrão acelera o uso.Sempre que possível defina o padrão na criação da tabela e só altere o valor daquele campo na inserção quando for extritamente necessário,isso vai te fazer ganhar algum tempo.

Normalmente a normalização das tabelas até sua 3ª forma normal é o ideal, mas só geralmente.Não tenha medo de duplicar dados para ganhar tempo!

Para consultas importantes use SELECT HIGH_PRIORITY … Isso fará com que a consulta “fure” a fila.
Do mesmo modo, sempre que possível utilize INSERT DELAYED … Isso fará com que várias inserções fiquem em cache e sejam adescarregadas ao mesmo tempo.Economizando MUITO tempo.(Só faça isso quando não precisar verificar imediatamente se o dado foi inserido ou não).

Na maioria dos servidores é mais rápido trabalhar com arquivos de imagem e apenas fazer referencia deles no banco de dados do que jogar as imagens para dentro do BD

Use nomes de colunas com até 18 caracteres, além de poupar tempo de processamento vai garantir que você possa portar facilmente seu banco de dados para qualquer servidor/qualquer BD.Se você tem colunas identicas em tabelas diferentes, declare-as com mesmo nome e mesmo tipo.

Saiba que um a clausula IN (valores) é extremamente otimizada no mysql.Se você já tem os valores para a clausula IN, não perca tempo fazendo subselects ou Joins!

Join É mais rápido que subselect, aprenda a usar JOIN!!

Se for necessário use tabelas HEAP, elas usam indices HASH e ficam apenas na memória, o que as torna MUITO rápidas, no entanto só permite uso de colunas de tamanho fixo!(Aprenda mais sobre HEAP)

Obs.Não use HEAP para dados vitais!!

Por fim, gere tabelas compactadas das tabelas que você não utiliza mais… Ou transfira aquelas tabelas que acumulam muitos dados e não requisitarão dados antigos para uma tabela compactada.Você pode compactar tabelas através do MYISAMPACK

Por enquanto isso é tudo pessoal.
Nas próximas tentarei falar de otimizações específicas para cada tipo de tabela e de otimizações que podem ser feitas diretamente no server, com opções de inicialização, configuração e compilação do MySQL!
Até lá!