MySQLアンチパターン
社内勉強会でMySQLアンチパターンというスライドを作成しました。
基本的にはオライリーさんから出ている『SQLアンチパターン』の内容からチョイスしているのですが、1つだけオレオレアンチパターンを考えたので、せっかくなのでまとめてみました。
SpeakerDeckのリンクだけを載せるのもどうかなと思いますし。。
アンチパターン：One-track Minded
なんでもInnoDBなんでもInt
アンチパターン概要
- 全てのテーブルのストレージエンジンをInnoDBにする。
- 全ての整数型のカラムをInt型にする。
つまり、何の考えもなしに（バ◯のひとつ覚え=One-track Minded）ストレージエンジンや型を決める。
シーン（どういう時に困るか）
- 顧客から急に全文検索機能の実装を依頼された。
- ログテーブルが肥大化して、サロゲートキー（主キー）がオーバーフローした。
＼(^o^)／ｵﾜﾀ
デメリット（問題点）
InnoDB
- 全文検索が利用出来ない
- 参照処理がMyISAMに比べて遅い
- データサイズがMyISAMに比べて大きい
Int型
- 最高で40億桁程度”しか”格納出来ない
- データサイズが常に4バイト
銀の弾丸などない( ｰ`дｰ´)ｷﾘｯ
解決策
ストレージエンジンはInnoDB、整数型はIntをデフォルトとしつつ、用途に応じた選択をする。
（例）ログテーブルのストレージエンジンはMyISAM、サロゲートキーはBIGINTとする。
（例）フラグのカラムはTINYINT型とする。
ストレージエンジン / 整数型一覧
まずは知ることから始める。
ストレージエンジン一覧
| 種類 | 主な特徴 |
|---|---|
| InnoDB | トランザクションが利用可能 行単位のロック |
| MyISAM | 参照処理が高速 全文検索が可能 トランザクションが利用不可 |
| MERGE(MyISAM) | 複数テーブルを仮想的に1つにみせる 巨大なテーブル向け |
| MEMORY | インメモリ 参照処理が高速 |
| CSV | カンマ区切り SQL発行可能 |
| ARCHIVE | データを圧縮して保存 INSERTとSELECTのみをサポート |
| BLACKHOLE | 内部処理なし スレーブ側だけにデータを保存する |
| FEDERATED | 他のMySQLサーバーと連携する トランザクション利用不可 |
| EXAMPLE | ストレージエンジンのテンプレート（自作用） |
整数型一覧
| 種類 | 範囲 | サイズ |
|---|---|---|
| INT | 2147483648から2147483647 符号無しの場合0から4294967295 |
4byte |
| TINYINT | 128から127 符号無しの場合0から255 |
1byte |
| SMALLINT | 32768から32767 符号無しの場合0から65535 |
2byte |
| MEDIUMINT | 8388608から8388607 符号無しの場合0から16777215 |
3byte |
| BIGINT | 9223372036854775808から9223372036854775807 符号無しの場合0から18446744073709551615 |
8byte |
まとめ
InnoDBはとても優れたストレージエンジン（5.5系からデフォルトになったし）ですし、Int型も使いやすいデータ型ですので、基本的にはファーストチョイスになることは間違いありませんが、他の選択肢があることを知った上で（1秒でも）検討した上で利用してください。というお話です。