MySQL - Processamento e otimização de consultas

Prévia do material em texto

UNIVERSIDADE FEDERAL DE GOIÁS 
INSTITUTO DE INFORMÁTICA 
BANCO DE DADOS 2 
 
 
 
 
Florentino dos Santos Filho 
Julio Sousa 
Gustavo Alves 
Orlando da C. P. Júnior 
 
 
 
MySQL 
Processamento e otimização de consultas 
 
 
 
 
 
 
 
 
 
 
Goiânia 
2019 
Sumário 
 
1. Introdução 3 
2. Visão geral da otimização 3 
3. Otimizando SELECTs e outras consultas 4 
3.1 Estimando o Desempenho de uma Consulta 5 
3.2 Otimização da cláusula WHERE 5 
3.3 Algoritmo Nested-Loop Join 6 
3.4 Otimização de leitura de vários intervalos 7 
3.5 Evitando varredura de tabela completa 8 
4. Indices 9 
4.1 Como o MySQL usa índices 9 
4.2 Otimização da chave primária 11 
4.3 Otimização de chave estrangeira 11 
4.4 Índices de coluna 12 
4.5 Comparação de índices B-Tree e Hash 13 
5. Plano de execução de consulta 14 
5.1 Sintaxe EXPLAIN 14 
6. Controlando o otimizador 16 
6.1 Controlando a avaliação do plano de consulta 16 
6.2 Otimizações comutáveis 18 
6.3 Dicas do otimizador 19 
6.4 Dicas de índice 19 
6.5 O modelo de custo do otimizador 20 
6.5.1 Operação geral do modelo de custo 20 
6.5.2 O banco de dados do modelo de custo 21 
6.5.3 Fazendo alterações no banco de dados do modelo de custo 22 
7. Histograma 22 
8. Conclusão 25 
Bibliografia 25 
 
1. Introdução 
Otimizar o desempenho das consultas é uma atividade extremamente necessária, tão 
fortemente dependente da intuição do desenvolvedor quanto de dados estatísticos rígidos de 
desempenho. Felizmente, bancos de dados como o MySQL possui algumas ferramentas para 
auxiliar o processo. Neste estudo abordaremos o processamento e otimização de consultas 
usando o SGBD relacional MySQL. 
2. Visão geral da otimização 
A parte mais importante para obter um sistema rápido é com certeza o projeto básico. 
Também precisa saber quais tipos de coisas o sistema estará fazendo, e quais são gargalos 
existentes. 
Os gargalos mais comuns são: 
● Pesquisa em disco: É necessário tempo para o disco encontrar uma quantidade de dados. 
Com os discos modernos em 1999, o tempo médio para isto era normalmente menor que 
10ms, portanto em teoria poderíamos fazer 100 buscas por segundo. Este tempo melhora 
moderadamente com discos novos e isso é muito difícil otimizar para uma única tabela. A 
maneira de otimizar isto é colocando os dados em mais de um disco. 
● Leitura de disco/Escrita (I/O): Quando o disco estiver na posição correta precisaremos 
que os dados sejam lidos. Com discos mais modernos em 1999, um disco retorna algo em 
torno de 10-20Mb/s. 
● Ciclos de CPU: Quando os dados na memória principal (ou se eles já estiverem lá) 
precisa processá-los para conseguir o resultado. O fator de limitação mais comum é ter 
pequenas tabelas, comparadas com a memória. Mas, com pequenas tabelas, normalmente 
não tem problemas com velocidade. 
 
● Largura de banda da memória: Quando a CPU precisa de mais dados que podem caber no 
cache da CPU a largura da banda da memória principal se torna um gargalo. Isto é um 
gargalo muito incomum para a maioria dos sistema. 
3. Otimizando ​SELECTs​ e outras consultas 
Quanto mais complexo seu sistema de permissões, maior a sobrecarga. Se não tiver 
nenhuma instrução GRANT realizada, MySQL otimiza a verificação de permissões de alguma 
forma. Dessa forma, se possui um volume muito alto, o tempo pode piorar tentando permitir o 
acesso. Por outro lado, maior verificação de permissões resulta em uma sobrecarga maior. 
Se o problema é com alguma função explícita do MySQL, pode sempre consultar o 
tempo da mesma com o cliente MySQL: 
mysql​> ​SELECT BENCHMARK​(1000000,1+1); 
+---------------------------------------+ 
| BENCHMARK(1000000,1+1) | 
+---------------------------------------+ 
| 0 | 
+---------------------------------------+ 
1 row in set (0.32 sec) 
 
O exemplo acima demonstra que o MySQL pode executar 1.000.000 expressões + em 
0.32 segundos em um Pentium II 400MHz. Todas funções MySQL devem ser bem otimizadas, 
mas existem algumas exceções. 
As consultas, na forma de instruções SELECT, executam todas as operações de pesquisa 
no banco de dados. O ajuste dessas instruções é uma prioridade, seja para obter tempos de 
resposta de menores no carregamento de páginas dinâmicas da Web ou para diminuir horas de 
trabalho para gerar enormes relatórios. 
Além de declarações SELECT, as técnicas de ajuste para consultas também se aplicam a 
construções como CREATE TABLE… AS SELECT, INSERT INTO...SELECT e cláusulas 
WHERE. Essas instruções têm considerações de desempenho adicionais porque combinam 
operações de gravação com as operações de consulta orientada para leitura. 
3.1 Estimando o Desempenho de uma Consulta 
Na maioria dos casos pode estimar a performance contando buscas em disco. Para tabelas 
pequenas, normalmente pode encontrar o registro com 1 pesquisa em disco (uma vez que o 
índice provavelmente está no cache). Para tabelas maiores, pode estimar (usando indices 
B-Tree++) que precisará de: log(row_count) / log(index_ block_length / 3 * 2 / (index_length + 
data_pointer_length)) + 1 buscas em disco para encontrar um registro. 
No MySQL um bloco de índice tem geralmente 1024 bytes e o ponteiro de dados 4 bytes. 
Uma tabela de 500.000 registros com um índice com tamanho de 3 (inteiro médio) lhe da: 
log(500,000)/log(1024/3*2/(3+4)) + 1 = 4 pesquisas. 
Como o índice acima necessita cerca de 500,000 * 7 * 3/2 = 5.2M, (assumindo que os 
buffers de índices são carregados até 2/3, que é o normal) provavelmente terá grande parte dos 
índices em memória e provavelmente precisará somente de 1 ou 2 chamadas para ler dados do 
SO para encontrar o registro. 
Entretanto, para escritas, precisará utilizar 4 requisições para encontrar onde posicionar o 
novo índice e normalmente 2 buscas para atualizar o índice e escrever o registro. Perceba que o 
que foi dito acima não significa que sua aplicação perderá performance por N log N! Como tudo 
é armazenado no cache de seu SO ou do servidor SQL as coisas começarão a ficar um pouco 
mais lentas quando as tabelas começarem a crescer. Quando os dados se tornam muito grandes 
para o cache, as coisas começarão a ficar bem mais lentas até que suas aplicações estejam 
limitadas a buscas em disco (o que aumenta em N log N). 
3.2 Otimização da cláusula WHERE 
O MySQL faz otimizações automaticamente, muitas vezes você pode evitar esse trabalho 
e deixar a consulta de uma forma mais compreensível e sustentável. Algumas das otimizações 
realizadas pelo MySQL: 
● Remoção de parênteses desnecessários: 
 ​((​a ​AND​ b​)​ ​AND​ c ​OR​ ​(((​a ​AND​ b​)​ ​AND​ ​(​c ​AND​ d​)))) 
