Início / dba / Perguntas / 342823
Accepted
Sidharth Samant
Asked: 2024-10-06 13:41:51 +0800 CST

MySQL usando um índice multicoluna mesmo quando a primeira coluna não está sendo consultada

  • 772

Tenho o MySQL versão 8.0.37.

Pelo que entendi sobre um índice de múltiplas colunas nesta versão, ele será usado pelo MySQL SOMENTE se a consulta contiver um subconjunto de todas as colunas, começando pela primeira.

Por exemplo, tenho esse índice na minha tabela InnoDB

mysql> show indexes from my_table;
+------------------------+------------+------------------------------------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name                        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------------------+------------+------------------------------------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| my_table |          0 | PRIMARY                         |            1 | id          | A         |       32643 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| my_table |          1 | my_table_entity_id              |            1 | entity_id   | A         |       20160 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| my_table |          1 | my_table_entity_id_sub_id_value |            1 | entity_id   | A         |       18222 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| my_table |          1 | my_table_entity_id_sub_id_value |            2 | sub_id      | A         |       32985 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| my_table |          1 | my_table_entity_id_sub_id_value |            3 | value       | A         |       32545 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+------------------------+------------+------------------------------------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

Com my_table_entity_id_sub_id_valueo índice, posso executar consultas em entity_id, ou em ambos entity_ide sub_idou em todas as 3 colunas. Isso também é o que a documentação do MySQL diz.

Entretanto, esta é a explain analyzesaída de uma consulta apenas na 2ª e 3ª colunas, ou seja, sub_ide value, e ainda assim o índice está sendo usado.

mysql> explain analyze select distinct entity_id from my_table where sub_id = 107 and value  = 'd90e7a26-2fc5-4e16-87c5-a2e9da5a26f7';
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Group (no aggregates)  (cost=3552 rows=330) (actual time=3.52..14.7 rows=3103 loops=1)
    -> Filter: ((my_table.`value` = 'd90e7a26-2fc5-4e16-87c5-a2e9da5a26f7') and (my_table.sub_id = 107))  (cost=3519 rows=330) (actual time=3.44..14.3 rows=3103 loops=1)
        -> Covering index scan on my_table using my_table_entity_id_sub_id_value  (cost=3519 rows=32985) (actual time=0.0741..10.4 rows=33202 loops=1)
 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.39 sec)

Percebo que é uma varredura de "índice de cobertura". O que entendo sobre elas é que são usadas para recuperar valores diretamente do índice, então entendo entity_idque o que estou selecting está naquele índice. No entanto, o whereainda está apenas na 2ª e 3ª colunas, e esse é o critério de filtragem.

Estou esquecendo de algo aqui? O que não estou entendendo sobre cobrir varreduras de índice?

mysql

4 respostas

  1. Best Answer

    Como sua consulta não faz referência à coluna mais à esquerda do índice, ela executa uma varredura de índice de cobertura , o que significa que, embora ela consiga ler apenas o índice, ela deve ler todas as entradas desse índice.

    Se você incluir a coluna mais à esquerda do índice nas condições da consulta, obterá uma pesquisa de índice , que permite que a consulta examine apenas as entradas correspondentes.

    mysql> explain analyze select distinct entity_id from my_table where entity_id = 123 and sub_id = 107 and value  = 'd90e7a26-2fc5-4e16-87c5-a2e9da5a26f7'\G

