MySQLでハイパフォーマンスなアクセスカウンター

MySQL を使ってアクセスカウンターを作ってみたいと思います。

テーマは

1, 高速(できうる限りカウンタの更新・参照を速くする)
2, 高信頼性（多重アクセスがあっても壊れない。不整合が起こらない）

です。

愚直に実装してみる。

まずは直感でザクっと作ります。

テーブル作成

CREATE TABLE counter (
 cnt int unsigned not null
) ENGINE=InnoDB;

参照

SELECT cnt FROM counter;

更新

UPDATE counter SET cnt = cnt + 1;

はい。解説の必要もないほどシンプルな内容だと思います。
ですが、このアクセスカウンターは高速とは言えません。
何故かというと、MySQLでは更新の際に対象の行にロックをかけるので、
例えば同時に100人アクセスがあったとしたら、100人同時にアクセスカウンターが
更新されるのではなく、1人ずつ処理されていきます。
最後の人は99人の更新が終わるまで待つんです。

工夫してみる

ちょっと工夫して、100人同時にアクセスがきたら、なるべく100人が同時に
アクセスカウンターを更新するようにしてみましょう。

テーブル作成

CREATE TABLE counter (
 id  int unsigned not null,
 cnt int unsigned not null,
 PRIMARY KEY (id)
) ENGINE=InnoDB;

参照

SELECT SUM(cnt) FROM counter;

更新

UPDATE counter SET cnt = cnt + 1 WHERE id = RAND() * 100;

テーブルを初期化する際に以下を id が 1 から 100 まで実行しておきます。

INSERT INTO counter (id, cnt) VALUES(?, 0);

先ほど問題だったのは、更新・参照する行が1行だけだったからです。
だから カウンタとして使う行を100行に増やし、更新する際は
100行の中からランダムに行を選ぶことで、同時アクセス時にロックされる行を
分散しています。

UPDATE時にテーブルをロックするのではなく、行のみをロックするInnoDBバンザイ！

参照する時は、100行に入ってる cnt をすべて SUM() します。

デイリーアクセス数を数えるカウンター

一日ごとにアクセス数を数えるカウンターを作りましょう。

テーブル作成

CREATE TABLE counter (
 date date         not null,
 id   int unsigned not null,
 cnt  int unsigned not null,
 PRIMARY KEY (date, id)
) ENGINE=InnoDB;

参照

SELECT SUM(cnt) FROM counter WHERE date = CURRENT_DATE();

更新

INSERT INTO counter (date, id, cnt) VALUES (CURRENT_DATE(), RAND() * 100, 1)
ON DUPLICATE KEY UPDATE cnt = cnt + 1;

テーブルに date カラムを追加しました。主キーも (date, id) となっています。
(id, date) でないことに注意してください。順番大事です。

何故か。InnoDB では主キーに設定したカラムに自動的にインデックスが作られます。
参照処理で where 句に date カラムが使われているのを見て下さい。
INDEX (date, id) は where 句に date のみ指定されていても使われるインデックスですが、
INDEX (id, date) は date のみ指定した where 句では使われません。

更新処理では ON DUPLICATE KEY を使用しています。
日付が変わって更新対象の行が存在しなければ INSERT を、存在していれば UPDATE を
行なっています。

一旦SELECT してみて、あれば UPDATE 無ければ INSERT といった処理をアプリ側で
書かないようにしましょう。そういう時は ON DUPLICATE KEY を使いましょう。
ネクストキーロックやギャップロックといった問題に悩まされる原因となります。

終わり

実践ハイパフォーマンスMySQL 第3版

全部この本からの受け売りです。