->​ ​(​a ​AND​ b ​AND​ c​)​ ​OR​ ​(​a ​AND​ b ​AND​ c ​AND​ d​) 
 
● Alteração em constante: 
 ​(​a​<​b ​AND​ b​=​c​)​ ​AND​ a​=​5 
->​ b​>​5​ ​AND​ b​=​c ​AND​ a​=​5 
 
● Remoção de condição: 
 ​(​b​>=​5​ ​AND​ b​=​5​)​ ​OR​ ​(​b​=​6​ ​AND​ ​5​=​5​)​ ​OR​ ​(​b​=​7​ ​AND​ ​5​=​6​) 
->​ b​=​5​ ​OR​ b​=​6 
 
● Expressões constantes usadas por índices são avaliadas apenas uma vez. 
● COUNT(*) em uma única tabela sem WHERE é recuperado diretamente das informações 
MyISAM e MEMORY. Isso também é feito para qualquer expressão NOT NULL 
quando usada com apenas uma tabela. 
● Detecção precoce de expressões constantes inválidas. O MySQL rapidamente detecta que 
algumas instruções SELECT são impossíveise não retorna nenhuma linha. 
● HAVING é mesclado com WHERE se você não usar GROUP BY ou funções agregadas 
(COUNT(), MIN() e assim por diante). 
3.3 Algoritmo Nested-Loop Join 
O MySQL executa junções entre tabelas usando um algoritmo Nested-Loop Join ou 
variações dele. ​Um algoritmo simples Nested-Loop Join (NLJ) lê linhas da primeira tabela em 
um loop, uma de cada vez, passando cada linha para um loop aninhado que processa a próxima 
tabela na junção. Esse processo é repetido quantas vezes houver tabelas a serem unidas. 
Suponha que uma junção entre três tabelas t1, t2e t3 deva ser executada usando os 
seguintes tipos de junção: 
Table Join Type 
t1 range 
t2 ref 
t3 ALL 
 
 
 
 
 
 
 
Se um algoritmo NLJ simples for usado, a junção será processada assim: 
for​ each row ​in​ t1 matching range ​{ 
 ​for​ each row ​in​ t2 matching reference key ​{ 
 ​for​ each row ​in​ t3 ​{ 
 ​if​ row satisfies join conditions​,​ send to client 
 ​} 
 ​} 
} 
3.4 Otimização de leitura de vários intervalos 
A leitura de linhas usando uma varredura de intervalo em um índice secundário pode 
resultar em muitos acessos aleatórios de disco à tabela base quando a tabela é grande e não é 
armazenada no cache do mecanismo de armazenamento. Com a otimização MRR (Disk-Sweep 
Multi-Range Read), o MySQL tenta reduzir o número de acesso aleatório ao disco para 
varreduras de intervalo, primeiro varrendo apenas o índice e coletando as chaves das linhas 
relevantes. Em seguida, as chaves são classificadas e, finalmente, as linhas são recuperadas da 
tabela base usando a ordem da chave primária. A motivação do MRR de varredura de disco é 
reduzir o número de acessos aleatórios ao disco e obter uma varredura mais seqüencial dos dados 
da tabela base. 
A otimização de leitura de várias faixas oferece os seguintes benefícios: 
● O MRR permite que as linhas de dados sejam acessadas sequencialmente e não em ordem 
aleatória, com base nas tuplas do índice. O servidor obtém um conjunto de tuplas de 
índice que satisfazem as condições da consulta, as classifica de acordo com a ordem do 
ID da linha de dados e usa as tuplas classificadas para recuperar as linhas de dados em 
ordem. Isso torna o acesso a dados mais eficiente e mais barato. 
● O MRR permite o processamento em lote de solicitações de acesso principal para 
operações que requerem acesso a linhas de dados por meio de tuplas de índice, como 
varreduras de índices de intervalo e junções equitativas que usam um índice para o 
atributo de junção. O MRR itera sobre uma sequência de intervalos de índice para obter 
tuplas de índice qualificadas. À medida que esses resultados se acumulam, eles são 
usados ​​para acessar as linhas de dados correspondentes. Não é necessário adquirir todas 
as tuplas de índice antes de começar a ler as linhas de dados. 
3.5 Evitando varredura de tabela completa 
A saída de EXPLAIN mostra ALL na coluna type quando o MySQL usa uma varredura 
de tabela completa para resolver uma consulta. Isso geralmente acontece nas seguintes 
condições: 
● A tabela é tão pequena que é mais rápido executar uma varredura de tabela do que se 
preocupar com uma pesquisa de chave. Isso é comum para tabelas com menos de 10 
linhas e um comprimento de linha curto. 
● Não há restrições utilizáveis ​​na cláusula ON ou WHERE para colunas indexadas. 
● Você está comparando colunas indexadas com valores constantes e o MySQL calculou 
(com base na árvore de índices) que as constantes cobrem uma parte muito grande da 
tabela e que uma varredura da tabela seria mais rápida. 
● Você está usando uma chave com baixa cardinalidade (muitas linhas correspondem ao 
valor da chave) por meio de outra coluna. Nesse caso, o MySQL assume que, usando a 
chave, ele provavelmente fará muitas pesquisas de chave e que uma varredura de tabela 
seria mais rápida. 
 
