對(duì)于 MySQL 數(shù)據(jù)庫(kù)來說，好的邏輯表和物理表的規(guī)劃至關(guān)重要，我們需要根據(jù)查詢語(yǔ)句來針對(duì)性地設(shè)計(jì) Schema ，沒有萬(wàn)能好用的 Schema。一個(gè) denormalized 的 schema 可以在某些場(chǎng)景下加速語(yǔ)句查詢，但是放在其他應(yīng)用場(chǎng)景下就會(huì)適得其反。增加輔助表可以優(yōu)化查詢，但是表的維護(hù)成本就很高。MySQL 的實(shí)現(xiàn)原理決定了這一事實(shí)。

《最全的MySQL性能指南》系列文章重點(diǎn)討論數(shù)據(jù)庫(kù)索引和 MySQL 的 schema 設(shè)計(jì)。在讀之前，讀者應(yīng)該熟悉數(shù)據(jù)庫(kù)設(shè)計(jì)基本知識(shí)。在系列文章中，我們會(huì)講到邏輯設(shè)計(jì)，物理級(jí)別的設(shè)計(jì)，和語(yǔ)句執(zhí)行。

MySQL 支持多種數(shù)據(jù)類型，存儲(chǔ)數(shù)據(jù)類型的選擇對(duì)性能至關(guān)重要，下面的一些基本原則可以幫助你在不同場(chǎng)景下選擇合適的數(shù)據(jù)類型：

字段越小越好

一般情況下，選擇可以存儲(chǔ)你的數(shù)據(jù)的最小數(shù)據(jù)類型，越小越快，因?yàn)檎加酶俚拇疟P空間，更少的內(nèi)存，和 CPU 緩存。小的數(shù)據(jù)類型也只需要較少的 CPU 周期來處理。

簡(jiǎn)單即更優(yōu)

處理簡(jiǎn)單的數(shù)據(jù)類型通常耗費(fèi)更少的 CPU 周期。舉個(gè)例子，整數(shù)比較比字母比較更快，將字母用數(shù)字表示更好。而且我們不應(yīng)該用字符串，而是應(yīng)該用 MySQL 原生的日期和時(shí)間類型來存儲(chǔ)時(shí)間數(shù)據(jù)，用整數(shù)來存儲(chǔ) IP 地址。

盡可能避免 NULL

很多表都支持帶 null 值的字段，即便是應(yīng)用軟件沒有 null 這么個(gè)概念，也還是會(huì)在表里加入這個(gè)一個(gè)值做為默認(rèn)值。最好的方式是每一個(gè)字段都標(biāo)注 NOT NULL，除非應(yīng)用確實(shí)有 NULL 的概念。MySQL 對(duì)涉及 null 字段的查詢無法做到最佳優(yōu)化，因?yàn)?null 值本身讓索引和數(shù)值比較更加復(fù)雜。帶 null 值的字段消耗更多的存儲(chǔ)空間，而且需要一些特殊的處理。當(dāng)帶 null 值的字段被加了索引的時(shí)候，每一個(gè)字段值都會(huì)多出一個(gè)字節(jié)，在 MyISAM 存儲(chǔ)引擎中還有可能將固定大小的索引變成隨機(jī)大小的索引。

把一個(gè)帶 null 的字段修改成 NOT NULL 的字段帶來的性能提升不大，所以如果不是必須的話，請(qǐng)不要在建表之后去修改，但是如果要在字段上加索隱，最好還是用 NOT NULL。

也有例外的情況。比如 InnoDB 用一個(gè)比特來存儲(chǔ) NULL，所以對(duì)于稀疏表，是很節(jié)省空間的。但是 MyISAM 就不是這樣。

確定字段類型的第一步，就是了解數(shù)據(jù)的基本特征：數(shù)值型，字符串型，時(shí)間型，等等。這個(gè)很好確定，但是也有上文提到的特殊情況。

下一步就是確定具體的數(shù)據(jù)類型。很多 MySQL 的數(shù)據(jù)類型可以存儲(chǔ)相同的數(shù)據(jù)，但是對(duì)數(shù)據(jù)的范圍要求、精度、存儲(chǔ)格式卻不一樣。某些數(shù)據(jù)格式有特殊的性質(zhì)。

比如說，DATETIME 和 TIMESTAMP 字段可以存儲(chǔ)相同的數(shù)據(jù) - 日期和時(shí)間，精確到秒。但是 TIMESTAMP 只需要 DATETIME 一般的存儲(chǔ)空間，能夠存儲(chǔ)時(shí)區(qū)信息，并且還支持自動(dòng)數(shù)據(jù)生成。但是，TIMESTAMP 能表示的時(shí)間范圍就小很多，而且其自動(dòng)數(shù)值生成功能有時(shí)候會(huì)產(chǎn)生不要的效果

整數(shù)

數(shù)字有兩種類型：整數(shù)和實(shí)數(shù)(帶小數(shù)點(diǎn)位的數(shù)字)。如果是存儲(chǔ)整數(shù)，我們可以可以使用TINYINT (8 bits，能存儲(chǔ)

到

范圍的整數(shù))

SMALLINT (16 bits，能存儲(chǔ)

到

范圍的整數(shù))

