Início / dba / Perguntas / 333683
Accepted
Shahid Thaika
Asked: 2023-12-03 17:35:00 +0800 CST

A consulta MySQL 8 não prefere índice com maior cardinalidade

  • 772

Eu tenho uma consulta semelhante à seguinte:

FROM example_table
WHERE 
    `date` BETWEEN '2023-11-26' AND '2023-11-28'
    AND location_id IN (3, 4, 6, 7, 8, 10, 11, 12, 14, 18, 19, 22, 23, 24, 28, 29, 30, 31, 32, 36, 39, 40, 41, 43, 45, 46, 48, 49, 50, 51, 52, 54, 55, 56, 57, 59, 60, 61, 62, 68, 69, 75, 121)
    AND ( `type` IS NULL OR ( `type` IN ('type1', 'type2', 'type3') ) )
GROUP BY location_id;

Meu entendimento é que, ao criar um índice multicoluna, a coluna com maior cardinalidade/seletividade vai primeiro. Tentei testar o desempenho com duas chaves de índice:

  1. (data, location_id, tipo, valor)
  2. (location_id, data, tipo, valor)

Na minha tabela real, tenho 11.833 valores exclusivos na coluna de data e apenas 99 em location_id. Atualmente, existem mais de 63 milhões de linhas.

No entanto, o MySQL 8 prefere usar aquele que começa com location_id. Mesmo quando tento FORCE INDEXe EXPLAIN ANALYZE, ele mostra um custo/tempo maior daquele que começa com date.

O que poderia estar acontecendo?

EDITAR:

EXPLICAR ANÁLISE:

  1. data primeiro índice
    -> Group aggregate: sum(ledger_entries.amount_cents)  (cost=1897 rows=6236) (actual time=0.167..4.67 rows=43 loops=1)
        -> Filter: ((ledger_entries.`date` = DATE'2023-11-28') and (ledger_entries.location_id in (3,4,6,7,8,10,11,12,14,18,19,22,23,24,28,29,30,31,32,36,39,40,41,43,45,46,48,49,50,51,52,54,55,56,57,59,60,61,62,68,69,75,121)) and ((ledger_entries.`type` is null) or (ledger_entries.`type` in ('Procedure','Adjustment','AncillarySale'))))  (cost=1273 rows=6236) (actual time=0.0221..4.09 rows=6192 loops=1)
            -> Covering index range scan on ledger_entries using index_le_date_location_type_amount_cents over (date = '2023-11-28' AND location_id = 3 AND type = NULL) OR (date = '2023-11-28' AND location_id = 3 AND type = 'Adjustment') OR (170 more)  (cost=1273 rows=6236) (actual time=0.02..2.83 rows=6192 loops=1)
  1. primeiro índice de localização
    -> Group aggregate: sum(ledger_entries.amount_cents)  (cost=1888 rows=6236) (actual time=0.171..4.74 rows=43 loops=1)
        -> Filter: ((ledger_entries.`date` = DATE'2023-11-28') and (ledger_entries.location_id in (3,4,6,7,8,10,11,12,14,18,19,22,23,24,28,29,30,31,32,36,39,40,41,43,45,46,48,49,50,51,52,54,55,56,57,59,60,61,62,68,69,75,121)) and ((ledger_entries.`type` is null) or (ledger_entries.`type` in ('Procedure','Adjustment','AncillarySale'))))  (cost=1265 rows=6236) (actual time=0.0244..4.15 rows=6192 loops=1)
            -> Covering index range scan on ledger_entries using ledger_entries_location_date_type_amount_cents over (location_id = 3 AND date = '2023-11-28' AND type = NULL) OR (location_id = 3 AND date = '2023-11-28' AND type = 'Adjustment') OR (170 more)  (cost=1265 rows=6236) (actual time=0.022..2.91 rows=6192 loops=1)
mysql

