[kreison]

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://*********/OiB2OjL1EoE

[PT]

Muito Bom tutorial.

[Gii]

Finalmente algum material nesta board sobre MYSQL.

Um dia pretendo criar um tutorial sobre modelos relacionais entre outros, quem sabe.

Good Work!

[kreison]

Obrigado pelos comentбrios galera, fico feliz que tenham gostado!

[Schocc]

Para o pessoal que nгo conhece o search/******, й uma maravilha de tutorial.

Parabйns por trazer este assunto a board PT-BR.

[Dolby]

Уtimo tutorial Kleyton, parabйns.

[DanDRT]

Quote:

Originally Posted by iCasTiel Finalmente algum material nesta board sobre MYSQL. Um dia pretendo criar um tutorial sobre modelos relacionais entre outros, quem sabe. Good Work!

Na verdade existe um уtimo tutorial do BlueX sobre MySQL tambem na board.

[Gii]

Quote:

Originally Posted by DanDRT Na verdade existe um уtimo tutorial do BlueX sobre MySQL tambem na board.

Estava me referindo desde o tempo que frequento esta BOARD, sem falar que й raro sair um material deste tipo aqui.

[DanDRT]

Quote:

Originally Posted by iCasTiel Estava me referindo desde o tempo que frequento esta BOARD, sem falar que й raro sair um material deste tipo aqui.

Bom isto й verdade e parabens pelo tutorial kreisson

[RebeloX]

Eu fiz dois tutoriais de MySQL um sobre um sistema de casas e o outro a explicar o uso de MySQL e o plugin r7 do BlueG.

Й verdade que poderia fazer mais, atй porque a maioria dos meus contributos para a board foram sempre tutoriais e depois eu deixei de fazer devido ao pouco feedback que havia e quando falo feedback nгo й sу dizer "Bom tutorial!!!" й ler e apontar criticas e erros, duvidas, isso mostra que alguйm estб interessado no assunto. O meu tutorial de MySQL era gigante e quando eu postei 1min depois ouve uma pessoa que postou "Bom tutorial..." e provavelmente nгo leu nada.

Eu sinceramente nгo li este tutorial, mas gostei pela a abordagem do assunto, se tiver paciencia e/ou vontade irei ler, visto que nгo estou mais envolvido no sa-mp.

---

@kreisson

Jб ouviste falar dos Prepared Statement (stmt) , atй ao que eu sei й bastante mais rбpido visto que sгo Querys definidos e depois sгo executados quando necessбrio, lembrando que ocupam espaзo na memуria, sendo que й necessбrio usar free_result para libertar a memуria depois. No query normal nуs usamos "INSERT INTO `table` (`field1`,`field2`) VALUES ('valor','valor')" com stmt substituimos o valor por um ? (ponto de interrogaзгo)

Uma das desvantagens й que nгo й possнvel usar case sensitive, penso que stmt seja usado para consultas grandes, como por exemplo as mensagens de um blog, visto que sгo muitas mensagens e nгo й necessбrio uma mensagem especifica (claro que depois podemos colocar um LIMIT para fazer pбginas)

Tirando isso gostei bastante do tema abordado mas penso que a maioria das pessoas nгo irб ligar para o assunto e nгo entender que atй na linguagem PAWN ( que й simples ) dб para fazer grandes optimizaзхes, alias a maioria daqui й gente que quer um servidor RPG para ganhar $$ ( o que nгo й a realidade porque server nгo dб dinheiro nenhum ), enfim.