Para tabelas pequenas, uma varredura de tabela geralmente é apropriada e o impacto no 
desempenho é insignificante. Para tabelas grandes, tente as seguintes técnicas para evitar que o 
otimizador escolha incorretamente uma varredura de tabela: 
● Use para atualizar as distribuições de chaves da tabela digitalizada. 
● Use FORCE INDEX a tabela digitalizada para informar ao MySQL que as varreduras de 
tabela são muito caras em comparação ao uso do índice fornecido: 
SELECT​ ​*​ ​FROM​ t1​,​ t2 ​FORCE​ ​INDEX​ ​(​index_for_column​) 
 ​WHERE​ t1​.​col_name​=​t2​.​col_name​; 
 
● Inicie o mysqld com a opção --max-seeks-for-key=1000 ou use SET 
max_seeks_for_key=1000 para dizer ao otimizador para assumir que nenhuma 
verificação de chave causa mais de 1.000 buscas de chave. 
4. Indices 
A melhor maneira de melhorar o desempenho das operações SELECT é criar índices em 
uma ou mais colunas das tabelas usadas em consultas. As entradas de índice agem como 
ponteiros para as linhas da tabela, permitindo que a consulta encontre rapidamente quais linhas 
correspondem a uma condição na cláusula WHERE e recupere os outros valores das colunas para 
essas linhas. Todos os tipos de dados MySQL podem ser indexados. 
Embora possa ser tentador criar índices para todas as colunas passíveis de serem usadas 
em consultas isso não é uma boa prática. Índices desnecessários desperdiçam espaço e tempo do 
MySQL. Os índices também aumentam o custo de inserções, atualizações e exclusões, pois cada 
índice deve ser atualizado em todas essa operações. Deve-se encontrar o equilíbrio certo para 
obter consultas rápidas usando o conjunto ideal de índices. 
4.1 Como o MySQL usa índices 
Os índices são usados ​​para localizar linhas com valores de coluna específicos 
rapidamente. Sem um índice, o MySQL deve começar com a primeira linha e depois ler a tabela 
inteira para encontrar as linhas relevantes. Quanto maior a tabela, mais isso custa em termos de 
processamento. Se a tabela possui um índice para as colunas em questão, o MySQL pode 
determinar rapidamente a posição do registro a ser encontrado no meio do arquivo de dados sem 
precisar examinar todos os registros. Isso é muito mais rápido do que ler todas as linhas 
sequencialmente. 
A maior parte dos índices MySQL (PRIMARY KEY, UNIQUE, INDEX, e FULLTEXT) 
são armazenados em B-Tree. Algumas exceções: índices em tipos de dados espaciais usam 
R-Tree; tabelas MEMORY também suportam índices de hash; InnoDB usa listas invertidas para 
índices FULLTEXT. 
O MySQL usa índices para estas operações: 
● Para encontrar as linhas que correspondem a uma cláusula WHERE rapidamente. 
● Para eliminar linhas. Se houver uma escolha entre vários índices, o MySQL normalmente 
usa o índice que encontra o menor número de linhas (o índice mais seletivo). 
● Se a tabela tiver um índice composto por várias colunas, qualquer prefixo mais à 
esquerda do índice poderá ser usado pelo otimizador para procurar linhas. Por exemplo, 
se você tem um índice de três colunas em (col1, col2, col3), você tem recursos de 
pesquisa indexados em (col1), (col1, col2). 
● Para recuperar linhas de outras tabelas ao executar junções. O MySQL pode usar índices 
em colunas com mais eficiência se eles forem declarados com o mesmo tipo e tamanho. 
Nesse contexto,VARCHAR e CHAR são considerados iguais se forem declarados com o 
mesmo tamanho. Por exemplo, VARCHAR(10) e CHAR(10) são do mesmo tamanho, 
mas VARCHAR(10) e CHAR(15) não são. 
● Para comparações entre colunas de seqüência de caracteres não binárias, ambas as 
colunas devem usar o mesmo conjunto de caracteres. Por exemplo, comparar uma coluna 
utf8 com uma coluna latin1 impede o uso de um índice. 
● A comparação de colunas diferentes (comparar uma coluna de sequência de caracteres 
com uma coluna temporal ou numérica, por exemplo) pode impedir o uso de índices se os 
valores não puderem ser comparados diretamente sem conversão. Para um determinado 
valor, como 1 na coluna numérica, pode comparar igual a qualquer número de valores na 
coluna string como '1', '1', '00001', ou '01.e1'. Isso exclui o uso de quaisquer índices para 
a coluna de string. 
● Para encontrar o valor MIN() ou MAX() para uma coluna indexada específica key_col. 
Isso é otimizado por um pré-processador que verifica se você está usando todas as partes 
principais que ocorreram anteriormente no índice. Nesse caso, o MySQL faz uma 
pesquisa de chave única para cada expressão ou e a substitui por uma constante. Se todas 
as expressões forem substituídas por constantes, a consulta retornará de uma só vez. Por 
exemplo: WHERE key_part_N = constantkey_colMIN()MAX() 
SELECT​ ​MIN​(​key_part2​),​MAX​(​key_part2​) 
 ​FROM​ ​tbl_name​ ​WHERE​ ​key_part1​=​10​; 
 
