28 Apr 2010

MySQLでのデータ型とストレージスペースの関係

得に指定がない場合はMySQL5のMyIsamとInnoDBについて説明しています。

数値型

整数

- TINYINT, SMALLINT, MEDIUMINT, INT, BIGINTはそれぞれ1,2,3,4,5byteの長さ。
- 固定長。上記した型によって型が決定され、行の値に関係なく固定長。
- (多分だけど)実際にストレージされる長さ = byte長 + 1 bit(正負を表すためのbit)
- UNSINED属性で正の数だけを扱うことができる。TINYINT = -128 to 127, TINYINT UNSINED = 0 to 255。ストレージスペースとパフォーマンスは変わらない。
- 上記のinteger型全てが64bitのBIGINTとして計算される。
- INT(1)は表示上の制限でしかない。長さを指定するのはMySQLのツールが表示の目的のための文字を指定しているだけ。ストレージと計算の目的においてはINT(1)とINT(20)は同一
- Falconの場合はストレージスペースの容量の指定ができない。
- INTEGER, BOOL, NUMERICはエイリアス。パフォーマンスに影響しない


実数

- FLOAT, DOUBLEはそれぞれ 4, 8 byte
- DECIMALは可変です。最小 1byte - 最大 33bytes
必要になるbyte数は小数点の左の桁数と右の桁数によって別々に計算されます。各端のbyte数は9桁毎に4byte必要になります。余りに対して必要になるbyte数が以下のテーブルによって決定されます。

桁数 byte数
1-2 1
3-4 2
5-6 3
7-8 4

例、DECIMAL(20, 10)
DECIMAL(M, D)はMが全体の桁数、Dが小数点以下の桁数を表します。Mは最大65, Dは最大30です。

小数点の左が10桁、右が10桁です。各端にフォーカスします。9桁のために4byteが使用され、余りの1桁のために1byteが使用されます。そのため各端に必要なbyte数は5byteです。

DECIMAL(M, D)
- 最小必要容量
Mの最小が1、Dが0。小数点を表すbyteが必要ないとして、最小必要容量は1byte

- 最大必要容量
Mの最大が65。65を9で割った余りが2。左:右を 63:2とするより 62:3とする方が消費される容量が大きい。62 = 9*6 + 8。最大必要容量は 左=6*4 + 4 右=4 小数点=1 の合計の33byte。




文字列型

VARCHAR


- MyIsam
- ROW_FORMAT=DYNAMICの場合
- 一つの行が断片化して保存される。検索に時間がかかることになるので、頻繁に更新があるテーブルには適さない。OPTIMIZE TABLEでデフラグできるがあまり実用的ではない。
- データファイルポインタのサイズが可変である。MyIsamはインデクスの中に行の場所を示すポインタがあるが、行を見つけるためにbyte長を使用する。大きなテーブルの場合、ポインタが大きくなるのでオーバーヘッドが発生する。
- テーブルが破損した場合修復に失敗する場合がある。ある行が更新に失敗して破損した場合、byte長で制御されているので後続の行のポインタが正確な場所を示さなくなり修復に失敗する可能性がある。

- ROW_FORMAT=FIXEDの場合
- 行が断片化しない。行が断片化しない。
- データファイルポインタのサイズが固定である。行を見つけるために行番号を使用するため検索が早い。
- テーブルが破損しても修復が容易全ての行が同じサイズのため、最悪破損した行のみが修復不可能になる。


CHAR

- CHAR(M)。Mは文字列で0から255。最大は4バイト文字(Mysql 5.5.4から)を使った1020byte
- 固定長で保存される
- 文字数がCHAR(M)のMより少ない場合はMになるまで空白文字でパッドされる
- 検索時に後続スペースは削除される
- けれどもメモリ上ではしばしば固定長のメモリが割り当てられる。メモリ内一時テーブルを使ったソートに悪影響。ディスク上でのファイルソートでも同じことが起きる。
- つまるところ、CHAR(5)とCHAR(100)に同じ長さの文字列を格納した場合、消費されるストレージスペースは同じだが、検索時にCHAR(5)の方が検索がずっと早くなることがある。



BLOBとTEXT


TEXT

- TINYTEXT, SMALLTEXT/TEXT(シノニム), MEDIUMTEXT, LONGTEXTはそれぞれ最長で255, 65535, 65535, 4GB


BLOB