MEDIUMINT (24 bits，能存儲(chǔ)

到

范圍的整數(shù))

INT (32 bits，能存儲(chǔ)

到

范圍的整數(shù))

BITINT (64 bits，能存儲(chǔ)

到

范圍的整數(shù))

整數(shù)類型的可以自帶 UNSIGNED 屬性，該屬性去除了所有負(fù)值，將能表示的最大值翻兩倍。例如，TINYINT UNSIGNED 可以存儲(chǔ) 0 到 255 的數(shù)值，而 TINYINT 只能存儲(chǔ) -128 到 127。

Signed 和 unsigned 數(shù)據(jù)類型占用相同的存儲(chǔ)空間，性能效果相同，所以根據(jù)你自己的需求隨意設(shè)置就好。

我們對(duì)數(shù)據(jù)類型的選擇決定了 MySQL 存儲(chǔ)數(shù)據(jù)的方式。但是整數(shù)相關(guān)的計(jì)算通常用的是 64位 BITINT 的方式進(jìn)行，就算在32位處理器中也是如此。

MySQL 可以指定整數(shù)的上限位數(shù)，比如 INT(11)，這其實(shí)是一個(gè)沒啥卵用的功能：這并不會(huì)改變整數(shù)的范圍，僅僅只是制定 MySQL 在 command line 下顯示整數(shù)所用的占位符的個(gè)數(shù)。從存儲(chǔ)和計(jì)算角度上來說，INT(1) 和 INT(20) 是一樣的

實(shí)數(shù)

實(shí)數(shù)是帶有小數(shù)點(diǎn)位的數(shù)字，但它們不僅僅存儲(chǔ)分?jǐn)?shù)性質(zhì)的數(shù)字，你也可以用 DECIMAL 來存儲(chǔ)達(dá)到連 BIGINT 都存不下的數(shù)值。MySQL 支持完全準(zhǔn)確和近似值類型的實(shí)數(shù)

FLOAT 和 DOUBLE 支持用浮點(diǎn)方式的近似計(jì)算，計(jì)算方式因處理器的不同而稍有差異。

DECIMAL 是用來存儲(chǔ)完全準(zhǔn)確的實(shí)數(shù)的。在 MySQL 5.0 以及以后的版本中，DECIMAL 支持完全精確計(jì)算。而 MySQL 4.1 和更早的版本則是使用浮點(diǎn)方式來進(jìn)行 DECIMAL 的運(yùn)算，這在某些情況下會(huì)出現(xiàn)很詭異的計(jì)算結(jié)果，所以在老版本中，DECIMAL 只是一個(gè) “存儲(chǔ)類型”。

在 MySQL 5.0 以及以后的版本中，MySQL server 負(fù)責(zé) DECIMAL 的所有計(jì)算，因?yàn)?CPU 本身不支持 DECIMAL 運(yùn)算。浮點(diǎn)運(yùn)算(Floating-point )會(huì)快很多，因?yàn)?CPU 支持浮點(diǎn)運(yùn)算。

浮點(diǎn)(floating-point)和 DECIMAL 類型都支持指定精度。對(duì)于 DECIMAL 字段，可以指定小數(shù)點(diǎn)之前和之后的最大位數(shù)，注意，這會(huì)影響字段的存儲(chǔ)。

MySQL 5.0 以及以后的版本中的 DECIMAL 支持最高帶有 65 個(gè)數(shù)字的數(shù)值。

相比于 DECIMAL 浮點(diǎn)類型通常要求較少的存儲(chǔ)空間來存儲(chǔ)相同的數(shù)據(jù)。一個(gè) FLOAT 字段占用 4 個(gè)字節(jié)，DOUBLE 占用 8 個(gè)字節(jié)并且精度更高。和整數(shù)一樣，浮點(diǎn)和 DECIMAL 只是存儲(chǔ)方式上的區(qū)別，計(jì)算都是通過 DOUBLE 的計(jì)算方式進(jìn)行的。

因?yàn)檎加酶嗟拇鎯?chǔ)空間和計(jì)算資源，應(yīng)該只有在需要精確小數(shù)計(jì)算的時(shí)候你才應(yīng)該使用 DECIMAL。一個(gè)可用的場(chǎng)景是存儲(chǔ)金融相關(guān)的數(shù)據(jù)。但是對(duì)于大體量數(shù)據(jù)，更好的方法是用 BIGINT，將貨幣數(shù)據(jù)轉(zhuǎn)化成可復(fù)原的整數(shù)形式。比如你需要存儲(chǔ)小數(shù)點(diǎn)后兩位的貨幣數(shù)據(jù)，你可以將原數(shù)值乘以一百萬(wàn)，存成 BITINT，這樣可以防止因?yàn)楦↑c(diǎn)數(shù)據(jù)存儲(chǔ)和 DECIMAL 運(yùn)算帶來的精度誤差。

字符串類型

MySQL 支持相當(dāng)多的字符串類型，這些類型在 4.1 版本和 5.0 版本中有了很大的變化，也因此變得更加復(fù)雜。

MySQL 4.1 開始，每個(gè)字符串字段能有不同的字符集和對(duì)應(yīng)的字符集排序規(guī)則。

VARCHAR 和 CHAR 類型

