Chapter3(1)

Reading?

Chapter 3
- 3.1 Choosing Optimal Data Types
- 3.2 Index basics
  - Type of indexes

Chapter 3

適当にインデックス張ると痛い目みるぜ！
index を張ると，search 速度と update 速度はトレードオフになる．
ex) update 時， index (B tree など) も更新する必要が生まれるから
database scheme の big picture だけじゃなくて detail を把握している必要がある．
query optimization, server tuning の章を見たら戻ってきてね．

3.1 Choosing Optimal Data Types

色々なデータ型があるけど，以下の指針で選ぶと良い．

 1. 小さい方が良い．- ディスク，メモリ，CPUキャッシュに効いてくる．でも，オーバーフローしちゃうような危険性は排除した方がいい．
 2. 単純な方が良い．- (例えば，string よりも int の方が) アクセスする CPU サイクルが小さくて済む．
 3. NULL は使うな． - NULL 絡みの処理は重い(複雑)．zero とか， ""とか，替わりの値を使った方が早い．# ただし，新たに indexing するようなときに限る

3.1.1 Whole Numbers

TINYINT, SMALLINT, MEDIUMINT, INT, or BIGINT
TINYINT UNSIGNED
とかできる.

INNT(11) などとしてビット幅を決定できるけど，storage は無視する．
MySQL tools みたいなクライアントがチェックする場合がある

Falcon storage は integer をオレオレ定義で持っている．
Brighthouse も同様．

3.1.2 Real Numbers

FLOAT, DOUBLE :
DECIMAL : 5.0以降だと精度保証されてるが，IEEE の浮動小数点演算形式でサポートされていないので，native で計算できない．
よってソフトウェアで計算する必要が生まれるので遅い．

4.1 より前だと，DECIMAL value をfloat で計算するのでおかしい動きをする．# storage type??

9 ケタで 4 バイト使う．
DECIMAL(18, 9) だと，小数点前で4バイト，小数点自体で1バイト，小数点後で4バイト．
最大 65ケタ．
FLOAT は 4 byte, DOUBLE は 8 byte．

DECIMAL 型は空間効率的にも計算効率的にも重いのでなるべく使わない方がいいよ<S

3.1.3 String Types

5.0 と 4.1 で大分ちがうけど，かなり特徴がある．
4.1 から，各カラムが文字列の集合を持てるようになったり，並び順を定義したり，照合の仕方を定義したりできるようになった．
この辺りがパフォーマンスに効いてくる．
かなり細かく設定できる．
order by で並べるときに，文字コードで比較が行われて思った結果がでない場合がある．
全角のダッシュは DB に突っ込むとやばい # 化ける
全角の奪取 # 取り戻す必要がある

mysql> SELECT 'ä' LIKE 'ae' COLLATE latin1_german2_ci;

-----------------------------------------+
'ä' LIKE 'ae' COLLATE latin1_german2_ci
-----------------------------------------+
0
-----------------------------------------+ みたいな．http://dev.mysql.com/doc/refman/5.1/ja/string-comparison-functions.html参考文献私と文字化け
http://txqz.net/genre/文字化け

* 3.1.3.1 VARCHAR と CHAR

エンジン依存なので，具体的にどう保存されているかを一般的に説明するのはムリ．
InnoDB / MyISAM を使っていると仮定
しかもディスクでの保存形式にのみ言及． # メモリ上だとまた別の形式らしい

VARCHAR
MyISAM で ROW_FORMAT = FIXED にすると，固定長の文字列にすることもできる．
スペースの節約が可能 # TokyoCabinet も同じような Fixed length DB があったかも．

VARCHAR には，1-2バイトの文字列長を表すメタデータがくっつく．
基本的にデータがちっちゃく納められるけど，UPDATE かけたりすると長さが変わって
スペースに無駄ができたりする．
MyISAM は断片化を防ぐために， ROW を分割したりする．

すっげー長い文字列を保存するときにはいいかも...UPDATEがないから:)

