キャッシュカラムによる SQL 最新値検索 – ソフトウェア技術者(ときどき科学)のつぶやき

以下のようなテーブルがあるとします。

CREATE TABLE nodes (id INTEGER PRIMARY KEY AUTO_INCREMENT);
CREATE TABLE graphs (id INTEGER PRIMAR KEY AUTO_INCREMENT);
CREATE TABLE logs (id INTEGER PRIMARY KEY AUTO_INCREMENT, node_id INTEGER NOT NULL, created_at DATETIME DEFAULT now());
CREATE TABLE graph_log (id INTEGER PRIMARY KEY AUTO_INCREMENT, graph_id INTEGER NOT NULL, log_id INTEGER NOT NULL);

logs レコードが追加されるたびに graphs への追加が判定され、graph_log に結果が追加されるとします。

この時に、「各 node について最新のlogsレコードに紐づく graphs.id を取得する」には、以下のような SQL 文を書くと思います。

SELECT l.node_id, g.id graph_id FROM graphs g INNER JOIN graph_log gl ON gl.graph_id=g.id INNER JOIN logs l ON gl.log_id=l.id GROUP BY l.node_id LEFT JOIN (SELECT node_id, MAX(id) id FROM logs GROUP BY node_id) lc ON lc.node_id=l.node_id AND lc.log_id=l.id;

しかし、graph_log が数千万行存在する場合、INDEX を張っても検索に時間がかかりすぎることがあります。

しばらく考えましたがうまい解決策はないので、おきて破りとなりますが、nodes テーブルに最新のlog_id を保存するカラムを追加し、logs を追加したときに、このカラムも更新することとしました。Laravel で書くと以下のようになります。

ALTER TABLE nodes ADD COLUMN max_log_id INTEGER;

public function createLog($node_id, $graphs_id) {
  $log = new Log;
  $log->node_id = $node_id;
  $log->save();
  if ($graphs_id !== null) {
    $log->graphs()->sync($graphs_id);
  }
  $node = Node::find($node_id);
  if ($node) {
    $node->max_log_id = $log->id;
    $node->save();
  }
}

こうすると、最新 graphs.id の検索は単純化されます。

SELECT n.id, gl.graph_id FROM nodes n LEFT JOIN graph_log gl WHERE n.max_log_id=gl.log_id;

実測したところ、以前の書き方だと30秒程度要していたのが数百ミリ秒で返されるようになりました。

コメントを残す コメントをキャンセル

コメントを残すコメントをキャンセル