兩個(gè)主要的字符串類型是 VARCHAR 和 CHAR，每個(gè)存儲(chǔ)引擎對(duì)這兩者的存儲(chǔ)方式都很不一樣。我們這里僅討論 InnoDB 和 MyISAM 兩種存儲(chǔ)，如果你關(guān)注的是其它存儲(chǔ)引擎，請(qǐng)參閱對(duì)應(yīng)的文檔。

我們來看一下 VARCHAR 和 CHAR 是如何在磁盤上存儲(chǔ)的。存儲(chǔ)引擎在內(nèi)存和磁盤上存儲(chǔ) VARCHAR 和 CHAR 的方式可能不一樣，不僅如此，當(dāng) MySQL 把字符串從存儲(chǔ)引擎中讀出來的時(shí)候還會(huì)進(jìn)行二次轉(zhuǎn)化。以下是兩種類型的一般比較：

VARCHAR

VARCHAR 存儲(chǔ)的是變量長(zhǎng)度的字符串，也是最常見的數(shù)據(jù)類型。VARCHAR 比定長(zhǎng)的字符串可以占用更少的存儲(chǔ)空間，因?yàn)?VARCHAR 存儲(chǔ)短的字符串就只需要更少的空間。但是在 MyISAM 中用 ROW_FORMAT=FIXED 創(chuàng)建出來的表，用的就是占用固定的磁盤空間，這可能會(huì)帶來存儲(chǔ)資源的浪費(fèi)。

VARCHAR 借助 1 或者 2 個(gè)額外的字節(jié)來記錄字符串的長(zhǎng)度。如果字段的最大長(zhǎng)度小于等于 255 個(gè)字節(jié)，就用一個(gè)字節(jié)，如果大于 255 就用 兩個(gè)字節(jié)。在拉丁語(yǔ)字符集情況下，VARCHAR(10) 最高占用 11 個(gè)字節(jié)的存儲(chǔ)空間，VARCHAR(1000) 占用 1002 個(gè)字節(jié)，因?yàn)轭~外需要兩個(gè)字節(jié)來存儲(chǔ)。

VARCHAR 對(duì)性能提升有幫助，因?yàn)檎加酶俚目臻g。但是，因?yàn)槭情L(zhǎng)度可變的，這可能會(huì)帶來額外的開銷。如果長(zhǎng)度超過的了最大值，不同的存儲(chǔ)引擎會(huì)有不同的策略。例如，MyISAM 會(huì)將一行數(shù)據(jù)切分，而 InnoDB 可能會(huì)將表分成多個(gè) page，其他引擎可能根本就不會(huì)在原數(shù)據(jù)上做修改。

當(dāng)最長(zhǎng)字符串長(zhǎng)度遠(yuǎn)大于平均字符串長(zhǎng)度，字符串更新很少見(因?yàn)樯衔奶岬降那蟹志筒粫?huì)出現(xiàn))，并且使用 UTF-8 這樣的復(fù)雜字符集(每一個(gè)字都占用不同字節(jié)數(shù)量的存儲(chǔ)空間)的時(shí)候，VARCHAR 是一個(gè)不錯(cuò)的選擇，。

MySQL 5.0 以及以后的版本中，會(huì)保留字符串后面的空格，而在 4.1 以及以前的版本中，MySQL 會(huì)將尾部空格剔除。

CHAR

CHAR 是定長(zhǎng)的：MySQL 總是按照一定數(shù)量劃分出足夠的空間，存儲(chǔ) CHAR 的時(shí)候，MySQL 總是刪除尾隨空格。CHAR 在相互比較的時(shí)候?yàn)榱碎L(zhǎng)度相同，會(huì)在尾部添加空位。

如果存儲(chǔ)很短的字符串，或者所有字符串都基本一樣長(zhǎng)，CHAR 是一個(gè)更好的選擇。比如說，CHAR 特別適合存儲(chǔ)密碼的 MD5 值，MD5 值的長(zhǎng)度都是相同固定的。對(duì)于經(jīng)常變化的數(shù)據(jù)，CHAR 也是一個(gè)比 VARCHAR 更好的選擇，因?yàn)槎ㄩL(zhǎng)字符串不會(huì)出現(xiàn)分片現(xiàn)象。對(duì)于很短的字符串字段，CHAR 有更好的性能；CHAR(1) 用來存儲(chǔ) Y 和 N 在單字節(jié)字符集中只需要一個(gè)字節(jié)，而 VARCHAR(1) 則需要兩個(gè)字節(jié)，其中一個(gè)用來記錄長(zhǎng)度。

CHAR 和 VARCHAR 的姊妹類型是 BINARY 和 VARBINARY，用來存儲(chǔ)二進(jìn)制字符串。二進(jìn)制字符串和普通字符串類似，只是存的是字節(jié)，不是字母。在尾部添加空位也不一樣：MySQL 給 BINARY 尾部添位用的是 \0 ，而不是空格，而且不會(huì)剔除尾部 \0

當(dāng)數(shù)據(jù)是二進(jìn)制的，而且需要比較的情況下，BINARY 和 VARBINARY 是比較好的選擇。二進(jìn)制比較的優(yōu)點(diǎn)除了不區(qū)分大小寫，MySQL 是一個(gè)一個(gè)字節(jié)地比較 BINARY 字符串，比較每個(gè)字節(jié)的數(shù)值，這樣做的結(jié)果就是二進(jìn)制比較會(huì)更加簡(jiǎn)單快速。

