MySQL海量数据操作处理 - MySQL Massive Data Manipulation Processing

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

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

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

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

另一个想法是对数据计量的每个新插入进行触发，但问题是这将更加复杂，因为我需要查看在其他时间提交的以前的计量。

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

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

通过一个查询就可以从现有数据中识别状态更改，但（正如您所发现的）非常昂贵。我建议您将每个状态更改存储在缓存中。

正如@Fluffeh所解释的，如果你使用合适的索引，从现有表中查找最新状态不会很昂贵；因此，触发方法应该是相当合理的。

因此：

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

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
       )
;

我假设您的表被很好地索引，并且您的查询很好地使用了这些索引？

在这种情况下，您似乎从复合索引中受益最大——日期和注册都有一个。每一个都有一个索引会有所帮助，但两者的综合索引会帮助更多。

添加复合索引的语法为：

alter table yourTableName add index yourIndexName(col1, col2);
mysql> select * from table1;
+---------+------+------+-------------+
| autonum | ID   | name | metavalue   |
+---------+------+------+-------------+
|       1 |    1 | Rose | Drinker     |
|       2 |    1 | Rose | Nice Person |
|       3 |    1 | Rose | Runner      |
|       4 |    2 | Gary | Player      |
|       5 |    2 | Gary | Funny       |
|       6 |    2 | Gary | NULL        |
|       7 |    2 | Gary | Smelly      |
+---------+------+------+-------------+
7 rows in set (0.01 sec)
mysql> alter table table1 add index autoNumID(autonum, ID);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

你可以研究的另一件事是制作一个更新一次的汇总表（每小时或每天等）。使用CRON或其他方法来运行查询，该查询将创建一个数据摘要到一个小得多的表中，以便您的报告工作。