● Para classificar ou agrupar uma tabela se a classificação ou o agrupamento for feito no 
prefixo mais à esquerda de um índice utilizável. Se todas as partes da chave forem 
seguidas, a chave será lida na ordem inversa. 
● Em alguns casos, uma consulta pode ser otimizada para recuperar valores sem consultar 
as linhas de dados. (Um índice que fornece todos os resultados necessários para uma 
consulta é chamado de índice de cobertura.) Se uma consulta usar de uma tabela apenas 
colunas incluídas em algum índice, os valores selecionados poderão ser recuperados da 
árvore de índices para maior velocidade: 
SELECT​ ​key_part3​ ​FROM​ ​tbl_name 
 ​WHERE​ ​key_part1​=​1 
 
Os índices são menos importantes para consultas em tabelas pequenas ou grandes, nas 
quais as consultas processam a maioria ou todas as linhas. Quando uma consulta precisa acessar 
a maioria das linhas, a leitura sequencial é mais rápida do que o trabalho em um índice. As 
leituras seqüenciais minimizam as buscas de disco, mesmo que nem todas as linhas sejam 
necessárias para a consulta. 
4.2 Otimização da chave primária 
A chave primária de uma tabela representa a coluna ou o conjunto de colunas que você 
usa em suas consultas mais importantes. Possui um índice associado, para desempenho rápido da 
consulta. O desempenho da consulta se beneficia da otimização NOT NULL, porque não pode 
incluir nenhum valor NULL. Com o mecanismo InnoDB de armazenamento, os dados da tabela 
são organizados fisicamente para realizar pesquisas e classificações ultra rápidas, com base na(s) 
coluna(s) da chave primária. 
Se sua tabela for grande e importante, mas não tiver uma coluna óbvia ou conjunto de 
colunas para usar como chave primária, você poderá criar uma coluna separada com valores de 
incremento automático para usar como chave primária. Esses IDs exclusivos podem servir como 
ponteiros para as linhas correspondentes em outras tabelas quando você ingressa em tabelas 
usando chaves estrangeiras. 
4.3 Otimização de chave estrangeira 
Se uma tabela tiver muitas colunas e você consultar muitas combinações diferentes de 
colunas, pode ser eficiente dividir os dados usados ​​com menos frequência em tabelas separadas 
com algumas colunas cada e relacioná-los de volta à tabela principal duplicando o ID numérico 
da tabela principal. Dessa forma, cada tabela pequena pode ter uma chave primária para 
pesquisas rápidas de seus dados e você pode consultar apenas o conjunto de colunas necessárias 
usando uma operação de junção. Dependendo de como os dados são distribuídos, as consultas 
podem executar menos E/S e ocupar menos memória cache, porque as colunas relevantes são 
compactadas no disco. (Para maximizar o desempenho, as consultas tentam ler o menor número 
possível de blocos de dados do disco). 
4.4 Índices de coluna 
O tipo mais comum de índice envolve uma única coluna, armazenando cópias dos valores 
dessa coluna em uma estrutura de dados, permitindo pesquisas rápidas para as linhas com os 
valores correspondentes da coluna. A estrutura de dados de B-Tree permite que o índice 
rapidamente encontrar um valor específico, um conjunto de valores, ou um intervalo de valores, 
correspondentes a operadores, tais como =, >, ≤, BETWEEN, IN, e assim por diante, em uma 
cláusula WHERE. 
O número máximo de índices por tabela e o comprimento máximo do índice são 
definidos por mecanismo de armazenamento. Todos os mecanismos de armazenamento suportam 
pelo menos 16 índices por tabela e um comprimento total de índice de pelo menos 256 bytes. A 
maioria dos mecanismos de armazenamento tem limites mais altos. 
 
Prefixos de índice 
Com sintaxe em uma especificação de índice para uma coluna de cadeia de caracteres, 
você pode criar um índice que use apenas os primeiros caracteres da coluna. A indexação apenas 
de um prefixo de valores de coluna dessa maneira pode tornar o arquivo de índice muito menor. 
Ao indexar uma ou coluna, você deve especificar um tamanho de prefixo para o índice. Por 
exemplo: col_name(N)NBLOBTEXT 
CREATE​ ​TABLE​ test ​(​blob_col ​BLOB​,​ ​INDEX​(​blob_col​(​10​))); 
 
 
 
Índices FULLTEXT 
Índices FULLTEXT são usados ​​para pesquisas de texto completo. Apenas o InnoDB e 
MyISAM podem suportar índices FULLTEXT e apenas para CHAR, VARCHAR, e colunas 
TEXT. A indexação sempre ocorre em toda a coluna e a indexação do prefixo da coluna não é 
suportada. 
 
Índices espaciais 
Você pode criar índices em tipos de dados espaciais. O MyISAM e o InnoDB suportam 
índices R-Tree em tipos espaciais. Outros mecanismos de armazenamento usam B-Tree para 
indexar tipos espaciais (exceto ARCHIVE, que não suporta a indexação de tipos espaciais). 
 
Índices no mecanismo de armazenamento MEMORY 
O mecanismo de armazenamento MEMORY usa índices HASH por padrão, mas também 
suporta índices B-Tree. 
4.5 Comparação de índices B-Tree e Hash 
Compreender as estruturas de dados de B-Tree e de Hash pode ajudar a prever como as 
consultas diferentes são executadas em diferentes mecanismos de armazenamento que usam 
essas estruturas de dados em seus índices, principalmente no mecanismo de armazenamento 
MEMORY que permite escolher índices de B-Tree ou de hash. 
 
Características do Índice B-Tree 
Um índice B-Tree pode ser utilizada para comparações de coluna em expressões que 
utilizam os =, >, >=, <, <=, ou BETWEEN operadores. 
SELECT​ ​*​ ​FROM​ ​tbl_name​ ​WHERE​ ​key_col​ ​LIKE​ ​'Patrick%'​; 
SELECT​ ​*​ ​FROM​ ​tbl_name​ ​WHERE​ ​key_col​ ​LIKE​ ​'Pat%_ck%'​; 
 