一般化的處理方式可能會(huì)適得其反

存儲(chǔ) “hello” 這個(gè)單詞對(duì)于 VARCHAR(5) 和 VARCHAR(200) 需要的存儲(chǔ)空間是一樣的，那用 VARCHAR(5) 好在哪里呢？

確實(shí)會(huì)好很多，VARCHAR(200) 會(huì)消耗更多的內(nèi)存，因?yàn)?MySQL 會(huì)分配固定的內(nèi)存空間，這對(duì)排序和內(nèi)存操作帶來很大壓力。

BLOB 和 TEXT 類型

BLOB 和 TEXT 是用來存儲(chǔ)占體很大的數(shù)據(jù)，分別以二進(jìn)制和字符的形式存儲(chǔ)。

實(shí)際上，不止是 BLOB 和 TEXT 這兩個(gè)，還有 TINYTEXT，SMALL TEXT，TEXT，MEDIUMTEXT，和 LONGTEXT，TINYBLOB，SMALLBLOB，BLOB，MEDIUMBLOB，和 LONGBLOB。 BLOB 等同于 SMALLBLOB， TEXT 等同于 SMALLTEXT。

和其它數(shù)據(jù)類型不同的是，MySQL 存儲(chǔ) BLOB 和 TEXT的方式有些特殊，InnoDB 會(huì)用一個(gè)單獨(dú)的“外部”存儲(chǔ)區(qū)域，表里實(shí)際存的是這個(gè)外部區(qū)域的位置信息(1 到 4 個(gè)字節(jié))。

BLOB 和 TEXT 的唯一區(qū)別是 BLOB 類型存儲(chǔ)的是二進(jìn)制數(shù)據(jù)，沒有 collation 和字符集，而 TEXT 則有字符集和 collation。

MySQL 用了和其它類型不同的方法給 BLOB and TEXT 排序：不會(huì)給整個(gè)字符串排序，而是給前 max_sort_length 數(shù)量的字節(jié)排序。如果你只想根據(jù)前面幾個(gè)字符排序，要么降低 max_sort_length 要么使用 ORDER BY SUBSTRING(column, length)

MySQL 無法按照這些數(shù)據(jù)類型的原長(zhǎng)建立索引，也不能在排序的時(shí)候用到索引。

BLOB 和 TEXT 類型 - 磁盤臨時(shí)表 & 排序

因?yàn)閮?nèi)存存儲(chǔ)引擎(memory storage engine)不支持 BLOB 和 TEXT 類型，查詢 BLOB 和 TEXT 字段并且需要?jiǎng)?chuàng)建臨時(shí)表的話的查詢語(yǔ)句需要使用 MyISAM 的磁盤臨時(shí)表(Percona 的內(nèi)存存儲(chǔ)引擎也支持)，即使是訪問小部分?jǐn)?shù)據(jù)也是如此。

這會(huì)對(duì)性能帶來很嚴(yán)重的影響，即便你將 MySQL 的配置設(shè)置成在內(nèi)存存放臨時(shí)表，也會(huì)導(dǎo)致很多的大開銷的系統(tǒng)調(diào)用。

最好的方法是不到萬(wàn)不得已的情況下不要使用 BLOB 和 TEXT 類型。如果一定需要的話，就在需要使用 BLOB 字段的語(yǔ)句(包括 ORDER BY 語(yǔ)句)中使用 SUBSTRING(column, length)，將其轉(zhuǎn)換成字符形式，因?yàn)檫@樣就可以使用內(nèi)存臨時(shí)表了。這里要確保使用比較短的 substring，保證臨時(shí)表不會(huì)比 max_heap_table_size 或者 tmp_table_size 更大，否則 MySQL 會(huì)將表變成磁盤形式的 MyISAM 表。該方法同樣適用于排序。

舉個(gè)例子，假設(shè)有一個(gè)一千萬(wàn)行的表，占用了幾個(gè)G的磁盤空間，表中包含一個(gè) VARCHAR(1000)，utf8字符集的字段，每個(gè)字母最多占用 3 個(gè)字節(jié)，所以最長(zhǎng)的字符串值有 3000 個(gè)字節(jié)。如果在 ORDER BY 語(yǔ)句中涉及該字段，那么整個(gè)表的查詢會(huì)產(chǎn)生 30 多個(gè) GB 的臨時(shí)表用來排序！

如果 EXPLAIN 中的 “Extra” 字段包含 “Using temporary”，那么代表著查詢語(yǔ)句用到了臨時(shí)表。

ENUM

有些時(shí)候可以用 ENUM 替代字符串類型，ENUM 字段存儲(chǔ)某些預(yù)設(shè)好的字符串值。MySQL 用非常節(jié)省空間的方式存儲(chǔ) ENUM，根據(jù)預(yù)設(shè)值的個(gè)數(shù)，只用一個(gè)或者兩個(gè)字節(jié)存儲(chǔ)，存儲(chǔ)方式是用一個(gè)整數(shù)當(dāng)作字段值，這個(gè)整數(shù)等于所有預(yù)設(shè)值列表里某個(gè)具體字符串的下表，通過一個(gè)查找表來維護(hù)這個(gè)映射。舉個(gè)例子：

