Início / dba / Perguntas / 72358
Accepted
Fake Name
Asked: 2014-07-25 22:25:29 +0800 CST

Melhorando o desempenho da consulta `GROUP BY` no sqlite3

  • 772

Eu tenho um pequeno aplicativo da web que está usando sqlite3 como banco de dados (o banco de dados é bem pequeno).

No momento, estou gerando algum conteúdo para exibir usando a seguinte consulta:

SELECT dbId,
        dlState,
        retreivalTime,
        seriesName,
        <snip irrelevant columns>
        FROM DataItems
        GROUP BY seriesName
        ORDER BY retreivalTime DESC
        LIMIT ?
        OFFSET ?;

Onde limitnormalmente é ~200 e offseté 0 (eles acionam um mecanismo de paginação).

De qualquer forma, agora, essa consulta está acabando completamente com meu desempenho. Leva aproximadamente 800 milissegundos para executar em uma tabela com aproximadamente 67 mil linhas.

Eu tenho índices em ambos seriesNamee retreivalTime.

sqlite> SELECT name FROM sqlite_master WHERE type='index' ORDER BY name;
<snip irrelevant indexes>
DataItems_seriesName_index
DataItems_time_index           // This is the index on retreivalTime. Yeah, it's poorly named

No entanto, EXPLAIN QUERY PLANparece indicar que eles não estão sendo usados:

sqlite> EXPLAIN QUERY PLAN SELECT dbId, 
                                  dlState, 
                                  retreivalTime, 
                                  seriesName 
                                  FROM 
                                      DataItems 
                                  GROUP BY 
                                      seriesName 
                                  ORDER BY 
                                      retreivalTime 
                                  DESC LIMIT 200 OFFSET 0;
0|0|0|SCAN TABLE DataItems
0|0|0|USE TEMP B-TREE FOR GROUP BY
0|0|0|USE TEMP B-TREE FOR ORDER BY

O índice seriesNameé COLLATE NOCASE, se isso for relevante.

Se eu soltar o GROUP BY, ele se comportará conforme o esperado:

sqlite> EXPLAIN QUERY PLAN SELECT dbId, dlState, retreivalTime, seriesName FROM DataItems ORDER BY retreivalTime DESC LIMIT 200 OFFSET 0;
0|0|0|SCAN TABLE DataItems USING INDEX DataItems_time_index

Basicamente, minha suposição ingênua seria que a melhor maneira de executar essa consulta seria voltar do último valor em retreivalTime, e toda vez que um novo valor seriesNamefor visto, anexá-lo a uma lista temporária e, finalmente, retornar esse valor. Isso teria um desempenho um tanto ruim para casos em que OFFSETé grande, mas isso acontece muito raramente neste aplicativo.

Como posso otimizar esta consulta? Posso fornecer as operações de consulta bruta, se necessário.

O desempenho de inserção não é crítico aqui, portanto, se eu precisar criar um ou dois índices adicionais, tudo bem.

Meus pensamentos atuais são um gancho de confirmação que atualiza uma tabela separada usada para rastrear apenas itens exclusivos, mas isso parece um exagero.

performance optimization

2 respostas

  1. Um índice pode ser usado para otimizar o GROUP BY, mas se o ORDER BY usar colunas diferentes, a classificação não pode usar um índice (porque um índice ajudaria apenas se o banco de dados pudesse ler as linhas da tabela na ordem de classificação ).

    Um índice COLLATE NOCASE não ajuda se você usar um agrupamento diferente na consulta. Adicione um índice 'normal' ou use GROUP BY seriesName COLLATE NOCASE, se for permitido.

    Usar a cláusula OFFSET para paginação não é muito eficiente, porque o banco de dados ainda precisa agrupar e classificar todas as linhas antes de poder passar por cima delas. Melhor usar um cursor de rolagem .

    Observação: não há garantia de que os valores dbIde dlStatevenham de qualquer linha específica; O SQLite permite colunas não agregadas em uma consulta agregada apenas para compatibilidade de bug com o MySQL.

    • 5
  2. Best Answer

    Aqui está uma sugestão: adicione um índice (seriesName, retreivalTime)e tente esta consulta. Não será super rápido, mas provavelmente mais eficiente do que o que você tem:

    SELECT d.dbId,
       d.dlState,
       d.retreivalTime,
       d.seriesName,
        <snip irrelevant columns>
FROM DataItems AS d
  JOIN
    ( SELECT seriesName, 
             MAX(retreivalTime) AS max_retreivalTime
      FROM DataItems
      GROUP BY seriesName
      ORDER BY max_retreivalTime DESC
      LIMIT ?
      OFFSET ?
    ) AS di
    ON  di.seriesName = d.seriesName
    AND di.max_retreivalTime = d.retreivalTime
ORDER BY di.max_retreivalTime ;

    Ou (variação) usando o PK também, com index on (seriesName, retreivalTime, dbId)e query:

    SELECT d.dbId,
       d.dlState,
       d.retreivalTime,
       d.seriesName,
        <snip irrelevant columns>
FROM DataItems AS d
  JOIN
    ( SELECT dbId
      FROM DataItems
      GROUP BY seriesName
      ORDER BY MAX(retreivalTime) DESC
      LIMIT ?
      OFFSET ?
    ) AS di
    ON  di.dbId = d.dbId
ORDER BY d.max_retreivalTime ;

    A lógica por trás das consultas é usar apenas o índice para os cálculos da tabela derivada (encontrar o máximo (tempo de recuperação) para cada seriesName e, em seguida, ordenar e fazer o limite de deslocamento).

    Então a própria tabela estará envolvida apenas para buscar as 200 linhas que serão exibidas.

    • 4

relate perguntas