doupang3062 2012-09-14 10:02
浏览 44
已采纳

MySQL海量数据操作处理

I am having the following problem processing big data in the database:

Basically all the metering from digital sensors are stored in the database for each second. What the reports should show from all that data is only the occurred changes, for example at time X the register #1 changed value from 0 to 1.

I have created a procedure that is able to return only the data i need (the changes), and that is saving me a lot of processing in php BUT the big problem is that for a current data of 4 days the query takes 6 * N seconds to complete where N is the number of the selected registers.

Now i was wondering what is the best solution to overcome this problem.

Another thought is to make a trigger on each new insert of the data metering but the problem is that this will be more complicated since i will need to look into the previous metering that were submitted at another time.

So I thought to create views that will be automatically updated when new data arrives in some way. That means when the request is made for the reports the data will be ready and fetched from the view.

Will this be a good solution?

  • 写回答

2条回答 默认 最新

  • dongli2000 2012-09-14 11:06
    关注

    Identifying status changes from your existing data is possible with a single query, but (as you have discovered) quite expensive. I would urge you to store each status change in a cache.

    As @Fluffeh explained, looking up the latest status from your existing table won't be very expensive if you use a suitable index; so the trigger approach ought to be quite reasonable.

    Therefore:

    1. Define a suitable index (if it does not already exist):

      ALTER TABLE existing_table ADD INDEX (register_id, timestamp);
      
    2. Create a table for the cache (and optionally set user permissions so that it cannot be directly modified by your application):

      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)
      );
      
    3. Define a trigger from a user that has permission to modify the new table:

      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 ;
      
    4. Populate the new table from the historical data:

      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
             )
      ;
      
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 下图接收小电路,谁知道原理
  • ¥15 装 pytorch 的时候出了好多问题,遇到这种情况怎么处理?
  • ¥20 IOS游览器某宝手机网页版自动立即购买JavaScript脚本
  • ¥15 手机接入宽带网线,如何释放宽带全部速度
  • ¥30 关于#r语言#的问题:如何对R语言中mfgarch包中构建的garch-midas模型进行样本内长期波动率预测和样本外长期波动率预测
  • ¥15 ETLCloud 处理json多层级问题
  • ¥15 matlab中使用gurobi时报错
  • ¥15 这个主板怎么能扩出一两个sata口
  • ¥15 不是,这到底错哪儿了😭
  • ¥15 2020长安杯与连接网探