文章來源地址http://www.zghlxwxcb.cn/news/detail-707996.html

1.?ENUM（枚舉）

1.1.?MySQL在存儲(chǔ)枚舉時(shí)非常緊湊，會(huì)根據(jù)列表值的數(shù)量壓縮到1或者2字節(jié)中

1.2.?轉(zhuǎn)換為ENUM會(huì)使表變小

1.3.?如果表中有其他索引，減少主鍵大小也會(huì)使這些非主鍵索引小得多

1.4.?ENUM列可以存儲(chǔ)一組預(yù)定義的不同字符串值

1.5.?ENUM字段是根據(jù)內(nèi)部整數(shù)值排序的，而不是根據(jù)字符串本身

1.6.?查詢中使用FIELD()函數(shù)顯式地指定排序順序，但這會(huì)導(dǎo)致MySQL無法利用索引消除排序

1.7.?MySQL將每個(gè)枚舉值存儲(chǔ)為整數(shù)，并且必須進(jìn)行查找以將其轉(zhuǎn)換為字符串表示，因此ENUM列有一些開銷

1.8.?將CHAR/VARCHAR列聯(lián)接到ENUM列可能比聯(lián)接到另一個(gè)CHAR/VARCHAR列更慢

1.9.?通常的設(shè)計(jì)實(shí)踐是使用帶有整數(shù)主鍵的“查找表”，以避免在聯(lián)接中使用字符串

1.10.?更改ENUM中的有效值會(huì)導(dǎo)致需要做schema變更

2.?日期和時(shí)間類型

2.1.?MySQL可以存儲(chǔ)的最小時(shí)間粒度是微秒

2.2.?DATETIME

2.2.1.?從1000年到9999年，精度為1微秒

2.2.2.?以YYYYMMDDHHMMSS格式存儲(chǔ)壓縮成整數(shù)的日期和時(shí)間

2.2.3.?與時(shí)區(qū)無關(guān)

2.2.4.?8字節(jié)的存儲(chǔ)空間

2.2.5.?以可排序、無歧義的格式顯示DATETIME值

2.2.6.?ANSI表示日期和時(shí)間的標(biāo)準(zhǔn)方式

2.2.7.?保留日期和時(shí)間的文本表示

2.3.?TIMESTAMP

2.3.1.?自1970年1月1日格林尼治標(biāo)準(zhǔn)時(shí)間（GMT）午夜以來經(jīng)過的秒數(shù)

2.3.1.1.?與UNIX時(shí)間戳相同

2.3.2.?從1970年到2038年1月19日

2.3.2.1.?會(huì)遇到2038年的問題

2.3.2.1.1.?使用帶符號(hào)的32位INT，可以表達(dá)直到2038年的時(shí)間

2.3.2.1.2.?使用無符號(hào)的32位INT，可以表達(dá)直到2106年的時(shí)間

2.3.2.1.3.?使用64位，還可以超出這些范圍

2.3.3.?時(shí)間戳顯示的值依賴于時(shí)區(qū)

2.3.3.1.?MySQL服務(wù)器、操作系統(tǒng)和客戶端連接都有時(shí)區(qū)設(shè)置

2.3.3.2.?存儲(chǔ)值0的TIMESTAMP在美國東部標(biāo)準(zhǔn)時(shí)間（EST）中顯示為1969-12-31 ??19：00：00，與格林尼治標(biāo)準(zhǔn)時(shí)間（GMT）差5小時(shí)

2.3.4.?4字節(jié)的存儲(chǔ)空間

2.3.5.?FROM_UNIXTIME()函數(shù)來將UNIX時(shí)間戳轉(zhuǎn)換為日期

2.3.6.?UNIX_TIMESTAMP()函數(shù)將日期轉(zhuǎn)換為UNIX時(shí)間戳

2.3.7.?保留與所使用時(shí)區(qū)相關(guān)的值

2.3.8.?TIMESTAMP的行為規(guī)則很復(fù)雜，并且在不同的MySQL版本中會(huì)發(fā)生變化，因此你應(yīng)該驗(yàn)證數(shù)據(jù)庫的行為是否符合需要。在對(duì)TIMESTAMP列進(jìn)行更改后，通常最好檢查SHOW CREATE TABLE命令的輸出

