总之，日期范围分区和内存配置实现了我的目标。

我需要增加分配给 的内存innodb_buffer_pool_size 因为默认的 8M 太低了。瑞克詹姆斯推荐 70% of RAM对于这个设置，他有很多很好的信息。

Edlerd 对这两个建议都是正确的:-)

我将数据拆分为每月分区，然后运行 ​​6,000 行响应查询，该查询最初需要 6 到 12 秒。现在它可以在不到一秒的时间内完成 (.984/.031)。我使用默认的 innodb 缓冲区大小(innodb_buffer_pool_size = 8M)运行它以确保它不仅仅是内存增加。

然后我设置 innodb_buffer_pool_size = 4G 并以 0.062/.032 的更好响应运行查询。

我还想提一下，增加内存还提高了我的 Web 应用程序和服务的整体速度，这些应用程序和服务接收消息并将消息写入此表，我对这个配置设置产生的差异感到震惊。我的 Web 服务器的第一个字节时间 (TTFB) 现在几乎与 MySQL Workbench 相当，有时会达到 20 秒。

我还发现slow query log file是一个很好的识别问题的工具，在那里我看到它表明我的 innodb_buffer_pool_size 很低，并突出显示了所有性能不佳的查询。这也确定了我需要索引其他表的区域。

编辑 2016-11-12 解决方案

我正在重构一个记录遥测数据的大表，它已经运行了大约 4-5 个月，并且已经生成了大约平均行大小约为 5400 万条记录。 380 字节。

我已经开始看到我的一个原始数据查询出现一些性能滞后，该查询返回设备在 24 小时内的所有日志。

一开始以为是索引，但我认为是MySQL需要处理的I/O量。典型的 24 小时查询将包含 2.2k 3k 到 9k 记录，我实际上希望支持大约 7 天的导出。

我在数据库性能调优方面没有经验，所以仍然只是学习绳索。我正在考虑一些策略。

CREATE TABLE `message_log` (
    `db_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `db_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created` datetime DEFAULT NULL,
    `device_id` int(10) unsigned NOT NULL,
    `display_name` varchar(50) DEFAULT NULL,
    `ignition` binary(1) DEFAULT NULL COMMENT 'This is actually IO8 from the falcom device',
    `sensor_a` float DEFAULT NULL,
    `sensor_b` float DEFAULT NULL,
    `lat` double DEFAULT NULL COMMENT 'default GPRMC format ddmm.mmmm \n',
    `lon` double DEFAULT NULL COMMENT 'default GPRMC longitude format dddmm.mmmm ',
    `heading` float DEFAULT NULL,
    `speed` float DEFAULT NULL,
    `pos_validity` char(1) DEFAULT NULL,
    `device_temp` float DEFAULT NULL,
    `device_volts` float DEFAULT NULL,
    `satellites` smallint(6) DEFAULT NULL, /* TINYINT will suffice */
    `navdist` double DEFAULT NULL,
    `navdist2` double DEFAULT NULL,
    `IO0` binary(1) DEFAULT NULL COMMENT 'Duress',
    `IO1` binary(1) DEFAULT NULL COMMENT 'Fridge On/Off',
    `IO2` binary(1) DEFAULT NULL COMMENT 'Not mapped',
    `msg_name` varchar(20) DEFAULT NULL, /* Will be removed */
    `msg_type` varchar(16) DEFAULT NULL, /* Will be removed */
    `msg_id` smallint(6) DEFAULT NULL,
    `raw` text, /* Not needed in primary query, considering adding to single table mapped to this ID or a UUID correlation ID to save on @ROWID query */
PRIMARY KEY (`db_id`),
KEY `Name` (`display_name`),
KEY `Created` (`created`),
KEY `DeviceID_AND_Created` (`device_id`,`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SELECT 
    ml.db_id, ml.db_created, ml.created, ml.device_id, ml.display_name, bin(ml.ignition) as `ignition`, 
    bin(ml.IO0) as `duress`, bin(ml.IO1) as `fridge`,ml.sensor_a, ml.sensor_b, ml.lat, ml.lon, ml.heading, 
    ml.speed,ml.pos_validity, ml.satellites, ml.navdist2, ml.navdist,ml.device_temp, ml.device_volts,ml.msg_id
FROM message_log ml 
WHERE ml.device_id = @IMEI
AND ml.created BETWEEN @STARTDATE AND DATE_ADD(@STARTDATE,INTERVAL 24 hour) 
ORDER BY ml.db_id;

最佳答案

Implement ranged partitioning by date: a) Keep monthly partitions. E.g. last 6 months b) Move anything older to archive table.

关于mysql - MySQL中如何优化大表，什么时候可以从分区中受益？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/40520162/