Tengo que añadir índices a mi tabla (columnas) y encontré este post:

Cómo muchos de los índices de base de datos es demasiado?

Cita:
«Habiendo dicho eso, claramente se puede añadir un montón de sentido de los índices de una tabla que no hacer nada. La adición de B-Árbol de índices para una columna con 2 valores distintos será inútil, ya que no aporta nada en términos de mirar los datos. El más singular de los valores en una columna, más se va a beneficiar de un índice.»

Es un Índice realmente inútil si sólo hay dos valores distintos? Dada una tabla como la siguiente (Base de datos MySQL InnoDB)

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

Condiciones adicionales:

  • La Base de datos contiene 300 Millones de registros
  • Estado sólo puede ser «activado» y «desactivado»
  • De 150 Millones de discos han estado= activado y 150 Millones de registros han
    stauts= deshabilitado

Mi entendimiento es, sin tener un índice sobre el status, seleccione con where status=’enabled’ podría resultar en un completo tablescan con 300 Millones de Registros a procesar?

Que tan eficiente es la búsqueda cuando se utiliza un índice de ÁRBOL b en el estado?

Debo índice de esta columna o no?

Qué alternativas (tal vez otros índices) ¿MySQL InnoDB proporcionar a buscar eficientemente los registros por el «where estado=»habilitado» cláusula en el ejemplo dado con una muy baja cardinalidad/selectividad de los valores?

InformationsquelleAutor Jan | 2010-03-05