Na primeira instrução, apenas as linhas com são consideradas. Na segunda instrução, 
apenas as linhas com são consideradas. 'Patrick' <= key_col < 'Patricl''Pat' <= key_col< 'Paul' 
 
 
As seguintes instruções SELECT não usam índices: 
SELECT​ ​*​ ​FROM​ ​tbl_name​ ​WHERE​ ​key_col​ ​LIKE​ ​'%Patrick%'​; 
SELECT​ ​*​ ​FROM​ ​tbl_name​ ​WHERE​ ​key_col​ ​LIKE​ ​other_col​; 
 
Características do Índice de Hash 
Os índices de hash têm características um pouco diferentes daquelas que acabamos de 
discutir: 
● Eles são usados ​​apenas para comparações de igualdade que usam os operadores =ou ⇔ 
(más são muito rápidos). Eles não são usados ​​para operadores de comparação como os < 
que encontram um intervalo de valores. Os sistemas que dependem desse tipo de pesquisa 
de valor único são conhecidos como "armazenamentos de valores-chave". 
● O otimizador não pode usar um índice de hash para acelerar as operações ORDER BY. 
(Este tipo de índice não pode ser usado para procurar a próxima entrada em ordem.) 
● O MySQL não pode determinar aproximadamente quantas linhas existem entre dois 
valores (isso é usado pelo otimizador de intervalo para decidir qual índice usar). 
● Somente chaves inteiras podem ser usadas para procurar uma linha. (Com um índice 
B-Tree, qualquer prefixo mais à esquerda da chave pode ser usado para encontrar linhas.) 
5. Plano de execução de consulta 
Dependendo dos detalhes de suas tabelas, colunas, índices e das condições de sua 
cláusula WHERE, o otimizador do MySQL considera muitas técnicas para executar com 
eficiência as pesquisas envolvidas em uma consulta SQL. 
Uma consulta em uma tabela muito grande pode ser realizada sem ter que, 
necessariamente, ler todas as linhas. Uma junção envolvendo várias tabelas pode ser executada 
sem comparar todas as combinações de linhas. O conjunto de operações que o otimizador 
escolhe para executar a consulta mais eficiente é chamado de "plano de execução da consulta". 
 5.1 Sintaxe EXPLAIN 
EXPLAIN nome_tabela é um sinônimo para DESCRIBE nome_tabela ou SHOW 
COLUMNS FROM nome_tabela. Na prática, o DESCRIBE é utilizado com mais frequência 
para obter informações sobre a tabela enquanto o EXPLAIN costuma ser executado para se obter 
o plano de execução da consulta. 
EXPLAIN​ ​nome_tabela 
ou ​EXPLAIN SELECT​ ​opções_select 
 
Quando uma instrução SELECT for precedida da palavra chave EXPLAIN, o MySQL 
explicará como ele deve processar a SELECT, fornecendo informação sobre como as tabelas 
estão sendo unidas e em qual ordem. Com a ajuda de EXPLAIN, é possível ver quando devem 
ser adicionados índices a tabelas para obter uma SELECT mais rápida que utiliza índices para 
encontrar os registros. 
Deve executar frequentemente ANALYZE TABLE para atualizar estatísticas de tabela 
tais como a cardinalidade das chaves que podem afetar a escolha que o otimizador faz. Pode ver 
se o otimizador une as tabelas em uma melhor ordem. Para forçar o otimizador a utilizar uma 
ordem específica de join para uma instrução SELECT, adicione uma cláusula 
STRAIGHT_JOIN. 
Para ligações mais complexas, EXPLAIN retorna uma linha de informação para cada 
tabela utilizada na instrução SELECT. As tabelas são listadas na ordem que seriam lidas. O 
MySQL soluciona todas as joins utilizando um método multi-join de varredura simples. Isto 
significa que o MySQL lê uma linha da primeira tabela, depois encontra uma linha que combina 
na segunda tabela, depois na terceira tabela e continua. Quando todas tabelas são processadas, 
ele exibe as colunas selecionadas e recua através da lista de tabelas até uma tabela na qual 
existem registros coincidentes for encontrada. O próximo registro é lido desta tabela e o processo 
continua com a próxima tabela. 
No MySQL versão 4.1 a saída do EXPLAIN foi alterada para funcionar melhor com 
construções como UNIONs, subqueries e tabelas derivadas. A mais notável é a adição de duas 
novas colunas: id e select_type. 
Instruções com o uso do EXPLAIN: 
● EXPLAIN trabalha com SELECT, DELETE, INSERT, REPLACE, e UPDATE. 
● Quando EXPLAIN é usado com uma declaração explicável, o MySQL exibe informações 
do otimizador sobre o plano de execução da declaração. Ou seja, o MySQL explica como 
processaria a instrução, incluindo informações sobre como as tabelas são unidas e em que 
ordem. 
● Quando o EXPLAIN é usado com uma instrução explicável, ele exibe o plano de 
execução da instrução em execução na conexão nomeada. 
● Para instruções SELECT, o EXPLAIN produz informações adicionais do plano de 
execução que podem ser exibidas usando SHOW WARNINGS. 
● EXPLAIN é útil para examinar consultas envolvendo tabelas particionadas. 
● A opção FORMAT pode ser usada para selecionar o formato de saída. TRADITIONAL 
apresenta a saída em formato tabular. Este é o padrão se nenhuma opção FORMAT 
estiver presente. Ele exibe as informações no formato JSON. 
 
Como exemplo de uma junção perfeita, o EXPLAIN produzirá este resultado: 
table type possible_keys key key_len ref rows Extra 
tt ALL AssignedPC NULL NULL NULL 3872 Using 
 ClientID, where 
 ActualPC 
et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1 
et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1 
do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1 
6. Controlando o otimizador 
O MySQL fornece o controle do otimizador através de variáveis ​​do sistema que afetam 
como os planos de consulta são avaliados. 
 
