{"id":5102,"date":"2021-05-23T19:43:26","date_gmt":"2021-05-23T22:43:26","guid":{"rendered":"https:\/\/blog.clusterweb.com.br\/?p=5102"},"modified":"2021-05-23T19:46:09","modified_gmt":"2021-05-23T22:46:09","slug":"como-usar-o-explain-para-perfilar-o-desempenho-da-consulta-no-banco-de-dados-do-azure-para-mariadb","status":"publish","type":"post","link":"https:\/\/blog.clusterweb.com.br\/?p=5102","title":{"rendered":"Como usar o EXPLAIN para perfilar o desempenho da consulta no Banco de Dados do Azure para MariaDB"},"content":{"rendered":"

EXPLAIN<\/strong>\u00a0\u00e9 uma ferramenta \u00fatil para otimizar consultas. A instru\u00e7\u00e3o EXPLAIN pode ser utilizada para obter informa\u00e7\u00f5es sobre como as instru\u00e7\u00f5es SQL s\u00e3o executadas. A sa\u00edda a seguir mostra um exemplo da execu\u00e7\u00e3o de uma instru\u00e7\u00e3o EXPLAIN.<\/p>\n

\n

SQL<\/span><\/p>\n

mysql> EXPLAIN<\/span> SELECT<\/span> * FROM<\/span> tb1 WHERE<\/span> id<\/span>=100<\/span>\\G\r\n*************************** 1.<\/span> row<\/span> ***************************\r\n           id<\/span>: 1<\/span>\r\n  select_type: SIMPLE\r\n        table<\/span>: tb1\r\n   partitions<\/span>: NULL<\/span>\r\n         type<\/span>: ALL<\/span>\r\npossible_keys: NULL<\/span>\r\n          key<\/span>: NULL<\/span>\r\n      key_len: NULL<\/span>\r\n          ref<\/span>: NULL<\/span>\r\n         rows<\/span>: 995789<\/span>\r\n     filtered: 10.00<\/span>\r\n        Extra: Using<\/span> where<\/span>\r\n<\/code><\/pre>\n
<\/p>\n
Como pode ser observado a partir desse exemplo, o valor da\u00a0chave<\/em>\u00a0\u00e9 NULL. Essa sa\u00edda significa que o MariaDB n\u00e3o pode encontrar nenhum \u00edndice otimizado para a consulta e executa uma verifica\u00e7\u00e3o de tabela. Vamos otimizar essa consulta, adicionando um \u00edndice na coluna\u00a0ID<\/strong>.<\/p>\n
SQL<\/span><\/div>\n
mysql> ALTER<\/span> TABLE<\/span> tb1 ADD<\/span> KEY<\/span> (id<\/span>);\r\nmysql> EXPLAIN<\/span> SELECT<\/span> * FROM<\/span> tb1 WHERE<\/span> id<\/span>=100<\/span>\\G\r\n*************************** 1.<\/span> row<\/span> ***************************\r\n           id<\/span>: 1<\/span>\r\n  select_type: SIMPLE\r\n        table<\/span>: tb1\r\n   partitions<\/span>: NULL<\/span>\r\n         type<\/span>: ref<\/span>\r\npossible_keys: id<\/span>\r\n          key<\/span>: id<\/span>\r\n      key_len: 4<\/span>\r\n          ref<\/span>: const\r\n         rows<\/span>: 1<\/span>\r\n     filtered: 100.00<\/span>\r\n        Extra: NULL<\/span>\r\n<\/code><\/pre>\n
O novo EXPLAIN mostra que o MariaDB agora usa um \u00edndice para limitar o n\u00famero de linhas a 1, o que reduziu drasticamente o tempo de busca.<\/p>\n
\u00cdndice de cobertura<\/h2>\n
Um \u00edndice de cobertura consiste em todas as colunas de uma consulta no \u00edndice para reduzir a recupera\u00e7\u00e3o de valor a partir de tabelas de dados. Aqui est\u00e1 uma ilustra\u00e7\u00e3o na instru\u00e7\u00e3o\u00a0GROUP BY<\/strong>\u00a0a seguir.<\/p>\n
SQL<\/span><\/div>\n
mysql> EXPLAIN<\/span> SELECT<\/span> MAX<\/span>(c1), c2 FROM<\/span> tb1 WHERE<\/span> c2 LIKE<\/span> '%100'<\/span> GROUP<\/span> BY<\/span> c1\\G\r\n*************************** 1.<\/span> row<\/span> ***************************\r\n           id<\/span>: 1<\/span>\r\n  select_type: SIMPLE\r\n        table<\/span>: tb1\r\n   partitions<\/span>: NULL<\/span>\r\n         type<\/span>: ALL<\/span>\r\npossible_keys: NULL<\/span>\r\n          key<\/span>: NULL<\/span>\r\n      key_len: NULL<\/span>\r\n          ref<\/span>: NULL<\/span>\r\n         rows<\/span>: 995789<\/span>\r\n     filtered: 11.11<\/span>\r\n        Extra: Using<\/span> where<\/span>; Using temporary; Using filesort\r\n<\/code><\/pre>\n
Como pode ser visto na sa\u00edda, o MariaDB n\u00e3o usa nenhum \u00edndice porque nenhum \u00edndice adequado est\u00e1 dispon\u00edvel. Tamb\u00e9m mostra\u00a0Usando tempor\u00e1rio; Usando file sort<\/em>, o que significa que o MariaDB cria uma tabela tempor\u00e1ria para satisfazer a cl\u00e1usula\u00a0GROUP BY<\/strong>.<\/p>\n
Criar um \u00edndice na coluna\u00a0c2<\/strong>\u00a0sozinho n\u00e3o faz diferen\u00e7a, e o MariaDB ainda precisa criar uma tabela tempor\u00e1ria:<\/p>\n
SQL<\/span><\/div>\n
mysql> ALTER<\/span> TABLE<\/span> tb1 ADD<\/span> KEY<\/span> (c2);\r\nmysql> EXPLAIN<\/span> SELECT<\/span> MAX<\/span>(c1), c2 FROM<\/span> tb1 WHERE<\/span> c2 LIKE<\/span> '%100'<\/span> GROUP<\/span> BY<\/span> c1\\G\r\n*************************** 1.<\/span> row<\/span> ***************************\r\n           id<\/span>: 1<\/span>\r\n  select_type: SIMPLE\r\n        table<\/span>: tb1\r\n   partitions<\/span>: NULL<\/span>\r\n         type<\/span>: ALL<\/span>\r\npossible_keys: NULL<\/span>\r\n          key<\/span>: NULL<\/span>\r\n      key_len: NULL<\/span>\r\n          ref<\/span>: NULL<\/span>\r\n         rows<\/span>: 995789<\/span>\r\n     filtered: 11.11<\/span>\r\n        Extra: Using<\/span> where<\/span>; Using temporary; Using filesort\r\n<\/code><\/pre>\n
Neste caso, um\u00a0\u00edndice coberto<\/strong>\u00a0em ambos\u00a0c1<\/strong>\u00a0e\u00a0c2<\/strong>\u00a0pode ser criado, atrav\u00e9s do qual adicionar o valor de\u00a0c2<\/strong>” diretamente no \u00edndice para eliminar pesquisa de dados adicionais.<\/p>\n
SQL<\/span><\/div>\n
mysql> ALTER<\/span> TABLE<\/span> tb1 ADD<\/span> KEY<\/span> covered(c1,c2);\r\nmysql> EXPLAIN<\/span> SELECT<\/span> MAX<\/span>(c1), c2 FROM<\/span> tb1 WHERE<\/span> c2 LIKE<\/span> '%100'<\/span> GROUP<\/span> BY<\/span> c1\\G\r\n*************************** 1.<\/span> row<\/span> ***************************\r\n           id<\/span>: 1<\/span>\r\n  select_type: SIMPLE\r\n        table<\/span>: tb1\r\n   partitions<\/span>: NULL<\/span>\r\n         type<\/span>: index<\/span>\r\npossible_keys: covered\r\n          key<\/span>: covered\r\n      key_len: 108<\/span>\r\n          ref<\/span>: NULL<\/span>\r\n         rows<\/span>: 995789<\/span>\r\n     filtered: 11.11<\/span>\r\n        Extra: Using<\/span> where<\/span>; Using index\r\n<\/code><\/pre>\n
Como o EXPLAIN acima mostra, o MariaDB agora usa o \u00edndice coberto e evita a cria\u00e7\u00e3o de uma tabela tempor\u00e1ria.<\/p>\n
\u00cdndice combinado<\/h2>\n
Um \u00edndice combinado consiste em valores de v\u00e1rias colunas e pode ser considerado uma matriz de linhas ordenadas por concatena\u00e7\u00e3o de valores das colunas indexadas. \u00a0Esse m\u00e9todo pode ser \u00fatil em uma instru\u00e7\u00e3o\u00a0GROUP BY<\/strong>.<\/p>\n
SQL<\/span><\/div>\n
mysql> EXPLAIN<\/span> SELECT<\/span> c1, c2 from<\/span> tb1 WHERE<\/span> c2 LIKE<\/span> '%100'<\/span> ORDER<\/span> BY<\/span> c1 DESC<\/span> LIMIT<\/span> 10<\/span>\\G\r\n*************************** 1.<\/span> row<\/span> ***************************\r\n           id<\/span>: 1<\/span>\r\n  select_type: SIMPLE\r\n        table<\/span>: tb1\r\n   partitions<\/span>: NULL<\/span>\r\n         type<\/span>: ALL<\/span>\r\npossible_keys: NULL<\/span>\r\n          key<\/span>: NULL<\/span>\r\n      key_len: NULL<\/span>\r\n          ref<\/span>: NULL<\/span>\r\n         rows<\/span>: 995789<\/span>\r\n     filtered: 11.11<\/span>\r\n        Extra: Using<\/span> where<\/span>; Using filesort\r\n<\/code><\/pre>\n
O MariaDB executa uma opera\u00e7\u00e3o de\u00a0classifica\u00e7\u00e3o de arquivos<\/em>\u00a0que \u00e9 bastante lenta, especialmente quando precisa classificar v\u00e1rias linhas. Para otimizar essa consulta, um \u00edndice combinado pode ser criado em ambas as colunas que est\u00e3o sendo classificadas.<\/p>\n
SQL<\/span><\/div>\n
mysql> ALTER<\/span> TABLE<\/span> tb1 ADD<\/span> KEY<\/span> my_sort2 (c1, c2);\r\nmysql> EXPLAIN<\/span> SELECT<\/span> c1, c2 from<\/span> tb1 WHERE<\/span> c2 LIKE<\/span> '%100'<\/span> ORDER<\/span> BY<\/span> c1 DESC<\/span> LIMIT<\/span> 10<\/span>\\G\r\n*************************** 1.<\/span> row<\/span> ***************************\r\n           id<\/span>: 1<\/span>\r\n  select_type: SIMPLE\r\n        table<\/span>: tb1\r\n   partitions<\/span>: NULL<\/span>\r\n         type<\/span>: index<\/span>\r\npossible_keys: NULL<\/span>\r\n          key<\/span>: my_sort2\r\n      key_len: 108<\/span>\r\n          ref<\/span>: NULL<\/span>\r\n         rows<\/span>: 10<\/span>\r\n     filtered: 11.11<\/span>\r\n        Extra: Using<\/span> where<\/span>; Using index\r\n<\/code><\/pre>\n
O EXPLAIN agora mostra que o MariaDB \u00e9 capaz de usar o \u00edndice combinado para evitar classifica\u00e7\u00e3o adicional, j\u00e1 que o \u00edndice j\u00e1 est\u00e1 classificado.<\/p>\n
Conclus\u00e3o<\/h2>\n
O uso de EXPLAIN e diferentes tipos de \u00edndices pode aumentar significativamente o desempenho. Ter um \u00edndice na tabela n\u00e3o significa necessariamente que o MariaDB seja capaz de us\u00e1-lo para suas consultas. Sempre valide suas pressuposi\u00e7\u00f5es usando a EXPLAIN e otimize as consultas usando \u00edndices.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
EXPLAIN\u00a0\u00e9 uma ferramenta \u00fatil para otimizar consultas. A instru\u00e7\u00e3o EXPLAIN pode ser utilizada para obter informa\u00e7\u00f5es sobre como as instru\u00e7\u00f5es SQL s\u00e3o executadas. A sa\u00edda a seguir mostra um exemplo da execu\u00e7\u00e3o de uma instru\u00e7\u00e3o EXPLAIN. SQL mysql> EXPLAIN SELECT * FROM tb1 WHERE id=100\\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tb1 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[91,730,1,42,51,1411,495,102],"tags":[1542,1417,378,1541,877,1418,353,906,366,1539,1201,361,698,355,1540,851],"class_list":["post-5102","post","type-post","status-publish","format-standard","hentry","category-banco-de-dados","category-clusterweb","category-viazap","category-leitura-recomendada","category-linux-linuxrs","category-mysql","category-profissional-de-ti","category-windows","tag-azure","tag-banco","tag-como","tag-consulta","tag-da","tag-dados","tag-de","tag-desempenho","tag-do","tag-explain","tag-mariadb","tag-no","tag-o","tag-para","tag-perfilar","tag-usar"],"_links":{"self":[{"href":"https:\/\/blog.clusterweb.com.br\/index.php?rest_route=\/wp\/v2\/posts\/5102","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.clusterweb.com.br\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.clusterweb.com.br\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.clusterweb.com.br\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.clusterweb.com.br\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5102"}],"version-history":[{"count":3,"href":"https:\/\/blog.clusterweb.com.br\/index.php?rest_route=\/wp\/v2\/posts\/5102\/revisions"}],"predecessor-version":[{"id":5105,"href":"https:\/\/blog.clusterweb.com.br\/index.php?rest_route=\/wp\/v2\/posts\/5102\/revisions\/5105"}],"wp:attachment":[{"href":"https:\/\/blog.clusterweb.com.br\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5102"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.clusterweb.com.br\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5102"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.clusterweb.com.br\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5102"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}