5.0 以降は trailing spaces を保存できるが，4.1では trailing spaces は切り詰められる．
trailing spaces -> 末尾にある空白．後の実験を見るとよく分かる．

CHAR
固定長文字列．
MD5のハッシュを保存したり，固定長IDを保存したりするのにイイ．
空間効率が VARCHAR より良い(長さを表すメタデータがないから) が，
頻繁に長さが変更される場合には VARCHARの方が良い．

trailing spaces の話は storage engine 固有の話．

BINARY, VARBINARY
空いた箇所を \0 で埋める．

BLOB and TEXT
これまで詳解されてきた型と異なって，データをそのまま保存する．
TEXT は文字列照合(ハッシュ値でもとるのかな)と文字セットを保持するが，
BLOB は保持しない．

ENUM
index が 65535 なので，最大で2バイトで表すことができる．

a + 0 って，何？

fish

apple

dog

1 2 3 4
棒線のところのインデックスを .frm に保存するので，テーブル内の要素の大きさを
抑えることができる． => メリット？

order by すると，内部インデックスでソートされるみたい．

VARCHAR - VARCHAR間, ENUM - ENUM 間の join は高速．
=> 何故か? join したときの挙動が分からないので聞く．

Date & Time Types

MySQL の時計は，普通は秒単位が一番細かい精度．
でも，アプリによってはマイクロ秒単位の修正が必要な場合がある．

DATETIME YYYYMMDDHHMMSS形式で 8バイト使って格納．
1001 - 9999年まで表せるらしい．
TIMESTAMP かい精度 1970 - 2038 年まで表せる．4バイト．
Linux の jiffies も UNIX タイム使ってて 32 bit で 2038 年にオーバーフローする
問題があった覚えがある．

特に何もなければ空間効率的な意味で TIMESTAMP を使うべき．
integer 型で TIMESTAMP 格納しても良いことないらしい．格納できるけど．

マイクロ秒単位でアクセスする場合は BIGINT か DOUBLE で格納すると良い．
gettimeofday() -> micro sec
getnstimeofday() -> nsec # kernel 用関数なのでドライバ書かないと使えない

Bit-Packed Data Types

BIT
5.0 以降は全く別の型．
64 個の true/false を格納可能．

内部の挙動は Engine によって異なる．
MyISAM では 17 個の異なる BIT カラムを 3バイトに丸めて格納する.
InnoDB, Memory では BIT(x) で，xが格納できる最小サイズの INT 方を割り当てる．

string 型として扱うので，データを取り出したときに char としてキャストされてしまって
混乱する場合がある．

numeric context だと数字として扱われる．
a + 0 => 数字

SET
たくさんの ture/false 値を保存するときに使う．
後で SET を変更しようとすると，ALTER TABLE しようとするとコストがかかるので良くない．
index は張れない．

