0 Otimização MySQL

Otimização MySQL

Requisitos para entender esse tutorial

• Conhecimento básico~médio em MySQL

1 - Introdução
Basicamente nesse tutorial vamos aprender algumas dicas úteis para otimizar o uso do MySQL, algumas das dicas são bem conhecidas, porem são pouco usadas pela maioria da galera do forum SA-MP (alias, poucas pessoas usam MySQL em seus gamemodes).
Vale ressaltar que não ira ser apresentado nenhum código PAWN nesse tutorial.


2 - Índices
Os índices do MySQL são bem fáceis de se criar e podem fazer suas consultas rodarem em uma velocidade extremamente mais rápida.
Sem o uso de índices o MySQL faz um table scan onde verifica todos os registros contidos na tabela (exceto que você use LIMIT) e aceita ou nega os registros que correspondem com a condição da consulta.
Já com o uso de índices o MySQL ira subdividir os registros em blocos com um numero menor de registros, ao efetuar a consulta o MySQL ira perguntar ao índice em que bloco podem estar os registros que correspondem a condição da consulta e assim ele ira apenas verificar os registros daquele bloco e ignorar os outros.

2.1 - Criando índices
A sintaxe para criar índices é muito simples:

Code:

CREATE INDEX nome_do_indice on nome_da_tabela(nome_do_campo)

É importante ressaltar que os índices não devem ser criados para qualquer campo, você deve usar apenas campos que você constantemente usa como condição em suas consultas!

Para exemplificar, irei usar uma tabela com 159.191 registros (preenchidos com números randômicos), 5 campos e apenas um índice primário. Como a estrutura que você vê na imagem abaixo:


Executei a seguinte consulta:

Code:

SELECT SQL_NO_CACHE * FROM `testeb` WHERE `num2`=2000

E o resultado obtido foi:


Agora criarei um índice usando a sintaxe:

Code:

CREATE INDEX numero on testeb(num2)

Repetirei a mesma consulta feita anteriormente e o resultado obtido foi:


Podemos ver claramente que com o índice minha consulta foi executada 2x mais rápida e caso seu banco de dados tenha um numero maior de registros a diferença pode aumentar incrivelmente. Vale também dizer que no nosso exemplo foram usados números randômicos nos registros, então não existia um padrão entre os registros e por isso se seus registros tiverem alguma semelhança ou lógica, a diferença na velocidade também ira aumentar.

Podemos ver no phpMyAdmin que o índice criado reduziu o numero de registros a serem verificados de 159.191 para 19.898, vejam na imagem abaixo (Índice "numero", vejam na quantidade):


2.2 - Criando índices usando numero de caracteres indicado
Também podemos criar índices indicando o numero de caracteres a ser usado pelo índice, assim podemos por exemplo, indicar para o índice usar como base os 4 primeiros dígitos de um campo. Caso fosse o numero de um telefone por exemplo, os 4 primeiros dígitos (o que seria o prefixo do telefone) provavelmente iriam se repetir constantemente e assim o índice acharia um padrão facilitando a divisão em blocos.
A sintaxe a ser usada no nosso exemplo seria:

Code:

CREATE INDEX numero on testeb(num3(4))

4 no caso seria o indicador para o índice usar apenas os 4 primeiros dígitos do campo.

Em caso de um servidor de SA-MP por exemplo, poderíamos usar a primeira letra de um nome como base, pois a primeira letra de um nick com certeza se repete e assim se dividiria em blocos com nomes que se começassem com a mesma letra.

2.3 - Índices PRIMARY e UNIQUE
Esses índices apesar de não dividirem os registros em blocos, também podem ajudar melhorar a velocidade de sua consulta.
No caso do índice UNIQUE, a consulta ira saber que ela só precisa encontrar 1 registro com o valor indicado (afinal se o valor é o único, só pode existir 1) e assim evitando de vasculhar por toda a tabela caso a consulta ache o registro desejado.
O índice PRIMARY é bem semelhante ao UNIQUE, geralmente é usado juntamente com AUTO_INCREMENT para criar um campo ID. Assim como UNIQUE só pode existir 1 registro igual a ele, então ao encontrar o registro desejado, ele encerra a consulta pois sabe que só existe aquele registro para a condição indicada.