mysql> CREATE TABLE enum_test(

-> e ENUM('fish', 'apple', 'dog') NOT NULL

-> );

mysql> INSERT INTO enum_test(e) VALUES('fish'), ('dog'), ('apple');

三行數(shù)據(jù)實(shí)際上存儲(chǔ)的是數(shù)字，不是字符串：

mysql> SELECT e + 0 FROM enum_test;

+-------+

| e + 0 |

+-------+

| 1 |

| 3 |

| 2 |

+-------+

如果 ENUM 的真實(shí)值是數(shù)字，那么這會(huì)導(dǎo)致很大的歧義，所以Jack建議不要這么做。

另外，ENUM 是根據(jù)對(duì)應(yīng)的整數(shù)值排序的，不是按照真實(shí)數(shù)據(jù)：

mysql> SELECT e FROM enum_test ORDER BY e;

+-------+

| e |

+-------+

| fish |

| apple |

| dog |

+-------+

解決辦法是，可以在定義 ENUM 成員值的時(shí)候，有意按照順序指定真實(shí)值，你也可以使用 FIELD() 來明確指出一個(gè)查詢語(yǔ)句的相關(guān)順序，但是這樣 MySQL 就無法在排序的時(shí)候使用索引了：

mysql> SELECT e FROM enum_test ORDER BY FIELD(e, 'apple', 'dog', 'fish');

+-------+

| e |

+-------+

| apple |

| dog |

| fish |

+-------+

像上面這種情況，如果我們定義 ENUM 值的時(shí)候就按照字母排好序，我們之后就不需要用 FIELD() 了。

ENUM 一個(gè)最大的缺點(diǎn)就是預(yù)設(shè)值不能隨意再添加，如果要添加或者刪除需要用到 ALTER TABLE，所以如果預(yù)設(shè)值在將來可能會(huì)變動(dòng)，ENUM 可能不是一個(gè)最佳選擇，除非 ALTER TABLE 不會(huì)對(duì)應(yīng)用產(chǎn)生大的影響，這在 MySQL 5.1 有比較好的支持。

因?yàn)?MySQL 存儲(chǔ)的是整數(shù)，而且需要將整數(shù)映射到具體的預(yù)設(shè)值，ENUM 字段會(huì)有一些額外的開銷。這可以通過 ENUM 的小范圍預(yù)設(shè)值來抵消，但不是所有情況都是如此，比如說把 CHAR 或者 VARCHAR 字段和一個(gè) ENUM 字段 join 起來，會(huì)比和另一個(gè) CHAR 或者 VARCHAR 字段 join 起來要慢。

為了展示說明，這里通過一個(gè)舉例來簡(jiǎn)單看一下 MySQL 執(zhí)行 join 的速度。我們有一個(gè) primary key 比較“寬”的表：

CREATE TABLE webservicecalls (

day date NOT NULL,

account smallint NOT NULL,

service varchar(10) NOT NULL,

method varchar(50) NOT NULL,

calls int NOT NULL,

items int NOT NULL,

time float NOT NULL,

cost decimal(9,5) NOT NULL,

updated datetime,

PRIMARY KEY (day, account, service, method)

) ENGINE=InnoDB;

這個(gè)表有大概11萬(wàn)行數(shù)據(jù)，大小只有 10 MB，所以整個(gè)表可以放入內(nèi)存。service 字段包含 5 個(gè)預(yù)設(shè)值，平均長(zhǎng)度是 4 個(gè)字符，method 字段包含 71 個(gè)預(yù)設(shè)值，平均長(zhǎng)度是 20 個(gè)字符。

我們復(fù)制了一份這個(gè)表，并且將 service 和 method 字段轉(zhuǎn)成了 ENUM：

CREATE TABLE webservicecalls_enum (

... 省略 ...

service ENUM(...預(yù)設(shè)值省略...) NOT NULL,

method ENUM(...預(yù)設(shè)值省略...) NOT NULL,

... 省略 ...

) ENGINE=InnoDB;

然后我們測(cè)量了一下根據(jù) primary key 來 join 表的性能：

mysql> SELECT SQL_NO_CACHE COUNT(*)

-> FROM webservicecalls

-> JOIN webservicecalls USING(day, account, service, method);

我們將上述查詢語(yǔ)句稍作修改，來相互 join VARCHAR 和 ENUM 字段，一下是測(cè)量結(jié)果：VARCHAR join VARCHAR：2.6 秒

VARCHAR join ENUM： 1.7 秒

ENUM join VARCHAR： 1.8 秒

ENUM join ENUM： 3.5 秒

將被 join 的字段改成 ENUM 之后，速度提升，但是 ENUM join VARCHAR 慢了一些，所以我們發(fā)現(xiàn)如果 VARCHAR 字段不是被 join 的情況，可以使用 ENUM。通常情況下，用 ENUM 這樣的整數(shù)對(duì)照表，比 join 字符要來的高效。

