Início / dba / Perguntas / 326150
Accepted
Elikill58
Asked: 2023-04-19 04:15:11 +0800 CST

Como corrigir o problema de desempenho com row_number e vários esquemas?

  • 772

Depois de receber a resposta para Como posso obter um contador de classificação válido? , adaptei ao meu próprio sistema. Mas agora, eu tenho um problema de desempenho. Todas as minhas solicitações são muito rápidas (menos de 0,0005s para a maioria delas), mas ao usar ROW_NUMBER()com vários esquemas, demora mais de 0,2s.

Aqui está um exemplo completo:

Esquema 1 , nomeado sanctions, com uma tabela nomeada banse composta por:

  • id, campo de incremento automático
  • uuid, varchar com índice
  • outros conteúdos não vinculados à pergunta

Esta tabela atual tem mais de 400 linhas.

Esquema 2 , denominado stats, com uma tabela nomeada playerse composta por:

  • id, campo de incremento automático
  • uuid, varchar com índice
  • coins, dobro
  • outros conteúdos não vinculados à pergunta

Esta tabela atual tem mais de 2000 linhas.

Minha consulta completa é assim :

SELECT
   uuid,
   (SELECT count(*) FROM sanctions.bans WHERE uuid = p.uuid) as nb,
   row_number() OVER (order by coins DESC) counter
FROM stats.players p;

Demora cerca de 0,22s.

Agora, vamos verificar parte por parte :

Ao executar SELECT count(*) FROM arkbans.litebans_bans WHERE uuid = p.uuid(e substituindo p.uuidpor um valor), nunca vou além de 0,0002s.

Ao executar:

SELECT
   uuid,
   row_number() OVER (order by coins DESC) counter
FROM stats.players p;

Demora cerca de 0,0017s.

Com ANALYZEchave:

insira a descrição da imagem aqui

Com ANALYZE FORMAT=JSON: aqui

Consulta analisada:

WITH Bans AS
(
    SELECT uuid, COUNT(*) AS BanCount
    FROM sanctions.bans
    GROUP BY uuid
)
 
SELECT
   p.uuid,
   COUNT(b.BanCount) as nb,
   row_number() OVER (order by MAX(p.coins) DESC) counter
FROM stats.players p
LEFT JOIN Bans b ON p.uuid = b.uuid

Como posso corrigir esse problema de desempenho?

Nota: a coluna "moedas" aqui é um exemplo. Na realidade, mais de 60 colunas usarão essa solicitação. Portanto, adicionar índice para cada coluna como esta não é uma opção para mim (muitos índices, com valores muito diferentes)

Nota 2: Não é possível fazer um db fiddle, pois está em vários bancos de dados e com muitos dados, desculpe.

query-performance

1 respostas

  1. Best Answer

    Ao executar SELECT count(*) FROM arkbans.litebans_bans WHERE uuid = p.uuid(e substituindo p.uuidpor um valor), nunca vou além de 0,0002s.

    Sim, executá-lo para um único uuidé de apenas 0,0002s, mas quantas linhas / uuids existem na sua stats.playerstabela? E se você multiplicar essa contagem por 0,0002s, quanto tempo dura o tempo de execução total agora? Isso é efetivamente o que você está fazendo quando tem uma expressão embutida em sua SELECTlista que filtra uma determinada linha da tabela externa. (Não é exatamente a mesma coisa, mas perto o suficiente para você ter uma ideia.)

    Você deve escrever sua consulta de uma forma mais relacional, com um real JOIN, assim:

    WITH Bans AS
(
    SELECT uuid, COUNT(1) AS BanCount
    FROM sanctions.bans
    GROUP BY uuid
)

SELECT
   p.uuid,
   IFNULL(b.BanCount, 0) as nb,
   row_number() OVER (order by p.coins DESC) counter
FROM stats.players p
LEFT JOIN Bans b
    ON p.uuid = b.uuid;

    Você pode achar esta reescrita ainda mais eficaz, juntando diretamente e agrupando os resultados:

    SELECT
   p.uuid,
   COUNT(b.BanCount) as nb,
   row_number() OVER (order by MAX(p.coins) DESC) counter
FROM stats.players p
LEFT JOIN sanctions.bans b
    ON p.uuid = b.uuid
GROUP BY p.uuid;

    Conforme mencionado nos comentários, um índice na (uuid, coins)tabela stats.playerspode ser mais adequado para o seu tipo de consulta.

    Você ANALYZE FORMAT=JSONestá mostrando que a maior parte do tempo é gasto em sanctions.bans. Especificamente esta linha é interessante:

    "attached_condition": "trigcond(stats.p.uuid = convert(b.uuid using utf8mb4))"

    Isso convert(b.uuid using utf8mb4)me indica que sua uuidcoluna na sanctions.banstabela é um conjunto de caracteres diferente daquele da stats.playerstabela. Isso é chamado de conversão implícita e pode causar problemas de desempenho. Intuitivamente, sinto que esse é o seu gargalo. Verifique e certifique-se de que ambos os campos são os mesmos conjuntos de caracteres (e agrupamentos).

    • 2

relate perguntas