7/24/2019 Otimizao de Consultas
1/34
Otimizao de Consultasno MySql
Crcia Felcio
7/24/2019 Otimizao de Consultas
2/34
Avaliao do tempo gasto na
execuo de uma expresso Para avaliar o impacto da execuo de uma uno
especica no !anco de dados "tilizar a uno !enc#mar$
%normar a quantidade de instru&es que sero executadase a uno'expresso Sintaxe( !enc#mar$)qtde*instrucoes+expressao,
-xemploSELECT BENCHMARK(1000000,1+1);.. expresso 1+1
S-/-C0 1-2C3MA45)6777777+)selectsum)valor*curso, rom cursos,, 8
select !enc#mar$)6777777+)select avg)valor*curso,
rom cursos,,8
7/24/2019 Otimizao de Consultas
3/34
Avaliao do tempo gasto na
execuo de uma expresso A uno BENCHMARK()executa a
expresso a quantidade de vezes
inormada "sada para medir a velocidade em que o
mysql executa a expresso O tempo gasto aparece na rente do campo
duration
O retorno da uno : sempre 7 O!s( 2o usar no servidor de !anco de
dados em produo
7/24/2019 Otimizao de Consultas
4/34
Como o Mysql processa
uma consulta;
7/24/2019 Otimizao de Consultas
5/34
Como o Mysql processa
uma consulta; 3= duas ormas de recuperar os
dados da ta!ela( Fazendo a leitura de cada lin#a data!ela )table scan,+ e retornando
somente as lin#as que satisazem oscrit:rios da cl=usula @3-4-
"tilizando um ndice para encontrarum su!con>unto de lin#as+ eretornando as lin#as que satisazemos crit:rios da cl=usula @3-4-
7/24/2019 Otimizao de Consultas
6/34
Plano de execuo -strat:gia do S1? para recuperar
os dados solicitados pela consulta Otimizador de consultas
B a parte do S1? respons=vel por tomardecis&es relacionadas a execuo daconsulta
7/24/2019 Otimizao de Consultas
7/34
Otimizador de ConsultasSQL Parser
Consulta SQLusurio
Coleo de blocos simples
Otimizador
Melhor Plano de execuo
Bloco SQL simples
Plano cannico
Cria planos alternatios
Planos alternatios
!stima custos
Melhor Plano de execuo
"rans#orma em $l%ebra
7/24/2019 Otimizao de Consultas
8/34
Plano de execuo Considera&es eitas pelo Otimizador de
Consultas
3= alguma outra orma de reescrever aconsulta para que ela possa executar demaneira mais eiciente;
3= indices disponveis para os dadosrequeridos pela consulta;
A utilizao desses ndices ir= mel#orar aperormance; Se sim+ quais os indices quepodem ser utilizados;
Se mltiplas ta!elas sero processadas+ em
qual ordem as ta!elas devem ser processadas;
7/24/2019 Otimizao de Consultas
9/34
Cac#e Cac#e de dados
Permite recuperar dados da memDria sem
necessidade de azer o acesso ao disco Cac#e de indices
Mant:m as inorma&es de indices emmemDria+ evitando o acesso ao disco
Cac#e de consultas Mant:m a especiicao de algumas
consultas e o resultado delas em memDria
7/24/2019 Otimizao de Consultas
10/34
Plano de -xecuoexplainselect aE
rom alunos a+ cursos cG#ere cEcod*cursoHaEcod*cursoand cEnome*cursoHISistemas para %nternetI8
7/24/2019 Otimizao de Consultas
11/34
Plano de -xecuo %d( 6 para a consulta principal+ J para a primeira
su!consulta+ etc Select*type(
SIMPLE( simples+ sem su!consulta ou "nion PRIMARY: select mais externo de uma consulta com
su!consulta SUBQUERY: Primeiro SELECTna su!consulta DEPENDENT SUBQUERY: Primeiro SELECT+ dependente
da su!consulta UNION: Segunda SELECT+ ou select posteriores)quando
#= mais de um union, UNION RESULT( O resultado da operao de unio
Tabl( 2ome da ta!ela ou alias ao qual a consulta se reere
7/24/2019 Otimizao de Consultas
12/34
Plano de -xecuo T!"( ALL( 0odas as colunas da ta!ela da consulta sero lidas
Ocorre quando no #= ndices para ser usado ou quando o mysql decide que avarredura na ta!ela ter= um custo menor que a utilizao de indices
#$%&'( "m indice ser= usado para recuperar os dadosE Os dados so recuperados atrav:s de um valor)constante, ornecido na cl=usula
G#ere+ encontrando as lin#as que KcasamL com esse valor *: "m indice ser= usado para recuperar as lin#as da ta!ela que KcasamL com as
lin#as ornecidas por um select executado anteriormente %ndica que o indice utilizado : uma c#ave prim=ria ou um campo unique+ e que
todas as partes do indice oram utilizadas R: Funciona como o *exceto pelo ato de(
somente parte do indice pode ser usado ou o indice no : um campo unique enem c#ave prim=ria
%-&b*!: U'-l-.a / -%-# "&-&a "a*a &a'-&a.* $ *&l'a$ /a &b#$%&l'a
Ra%( "m indice : utilizado para recuperar um intervalo de valores Ocorre quando #= operadores + N+ ou 1-0@--2 involvidos na consulta
I%2( Faz uma varredura de ndice para encontrar as colunas necess=rias *$*%ll: 3%#-$%a #$/$ $ re exceto pelo ato que a condio tam!:m inclui
uma !usca por valores nulosE -%2 /*( Ocorre quando o MyS/ az a >uno de v=rios ndices para c#egar no
resultado
7/24/2019 Otimizao de Consultas
13/34
Plano de -xecuo Possi!le*$eys( %ndices que sero considerados pelo
Mysql na otimizao da consulta 5ey( %ndice utilizado pelo Mysql ou 2"// caso no
utilize indice 5ey*len( Mostra o taman#o da coluna ndice
Se mais de uma coluna or usada+ o campo poder= indicarsomente parte do indice utilizado
4e( mostra quais colunas ou contantes so usadaspara selecionar registros da ta!ela 4oGs( %ndica o nmero de lin#as que o mysql estima
que sero processadas para o!ter o resultado A estimativa : eita para o passo em questo
7/24/2019 Otimizao de Consultas
14/34
Plano de -xecuo -xtra( Cont:m inorma&es adicionais so!re
o passo de execuo
"sing %ndex( %ndica que a consulta pode serresolvida utilizando somente as inorma&es dosndices
"sing G#ere( "ma cl=usula 4HEREser= utilizada
para deinir as lin#as do resultado %mpossi!le G#ere( %ndica que a condio da
cl=usula 4HEREnunca ser= satiseita "sing >oin !uer( "tiliza as inorma&es o!tidas
pela >uno de ta!elas
7/24/2019 Otimizao de Consultas
15/34
Analisando consultas Campo roGs
%ndica a quantidade de lin#as que
oram lidas para gerar o resultado daconsulta0otal de lin#as( num*roGs6num*roGsJ
-xemplo( total de lin#asH6QHQlin#as
7/24/2019 Otimizao de Consultas
16/34
Analisando consultas Mostrar o plano de consulta para a
consulta K/istar as inorma&es
dos alunos que possuem umcodigo de curso v=lidoL "tilizando su!consulta "tilizando >uno de ta!ela
7/24/2019 Otimizao de Consultas
17/34
Analisando consultasexplain select rom alunos G#ere cod*curso
in)select cod*curso rom cursos, 8
explain select aE rom alunos a +cursos cG#ere aEcod*cursoHcEcod*curso8 ual a quantidade de lin#as acessadas para o
primeiro caso; ual a quantidade de lin#as acessadas para osegundo caso;
ual ter= mel#or perormance;
7/24/2019 Otimizao de Consultas
18/34
Analisando Consultas
7/24/2019 Otimizao de Consultas
19/34
Analisando consultas Podemos notar que(
2a consulta utilizando >uno o
otimizador de consultas acessou primeiroa ta!ela cursos 2o respeitou a ordem das ta!elas na
consulta "tilizou o indice nome*curso para realizar a
consulta Como orar o otimizador a utilizar a ordem
esta!elecida na consulta; "tilizando o termo S04A%30*RO%2
7/24/2019 Otimizao de Consultas
20/34
Analisando Consultas-xplainselect aE rom alunos a S04A%30*RO%2
cursos c G#ere aEcod*cursoHcEcod*curso8 O S04A%30*RO%2 ora o otimizador autilizar a ordem esta!elecida na consulta
?essa orma+ teremos o mesmo resultado
da consulta com su!query Somente 6J lin#as sero acessadas para gerar
o resultado
7/24/2019 Otimizao de Consultas
21/34
Analisando Consultas
Outras possi!ilidades "S- %2?-
%ntrui o MTS/ a utilizar um indice
FO4C- %2?- Fora a utilizao do indice
%2O4- %2?- %ntrui o mysql a ignorar a utilizao do indice
7/24/2019 Otimizao de Consultas
22/34
Analisando consultas -xemplos(explainselect aE rom alunos a + cursos c "se %2?-)nome*curso*idx,G#ere cEcod*cursoHaEcod*curso
and cEnome*cursoHUCiencia da ComputacaoU8
explainselect aErom alunos a FO4C- %2?-)cod*curso,+ cursos cG#ere cEcod*cursoHaEcod*cursoand cEnome*cursoHUCiencia da ComputacaoU8
explainselect aErom alunos a %2O4- %2?-)cod*curso,+ cursos cG#ere cEcod*cursoHaEcod*cursoand cEnome*cursoHUCiencia da ComputacaoU8
7/24/2019 Otimizao de Consultas
23/34
-xerccio Mostrar o plano de execuo da consulta
que lista as inorma&es dos cursos quepossuem alunos "tilizando su!consulta "tilizando >uno de ta!elas+ sendo a ta!ela
alunos a primeira da cl=usula rom 3= dierena entre a quantidade de lin#as
acessadas; Acrescente o S04A%30*RO%2 para aconsulta com >uno
O acr:scimo do S04A%30*RO%2 diminuiu ouaumenta a quantidade de lin#asacessadas;
7/24/2019 Otimizao de Consultas
24/34
%nserindo indices
Considerando que a ta!ela cursos nopossui indice para o campo nome*curso Mostrar o plano de execuo para listar as
inorma&es dos alunos que cursam o curso deSistemas para %nternet+ usando >uno deta!elas
explain
select aErom alunos a+ cursos cG#ere cEcod*cursoHaEcod*cursoand cEnome*cursoHU Sistemas para %nternet
U8
7/24/2019 Otimizao de Consultas
25/34
%nserindo %ndices
2o caso da consulta anterior+ nen#umcampo c#ave oi utilizado
O acr:scimo de um indice no camponome+ diminuir= a quantidade delin#as acessadas;
7/24/2019 Otimizao de Consultas
26/34
%nserindo %ndices
Alter ta!le cursos add indexnome*curso*idx)nome*curso,8
explainselect aErom alunos a+ cursos cG#ere cEcod*cursoHaEcod*cursoand cEnome*cursoHUSistemas para %nternetU8
7/24/2019 Otimizao de Consultas
27/34
%nserindo %ndice
O indice criado pode contersomente parte do das inorma&es
do campo )inicio, -xemplo( Considere que os nomes
dos cursos variam mais nas V
primeiras letras Pode ser criado um indice com essesvalores
Alter ta!le cursos add index
nome curso idx)nome curso)V,,8
7/24/2019 Otimizao de Consultas
28/34
-xerccios Adicione o campo data de nascimento a ta!ela
alunos Atualize os valores das datas de nascimento dos
alunos de maneira a ter Q7W dos alunos com maisde 6X anos de idade e Q7W com menos de 6Xanos
Analise o plano de execuo da consulta Klistar asinorma&es dos alunos com menos de 6X anosL "tilize a uno date*su!)current*date+interval 6X year, uantas lin#as oram acessadas para gerar o resultado;
Acrescente um indice para o campo data de
nascimento
7/24/2019 Otimizao de Consultas
29/34
-xerccios
explainselect rom alunos orce
index)dt*nasc*idx, G#eredate*su!)current*date+interval 6Xyear,Ndt*nasc
7/24/2019 Otimizao de Consultas
30/34
Perormance de %nsert
%nserir dados em uma ta!ela envolveas seguintes atividades(
Conexo -nviar o comando de insero para o
servidor Analisar o comando %nserir o registro %nserir os ndices Fec#ar
7/24/2019 Otimizao de Consultas
31/34
Perormance de %nsert %nsero de mltiplas lin#as%nsert into cursos)nome*curso+sigla*curso,
values )UCiencia da Computacao U+ UCOMPU,+
)UYootecnia U+ UYooU,8Apresenta mel#or perormance que os
comandos separados(%nsert into cursos)nome*curso+sigla*curso,
values )UCiencia da Computacao U+ UCOMPU,8%nsert into cursos)nome*curso+sigla*curso,)UYootecnia U+ UYooU,8
7/24/2019 Otimizao de Consultas
32/34
Perormance de %nsert
/OC5 0A1/-S cursos alunos @4%0-8
%nsert intocursos)nome*curso+sigla*curso,values )U?ecoraoU+ U?-CU,8
%nsert intocursos)nome*curso+sigla*curso,values )IFilosoiaU+ UF%/OU,8
7/24/2019 Otimizao de Consultas
33/34
-xerccio
1anco de dados de estados ecidades
Analisar script de criao do !anco dedados Fazer an=lise do plano de consulta para
a consulta que lista as cidades de
acordo com a "F a qual ela pertence Mostrar o plano de consulta sem a utilizao
de um ndice Com utilizao do indice
"sar as op&es ( Straig#t*>oin+ Force %ndex+% nore %ndex
7/24/2019 Otimizao de Consultas
34/34
-xerccio
Fazer an=lise do plano de consulta para aconsulta que lista as cidades de um estado
de acordo com a letra inicial do nome dacidade Mostrar o plano de consulta sem a utilizao de
um ndice
Com utilizao do indice "sar as op&es ( Straig#t*>oin+ Force %ndex+
%gnore %ndex