whats what plain length index constraint mysql indexing composite-index

mysql - what - ¿Cuándo debería usar un índice compuesto?



what is index in mysql (9)

Debe usar un índice compuesto cuando usa consultas que se benefician de él. Un índice compuesto que se ve así:

index( column_A, column_B, column_C )

se beneficiará de una consulta que usa esos campos para unir, filtrar y, a veces, seleccionar. También beneficiará las consultas que usan subconjuntos de columnas más a la izquierda en ese compuesto. Por lo tanto, el índice anterior también satisfará las consultas que necesiten

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

Pero no ayudará (al menos no directamente, tal vez pueda ayudar parcialmente si no hay mejores índices) a las consultas que necesitan

index( column_A, column_C )

Observe cómo falta la columna_B.

En su ejemplo original, un índice compuesto para dos dimensiones beneficiará principalmente consultas que consultan en ambas dimensiones o la dimensión más a la izquierda por sí misma, pero no la dimensión más a la derecha por sí misma. Si siempre está consultando dos dimensiones, un índice compuesto es el camino a seguir, realmente no importa cuál es el primero (probablemente).

  1. ¿Cuándo debería usar un índice compuesto en una base de datos?
  2. ¿Cuál es la ramificación del rendimiento al usar un índice compuesto?
  3. ¿Por qué debería usar usar un índice compuesto?

Por ejemplo, tengo una tabla 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 ;

¿Tiene sentido para mí usar un índice compuesto tanto para geolat como geolng , de modo que:

Yo reemplazo:

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

con:

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

Si es así:

  • ¿Por qué?
  • ¿Cuál es la ramificación del rendimiento al usar un índice compuesto?

ACTUALIZAR:

Como muchas personas lo han declarado completamente dependiente de las consultas que realizo, a continuación se muestra la consulta más común realizada:

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

ACTUALIZACIÓN 2:

Con el siguiente esquema de base de datos:

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 el siguiente 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 regresa:

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

No entiendo muy bien cómo leer el comando EXPLAIN. ¿Esto se ve bien o mal? En este momento, NO estoy usando un índice compuesto para geolat y geolng. Debería ser?

El índice compuesto puede ser útil cuando desee optimizar la cláusula group by (consulte este artículo http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization.html ). Por favor pon atención:

Las condiciones previas más importantes para usar índices para GROUP BY son que todas las columnas GROUP BY hacen referencia a los atributos del mismo índice, y que el índice almacena sus claves en orden (por ejemplo, este es un índice BTREE y no un índice HASH)

Estoy con @Mitch, depende completamente de tus consultas. Afortunadamente puede crear y soltar índices en cualquier momento, y puede anteponer la palabra clave EXPLAIN a sus consultas para ver si el analizador de consultas usa los índices.

Si va a buscar un par latitud / longitud exacto, este índice probablemente tenga sentido. Pero es probable que estés buscando casas a cierta distancia de un lugar en particular, por lo que tus consultas se verán de la siguiente manera (consulta la source ):

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

y el índice muy probablemente no será útil en absoluto. Para consultas geoespaciales, necesita algo como this .

Actualización: con esta consulta:

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

El analizador de consultas podría usar un índice solo en geolat, o un índice en geolng solo, o posiblemente ambos índices. No creo que use un índice compuesto. Pero es fácil probar cada una de estas permutaciones en un conjunto de datos real y luego (a) ver qué le dice EXPLAIN y (b) medir el tiempo que realmente tarda la consulta.

Imagine que tiene las siguientes tres consultas:

Pregunta 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

Si tiene un índice separado por columna, las tres consultas usan índices. En MySQL, si tiene un índice compuesto ( geolat , geolng ), solo la consulta I y la consulta II (que usa la primera parte del índice de composición) usa índices. En este caso, la consulta III requiere una búsqueda de tabla completa.

En la sección de Indices de columnas múltiples del manual, se explica claramente cómo funcionan los índices de columnas múltiples, por lo que no quiero volver a escribir el manual.

Desde la página del Manual de referencia de MySQL :

Un índice de varias columnas se puede considerar una matriz ordenada que contiene valores que se crean al concatenar los valores de las columnas indexadas .

Si usa un índice separado para las columnas geolat y geolng, tiene dos índices diferentes en su tabla que puede buscar de forma independiente.

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

Si usa un índice compuesto, solo tiene un índice para ambas columnas:

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 es un número de registro relativo (para simplificar, puede decir ID). Los dos primeros índices se generan por separado y el tercer índice es compuesto. Como puede ver, puede buscar basado en geolng en composite one, ya que está indexado por geolat, sin embargo, es posible buscar por geolat o "geolat AND geolng" (ya que geolng es un índice de segundo nivel).

Además, eche un vistazo a la sección del manual Cómo usa MySQL Indexes .

Los índices compuestos son muy potentes ya que:

  • Hacer cumplir la integridad de la estructura
  • Habilite la clasificación en una ID FILTRADA

ENFORCE LA INTEGRIDAD DE LA ESTRUCTURA

Los índices compuestos no son solo otro tipo de índice; pueden proporcionar una estructura NECESARIA a una tabla imponiendo la integridad como clave principal.

Mysql''s Innodb admite clustering y el siguiente ejemplo ilustra por qué puede ser necesario un índice compuesto.

