【MySQL資料庫】第四章解讀：Schema與資料類型優化（上）

前言：

    高效能的基石：良好的邏輯、物理設計，依照系統要執行的查詢語句設計schema

#     本章著重MySQL資料庫設計，介紹mysql資料庫設計與其他關係型資料庫管理系統的差異

schema：【來源】

       schema就是資料庫物件的集合，這個集合包含了各種物件如：表、視圖、預存程序、索引等。為了區分不同的集合，就需要給不同的集合起不同的名字，預設會一個使用者對應一個集合，使用者的schema名等於使用者名，並作為該使用者缺省schema。所以schema集合看上去像用戶名。

       如果把database視為一個倉庫，倉庫很多房間（schema），一個schema代表一個房間，table可以看作是每個房間中的儲物櫃，user是每個schema的主人，有操作資料庫中每個房間的權利，就是說每個資料庫對映的user有每個schema（房間）的鑰匙。 SQL server和Oracle mysql有別

4.1選擇優化的資料類型

#原則：

#1、更小的透過更好，盡量使用可正確儲存資料的最小的資料類型（佔更少的磁碟記憶體CPU緩存，處理時需要CPU週期更少：更快），但能罩得住數據，存不下就尷尬了

2 、簡單就好：簡單型別（更少CPU週期），使用MySQL內建型別存時間，整數存ip，整數型較字代價低（字元集與校對排序規則使字元較複雜）

3、盡量避免null：最好指定為not null

        *）null列使用更多的儲存空間，mysql裡需要特殊處理

        *）null使索引、索引統計和值比較更複雜；可為null的列被索引時，每個索引記錄需額外的位元組

        例外：InnoDB使用單獨位元bit儲存null,so對於稀疏資料（許多值為null）有很好的空間效率，不適合MyISAM

4.1.1整數型別【參考】

整數whole number

tinyint(8位元儲存空間)  smallint(16)  mediumint (24)   int(32)    bigint(64)

#1、儲存值的範圍：，N是儲存空間的位元數

2、unsigned：可選、不容忍負值，可使正數的上限提高一倍：tinyint unsigned 0~255，tinyint-128~127

3、有無符號使用相同的儲存空間，相同的效能

可為整數指定寬度，例如INT(11)，對於大多數應用無意義，不會限制值的合法範圍，只是規定了交互工具顯示字符的個數，對於存儲和計算，int（1）和int （20）是相同的；

實數real number：帶小數

float和double，mysql使用duble作為內部浮點計算的類型

decimal：儲存精確的小數，mysql伺服器本身實現，decimal(18,9)18位，9位小數，9個位元組（前4點4點1）

       盡量只在對小數進行精確計算時才使用（額外的空間和計算開銷），如財務資料

      資料量大時，考慮使用bigint代替，將需要儲存的貨幣單位據小數的位數乘以相應的倍數

浮點：

        建議：只指定型別、不定精確度（mysql），這些精確度非標準，mysql會悄悄選取型別、或存時對值取捨

        儲存相同範圍的值時，儲存相同範圍的值時，比decimal更少的空間，float4位元組存double8位元組（更高精度範圍）

4.1.3字串類型

varchar和char：

#前提： innodb和myisam引擎，最主要的字串類型

磁碟儲存：儲存引擎儲存的方式與在記憶體、磁碟上的不能不一樣，所以mysql伺服器從引擎取值需轉格式

varchar：

1、儲存可變字串，比定長節省空間（僅使用必要的空間），但如果表使用row_format=fixed，行會定長存儲

2、需使用1/2額外位元組記錄字串長度；1）列max長度

3、節省儲存空間，利於效能；但在update可能使行變比原來更長、需要額外工作

適當的情況：

    1）字串列最大長度比平均長度大很多；2）列的更新少（不擔心碎片）；3）使用UTF-8字串，每個字元均使用不同的位元組數儲存

char：

1、定長，根據長度分配空間，刪除all末尾空格；長度不夠、空格填充

2、存儲空間上更有效率，char(1)來存儲只有Y N的值1個位元組，varchar2位元組，還有一個記錄長度

適合的情況：

      1）適合儲存很短的字串；2）或all值接近同一個長度；3）經常變更的數據，存儲不易碎片

