¿MySQL: dividir una tabla grande en particiones o tablas separadas?

Detalles del problema

tengo una base de datos mysql que contiene más de 20 tablas, pero una de ellas es muy grande porque recoge datos de medición de diferentes sensores. Tiene aproximadamente 145 GB de tamaño en disco y contiene más de 1.000 millones de registros. Todos estos datos también se copian a otro servidor mysql.
Quiero dividir los datos en trozos más pequeños, así que mi pregunta es cuál de las siguientes soluciones es mejor. Usaré la marca de tiempo del registro para dividir los datos por el año. Casi todas las consultas Select realizadas en esta tabla contienen el campo "timestamp"en la sección "where"de la consulta.
Estas son las soluciones que no puedo decidir:
  • utiliza particiones MySQL y divide los datos por años (por ejemplo, particiones 1 - 2010, particiones 2 - 2011, etc.)
  • crea una tabla separada y divide los datos por a ño (por ejemplo, tabla measure 2010, measure 2011, etc.)
  • ¿Hay alguna otra opción que no sepa?
    Sé que en el primer caso, mysql mismo obtendrá datos de "fragmentos", y en el segundo, tengo que escribir un envoltorio para ello y hacerlo yo mismo. ¿En el segundo caso, hay alguna otra manera de que todas las tablas individuales se consideren "una tabla grande"para obtener datos?
    Sé que alguien ha hecho esta pregunta en el pasado, pero tal vez alguien ha propuesto algunas nuevas soluciones (no lo sé), o las mejores prácticas han cambiado.
    Muchas gracias por tu ayuda.
    Editar:
    El patrón es similar a esto:
    device_id (INT)
    timestamp (DATETIME)
    sensor_1_temp (FLOAT)
    sensor_2_temp (FLOAT)
    etc. (30 more for instance)
    
    Todas las temperaturas del sensor se escriben cada minuto al mismo tiempo. Tenga en cuenta que aproximadamente 30 mediciones de sensores diferentes se registran en una fila. Estos datos se utilizan principalmente para mostrar gráficos y otros fines estadísticos. Bueno, si quieres una nueva respuesta, eso significa que probablemente ya has leído mi respuesta. Para los pocos casos de uso en los que la partición puede ayudar a mejorar el rendimiento, vea Partitioning blog. Suenas diferente de los cuatro casos.
    Contracción

    Detalles de la solución

    . device_id es de 4 bytes; ¿De verdad tienes millones de dispositivos? INT es un byte con un rango de 0.. 255.TINYINT UNSIGNED es de 2 bytes con un rango de 0. 64k. Esto reducirá la Mesa un poco.
    Si su verdadero problema es cómo manejar tantos datos, vamos a "pensar fuera de la Caja". Sigue leyendo.
    Dibujo... ¿Qué rango de fechas estás dibujando?
    ¿
  • última hora/día/semana/mes/año?
  • ¿
  • cualquier hora/día/semana/mes/año?
  • ¿
  • cualquier rango, sin límite de día/semana/mes/año?
  • ¿Qué estás dibujando?
    ¿
  • promedio diario?
  • ¿
  • máximo por minuto en un día?
  • ¿
  • candelabro (etc.) para uso diurno, semanal u otras ocasiones?
  • En cualquier caso, debe construir (y mantener incrementalmente) una tabla de resumen que contenga datos. Una línea contendrá una hora de información resumida. Sugiero
    CREATE TABLE Summary (
        device_id SMALLINT UNSIGNED NOT NULL,
        sensor_id TINYINT UNSIGNED NOT NULL,
        hr TIMESTAMP NOT NULL,
        avg_val FLOAT NOT NULL,
        min_val FLOAT NOT NULL,
        max_val FLOAT NOT NULL
        PRIMARY KEY (device_id, sensor_id, hr)
    ) ENGINE=InnoDB;
    
    Una tabla de resumen puede ser de 9 GB (para la cantidad actual de datos).
    SELECT hr,
           avg_val,
           min_val,
           max_val
        FROM Summary
        WHERE device_id = ?
          AND sensor_id = ?
          AND hr >= ?
          AND hr  < ? + INTERVAL 20 DAY;
    
    Le proporcionará 480 horas de valor hi/lo/AVG; ¿Suficiente para dibujar gráficos? Obtener 480 filas de una tabla de resumen es mucho más rápido que obtener 60 * 480 filas de la tabla de datos original.
    La obtención de datos similares durante un a ño puede sofocar un paquete de dibujo, por lo que puede ser necesario construir un resumen - resolución de un día. Es de aproximadamente 0,4 GB.
    Hay varias maneras diferentes de crear una tabla de resumen; Podemos discutirlo después de que hayas pensado en su belleza y leído Summary tables blog. Tal vez la mejor manera de recoger datos durante una hora y luego ampliar la tabla de resumen. Esto es un poco como el disparador discutido en my Staging table blog.
    ¿Si usted tiene un resumen por hora, realmente necesita datos por minuto? Piensa en tirarlo. O tal vez un mes después. Esto resulta en el uso de particiones, pero sólo para eliminar los beneficios de los datos antiguos, como se describe en el "Caso 1"de Partitioning blog. Es decir, tendrá particiones diarias, usando SMALLINT UNSIGNED y DROP cada noche para cambiar la hora de la tabla de hechos. Esto reducirá la huella de 145gb, pero no perderá demasiados datos. Nueva superficie: aproximadamente 12 GB (resumen por hora + detalles por minuto de los últimos 30 días)
    Nota: Summary Table blog muestra cómo obtener la desviación estándar.