LeetCode mais difícil SQL 185: A melhor solução ainda é lenta?

LEETCODE SQL Optimization: Funções de janela vs. desenvolvedores de subconsivos que frequentemente usam o leetcode certamente estão familiarizados com o problema 185: os três melhores salários do departamento, uma pergunta clássica incluída no topo SQL 50. O funcionário fornece duas soluções de referência, que podem ser encontradas em: solução oficial. Essas duas abordagens desencadearam extensas discussões na seção de comentários do LEETCODE. Vamos realizar um teste prático para comparar as diferenças entre essas duas respostas corretas nos cenários de dados do mundo real. Soluções oficiais Soluções Solução 1: Abordagem de subconstração (método tradicional) Selecione D.Nome como ‘Departamento’, E1.Name como ‘Empregado’, E1. Salário como ‘Salário’ do Empregado E1 Participação do Departamento D em E1.Departmentid = D.id, onde 3> (contagem seleta (distinta E2.Salary) do funcionário e2 onde o e2.. Digite o modo de saída do modo de tela cheia. Ele calcula o número de funcionários no mesmo departamento com salários mais altos do que o funcionário atual. Se a contagem for menor que 3, o registro será retido. Solução 2: Abordagem da função de janela (método moderno) com funcionário_departamento como (selecione d.id, d.name como departamento, salário como salário, e.name como funcionário, dense_rank () (partição por d.id order by salary desc) como rnk do departamento D ingressar em funcionários e em d.id = e.dartmentid) seleção, funcionário, funcionário do funcionário; Digite o modo de saída do modo de tela cheia. Construir o ambiente de teste Deixe a IA ajudar a gerar scripts: tabela: funcionário+————–+———+| Nome da coluna | Tipo | +—————-+———+| id | int | | nome | varchar | | salário | int | | DepartamentoId | int | +————–+———+ID é a chave primária para esta tabela. DepartamentId é uma chave estrangeira referenciando a coluna ID da tabela de departamento. Cada linha indica o ID, nome, salário e ID do departamento de um funcionário. Tabela: Departamento+————-+———+| Nome da coluna | Tipo | +————-+———+| id | int | | nome | varchar | +————-+———+ID é a chave primária para esta tabela. Cada linha indica o ID e o nome de um departamento. Digite declarações de criação da tabela de tabela de tabela de tabela de tabela de tela cheia de tela cheia: – Crie o departamento de tabela de departamento Criar departamento de tabela (ID int Primary Key, Nome Varchar (100)); – Crie a tabela de funcionários Criar Table Funcionário (Id int Primary Key, Nome Varchar (100), Salário Int, Departamento Int, Departamento de Referências de Chave Espanha (Departamento) (ID)); Digite o modo de saída do modo de tela completa Procedimento armazenado em tela cheia: Este procedimento armazenado cria 10 departamentos e insere 1.000 funcionários em cada departamento, resultando em um total de 10.000 registros de funcionários. Delimiter $$ CREATE PROCEDIMENTO GERETE_TEST_DATA () BEGN Declare dept_id int padrão 1; Declarar emp_id int padrão 1; Declarar eu int; Declarar j int; Set i = 1; Enquanto i <= 10 inserir no departamento (id, nome) valores (i, concat (‘dept_’, i)); Set i = i + 1; Fim enquanto; Set i = 1; Enquanto i <= 10 do set j = 1; Enquanto j <= 1000 insira em funcionários (id, nome, salário, departamento) valores (EMP_ID, concat (‘funcionário_’, emp_id), piso (3000 + rand () * 7000), i); Set emp_id = emp_id + 1; Set j = j + 1; Fim enquanto; Set i = i + 1; Fim enquanto; END $$ delimitador; Digite o modo de saída do modo de tela cheia de tela cheia Executar o procedimento armazenado: CALL GERETE_TEST_DATA (); Digite os resultados da comparação de desempenho do modo de tela cheia de tela cheia: a diferença de desempenho acabou sendo significativa. O SQL usando dense_rank () executado em milissegundos, enquanto a versão de subconsulta levou mais de 5 segundos, o que é inaceitável. Por que usar funções de janela? A partir da versão 8.0 do MySQL, foram introduzidas funções de janela, aprimorando bastante os recursos de análise complexos no SQL. Comparados aos métodos tradicionais, como subconsignidades ou tabelas temporárias, as funções de janela oferecem as seguintes vantagens: Evite repetições e vários cálculos: métodos tradicionais como subconsências geralmente requerem várias varreduras de tabela, especialmente para cálculos como classificação ou somas cumulativas, que podem levar a gargalos de desempenho. As funções da janela completam vários cálculos em uma única varredura. Reduza os resultados intermediários e as despesas gerais da tabela temporária: as subconsirias ou as junções geralmente geram conjuntos de resultados intermediários, potencialmente exigindo tabelas temporárias ou mesas de trabalho, aumentando o uso de memória e disco. Evite materialização desnecessária: as funções da janela compartilham os resultados da computação sob a mesma partição e ordem por condições, evitando cálculos repetidos. A otimização do SQL com SQLFlash, embora haja diferenças na sintaxe do SQL, os resultados permanecem consistentes. Parece que o SQLFlash pode lidar facilmente com isso! Resumo e recomendações priorizam as funções da janela: No MySQL 8.0+ ou nos bancos de dados que suportam funções da janela, elas são a escolha superior. Para uma análise mais detalhada no SQL Server, consulte meu artigo anterior: Otimização de desempenho do servidor SQL: funções de janela para dobrar sua eficiência de consulta

Fonte