Para crear tablas de amigos (es decir, para una red social), necesitamos 2 columnas: user_id, friend_id .

Table Strcture

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

En virtud, una clave principal (PK) es única y al crear un PK compuesto, Innodb comprobará automáticamente que no existen duplicados en user_id, friend_id cuando se agrega un nuevo registro. Este es el comportamiento esperado ya que ningún usuario debería tener más de 1 registro (enlace de relación) con friend_id = 2 por ejemplo.

Sin un PK compuesto, podemos crear este esquema utilizando una clave sustituta:

user_friend_id user_id friend_id Primary Key -> (user_friend_id)

Ahora, cada vez que se agrega un nuevo registro, debemos verificar que no exista un registro anterior con la combinación user_id, friend_id .

Como tal, un índice compuesto puede forzar la integridad de la estructura.

HABILITAR CLASIFICAR EN UNA ID FILTRADA

Es muy común ordenar un conjunto de registros por la hora de la publicación (marca de tiempo o fecha y hora). Por lo general, esto significa publicar en un ID determinado. Aquí hay un ejemplo

Table User_Wall_Posts (piense si las publicaciones en la pared de Facebook)

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

Queremos consultar y encontrar todas las publicaciones de user_id = 10 y ordenar las publicaciones de comentarios por timestamp (date).

SQL QUERY

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

El PK compuesto permite a Mysql filtrar y ordenar los resultados usando el índice; Mysql no tendrá que usar un archivo temporal o una clasificación de archivos para obtener los resultados. Sin una clave compuesta, esto no sería posible y causaría una consulta muy ineficiente.

Como tal, las claves compuestas son muy potentes y se ajustan más que al simple problema de "Quiero buscar column_a, column_b así que column_a, column_b claves compuestas. Para mi esquema de base de datos actual, tengo tantas claves compuestas como claves individuales. ¡pasar por alto el uso de una clave compuesta!

No hay blanco y negro, una respuesta para todos los tamaños.

Debería usar un índice compuesto, cuando su carga de trabajo de consulta se beneficiaría de uno.

Debe determinar su carga de trabajo de consulta para determinar esto.

Un índice compuesto entra en juego cuando las consultas se pueden satisfacer completamente desde ese índice.

ACTUALIZACIÓN (en respuesta a la edición de la pregunta publicada): si selecciona * de la tabla, se puede usar el índice compuesto, puede que no. Deberá ejecutar EXPLAIN PLAN para estar seguro.

Para realizar búsquedas espaciales, necesita un algoritmo R-Tree , que permite buscar áreas geográficas muy rápidamente. Exactamente lo que necesitas para este trabajo.

Algunas bases de datos tienen índices espaciales incorporados. Una búsqueda rápida en Google muestra que MySQL 5 los tiene (lo que al ver su SQL supongo que está utilizando MySQL).

Podría haber una idea errónea sobre qué hace el índice compuesto. Muchas personas piensan que el índice compuesto se puede usar para optimizar una consulta de búsqueda, siempre y cuando la cláusula where cubra las columnas indexadas, en su caso geolat y geolng . Vamos a profundizar más:

Creo que sus datos sobre las coordenadas de las casas serían decimales aleatorios como tales:

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 ...

Dado que los valores geolat y geolng casi no se repiten. Un índice compuesto en geolat y geolng se vería más o menos así:

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 ...

¡Por lo tanto, la segunda columna del índice compuesto es básicamente inútil ! La velocidad de su consulta con un índice compuesto probablemente sea similar a un índice solo en la columna geolat .

Como lo menciona Will, MySQL proporciona soporte de extensión espacial . Un punto espacial se almacena en una sola columna en lugar de dos columnas independientes. El índice espacial se puede aplicar a dicha columna. Sin embargo, la eficiencia podría ser sobrevalorada según mi experiencia personal. Podría ser que el índice espacial no resuelva el problema de dos dimensiones, sino que simplemente acelere la búsqueda utilizando R-Trees con división cuadrática .

La desventaja es que un punto espacial consume mucha más memoria ya que usa números de doble precisión de ocho bytes para almacenar coordenadas. Corrígeme si estoy equivocado.

Los índices compuestos son útiles para

  • 0 o más cláusulas "=", más
  • a lo sumo una cláusula de rango.

Un índice compuesto no puede manejar dos rangos. Discuto esto más en mi libro de cocina índice .

Buscar más cercano - Si la pregunta es realmente acerca de la optimización

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

entonces ningún índice realmente puede manejar ambas dimensiones.

En cambio, uno debe ''pensar fuera de la caja''. Si se implementa una dimensión a través de particiones y la otra se implementa seleccionando cuidadosamente la PRIMARY KEY , se puede obtener una eficiencia significativamente mejor para tablas muy grandes de búsqueda lat / lng. Mi blog latlng va a los detalles de cómo implementar "encontrar más cercano" en el mundo. Incluye código.

Las PARTITIONs son rayas de rangos de latitud. La PRIMARY KEY comienza deliberadamente con la longitud para que las filas útiles puedan estar en el mismo bloque. Una rutina almacenada orquesta el código desordenado para hacer el order by... limit... y para hacer crecer el "cuadrado" alrededor del objetivo hasta que tenga suficientes cafeterías (o lo que sea). También se ocupa de los cálculos del gran círculo y del manejo de la fecha y los polos.