2.3 - AVISOS
Como eu já citei anteriormente, apenas crie índices para campos que você usa constantemente como condição (WHERE) em consultas. Afinal você não precisa de índices para campos que você não consulta por exemplo.

E outra coisa muito importante é: NÃO CRIE MUITOS ÍNDICES!
Um exemplo pratico para exemplificar o porque de não se criar muitos índices é: Você tem uma agenda telefônica, nela tem um índice no inicio que você usa para achar o numero desejado mais rápido, porém não faz sentido que o índice por exemplo seja maior que a lista telefônica em si (Se não seria mais fácil ignorar o índice e ver folha a folha atrás do numero que você quer achar).
O indicado é que se crie de 2 a 3 índices, isso deve ser o suficiente para otimizar suas consultas sem ter nenhuma dor de cabeça. Se você acha que precisa de mais índices, talvez o mais indicado seja particionar sua tabela e é isso que veremos na próxima parte desse tutorial.


3 - Partições
3.1 - O que é particionar uma tabela?
Particionar uma tabela é basicamente subdividir a mesma em varias partes para melhor distribuir os registros contidos nela. Existem vários tipos de particionamento que serão mostrados nos tópicos a seguir.

3.2 - Quando particionar?

• Muitos índices criados
• Numero de registros muito grande

3.3 - Diferença nas queries de consulta
As queries de consultas não iram mudar devido ao particionamento, é o MySQL que ira fazer todo o trabalho de saber em qual partição está determinado registro e ir lá encontra-lo.

3.4 - Como particionar
3.4.1 - Chaves de particionamento
Chaves de particionamento são os campos que irão ser usados como base para particionar a tabela. Dos registros desse campo serão tirados os valores que iram servir para indicar o padrão usado para particionar a tabela.
AVISO: Os valores da chave de particionamento PRECISAM ser valores INT.

3.4.2 - Por RANGE
Criando uma partição usando RANGE, você pode indicar de que forma irão se dividir as partições indicando por exemplo o RANGE de ids para cada partição.
Um exemplo de sintaxe para criação de uma tabela particionada usando RANGE seria:

Code:

CREATE TABLE testando (
    nome VARCHAR(30),
    id INT NOT NULL
)
PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN (20),
    PARTITION p1 VALUES LESS THAN (40),
    PARTITION p2 VALUES LESS THAN (50),
    PARTITION p3 VALUES LESS THAN (70),
    PARTITION p4 VALUES LESS THAN MAXVALUE

);

Vamos entender parte a parte agora:

Code:

