mysql - son - Usar marca de tiempo(o fecha y hora) como parte de la clave principal(o parte del índice agrupado)

llave unica base de datos

Question

Uso la siguiente consulta con frecuencia:

SELECT * FROM table WHERE Timestamp > [SomeTime] AND Timestamp < [SomeOtherTime] and publish = 1 and type = 2 order by Timestamp

Me gustaría optimizar esta consulta, y estoy pensando en colocar la marca de tiempo como parte de la clave principal para el índice agrupado, creo que si la marca de tiempo es parte de la clave principal, los datos insertados en la tabla escriben en el disco secuencialmente por campo de marca de tiempo. También creo esto mejora mucho mi consulta, pero no estoy seguro si esto ayudaría.

table has 3-4 million+ rows.
timestamp field never changed.
I use mysql 5.6.11

Un punto es: si esto mejora mi consulta, es mejor utilizar la marca de tiempo (4 bytes en mysql 5.6) o la fecha y hora (5 bytes en mysql 5.6).

Answer 1

1) Si los valores de fecha y hora son únicos, puede convertirlos en clave principal. De lo contrario, cree un índice en la columna de marca de tiempo como lo usa con frecuencia en "dónde".

2) usar la cláusula BETWEEN parece más natural aquí. Sugiero que use el índice TREE (tipo de índice predeterminado) no HASH.

3) cuando la columna de marca de tiempo está indexada, no necesita orden de llamada por - ya está ordenada. (por supuesto, si su índice es ÁRBOL no HASH).

4) integer unix_timestamp es mejor que datetime tanto desde el lado del uso de la memoria como desde el lado del rendimiento. La comparación de fechas es una operación más compleja que la comparación de números enteros.

La búsqueda de datos en el campo indexado toma O (registro (filas)) búsquedas de árbol. La comparación de enteros es O (1) y la comparación de fechas es O (date_string_length). Entonces, la diferencia es (número de búsquedas en árbol) * (difference_comparison) = O (date_string_length) / O (1)) * O (log (rows)) = O (date_string_length) * O (log (rows))

Answer 2

Cuatro millones de filas no son enormes.

Una diferencia de un byte entre los tipos de datos datetime y timestamp es lo último que debe considerar al elegir entre esos dos tipos de datos. Revise sus especificaciones

Hacer que una marca de tiempo sea parte de tu clave principal es una mala, mala idea. Piense en revisar qué significa clave primaria en una base de datos SQL.

Pon un índice en tu columna de marca de tiempo. Obtén un plan de ejecución y pégalo en tu pregunta. Determina el rendimiento de tu consulta mediana y pégalo también en tu pregunta.

Devolver las filas de un solo día de una tabla indexada de 4 millones de filas en mi computadora de escritorio requiere 2 ms. (Devuelve alrededor de 8000 filas)