php - MySQL海量数据处理

我在处理数据库中的大数据时遇到以下问题：

基本上，每秒钟将来自数字传感器的所有计量都存储在数据库中。
报告应从所有数据中显示的只是发生的更改，例如，在时间X处寄存器1的值从0更改为1。

我创建了一个过程，该过程只能返回我需要的数据（更改），并且为我节省了很多php处理，但是最大的问题是，对于4天的当前数据，查询需要6 * N完成的秒数，其中N是所选寄存器的数量。

现在我想知道什么是克服此问题的最佳解决方案。

另一个想法是在数据计量的每个新插入上触发一个问题，但是问题是，这将变得更加复杂，因为我将需要研究在另一时间提交的先前的计量。

因此，我想创建一些视图，这些视图将在新数据以某种方式到达时自动更新。这意味着当请求报告时，数据将准备就绪并从视图中获取。

这将是一个好的解决方案吗？

最佳答案

只需一次查询就可以从现有数据中识别状态更改，但是（如您所发现的）代价很高。我敦促您将每个状态更改存储在缓存中。

作为@Fluffeh explained，如果使用合适的索引，从现有表中查找最新状态将不会很昂贵；因此触发方式应该是相当合理的。

因此：


定义一个合适的索引（如果尚不存在）：

ALTER TABLE existing_table ADD INDEX (register_id, timestamp);

为缓存创建一个表（并可以选择设置用户权限，以便您的应用程序不能直接对其进行修改）：

CREATE TABLE status_changes VALUES (
  register_id ...,
  timestamp   TIMESTAMP,
  old_status  ...,
  new_status  ...,

  PRIMARY KEY                (register_id, timestamp),

  FOREIGN KEY                (register_id, timestamp, old_status)
   REFERENCES existing_table (register_id, timestamp, status),

  FOREIGN KEY                (register_id, timestamp, new_status)
   REFERENCES existing_table (register_id, timestamp, status)
);

定义来自有权修改新表的用户的触发器：

DELIMITER ;;

CREATE TRIGGER record_change AFTER INSERT ON existing_table FOR EACH ROW
BEGIN
  DECLARE  _last_status ... ;

  SELECT   last.status
  INTO     _last_status
  FROM     existing_table AS last
  WHERE    last.register_id <=> NEW.register_id
       AND last.timestamp    <  NEW.timestamp
  ORDER BY last.timestamp DESC
  LIMIT    1;

  IF NOT NEW.status <=> _last_status THEN
    INSERT INTO status_changes (
      register_id,
      timestamp,
      old_status,
      new_status
    ) VALUES (
      NEW.register_id,
      NEW.timestamp,
      _last_status,
      NEW.status
    );
  END IF;
END;;

DELIMITER ;

根据历史数据填充新表：

INSERT IGNORE INTO status_changes (
  register_id,
  timestamp,
  old_status,
  new_status
)
SELECT NEW.register_id,
       NEW.timestamp,
       (
         SELECT   last.status
         FROM     existing_table AS last
         WHERE    last.register_id <=> NEW.register_id
              AND last.timestamp    <  NEW.timestamp
         ORDER BY last.timestamp DESC
         LIMIT    1
       ) AS _last_status,
       NEW.status
FROM   existing_table AS NEW
WHERE  NOT NEW.status <=> (
         SELECT   last.status
         FROM     existing_table AS last
         WHERE    last.register_id <=> NEW.register_id
              AND last.timestamp    <  NEW.timestamp
         ORDER BY last.timestamp DESC
         LIMIT    1
       )
;