2.3.9.?特殊屬性

2.3.9.1.?當(dāng)插入一行記錄時(shí)沒有指定第一個(gè)TIMESTAMP列的值，MySQL會(huì)將該列的值設(shè)置為當(dāng)前時(shí)間

2.3.9.2.?當(dāng)更新一行記錄時(shí)沒有指定第一個(gè)TIMESTAMP列的值，MySQL默認(rèn)也會(huì)將該列的值更新為當(dāng)前時(shí)間

2.3.9.3.?可以為任何TIMESTAMP列配置插入和更新行為

2.3.9.4.?TIMESTAMP列在默認(rèn)情況下為NOT NULL，這也和其他的數(shù)據(jù)類型不一樣

3.?位壓縮數(shù)據(jù)類型

3.1.?從技術(shù)上來說都是字符串類型

3.2.?BIT

3.2.1.?可以使用BIT列存儲(chǔ)一個(gè)或多個(gè)true/false值

3.2.2.?視為字符串類型，而不是數(shù)字類型

3.2.3.?避免使用這種類型

3.2.3.1.?建議使用TINYINT

3.3.?SET

3.3.1.?如果需要存儲(chǔ)多個(gè)true/false值，可以考慮使用MySQL原生的SET數(shù)據(jù)類型

3.3.2.?以一組打包的位的集合來表示的

3.3.3.?更有效地利用存儲(chǔ)空間

3.3.4.?FIND_IN_SET()和FIELD()等函數(shù)，使其易于在查詢中使用

3.3.5.?替代方法是使用整數(shù)作為二進(jìn)制位的打包集合

3.3.5.1.?可以在不使用ALTER TABLE的情況下更改字段表示的“枚舉”

3.3.5.2.?查詢更難編寫和理解

4.?JSON數(shù)據(jù)類型

4.1.?決定使用原生SQL還是JSON取決于在數(shù)據(jù)庫中存儲(chǔ)JSON的便捷性是否大于性能

4.2.?如果每天訪問這些數(shù)據(jù)數(shù)百萬次或數(shù)十億次，速度差異就會(huì)累加起來

4.3.?使用SQL列的速度仍然更好于JSON列

5.?標(biāo)識(shí)符

5.1.?引用行及通常使其唯一的方式

5.2.?為標(biāo)識(shí)符列選擇數(shù)據(jù)類型時(shí)，應(yīng)該與聯(lián)接表中的對(duì)應(yīng)列保持一致

5.3.?在為標(biāo)識(shí)符列選擇類型時(shí)，不僅需要考慮存儲(chǔ)類型，還需要考慮MySQL如何對(duì)該類型執(zhí)行計(jì)算和比較

5.4.?在可以滿足值的范圍的需求，并且預(yù)留未來增長空間的前提下，應(yīng)該選擇最小的數(shù)據(jù)類型

5.5.?整數(shù)通常是標(biāo)識(shí)符的最佳選擇

5.5.1.?速度快

5.5.2.?自動(dòng)遞增

5.6.?對(duì)于標(biāo)識(shí)符來說，ENUM和SET類型通常是糟糕的選擇

5.6.1.?ENUM和SET列適用于保存訂單狀態(tài)或產(chǎn)品類型等信息

5.7.?應(yīng)避免使用字符串類型作為標(biāo)識(shí)符的數(shù)據(jù)類型

5.7.1.?很消耗空間

5.7.2.?比整數(shù)類型慢

5.8.?對(duì)于完全“隨機(jī)”的字符串要非常小心

5.8.1.?MD5()、SHA1()或UUID()生成的字符串

5.9.?新值會(huì)任意分布在很大的空間內(nèi)，這會(huì)減慢INSERT和某些類型的SELECT查詢的速度

5.9.1.?插入的值會(huì)寫到索引的隨機(jī)位置，所以會(huì)使得INSERT查詢變慢

5.9.2.?導(dǎo)致頁分裂、磁盤隨機(jī)訪問，以及對(duì)于聚簇存儲(chǔ)引擎產(chǎn)生聚簇索引碎片