然而，還有一個(gè)將 service 和 method 字段轉(zhuǎn)成 ENUM 的好處：根據(jù) SHOW TABLE STATUS 中的 Data_length 字段，轉(zhuǎn)換之后的表縮小了三分之一。在某些時(shí)候，存儲(chǔ)方面帶來的提升可能大過 ENUM join VARCHAR 帶來的性能耗損。而且，primary key 也小了一半。在 InnoDB 引擎中，縮小 primary key 也能讓索引大大縮小

Date 和 Time 類型

MySQL 針對(duì)日期和時(shí)間有多種不同的類型。比如 YEAR 和 DATE。MySQL 能存儲(chǔ)的最小時(shí)間單位是秒(MariaDB 是微秒)，但卻能在微秒級(jí)別上進(jìn)行計(jì)算。

大多數(shù)時(shí)間相關(guān)的數(shù)據(jù)類型沒有替代類型，所以也就不存在哪個(gè)更好的說法。唯一的問題就是，如果需要同時(shí)存儲(chǔ)日期和時(shí)間，該怎么辦。對(duì)此 MySQL 提供兩個(gè)類似的數(shù)據(jù)類型：DATETIME 和 TIMESTAMP。對(duì)于大多數(shù)應(yīng)用來說，兩者都可以，但某些情況下，就有優(yōu)劣之分了，我們具體來看一下：

DATETIME：

這個(gè)類型可以存儲(chǔ)大范圍的數(shù)據(jù)，從 1001 年到 9999 年，精度到達(dá)秒級(jí)，存儲(chǔ)日期和時(shí)間用一個(gè)整數(shù)形式表示：YYYYMMDDHHMMSS，無時(shí)區(qū)，使用 8 個(gè)字節(jié)的存儲(chǔ)空間。

默認(rèn)情況下，MySQL 顯示 DATETIME 的格式是 “2008-01-16 22:37:08”，這是 ANSI 標(biāo)準(zhǔn)下規(guī)范的日期時(shí)間表示方法

TIMESTAMP：

顧名思義，TIMESTAMP 存儲(chǔ)的是從 格林尼治時(shí)間(GMT)1970 年 1 月 1 日凌晨過去了多少秒，和 Unix 時(shí)間戳是一樣的。TIMESTAMP 只需要 4 個(gè)字節(jié)存儲(chǔ)，所以可能表示的時(shí)間范圍比 DATETIME 小很多：從 1970 年到 2039 年。MySQL 提供 FROM_UNIXTIME() 和 UNIX_TIMESTAMP() 進(jìn)行 Unix 時(shí)間和日期之間的轉(zhuǎn)換。

MySQL 4.1 和之后的版本用和展示 DATETIME 相同的方式展示 TIMESTAMP 的值，但是 MySQL 4.0 和之前的版本在展示的時(shí)候沒有標(biāo)點(diǎn)符號(hào)，但這僅僅只是展示形式的不同，TIMESTAMP 的存儲(chǔ)格式在所有 MySQL 的版本中都是一樣的

TIMESTAMP 的展示數(shù)值也取決于時(shí)區(qū)，MySQL，操作系統(tǒng)，和訪問客戶端都有自己的時(shí)區(qū)設(shè)置。所以，當(dāng) TIMESTAMP 存儲(chǔ)的是 0 的時(shí)候，展示出來的是 1969-12-31 19:00:00 EST，和 GMT 有 5 個(gè)小時(shí)的時(shí)差。這個(gè)區(qū)別需要注意：如果是在不同時(shí)區(qū)存儲(chǔ)和訪問時(shí)間數(shù)據(jù)，TIMESTAMP 和 DATETIME 的應(yīng)對(duì)方式是不同的。前者是帶了時(shí)區(qū)，后者強(qiáng)調(diào)日期和時(shí)間的展示形式。

TIMESTAMP 也有 DATETIME 不具備的特殊性質(zhì)。默認(rèn)情況下，如果沒有制定具體的值，MySQL 會(huì)把第一個(gè) TIMESTAMP 字段的值設(shè)成當(dāng)前時(shí)間

一般情況下，如果可以用 TIMESTAMP 盡量用，因?yàn)?TIMESTAMP 比 DATETIME 更節(jié)省存儲(chǔ)空間。一些人會(huì)將 UNIX 時(shí)間戳存成整數(shù)形式，但是這樣并沒有什么優(yōu)勢(shì)，整數(shù)形式比較不和規(guī)范，所Jack我建議不要這么做。

那如果需要存儲(chǔ)秒級(jí)一下的數(shù)據(jù)呢？比如毫秒。MySQL 目前沒有特定的數(shù)據(jù)格式支持，但是你可以用現(xiàn)有的類型做一些設(shè)計(jì)：你可以用 BIGINT 存儲(chǔ)微秒級(jí)的時(shí)間戳，或者你可以用 DOUBLE 來存儲(chǔ)帶小數(shù)點(diǎn)的秒級(jí)數(shù)據(jù)。哪種方法都合適，或者可以直接用 MariaDB

Bit-存儲(chǔ)數(shù)據(jù)類型

MySQL 里面的少數(shù)存儲(chǔ)類型支持用數(shù)據(jù)中的某些 bit ，用壓縮的方式存儲(chǔ)數(shù)據(jù)。所有這些格式是用字符串形式存儲(chǔ)

BIT

