カジュアルにテーブル全件持ってくる
※この項は、はてなダイアリーで書いた記事を若干書き直したものです
テーブルのデータを全件持ってきてあーだこーだする機会ってなんだかんだ年に数回はある気がします（主に補填とか）。
例えば、以下の様なテーブルがあったとします。
| id | name | data |
|---|---|---|
| 1 | aaaa | aaaa-aaaa |
| 2 | bbbb | bbbb-bbbb |
| 3 | cccc | cccc-cccc |
| 4 | dddd | dddd-dddd |
| ... | ... | ... |
| 10000000 | zzzz | zzzz-zzzz |
このテーブルからちょっとずつとってきて、なんか処理を入れて別のテーブルに入れたりするとき、普通は以下の様なクエリを書くんじゃないかと思いますが…
SELECT * FROM table LIMIT 0, 1000
SELECT * FROM table LIMIT 1000, 1000
...
SELECT * FROM table LIMIT 9999000, 1000
こうやってしまうと、実は MySQL の内部では以下の様な動作をしてデータを取ってきます。
mysql> EXPLAIN SELECT * FROM test_table ORDER BY id ASC LIMIT 900000,1000;
+----+-------------+-------------+-------+---------------+---------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+---------+---------+------+--------+-------+
| 1 | SIMPLE | test_table | index | NULL | PRIMARY | 8 | NULL | 901000 | |
+----+-------------+-------------+-------+---------------+---------+---------+------+--------+-------+
1 row in set (0.00 sec)
要するに OFFSET + LIMIT 分のデータを取ってきて、最後に LIMIT 分に絞り込む、というような動作です。
これだと、テーブルサイズが大きくなればなるほど超不利ですね。
そこで、もし上記のテーブルのように連番で id が振ってあるテーブルなのであれば、下記のようにクエリを書き換えることができます。
SELECT * FROM table WHERE id BETWEEN 1 AND 1000
SELECT * FROM table WHERE id BETWEEN 1001 AND 2000
...
SELECT * FROM table WHERE id BETWEEN 9999001 AND 10000000
mysql> EXPLAIN SELECT * FROM test_table WHERE id BETWEEN 900000 AND 901000;
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | test_table | range | PRIMARY | PRIMARY | 8 | NULL | 1000 | Using where |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
こうすれば、WHERE 句で絞り込んだ要素だけを取ってきてくれるので OFFSET と LIMIT を使うより圧倒的に速くなります。
カジュアルにテーブル設計する
テーブル設計って超大事ですよね。
でも、最近は横着してこういうテーブル作ることが結構ある気がします。
CREATE TABLE `campaign_info` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`status` tinyint(3) NOT NULL DEFAULT '0',
`start_time` datetime NOT NULL,
`end_time` datetime NOT NULL,
`campaign_info_attr` text,
`mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`ctime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `available_index` (`status`,`start_time`,`end_time`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4;
要は、WHERE 句で問い合わせそうなカラムだけ切り出しつつ、他の雑多なデータは info_attr という名のブラックボックスに JSON 形式で入れてしまう、というかなり乱雑な作りです。ちょっとリッチな KVS みたいな感じですね。
もちろん、無軌道に info_attr にいろんなデータを突っ込んでいくといつか破綻してしまうので、そのへんはどういうデータを入れるか？（入っているか？）というデータ構造をモデル層あたりで担保してやるといいと思います。
問題点としては、行サイズが大きくなりがちで、設計段階でのサイズが読めなくなってしまいますし、正規化が事実上困難になってしまうので、特にユーザー操作によって更新されるようなテーブルはきちんと設計して普通のテーブルを作ったほうが良いと思います。
カジュアルに楽観的ロックを取る
いわゆる楽観的ロックも MySQL を使えば超簡単。
まずは、以下の様なテーブルを作ります。
CREATE TABLE IF NOT EXISTS user_lock (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) unsigned NOT NULL,
`status` tinyint(3) NOT NULL DEFAULT '0',
`mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`ctime` timestamp NOT NULL DEFAULT '0000-00-00 00\:00\:00',
PRIMARY KEY (`id`),
UNIQUE KEY `campaign_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
そして、ロックを取りたい処理の前で以下のクエリーを流しましょう。
UPDATE user_lock SET status = 1 WHERE user_id = 1 AND status = 0
上記のクエリーを流して変更行数が1なら処理を続行、0（更新されなかった）なら処理を中断、という感じにすればよいでしょう。WHERE 句の status = 0 がミソで、もし status の値が変更されていたら該当の行は更新されないはずなので、これでユーザーが連打しまくったりしても安心ですね。
ついでに、処理が失敗したらまた0に戻し、処理が正常に終わったら0以外に status を変えてやれば状態管理的なこともできます。
（ちなみに、この処理をちょこっと変えたものを某ソーシャルゲームプラットフォームの某コイン還元キャンペーンの内部で使っており、今のところ変な挙動もなくちゃんと動いている…はずです）