2 respostas

  1. GROUP BY location_id.

    • Se o índice escolhido começar com location_id, o processamento poderá passar pelo índice,

    • Caso contrário, será necessário haver uma tabela temporária e uma classificação.

    O Otimizador não possui informações suficientes para saber com certeza qual plano de execução seria realmente mais rápido, mas os itens acima são os melhores com os quais ele pode trabalhar.

    Se você quiser discutir isso mais detalhadamente, forneça SHOW CREATE TABLEe o arquivo EXPLAIN ANALYZEs.

    • 2
  2. Best Answer

    Meu entendimento é que, ao criar um índice multicoluna, a coluna com maior cardinalidade/seletividade vai primeiro.

    Isso faz sentido se você também quiser reutilizar esse índice com consultas que não usam todas as colunas indexadas. Se você tiver um índice em (a,b,c), ele também servirá como índice em (a,b) e (a) gratuitamente. Esses índices "livres" são muito mais úteis se (a) e/ou (a,b) tiverem boa seletividade (alta cardinalidade). Caso contrário, se (a) tiver baixa cardinalidade, então um índice apenas em (a) será inútil.

    Agora sua consulta faz:

    date BETWEEN date1 and date2
AND location_id IN (big list)
GROUP BY location_id

    Com um índice btree em (location_id,date) é bem simples, o algo seria assim:

    for loc_id in (big_list):
    range query on (location_id,date) BETWEEN (loc_id,date1) AND (loc_id,date2)

    Um índice btree em (a,b,c) é ordenado por (a,b,c), portanto, suporta consultas de intervalo em qualquer subconjunto de colunas, desde que sejam (a), (a,b) ou (a, b,c). Mas não qualquer outra combinação ou qualquer outra ordem.

    Hmm... Agora tenho que explicar a ordem das tuplas... É como ordenar por (sobrenome,nome). Nesse caso, uma consulta de intervalo em (location_id,date) entre (1,'2022-01-02') e (1,'2022-01-04') selecionará estas linhas:

    loc   date
-----------
1     2023-01-01
1     2023-01-02 <-range start and read next
1     2023-01-03 <-read next
1     2023-01-04 <-read and stop
2     2023-01-01
2     2023-01-02
2     2023-01-03
2     2023-01-04

    ... tudo o que faz é encontrar a primeira linha do intervalo e depois ler as linhas do índice em ordem até o final do intervalo, o que é muito rápido. Então você tem uma pesquisa de índice por loc_id e depois lê o intervalo. Como bônus, os dados já estão ordenados por location_id, portanto, não é necessário fazer nenhum trabalho extra para o grupo. Parece bom.

    Com um índice btree ativado (date,location_id) , é muito mais complicado. Vamos pegar os dados anteriores e fazer um índice ordenado novamente.

    date         loc
----------------
2023-01-01   1
2023-01-01   2
2023-01-02   1
2023-01-02   2
2023-01-03   1
2023-01-03   2
2023-01-04   1
2023-01-04   2

    O problema aqui é que as colunas do índice são trocadas, mas a consulta de intervalo ainda está na mesma coluna de antes. Ainda é a data. Se você tiver um índice em (data, loc), ele poderá fazer uma consulta de intervalo com eficiência em (data), mas o índice não filtrará por loc. Isso deve ser feito depois de ler as linhas do índice. Vamos fazer a consulta do intervalo de datas entre '2022-01-02' e '2022-01-04':

    date         loc
----------------
2023-01-01   1
2023-01-01   2
2023-01-02   1 <- range start and read next
2023-01-02   2 <- read next
2023-01-03   1 <- read next
2023-01-03   2 <- read next
2023-01-04   1 <- read next
2023-01-04   2 <- read and stop

    Portanto, ele irá verificar e ler muitas linhas com location_id's que não estão na sua (grande lista) e depois jogá-las fora. Isso ainda é bom se o intervalo de datas for pequeno, é melhor ler 1% da tabela e jogar fora a maior parte do que não ter o índice, ler a tabela inteira e também jogar fora a maior parte para acabar com o mesmo resultado.

    Além disso, as linhas resultantes não são ordenadas por location_id, portanto o agrupamento por precisa de trabalho extra.

    Portanto a escolha do plano de consulta é lógica.

    Os índices também podem ser usados ​​para evitar a classificação, portanto, um índice em (loc,data) otimizará "WHERE loc=... AND data BETWEEN... ORDER B data", mas não funcionará se a consulta tiver "loc IN (...)" porque então ele realmente leria vários pedaços em ordem de data, mas ainda teria que classificar todo o resultado.

    • 0

relate perguntas