對應空格、存儲：

char類型存儲時末尾空格被刪；數據如何存儲取決於儲存引擎，Memory引擎只支援定長的行（最大長度分配空間）

binary，varbinary：儲存二進位字串，字節碼，長度不夠、\0來湊（不是空格）檢索時不會去 

慷慨不是明智的：varchar（5）和varchar（100）存儲'hell'空間開銷一樣，長的列消耗更多內存

blob和text：大數據

     分別以二進位和字元方式存儲，分別屬於兩組不同的資料類型：字元類型：tinytext、smalltext、text、mediumtext、longtext，對應的二進位類型是tinyblob、smallblob、blob、mediumblob、longblob，兩個類別僅有的不同：blob型別儲存的是二進位，無排序規則或字元集，text有字串排序規則；

      MySQLSQL會把每個blob和text當做獨立的物件處理，儲存引擎儲存時會做特殊處理，當值太大，innoDB使用專門的外部儲存區域進行存儲，此時每個值在行內需要1 ~4個位元組儲存一個指針，然後在外部儲存實際的值；

     mysql對他們的列排序：只對每列前max_sort_length位元組排序；且不能將列全部長度的字串進行索引，也不能使用這些索引消除排序；

如果explain執行計劃的extra包含using temporary：這個查詢使用了隱式臨時表

使用enum代替字串類型

#      定義時指定取值範圍，對1～255個成員的枚舉需要1個位元組儲存；對於256～65535個成員，需要2個位元組儲存。最多可以有65535個成員，ENUM型別只能從成員中選擇一個；和set相似

      可把不重複的固定的字串儲存成一個預先定義的集合，mysql在儲存枚舉時會據列表值的數量壓縮到1/2位元組中，在內部會將每個值在列表中的位置儲存為整數（從1開始，必須進行查找才能轉換為字串，開銷、列表小可控），且在表的.frm檔案中保持「數字-字符字串」映射關係的「查找表」；

      將一個數字儲存到一個 ENUM 中，數字被當作一個索引值，並且儲存的值是該索引值所對應的列舉成員： 在一個 ENUM字串中儲存數字是不明智的，因為它可能會打亂思維；ENUM 值依照列規格說明中的列表順序進行排序。 (ENUM 值依照它們的索引號碼排序。)舉例來說，對於 ENUM("a", "b") "a" 排在 "b" 後，但對於 ENUM("b", "a")， "b" 卻排在 "a"之前。空字串排在非空字串前，NULL 值排在其它所有的枚舉值前。為了防止意想不到的結果，建議依照字母的順序定義 ENUM列表。也可以透過使用GROUP BY CONCAT(col) 來確定該以字母順序排序而不是以索引值。 【來源】

      排序時安裝創建表時的順序排序的（應該是）；枚舉最不好的地方：字符串列表是固定的，添加刪除字符串須使用alter  table；在'查找表'時採用整數主鍵避免基於字串的值進行關聯；

4.1.4日期和時間

datetime：大範圍的值1001 9999 s YYYYMMDDHHMMSS 與時區無關8位元組

     默認，以可排序、無歧義的格式顯示datetime：2008-01-02 22:33:44

timestamp：1970 2038，1970 1 1以來的秒數，時區 4位元組 

     from_unixtime將unix時間戳記日期，unix_timestamp將日期轉unix時間戳

    插入時沒有指定第一個timestamp欄位的值，設為當前時間，插入記錄時，預設更新第一個timestamp列的值，timestamp類別為not null，盡量使用timestamp（空間效率高）；

可以使用bigint類型儲存微妙等級的時間戳，或double存秒之後的小數部分，或使用MariaDB取代MySQL；

4.1.5 位元

bit：mysql5.0

    前與tinyint同義詞，新功能

    bit（1）單位元的字段，bit（2） 2個位，最大長度64個位元

   行為因儲存引擎而異，MyISAM打包儲存all的BIT列（17個單獨的bit列只需要17個位元存儲，myisam3位元組ok），其他引擎Memory和innoDB為每bit列使用足夠儲存的最小整數類型來存放，不節省儲存空間；

    mysql把bit當作字串類型，檢索bit（1）值、結果是包含二進位0/1的字串，數字上下文的場景檢索，將字串轉成數字，大部分應用，best避免使用；

