Início / dba / Perguntas / 22091
Accepted
user4951
Asked: 2012-08-07 21:33:15 +0800 CST

Como o índice composto ajuda a acelerar a consulta?

  • 772

Digamos que temos várias colunas na tabela mysql.

Latitude longitude

Digamos que façamos um índice (latitude e longitude)

Bem, o índice classificará primeiro por Latitude e SE a Latitude for a mesma, ele classificará por Longitude.

Algo suspeito deve aparecer. A latitude raramente, ou nunca, é exatamente a mesma.

Digamos que queremos encontrar todos os pontos cujo Lat,Long está "em uma caixa" Como na Terra esses índices podem ser úteis.

Observação: estou tentando entender como funciona a indexação. Não estou tentando dizer esnobemente que o índice composto é estúpido. Não precisa ser uma resposta completa. Qualquer apontamento é bom.

Mais importante, suspeito que uma consulta usará apenas 1 índice e a única maneira de uma consulta usar o índice multilpe é criar um índice composto.

mysql index

2 respostas

  1. Best Answer

    Os índices são, na maioria dos casos, uma estrutura B-Tree (ou algum tipo de). Existem DBMS que suportam diferentes tipos de indexação.

    Como você está falando sobre longitude/latitude, o PostgreSQL com seus índices GIST vem à mente. O Oracle possui índices Bitmap além dos índices B-Tree. Tenho certeza de que o SQL Server e o DB2 também têm alguns tipos de índice especiais. E há índices de texto completo que tornam a pesquisa de texto muito eficiente.

    Um índice B-Tree é muito eficiente para encontrar um valor específico - pense em um índice de chave primária em que todos os valores são diferentes. Se o índice contiver apenas a(s) coluna(s) PK (ou seja, não é um índice clusterizado), normalmente procurar uma linha por um valor PK específico não leva mais do que (aproximadamente) 3-4 operações IO (pelo menos com Oracle). 2-3 para encontrar o bloco de índice e um adicional para ler toda a linha. Isso fica mais eficiente se o índice contiver colunas adicionais para que a pesquisa da linha real da tabela não seja necessária. O termo para isso é "índice de cobertura" ou "recuperação somente de índice".

    Agora, para fazer "pesquisas de intervalo" (por exemplo where foo > 42), um índice é muito útil, assim como na maioria dos DBMS, o índice também pode ser verificado de acordo com um predicado. Normalmente (novamente, isso depende muito do DBMS), isso é um pouco menos eficiente do que uma pesquisa direta (novamente, isso também depende da capacidade de fazer uma "recuperação somente de índice").

    Não conheço nenhum BMS que não possa usar mais de um índice em uma consulta. Pense em uma junção em uma coluna PK e FK - dependendo da distribuição de dados, o DBMS pode usar o índice para localizar as linhas pai (pesquisa PK) e as linhas filhas (pesquisa FK).

    Mas nem todo SGBD pode usar mais de um índice para a mesma tabela em uma única consulta.

    Afinal, se um índice está sendo usado ou não, depende de muitas coisas.

    Posso altamente recomendar http://use-the-index-luke.com/ que é uma introdução muito boa sobre indexação em todos os principais DBMS.

    Informações específicas do DBMS:

    Oracle: http://docs.oracle.com/cd/E11882_01/server.112/e25789/indexiot.htm
    PostgreSQL: http://www.postgresql.org/docs/current/static/indexes.html

    • 4

  2. Os valores não precisam ser exatos para usar um índice. Em seu exemplo particular, uma varredura de intervalo limitará a quantidade de dados examinados. Um índice em ambos os limites permite que os dados sejam limitados em ambos os eixos.

    Uma varredura de alcance opera conceitualmente semelhante a como você olharia para um mapa. Se você quiser encontrar o Monumento de Washington na Latitude: 38,889449 Longitude: -77,035232, você pode começar sua pesquisa concentrando-se em 30 a 40 Latitude e -70 a -80 Longitude. Se você apenas olhasse para o Latitude, teria muito mais mapas para ver do que quando olhasse para ambos.

    Aqui está uma demonstração no Oracle.

    > drop table t1
table T1 dropped.
> create table t1 as 
   (select dbms_random.value()*90 lat, dbms_random.value()*180 lon, level x 
      from dual connect by level<=5000)
table T1 created.
> create index x1 on t1 (lat)
index X1 created.
> set autotrace on explain
Autotrace Enabled
Displays the execution plan only.
> select * from t1 WHERE lat between 30 AND 40 AND lon BETWEEN 100 and 110
LAT LON X
--- --- -
30.3767657321169330960894098832402216675 104.010180757903737597756516124940719947 815 
30.5867860257718358281746146497630607937 105.59648614423201691420471610631375134 3308 
31.4193624839183326648532951557713565746 
...    
 25 rows selected 

Plan hash value: 3070918941

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |    25 |   975 |    12   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1   |    25 |   975 |    12   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | X1   |    23 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("LON">=100 AND "LON"<=110)
   2 - access("LAT">=30 AND "LAT"<=40)

Note
-----
   - dynamic sampling used for this statement (level=2)

> set autotrace off
Autotrace Disabled
> create index x2 on t1 (lat,lon)
index X2 created.
> set autotrace on explain
Autotrace Enabled
Displays the execution plan only.
> select * from t1 WHERE lat between 30 AND 40 AND lon BETWEEN 100 and 110
LAT LON X
--- --- -
30.3767657321169330960894098832402216675 104.010180757903737597756516124940719947 815 
30.5867860257718358281746146497630607937 105.59648614423201691420471610631375134 3308 
31.4193624839183326648532951557713565746 
...    
 25 rows selected 

Plan hash value: 1153438080

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |    25 |   975 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |    25 |   975 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | X2   |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("LAT">=30 AND "LON">=100 AND "LAT"<=40 AND "LON"<=110)
       filter("LON">=100 AND "LON"<=110)

Note
-----
   - dynamic sampling used for this statement (level=2)

> set autotrace off
Autotrace Disabled
    • 3

relate perguntas