萌えキャラとは何だったのか

最近 DBIC を使ってコードを書いてるけどオブジェクトの永続化の功罪について想いを馳せることが多いmix3です

CREATE TABLE user_item (
  id           BIGINT  UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id      INTEGER UNSIGNED NOT NULL DEFAULT 0,
  item_id      INTEGER UNSIGNED NOT NULL DEFAULT 0,
  is_equiped   TINYINT UNSIGNED NOT NULL DEFAULT 0,
  is_protected TINYINT UNSIGNED NOT NULL DEFAULT 0,
  INDEX idx_1 (user_id, item_id, is_protected),
  INDEX idx_2 (user_id, item_id, is_protected, is_equiped),
  INDEX idx_3 (user_id, item_id, is_protected, id),
  INDEX idx_4 (user_id, item_id, is_protected, is_equiped, id),
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4;

こんなテーブルがあったとして、以下の優先順位で抽出したい

• is_protected = 1 は保護されているので除外
• id 順に SELECT
  • ただし is_equiped = 1 は SELECT の対象だが装備してるので優先度を低く

このとき user_id = 1, item_id = 1 のものを 2個 SELECT する場合

SELECT * FROM user_item
WHERE user_id = 1
AND   item_id = 1
AND   is_protected = 0
ORDER BY is_equiped, id ASC LIMIT 2;

多分こんなクエリになると思う　実際にINSERTして試してみる

INSERT into user_item
  (user_id, item_id, is_equiped, is_protected)
VALUES
  (1, 1, 1, 1), -- 装備中 & 保護されてる
  (1, 1, 1, 0), -- 装備中
  (1, 1, 0, 0),
  (1, 1, 0, 0),
  (1, 1, 0, 0),
  (1, 2, 0, 0);

mysql> SELECT * FROM user_item WHERE user_id = 1 AND item_id = 1 AND is_protected = 0 ORDER BY is_equiped, id ASC LIMIT 2;
+----+---------+---------+------------+--------------+
| id | user_id | item_id | is_equiped | is_protected |
+----+---------+---------+------------+--------------+
|  3 |       1 |       1 |          0 |            0 |
|  4 |       1 |       1 |          0 |            0 |
+----+---------+---------+------------+--------------+

期待通り id:1 は除外 id:2 は優先度低いので id:3,4 が帰ってきている

select for update

トランザクション内で select for update するとロックしてくれる

BEGIN;
SELECT * FROM user_item WHERE id = 1 FOR UPDATE;

ターミナルを二つ開いてそれぞれで上記SQLを流すと片方の SELECT が待たされる

ところで select for update に ORDER BY LIMIT が含まれる場合はどうなるのだろうか？

SELECT * FROM user_item FORCE INDEX (idx_N)
WHERE user_id = 1
AND   item_id = 1
AND   is_protected = 0
ORDER BY is_equiped, id ASC LIMIT 2 FOR UPDATE;

したあとに

SELECT * FROM user_item WHERE id = N FOR UPDATE;

を idx_1~4, id = 1 ~ 6 まで試してみた

	id=1	id=2	id=3	Id=4	id=5	id=6
idx_1		×	×	×	×
idx_2			×	×
idx_3	×	×	×	×	×
idx_4			×	×

こんな感じになった

ORDER BY や LIMIT などを含む select for update も index を張っていればロック範囲を最小限に抑えらえるようだ

ただ idx_3 は id=1 までロックされて変な index を張るとロックの範囲も変な感じになるようだ

idx_2 が idx_4 と同じ最小範囲のロックをしているのは id が primary key だからだろうか？

結論

index は大事