sql - relaciones - ¿Tiene sentido usar un índice que tenga una cardinalidad baja?

tipos de relaciones en base de datos ejemplos

Question

Soy principalmente un desarrollador de ActionScript y de ninguna manera un experto en SQL, pero de vez en cuando tengo que desarrollar cosas simples del lado del servidor. Entonces, pensé en preguntarle a personas más experimentadas sobre la pregunta en el título.

Tengo entendido que no gana mucho al establecer un índice en una columna que contenga pocos valores distintos. Tengo una columna que contiene un valor booleano (en realidad es un int pequeño, pero lo estoy usando como un indicador), y esta columna se utiliza en las cláusulas WHERE de la mayoría de las consultas que tengo. En un caso "promedio" teórico, la mitad de los valores de los registros será 1 y la otra mitad, 0. Entonces, en este escenario, el motor de base de datos podría evitar un escaneo completo de tabla, pero tendrá que leer muchas filas de todos modos (filas totales / 2).

Entonces, ¿debería hacer de esta columna un índice?

Para el registro, estoy usando Mysql 5, pero estoy más interesado en un razonamiento general sobre por qué tiene / no tiene sentido indexar una columna que sé que tendrá una cardinalidad baja.

Gracias por adelantado.

Answer 1

En mi humilde opinión es de utilidad limitada. Supongo que en la mayoría de los casos hay otros criterios que está usando en sus consultas, además de la bandera que probablemente ayude mucho más.

Al 50%, probablemente haré algunos benchmarking con / sin y veré si hace mucha diferencia.

Answer 2

Puede valer la pena incluir el campo booleano en un índice compuesto. Por ejemplo, si tiene una tabla grande de mensajes que, por lo general, debe solicitarse por fecha, pero también tiene un campo booleano eliminado , por lo que a menudo lo consulta de esta manera:

SELECT ... FROM Messages WHERE Deleted = 0 AND Date BETWEEN @start AND @end

Definitivamente se beneficiará de tener un índice compuesto en los campos Eliminado y Fecha .

Answer 3

Un índice puede ayudar incluso en campos de cardinalidad baja si:

1. Cuando uno de los posibles valores es muy poco frecuente en comparación con los otros valores y lo busca.

   Por ejemplo, hay muy pocas mujeres daltónicas, por lo que esta consulta:

   SELECT  *
   FROM    color_blind_people
   WHERE   gender = ''F''

   lo más probable es que se beneficie de un índice de gender .

2. Cuando los valores tienden a agruparse en el orden de la tabla:

   SELECT  *
   FROM    records_from_2008
   WHERE   year = 2010
   LIMIT 1

   Aunque solo hay 3 años distintos aquí, los registros con años anteriores probablemente se agreguen primero, por lo que se deberían escanear muchos registros antes de devolver el primer registro de 2010 si no fuera por el índice.

3. Cuando necesitas ORDER BY / LIMIT :

   SELECT  *
   FROM    people
   ORDER BY
           gender, id
   LIMIT 1

   Sin el índice, se filesort un filesort . Aunque está optimizado para el LIMIT , aún necesitaría una exploración completa de la tabla.

4. Cuando el índice cubre todos los campos utilizados en la consulta:

   CREATE INDEX (low_cardinality_record, value)
   
   SELECT  SUM(value)
   FROM    mytable
   WHERE   low_cardinality_record = 3

5. Cuando necesitas DISTINCT :

   SELECT  DISTINCT color
   FROM    tshirts

   MySQL usará INDEX FOR GROUP-BY , y si tiene pocos colores, esta consulta será instantánea incluso con millones de registros.

   Este es un ejemplo de un escenario cuando el índice en un campo de cardinalidad baja es más eficiente que el de un campo de cardinalidad alta.

Tenga en cuenta que si el rendimiento de DML no tiene mucho que ver con un problema, entonces es seguro crear el índice.

Si el optimizador cree que el índice es ineficiente, el índice simplemente no se usará.

Answer 4

Usualmente hago una prueba de índice simple de "tener índice" versus "no tengo". Según mi experiencia, obtiene la mayor parte del rendimiento en consultas que usan ORDER BY en la columna indexada. En caso de que tenga cualquier clasificación en esa columna, es muy probable que la indexación le ayude.