Compreender as consultas MySQL com explicar

você está em seu novo trabalho como administrador de banco de dados ou engenheiro de dados e você acabou de se perder tentando descobrir o que essas consultas de aparência insana devem significar e fazer. Por que existem 5 junções e por que existe um ORDER BY usado dentro de uma subquery antes de uma das junções acontecer? Lembre – se, você foi contratado por uma razão-muito provavelmente, essa razão também tem a ver com muitas perguntas complicadas que foram criadas e editadas ao longo da última década.

TheEXPLAIN keyword is used throughout various SQL databases and provides information about how your SQL database executes a query. No MySQL, EXPLAIN pode ser usada na frente de uma consulta começando com SELECTINSERTDELETEREPLACE e UPDATE. Para uma consulta simples, pareceria com o seguinte:

EXPLAIN SELECT * FROM foo WHERE foo.bar = 'infrastructure as a service' OR foo.bar = 'iaas';

em vez do resultado habitual, MySQL iria então mostrar o seu plano de execução da declaração, explicando quais os processos que ocorrem em que ordem ao executar a declaração.

Nota: Se EXPLAINnão funcionar para si, o seu utilizador da base de dados poderá não ter o privilégio SELECT privilégio para as tabelas ou vistas que está a usar na sua declaração.

EXPLAIN é uma grande ferramenta para resolver rapidamente consultas lentas. Embora ele possa certamente ajudá-lo, ele não vai tirar a necessidade de pensamento estrutural e uma boa visão geral dos modelos de dados em vigor. Muitas vezes, a correção mais simples e o conselho mais rápido é adicionar um índice às colunas de uma tabela específica em questão se elas são usadas em muitas consultas com problemas de desempenho. Cuidado, no entanto, não use muitos índices como isso pode ser contraproducente. Ler o índice e a tabela só faz sentido se a tabela tem uma quantidade significativa de linhas e você precisa apenas de alguns pontos de dados. Se você está recuperando um enorme resultado definido a partir de uma tabela e questionando colunas diferentes muitas vezes, um índice em cada coluna não faz sentido e impede o desempenho mais do que ajuda. Para mais informações sobre os cálculos reais do Índice vs nenhum índice, leia a estimativa de desempenho na documentação oficial MySQL.

As coisas que você deseja evitar sempre que possível e aplicável São ordenação e cálculos dentro de consultas. Se você acha que não pode evitar cálculos dentro de suas consultas: Sim, você pode. Escreva o conjunto de resultados em outro lugar e calcule seu ponto de dados fora da consulta, ele vai colocar menos tensão no banco de dados e, portanto, ser globalmente melhor para a sua aplicação. Certifique-se apenas de documentar por que você está calculando dentro de sua aplicação em vez de ter um resultado produzido em SQL imediatamente. Caso contrário, o próximo administrador de banco de dados ou desenvolvedor vai aparecer e ter a gloriosa idéia de usar um cálculo dentro da consulta ao longo das linhas de: “Oh olhe, meu antecessor nem sabia que você pode fazer isso em SQL!”Algumas equipes de desenvolvedores que ainda não tiveram o problema inevitável de bancos de dados moribundos podem usar cálculos em consulta para diferenças de número entre datas ou pontos de dados semelhantes.

A regra geral para consultas SQL é a seguinte:

seja preciso e gere apenas os resultados de que necessita.

Vamos conferir um pouco mais complicado de consulta…

