vistas - reorganizar indices en mysql

MySQL: columnas de baja cardinalidad/selectividad=¿cómo indexar?

Question

Necesito agregar índices a mi tabla (columnas) y tropecé con esta publicación:

¿Cuántos índices de bases de datos son demasiados?

Cita: "Habiendo dicho eso, puedes agregar claramente una gran cantidad de índices inútiles a una tabla que no hará nada. Agregar índices B-Tree a una columna con 2 valores distintos no tendrá sentido ya que no agrega nada en términos de buscar los datos. Cuanto más únicos sean los valores en una columna, más se beneficiará de un índice ".

¿Realmente es inútil un índice si solo hay dos valores distintos? Dada una tabla de la siguiente manera (base de datos MySQL, InnoDB)

Id (BIGINT)
fullname (VARCHAR)
address (VARCHAR)
status (VARCHAR)

Otras condiciones:

• La base de datos contiene 300 millones de registros
• El estado solo puede ser "habilitado" y "deshabilitado"
• 150 millones de registros tienen status = habilitado y 150 millones de registros tienen stauts = deshabilitado

Según tengo entendido, sin tener un índice de estado, ¿una selección con where status=''enabled'' resultaría en una tabla completa con 300 millones de registros para procesar?

¿Qué tan eficiente es la búsqueda cuando uso un índice BTREE sobre el estado?

¿Debería indexar esta columna o no?

¿Qué alternativas (tal vez cualquier otro índice) proporciona MySQL InnoDB para buscar registros de manera eficiente mediante la cláusula "where status =" enabled "en el ejemplo dado con una cardinalidad / selectividad muy baja de los valores?

Answer 1

Casi no necesitará todos los 150 millones de registros a la vez, por lo que supongo que el "estado" siempre se utilizará junto con otras columnas. Tal vez tendría más sentido usar un índice compuesto como (estado, nombre completo)

Answer 2

El índice que describes es bastante inútil. Un índice se utiliza mejor cuando necesita seleccionar un número pequeño de filas en comparación con el total de filas.

La razón de esto está relacionada con la forma en que una base de datos accede a una tabla. Las tablas se pueden evaluar mediante un escaneo completo de la tabla, donde cada bloque se lee y se procesa sucesivamente. O mediante un rowid o búsqueda de clave, donde la base de datos tiene una clave / rowid y lee la fila exacta que requiere.

En el caso donde usa una cláusula where basada en la clave principal u otro índice único, ej. where id = 1 , la base de datos puede usar el índice para obtener una referencia exacta de dónde se almacenan los datos de la fila. Esto es claramente más eficiente que hacer un escaneo de tabla completo y procesar cada bloque.

Ahora volviendo a su ejemplo, tiene una cláusula where status = ''enabled'' , el índice devolverá 150m filas y la base de datos tendrá que leer cada fila alternadamente usando lecturas pequeñas separadas. Mientras que acceder a la tabla con una exploración de tabla completa permite que la base de datos utilice lecturas más grandes y eficientes.

Hay un punto en el que es mejor hacer una exploración de tabla completa en lugar de usar el índice. Con mysql puede usar FORCE INDEX (idx_name) como parte de su consulta para permitir comparaciones entre cada método de acceso a la tabla.

Referencia: http://dev.mysql.com/doc/refman/5.5/en/how-to-avoid-table-scan.html

Answer 3

La respuesta de @ a''r es correcta, sin embargo, se debe señalar que la utilidad de un índice viene dada no solo por su cardinalidad sino también por la distribución de los datos y las consultas que se ejecutan en la base de datos.

En el caso de OP, con 150M registros con status=''enabled'' y 150M con status=''disabled'' , el índice es innecesario y una pérdida de recursos.

En el caso de que los registros 299M tengan status=''enabled'' y 1M tengan status=''disabled'' , el índice es útil (y se usará) en consultas de tipo SELECT ... where status=''disabled'' .
Las consultas de tipo SELECT ... where status=''enabled'' se seguirán ejecutando con un escaneo completo de la tabla.

Answer 4

Lamento decir que no estoy de acuerdo con Mike. Agregar un índice tiene como objetivo limitar la cantidad de búsquedas de registros completos para MySQL, limitando así IO, que generalmente es el cuello de botella.

Esta indexación no es gratuita; lo paga en inserciones / actualizaciones cuando el índice debe actualizarse y en la búsqueda en sí, ya que ahora necesita cargar el archivo de índice (es probable que el índice de texto completo para registros de 300M no esté en la memoria). Por lo tanto, es posible que obtenga IO adicional en lugar de limitarlo.

Estoy de acuerdo con la afirmación de que una variable binaria se almacena mejor como una sola, bool o tinyint, ya que eso disminuye la longitud de una fila y, por lo tanto, puede limitar el disco IO, también las comparaciones en números son más rápidas.

Si necesita velocidad y rara vez usa los registros deshabilitados, puede desear tener 2 tablas, una para registros habilitados y otra para deshabilitados y mover los registros cuando el estado cambie. A medida que aumenta la complejidad y el riesgo, esta sería mi última opción, por supuesto. Definitivamente realice la mudanza en una transacción si llega a hacerlo.

Simplemente me vino a la mente que puedes verificar si realmente se usa un índice usando la declaración de explain . Eso debería mostrarle cómo MySQL está optimizando la consulta. Realmente no sé hoe MySQL optimiza las consultas, pero desde postgresql sé que debes explicar una consulta en una base de datos aproximadamente igual (en tamaño y datos) que la base de datos real. Entonces, si tiene una copia en la base de datos, cree un índice en la tabla y vea si realmente se usa. Como dije, lo dudo, pero definitivamente no lo sé todo :)

Answer 5

Si los datos se distribuyen como 50:50, la consulta como status="enabled" evitará la mitad del escaneo de la tabla.

Tener índice en tales tablas depende completamente de la distribución de datos, i, e: si las entradas que tienen el estado habilitado son 90% y otras 10%. y para consulta donde status="disabled" escanea solo el 10% de la tabla.

por lo tanto, tener índice en tales columnas depende de la distribución de los datos.

Answer 6

Jan, definitivamente deberías indexar esa columna. No estoy seguro del contexto de la cita, pero todo lo que dijo arriba es correcto. Sin un índice en esa columna, seguramente hará un escaneo de tabla en las filas de 300M, que es lo peor que puede hacer por esos datos.

Jan, como se le preguntó, donde su consulta involucra simplemente "donde estado = habilitado" sin algún otro factor limitante, un índice en esa columna aparentemente no ayudará (me alegro de que la comunidad SO me mostró qué sucede). Sin embargo, si hay un factor limitante, como "límite 10", un índice puede ayudar. Además, recuerde que los índices también se usan en grupo por orden por optimizaciones. Si está haciendo "select count (*), status from table group by status", un índice sería útil.

También debería considerar convertir el estado en una minúscula donde 0 representaría deshabilitado y 1 estaría habilitado. ¡Estás desperdiciando toneladas de espacio almacenando esa cadena contra una minúscula que solo requiere 1 byte por fila!