MySQLのテーブル設計で気をつけるべき点

MySQLと言えばRDBでおなじみですが、

今回はMySQLを使用して開発していた際に

テーブル設計で特に留意していた点に関してお話しようと思います。

 

留意点

  • インデックスは、可能ならUNIQUE KEYにする
  • text型に複数データを入れない
  • エンジンは、innoDBを使用する

 

インデックスは、可能ならUNIQUE KEYにする

まずは「インデックスは、可能ならUNIQUE KEYにする」に関して、お話ししようと思います。

無論、idなどPRIMARY KEY になるものに関しては例外ですが

それ以外に関しては、なるべくUNIQUE KEYにするべきです。

理由は、大きく2つあります。

  • 高速である
  • 重複データを許さない事により、データの整合性が取れる

の2つです。

高速

高速なのも、データの整合性も、指定の条件で取れたデータは必ず1件という部分にあるかと思います。

1件のため、高速なのはもちろん、ユーザごとに1つしか許さないデータ(例:ユーザの所持しているアイテムなど)は、

同タイミングでデータ生成処理が走ったとしても、おかしいデータが生成されない強みがあります。

余談ですが、1件だけの場合、データを追うときにも分かりやすいですよね。

 

text型に複数データを入れない

次に、「text型に複数データを入れない」に関して、お話しします。

 

text型に、複数データを入れた場合の問題点

〜様々な情報を管理したい。でも、カラムはあんまり増やしたくない。

text型を用い、データを複数入れられるようにすれば良い。

データを使用する際は、パーサー(改行区切りなど)を使用して、

データを取得・保存すれば良いのではないか〜

このような経験が、皆さんにもあるかと思います。

弊社でも、このような経験がありました。

しかし実際に運用してみますと、下記の問題が浮かび上がってきたのです。

  • text型内のデータに更新がかかった場合に、非常に低速なSQLになってしまう
  • text型の文字列長の最大を超えてデータを登録しようとした結果、データが壊れてしまう

そもそも、更新時のSQLが非常に長くなってしまうことにより、

データの更新速度も遅くなってしまいます。

亀に勝るとも劣らない速度

 

複数データを入れて運用してしまった場合の対処法と、その結果

この場合の対処方法として、下記で対応しました。

  • text型のデータが存在する場合は、別テーブルに移動させる。(区切り文字で、1レコード作成するイメージ)データ移動後は、該当カラムを空ないし移動させたことがわかるようにする
  • text型のデータが存在しない場合は、別テーブルを参照する

このように別テーブルにデータを移行し、データの一部を更新できるようにすることで、

text型の更新が遅かった問題を高速化することに成功しました。

びっしりと文字が埋まったノートを、一度すべて消しゴムで消して書き直すような状態から、

一部だけ消して、その部分だけ書き直せるようになった感じをご想像いただければと思います。

ノートと消しゴム

また「どのような内容が記録されているか」が分かりづらかったですが、

カラムごとに分割されたことにより、どのような内容が記録されているかが

分かりやすくなっているかと思います。

カラム名やカラム説明が、どのようなものを保存しているかを

大体表しているはずです。

 

エンジンは、innoDBを使用する

最後に、「エンジンは、innoDBを使用する」に関して、お話しします。

なぜinnoDBを使用するかと言いますと、

transactionと呼ばれる、

「指定のSQLから指定のSQLまでを実行させた場合に、途中でSQLが失敗したとしてもロールバックが可能な機能」

を利用することが可能だからです。

例えばですが、

transaction内でレコード作成→更新1→更新2→更新3の処理が行われていた場合に、

更新2で失敗したとしてもレコード作成が行われず、更新もなかったことになります。

 

ここで1つ注意点です。

このtransactionは、同一transaction内での指定のSQLから指定のSQLをチェックするので、

別transactionになっていると、ダメというところがあります。

取り返しのつかないことになる前に

今回は、こちらで以上となります。

ここまでご覧下さいまして、誠に有難う御座いました!

コメントを残す