Início / coding / Perguntas / 79540674
Accepted
Alan Chen
Asked: 2025-03-28 14:20:30 +0800 CST

Qual coluna indexar na indexação de várias colunas?

  • 772
ID do usuário Primeiro Meio Durar Tipo Criado em
123 John Henrique Corça Mago 28/03/2025

Digamos que eu tenha a seguinte tabela acima. Gostaria de criar um índice para ajudar a acelerar minhas consultas.

Todas as consultas devem ser do tipo:

Select *
from users
where Type = 'SomeType'
  and First = 'SomeName1'
Order by CreatedAt DESC;

Select *
from users
where Type = 'SomeType'
  and Middle = 'SomeName2'
Order by CreatedAt DESC;

Select *
from users
where Type = 'SomeType'
  and Last = 'SomeName3'
Order by CreatedAt DESC;

Como eu indexaria as colunas para tornar as consultas eficientes? Seria CreatedAta primeira da coluna indexada?

Estou pensando

CREATE INDEX idx_users on users(CreatedAt, Type, First, Middle, Last)

CreatedAte typesempre seria usado, enquanto primeiro, meio e último variam.

sql

3 respostas

  1. Best Answer

    O índice que você propôs pode não ser útil:

    CREATE INDEX idx_users on users(CreatedAt, Type, First, Middle, Last)

    O fato de você estar selecionando e ordenando por CreatedAtnão significa que ele seja necessário como uma chave de índice. No meu teste em 400 mil amostras , nenhuma das três consultas que você mostrou acabou usando-o. Todas acabaram executando uma varredura sequencial que levou acima de 150ms.

    Se você simplesmente remover createdatdo índice, eles ficarão felizes em usá-lo para uma varredura de índice de bitmap abaixo 5ms. Você pode adicionar mais ajustes dependendo de quanta latência de entrada você está pronto para tolerar e quanto espaço você está disposto a gastar para acomodar os índices.

    Aqui está uma comparação do espaço de armazenamento que eles ocupam em 200 mil linhas, com tempos de consulta em ms em média em 45 chamadas, mais o tamanho do índice e quanto mais espaço você precisa para ele, em % em comparação com o tamanho da tabela base:
    demonstração em db<>fiddle

    variante consulta1 consulta2 consulta3 tamanho dos índices %tamanho
    (Type,First,Middle,Last) include(createdat,userid); 0,159 0,148 0,148 35 MB 104
    (Type,First,CreatedAt) INCLUDE(Middle,Last,UserID);
    (Type,Middle,CreatedAt) INCLUDE(First,Last,UserID);
    (Type,Last,CreatedAt) INCLUDE (First,Middle,UserID);
    charlieface    		 0,172 0,165 0,161 108 MB 325
    (Type,First,Middle,Last); 0,197 0,187 0,182 33 MB 98
    (Type,first);
    (Type,middle);
    (Type,last);    		 0,196 0,191 0,179 4912 KB de dados 14
    (first,type,createdat desc);
    (middle,type,createdat desc);
    (last,type,createdat desc);
    Thorsten Kettner    		 0,214 0,210 0,202 53 MB 161
    (Type) include(First,Middle,Last, UserID,CreatedAt); 2.712 2.689 2.399 34 MB 103
    (Type); 5.098 5.024 4.627 1456 KB de dados 4
    (CreatedAt,Type,First,Middle,Last); 77.073 76.153 76.333 32 MB 97
    • 3

  2. Indexação é sobre seletividade. Se houvesse um índice, e uma consulta selecionasse 50% das linhas na tabela, por exemplo, então seria uma má ideia para o DBMS usar o índice, pois isso daria muito trabalho comparado a apenas ler todas as linhas da tabela sequencialmente.

    Há duas colunas em cada cláusula where. Qual das duas é mais seletiva? Vamos dar uma olhada na primeira consulta: eu esperaria que houvesse mais nomes distintos do que tipos, então o primeiro nome seria a primeira coluna no índice e o tipo seria apenas a segunda. Então, um índice para a primeira consulta ficaria assim:

    create index idx_1 on users (first, type);

    Como você está usando apenas comparação de igualdade (Type = ... , First = ...), você atingirá uma posição no índice onde encontrará todas as correspondências. Isso nos permite adicionar a coluna CreatedAt ao índice, de modo que todas essas correspondências já estariam classificadas:

    create index idx_1 on users (first, type, createdat desc);

    Para as outras duas consultas você teria:

    create index idx_2 on users (middle, type, createdat desc);
create index idx_3 on users (last, type, createdat desc);

    Então, você precisa de três índices diferentes para as três consultas.

    Se você quisesse fornecer apenas um único índice para todas as consultas, indexaria o tipo, porque esta é a coluna na cláusula where que todas as três consultas têm em comum. Você poderia adicionar as outras colunas, para fornecê-las antes que a tabela fosse lida, mas duvido que o DBMS usaria o índice, de qualquer forma, porque não presumo que o tipo seja seletivo o suficiente. Uma consulta pode se beneficiar, ou seja, aquela em que a segunda coluna indexada corresponderia à coluna na cláusula where, mas é isso. Portanto, fornecer apenas um índice não é uma opção promissora. Você deve fornecer três índices, um por consulta.

    • 1

  3. Esse índice não é útil, assim CreatedAtcomo a primeira coluna, o que significa que todas as outras colunas não podem ser buscadas.

    Além disso:

    • Como não "cobre" totalmente a consulta, que usa select *. Você precisaria de todas as outras colunas em um include.
    • Middlee Lastnão estão ajudando você por estarem na chave do índice, pois estão depois de First. Então esse índice (mesmo que estivesse cobrindo) só ajudará na primeira consulta.

    Então você precisa de três índices separados.

    (Type, First, CreatedAt) INCLUDE (Middle, Last, UserID)
(Type, Middle, CreatedAt) INCLUDE (First, Last, UserID)
(Type, Last, CreatedAt) INCLUDE (First, Middle, UserID)

    Você pode ver no violino que isso lhe dá uma varredura somente de índice em todas as três consultas.

    db<>violino

    • 0

relate perguntas