6.1 Controlando a avaliação do plano de consulta 
A tarefa do otimizador de consulta é encontrar um plano ideal para executar uma consulta 
SQL. Porque a diferença no desempenho entre os planos, sendo "bom" e "ruim", podem ser 
gigantescas (ou seja, segundos, horas ou mesmo dias), a maioria dos otimizadores de consulta, 
incluindo o MySQL, realiza uma pesquisa mais ou menos exaustiva de um plano ideal entre 
todos os possíveis planos de avaliação de consulta. Para consultas de junção, o número de 
possíveis planos investigados pelo otimizador do MySQL cresce exponencialmente com o 
número de tabelas referenciadas em uma consulta. Para um pequeno número de tabelas 
(geralmente menor que 7 a 10), isso não é um problema. No entanto, quando consultas maiores 
são enviadas, o tempo gasto na otimização de consultas pode facilmente se tornar o principal 
gargalo no desempenho do servidor. 
Um método mais flexível para otimização de consultas permite ao usuário controlar o 
quão exaustivo o otimizador é na busca de um plano ideal de avaliação de consultas. A idéia 
geral é que, quanto menos planos forem investigados pelo otimizador, menos tempo será gasto 
na compilação de uma consulta. Por outro lado, como o otimizador ignora alguns planos, ele 
pode não encontrar um plano ideal. 
O comportamento do otimizador em relação ao número de planos que ele avalia pode ser 
controlado usando duas variáveis ​​do sistema: 
● A variável optimizer_prune_level diz ao otimizador para ignorar determinados planos 
com base em estimativas do número de linhas acessadas para cada tabela. Nossa 
experiência mostra que esse tipo de "palpite " raramente deixa de lado os planos ideais e 
pode reduzir drasticamente os tempos de compilação de consultas. É por isso que esta 
opção está ativada (optimizer_prune_level=1) por padrão. No entanto, sevocê acredita 
que o otimizador perdeu um plano de consulta melhor, essa opção pode ser desativada 
(optimizer_prune_level=0), com o risco de a compilação de consultas demorar muito 
mais. Observe que, mesmo com o uso dessa heurística, o otimizador ainda explora um 
número aproximadamente exponencial de planos. 
● A variável optimizer_search_depth informa a que distância do “futuro” de cada plano 
incompleto o otimizador deve procurar avaliar se deve ser expandido ainda mais. Valores 
menores de optimizer_search_depth podem resultar em menores tempos de compilação 
de consultas. Por exemplo, consultas com 12, 13 ou mais tabelas podem exigir facilmente 
horas e até dias para serem compiladas se optimizer_search_depth estiver próximo ao 
número de tabelas na consulta. Ao mesmo tempo, se compilado com 
optimizer_search_depth igual a 3 ou 4, o otimizador pode compilar em menos de um 
minuto para a mesma consulta. Se você não tiver certeza do valor para a variável 
optimizer_search_depth, ela pode ser definida como 0 para informar ao otimizador para 
determinar o valor automaticamente. 
6.2 Otimizações comutáveis 
A variável optimizer_switch do sistema permite o controle sobre o comportamento do 
otimizador. Seu valor é um conjunto de sinalizadores, cada um com um valor on ou off para 
indicar se o comportamento do otimizador correspondente está ativado ou desativado. Essa 
variável possui valores globais e de sessão e pode ser alterada em tempo de execução. O padrão 
global pode ser definido na inicialização do servidor. 
Para ver o conjunto atual de sinalizadores do otimizador, selecione o valor da variável: 
mysql>​ ​SELECT​ ​@@optimizer_switch​\G 
***************************​ 1. row ​*************************** 
@@optimizer_switch​:​ index_merge=on,index_merge_union=on, 
 index_merge_sort_union=on, 
 index_merge_intersection=on, 
 engine_condition_pushdown=on, 
 index_condition_pushdown=on, 
 mrr=on,mrr_cost_based=on, 
 block_nested_loop=on,batched_key_access=off, 
 materialization=on,semijoin=on,loosescan=on, 
 firstmatch=on,duplicateweedout=on, 
 subquery_materialization_cost_based=on, 
 use_index_extensions=on, 
 condition_fanout_filter=on,derived_merge=on 
 
Para alterar o valor de optimizer_switch, atribua um valor que consiste em uma lista 
separada por vírgula de um ou mais comandos: 
SET​ ​[​GLOBAL​|​SESSION​]​ optimizer_switch​=​'​command​[,​command​]...'​; 
 
Cada valor deve ter um dos formulários mostrados na tabela a seguir. 
 
Sintaxe de Comando Significado 
default Redefina toda otimização para seu valor padrão 
opt_name​=default Defina a otimização nomeada como seu valor padrão 
opt_name​=off Desativar a otimização nomeada 
opt_name​=on Ativar a otimização nomeada 
 
6.3 Dicas do otimizador 
Um meio de controle sobre as estratégias do otimizador é definir a variável 
optimizer_switch do sistema. Alterações nessa variável afetam a execução de todas as consultas 
subsequentes, para afetar uma consulta de maneira diferente de outra, é necessário alterar 
optimizer_switch antes de cada uma. 
Outra maneira de controlar o otimizador é usando dicas do otimizador, que podem ser 
especificadas em instruções individuais. Como as dicas do otimizador se aplicam por instrução, 
elas fornecem um controle mais refinado sobre os planos de execução de instruções do que pode 
ser obtido usando optimizer_switch. Por exemplo, você pode ativar uma otimização para uma 
tabela em uma instrução e desativar a otimização para uma tabela diferente. Dicas em uma 
instrução têm precedência sobre sinalizadores optimizer_switch. 
Exemplo: 
SELECT​ ​/*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */​ f1 
 ​FROM​ t3 ​WHERE​ f1 ​>​ ​30​ ​AND​ f1 ​<​ ​33​; 
SELECT​ ​/*+ BKA(t1) NO_BKA(t2) */​ ​*​ ​FROM​ t1 ​INNER​ ​JOIN​ t2 ​WHERE​ ​...; 
SELECT​ ​/*+ NO_ICP(t1, t2) */​ ​*​ ​FROM​ t1 ​INNER​ ​JOIN​ t2 ​WHERE​ ​...; 
SELECT​ ​/*+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) */​ ​*​ ​FROM​ t1 ​...; 
EXPLAIN​ ​SELECT​ ​/*+ NO_ICP(t1) */​ ​*​ ​FROM​ t1 ​WHERE​ ​...; 
6.4 Dicas de índice 
As dicas de índice fornecem ao otimizador informações sobre como escolher índices 
durante o processamento da consulta. As dicas de índice se aplicam apenas a instruções 
SELECT. As dicas de índice são especificadas após o nome de uma tabela. A sintaxe para se 
referir a uma tabela individual, incluindo dicas de índice, é semelhante a esta a seguir: 
 
 
 
 
 
 
 
 
 
 
tbl_name​ ​[[​AS​]​ ​alias​]​ ​[​index_hint_list​] 
 