在 MySQL 5.0 之前，BIT 就是 TINYINT，在是在 MySQL 5.0 和以后的版本中，就是一個(gè)完全不同的數(shù)據(jù)類型

你能用 BIT 字段存儲(chǔ)一個(gè)或者多個(gè) true/false 值，BIT(1) 定義了一個(gè)包含了單個(gè) bit 的值，BIT(2) 包含兩個(gè) bit，以此類推，BIT 字段的最大程度是 64 個(gè) bits。

BIT 的性質(zhì)在不同存儲(chǔ)引擎中是不一樣的。MyISAM 將所有 bit 字段放在一起，所以 17 個(gè) BIT 字段只需要 17 個(gè) bit 就行(字段值不能出現(xiàn) NULL), MyISAM 將其轉(zhuǎn)換成 3 個(gè)字節(jié)存儲(chǔ)。其他存儲(chǔ)引擎中，例如內(nèi)存的 InnoDB，將 bit 用最小整數(shù)表示，這樣會(huì)比較消耗存儲(chǔ)空間。

MySQL 將 BIT 當(dāng)作字符串對(duì)待，而不是數(shù)值。當(dāng)你讀取一個(gè) BIT(1) 字段的時(shí)候，顯示出來的實(shí)際上是一個(gè)字符串，值是 0 或者 1，而不是 ASCII ”0“ 或者 ”1“。但是，如果用數(shù)值的方法讀取，那結(jié)果就是數(shù)值。例如，如果存儲(chǔ)的是 b'00111001' (相當(dāng)于數(shù)字 57)，將其存儲(chǔ)在 BIT(8) 字段中，讀取之后，你會(huì)得到字母編碼是 57 的字符串，這也是 ”9“ 的ASCII 字符碼：

mysql> CREATE TABLE bittest(a bit(8));

mysql> INSERT INTO bittest VALUES(b'00111001');

mysql> SELECT a, a + 0 FROM bittest;

+------+-------+

| a | a + 0 |

+------+-------+

| 9 | 57 |

+------+-------+

這會(huì)產(chǎn)生很多歧義，Jack建議慎用 BIT，在大多數(shù)場(chǎng)景下，最好不要使用 BIT 類型

如果你需要將 true/false 值存在一個(gè)單個(gè) bit 的字段中，另一個(gè)方法就是用 nullable 的 CHAR(0) 字段，該字段可以存儲(chǔ)無數(shù)據(jù)(NULL)和長(zhǎng)度是0的數(shù)據(jù)(空字符串)

SET

如果你需要存儲(chǔ)多個(gè) true/false 值，可以考慮用 MySQL 自帶的 SET 數(shù)據(jù)類型將其合并成一個(gè)字段，MySQL 對(duì)此的存儲(chǔ)方式是用一個(gè) bit set。存儲(chǔ)起來非常高效，MySQL 還可以利用 FIND_IN_SET() 和 FIELD() 優(yōu)化查詢語(yǔ)句。最大的缺點(diǎn)就是如果需要修改字段的定義，那么代價(jià)就很高：需要 ALTER TABLE 語(yǔ)句，這在大的表中代價(jià)是很大的，而且，你不能在 SET 字段上用索引

整數(shù)字段上的位運(yùn)算

另外一種和 SET 有相同效果的方案是始終整數(shù)表示一個(gè) bit 集合，例如，你可以將 8 個(gè) bits 放入一個(gè) TINYINT，用位運(yùn)算符來處理它們。在應(yīng)用中，還可以利用代名稱的常數(shù)來表示每一個(gè) bit，簡(jiǎn)化復(fù)雜度。

這種方法的優(yōu)點(diǎn)在于我們可以不需要 ALTER TABLE 就能改變字段含義。缺點(diǎn)是你的查詢語(yǔ)句會(huì)變的比較復(fù)雜和難懂，有些人喜歡位運(yùn)算，覺得逼格高，但是有些人則不喜歡，所以這主要還是看個(gè)人和團(tuán)隊(duì)喜好。

一個(gè)合并 bits 的應(yīng)用就是 ACL，控制訪問權(quán)限。每個(gè) bit 或者 SET 元素代表了一個(gè)訪問權(quán)限開關(guān)，比如 CAN_READ，CAN_READ，CAN_DELETE。如果你使用的是 SET 字段類型，那么你會(huì)依賴 MySQL 的 bit 映射表；如果使用整數(shù)字段，映射表會(huì)放在應(yīng)用當(dāng)時(shí)。一個(gè) SET 字段查詢語(yǔ)句舉例：

mysql> CREATE TABLE acl (

-> perms SET('CAN_READ', 'CAN_WRITE', 'CAN_DELETE') NOT NULL

-> );

mysql> INSERT INTO acl(perms) VALUES ('CAN_READ,CAN_DELETE');

mysql> SELECT perms FROM acl WHERE FIND_IN_SET('AN_READ', perms);

+---------------------+

| perms |

+---------------------+

| CAN_READ,CAN_DELETE |

+---------------------+

如果你用的是一個(gè)整數(shù)，那么上述舉例就是：

mysql> SET @CAN_READ := 1 << 0,

-> @CAN_WRITE := 1 << 1,

-> @CAN_DELETE := 1 << 2;