SELECT site_options.domain, sites_users.user, site_taxes.monthly_statement_fee, site.name, AVG(price) AS average_product_price FROM sites_orders_products, site_taxes, site, sites_users, site_options WHERE site_options.site_id = site.id AND sites_users.id = site.user_id AND site_taxes.site_id = site.id AND sites_orders_products.site_id = site.id GROUP BY site.id ORDER BY site.date_modified desc LIMIT 5;+-----------------------------+-----------------------------+-----------------------+------------------------------------------+-----------------------+| domain | user | monthly_statement_fee | name | average_product_price |+-----------------------------+-----------------------------+-----------------------+------------------------------------------+-----------------------+| www.xxxxxxxxxxxxxxxxxxx.com | [email protected] | 0.50 | xxxxxxxxxxxxxxxxxxxxx | 3.254781 || www.xxxxxxxxxxx.com | [email protected] | 0.50 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | 9.471022 || | [email protected] | 0.00 | xxxxxxxxxxxxxxxxx | 8.646297 || | [email protected] | 0.00 | xxxxxxxxxxxxxxx | 9.042460 || | [email protected] | 0.00 | xxxxxxxxxxxxxxxxxx | 6.679182 |+-----------------------------+-----------------------------+-----------------------+------------------------------------------+-----------------------+5 rows in set (0.00 sec)

…e o seu EXPLAIN saída.

+------+-------------+---------------------------------+--------+-----------------+---------------+---------+---------------------------------+------+-----------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+-------------+---------------------------------+--------+-----------------+---------------+---------+---------------------------------+------+-----------+| 1 | SIMPLE | sites | index | PRIMARY,user_id | PRIMARY | 4 | NULL | 858 | Using temporary; Using filesort || 1 | SIMPLE | sites_options | ref | site_id | site_id | 4 | service.sites.id | 1 | || 1 | SIMPLE | sites_taxes | ref | site_id | site_id | 4 | service.sites.id | 1 | || 1 | SIMPLE | sites_users | eq_ref | PRIMARY | PRIMARY | 4 | service.sites.user_id | 1 | || 1 | SIMPLE | sites_orders_products | ref | site_id | site_id | 4 | service.sites.id | 4153 | |//+------+-------------+---------------------------------+--------+-----------------+---------------+---------+---------------------------------+------+-----------+5 rows in set (0.00 sec)

as colunas na EXPLAIN saída com as que necessitam de atenção especial para identificar problemas a negrito são:

• id (id de consulta)
• select_type (tipo de declaração)
• table (tabela de referência)
• type (tipo de associação)
• possible_keys (as teclas que poderia ter sido usado)
• key (chave que foi usada)
• key_len (comprimento da chave usada)
• ref (colunas em comparação ao índice)
• linhas (quantidade de linhas pesquisadas)
• Extra (informações adicionais)

quanto maior A quantidade de linhas a ser procurado, a melhor a nível de otimização sobre índices e consulta de precisão precisa estar em ordem para maximizar o desempenho. A coluna Extra mostra possíveis ações em que você poderia se concentrar para melhorar a sua consulta, se aplicável.

Mostrar Avisos;

Se a consulta que você usou com EXPLAIN não analisa corretamente, você pode digitar SHOW WARNINGS; em seu MySQL query editor para mostrar informações sobre a última instrução que foi executada, e não era de diagnóstico, por exemplo, não irá mostrar informações para instruções de como SHOW FULL PROCESSLIST;. Embora não possa dar um plano de execução de consulta adequado como EXPLAIN does, ele pode dar-lhe dicas sobre aqueles fragmentos de consulta que poderia processar. Digamos que usamos a consulta EXPLAIN SELECT * FROM foo WHERE foo.bar = 'infrastructure as a service' OR foo.bar = 'iaas'; em qualquer banco de dados dado que não tem realmente uma tabela foo. O MySQL saída seria:

ERROR 1146 (42S02): Table 'db.foo' doesn't exist

Se digitar SHOW WARNINGS; o resultado é o seguinte:

+-------+------+-------------------------------------+| Level | Code | Message |+-------+------+-------------------------------------+| Error | 1146 | Table 'db.foo' doesn't exist |+-------+------+-------------------------------------+1 row in set (0.00 sec)

Vamos tentar fazer isso com uma deliberada erro de sintaxe.

EXPLAIN SELECT * FROM foo WHERE name = ///;

isto gera os seguintes avisos:

> SHOW WARNINGS;+-------+------+---------------------------------------------------------------------+| Level | Code | Message |+-------+------+---------------------------------------------------------------------+| Error | 1064 | You have an error in your SQL syntax; (...) near '///' at line 1 |+-------+------+---------------------------------------------------------------------+