set

       建立表格時，就指定SET類型的值範圍 ：屬性名稱SET('值1','值2','值3'...,'值n') ，「值n」參數表示清單中的第n個值，這些值末端的空格將會被系統直接刪除，欄位元素順序系統自動依照定義時的順序顯示重複只存一次。

      其基本形式與ENUM型別相同。 SET類型的值可以取清單中的一個元素或多個元素的組合。取多個元素時，不同元素之間用逗號隔開。 SET類型的值最多只能是有64個元素構成的組合，根據成員的不同，存儲上也有所不同：【參考，同enum】

1～8成员的集合，占1个字节。
9～16成员的集合，占2个字节。
17～24成员的集合，占3个字节。
25～32成员的集合，占4个字节。
33～64成员的集合，占8个字节。

       需要保持很多true、false值，可考慮合併這些列到set類型，在mysql內部以一系列打包的位元的集合來表示的（有效利用儲存空間）且mysql有find_in_set、field函數，方便在查詢中使用；

      缺點：改變列的定義代價高，需要alter table，無法再set上通索引查找

在整數列按位操作：

     代替set的方式：使用整數包裝一系列的位元：可把8個位元包裝到tinyint中，且位元操作來使用，為位元定義名稱常數來簡化這個工作，但是這樣查詢語句較難寫且難理解   

4.1.6選擇識別符identifier

標識列：自增長列【來源】

     1）可不使用手動插入值，系統提供預設序列值;2)不要求和主鍵搭配; 3)要求是unique key;

     4）一個表格最多一個；5）型別只能是數值；5）可以透過set auto_increment_increment=3;

選擇識別列類型時

      考慮儲存類型、mysql對此類型怎麼執行計算和比較，確定後確保在all關聯表中使用same類型，類型間要精確匹配；

技巧：

1、整數類型：整數通常最好的選擇，很快且可使用auto_increment

2、enum和set類型，儲存固定資訊

3、字串：避免，耗空間較數字慢，myisam表特別小心（預設對字串壓縮使用、查詢慢）        1）完全「隨機」字串MD5 /SHA1/UUID函數產生的新值會任意分佈在很大的空間內，導致insert及部分的select變慢：插入值隨機的寫到索引的不同位置，insert變慢(頁分裂磁碟隨機存取群集索引碎片）；select變慢、邏輯上相鄰的行分佈在磁碟和記憶體不同的地方；

隨機值導致快取對all類型的查詢語句效果都變差（使

快取賴以工作的存取局部性原理失效）          聚簇索引

，實際儲存的

循序結構與資料儲存的物理結構一致，通常來說物理順序結構只有一種，一個表的聚集索引也只能有一個，通常預設都是主鍵，設定了主鍵，系統預設就為你加上了叢集索引；【來源】

          

非聚簇索引

記錄的物理順序與邏輯順序沒有必然的聯繫，與資料的儲存物理結構沒有關係；一個表對應的非聚簇索引可以有多條，根據不同列的約束可以建立不同要求的非聚集索引；

         2）儲存uuid，移除-符號，或用unhex轉換uuid值為16位元組的數字，且儲存於binary（16 ）列中，檢索時透過hex函數格式化為16進位格式;

           UUID產生的值與加密雜湊函數(sha1)產生不同的特徵：uuid分佈不均勻，有一定順序，不如遞增整數

當心自動產生的schema：######     嚴重效能問題，很大的varchar、關聯列不同的型別；###

    orm會將任意類型的資料儲存到任何類型的後端資料儲存中，並沒有設計使用更優的類型存儲，有時為每個物件每個屬性使用單獨行，設定使用基於時間戳記的版本控制，導致單一屬性會有多個版本存在；權衡

4.1.7特殊型別資料：空

相關文章：

#【MySQL資料庫】第三章解讀：伺服器效能剖析（上）

#【MySQL資料庫】第三章解讀（下）

以上是【MySQL資料庫】第四章解讀：Schema與資料類型優化（上）的詳細內容。更多資訊請關注PHP中文網其他相關文章！