PARTITION BY RANGE (id) (

Aqui é indicado que o valores do campo ID serão usados como chave de particionamento

Code:

PARTITION p0 VALUES LESS THAN (20)

Aqui criamos a primeira partição, sendo p0 o nome da partição e "LESS THAN (20)" indicando que nessa partição apenas são aceitos valores menores que 20.

Code:

PARTITION p4 VALUES LESS THAN MAXVALUE

Essa é a ultima partição que criamos e usamos MAXVALUE pois aqui ficara todos os registros que não corresponderem as condições das outras partições criadas.

3.4.3 - Por LIST
Criando uma partição usando LIST, você pode indicar de que forma irão se dividir as partições indicando listas com os valores que serão aceitados na partição.
Um exemplo de sintaxe para criação de uma tabela particionada usando LIST seria:

Code:

CREATE TABLE testando (
    nome VARCHAR(30),
    id INT
)
PARTITION BY LIST(store_id) (
    PARTITION p0 VALUES IN (1,2,9),
    PARTITION p1 VALUES IN (3,10,5),
    PARTITION p2 VALUES IN (4,11,6),
    PARTITION p3 VALUES IN (99,60,30)
);

Não muda muito em relação ao particionamento anterior, mas agora usamos "LIST" ao em vez de "RANGE" e "VALUES IN" ao em vez de "LESS THAN". E é claro indicamos a lista de valores que serão aceitos na partição.

3.4.4 - Por HASH
No particionamento por HASH, você simplesmente indica o campo que será usado como chave de particionamento e o numero de partições. O resto do trabalho o MySQL ira fazer para você (consequentemente você não ira ter controle do local ou forma que os registros serão distribuídos).

Code:

CREATE TABLE testando (
    nome VARCHAR(30),
    datateste DATE NOT NULL DEFAULT '1970-01-01',
    id INT
)
PARTITION BY HASH( MONTH(datateste) )
PARTITIONS 12;

Agora explicando:

Code:

PARTITION BY HASH( MONTH(datateste) )

Nesse caso usamos os mêses contidos nos valores do campo datateste como chave de particionamento.

Code:

PARTITIONS 12;

Aqui indicamos o numero de partições a serem criadas, nesse caso vai ficar 1 partição para cada mês do ano (12 meses portanto 12 partições).

3.4.5 - Por KEY
O particionamento por KEY é parecido com o HASH, porem aqui temos uma exceção para o aviso dado anteriormente de que só se pode usar campos com valores INT como chaves de partição. Nesse caso você pode sim usar campos com VARCHAR por exemplo como chave de partição.
Veja o exemplo de sintaxe:

Code:

CREATE TABLE testando (
    testestr CHAR(32) PRIMARY KEY
)
PARTITION BY KEY(testestr)
PARTITIONS 7;

3.5 - Considerações
Dada todas essas informações, cabe a você decidir se deve particionar sua tabela e qual é o melhor tipo de particionamento caso deseje particionar.
Vale também ressaltar que nos exemplos de sintaxe para criação de particionamento eu usei CREATE TABLE, porem você também pode usar ALTER TABLE sem nenhum problema para particionar uma tabela que já existe e com registros.


4 - Engines
4.1 - Breve introdução
Não irei me aprofundar muito nesse assunto, pois apenas quero mostrar 1 tipo especifico de engine, porém se você já tem um conhecimento básico de MySQL sabe que ao criar uma tabela você pode escolher a engine de armazenamento para a mesma e cada engine oferece uma vantagem/desvantagem.

4.2 - ARCHIVE
A engine ARCHIVE de armazenamento é muito pouco usada, porem se você tem alguma tabela com valores constantes e que você sabe que não ira precisar dar nenhum update (como por exemplo uma tabela com os carros a serem spawnados), então essa engine é a indicada. Então não esqueça: usando esta engine você não pode editar os registros, por é apenas indicada para casos de valores constantes.

Qual vantagem de usar essa engine?
Ela faz com que a tabela tenha um tamanho significativamente menor do que as de mais, pois usa zlib para comprimir as informações. Assim ocupa menos espaço e também aumentaram a eficiência/rapidez de seus backups.


5 - FIM!
5.1 - Sugestões? Erros?
Esse é o meu primeiro tutorial, então acredito que pode ser melhorado, portanto deixem sugestões de como eu devo melhorar e também me corrijam caso existam erros (não sou nenhum expert em MySQL, então sim, posso ter me equivocado em algum momento).
5.2 - Agradecimentos
Como sempre agradeço a toda galera da iPs e espero que realmente possa ter ajudado pelo menos 1 pessoa com esse tutorial.
5.3 - Links úteis sobre o assunto
5.3.1 - Índices
http://www.linhadecodigo.com.br/arti...consultas.aspx
http://www.profissionaisti.com.br/20...s-em-ate-100x/
http://www.webmaster.pt/mysql-otimiz...ices-5148.html
5.3.2 - Partições
http://robsonpeixoto.com/blog/mysql-partition/
http://www.devmedia.com.br/particion...aplicacao/7299
http://dev.mysql.com/doc/refman/5.5/...mitations.html
5.3.3 - Vídeo
http://youtu.be/OiB2OjL1EoE

__________________