Quando devo usar um índice composto?

  1. Quando devo usar um índice composto em um database?
  2. Quais são as ramificações de desempenho usando um índice composto)?
  3. Por que devo usar um índice composto?

Por exemplo, eu tenho uma tabela de homes :

 CREATE TABLE IF NOT EXISTS `homes` ( `home_id` int(10) unsigned NOT NULL auto_increment, `sqft` smallint(5) unsigned NOT NULL, `year_built` smallint(5) unsigned NOT NULL, `geolat` decimal(10,6) default NULL, `geolng` decimal(10,6) default NULL, PRIMARY KEY (`home_id`), KEY `geolat` (`geolat`), KEY `geolng` (`geolng`), ) ENGINE=InnoDB ; 

Faz sentido para mim usar um índice composto para geolat e geolng , de tal forma que:

Eu substituo:

  KEY `geolat` (`geolat`), KEY `geolng` (`geolng`), 

com:

 KEY `geolat_geolng` (`geolat`, `geolng`) 

Se então:

  • Por quê?
  • Qual é a ramificação de desempenho usando um índice composto?

ATUALIZAR:

Como muitas pessoas afirmaram que depende totalmente das consultas que eu realizo, abaixo está a consulta mais comum realizada:

 SELECT * FROM homes WHERE geolat BETWEEN ??? AND ??? AND geolng BETWEEN ??? AND ??? 

ATUALIZAÇÃO 2:

