《高性能MySQL》筆記——MySQL建表資料類型的選擇

前段時間看了《高性能MySQL》中的選擇優化的資料類型，這裡主要是做一下筆記。

首先資料選擇有幾個簡單原則：

• 更小的通常更好。一般情況下，應該盡量使用可以正确存儲資料的最小資料類型。例如隻需要存 0~200，tinyint unsigned 更好。更小的資料類型通常更快，因為它們占用更少的磁盤、記憶體和 CPU 緩存，并且處理時需要的 CPU 周期也更少。
• 簡單就好。簡單資料類型的操作通常需要更少的 CPU 周期。例如，整型比字元操作代價更低，因為字元集和校對規則（排序規則）使字元比較 比 整型比較更複雜。這裡有兩個例子：一個是應該使用 MySQL 内建的類型（date, time, datatime）而不是字元串來存儲日期和時間，另一個是應該用無符号整型存儲 IP 位址。
• 盡量避免NULL。通常情況下最好指定列為 NOT NULL，除非真的需要存儲 NULL 值。如果查詢中包含可為 NULL 的列，對 MySQL 來說更難優化，因為可為 NULL 的列使得索引、索引統計和值比較都更複雜。特别是計劃在列上建索引，就應該盡量避免設計成可為 NULL 的列。

整數類型：（tinyint，smallint，mediumint，int，bigint）

存儲（位元組）	存儲（位）	有符号範圍	無符号範圍
TINYINT	1	8	-128 ~127	0~255
SMALLINT	2	16	-32768~32767	0~65535
MEDIUMINT	3	24	-8388608~8388607	0~16777215
INT	4	32	-2147483648~2147483647	0~4294967295
BIGINT		64	-2^63~2^63-1	0~2^64-1

• 整數類型有可選的 unsigned 屬性，表示不允許負值，這大緻可以使正數的上限提高一倍。主鍵自增 id 适合設定為 unsigned 屬性的 int 類型。
• MySQL 可以為整數類型指定寬度，例如 int(11)，對大多數應用這是沒有意義的：它不會限制值的合法範圍，隻是規定了 MySQL 的一些互動工具（例如 MySQL 指令行用戶端）用來顯示字元的個數。對于存儲和計算來說，int(1) 和 int(20) 是相同的。

實數類型：（float，double，decimal）

• float，double 屬于浮點類型（近似值）。decimal 屬于定點類型（精确值）。
• MySQL 浮點型和定點型可以用類型名稱後加（M，D）來表示，M 表示該值的總共長度，D 表示小數點後面的長度。
• float 使用 4 個位元組存儲；double 使用使用 8 個位元組存儲；decimal 則是将數字打包儲存到一個二進制字元串中（每 4 個位元組存 9 個數字）。例如，decimal(18, 9) 小數點兩邊将各存儲 9 個數字，一共使用 9 個位元組：小數點前的數字用 4 個位元組，小數點後的數字用 4 個位元組，小數點本身占 1 個位元組。
• 因為需要額外的空間和計算開銷，是以應該盡量隻在對小數進行精确計算時才使用 decimal——例如存儲财務資料。但在資料量比較大的時候，可以考慮使用 bigint 代替 decimal ，将需要存儲的貨币機關根據最小的位數乘以相應的倍數即可。

字元串類型：

varchar 和 char 類型

• varchar 類型用于存儲可變長字元串，是最常見的字元串資料類型。
• varchar 需要使用 1 或 2 個額外位元組記錄字元串長度：如果列的最大長度小于或等于 255 位元組，則隻使用 1 個位元組表示，否則使用 2 個位元組。
• char 類型是定長的：MySQL 總是根據定義的字元串長度配置設定足夠的空間。
• char 存儲會删除存儲資料的末尾空格；varchar 不會。（準确來說是 MySQL 4.1 以後的版本 varchar 不會删除存儲資料的末尾空格）

blob 和 text 類型

• blob 和 text 都是為了存儲很大的資料而設計的字元串資料類型，分别采用二進制和字元方式存儲。
• 與其它類型不同，MySQL 把每個 blob 和 text 值當作一個獨立的對象處理。
• blob 和 text 家族之間僅有的不同是 blob 類型存儲的是二進制資料，沒有排序規則或字元集，而 text 類型有字元集和排序規則。
• memory 引擎不支援 blob 和 text 類型。

枚舉（enum）類型

• MySQL 在内部會将每個值在清單中的位置儲存為整數，并且在表的 .frm 檔案中儲存 “數字-字元串” 映射關系的 “查找表”。
• 如果使用數字作為枚舉常量，這種雙重性很容易導緻混亂，例如 enum('1', '2', '3') 。建議盡量避免這麼做。
• 枚舉字段是按照内部存儲的整數而不是定義的字元串進行排序的。

日期和時間類型：

• MySQL 能存儲的最小時間粒度為秒。
• datetime 儲存範圍從 1001 年到 9999 年，精度為秒。與時區無關。使用 8 位元組的存儲空間。
• timestamp 類型儲存了從 1970 年 1 月 1 日 午夜（格林尼治标準時間）以來的秒數，它和 unix 時間戳相同。使用 4 位元組的存儲空間，範圍從 1970 年到 2038 年。與時區有關。
• MySQL 4.1 以及更新的版本按照 datetime 的方式格式化 timestamp 的值，這僅僅是顯示格式上的差別，timestamp 的存儲格式在各個版本都是一樣的。
• 除了特殊行為之外，通常也應該盡量使用 timestamp ，因為它比 datetime 空間效率更高。有時候人們會将 unix 時間戳存儲為整數值，但這不會帶來任何收益。用整數儲存時間戳的格式通常不友善處理，是以我們不推薦這麼做。（對于這點很多公司項目中是用整數存儲，我也查了資料，使用整數的理由一般是日期比較，計算時整數更好處理）