ランキングのレガシーな設計をリビルド

エキサイトの武藤です。

エキサイトホールディングス Advent Calendar 2022の14日目の記事です。

https://qiita.com/advent-calendar/2022/excite-hd

担当しているサービスのランキング機能のリビルドを行いました。実現するにあたって、考慮したところを紹介します。

どのようなランキングか？

記事メディアを前提として、ランキングの仕様を説明します。下記の仕様を想定します。

記事ごとのPVやいいね数を集計したランキング
日次更新

以前のランキング機能のシステム概要

以前のランキング機能のシステム概要について説明します。

まず、バッチが集計を行い、ランキングデータを生成します。このとき、記事のタイトルや本文といった表示用のデータと合わせてランキングを生成します。

生成したランキングデータは、ファイルに書き出され、アプリケーションが実行しているサーバへ配布されます。アプリケーションは、ファイルを読み込んで、ランキングを表示します。

バッチは1日1回実行します。翌日のランキング更新では、前日と同名のファイルに上書きされます。アプリケーションは、配布し直されたファイルを同名で参照します。

この仕様によって、以下のデメリットがありました。

アプリケーションごとにファイルが配布されることによるデータの点在化
ファイルはランキング生成時のみに更新されるため、コンテンツのデータが更新されたときに、追従できない（手動でバッチを実行する等の運用が発生する）
ランキングのデータが蓄積されない

リビルドした仕様

上記のデメリット解消のため、システムフローの修正と新たにテーブル設計をしました。

システムフロー

特段、真新しいものではなく、シンプルな仕様を考えました。

ランキングデータをDBで管理する
アプリケーションのランキングの取得はAPI経由にする
APIのみがDBにアクセスする

DB管理にすることで、データが一元化されます。日付ごとに保存されるので、ランキングのデータが蓄積できます。 APIのみがDBにアクセスするようにし、データフローをシンプルにします。

ランキングの日付や順位は記事とは別のテーブルで管理します。必要に応じてコンテンツのテーブルをJOINして取得します。これによって、最新状態のコンテンツを取得できます。

テーブル設計

続いて、テーブルの設計です。記事とランキングテーブルです。

DBはMySQLを前提とします。

create table `article`
(
    `article_id`  varchar(255) not null,
    `title`       varchar(11)  not null,
    `body`        text         not null,
    `insert_date` datetime     not null default current_timestamp,
    primary key (`article_id`)
) ENGINE=InnoDB default CHARSET=utf8mb4;

create table `article_ranking_pv`
(
    `ranking_date` date         not null,
    `article_id`   varchar(255) not null,
    `pv`           int(11)      not null,
    `insert_date`  datetime     not null default current_timestamp,
    primary key (`ranking_date`, `article_id`),
    KEY            `ranking_date` (`ranking_date`,`pv`)
) ENGINE=InnoDB default CHARSET=utf8mb4;

artilce_ranking_pv では、ランキング日時と記事ID、集計時点のPVを持ちます。

次に、ランキングと記事を取得するSQLのEXPLAIN です。

explain
select ranking_date,
       article_id,
       title,
       pv,
       ( select count(r2.pv)
         from article_ranking_pv as r2
         where article_ranking_pv.pv < r2.pv
           and article_ranking_pv.ranking_date = r2.ranking_date ) + 1 as ranking_order
from article_ranking_pv
         inner join article using (article_id)
where ranking_date = '2022-12-08'
order by pv desc;