7 Comentarios

  1. 37

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

    La razón de esto está relacionado con la manera de una base de datos tiene acceso a una tabla. Las tablas pueden ser evaluados por un análisis completo de la tabla, donde cada bloque es de lectura y procesado en turno. O por un rowid o clave de búsqueda, donde la base de datos tiene una clave/rowid y lee la línea exacta que se requiere.

    En el caso de que usted utilice una cláusula where basado en la clave principal o de otro índice único, por ejemplo. where id = 1, la base de datos puede utilizar el índice para obtener una referencia exacta a donde la fila de datos se almacena. Este es claramente más eficiente que hacer un análisis completo de la tabla y el procesamiento de cada bloque.

    Ahora, volviendo a tu ejemplo, usted tiene una cláusula where de where status = 'enabled', el índice de retorno de 150 m filas y la base de datos se tiene que leer cada fila en vez de usar pequeñas lecturas. Mientras que el acceso a la tabla con un análisis completo de la tabla permite que la base de datos para hacer uso más eficiente de los más grandes lecturas.

    Hay un punto en el que es mejor hacer un análisis completo de la tabla en lugar de utilizar el índice. Con mysql se puede utilizar FORCE INDEX (idx_name) como parte de su consulta para permitir comparaciones entre cada tabla del método de acceso.

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

    • Yo veo lo que estás diciendo, pero por lo general hay otro factor limitante. Por ejemplo, digamos que añade límite de 10, luego de un índice es mejor, ¿no? Supongo que como se pide, a la derecha
  2. 11

    Siento decir que no estoy de acuerdo con Mike. La adición de un índice está destinado a limitar la cantidad de registros completos busca de MySQL, con la consiguiente limitación de IO que suele ser el cuello de botella.

    Esta indexación no es libre; usted paga por ella en inserciones y actualizaciones cuando el índice tiene que ser actualizado y en la búsqueda de sí mismo, ya que ahora tiene que cargar el archivo de índice (índice de texto completo de 300M registros probablemente no está en la memoria). Así que podría ser que le extra IO en lugar de limitting ella.

    Estoy de acuerdo con la afirmación de que una variable binaria es la mejor forma de almacenar como uno, un bool o tinyint, a medida que disminuye la longitud de una fila y puede, por tanto, el límite de e /s de disco, también comparaciones sobre los números son más rápidos.

    Si necesita velocidad y que rara vez uso la movilidad de los registros, es posible que desee tener 2 tablas, una para el habilitado y una para personas de movilidad de los registros y mover los registros cuando los cambios de estado. A medida que aumenta la complejidad y el riesgo que esta sería mi última opción, por supuesto. Definitivamente hacer el movimiento en la transacción de 1 si le sucede a ir a por ello.

    Que acaba de aparecer en mi cabeza que usted puede comprobar si un índice es en realidad utilizado mediante el explicar declaración. Debe mostrar cómo MySQL es la optimización de la consulta. Yo no sé realmente azada MySQL optimiza las consultas, pero a partir de postgresql yo sé que usted debe explicar una consulta en una base de datos de aproximadamente el mismo (en tamaño y datos) como la base de datos real. Así que si usted tiene una copia en la base de datos, crear un índice en la tabla y ver si realmente se usa. Como ya he dicho, no lo dudo, pero yo definitivamente no lo sabes todo:)

    • +1 El uso de cualquiera de las particiones o separados 2 tablas es una buena sugerencia.
    • Este es un buen debate. Estoy de acuerdo acerca de las particiones, pero sólo si él no desea que los registros independientemente de su estado. Si cada consulta implica una donde en el estado, la partición tiene sentido para mí.
    • También, la pena en insertar, en mi opinión, se ve compensado por el alza en seleccionar. Si el estado se convierte en un tinyint, y usted tiene una afinada correctamente mysql server, el archivo de índice de 300M registros cabe fácilmente en clave de búfer, después de un poco de tiempo de calentamiento.
    • Veo que, cuando se trata simplemente de donde el estado = deshabilitado, el índice sería peor. Gracias!
    • Muchas gracias por tu respuesta!
  3. 6

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

    Tener índice de tales tablas se depende por completo de la distribución de los datos, i,e : si las entradas de haber estado habilitado es de 90% y otros 10%. y para consulta de donde status="disabled" analiza sólo el 10% de la tabla.

    así que tener índice en tales columnas depende de la distribución de datos.

  4. 4

    Apenas tenemos todos los 150 millones de registros a la vez, así que supongo que «el estado» siempre va a ser utilizado en conjunción con otras columnas. Tal vez se haría más sentido utilizar un índice compuesto como (estado, fullname)

    • Esta respuesta no aborda la pregunta, tal como solicitó. Si él agregó que el índice, y ahora quiere hacer una búsqueda sólo por su apellido, es una exploración de la tabla. También, lo que si se quiere que el último movilidad diez registros? Como le preguntó, él quiere «, donde el estado=deshabilitado». La adición de fullname para el índice puede ser una sobrecarga innecesaria.
    • pero sólo si usted no utiliza where fullname like '%something%' como un índice no es útil en como con comodines en por tanto lados.
    • No, si el estado ocupa el primer lugar en el índice, NO tiene un índice de fullname. Orden de las columnas de la materia.
  5. 3

    Jan, usted debe definitivamente el índice de la columna. No estoy seguro de que el contexto de la cita, pero todo lo que se dijo anteriormente es correcta. Sin un índice en la columna, que son sin duda haciendo un recorrido de tabla en 300M de filas, que es la peor que usted puede hacer para que los datos.

    Jan, como pidió, donde su consulta implica, simplemente, «donde el estado=activado» sin algún otro factor limitante, un índice en la columna aparentemente no ayuda (me alegro de que ASÍ la comunidad me mostró lo que está arriba). Si, sin embargo, no es un factor limitante, como «límite de 10» de un índice puede ayudar. También, recuerde que los índices se utilizan también en el group by y order by optimizaciones. Si usted está haciendo «select count(*),el estatus de grupo de la tabla por el estado», un índice sería de gran ayuda.

    Usted debería considerar también la posibilidad de convertir el estado en un tinyint donde 0 representaría discapacitados y 1 sería habilitado. Estás perdiendo toneladas de espacio de almacenamiento de la cadena frente a un tinyint que sólo requiere 1 byte por cada fila!

    • Hmm, ¿por qué la downvote? ¿Estoy equivocada? Si es así, me encantaría saber por qué, ya que significaría que se me haya olvidado algo fundamental acerca de MySql y los índices.
    • estás seguro? ¿Y el hecho de que los índices se almacenan en la clave de amortiguamiento, en donde los datos no? También, no estoy seguro de lo que «saltar hacia atrás y adelante» significa.
    • Hola Mike, muchas Gracias por tu respuesta, fue muy útil para mí.
  6. 1

    @a r respuesta es correcta, sin embargo, es necesario señalar que la utilidad de un índice está dado no sólo por su cardinalidad, sino también por la distribución de los datos y de las consultas que se ejecutan en la base de datos.

    En la OP del caso, a 150 metros de los registros de tener status='enabled' y 150M de tener status='disabled', el índice es innecesario y una pérdida de recursos.

    En caso de 299M registros de tener status='enabled' y 1M tener status='disabled', el índice es útil (y se utiliza) en las consultas de tipo SELECT ... where status='disabled'.

    Las consultas de tipo SELECT ... where status='enabled' correrá con un análisis completo de la tabla.

  7. 0

    Tengo una columna similar en mi base de datos MySQL. Aproximadamente 4 millones de filas, con la distribución de 90% a entre 1 y 10% 0.

    Sólo he descubierto hoy que mis consultas (where column = 1) en realidad ejecutan mucho más rápido SIN el índice.

    Tontamente he eliminado el índice. Yo digo tontamente, porque ahora me sospechar de las consultas (where column = 0) puede tener todavía se benefició de ella. Así, en lugar debo decirle explícitamente MySQL ignorar el índice cuando estoy buscando 1, y usarlo cuando estoy buscando 0. Tal vez.

Dejar respuesta

Please enter your comment!
Please enter your name here