姫ヒャクを支える技術(MySQL編)

tech kayac

Apr 11th, 2014

Profile

• id: Songmu
• Masayuki Matsuki

Recent CPAN Modules

• MySQL::Partition

MySQLの話をします

• InnoDBの話しかしません

ゆるふわアグレッシブ運用

• マスター一台で頑張る
• SLAVE参照とかなるべくしない。SLAVEはあくまでバックアップ
• RDSのMulti AZ最高
• データをメモリに載せきる
• シャーディングとかなにそれ

AGENDA

• テーブル設計
• データ量を抑える工夫
  • 論理削除を使わない
  • パーティション
• データの不整合を防ぐ仕組み
  • FOR UPDATEは銀の弾丸ではない(未完成)

テーブル設計

正規化

• 極力正規化しよう
• サロゲートキー + UNIQUEキー制約

リレーションについて

• 各テーブルはidカラムがpkになっている
• hoge.id <- hoge_id でのリレーション
  • 逆に外部キーではない時は *id は極力使わない (code とか)
  • 外部キー制約を使わない代わりに自動検出しやすくする
• リレーションが複数あるような場合は、カラムにprefixを付けて調整する
  • ex. user.id <- user_id, friend_user_id
  • friend_idとかにしてしまうのはNG
• user.user_id とかでもいいのかなーとか最近思い始めているがそういうレールを作るのがめんどくさい

外部キー制約

• 使わない
• パーティション切れなくなるのが痛い
• 余計なINDEXが作られる問題

データ型について

https://dev.mysql.com/doc/refman/5.6/en/storage-requirements.html

• 1レコード1byte削れると、1億行あるテーブルで、100MByteの節約になる
• データ量を気にしつつある程度規約ベースで考えるのが吉
• 細かいところで悩む位だったら規約を富豪側に倒したほうが良い

方針

• 数値系は基本的にunsigned
• マスタデータのid: SMALLINT
• トランザクション系のid: BIGINT
• フラグや数パターンしかないtypeなど: TINYINT
• データ量が少ないマスタデータでもidはTINYINTにはしない
• 日付はDATETIME(DATE)で構わない
  • MySQL5.6.4以降は5byteだし(マイクロ秒を格納しない限り)
• JSONを格納するカラムとかはカジュアルにTEXTを使う
  • ただし、TEXTの上限は実は65535byteだった!!!
  • MIDEUMTEXTとかLONGTEXTとかある
• 小数型は使わない。使うときはDECIMAL

767byte問題

ユニークキー制約をかける場合、そのフィールド長に上限が767byteになっている。 VARCHARにユニークキー制約をかける場合等に注意が必要

• UTF8だとVARCHAR(255)
• UTF8MB4だとVARHCAR(191)

逆にVARCHARのフィールドサイズはこれ決め打ちで良い。 (複合ユニークキーの場合もあるけど…)

データ量に関するバッドノウハウ

以下の様なことは超絶大規模でもない限りやらないでいい。

• TEXT系のテーブルにindexを張らない
  • 代わりにmurmurhashでi沿っていにそっちにINDEX張るとか
• 日付型を使わないでunsigned intを使う(2104年問題)

user.idはBIGINTにするかどうか問題

別にINTEGERで良くて基本的に男気の問題でしかない。パズドラでさえ3000万

ただ、それ以外のテーブルはBIGINTにしておいたほうが良い。

userに対して1対1のテーブル

ドメインに因って、userデータを分割する

• player
• player_coin
• player_coach
• player_stamina
• player_injury
• player_parctice
• player_status

こういうテーブルは、user_idがpkで良いと最近思い始めてきた。ただ、user_idだけに限ったほうが良いと思う

INDEXの張り方

• 必要以上に張らない
• 張る順番に気をつける
• B+ treeを意識して、どういう順番で絞り込んでいくかを考える
• カーディナリティの少ないカラムをINDEXに含めるときにどうするか問題

データ量を抑える工夫

論理削除を使わない

• MySQLのDELETEは遅いことで有名
• 最近のハードウェアならなんとかなる
• DELETEして履歴テーブルにINSERTする
• 履歴テーブルはパーティションを切って落とす

パーティションを活用する

• RANGEパーティション
  • Dailyで切る
  • id, created_atとかでpk
• LISTパーティション
  • event_id毎に切る
  • id, event_idとかでpk

姫ヒャクのテーブル構成

154テーブル

• マスタデータ 85テーブル
• Deilyパーティション 14テーブル
• LISTパーティション 19テーブル

TO_DAYS('2014-01-01')とかやらない

• RANGE COLUMNSパーティションを使う

5.1以前
ALTER TABLE PARTITION BY RANGE (TO_DAYS(created_at)) ( pname VALUES LESS THAN TO_DAYS('2014-04-01') )

5.5以降
ALTER TABLE PARTITION BY RANGE COLUMNS (created_at) ( pname VALUES LESS THAN '2014-04-01' )

カジュアルにINSERTする

• クエスト出撃毎にセッション用のレコードをINSERTする
• そのレコードのstateカラムを更新する

パーティションの注意点

• autoincrementのidのユニーク制が担保されなくなる
• idだけでロックをかけると乙る