5.9.3.?SELECT查詢也會(huì)變慢，因?yàn)檫壿嬌舷噜彽男袝?huì)廣泛分布在磁盤和內(nèi)存中

5.9.4.?對(duì)于所有類型的查詢，隨機(jī)值都會(huì)導(dǎo)致緩存的性能低下，因?yàn)樗鼈儠?huì)破壞引用的局部性，而這正是緩存的工作原理

5.10.?存儲(chǔ)通用唯一標(biāo)識(shí)符（UUID）值，則應(yīng)該刪除破折號(hào)

5.10.1.?更好的做法是，使用UNHEX()函數(shù)將UUID值轉(zhuǎn)換為16字節(jié)的數(shù)字，并將其存儲(chǔ)在一個(gè)BINARY（16）列中

5.10.2.?可以使用HEX()函數(shù)以十六進(jìn)制格式檢索值

6.?特殊數(shù)據(jù)類型

6.1.?IPv4地址

6.1.1.?使用VARCHAR（15）列來存儲(chǔ)

6.1.2.?實(shí)際上是32位無符號(hào)整數(shù)，而不是字符串

6.1.2.1.?小數(shù)點(diǎn)將地址分成四段的表示方法只是為了讓人們閱讀容易

6.1.2.2.?存儲(chǔ)為無符號(hào)整數(shù)

6.1.3.?INET_ATON()和INET_NTOA()函數(shù)來在這兩種表示形式之間進(jìn)行轉(zhuǎn)換

6.1.4.?從VARCHAR（15）的約16字節(jié)縮減到無符號(hào)32位整數(shù)的4字節(jié)

7.?schema設(shè)計(jì)中的陷阱

7.1.?太多的列

7.2.?太多的聯(lián)接

7.2.1.?MySQL限制每個(gè)聯(lián)接有61個(gè)表

7.3.?全能的枚舉

7.3.1.?要小心過度使用ENUM

7.4.?變相的枚舉

7.4.1.?ENUM列允許在列中保存一組已定義值中的單個(gè)值

7.4.2.?SET列則允許在列中保存一組已定義值中的一個(gè)或多個(gè)值

7.4.3.?如果真和假兩種情況不會(huì)同時(shí)出現(xiàn)，那么毫無疑問應(yīng)該使用ENUM列而不是SET列

7.5.?NULL不是虛擬值

7.5.1.?在表中存儲(chǔ)事實(shí)上的“空值”，可以使用0、特殊值或空字符串作為代替

7.5.2.?當(dāng)需要表示未知值時(shí)，不要太害怕使用NULL

7.5.3.?在某些情況下，使用NULL比使用某個(gè)虛擬常數(shù)更好

7.5.4.?MySQL會(huì)對(duì)NULL值進(jìn)行索引，而Oracle則不會(huì)

8.?schema管理

8.1.?修改schema是數(shù)據(jù)庫工程師必須承擔(dān)的最常見任務(wù)之一

8.2.?schema變更管理視為“數(shù)據(jù)存儲(chǔ)平臺(tái)”的一部分

8.3.?盡可能靠近現(xiàn)有的軟件部署工具和工作流程

8.4.?應(yīng)該使用能夠集成針對(duì)schema更改的基本檢測(cè)的工具，以確保滿足一些基線需求

8.5.?如果所在的組織使用多種編程語言且發(fā)展迅速，請(qǐng)確保不會(huì)意外地引入人為瓶頸

8.6.?Skeema是一個(gè)在跨多個(gè)環(huán)境的版本控制中管理schema更改的杰出開源解決方案

8.7.?gh-ost是由GitHub的數(shù)據(jù)工程團(tuán)隊(duì)創(chuàng)建的，專門作為一種管理schema更改過程的解決方案，既不影響服務(wù)，也不使用觸發(fā)器

8.7.1.?其使用的是二進(jìn)制日志而不是觸發(fā)跟蹤變化，這是更安全的選擇，所以不必?fù)?dān)心觸發(fā)器的性能影響

到了這里，關(guān)于讀高性能MySQL（第4版）筆記07_優(yōu)化數(shù)據(jù)類型（下）的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！