Esta avisos de saída é bastante simples e mostrado pelo MySQL como saída de um resultado imediato, mas para mais complicado consultas que não analisar, ainda é possível dar uma olhada no que acontece nesses consulta de fragmentos que podem ser analisados. SHOW WARNINGS; especiais inclui marcadores que podem fornecer informações úteis, tais como:

• <index_lookup>(query fragment): um índice de pesquisa de aconteceria se a consulta tinha sido devidamente analisado
• <if>(condition, expr1, expr2): uma condição se está ocorrendo nesta parte específica da consulta
• <primary_index_lookup>(query fragment): um índice de pesquisa seria a acontecer através de chave primária
• <temporary table>: uma tabela interna seria criado aqui para salvar resultados temporários, por exemplo, em subconsultas antes de associações

Para saber mais sobre estes marcadores, leia Estendido Explicar o Formato de Saída oficial da documentação do MySQL.

a correção de longo prazo

Existem várias maneiras de corrigir a causa raiz do mau desempenho do banco de dados. O primeiro ponto a ser analisado é o modelo de dados, i.e. como os dados são estruturados e você está usando o banco de dados certo? Para muitos produtos, um banco de dados SQL é muito bom. Uma coisa importante a lembrar é sempre separar os logs de acesso do banco de dados de produção regular, o que infelizmente não acontece em muitas empresas. Principalmente nestes casos, uma empresa começou pequena, cresceu, e essencialmente ainda usa o mesmo banco de dados, o que significa que eles acessam o mesmo banco de dados para tanto funcionalidade de registro, bem como outras transações. Isso reduz significativamente o desempenho global, especialmente à medida que a empresa cresce. Por conseguinte, é muito importante criar um modelo de dados que se adapte e seja sustentável.

modelo de dados

escolher um modelo de dados irá provavelmente revelar a forma certa de banco de dados também. A menos que o seu produto é muito básica, você provavelmente terá várias bases de dados para vários casos de uso – se quase em tempo real, os números de logs de acesso, você provavelmente irá querer um altamente performance data warehouse considerando que regular as transações podem ocorrer através de um banco de dados SQL, e você pode ter um gráfico de banco de dados que acumula os dados relevantes pontos de ambos os bancos de dados em um recommender motor bem.

A arquitetura de software do produto Global é tão importante quanto a própria base de dados, uma vez que o mau design aqui resultará em gargalos que vão para a base de dados e desaceleram tudo tanto do lado do software como do que a base de dados pode produzir. Você terá que escolher se os recipientes são adequadas para o seu produto, seja um monolito é a melhor maneira de lidar com as coisas, se você deseja ter um núcleo monólito com vários microservices segmentação outras funcionalidades espalhadas em outros lugares e como você pode acessar, coletar, processar e armazenar os dados.

Hardware

tão importante quanto a sua estrutura geral, o seu hardware é um componente chave no desempenho da sua base de dados. Exoscale oferece várias opções de instância que você pode usar dependendo de sua transação e volume de armazenamento, bem como o seu tempo de resposta desejado.

é crucial determinar os períodos de pico da sua aplicação e, portanto, saber quando omitir as consultas administrativas mais lentas, se possível. As estatísticas de disco I/O e de rede precisam ser consideradas também quando você projetar o tempo de suas transações de banco de dados e análises.

resumo

Em conclusão, aqui estão os principais pontos para o desempenho a longo prazo resumidos:

• desenvolvimento de modelo de dados que atenda às necessidades de sua empresa
• escolha a forma de banco de dados
• usar uma arquitetura de software que corresponde ao seu produto
• passam através de iterações de olhar para a estrutura de suas consultas e usar EXPLAIN no mais complicada queridos, otimizar o uso do seu banco de dados escolhido(s), também com relação a atualizações de banco de dados e como eles podem afetar
• escolher as instâncias que melhor se adequar ao seu aplicativo de banco de dados e necessidades de acordo com o desempenho e largura de banda