index_hint_list​: 
 ​index_hint​ ​[​index_hint​]​ ​... 
 
index_hint​: 
 ​USE​ {​INDEX​|​KEY​} 
 ​[​FOR​ {​JOIN​|​ORDER​ ​BY​|​GROUP​ ​BY​}​]​ ​([​index_list​]) 
 ​|​ {​IGNORE​|​FORCE​} {​INDEX​|​KEY​} 
 ​[​FOR​ {​JOIN​|​ORDER​ ​BY​|​GROUP​ ​BY​}​]​ ​(​index_list​) 
 
index_list​: 
 ​index_name​ ​[,​ ​index_name​]​ ​… 
6.5 O modelo de custo do otimizador 
Para gerar planos de execução, o otimizador usa um modelo de custo baseado em 
estimativas do custo de várias operações que ocorrem durante a execução da consulta. 
O otimizador possui um banco de dados de estimativas de custo para usar durante a 
construção do plano de execução. Essas estimativas são armazenadas nas tabelas server_cost e 
engine_cost no mysql e são configuráveis ​​a qualquer momento. O objetivo dessas tabelas é 
possibilitar o ajuste fácil das estimativas de custo que o otimizador usa quando tenta chegar aos 
planos de execução da consulta. 
6.5.1 Operação geral do modelo de custo 
O modelo de custo do otimizador configurável funciona assim: 
● O servidor lê as tabelas do modelo de custo na memória na inicialização e usa os valores 
na memória no tempo de execução. Qualquer estimativa NULL sem custo especificada 
nas tabelas tem precedência sobre a constante de custo padrão compilada correspondente. 
Qualquer estimativa NULL indica ao otimizador para usar o padrão compilado. 
● Em tempo de execução, o servidor pode reler as tabelas de custos. Isso ocorre quando um 
mecanismo de armazenamento é carregado dinamicamente ou quando uma instrução 
FLUSH OPTIMIZER_COSTS é executada. 
● As tabelas de custo permitem que os administradores do servidor ajustem facilmente as 
estimativas de custo alterando as entradas nas tabelas. Também é fácil reverter para o 
padrão, definindo o custo de uma entrada para NULL. O otimizador usa os valores de 
custo na memória, portanto, as alterações nas tabelas devem ser seguidas por FLUSH 
OPTIMIZER_COSTS para entrar em vigor. 
● As tabelas de custo são específicas para uma determinada instância do servidor. O 
servidor não replica as alterações da tabela de custos em escravos de replicados. 
6.5.2 O banco de dados do modelo de custo 
O banco de dados do modelo de custo do otimizador consiste em duas tabelas que contêm 
informações de estimativa de custo para operações que ocorrem durante a execução da consulta: 
● server_cost: Estimativas de custo do otimizador para operações gerais do servidor. 
● Engine_cost: Estimativas de custo do otimizador para operações específicas de 
mecanismos de armazenamento. 
 
A tabela server_cost, por exemplo, contém estas colunas: 
cost_name 
O nome de um cálculo de custo usado no modelo de custo. O nome não diferenciamaiúsculas de minúsculas. Se o servidor não reconhecer o nome do custo ao ler esta tabela, ele 
gravará um aviso no log de erros. 
cost_value 
O valor da estimativa de custo. Se o valor for diferente de NULL, o servidor o utilizará 
como custo. Caso contrário, ele usa a estimativa padrão (o valor compilado). Os DBAs podem 
alterar um cálculo de custo atualizando esta coluna. Se o servidor descobrir que o valor do custo 
é inválido (não positivo) ao ler esta tabela, ele grava um aviso no log de erros. 
last_update 
A hora da última atualização da linha. 
comment 
Um comentário descritivo associado ao cálculo de custos. Os DBAs podem usar esta 
coluna para fornecer informações sobre por que uma linha de estimativa de custo armazena um 
valor específico. 
6.5.3 Fazendo alterações no banco de dados do modelo de custo 
Para os DBAs que desejam alterar os parâmetros do modelo de custo dos padrões, tente 
dobrar ou reduzir pela metade o valor e medir o efeito. Alterações nos parâmetros 
io_block_read_cost e memory_block_read_cost são mais prováveis ​​de produzir resultados que 
valem a pena. 
Esses valores de parâmetro permitem que modelos de custo para métodos de acesso a 
dados levem em consideração os custos de leitura de informações de diferentes fontes, isto é, o 
custo de ler informações do disco versus ler as informações já em um buffer de memória. Por 
exemplo, todas as outras coisas são iguais, definir io_block_read_cost um valor maior que 
memory_block_read_cost faz com que o otimizador prefira planos de consulta que leem 
informações já mantidas na memória a planos que devem ler do disco. 
Este exemplo mostra como alterar o valor padrão para io_block_read_cost: 
UPDATE​ mysql.engine_cost 
 ​SET​ cost_value ​=​ ​2.0 
 ​WHERE​ cost_name ​=​ ​'io_block_read_cost'​; 
FLUSH​ ​OPTIMIZER_COSTS​; 
 
Este exemplo mostra como alterar o valor io_block_read_cost apenas para o mecanismo 
de armazenamento InnoDB: 
INSERT​ ​INTO​ mysql​.​engine_cost 
 ​VALUES​ ​(​'InnoDB'​,​ ​0​,​ ​'io_block_read_cost'​,​ ​3.0​, 
 ​CURRENT_TIMESTAMP​,​ ​'Using a slower disk for InnoDB'​); 
