我正在为家电产品构建物联网系统。
我的数据表已创建为
mysql> SHOW CREATE TABLE DataM1G
*************************** 1. row ***************************
Table: DataM1
Create Table: CREATE TABLE `DataM1` (
`sensor_type` text,
`sensor_name` text,
`timestamp` datetime DEFAULT NULL,
`data_type` text,
`massimo` float DEFAULT NULL,
`minimo` float DEFAULT NULL,
KEY `timestamp_id` (`timestamp`) USING BTREE,
KEY `super_index_id` (`timestamp`,`sensor_name`(11),`data_type`(11)) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
并且查询是
SELECT
sensor_type, sensor_name, timestamp, data_type,
MAX(massimo) as massimo, MIN(minimo) as minimo
FROM DataM1
WHERE timestamp >= NOW() - INTERVAL 1 HOUR
GROUP BY timestamp, sensor_type, sensor_name, data_type;
现在的问题是,当表达到400万(几天(行时,查询需要50多秒。
编辑:EXPLAIN结果如下:
id: 1
select_type: SIMPLE
table: DataM1
partitions: p0,p1,p2,p3,p4,p5,p6
type: range
possible_keys: timestamp_id,super_index_id
key: timestamp_id
key_len: 6
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index condition; Using temporary; Using filesort
编辑:回复的示例行是:
*************************** 418037. row ***************************
sensor_type: SEN
sensor_name: SEN_N2
timestamp: 2016-10-16 17:28:48
data_type: flow_rate
massimo: 17533.8
minimo: 17533.5
编辑:我已将timestamp、sensor_type、sensor_name和data_type值标准化,并创建了一个视图(_V(以方便数据消费:
CREATE VIEW `_view` AS (
select (
select `vtmp`.`timestamp` from `timestamp` `vtmp` where (`vtmp`.`no` = `pm`.`timestamp`)) AS `timestamp`,(
select `vtmp`.`sensor_type` from `sensor_type` `vtmp` where (`vtmp`.`no` = `pm`.`sensor_type`)) AS `sensor_type`,(
select `vtmp`.`sensor_name` from `sensor_name` `vtmp` where (`vtmp`.`no` = `pm`.`sensor_name`)) AS `sensor_name`,(
select `vtmp`.`data_type` from `data_type` `vtmp` where (`vtmp`.`no` = `pm`.`data_type`)) AS `data_type`,
`pm`.`massimo` AS `massimo`,
`pm`.`minimo` AS `minimo`
from `datam1` `pm` order by `pm`.`timestamp` desc);
有没有一种方法可以加快索引、分片和/或分区的速度?还是重新思考一下将不同表格中的信息分开的表格更好?如果是这样的话,有人能在这种情况下提出他的最佳做法吗?
- 不要使用"前缀"索引,如
sensor_name(11)
;它很少有帮助,有时也很伤人 - 如果传感器名称和类型,并且data_type不能超过255个字符,则不要使用
TEXT
;而CCD_ 3具有一定的现实限制 - 规范化传感器名称和类型以及data_type——我认为它们重复了很多。
ENUM
是一个合理的选择 - KEY(时间戳(和KEY(时间戳记,…(是冗余的;去掉前者
- 你的桌子需要
PRIMARY KEY
。如果没有列(或列集(是唯一的,则使用AUTO_INCREMENT
- 也许您不希望使用确切的时间戳启动
GROUP BY
。也许缩短到小时?例如,CONCAT(LEFT(timestamp, 13), ':xx')
会产生类似2016-10-16 20:xx
的结果 - 查询花费很长时间的主要原因是它输出了418K行。你会怎么处理这么多行?我看不到
LIMIT
和ORDER BY
。这种情况还会继续吗 - 分区和分片对速度没有任何帮助
这些建议将在各个方面有所帮助。一旦你修复了其中的大部分,我们就可以讨论如何使用汇总表来获得10倍的加速。
此答案讨论如何构建汇总表。
CREATE TABLE Summary (
-- The primary key:
hr DATETIME NOT NULL COMMENT "Start of hour",
sensor_type ...,
sensor_name ...,
-- The aggregates being collected:
num_readings SMALLINT UNSIGNED NOT NULL,
sum_reading FLOAT NOT NULL, -- (maybe)
min_reading FLOAT NOT NULL,
max_reading FLOAT NOT NULL,
PRIMARY KEY(hr, sensor_type, sensor_name),
INDEX(sensor_name, hour) -- Maybe you want to look up by sensor?
) ENGINE=InnoDB;
每小时,用类似的东西填充
INSERT INTO Summary
(hr, sensor_type, sensor_name, num_readings,
sum_reading, min_reading, max_reading)
SELECT
FROM_UNIXTIME(3600 * (FLOOR(UNIX_TIMESTAMP() / 3600) - 1)), -- start of prev hour
sensor_type,
sensor_name,
COUNT(*), -- how many readings were taken in the hour.
SUM(??), -- maybe this is not practical, since you seem to have pairs of readings
MAX(massimo),
MIN(minimo)
FROM DataM1
WHERE `timestamp` >= FROM_UNIXTIME(3600 * (FLOOR(UNIX_TIMESTAMP() / 3600) - 1))
AND `timestamp` < FROM_UNIXTIME(3600 * (FLOOR(UNIX_TIMESTAMP() / 3600)));
这假设你每分钟都在阅读一次。如果你每小时只阅读一次,那么总结到一小时会更有意义。
更多讨论:汇总表。
为了更健壮,INSERT-SELECT摘要可能需要更复杂——如果你错过了一个小时怎么办。(还有其他可能出错的事情。(
注意事项:这个汇总表比从"事实"表中读取要快得多,但它只能显示基于整个小时的时间范围。如果你需要"最后60分钟",你需要进入事实表。
另一个注意事项:您应该在事实中规范庞大、重复的sensor_name
之类的东西,但在构建汇总表时,您可以(也许应该(去规范化。(我在这个例子中省略了这些步骤。(
对于获取昨天的数据:
SELECT sensor_type, sensor_name, data_type,
MAX(massimo) as massimo,
MIN(minimo) as minimo
FROM Summary
WHERE timestamp >= CURRENT_DATE() - INTERVAL 1 DAY
AND timestamp < CURRENT_DATE()
GROUP BY sensor_type, sensor_name, data_type;
整个六月:
WHERE timestamp >= '2016-06-01'
AND timestamp < '2016-06-01' + INTERVAL 1 MONTH
注意:获得平均值的简单方法是对平均值进行平均。但数学上正确的方法是求和,除以计数的和。因此我加入了sum_reading
和num_readings
。另一方面,当对天气读数等进行平均时,通常会得到每天的平均值,然后再计算几天的平均值。我让你来决定什么是"正确的"。
我认为这就是这样的用例,当你有这么多数据时,最好的解决方案可能是使用noSQL数据库,并在存储数据之前执行一些聚合。你可以看看谷歌大查询和云数据流
然而,为了回答您的问题,我会使用我的系统所需的最小粒度预先计算数据聚合(您可以每10分钟计算一次聚合(,然后您将能够对少量数据执行查询。