Início
Steam![[Tutorial] Otimização do MySQL [Índices, partições e engines] Empty](https://2img.net/i/empty.gif)
Otimização do 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 fórum 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ê usa LIMIT) e aceita ou nega os registros que correspondem com a condição da consulta.
Já com o uso de índices o MySQL irá subdividir os registros em blocos com um número menor de registros, ao ser controlado a consulta o MySQL irá perguntar ao índice em que bloco pode estar os registros que correspondem a condição da consulta e assim ele irá apenas verificar os registros daquele bloco e ignore os outros.
2.1 - Criando índices
Uma sintaxe para criar índices é muito simples:
- Código:
CREATE INDEX nome_do_indice em 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ê usa constantemente como condição em suas consultas!
Para exemplificar, vou 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:
Código:Para exemplificar, vou 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:
- Código:
SELECT SQL_NO_CACHE * FROM `testeb` WHERE `num2`=2000
E o resultado obtido foi:
Agora crieei um ndice usando a sintaxe:
Código:Agora crieei um ndice usando a sintaxe:
- Código:
CREATE INDEX numero no 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 número maior de registros a diferença pode aumentar incrivelmente. Vale também dizer que no nosso exemplo foram usados números aleatórios nos registros, então não existia um padrão entre os registros e por isso se seus registros tinham alguma semelhança ou lógica, a diferença na velocidade também ira aumentar.
Podemos ver no phpMyAdmin que o índice criado entre o número de registros a serem verificados de 159.191 para 19.898, vejam na imagem abaixo (Índice "número", vejam na quantidade): 2.2 - Criando índices usando o número de caracteres
indicados
Também podemos criar índices indicando o número de caracteres a serem usados pelo índice, assim podemos por exemplo, indicar para o índice usar como base os 4 primeiros dígitos de um campo. Caso o número fosse 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 facilitaria a divisão em blocos.
A sintaxe a ser usada em nosso exemplo seria:
Código:Podemos ver claramente que com o índice minha consulta foi executada 2x mais rápida e caso seu banco de dados tenha um número maior de registros a diferença pode aumentar incrivelmente. Vale também dizer que no nosso exemplo foram usados números aleatórios nos registros, então não existia um padrão entre os registros e por isso se seus registros tinham alguma semelhança ou lógica, a diferença na velocidade também ira aumentar.
Podemos ver no phpMyAdmin que o índice criado entre o número de registros a serem verificados de 159.191 para 19.898, vejam na imagem abaixo (Índice "número", vejam na quantidade): 2.2 - Criando índices usando o número de caracteres
indicados
Também podemos criar índices indicando o número de caracteres a serem usados pelo índice, assim podemos por exemplo, indicar para o índice usar como base os 4 primeiros dígitos de um campo. Caso o número fosse 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 facilitaria a divisão em blocos.
A sintaxe a ser usada em nosso exemplo seria:
- Código:
CREATE INDEX numero em 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, podemos 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 començ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 a melhorar a velocidade de sua consulta.
No caso do índice UNIQUE, a consulta vai saber que ela só precisa encontrar 1 registro com o valor indicado (afinal se o valor é o único, só pode existir 1) e assim evitar 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 :copyright:: NГO CRIE MUITOS НNDICES!
Um exemplo prático 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 ignore o ndice e ver folha a folha atrбs do numero que vocк quer achar).
O indicado é que se chora 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 várias partes para melhor distribuir os registros conteúdos nela. Existem vários tipos de particionamento que serão exibidos nos tópicos a seguir.
3.2 - Quando participar?
Em caso de um servidor de SA-MP por exemplo, podemos 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 començ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 a melhorar a velocidade de sua consulta.
No caso do índice UNIQUE, a consulta vai saber que ela só precisa encontrar 1 registro com o valor indicado (afinal se o valor é o único, só pode existir 1) e assim evitar 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 :copyright:: NГO CRIE MUITOS НNDICES!
Um exemplo prático 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 ignore o ndice e ver folha a folha atrбs do numero que vocк quer achar).
O indicado é que se chora 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 várias partes para melhor distribuir os registros conteúdos nela. Existem vários tipos de particionamento que serão exibidos nos tópicos a seguir.
3.2 - Quando participar?
- Muitos índices criados
- Número 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 servirão para indicar o padrão usado para compartilhar 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á 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:
Código: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 servirão para indicar o padrão usado para compartilhar 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á 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:
- Código:
[size=14][size=13]CREATE TABLE testando (
nome VARCHAR(30),
id INT NÃO NULO
)
PARTIÇÃO POR INTERVALO (id) (
PARTIÇÃO p0 VALORES MENOS QUE (20),
PARTIÇÃO p1 VALORES MENOS QUE (40),
PARTIÇÃO p2 VALORES MENOS QUE (50),
PARTIÇÃO p3 VALORES MENOS QUE (70),
PARTIÇÃO p4 VALORES MENOS QUE MAXVALUE
);[/size][/size]
[center][/center]
Vamos entender parte a parte agora:
Código:- Código:
PARTIÇÃO POR INTERVALO (id) (
Aqui é indicado que os valores do campo ID serгo usados como chave de particionamento
Código:- Código:
PARTIÇÃO p0 VALORES MENOS QUE (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.
Código:- Código:
PARTIÇÃO p4 VALORES MENOS QUE MAXVALUE
Essa é a última partição que criamos e usamos MAXVALUE pois aqui ficara todos os registros que não correspondem às 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 se dividir as partições indicando listas com os valores que serão aceitos na partição.
Um exemplo de sintaxe para criação de uma tabela particionada usando LIST seria:
Código:3.4.3 - Por LIST
Criando uma partição usando LIST, você pode indicar de que forma ir se dividir as partições indicando listas com os valores que serão aceitos na partição.
Um exemplo de sintaxe para criação de uma tabela particionada usando LIST seria:
- Código:
CREATE TABLE testando (
nome VARCHAR(30),
eu não fiz
)
PARTIÇÃO POR LISTA(store_id) (
PARTIÇÃO p0 VALORES EM (1,2,9),
PARTIÇÃO p1 VALORES EM (3,10,5),
PARTIÇÃO p2 VALORES EM (4,11,6),
PARTIÇÃO p3 VALORES EM (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". É claro que indicamos a lista de valores que serão aceitos na participação.
3.4.4 - Por HASH
No particionamento por HASH, você simplesmente indica o campo que será usado como chave de particionamento e o número 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).
Código:3.4.4 - Por HASH
No particionamento por HASH, você simplesmente indica o campo que será usado como chave de particionamento e o número 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).
- Código:
[size=14][size=13]CREATE TABLE testando (
nome VARCHAR(30),
datateste DATE NOT NULL DEFAULT '1970-01-01',
eu não fiz
)
PARTIÇÃO POR HASH( MONTH(datateste) )
PARTIÇÕES 12;[/size][/size]
[center][/center]
Agora explicando:
Código:- Código:
PARTIÇÃO POR HASH( MONTH(datateste) )
Nesse caso, usamos os mêses contidos nos valores do campo datateste como chave de particionamento.
Código:- Código:
PARTIÇÕES 12;
Aqui indicamos o número de festas a serem criadas, nesse caso vai ficar 1 festa para cada mês do ano (12 meses portanto 12 festas).
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:
Código: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:
- Código:
[size=14][size=13]CREATE TABLE testando (
testestr CHAR(32) PRIMARY KEY
)
PARTIÇÃO POR CHAVE(testestr)
PARTIÇÕES 7;[/size][/size]
[center][/center]
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 irá 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 sabe MySQL que ao criar uma tabela você pode escolher a engine de armazenamento para a a mesma e cada motor oferece uma vantagem/desvantagem.
4.2 - ARQUIVO
A engine ARCHIVE de armazenamento é muito pouco usada, porem se você tem alguma tabela com valores constantes e que você sabe que não vai precisar dar nenhuma atualização (como por exemplo uma tabela com os carros a serem gerados), então essa engine é a indicada . Entгo nгo esqueзa: using this engine vocк nгo pode editar os registros, por й apenas indicado para casos de valores constantes.
Qual vantagem de usar esse motor?
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 aumentou 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 me equivocar em algum momento).
5.2 - Agradecimentos
Como sempre agradecer 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
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 irá 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 sabe MySQL que ao criar uma tabela você pode escolher a engine de armazenamento para a a mesma e cada motor oferece uma vantagem/desvantagem.
4.2 - ARQUIVO
A engine ARCHIVE de armazenamento é muito pouco usada, porem se você tem alguma tabela com valores constantes e que você sabe que não vai precisar dar nenhuma atualização (como por exemplo uma tabela com os carros a serem gerados), então essa engine é a indicada . Entгo nгo esqueзa: using this engine vocк nгo pode editar os registros, por й apenas indicado para casos de valores constantes.
Qual vantagem de usar esse motor?
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 aumentou 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 me equivocar em algum momento).
5.2 - Agradecimentos
Como sempre agradecer 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
Creditos: Kreison
![[Tutorial] Otimização do MySQL [Índices, partições e engines] D07Xwqb](https://i.imgur.com/d07Xwqb.gif)
![[Tutorial] Otimização do MySQL [Índices, partições e engines] Yjab9HN](https://i.imgur.com/yjab9HN.png)