Com o seguinte esquema do database:

 CREATE TABLE IF NOT EXISTS `homes` ( `home_id` int(10) unsigned NOT NULL auto_increment, `primary_photo_group_id` int(10) unsigned NOT NULL default '0', `customer_id` bigint(20) unsigned NOT NULL, `account_type_id` int(11) NOT NULL, `address` varchar(128) collate utf8_unicode_ci NOT NULL, `city` varchar(64) collate utf8_unicode_ci NOT NULL, `state` varchar(2) collate utf8_unicode_ci NOT NULL, `zip` mediumint(8) unsigned NOT NULL, `price` mediumint(8) unsigned NOT NULL, `sqft` smallint(5) unsigned NOT NULL, `year_built` smallint(5) unsigned NOT NULL, `num_of_beds` tinyint(3) unsigned NOT NULL, `num_of_baths` decimal(3,1) unsigned NOT NULL, `num_of_floors` tinyint(3) unsigned NOT NULL, `description` text collate utf8_unicode_ci, `geolat` decimal(10,6) default NULL, `geolng` decimal(10,6) default NULL, `display_status` tinyint(1) NOT NULL, `date_listed` timestamp NOT NULL default CURRENT_TIMESTAMP, `contact_email` varchar(100) collate utf8_unicode_ci NOT NULL, `contact_phone_number` varchar(15) collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`home_id`), KEY `customer_id` (`customer_id`), KEY `city` (`city`), KEY `num_of_beds` (`num_of_beds`), KEY `num_of_baths` (`num_of_baths`), KEY `geolat` (`geolat`), KEY `geolng` (`geolng`), KEY `account_type_id` (`account_type_id`), KEY `display_status` (`display_status`), KEY `sqft` (`sqft`), KEY `price` (`price`), KEY `primary_photo_group_id` (`primary_photo_group_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=8 ; 

Usando o seguinte SQL:

 EXPLAIN SELECT homes.home_id, address, city, state, zip, price, sqft, year_built, account_type_id, num_of_beds, num_of_baths, geolat, geolng, photo_id, photo_url_dir FROM homes LEFT OUTER JOIN home_photos ON homes.home_id = home_photos.home_id AND homes.primary_photo_group_id = home_photos.home_photo_group_id AND home_photos.home_photo_type_id = 2 WHERE homes.display_status = true AND homes.geolat BETWEEN -100 AND 100 AND homes.geolng BETWEEN -100 AND 100 

EXPLAIN retorna:

 id select_type table type possible_keys key key_len ref rows Extra ---------------------------------------------------------------------------------------------------------- 1 SIMPLE homes ref geolat,geolng,display_status display_status 1 const 2 Using where 1 SIMPLE home_photos ref home_id,home_photo_type_id,home_photo_group_id home_photo_group_id 4 homes.primary_photo_group_id 4 

Eu não entendo muito bem como ler o comando EXPLAIN. Isso parece bom ou ruim? No momento, NÃO estou usando um índice composto para geolat e geolng. Eu deveria ser?

Você deve usar um índice composto quando estiver usando consultas que se beneficiam dele. Um índice composto que se parece com isso:

 index( column_A, column_B, column_C ) 

irá beneficiar uma consulta que usa esses campos para unir, filtrar e, às vezes, selecionar. Ele também beneficiará consultas que usam subconjuntos de colunas mais à esquerda nesse composto. Portanto, o índice acima também satisfará as consultas que precisam

 index( column_A, column_B, column_C ) index( column_A, column_B ) index( column_A ) 

Mas não será (pelo menos não diretamente, talvez possa ajudar parcialmente se não houver melhores índices) ajuda para consultas que precisam

 index( column_A, column_C ) 

Observe como a coluna_B está ausente.

Em seu exemplo original, um índice composto para duas dimensões beneficiará principalmente as consultas que consultam em ambas as dimensões ou na dimensão mais à esquerda, por si só, mas não a dimensão mais à direita por si só. Se você está sempre consultando duas dimensões, um índice composto é o caminho a percorrer, realmente não importa qual é o primeiro (muito provavelmente).

Imagine que você tenha as três consultas a seguir:

Consulta I:

 SELECT * FROM homes WHERE `geolat`=42.9 AND `geolng`=36.4 

Consulta II:

 SELECT * FROM homes WHERE `geolat`=42.9 

Consulta III:

 SELECT * FROM homes WHERE `geolng`=36.4 

Se você tiver um índice separado por coluna, todas as três consultas usarão índices. No MySQL, se você tiver um índice composto ( geolat , geolng ), apenas a consulta I e a consulta II (que usa a primeira parte do índice composto) usam índices. Nesse caso, a consulta III requer pesquisa de tabela completa.

Na seção de Índices de Múltiplas Colunas do manual, é explicado com clareza como vários índices de colunas funcionam, portanto, não quero digitar novamente o manual.

Na página do Manual de Referência do MySQL :

Um índice de várias colunas pode ser considerado uma matriz classificada contendo valores criados pela concatenação dos valores das colunas indexadas .

Se você usa um índice separado para colunas geolat e geolng, você tem dois índices diferentes em sua tabela que você pode pesquisar independentemente.

 INDEX geolat ----------- VALUE RRN 36.4 1 36.4 8 36.6 2 37.8 3 37.8 12 41.4 4 INDEX geolng ----------- VALUE RRN 26.1 1 26.1 8 29.6 2 29.6 3 30.1 12 34.7 4 

Se você usar o índice composto, terá apenas um índice para as duas colunas:

 INDEX (geolat, geolng) ----------- VALUE RRN 36.4,26.1 1 36.4,26.1 8 36.6,29.6 2 37.8,29.6 3 37.8,30.1 12 41.4,34.7 4 

RRN é o número de registro relativo (para simplificar, você pode dizer ID). Os dois primeiros índices gerados separados e o terceiro índice são compostos. Como você pode ver, você pode pesquisar com base em geolng na composição uma vez que é indexado por geolat, no entanto, é possível pesquisar por geolat ou “geolat E geolng” (desde que geolng é o segundo nível de índice).

Além disso, dê uma olhada em Como o MySQL usa o manual de índices .

Pode haver um equívoco sobre o que o índice composto faz. Muitas pessoas pensam que o índice composto pode ser usado para otimizar uma consulta de pesquisa, desde que a cláusula where cubra as colunas indexadas, no seu caso geolat e geolng . Vamos nos aprofundar mais:

Acredito que seus dados sobre as coordenadas dos lares seriam decimais randoms como tal:

 home_id geolat geolng 1 20.1243 50.4521 2 22.6456 51.1564 3 13.5464 45.4562 4 55.5642 166.5756 5 24.2624 27.4564 6 62.1564 24.2542 ... 

Já que os valores de geolng e geolng dificilmente se repetem. Um índice composto de geolat e geolng seria algo como isto:

 index_id geolat geolng 1 20.1243 50.4521 2 20.1244 61.1564 3 20.1251 55.4562 4 20.1293 66.5756 5 20.1302 57.4564 6 20.1311 54.2542 ... 

Portanto, a segunda coluna do índice composto é basicamente inútil ! A velocidade de sua consulta com um índice composto provavelmente será semelhante a um índice apenas na coluna geolat .

Como mencionado por Will, o MySQL fornece suporte a extensão espacial . Um ponto espacial é armazenado em uma única coluna, em vez de duas colunas de lat separadas. O índice espacial pode ser aplicado a essa coluna. No entanto, a eficiência pode ser superestimada com base na minha experiência pessoal. Pode ser que o índice espacial não resolva o problema bidimensional, mas apenas acelere a pesquisa usando R-Trees com divisão quadrática .

A desvantagem é que um ponto espacial consome muito mais memory, já que usou números de precisão dupla de oito bytes para armazenar coordenadas. Corrija-me se eu estiver enganado.

Índices compostos são muito poderosos, pois eles:

  • Reforce a integridade da estrutura
  • Ativar a sorting em um ID FILTRADO

ENERGIA ESTRUTURA INTEGRIDADE

Índices compostos não são apenas outro tipo de índice; eles podem fornecer estrutura NECESSARY a uma tabela, impondo integridade como a chave primária.

Innodb do Mysql suporta clustering e o exemplo a seguir ilustra porque um índice composto pode ser necessário.

Para criar tabelas de amigos (ou seja, para uma rede social), precisamos de 2 colunas: user_id, friend_id .

Tabela Strcture

 user_id (medium_int) friend_id (medium_int) Primary Key -> (user_id, friend_id) 

Por virtude, uma Chave Primária (PK) é única e ao criar uma PK composta, a Innodb verificará automaticamente se não há duplicatas no user_id, friend_id existe quando um novo registro é adicionado. Esse é o comportamento esperado, já que nenhum usuário deve ter mais de um registro (link de relacionamento) com friend_id = 2 por exemplo.

Sem um PK composto, podemos criar esse esquema usando uma chave substituta:

 user_friend_id user_id friend_id Primary Key -> (user_friend_id) 

Agora, sempre que um novo registro for adicionado, teremos que verificar se um registro anterior com a combinação user_id, friend_id ainda não existe.

Como tal, um índice composto pode impor a integridade da estrutura.

ENABLE SORTING EM UM ID FILTRADO

É muito comum classificar um conjunto de registros pelo horário do post (timestamp ou datetime). Geralmente, isso significa postar em um determinado ID. Aqui está um exemplo

Tabela User_Wall_Posts (pense nos posts do Facebook)

 user_id (medium_int) timestamp (timestamp) author_id (medium_int) comment_post (text) Primary Key -> (user_id, timestamp, author_id) 

Queremos consultar e encontrar todas as postagens de user_id = 10 e classificar as postagens de comentários por timestamp (data).

QUERY SQL

 SELECT * FROM User_Wall_Posts WHERE user_id = 10 ORDER BY timestamp DES 

O PK composto permite que o Mysql filtre e classifique os resultados usando o índice; O Mysql não terá que usar um arquivo temporário ou um filesort para buscar os resultados. Sem uma chave composta, isso não seria possível e causaria uma consulta muito ineficiente.

Como tal, as chaves compostas são muito poderosas e se adequam mais do que o simples problema de “Eu quero procurar por column_a, column_b então usarei chaves compostas. Para meu esquema de database atual, tenho tantas chaves compostas quanto chaves únicas. ignore o uso de uma chave composta!

Índices compostos são úteis para

  • 0 ou mais cláusulas “=”, mais
  • no máximo uma cláusula de intervalo.

Um índice composto não pode manipular dois intervalos. Eu discuto isso ainda mais no meu livro de receitas do índice .

Encontre o mais próximo – Se a questão é realmente sobre como otimizar

 WHERE geolat BETWEEN ??? AND ??? AND geolng BETWEEN ??? AND ??? 

então nenhum índice pode realmente manipular ambas as dimensões.

Em vez disso, é preciso “pensar fora da checkbox”. Se uma dimensão é implementada via particionamento e a outra é implementada escolhendo cuidadosamente a PRIMARY KEY , pode-se obter uma eficiência significativamente melhor para tabelas muito grandes de pesquisa lat / lng. Meu blog vai para os detalhes de como implementar o “achado mais próximo” no mundo. Inclui código.

As PARTITIONs são faixas de intervalos de latitude. A PRIMARY KEY começa deliberadamente com longitude, de modo que as linhas úteis provavelmente estejam no mesmo bloco. Uma rotina armazenada orquestra o código confuso para fazer a order by... limit... e para aumentar o “quadrado” em torno do alvo até que você tenha cafés suficientes (ou qualquer outro). Ele também cuida dos cálculos do grande círculo e manipula o dateline e os polos.

Não há preto e branco, tamanho único para todas as respostas.

Você deve usar um índice composto, quando sua carga de trabalho de consulta se beneficiaria de um.

Você precisa definir o perfil de sua carga de trabalho de consulta para determinar isso.

Um índice composto entra em jogo quando as consultas podem ser satisfeitas inteiramente desse índice.

ATUALIZAÇÃO (em resposta à edição da pergunta postada): Se você estiver selecionando * da tabela, o índice composto poderá ser usado. Você precisará executar o EXPLAIN PLAN para ter certeza.

Para fazer pesquisas espaciais, você precisa de um algoritmo R-Tree , que permite pesquisar áreas geográficas muito rapidamente. Exatamente o que você precisa para este trabalho.

Alguns bancos de dados têm índices espaciais embutidos. Uma pesquisa rápida no Google mostra que o MySQL 5 os possui (que, olhando para o seu SQL, estou supondo que você esteja usando o MySQL).

O índice composto pode ser útil quando você deseja otimizar a cláusula group by (consulte este artigo http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization.html ). Por favor preste atenção:

As condições prévias mais importantes para o uso de índices para GROUP BY são que todas as colunas GROUP BY referenciam atributos do mesmo índice, e que o índice armazena suas chaves em ordem (por exemplo, este é um índice BTREE e não um índice HASH)

Estou com @Mitch, depende inteiramente de suas dúvidas. Felizmente, você pode criar e descartar índices a qualquer momento e pode preceder a palavra-chave EXPLAIN às suas consultas para ver se o analisador de consultas usa os índices.

Se você estiver procurando um par exato de latitude / longitude, esse índice provavelmente faria sentido. Mas provavelmente você estará procurando por casas a uma certa distância de um lugar específico, então suas consultas serão parecidas com isso (veja a fonte ):

 select *, sqrt( pow(h2.geolat - h1.geolat, 2) + pow(h2.geolng - h1.geolng, 2) ) as distance from homes h1, homes h2 where h1.home_id = 12345 and h2.home_id != h1.home_id order by distance 

e o índice provavelmente não será útil. Para consultas geoespaciais, você precisa de algo assim.

Atualização: com esta consulta:

 SELECT * FROM homes WHERE geolat BETWEEN ??? AND ??? AND geolng BETWEEN ??? AND ??? 

O analisador de consultas poderia usar um índice somente em geolat, ou um índice somente em geolng, ou possivelmente ambos os índices. Eu não acho que usaria um índice composto. Mas é fácil testar cada uma dessas permutações em um dataset real e, em seguida, (a) ver o que o EXPLAIN informa e (b) medir o tempo que a consulta realmente leva.