mysql> CREATE TABLE acl (

-> perms TINYINT UNSIGNED NOT NULL DEFAULT 0

-> );

mysql> INSERT INTO acl(perms) VALUES(@CAN_READ + @CAN_DELETE);

mysql> SELECT perms FROM acl WHERE perms & @CAN_READ;

+-------+

| perms |

+-------+

| 5 |

+-------+

我們這里使用的變量來定義，但你也可以在應(yīng)用中使用自己的常數(shù)。

ID 字段的類型選擇

給 ID 類型的字段選擇一個(gè)合適的字段類型至關(guān)重要。這些字段經(jīng)常會(huì)被拿去和其它字段比較(比如 join)，或者用這點(diǎn)字段去查找其它字段。你也會(huì)用它當(dāng)多 foreign keys，所以如果是給這些字段選擇類型，還需要考慮其他相關(guān)表的相關(guān)字段類型。

在選擇類型的時(shí)候，你不僅要考慮存儲(chǔ)方面的問題，還需要結(jié)合 MySQL 是如何對(duì)這個(gè)類型進(jìn)行計(jì)算和比較的。比如，MySQL 存儲(chǔ) ENUM 和 SET 類型的方式是用整數(shù)，在比較字符串的時(shí)候會(huì)將其轉(zhuǎn)換成字符串。

當(dāng)選定了一個(gè) ID 字段的類型之后，在其它表的相關(guān)字段中也需要使用相同的類型，需要完全相同，比如是否都是 UNSIGNED

選擇占用空間最小的類型來存儲(chǔ)，適當(dāng)留出額外空間來應(yīng)對(duì)將來的增長(zhǎng)需求。比如，如果你要 province_id 字段存儲(chǔ)國(guó)內(nèi)所有的省份，你不需要存儲(chǔ)幾百萬(wàn)個(gè)數(shù)值，所以不要用 INT，TINYINT 就足夠了而且還省了 3 個(gè)字節(jié)的存儲(chǔ)空間，如果你將這個(gè)字段當(dāng)作 oreign key 和其它表相連，3 個(gè)字節(jié)能帶來巨大的性能差異。這里Jack給幾點(diǎn)建議：整數(shù)類型：整數(shù)類型是 ID 類型的最佳選擇，因?yàn)樘幚砥饋砗芸?#xff0c;并且可以使用 AUTO_INCREMENT

ENUM 和 SET：ENUM 和 SET 非常不適合用作 ID 類型的字段，只是在小的靜態(tài)定義類型的表中可以用作 ID 類型的字段。ENUM 和 SET 字段適合存儲(chǔ)比如訂單狀態(tài)，產(chǎn)品類型，或者性別等數(shù)據(jù)。舉個(gè)例子，如果你用 ENUM 定義產(chǎn)品類型，那么你需要另一個(gè) ID 是 ENUM 字段的表，但這樣的情況應(yīng)該避免。

字符串類型：盡量避免使用字符串類型的 ID 字段，因?yàn)檎加锰嗟目臻g而且比整數(shù)型要慢很多。而且要慎用

如果需要存儲(chǔ) UUID 數(shù)據(jù)，你應(yīng)該將 UUID 中的中劃線剔除，或者用 UNHEX() 將其轉(zhuǎn)換成 16 字節(jié)的數(shù)字，然后存儲(chǔ)在 BINARY(16) 的字段中，之后你就能用 HEX() 將它們讀成16進(jìn)制的數(shù)字。

特殊類型的數(shù)據(jù)

某些數(shù)據(jù)沒有直接對(duì)應(yīng)的存儲(chǔ)類型，毫秒級(jí)別的時(shí)間戳就是一個(gè)例子。我們?cè)谏衔漠?dāng)中已經(jīng)闡述了如何存儲(chǔ)這類型的數(shù)據(jù)。

另一個(gè)例子就是 IPv4 地址。人們通常使用 VARCHAR(15) 來存儲(chǔ)，但是，IPv4 實(shí)際上是 unsigned 的 32 位整數(shù)，不是字符串。里面的點(diǎn)符號(hào)只是為了方便大家閱讀罷了。我們應(yīng)該將 IP 地址存成 unsigned 整數(shù)。MySQL 支持用 INET_ATON() 和 INET_NTOA() 將兩者相互轉(zhuǎn)換。

總結(jié)費(fèi)腦，碼字不易，如若有用，還請(qǐng)點(diǎn)贊，分享更贊～

參考^某些第三方存儲(chǔ)引擎，例如 Infobright，在存儲(chǔ)格式和壓縮方式上和大多數(shù) MySQL 原生支持的存儲(chǔ)引擎很不一樣

^讀取 BINARY 的時(shí)候要格外注意，MySQL 會(huì)在尾部添加 \0，所以如果要比較 BINARY，一定要記住這一點(diǎn)！

^在不同 MySQL 版本之間，TIMESTAMP 有不同的復(fù)雜規(guī)則，所以你需要親自驗(yàn)證

^如果你使用 InnoDB，如果字段類型不完全一樣，是不能創(chuàng)建 foreign key 的

總結(jié)

以上是生活随笔為你收集整理的mysql访问类型最好的_【干货满满】最全的MySQL性能指南（一）：选择最佳的数据类型...的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。