MySQL にまつわるカジュアルなあれこれ

## カジュアルにテーブル全件持ってくる
**※この項は、[はてなダイアリーで書いた記事](http://d.hatena.ne.jp/pullphone/20120718/1342611893)を若干書き直したものです**

テーブルのデータを全件持ってきてあーだこーだする機会ってなんだかんだ年に数回はある気がします（主に補填とか）。

例えば、以下の様なテーブルがあったとします。

| id       | name | data      |
| :------  | :--- | :-------- |
| 1        | aaaa | aaaa-aaaa |
| 2        | bbbb | bbbb-bbbb |
| 3        | cccc | cccc-cccc |
| 4        | dddd | dddd-dddd |
| ...      | ...  | ...       |
| 10000000 | zzzz | zzzz-zzzz |

このテーブルからちょっとずつとってきて、なんか処理を入れて別のテーブルに入れたりするとき、普通は以下の様なクエリを書くんじゃないかと思いますが…

```mysql
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 が振ってあるテーブルなのであれば、下記のようにクエリを書き換えることができます。

```mysql
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``` を使うより圧倒的に速くなります。

## カジュアルにテーブル設計する
テーブル設計って超大事ですよね。

でも、最近は横着してこういうテーブル作ることが結構ある気がします。

```mysql
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 を使えば超簡単。

まずは、以下の様なテーブルを作ります。

```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;
```

そして、ロックを取りたい処理の前で以下のクエリーを流しましょう。

```mysql
UPDATE user_lock SET status = 1 WHERE user_id = 1 AND status = 0
```

上記のクエリーを流して変更行数が1なら処理を続行、0（更新されなかった）なら処理を中断、という感じにすればよいでしょう。WHERE 句の ```status = 0``` がミソで、もし ```status``` の値が変更されていたら該当の行は更新されないはずなので、これでユーザーが連打しまくったりしても安心ですね。

ついでに、処理が失敗したらまた0に戻し、処理が正常に終わったら0以外に ```status``` を変えてやれば状態管理的なこともできます。

（ちなみに、この処理をちょこっと変えたものを某ソーシャルゲームプラットフォームの某コイン還元キャンペーンの内部で使っており、今のところ変な挙動もなくちゃんと動いている…はずです）