mizzsugar’s blog

日々感じていることや学んだことを書きます。エンジニアリング以外にも書くかもしれません。

「失敗から学ぶRDBの正しい歩き方」を読んで

この本の概要

RDBを使って開発する上で、よく起こる問題とその解決策が書かれています。 テーブル設計からMySQL/PostgreSQLの違いを踏まえたロックの仕組みなど、内容は多岐に渡ります。 1章あたりが短いため、サクサク読めます。「やりすぎたJSON」「聞かないINDEX」など章のタイトルがわかりやすいので、「近々INDEX設計をするからこの章を読もう」みたいな辞書的な使い方もできます。

gihyo.jp

おすすめしたい人

  • RDBMSを使ってなにかを作る上で、予め起こりうる問題を防ぎたい人(もしくはすでに問題が起こって困っている人)
  • 運用に耐えるテーブル設計ができるようになりたい人

感想

RDBを使って何か作っている方は、手元に1冊持っていることをおすすめします。 私は、RDBを使う中で何かあればこの本を手に取っています。開発の指針にもしており、大変助かっています。

また、本書の「はじめに」に、RDBの使い方のアンチパターンを学ぶ意義を以下のように書いています。RDBアンチパターンを学ぶ重要さが身にしみました。

データベースは長く付き合っていかねばならない相手であり、開発者はその特性ゆえの問題にぶつかることがあります。

(中略)

  • データベースの停止はサービスの停止を伴うことが多いため、メンテナンスしにくい
  • データは常に増え続け、リファクタリングしにくい
  • サービスの中核を担うため、変更による影響が大きい

特に学びになった章

4章: 効かないINDEX

  • INDEXが使われるには以下の条件が必要。
1. 検索結果がテーブル全体の20%未満
2. 検索対象のテーブルが十分に大きい(数万〜数十万行が目安) 
  • WHERE age * 10 > 100など検索対象の列を加工するとすべての行に対してage * 10を実行する必要ができてINDEXが使われないので、age > 100/10にするなどその列を計算しないようにします。
  • 統計情報のためのサンプリング前後で大量のデータの更新があったりすると、統計情報と実際のテーブルで乖離してINDEXが使われないことがあります。その場合は統計情報を更新します。

8章: JSONの甘い罠

  • JSON型のデメリット
* ORMが使えない(個人的に、DjangoやSQLAlchemyはJSON型サポートしているけど他の言語はどうなのか気になります。)
* データの整合性が保てない
* データの中身を指定できない
* 参照整合性制約を強制できない
  • JSON型のメリット
* JSONをそのものに対応している
* スキーマレスに値を保存できる
→Web APIのレスポンスなど仕様変更が頻繁なデータをエラーなく保存するのに役立つ。Web APIの場合、必要なデータは正規化してその他が含まれるJSONは別のテーブルに分ける設計もあり。
  • Entity Attribute ValueからJSON型にしない方がよい場合
* 正規化することができない
* JSONに対して頻繁に更新を行いたい
* 検索条件としてJSON内の属性を固定できない

13章: 知らないロック

MySQLPostgreSQLとではロックの仕様が異なります。

PostgreSQL

PostgreSQLではSELECTでも「AccessShareLock」という一番小さいレベルのロックを取るので、以下の場合はLOCKテーブルとデッドロックが発生します。 MySQLではLOCK TABLE実行時にそれまでのアクティブなトランザクションを暗黙的にコミットするので、デッドロックが起こりません。

-- トランザクションA
demo=# BEGIN;
BEGIN
demo=# SELECT * FROM demo;

-- トランザクションB
demo=# BEGIN;
BEGIN
demo=# SELECT * FROM demo;

-- トランザクションA
demo=# LOCK TABLE demo;
LOCK TABKE
-- ここでAとBのデッドロックが発生する
MySQL
ギャップロック
  • 「INDEXを持つ行」と「INDEX値を持つ行」の間にやるギャップ
  • 先頭の「INDEX値を持つ行」の前に存在するギャップ
  • 末尾の「INDEX値を持つ行」の後に存在するギャップ へのロック。
WHERE id > 3 AND id < 8

で、id=4,7の行が実際にある場合、 id=5~6がギャップロックの範囲。

ネクスキーロック

行ロックとその行の直前のギャップロックの組み合わせ。

-- トランザクションA
mysql > BEGIN;
mysql > UPDATE demo SET num = 2 WHERE id < 3;

-- トランザクションB
mysql > BEGIN;
mysql > UPDATE demo SET num = 3 WHERE id = 3;
-- ネクストキーロックによってトランザクションAではid=3の行もロックの対象になるので、この時点で待たされます。

14章: ロックの功罪

  • ロックは並列処理でデータの一貫性を守るためにあります。
  • トランザクション分離レベルは
1. read uncommited
2. read commited
3. repeatable read
4. serializable

があり、下に行くほど並列度が下がります。

ダーティーリード ファジーリード ファントムリード ロストアップデート
read uncommited 発生 発生 発生 発生
read commited 起きない 発生 発生 発生
repeatable read 起きない 起きない 発生 発生
serializable 起きない 起きない 起きない 起きない
  • ロストアップデートは複数トランザクションで更新が並列に行われた場合、後に実行されたトランザクションで結果が上書きされる現象。ロストアップデートはトランザクション分離レベルによって振る舞いが変わるため、運用途中で変更した場合、値がずれるなどSQLの結果が意図せず変更されることがあります。
-- repeatable readの場合
-- トランザクションA
BEGIN;
SELECT 価格 FROM 商品 WHERE id = 1; -- 1000

-- トランザクションB
BEGIN;
SELECT 価格 FROM 商品 WHERE id = 1; -- 1000
UPDATE 商品 SET 価格 = 価格+1 WHERE id = 1;
SELECT 価格 FROM 商品 WHERE id = 1; -- 1001
COMMIT:

-- トランザクションA
UPDATE 商品 SET 価格 = 価格 + 5 WHERE id = 1;
COMMIT;
SELECT 価格 FROM 商品 WHERE id = 1; -- 1005
-- repeatable readの場合ファジーリードが発生しないため、価格が1005になるが、read commitedの場合は1006になります。
  • PostgreSQL9.1以降とMySQLではrepeatable readでもファントムリードが発生しない
  • MySQLではSELECT ... FOR UPDATEでロックをした場合、repeatable readを利用していても直前にコミットされたレコードを返す
  • 並列度を維持しつつデータの一貫性を担保するには、repeatable readを設定している場合に一貫性を担保したい箇所でSELECT ... FOR UPDATEで排他ロックを取得してロストアップデートを防ぐなど工夫が必要。