Integer 型に対する bit 操作
enumuration field を alter table 無しでできるのが特徴．
=> 理由が不明．
alter table の挙動が分からないので(ry

Choosing Identifiers

関係性のあるテーブル同士だったら，全部同じ型にするのも良いかも．
=> join が高速だから．

identifier column を決めたら，storage type だけじゃなくて
computation and comparison type も考慮に入れるべき．
ENUM と SET は storage type は INT だけど，比較のときに
string context では string 型に変換されてしまう

関連性のあるテーブルの(columnの？)型が UNSIGNED を含めて同じか
どうかをちゃんと調べること．キャストが起きて遅くなる．

将来的に困らない程度で最小のデータ型を選ぼう

Integer : identifier には一番イカす．
ENUM と SET : identifier には適していない．
String : できる限り避けるべき．MyISAM では packed indexes を使うのでかなり遅い?
MD5(), SHA1(), or UUID() とかで生成されたランダムな文字列を突っ込むと相当遅い．
page split, ramdam disk access, cluster index fragmentation
locality が効かなくなる
キャッシュの効きも悪くなる

Beware of Autogenerated Schemas性能がヤバイくらい劣化することがあるらしい．
OR Mapper もそういうことがあるみたい．
複雑なものを開発者から見えなくすると，スケールしない．
パフォーマンスを生産効率の

INET_ATON
INET_NTOA

3.2 Index basics

Indexes = DB 内に保存されたデータを効率よくアクセスするためのデータ構造
- とりあえず張っとけばいいというものでもない．

Indexes = MySQL 用語で key
- Indexing はデータサイズが大きくなるにつれて重要になるので，注意．
- いい加減なインデッス張ってると，データがでかくなったときに一気に効いてくる．
- イメージとしては，本の index と同じ．「ある変数の値(actor_id)が5であるカラムを index を使って
  を探して，column を返す」．mysql> SELECT first_name FROM sakila.actor WHERE actor_id = 5;

sakila.actor table から actor_id = 5 をもつ column の first_name row を取り出す．
そういえば，select に * 以外を指定したときって，他の row はメモリにのらないのかなー？
キャッシュは関係ある？オプション?

1つ以上の column に対して index を張るときは，column の順番が重要．
別々に index 張る処理とは別．

Type of indexes

Index の種類は Storage Engine 依存．

B-tree index (実際は B+-tree engine)
- Archive engine 以外のエンジンには搭載されている，非常に基本的なエンジン．
  - Archives は AUTO_INCREMENT column が index 替わりに使われるようになってから index が搭載されたことがない．-- CREATE TABLE する時に B-tree って言ってても，別の構造体を使っていることがままある．--- NDB cluster Engine は中で T-tree を使ってる.
- T-tree : 木構造だけどデータの持ち方がちがう．

storage engine によって disk への index の格納方法は異なる．
- MyISAM は圧縮してディスクに保存．:
  行の格納した場所の物理的な位置を使ってインデックスを張る．
- InnoDB は圧縮してると利用できない最適化があるのでそのまま保存

遅延と効率・スループットのトレードオフかな?

B-tree は，全探索をしないので高速．
- root -> node -> node -> ... -> node -> leaf.
- node は left, right, misc へのポインターを持つ．
- leaf はカラム? データへのポインタをもつ．
```
CREATE TABLE People (
   last_name  varchar(50)    not null,
   first_name varchar(50)    not null,
   dob        date           not null,
   gender     enum('m', 'f') not null,
   key(last_name, first_name, dob)  <-- これで index を指定！);
```
- figure 3.2 を見ると，確かに key の順番が重要であることが分かる．
- 多分文字コードは ASCII
- 日本語の場合は文字コードの大きさ順で入るのかなー？
こんな Query だと index が効果を発揮するよ！！
1. 完全一致
2. prefix 一致
3. (suffix は確かに B-tree の構造と相性良くなさそう．)
4. 完全一致 + prefix 一致++ Index で直接指示 : key-value と同じかな．

value を探す
順番が大事 : order by するときに大事．

うまく検索できないクエリ
1. ←端から始まらない場合
2. 特定の文字で終わる場合
3. 歯抜けのクエリがある場合 : B-tree による index は prefix 一致で処理をおこなうので，
  もし最後の index だけ与えられても，真ん中の情報が与えられないとそこは強制的に全探索を
  おこなう必要が生まれる．

順序を満たすように複数の組み合わせを行う必要があるかもね． Query

Hash Indexes

row 毎に，index column に対して Hash を作る．
高速?
- hash table って中でどういう構造してるんだっけ．=> B-tree
- メモリテーブルにある

制限
1. 一旦 index を張った row に対してアクセスする必要がある．
2. ソートしてないので， order by する query がとんできても index による
  効果は生じない．
3. 部分一致に使えない．
4. hash 値がぶつかると遅くなる．
5. hash 値がぶつかりまくってる状態だと，index 操作が重くなる．カラムの作成時のupdate，削除など．ポインタをたどりまくるので．
Building your own hash indexes

hybrid hash 結合