- TINYBLOB, SMALLBLOB/BLOB(シノニム), MEDIUMBLOB, LONGBLOBはそれぞれ最長で255, 65535, 65535, 4GB
- 先頭の768bytesまでが通常の行と同じ領域に格納される
- InnoDBには1行あたりおよそ8KBまでというサイズ制限があるためひとつのテーブルにBLOBカラムは10個までしか作成することが出来ない



ENUM

- 列挙値を最大65535個まで定義できる
- 列挙値255個までが1byte。列挙値65535個までが2bytes
- 列挙値を格納するテーブルと、表示用の値を格納するテーブルがある
- 必ず結合が発生するので検索時にオーバーヘッドがあるが、大概はサイズが小さいことにより解決される
- CHAR,VACHARとENUMをJOINするのはCHAR,VACHARをCHAR, VARCHARにJOINするより時間がかかる
- 但しCHAR, VARCHARを使用するより大きくストレージスペースを少なくできるので結果的に処理が早い

DATEとTIME


DATETIME

- 8bytes固定長

TIMESTAMP

- 4bytes固定長
- January, 1, 1970(Greenwich Mean Time)からの時間の経過を格納しているためストレージスペースが小さい
- unix_timestamp()でユニクスタイムスタンプと同じ形式で取得可能

> select unix_timestamp(a) from t;
+-------------------+
| unix_timestamp(a) |
+-------------------+
|         315532800 | 
+-------------------+
1 row in set (0.00 sec)


Bit-Packed


BIT

- ストレージエンジンによって必要なストレージスペースが異なる
- 最大64bitsまで定義できる
- MyIsamは全てのbitを一つのカラムに格納する
- MemoryとInnoDBは定義されたビット数を表現するのに必要なだけのintegerを代わりに使用する
- MySQLはBITを以下の様に文字列として扱ってしまい混乱の種になる。そのためあまり使用が推奨されない。
mysql> CREATE TABLE bittest(a bit(8));
mysql> INSERT INTO bittest VALUES(b'00111001');
mysql> SELECT a, a + 0 FROM bittest;
+------+-------+
| a | a + 0 |
+------+-------+
| 9 | 57 |
+------+-------+
aの値9は57をASCII文字コードで変換したものである。

もし、 true/falseを1ビットのストレージスペースに格納したい場合はNULLを許容するCHAR(0)カラムを使うことができる。このカラムは、NULLか0長値(空文字)を扱うことができる。


SET

- ラベル付きのビット演算(ACLなど)に便利
- ALTER TABLEをラージテーブルに対して行うとexpensive
mysql> CREATE TABLE acl (
-> perms SET('CAN_READ', 'CAN_WRITE', 'CAN_DELETE') NOT NULL
-> );
mysql> INSERT INTO acl(perms) VALUES ('CAN_READ,CAN_DELETE');
mysql> SELECT perms FROM acl WHERE FIND_IN_SET('CAN_READ', perms);
+---------------------+
| perms |
+---------------------+
| CAN_READ,CAN_DELETE |
+---------------------+

21 Apr 2010

複数形と単数形のコンビネーション

いっつもこんがらがるのでメモ。

一つの文章にいくつかの名詞があってそれぞれに数の関係がある場合よくこんがらがる。この文章の訳として正しい文がどれか分かりますか?

”私は開発チームのサブマネージャとして働いた経験がいくつかあります。”

1. I have several experiences working as a sub-manager in development teams.
2. I have several experiences working as sub-managers in development teams.
3. I have several experiences working as a sub-manager in a development team.


§

正解は1番。経験が幾つかあって、関わったチームも複数。だけど私は一人だからworking as A sub-managerが正しい。

2番目は多分自分が不特定複数人いるイメージになってしまい、文が意味をなさない。
3番目は経験が幾つかあるのにチームは一つ。だからどっちの情報を信じていいか分からなくなるので、混乱させる。

より意味を鮮明にするなら以下の文が良い。
I have several experiences in working as a sub-manager for different development teams.

§

では、"一人一人と別々に食事をした。"という意味の文はどう書くのが適当でしょうか?

I had three separate dinners and in each I was with another party.
”私は三つの別のディナーをしました。そしていずれの場合も別の相手と一緒でした。”

最後のpartyは相手を意味します。


I had dinner with three separate people in a single day.
"私は三人の別の人と一日にディナーをした。"

いずれの場合も数を特定する表現(太字)が必要になります。ロジカルに表現しないと多分に聞き手にイマジネーションを働かせる余裕を持たせてしまいます。
.