シーケンサーのログをMySQLで保存する場合のテーブル構成

Question

長文の質問ですいません。
最大20台程度のPLC(シーケンサ)のデバイスログを格納するテーブル設計について質問です。

●デバイス
ビットデバイス　X, Y, M など ON/OFF値を16点単位で保存
ワードデバイス　D, W など 0000h～FFFFh値
取得するデバイスの種類や点数はPLCによって異なり、途中で変更もあります
※1PLC最大で、ビット値：160点(10ワード)、ワード値：20点程度
例：Y0010～Y002F, M0016～M0063, D0100～D0109,D0201,D0209

●サンプリング
周期ごとに対象デバイスの値をまとめて保存します
周期は１分単位でPLCによって異なります
収録期間は５年以上

●参照用途
任意の期間のPLCデバイスログのグラフ表示／CSV出力
※ビットデバイスは１点単位で選択
※複数PLCのログを同時に参照する必要はなし

素人ながら以下のようなテーブル構成を考えていますが、いまいちスッキリしません。

■PLC一覧
plc_list (
　plc_id  smallint
　interval_sec  int  // 収録周期秒
　title  varchar(64)　// PLC名称
)
■デバイス種別
plc_device (
　device_id  smallint
　device_type  varchar(8) // X, Y, M など名称
　data_type  smallint // 0=BIT, 1=WORD 
　※ビット値を16点のワードで持つ場合の判定用
)
■ログ（親）
plc_log (
　log_id  int
　plc_id  smallint
　log_date  datetime  // 収録時刻
)
■ログ（子）
plc_log_data (
　id  int   // 不要？
　log_id  int
　device_id  smallint  // デバイス種別
　device_no  smallint  // デバイス番号
　value  smallint  //　ログ値
)

期間が数年となると件数もかなりになり
・例：10件×1分×20PLC　１日：28万件、１年：１億件
検索絞り込み時の負荷が気になります。
どのようなテーブル構成にすべきかご意見下さい。
例えば

• ビットデバイスの定義をどうするか（１点単位または16点の16ビット値など）
• ログテーブルは１つにするか、PLC個別にするか

などです
また、INDEXはどう設定するのが良いでしょう。

よろしくお願いします。

Comments

[yuma.inaura]

ログテーブルが2個あるのって何故でしょう？

[akitomo3131]

説明が不足していました。
１度の処理で１周期分の保存を行うので、時刻やplc_idを全てのログ値に持たせるのは効率が良くないかと

Answer 1

その構成で良いんじゃないでしょうか。
集計が重くなってきたらまた別に集計済みテーブル的なものを定期作成する手段もあります。

インデックスの貼り方はクエリによりますが絞り込み対象の条件となるカラムに設定してはいかがでしょう。

あとデータ量が膨大になる場合のプライマリーキーはbigintで持っておくと良いかとしれません。

Comments

[akitomo3131]

回答、ありがとうございます。

このケースのように保存件数や種別が異なる場合、
一般的に拠点(PLC)毎にログデータを分けますか。
それとも１つの巨大なログデータから拠点(PLC)IDで絞り込みますか。
あまり個別に分けるのはデータベースとしてどうなのか
その辺りの経験がないので。

[yuma.inaura]

PLCに関しては存じないのですが
一般的には同種のデータを扱うのであれば同じテーブルにすると思います