ふくだ学習録とは？
ふくだが学習したことの備忘録。
目に見える形で残すことによってやる気を出す個人的な作戦です。
他人に見せるように書いているわけではないので、すごく読みにくいです。
読了した本
データベースエンジニア養成読本 [DBを自由自在に活用するための知識とノウハウ満載!]
ゼロから作るDeepLearning
PHPフレームワーク CakePHP 3入門
今読んでいる本
SQLアンチパターンを読んで
８：マルチカラムアトリビュート（複数列属性）
例えば連絡先テーブル内で、電話番号を管理する時、携帯電話、固定電話以外に多くの電話番号（例えば事務所や会社、親族の電話番号など）を登録したいとなる場合がある。そういった列数の必要十分数が不確定な時に起こりうる問題。
アンチパターン：複数の列を定義する
複数の列を用意したところで、その列数が必要十分な数あるのかどうかに対して保証はできない。
さらに情報取得する時に、全ての列に対してクエリを投げなくてはいけなくなるため、効率も悪い。
解決策：従属テーブルを作成する。
ジェイウォークのパターンと同じで、従属テーブルを作成することで解決できる。
メタデータトリブル（メタデータ大増殖）
無限に属性列が増えていってしまう問題。
例えば、各年ごとの売上金情報などを、会社データテーブルに含めることになると、2020,2021,2022…といったように列数が増えていってしまい、クエリ実行速度が落ちていってしまう。
アンチパターン：テーブルや列をコピーする。
例えば、2020_sales2021_salesといったようにテーブルを複製していくのはよくない。
なぜなら、毎年新たなテーブル作成が必要になる（タスクが増える）し、元テーブルとの依存関係を毎年更新していく必要が出てくる。
また年をまたいでのデータ収集などもしにくくなる点もアンチパターンの理由の一つ。
解決策：パーティショニングと正規化を行う。
水平パーティショニング、垂直パーティショニング、従属テーブルを導入する。
水平パーティショニング
データべースを行単位で分割していく技術。
ただし論理的には1つのデータベースになっているため、クエリなどは簡単に投げることができる。
CREATE TABLE Bugs (
bug_id SERIAL PRIMALY KEY,
--他の列--
date_reported DATE
) PARTITION BY HASH ( YEAR(date_reported) )
PARTITIONS 4;
※最終行は、物理的にテーブルを分ける最大数を定義している。（今回は4つを最大値にしている。）
垂直パーティショニング
列単位でパーティショニングする。列の一部のサイズが大きい場合や、滅多に使用されない列が存在する時に有効。（例えばBLOB列やTEXT列などがある時）
ここ最近のRDBでは、自動的にBLOBやTEXTを、別テーブルにしてくれる機能があったりする。
従属テーブル
例でいうと、プロジェクトと年の組み合わせで主キー（つまり複合キー）を作成するようにして、一つのテーブルで管理するようにする。
９：ラウンディングエラー（丸め誤差）
データベース上で、小数点以下の計算を正しく行おうとして発生する問題。
金額の計算などの、誤差が発生すると大きな問題になる場合に気をつける必要がある。
アンチパターン：FLOAT型を用いる。
FLOAT型では、丸めが避けられない。なぜならば１０進数の全ての数字を２進数で正しく表現できるわけではないから。
解決策：NUMERICデータ型を使用する。
SQLデータ型のNUMERIC、またはDECIMALを使用する。
10:サーティーワンフレーバー
列に格納する値が限定しずらい時に発生する問題。
アンチパターン：限定する値を列定義で指定する。
有効なデータ型を列の定義時に指定するという方法はアンチパターン。
限定する値をデータで指定する。
参照テーブルを作成して、列に格納する値は全てそこから使用するようにする。
外部キーなどを用いて、参照整合性も確保しておく。（その場合、不使用になる行をDELETEするのではなく、ACTIVE列などを用いて、UPDATEで管理するようにする。）
11:ファントムファイル（幻のファイル）
画像などの大容量メディアファイルを格納する時に気をつけなければならない問題。
物理的にファイルを格納し、データベース上にはファイルへのパスのみを格納している時などに起こりうる可能性がある。
アンチパターン：物理ファイルの使用を必須と思い込む
例えば、データベース上のパス情報を削除しても、ファイルはそのまま残り続けることになるため、ガベージコレクション問題が起こってしまう。
また自動的にファイルを削除するようなシステムを組んでいたとしても、トランザクション処理問題やロールバック対応などが難しくなってしまう。
アンチパターンを用いてもいい場合
- データベース容量を減らしたい場合
- バックアップ時間を短時間にしておきたい場合
- 画像に対してのプレビューや編集を容易に行いたい場合
解決策：必要に応じてBLOB型を使用することを検討する。
外部ファイルを使用することが「必ずしも悪」というわけではない。常に選択肢を複数持って検討することが重要。
12:インデックスショットガン（闇雲インデックス）
何にでも全ての列にインデックスを貼る問題（やっちゃいそー、、、）
解決策￥：「MENTOR」の原則に基づいて効果的なインデックス管理を行う
MENTORとは、Measure(測定),Explain（解析）,Nominate（指名）,Test（テスト）,Optimize（最適化）,Rebuild（再構築）の略。
13:フィア・オブ・ジ・アンノウン（恐怖のunknown）
欠けている値を区別できない問題。NULLを含む列に対して適切にアプローチできない時のことを指している。
SQLではnullやunknownやinapplicableに相当する値をNULLで表現している。
アンチパターン：NULLを一般地として扱う。
例えば下記のようなSQLの場合。hoursがNULLだったとしてもクエリは１０で返ってこない。NULLで返ってくる。
SELECT hours + 10 FROM BUGS;
理由は、NULLはゼロではないため。unknownよりも１０大きな数はunknownになるから。
（他にも。文字列などと結合したらNULLになって返ってくる。）
またNULLを用いた比較も、全てunknownで返ってきてしまうため、比較で検索することも容易ではない。
解決策：NULLを一意な値として扱う
SQLでは３値論理という振る舞いがある。
↓わかりやすい！
【新人教育 資料】第2章 SQLへの道 〜3値論理編〜
14:アンビギュアスグループ（曖昧なグループ）
グループ内の最大値（または最小値や平均値）を取得する時に発生する可能性のある問題。
最大値が見つかった行の、他の値も正しく取得することができない時に発生する。
アンチパターン：非グループ化列を参照する。
GROUP BY句でグループ化してSELECTで単一値で取得してこれるのは、GROUP BY句で指定していた値のみで、それに紐づいている別列の情報はとってこれていない。（保証していない）
アンチパターンを使用してもいいとき
主キーを用いてグループ化する時。（関数従属性がある時）
ただし理論的には問題はないものの、実際のデータベースではエラーとなって返ってくることがほとんど。
解決策
曖昧でない列を使用する。例えば主キーでGRUOP BY句を使用する。
またはサブクエリ、導出テーブルを使用する（こっちの現実的なイメージ）
15:ランダムセレクション
ランダムな値を返す時に生じる恐れがある問題。
アプリケーション側に全てのデータを取得して、その中からサンプルを選ぶ方式ではなく、直接データベースからクエリでサンプル取得できるようにするのが目的。
アンチパターン：データをランダムにソートする
ランダムにソートすることにより、クエリ実行時にインデックスを使用できなくなってしまう。そのためパフォーマンスが落ちてしまう。
またランダムソートを行う時に必要になる結果は、最初の１行のみの場合が多い。２行目以下は不要（無駄）なため、それもパフォーマンスに悪い。
アンチパターンを用いても良い時
行数が少ない時。
解決策：特定の順番に依存しないようにする
- １と最大値の間のランダムなキー値を選択する
→この方式の場合は、欠番がないようにしないといけない。
欠番がある場合は、ランダムに選ばれた値と行が一致しない可能性が出てくるから。
- 全てのキー値をリストに受け取り、ランダムに1つ選択する。
→クエリを2回実行する必要があり、かつ番号が多くなると重くなる可能性がある。
※大体のRDBには、独自の解決策を提供してくれている。それを使用するのが一番いい。
16:プアマンズ・サーチエンジン（貧者のサーチエンジン）
全文検索を行う時に考慮すべき問題。
短い文字列（検索文字列）と、長い文字列（データベースに格納されている文字列）を比較して、検出する時に意識すべきもの。
アンチパターン：パターンマッチ述語を使用する
LIKE述語を用いるようなパターンマッチ検索は、インデックスのメリットを得られなくなってしまうため避けるべき。
解決策：適切なツールを用いるべき
つまり全文検索エンジンを使用するべき。基本的にはそのRDBが提供しているものを使用すればいいが、独自で作成したいときは転置インデックスを作る。
17:スパゲッティクエリ
同時に複数の結果を返すクエリを書いてしまい、かつそのクエリ結果が適切なものになっていない状態を指す。
アンチパターン：複雑な問題をワンステップで解決しようとしてはならない
RDBの特性上、リレーション関係がないテーブルを結合すると、デカルト積で返ってきてしまう。
その結果、計算パフォーマンスが悪くなるだけでなく、計算結果も適切な値が返ってこなくなる可能性が高い（正しくはデカルト積になっていることを考慮したSQLを書くことができない）
解決策：分割統治を行う
ワンステップずつ処理していく。
またはどうしても１クエリで処理してしまいたい時には、UNIONを用いて1つの結果セットにまとめる。
インプリシットカラム（暗黙の列）
出来るだけタイプ数を減らしたいがために、正しいデータが取得できないことを指す問題。
（例えば、結合したいテーブル内に同じ名前の列名がある場合など）
アンチパターン：ショートカットの罠に陥る
ワイルドカードの使用や、暗黙的な列指定により、タイプ数は減らせるが、リファクタリングがしずらくなる。
解決策：列名を明示的に指定する
ワイルドカードや暗黙的な列指定は、できるだけ使用せずに明示的に指定するようにする。
そのほうが無駄なデータトラフィックが減るので、パフォーマンスが良くなるし、リファクタリングも容易になる。
リーダブルコード（読み取り可能パスワード）
パスワードを忘れた際のために、リカバリーとリセットを考慮しておく時に発生する可能性がある問題。
アンチパターン：パスワードを平文で格納する。
当たり前にだめ。
解決策：ソルトをつけてパスワードハッシュを格納する
ソルトをつけてからハッシュ化させることで、攻撃に対して強くなる。（試行回数が飛躍的に伸びる）
レインボーテーブルと呼ばれる攻撃には２０文字以上のソルトが必要で、かつ各パスワードごとに異なるソルトを用意する必要がある。
SQlインジェクション
動的SQLを使用しているがために起こりうる問題。
脆弱なシステムに対して、攻撃者が意図的にSQL文を作成してデータベースから情報を取得する。
解決策：誰も信用してはならない
様々な段階で、適切な処理を行なっておくことで、SQLインジェクションを防ぐしかない。
入力時のフィルタリングや、動的値を用いる時はプリペアドステートメントを用いる。またはユーザーの入力値とSQLクエリを引き離す（アプリケーションで関数定義し、その関数の返り値を渡すようにする）などする。
今日の一言
SQLアンチパターンを読んで思ったけど、この本に書いてるのは結構基礎的なことやわ（笑）
というか「基礎こそ重要」ってことなんかも。
とはいえ、やっぱり基礎ばっかり勉強してても仕方ない部分はあるから、使い方勉強するためにも来週土日からはアプリ制作入る！！平日は本で勉強（知識インプット）休日はアプリ制作（アウトプット）のすみ分けで進める！