1. 優(yōu)化數(shù)據(jù)結(jié)構(gòu)
- 盡量避免null.
- 可為NULL的列使索引,索引統(tǒng)計和值比較都更加復(fù)雜.
- 它需要更多的存儲空間, 在MySql 里需要特殊的處理.
- 索引可為NULL的列時,每個索引記錄需要一個額外的字節(jié). (InnoDB使用單獨的bit來存儲NULL值,會稍微好點).
- 更小的通常更好. 它們占用更少的磁盤,內(nèi)存和緩存, 處理時需要的CPU 周期也更少.
- 簡單就好. 簡單類型的操作需要更少的CPU周期.
- 兩步進行數(shù)據(jù)類型的選擇:
- 確定合適的大類型: 數(shù)字,字符串,時間等.
- 根據(jù)存儲的長度和范圍, 允許的精度, 需要的物理空間 來選擇具體的類型.
2. 整數(shù)類型
2.1 整數(shù)類型
- 不同的具體類型(包含無符號數(shù))決定的是如何在內(nèi)存和磁盤中保存數(shù)據(jù),并不影響性能,整數(shù)計算都使用64位的BIGINT.
- 整型的寬度,例如INT(11)中的11, 只是規(guī)定了某些交互工具中, 用來顯式字符的個數(shù), 并不影響存儲和值范圍.
2.2 實數(shù)類型
- 使用DOUBLE作為內(nèi)部浮點類型的計算類型.
- Decimal需要額外的空間和計算開銷. 僅在需要對小數(shù)進行精確計算時才使用. 當(dāng)數(shù)據(jù)量大時可使用BIGINT替代.
3. 字符串類型
- VARChar 需要額外的1或2個字節(jié)(根據(jù)最大長度的大小)記錄長度.
- 比定長類型更節(jié)省空間, 因為僅使用必要的空間.
- 若表使用Row_Format = Fixed 創(chuàng)建時,每一行都使用定長存儲,會浪費空間.
- Update 時若造成行更長, 可能會導(dǎo)致碎片.
- 適用場景: 字符串列的最大長度比平均長度大很多. 列更新少(不易產(chǎn)生碎片). 采用的字符集中每個字符都使用不同的字節(jié)數(shù)進行存儲.
- CHAR 適合較短的字符串, 或所有值都接近一個長度.
- Binary,VarBinary 在需要存儲二進制數(shù)據(jù)時, 其比較是按字節(jié)逐次比較,更加簡單高效.
- Blob和Text 用于存儲很大的字符串.
- 其值會被當(dāng)成獨立的對象處理. 當(dāng)值很大時,會使用外部空間存儲,內(nèi)部存儲指針.
- 其列排序只對最前max_sort_length字節(jié)而非整個字節(jié)排序.
- 不能對列全部長度的字符串進行索引.
- Memory?引擎不支持Blob和Text. 若查詢使用了他們, 會造成磁盤臨時表的使用.
- 應(yīng)避免使用它們. 如使用Substring將列值轉(zhuǎn)換為字符串.
4. 枚舉
- 有時可以使用枚舉類代替常用的字符串類型.
- 問題: 列表是固定的,添加或刪除必須使用alter table. 對于未來會變更的情況,盡量不使用,除非只在末尾添加元素.
- 存儲枚舉時非常緊湊, 會根據(jù)列表值的數(shù)量壓縮到1/2字節(jié)中. 在內(nèi)部將值在列表中的位置保存為整數(shù). 并在.frm文件中保存'數(shù)字-字符串'映射關(guān)系的查找表.
- 盡量避免使用數(shù)字作為枚舉常量, 這樣會有雙重性.
- 按照內(nèi)部存儲的整數(shù)而不是定義的字符串的值進行排序的(也就是定義值時的順序).
5. 日期和時間類型
- TimeStamp 保存了從1970 年以來的秒數(shù).
- 只使用4個字節(jié)存儲,所以只能到2038 年.
- 等同于UNIX時間戳.
- from_unixtime()/unix_timestamp()進行日期和Unix時間戳的轉(zhuǎn)換.
- 顯示值依賴于時區(qū).
- TimeStamp 列默認為Not Null.
- 其空間效率更高,所以應(yīng)盡量使用TimeStamp.
- DateTime
- 可保存從1001到9999 年, 精度為秒.
- 將時間和日期封裝到格式為YYYYMMDDHHMMSS 的整數(shù)中,與時區(qū)無關(guān).
- 使用8個字節(jié)存儲.
6. 位數(shù)據(jù)類型
- BIT
- 被當(dāng)做字符串類型,而不是數(shù)字類型.
- 檢索出的結(jié)果是包含0和1的字串.
- 但在數(shù)字上下文中得到的是字串對應(yīng)的數(shù)字值. 所以會產(chǎn)生二義性.
- SET
- 若需要保存很多true/false 值, 可以合并這些列到一個SET中.
- 如ACL: SET('CAN_READ', 'CAN_WRITE', 'CAN_DELETE').
- 內(nèi)部以一系列打包的位的集合來表示,從而有效地利用了存儲空間.
- 問題是改變列定義(交換可讀和可寫的位置)?時的代價較大, 且無法在SET列上通過索引查找.
- 若需要保存很多true/false 值, 可以合并這些列到一個SET中.
- 在整數(shù)列上進行按位操作.
- 一種替代SET的方式是使用一個整數(shù)包裝一系列的位. 如把8個位包裝到一個TINYINT中,并按位操作來使用.
- 好處是可以不適用Alter Table 改變字段代表的"枚舉"值.
- 缺點是查詢語句更難寫, 并且難以理解.
7. Schema 的設(shè)計陷阱
- 太大的列.
- 存儲引擎API需要在服務(wù)器層和存儲引擎層通過緩存格式拷貝數(shù)據(jù), 然后在服務(wù)器層將緩存內(nèi)容解碼成各個列. 從行緩存中將編碼過的列轉(zhuǎn)換成行數(shù)據(jù)結(jié)構(gòu)的代碼很高.
- 定長行結(jié)構(gòu)與服務(wù)器層的行結(jié)構(gòu)正好匹配, 所以不需要轉(zhuǎn)換. 而變長結(jié)構(gòu)總是需要昂貴的轉(zhuǎn)換.
- 太多的關(guān)聯(lián)
- 所謂的"實體-屬性-值(EVA)"是一種糟糕的設(shè)計模式. 它需要過多的自關(guān)聯(lián).
- 全能的枚舉
- 類似enum('','0','1',....'31'). 會造成新增值時的alter table.
- 應(yīng)該使用整數(shù)作為外鍵關(guān)聯(lián)到字典表或查找表來查找具體值.
- 多數(shù)情況下,應(yīng)該避免使用NULL值,而使用替代(0,特殊值). 但不要極端.
- MySql會在索引中存儲NULL值.
