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 C#调用python代码(python带有库)
  • ¥15 矩阵加法的规则是两个矩阵中对应位置的数的绝对值进行加和
  • ¥15 活动选择题。最多可以参加几个项目?
  • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型
  • ¥15 vs2019中数据导出问题
  • ¥20 云服务Linux系统TCP-MSS值修改?
  • ¥20 关于#单片机#的问题:项目:使用模拟iic与ov2640通讯环境:F407问题:读取的ID号总是0xff,自己调了调发现在读从机数据时,SDA线上并未有信号变化(语言-c语言)
  • ¥20 怎么在stm32门禁成品上增加查询记录功能
  • ¥15 Source insight编写代码后使用CCS5.2版本import之后,代码跳到注释行里面
  • ¥50 NT4.0系统 STOP:0X0000007B