數據庫字段設計規範
1、優先選擇符合存儲需要的最小的數據類型
原因
列的字段越大,建立索引時所需要的空間也就越大,這樣一頁中所能存儲的索引節點的數量也就越少也越少,在遍歷時所需要的 IO 次數也就越多, 索引的性能也就越差
方法
1)將字符串轉換成數字類型存儲,如:將 IP 地址轉換成整形數據。
mysql 提供了兩個方法來處理 ip 地址:
inet_aton 把 ip 轉爲無符號整型 (4-8 位)
inet_ntoa 把整型的 ip 轉爲地址
插入數據前,先用 inet_aton 把 ip 地址轉爲整型,可以節省空間。顯示數據時,使用 inet_ntoa 把整型的 ip 地址轉爲地址顯示即可。
2)對於非負型的數據(如自增 ID、整型 IP)來說,要優先使用無符號整型來存儲
因爲:無符號相對於有符號可以多出一倍的存儲空間
SIGNED INT -2147483648~2147483647
UNSIGNED INT 0~4294967295
VARCHAR(N) 中的 N 代表的是字符數,而不是字節數
使用 UTF8 存儲 255 個漢字 Varchar(255)=765 個字節。過大的長度會消耗更多的內存
2、避免使用 TEXT、BLOB 數據類型,最常見的 TEXT 類型可以存儲 64k 的數據
建議把 BLOB 或是 TEXT 列分離到單獨的擴展表中
Mysql 內存臨時表不支持 TEXT、BLOB 這樣的大數據類型,如果查詢中包含這樣的數據,在排序等操作時,就不能使用內存臨時表,必須使用磁盤臨時表進行。
而且對於這種數據,Mysql 還是要進行二次查詢,會使 sql 性能變得很差,但是不是說一定不能使用這樣的數據類型。
如果一定要使用,建議把 BLOB 或是 TEXT 列分離到單獨的擴展表中,查詢時一定不要使用 select * 而只需要取出必要的列,不需要 TEXT 列的數據時不要對該列進行查詢。
TEXT 或 BLOB 類型只能使用前綴索引
因爲 MySQL 對索引字段長度是有限制的,所以 TEXT 類型只能使用前綴索引,並且 TEXT 列上是不能有默認值的。
3、避免使用 ENUM 類型
修改 ENUM 值需要使用 ALTER 語句
ENUM 類型的 ORDER BY 操作效率低,需要額外操作
禁止使用數值作爲 ENUM 的枚舉值
4、儘可能把所有列定義爲 NOT NULL
原因:
索引 NULL 列需要額外的空間來保存,所以要佔用更多的空間;
進行比較和計算時要對 NULL 值做特別的處理
5、使用 TIMESTAMP(4 個字節)或 DATETIME 類型(8 個字節)存儲時間
TIMESTAMP 存儲的時間範圍 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07。
TIMESTAMP 佔用 4 字節和 INT 相同,但比 INT 可讀性高
超出 TIMESTAMP 取值範圍的使用 DATETIME 類型存儲。
經常會有人用字符串存儲日期型的數據(不正確的做法):
缺點 1:無法用日期函數進行計算和比較
缺點 2:用字符串存儲日期要佔用更多的空間
6、同財務相關的金額類數據必須使用 decimal 類型
非精準浮點:float,double
精準浮點:decimal
Decimal 類型爲精準浮點數,在計算時不會丟失精度。佔用空間由定義的寬度決定,每 4 個字節可以存儲 9 位數字,並且小數點要佔用一個字節。可用於存儲比 bigint 更大的整型數據。
本文由 Readfog 進行 AMP 轉碼,版權歸原作者所有。
來源:https://mp.weixin.qq.com/s/egcnWjozw2HmWvnVamU8yw