*************************** 1. row ***************************
EXPLAIN: -> Group (no aggregates)  (cost=0.45 rows=1) (actual time=0.0187..0.0187 rows=0 loops=1)
    -> Filter: ((my_table.sub_id = 107) and (my_table.`value` = 0))  (cost=0.35 rows=1) (actual time=0.0177..0.0177 rows=0 loops=1)
        -> Index lookup on my_table using entity_id (entity_id=123)  (cost=0.35 rows=1) (actual time=0.017..0.017 rows=0 loops=1)
                 ^^^^^^
    • 8

  2. "Covering" (também conhecido como "Using index") significa que todas as colunas são encontradas em um único INDEX. Isso evita o vai e vem entre o BTree de índice e o BTree de dados, o que é um benefício de desempenho.

    A citação que você encontrou está incompleta. Ela menciona apenas as situações ótimas em que "cobrir" é benéfico. (Veja meu terceiro item abaixo.)

    Eu esperaria o seguinte:

    • Observe cada 'linha' no índice ("Análise de índice de cobertura")
    • Não toque nos dados do BTree.
    • Verifique cada linha de índice para value= 'd90e7a26...' e sub_id = 107`
    • Reúna os entity_idvalores do índice BTree.
    • Possivelmente está percebendo que o índice é ordenado por entity_id, e, portanto, pode evitar uma passagem de de-dup. Mas não consigo ler o Explain bem o suficiente para deduzir isso.

    Pontas:

    • INDEX(entity_id)é redundante com o outro índice e pode ser removido.

    • Se o par entity_id, sub_idfor único e você não tiver outra utilidade idalém de ser o PK, então livre-se dele ide tenhaPRIMARY KEY(entity_id, sub_id)

    • O seguinte seria mais rápido para sua consulta:

      `INDEX(sub_id, value,  -- either order
       entity_id)      -- last
    • 6

  3. Um banco de dados pode realmente fazer uso dos índices conforme especificado para uma consulta de índice, e pode melhorar o desempenho. Não tenho certeza se o mysql fará isso para você.

    O truque é usar um acesso de índice para enumerar todos os valores possíveis de entity_id, e um segundo (ou pelo menos um que interfira com o primeiro), em my_table_entity_id_sub_id_value, para verificar se o tripleto está na sua tabela. Isso economizará tempo se houver poucos valores de o suficiente entity_idpara que as pesquisas de índice sejam mais rápidas do que a varredura completa do índice. Por causa do seu índice redundante em entity_id, essas informações devem estar disponíveis.

    Você pode forçar esse comportamento com algo como:

    select distinct entity_id from my_table 
        where sub_id = 107 
        and value  = 'd90e7a26-2fc5-4e16-87c5-a2e9da5a26f7' 
        and entity_id in (select distinct entity_id from my_table);

    Talvez seja necessário armazenar em cache a seleção interna...

    • 1

  4. Ele está fazendo uma varredura de índice de cobertura. Conforme os documentos :

    Em alguns casos, uma consulta pode ser otimizada para recuperar valores sem consultar as linhas de dados. (Um índice que fornece todos os resultados necessários para uma consulta é chamado de índice de cobertura.) Se uma consulta usar de uma tabela apenas colunas que estão incluídas em algum índice, os valores selecionados podem ser recuperados da árvore de índice para maior velocidade

    Sua consulta usa apenas colunas naquele índice. O otimizador deve ter concluído que usar aquele índice e fazer uma varredura era mais rápido do que usar um dos outros índices para fazer uma pesquisa e, em seguida, fazer leituras diretamente nas linhas de dados completas. Não posso ter certeza do porquê disso. Certamente, se você tivesse um índice que começasse com sub_idand valuemas incluísse entity_idtambém, isso seria usado em vez disso e uma pesquisa teria sido feita em vez de uma varredura.

    Mas pense no caso em que sua tabela tem linhas muito grandes. Digamos 100 colunas diferentes e algumas strings grandes ou dados binários. Digamos que ela se aproxima do tamanho máximo de linha de 64k, e seu índice ocupa apenas 32 bytes por linha. E supondo a partir de sua saída, há 33.000 linhas no total e 330 linhas ou 1% correspondendo aos seus critérios. Digamos que também há correspondências diferentes de sub_id e value, de modo que sub_id107 tem 10 valuevalores diferentes associados a ele para 800 linhas no total e o único valueque você especificou tem 4 valores diferentes de sub_id associados a ele para 500 linhas no total.

    Se fizer a pesquisa usando o índice em sub_id, ele encontrará 800 linhas que precisa verificar. O que você obtém é uma lista das linhas e como acessá-las no disco. Ele então carregaria todos os dados para essas 800 linhas porque precisa de mais dados do que o índice contém. Essas linhas podem ser espalhadas no disco, já que o índice não é agrupado e, se cada uma tiver 60k, cada uma envolveria a leitura de vários setores, um total de 48mb de dados. Ele então tem que verificar cada um valuepara filtrar com base nos outros critérios e capturar os entity_idvalores exclusivos.
    Essas operações não são muito amigáveis ​​ao cache da CPU porque os dados são espalhados. Consultas futuras usando valores diferentes para sub_ide valueacessarão centenas de outras áreas no disco e ocuparão outros 48mb. Esta instância não é tão ruim porque os números são baixos, mas em um sistema maior isso pode acabar sendo muitos dados e não muito bom em termos de cache.

    Se estiver fazendo uma varredura usando o índice que contém todos os dados, ele tem que carregar o índice inteiro. São 33.000 linhas em vez de apenas 800, mas cada linha é muito menor, talvez apenas 32 bytes. Isso é apenas 1 MB que ele tem que ler do disco e provavelmente em mais como 250 leituras em vez de 13.000. É fácil armazenar em cache 1 MB e escanear essas 33.000 linhas para os dados que você precisa não é difícil para a CPU.

    Aqui está uma analogia imperfeita. Imagine que você tem arquivos sobre 33.000 pessoas empregadas pela sua empresa. A chave primária é EmployeeID. O arquivo de funcionários tem muitos dados, incluindo endereço, números de telefone, datas de eventos de emprego, números de previdência social, dados biométricos, etc. Cada arquivo de funcionário ocupa uma página inteira, e eles são armazenados em armários de arquivo ordenados por EmployeeID para fácil acesso (a chave primária).

    Você tem a tarefa de encontrar o LastName exclusivo de funcionários com olhos azuis. Você tem dois outros arquivos. O Gabinete A tem listas de funcionários organizadas por cor dos olhos. Esta lista contém apenas EyeColor e EmployeeId. É muito rápido encontrar os valores EmployeeId que você está procurando, mas para cada um você tem que ir ao arquivo principal e puxar o arquivo do funcionário e obter o LastName de lá. O Gabinete B tem listas de funcionários organizadas por sobrenome, mas cada linha tem LastName, EyeColor e EmployeeId. Você pode colocar cerca de 120 desses registros em cada página. Para executar sua tarefa, você tem que puxar o arquivo inteiro com 275 páginas, mas não precisa olhar para mais nada. Você pode simplesmente escanear cada página e procurar por EyeColor = 'blue' e registrar o sobrenome porque ele está bem ali. Não é preciso correr até o arquivo principal para puxar cada arquivo.

    • 1

relate perguntas