Início / dba / Perguntas / 329517
Accepted
bfredo123
Asked: 2023-07-21 14:14:53 +0800 CST

Como otimizar SELECT COUNT com LEFT JOIN?

  • 772

Tenho um banco de dados MariaDB 10.5.10, com uma tabela activitycomposta por 150.000 linhas. ele contém:

  • um idcampo (inteiro, chave primária)
  • a tenant. Existem cerca de 85.000 linhas com inquilino = 12
  • an activity_type(inteiro, atuando como uma chave para outra tabela activity_type)

Outra tabela, activity_type, tem 6 linhas, com um id(chave primária, inteiro)

Essa consulta, que é gerada a partir de um ORB (e que praticamente não posso modificar) requer cerca de 4 a 5 segundos para ser executada e gostaria de fazê-la funcionar mais rápido:

select count(activity.id) from activity
   left join activity_type on activity.type = activity_type.id
where tenant = 12 ;

Quando removo a left joinlinha, ela roda muito mais rápido, cerca de 0,7 segundos.

Usando explain, posso ver que sem left join, a extracoluna menciona using index(na verdade, o índice da tenantcoluna):

explain select SQL_NO_CACHE count(activity.id) from activity
where tenant = 12;

insira a descrição da imagem aqui

Mas com left joina extracoluna está vazia:

explain select SQL_NO_CACHE count(activity.id) from activity
  left join activity_type on activity.type = activity_type.id
where tenant = 12;

insira a descrição da imagem aqui

E com a cláusula left joinbut NO where, também mostra Using index:

explain select SQL_NO_CACHE count(activity.id) from activity
  left join activity_type on activity.type = activity_type.id;

insira a descrição da imagem aqui

Você poderia me ajudar a melhorar o desempenho quando as instruções LEFT JOINe as WHEREestão presentes?

Observe que:

  • o LEFT JOINnão adiciona nenhuma linha aos resultados, porque há apenas uma activity_typelinha por atividade (também tentei adicionar um UNIQUEíndice no activity_type.id, mas não muda nada no desempenho)
  • porém preciso da LEFT JOINdeclaração, pois em alguns casos, os usuários podem querer adicionar mais critérios (na wherecláusula) relacionados aos campos da activity_typetabela.

Muito obrigado!

performance

2 respostas

  1. Best Answer

    Adicione o seguinte índice à activitytabela

    alter table activity add index tp_ten(type,tenant);

    Consulte índices compostos (compostos)

    • 1

  2. Eu preciso da instrução LEFT JOIN, porque em alguns casos, os usuários podem querer adicionar mais critérios (na cláusula where) relacionados aos campos na tabela activity_type.

    Não, você não. Construa a consulta desejada com base nos critérios que o usuário fornecer a você. Parece que AND activity_type = '...'é o único caso em que a LEFT JOINcláusula é necessária.

    Outras dicas:

    • Diga COUNT(*)em vez de COUNT(x)a menos que você precise verificar x IS NOT NULL.
    • Por favor, forneça SHOW CREATE TABLEpara que possamos saber exatamente quais índices, tipos de dados, etc, estão nas tabelas.
    • O MariaDB (mas não o MySQL) é inteligente o suficiente para se livrar de qualquer um LEFT JOINque não seja realmente necessário.

    Os EXPLICA:

    1. No InnoDB, o PK é anexado implicitamente a qualquer índice secundário. Isso significa que está "Usando índice"(tenent, id)
    2. O "Extra" vazio provavelmente se deve ao pensamento de que precisa type.
    3. O PRIMARY KEY(id)'agrupado' como um "índice" para os dados. Portanto, "Usar índice" neste caso significa "varredura de tabela".

    Confuso? Eu não culpo você.

    Eu usaria uma string curta para "tipos" e a incluiria como uma coluna em activity. Isso eliminaria o JOIN(a menos que haja algo mais de interesse na tabela 'types'). Mas então, você pode precisar de INDEX(tenant, type), especialmente para a versão simplificada da consulta original.

    • 0

relate perguntas