共計 5091 個字符，預(yù)計需要花費 13 分鐘才能閱讀完成。
自動寫代碼機器人，免費開通
這篇文章主要介紹 Schema 與數(shù)據(jù)類型優(yōu)化的方法，文中介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們一定要看完！
schema 就是數(shù)據(jù)庫對象的集合，這個集合包含了各種對象如：表、視圖、存儲過程、索引等。為了區(qū)分不同的集合，就需要給不同的集合起不同的名字，默認情況下一個用戶對應(yīng)一個集合，用戶的 schema 名等于用戶名，并作為該用戶缺省 schema。所以 schema 集合看上去像用戶名。
如果把 database 看作是一個倉庫，倉庫很多房間（schema），一個 schema 代表一個房間，table 可以看作是每個房間中的儲物柜，user 是每個 schema 的主人，有操作數(shù)據(jù)庫中每個房間的權(quán)利，就是說每個數(shù)據(jù)庫映射的 user 有每個 schema（房間）的鑰匙。SQL server 和 Oracle mysql 有別
4.1 選擇優(yōu)化的數(shù)據(jù)類型原則：
1、更小的通過更好，盡量使用可正確存儲數(shù)據(jù)的最小的數(shù)據(jù)類型（占更少的磁盤 內(nèi)存 CPU 緩存，處理時需要 CPU 周期更少：更快），但能罩得住數(shù)據(jù)，存不下就尷尬了
2、簡單就好：簡單類型（更少 CPU 周期），使用 MySQL 內(nèi)建類型存時間，整型存 ip，整型較字符代價低（字符集和校對排序規(guī)則使字符較復(fù)雜）
3、盡量避免 null：最好指定為 not null
*）null 列使用更多的存儲空間，mysql 里需要特殊處理
*）null 使索引、索引統(tǒng)計和值比較更復(fù)雜；可為 null 的列被索引時，每個索引記錄需額外的字節(jié)
例外：InnoDB 使用單獨位 bit 存儲 null,so 對于稀疏數(shù)據(jù)（很多值為 null）有很好的空間效率，不適合 MyISAM
4.1.1 整數(shù)類型【參考】整數(shù) whole number
tinyint(8 位存儲空間) smallint(16) mediumint(24) int(32) bigint(64)
1、存儲值的范圍：
2、unsigned：可選、不容許負值，可使正數(shù)的上限提高一倍：tinyint unsigned 0~255，tinyint-128~127
3、有無符號使用相同的存儲空間，相同的性能
可為整型指定寬度，例如 INT(11)，對于大多數(shù)應(yīng)用無意義，不會限制值的合法范圍，只是規(guī)定了交互工具顯示字符的個數(shù)，對于存儲和計算，int（1）和 int（20）是相同的；
實數(shù) real number：帶小數(shù)
float 和 double，mysql 使用 duble 作為內(nèi)部浮點計算的類型
decimal：存儲精確的小數(shù)，mysql 服務(wù)器自身實現(xiàn)，decimal(18,9)18 位，9 位小數(shù)，9 個字節(jié)（前 4 后 4 點 1）
盡量只在對小數(shù)進行精確計算時才使用（額外的空間和計算開銷），如財務(wù)數(shù)據(jù)
數(shù)據(jù)量大時，考慮使用 bigint 代替，將需要存儲的貨幣單位據(jù)小數(shù)的位數(shù)乘以相應(yīng)的倍數(shù)
浮點：
建議：只指定類型、不定精度（mysql），這些精度非標準，mysql 會悄選類型、或存時對值取舍
存儲同樣范圍的值時，比 decimal 更少的空間，float4 字節(jié)存 double8 字節(jié)（更高精度范圍）
4.1.3 字符串類型 varchar 和 char：
前提：innodb 和 myisam 引擎，最主要的字符串類型
磁盤存儲：存儲引擎存儲的方式與在內(nèi)存、磁盤上的不能不一樣，所以 mysql 服務(wù)器從引擎取值需轉(zhuǎn)格式
varchar：
1、存儲可變字符串，比定長節(jié)省空間（僅使用必要的空間），但如果表使用 row_format=fixed，行會定長存儲
2、需使用 1 / 2 額外字節(jié)記錄字符串長度；1）列 max 長度 =255 字節(jié)，1 字節(jié)表示，否 2 字節(jié)，2）采用 latinl 字符集，varchar（10）列需 11 個字節(jié)的存儲空間，varchar（1000）1002 字節(jié)，2 字節(jié)存儲長度信息
3、節(jié)省存儲空間，利于性能；但在 update 可能使行變得比原來更長、需做額外工作
合適的情況：
1）字符串列最大長度比平均長度大很多；2）列的更新少（不擔(dān)心碎片）；3）使用 UTF- 8 字符串，每個字符均使用不同的字節(jié)數(shù)存儲
char：
1、定長，據(jù)長度分配空間，刪除 all 末尾空格；長度不夠、空格填充
2、存儲空間上更有效率，char(1)來存儲只有 Y N 的值 1 個字節(jié)，varchar2 字節(jié)，還有一個記錄長度
適合的情況：
1）適合存儲很短的字符串；2）或 all 值接近同一個長度；3）經(jīng)常變更的數(shù)據(jù)，存儲不易碎片
對應(yīng)空格、存儲：
char 類型存儲時末尾空格被刪；數(shù)據(jù)如何存儲取決于存儲引擎，Memory 引擎只支持定長的行（最大長度分配空間）
binary，varbinary：存儲二進制字符串，字節(jié)碼，長度不夠、\0 來湊（不是空格）檢索時不會去
慷慨不是明智的：varchar（5）和 varchar（100）存儲‘hell’空間開銷一樣，長的列消耗更多內(nèi)存
blob 和 text：大數(shù)據(jù)
分別用二進制和字符方式存儲，分別屬于兩組不同的數(shù)據(jù)類型：字符類型：tinytext、smalltext、text、mediumtext、longtext，對應(yīng)的二進制類型是 tinyblob、smallblob、blob、mediumblob、longblob，兩類僅有的不同：blob 類型存儲的是二進制，無排序規(guī)則或字符集，text 有字符串 排序規(guī)則；
MySQL 會把每個 blob 和 text 當做獨立的對象處理，存儲引擎存儲時會做特殊處理，當值太大，innoDB 使用專門的外部存儲區(qū)域進行存儲，此時每個值在行內(nèi)需要 1~4 個字節(jié)存儲一個指針，然后在外部存儲實際的值；
mysql 對他們的列排序：只對每列前 max_sort_length 字節(jié)排序；且不能將列全部長度的字符串進行索引，也不能使用這些索引消除排序；
如果 explain 執(zhí)行計劃的 extra 包含 using temporary：這個查詢使用了隱式臨時表
使用 enum 代替字符串類型
定義時指定取值范圍，對 1～255 個成員的枚舉需要 1 個字節(jié)存儲；對于 256～65535 個成員，需要 2 個字節(jié)存儲。最多可以有 65535 個成員，ENUM 類型只能從成員中選擇一個；和 set 相似
可把不重復(fù)的固定的字符串存儲成一個預(yù)定義的集合，mysql 在存儲枚舉時會據(jù)列表值的數(shù)量壓縮到 1 / 2 字節(jié)中，在內(nèi)部會將每個值在列表中的位置保存為整數(shù)（從 1 開始，必須進行查找才能轉(zhuǎn)換為字符串，開銷、列表小 可控），且在表的.frm 文件中保持“數(shù)字 - 字符串”映射關(guān)系的“查找表”；
將一個數(shù)字存儲到一個 ENUM 中，數(shù)字被當作為一個索引值，并且存儲的值是該索引值所對應(yīng)的枚舉成員：在一個 ENUM 字符串中存儲數(shù)字是不明智的，因為它可能會打亂思維；ENUM 值依照列規(guī)格說明中的列表順序進行排序。(ENUM 值依照它們的索引號排序。)舉例來說，對于 ENUM(a , b) a 排在 b 后，但是對于 ENUM(b , a)，b 卻排在 a 之前。空字符串排在非空字符串前，NULL 值排在其它所有的枚舉值前。為了防止意想不到的結(jié)果，建議依照字母的順序定義 ENUM 列表。也可以通過使用 GROUP BY CONCAT(col) 來確定該以字母順序排序而不是以索引值。【源】
排序時安裝創(chuàng)建表時的順序排序的（應(yīng)該是）；枚舉最不好的地方：字符串列表是固定的，添加刪除字符串須使用 alter table；在‘查找表’時采用整數(shù)主鍵避免基于字符串的值進行關(guān)聯(lián)；
4.1.4 日期和時間 datetime：大范圍的值 1001 9999 s YYYYMMDDHHMMSS 與時區(qū)無關(guān) 8 字節(jié)
默認，以可排序、無歧義的格式顯示 datetime：2008-01-02 22:33:44
timestamp：1970 2038，1970 1 1 以來的秒數(shù)，時區(qū) 4 字節(jié)
from_unixtime 將 unix 時間戳轉(zhuǎn)日期，unix_timestamp 將日期轉(zhuǎn) unix 時間戳
插入時沒有指定第一個 timestamp 列的值，設(shè)置為當前時間，插入記錄時，默認更新第一個 timestamp 列的值，timestamp 類為 not null，盡量使用 timestamp（空間效率高）；
可以使用 bigint 類型存儲微妙級別的時間戳，或 double 存秒之后的小數(shù)部分，或使用 MariaDB 代替 MySQL；
4.1.5 位 bit：mysql5.0
前與 tinyint 同義詞，新特性
bit（1）單個位的字段，bit（2）2 個位，最大長度 64 個位
行為因存儲引擎而異，MyISAM 打包存儲 all 的 BIT 列（17 個單獨的 bit 列只需要 17 個位存儲，myisam3 字節(jié) ok），其他引擎 Memory 和 innoDB 為每 bit 列使用足夠存儲的最小整數(shù)類型來存放，不節(jié)省存儲空間；
mysql 把 bit 當做字符串類型，檢索 bit（1）值、結(jié)果是包含二進制 0 / 1 的字符串，數(shù)字上下文的場景檢索，將字符串轉(zhuǎn)成數(shù)字，大部分應(yīng)用，best 避免使用；
set
創(chuàng)建表時，就指定 SET 類型的取值范圍：屬性名 SET(值 1 , 值 2 , 值 3 …, 值 n)，“值 n”參數(shù)表示列表中的第 n 個值，這些值末尾的空格將會被系統(tǒng)直接刪除，字段元素順序 系統(tǒng)自動按照定義時的順序顯示 重復(fù) 只存一次。
其基本形式與 ENUM 類型一樣。SET 類型的值可以取列表中的一個元素或者多個元素的組合。取多個元素時，不同元素之間用逗號隔開。SET 類型的值最多只能是有 64 個元素構(gòu)成的組合，根據(jù)成員的不同，存儲上也有所不同：【參考，同 enum】
1～8 成員的集合，占 1 個字節(jié)。9～16 成員的集合，占 2 個字節(jié)。17～24 成員的集合，占 3 個字節(jié)。25～32 成員的集合，占 4 個字節(jié)。33～64 成員的集合，占 8 個字節(jié)。
需要保持很多 true、false 值，可考慮合并這些列到 set 類型，在 mysql 內(nèi)部以一系列打包的位的集合來表示的（有效利用存儲空間）且 mysql 有 find_in_set、field 函數(shù)，方便在查詢中使用；
缺點：改變列的定義代價高，需要 alter table，無法再 set 上通索引查找
在整數(shù)列按位操作：
代替 set 的方式：使用整數(shù)包裝一系列的位：可把 8 個位包裝到 tinyint 中，且按位操作來使用，為位定義名稱常量來簡化這個工作，但是這樣查詢語句較難寫且難理解
4.1.6 選擇標識符 identifier 標識列：自增長列【源】
1）可不用手動插入值，系統(tǒng)提供默認序列值;2)不要求和主鍵搭配 ; 3)要求是 unique key；
4）一個表最多一個；5）類型只能是數(shù)值；5）可通過 set auto_increment_increment=3;
選擇標識列類型時
考慮存儲類型、mysql 對這種類型怎么執(zhí)行計算和比較，確定后確保在 all 關(guān)聯(lián)表中使用 same 類型，類型間要精確匹配；
技巧：
1、整數(shù)類型：整數(shù)通常最好的選擇，很快且可使用 auto_increment
2、enum 和 set 類型，存儲固定信息
3、字符串：避免，耗空間較數(shù)字慢，myisam 表特別小心（默認對字符串壓縮使用、查詢慢）
1）完全“隨機”字符串 MD5/SHA1/UUID 函數(shù)生成的新值 會任意分布在很大的空間內(nèi)，導(dǎo)致 insert 及部分的 select 變慢：插入值隨機的寫到索引的不同位置，insert 變慢(頁分裂 磁盤隨機訪問 聚簇索引碎片）；select 變慢、邏輯上相鄰的行分布在磁盤和內(nèi)存不同的地方；隨機值導(dǎo)致緩存對 all 類型的查詢語句效果都變差（使緩存賴以工作的訪問局部性原理失效）
聚簇索引，實際存儲的循序結(jié)構(gòu)與數(shù)據(jù)存儲的物理結(jié)構(gòu)一致，通常來說物理順序結(jié)構(gòu)只有一種，一個表的聚簇索引也只能有一個，通常默認都是主鍵，設(shè)置了主鍵，系統(tǒng)默認就為你加上了聚簇索引；【源】
非聚簇索引記錄的物理順序與邏輯順序沒有必然的聯(lián)系，與數(shù)據(jù)的存儲物理結(jié)構(gòu)沒有關(guān)系；一個表對應(yīng)的非聚簇索引可以有多條，根據(jù)不同列的約束可以建立不同要求的非聚簇索引；
2）存儲 uuid，移除 - 符號，或者用 unhex 轉(zhuǎn)換 uuid 值為 16 字節(jié)的數(shù)字，且存儲在 binary（16）列中，檢索時通過 hex 函數(shù)格式化為 16 進制格式;
UUID 生成的值與加密散列函數(shù) (sha1) 生成的值不同特征：uuid 分布不均勻，有一定順序，不如遞增整數(shù)
當心自動生成的 schema：
嚴重性能問題，很大的 varchar、關(guān)聯(lián)列不同的類型；
orm 會存儲任意類型的數(shù)據(jù)到任意類型的后端數(shù)據(jù)存儲中，并沒有設(shè)計使用更優(yōu)的類型存儲，有時為每個對象每個屬性使用單獨行，設(shè)置使用基于時間戳的版本控制，導(dǎo)致單個屬性會有多個版本存在；權(quán)衡
4.1.7 特殊類型數(shù)據(jù)：空
以上是“Schema 與數(shù)據(jù)類型優(yōu)化的方法”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對大家有幫助，更多相關(guān)知識，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道！
向 AI 問一下細節(jié)
