Início / dba / Perguntas / 3480
Accepted
Jeremy Boyd
Asked: 2011-06-25 07:15:11 +0800 CST

O que é mais eficiente, uma cláusula where ou uma junção com mais de um milhão de tabelas de linhas?

  • 772

Executamos um site que tem 250 milhões de linhas em uma tabela e em outra tabela à qual o associamos para a maioria das consultas tem pouco menos de 15 milhões de linhas.

Estruturas de amostra:

MasterTable (Id, UserId, Created, Updated...) -- 15MM Rows
DetailsTable (Id, MasterId, SomeColumn...) -- 250MM Rows
UserTable (Id, Role, Created, UserName...) -- 12K Rows

Regularmente temos que fazer algumas consultas em todas essas tabelas. Uma é obter estatísticas para usuários gratuitos (~ 10 mil usuários gratuitos).

Select Count(1) from DetailsTable dt 
join MasterTable mt on mt.Id = dt.MasterId 
join UserTable ut on ut.Id = mt.UserId 
where ut.Role is null and mt.created between @date1 and @date2

O problema é que essa consulta algumas vezes será executada por muito tempo devido ao fato de as junções acontecerem muito antes do where.

Nesse caso, seria mais sensato usar wheres em vez de joins ou possivelmente where column in(...)?

join best-practices

3 respostas

  1. Best Answer

    Para RDBMS moderno, não há diferença entre "JOIN explícito" e "JOIN-in-the-WHERE" (se todos os JOINS forem INNER) em relação ao desempenho e ao plano de consulta.

    A sintaxe explícita do JOIN é mais clara e menos ambígua (veja os links abaixo)

    Agora, o JOIN-before-WHERE é um processamento lógico , não um processamento real , e os otimizadores modernos são inteligentes o suficiente para perceber isso.

    Seu problema aqui é provavelmente a indexação.

    Por favor, mostre-nos todos os índices e chaves nestas tabelas. E os planos de consulta

    Nota: esta pergunta estaria próxima no StackOverflow por ser uma duplicata até agora... COUNT(1) vs COUNT(*) é outro mito quebrado também.

    • 23

  2. Você tem que refatorar a consulta completamente

    Tente executar as cláusulas WHERE antes e os JOINs depois

    Select Count(1) from DetailsTable dt
join (Select UserId,Id FROM MasterTable where
created between @date1 and @date2) mt on mt.Id = dt.MasterId 
join (Select Id FROM UserTable WHERE Role is NULL) ut
on ut.Id = mt.UserId;

    Mesmo se você executar um plano EXPLAIN nesta consulta refatorada e parecer pior que o original, tente de qualquer maneira. As tabelas temporárias criadas internamente realizarão junções cartesianas, mas essas tabelas são menores para trabalhar.

    Tirei essa ideia deste vídeo do YouTube .

    Experimentei os princípios do vídeo em uma pergunta muito complexa no StackOverflow e recebi uma recompensa de 200 pontos.

    @gbn mencionou garantir que você tenha os índices corretos. Nesse caso, indexe a coluna criada na MasterTable.

    De uma chance !!!

    ATUALIZAÇÃO 2011-06-24 22:31 EDT

    Você deve executar estas consultas:

    SELECT COUNT(1) AllRoles FROM UserTable;
SELECT COUNT(1) NullRoles FROM UserTable WHERE Role is NULL;

    Se NullRoles X 20 < AllRoles (em outras palavras, se NullRoles for menor que 5% das linhas da tabela), você deve criar um índice não exclusivo, o Role em UserTable. Caso contrário, uma tabela completa de UserTable seria suficiente, pois o Query Optimizer pode descartar o uso de um índice.

    ATUALIZAÇÃO 2011-06-25 12:40 EDT

    Como sou um DBA MySQL, meu método de fazer as coisas requer não confiar no MySQL Query Optimizer por meio de pessimismo positivo e ser conservador. Assim, tentarei refatorar uma consulta ou criar índices de cobertura necessários para ficar à frente dos maus hábitos ocultos do MySQL Query Optimizer. A resposta do @gbn parece mais completa, pois o SQL Server pode ter mais "bom juízo" avaliando consultas.

    • 7

  3. Tínhamos uma tabela [Detalhe] com cerca de 75 milhões de linhas; uma tabela [Master] com cerca de 400 mil linhas e uma tabela [Item] relacionada com 7 linhas - sempre e para sempre. Ele armazenava o pequeno conjunto de “números de item” (1-7) e estava modelando um formulário de papel, milhões dos quais eram impressos e distribuídos todos os meses. A consulta mais rápida foi a que você menos provavelmente pensaria primeiro, envolvendo o uso de uma junção cartesiana. IIRC, era algo como:

    SELECT m.order_id, i.line_nr, d.Item_amt
FROM Master m, Item i 
INNER JOIN Detail d ON m.order_id = d.order_id

    Mesmo que haja um link lógico “id” entre [Item] e [Detail], o CROSS JOIN funcionou melhor que o INNER JOIN.

    O RDBMS era o Teradata com sua tecnologia MPP, e o IDR qual era o esquema de indexação. A tabela de 7 linhas não tinha índice, pois TABLE SCAN sempre teve o melhor desempenho.

    • 1

relate perguntas