mysql - tipos - Crear un índice en una marca de tiempo para optimizar la consulta

optimizar consultas mysql explain

Question

Tengo una consulta de la siguiente forma:

SELECT * FROM MyTable WHERE Timestamp > [SomeTime] AND Timestamp < [SomeOtherTime]

Me gustaría optimizar esta consulta, y estoy pensando en poner un índice en la marca de tiempo, pero no estoy seguro si esto ayudaría. Idealmente, me gustaría hacer que la marca de tiempo sea un índice agrupado, pero MySQL no admite índices agrupados, a excepción de las claves primarias.

• MyTable tiene 4 millones de filas.
• Timestamp es en realidad del tipo INT .
• Una vez que se ha insertado una fila, nunca se cambia.
• El número de filas con cualquier Timestamp dada es, en promedio, aproximadamente 20, pero podría ser tan alto como 200.
• Las filas recién insertadas tienen una Timestamp que es mayor que la mayoría de las filas existentes, pero podría ser menor que algunas de las filas más recientes.

¿Un índice en Timestamp me ayudaría a optimizar esta consulta?

Answer 1

Definitivamente deberías usar un índice. MySQL no tiene idea en qué orden están esas marcas de tiempo, y para encontrar un registro para una marca de tiempo determinada (o rango de marca de tiempo) necesita buscar en cada registro. Y con 4 millones de ellos, ¡es bastante tiempo! Los índices son su manera de decirle a MySQL sobre sus datos: "Voy a ver este campo con bastante frecuencia, así que mantenga una lista de dónde puedo encontrar los registros para cada valor".

Los índices en general son una buena idea para los campos consultados con regularidad. La única desventaja de la definición de índices es que usan espacio de almacenamiento adicional, por lo tanto, a menos que tenga poco espacio, debería intentar usarlos. Si no se aplican, MySQL simplemente los ignorará de todos modos.

Answer 2

No estoy en desacuerdo con la importancia de la indexación para mejorar los tiempos de consulta de selección, pero si puede indexar en otras claves (y formar sus consultas con estos índices), la necesidad de indexar en la marca de tiempo puede no ser necesaria.

Por ejemplo, si tiene una tabla con timestamp , category y userId , puede ser mejor crear un índice en userId . En una tabla con muchos usuarios diferentes, esto reducirá considerablemente el conjunto restante en el que buscar la marca de tiempo.

... y si no me equivoco, la ventaja de esto sería evitar la sobrecarga de crear el índice de marca de tiempo en cada inserción; en una tabla con altas tasas de inserción y marcas de tiempo altamente únicas, esto podría ser una consideración importante.

Estoy luchando con los mismos problemas de indexación basados ​​en marcas de tiempo y otras claves. Aún tengo que hacer pruebas para poder poner pruebas detrás de lo que digo aquí. Intentaré la devolución de datos en función de mis resultados.

Un escenario para una mejor explicación:

1. sello de tiempo 99% único
2. userId 80% único

3. categoría 25% único

   • La indexación en la marca de tiempo reducirá rápidamente los resultados de la consulta al 1% del tamaño de la tabla
   • La indexación en userId reducirá rápidamente los resultados de la consulta al 20% del tamaño de la tabla
   • La indexación en la categoría reducirá rápidamente los resultados de la consulta al 75% del tamaño de la tabla
   • La inserción con índices en la marca de tiempo tendrá una gran sobrecarga **
   • A pesar de que sabemos que nuestras inserciones respetarán el hecho de tener incrementos en las marcas de tiempo, no veo ninguna discusión sobre la optimización de MySQL basada en claves incrementales.
   • La inserción con índices en userId tendrá una sobrecarga razonablemente alta.
   • La inserción con índices en la categoría tendrá una sobrecarga razonablemente baja.

** Lo siento, no sé la sobrecarga calculada o la inserción con indexación.

Answer 3

Si sus consultas utilizan principalmente esta marca de tiempo, puede probar este diseño (ampliando la clave principal con la marca de tiempo como primera parte):

CREATE TABLE perf (
, ts INT NOT NULL
, oldPK
, ... other columns
, PRIMARY KEY(ts, oldPK)
, UNIQUE (oldPK)
) ENGINE=InnoDB ;

Esto asegurará que las consultas como la que publicó utilizarán la clave agrupada (primaria).

La desventaja es que sus insertos serán un poco más lentos. Además, si tiene otros índices en la tabla, usarán un poco más de espacio (ya que incluirán la clave primaria más amplia de 4 bytes).

La mayor ventaja de dicho índice agrupado es que las consultas con escaneos de gran alcance, por ejemplo, las consultas que tienen que leer grandes partes de la tabla o la tabla completa encontrarán las filas relacionadas secuencialmente y en el orden deseado ( BY timestamp ), que también ser útil si desea agrupar por día, semana, mes o año.

El antiguo PK aún se puede usar para identificar filas al mantener una restricción UNIQUE sobre él.

También es posible que desee echar un vistazo a TokuDB , una variante de MySQL (y de código abierto) que permite múltiples índices agrupados .

Answer 4

Sin dudas al respecto. Sin el índice, su consulta tiene que ver cada fila en la tabla. Con el índice, la consulta será más o menos instantánea en lo que respecta a la ubicación de las filas correctas. El precio que pagará es una leve disminución en el rendimiento de las inserciones; pero eso realmente será leve.