FLUSH​ ​OPTIMIZER_COSTS​; 
7. Histograma 
O Esquema de desempenho mantém tabelas de resumo de eventos de instruções que 
contêm informações sobre latência mínima, máxima e média de instruções. Essas tabelas 
permitem uma avaliação de alto nível do desempenho do sistema. Para permitir a avaliação em 
um nível mais refinado, o Esquema de Desempenho também coleta dados do histograma para 
latências de instruções. Esses histogramas fornecem informações adicionais sobre distribuições 
de latência. 
 
Informações do histograma: 
mysql>​ ​SELECT​ ​* 
 ​FROM​ performance_schema​.​events_statements_histogram_by_digest 
 ​WHERE​ ​SCHEMA_NAME​ ​=​ ​'mydb'​ ​AND​ DIGEST ​=​ ​'bb3f69453119b2d7b3ae40673a9d4c7c' 
 ​AND​ COUNT_BUCKET ​>​ ​0​ ​ORDER​ ​BY​ BUCKET_NUMBER\G 
***************************​ 1. row ​*************************** 
 SCHEMA_NAME​:​ mydb 
 DIGEST​:​ bb3f69453119b2d7b3ae40673a9d4c7c 
 BUCKET_NUMBER​:​ 42 
 BUCKET_TIMER_LOW​:​ 66069344 
 BUCKET_TIMER_HIGH​:​ 69183097 
 COUNT_BUCKET​:​ 1 
COUNT_BUCKET_AND_LOWER​:​ 1 
 BUCKET_QUANTILE​:​ 0.058824 
***************************​ 2. row ​*************************** 
 SCHEMA_NAME​:​ mydb 
 DIGEST​:​ bb3f69453119b2d7b3ae40673a9d4c7c 
 BUCKET_NUMBER​:​ 43 
 BUCKET_TIMER_LOW​:​ 69183097 
 BUCKET_TIMER_HIGH​:​ 72443596 
 COUNT_BUCKET​:​ 1 
COUNT_BUCKET_AND_LOWER​:​ 2 
 BUCKET_QUANTILE​:​ 0.117647 
***************************​ 3. row ​*************************** 
 SCHEMA_NAME​:​ mydb 
 DIGEST​:​ bb3f69453119b2d7b3ae40673a9d4c7c 
 BUCKET_NUMBER​:​ 44 
 BUCKET_TIMER_LOW​:​ 72443596 
 BUCKET_TIMER_HIGH​:​ 75857757 
 COUNT_BUCKET​:​ 2 
COUNT_BUCKET_AND_LOWER​:​ 4 
 BUCKET_QUANTILE​:​ 0.235294 
***************************​ 4. row ​*************************** 
 SCHEMA_NAME​:​ mydb 
 DIGEST​:​ bb3f69453119b2d7b3ae40673a9d4c7c 
 BUCKET_NUMBER​:​ 45 
 BUCKET_TIMER_LOW​:​ 75857757 
 BUCKET_TIMER_HIGH​:​ 79432823 
 COUNT_BUCKET​:​ 6 
COUNT_BUCKET_AND_LOWER​:​ 10 
 BUCKET_QUANTILE​:​ 0.625000 
… 
 
Por exemplo, valores a seguir indicam que 23,52% das consultas são executadas em 
menos de 75,86 microssegundos: 
BUCKET_TIMER_HIGH: ​75857757 
 BUCKET_QUANTILE: ​0.235294 
 
Os valores abaixo indicam que 62,50% das consultas são executadas em menos de 79,44 
microssegundos: 
BUCKET_TIMER_HIGH: ​79432823 
 BUCKET_QUANTILE: ​0.625000 
 
Um histograma consiste em N intervalos, em que cada linha representa um intervalo, com 
o número do intervalo indicado pela coluna BUCKET_NUMBER. Os números do intervalo 
começam com 0. Cada tabela de resumo do histograma de instrução possui essas colunas de 
resumo contendo valores agregados: 
 
BUCKET_TIMER_LOW, BUCKET_TIMER_HIGH 
Um bucket conta instruções que possuem uma latência, medida entre 
BUCKET_TIMER_LOW e BUCKET_TIMER_HIGH: 
● O valor de BUCKET_TIMER_LOW para o primeiro intervalo ( BUCKET_NUMBER= 
0) é 0. 
● O valor de BUCKET_TIMER_LOWpara um bucket ( BUCKET_NUMBER= k) é o 
mesmo que BUCKET_TIMER_HIGH para o bucket anterior ( BUCKET_NUMBER= 
k−1) 
● O último bloco é um conjunto de instruções que possuem uma latência que excede os 
blocos anteriores no histograma. 
 
COUNT_BUCKET 
O número de instruções medidas com uma latência no intervalo de 0 até 
BUCKET_TIMER_LOW, mas não incluindo BUCKET_TIMER_HIGH. 
 
COUNT_BUCKET_AND_LOWER 
O número de instruções medidas com uma latência no intervalo de 0 em diante, não 
incluindo BUCKET_TIMER_HIGH. 
 
 
 
 
 
BUCKET_QUANTILE 
A proporção de instruções que se enquadram neste ou em um intervalo inferior. Essa 
proporção corresponde, por definição, COUNT_BUCKET_AND_LOWER / 
SUM(COUNT_BUCKET) e é exibida como uma coluna de conveniência. 
8. Conclusão 
Neste trabalho abordamos a importância da indexação inteligente, algoritmos de junção, 
examinamos o funcionamento do plano EXPLAIN e também detalhamos vários procedimentos 
que podem ser úteis para um melhor desempenho do SGBD. 
Como observado neste estudo, as operações de banco de dados geralmente tendem a ser o 
principal gargalo para a maioria dos aplicativos atualmente. Não são apenas os DBAs que 
precisam se preocupar com esses problemas de desempenho. Os programadores precisam fazer a 
sua parte estruturando as tabelas corretamente, escrevendo consultas otimizadas e o melhor 
código para aquela aplicação. 
Bibliografia 
● Documentação do MySQL: 
■ https://dev.mysql.com/doc/ 
■ Acesso em: 24/11/2019 
 
 
https://dev.mysql.com/doc/