雲栖号資訊：【 點選檢視更多行業資訊】
在這裡您可以找到不同行業的第一手的上雲資訊，還在等什麼，快來！
最近需要對表加一個字段，同時覺得前期建立表的時候有點粗暴，沒有加很對限制，比如有些字元串長度是有限制的，在建立表時字段也沒有對其進行限制。是以想借着這次加字段對表字段也進行一個優化，在優化之前先看了點理論知識，理論指導實踐。
寫在前面
選擇合适的字段類型既可以節省空間，又可以在查詢上提高效率，是以字段類型選擇是很重要的。本篇文章将介紹常用字段類型：
- 整數類型
- 實數類型
- 字元串類型
- 日期和時間
- 枚舉類型
整數類型有TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT，存儲空間及數值範圍如下表
數值範圍為-2^(N-1) ~ 2^N， 其中N為存儲空間大小
整數類型有可選的UNSIGNED屬性，不允許出現負值。設定UNSIGNED屬性可以使正數的上限提高一倍，數值範圍大小為 0 ~ 2^(N-1) + 2^N
一般選擇最小的能夠滿足存儲的類型就行，更小的資料類型通常更快，占用更少的磁盤、記憶體和CPU緩存。處理時需要的CPU周期也更少。
FLOAT
單精度浮點型，使用8位
DOUBLE
雙精度浮點型，使用16位存儲
DECIMAL
float和double進行計算時會發生精度損失，需要精度計算的時候可以使用DECIMAL，使用DECIMAL需要額外的空間和計算開銷，是以當且僅當需要精度計算時才使用。
1. VARCHAR和CHAR
varchar和char是非常非常常用的字元串類型
VARCHAR
VARCHAR用于存儲變長字元串，使用該類型存儲字元串時需要額外使用1或2個額外位元組記錄字元串的長度：
- 列的最大長度小于或等于255 => 使用1位元組
- 列的長度大于255 => 使用2位元組
适用VARCHAR作為存儲類型的場景：
- 列更新很少 => 列經常更新容易産生頁分裂
- 列長度非固定 => VARCHAR存儲時隻使用必要空間，是以會省空間
CHAR
CHAR用于存儲定長字元串，在存儲CHAR類型時，會删除所有的末尾空格
使用CHAR最為存儲類型的場景
- 列幾乎定長
- 列長度很短 => VARCHAR需要額外位元組存儲長度
- 列經常更新
2. BLOB和TEXT類型
BLOB和TEXT類型都是用來存儲很大的資料，比如文章内容這些
BLOB
采用二進制方式存儲， BLOB細分又可以分為TINYBLOB，SMALLBLOB，BLOB，MEDIUMBLOB， LONGBLOB
TEXT
采用字元方式存儲，TEXT細分又可以分為TINYTEXT，SMALLTEXT，TEXT，MEDIUMTEXT， LONGTEXT
當BLOB和TEXT值太大時，InnoDB存儲會使用外部存儲區域來存儲值，然後儲存一個1~4位元組的指針指向外部存儲
日期和時間類型
常用的日期類型有DATETIME和TIMESTAMP
DATETIME
使用8位元組存儲，可以儲存大範圍的值，從1001~9999年
TIMESTAMP
使用4位元組存儲，儲存範圍比DATETIME小，從1970~2038年
對于需要存儲更小粒度的日期和時間可以使用DOUBLE或BIGINT，當然不是存儲小粒度也可以使用BIGINT
DATETIME和TIMESTAMP如何選擇
之前曾因為時間類型搞出過線上慢查詢，這篇文章記錄了慢查詢原因：很高興！終于踩到了慢查詢的坑， 對于需要對時間進行範圍查找、排序、分組等操作之類的建議使用BIGINT，如果對時間類型字段沒有任何操作，建議使用TIMESTAMP。
在stackoverflow下找到如下：
可以使用枚舉列代替常用的字元串類型，通過枚舉可以限制值的取值範圍
枚舉使用
建立表語句：
插入語句：
對于status字段底層存儲的是整數而不是字元串，在底層會維護一個 數字 - 字元串的映射關系
查詢語句并根據status字段進行排序：
查詢結果：
說明：
- 排序的結果是根據内部存儲的整數來的而不是定義的字元串進行排序的
- 底層存儲的是整數，根據映射關系轉化為字元串，是以會有一定的開銷
為什麼使用TINYINT而不用ENUM
之前建立表的時候對于常用字元串的代替選擇的都是TINYINT類型，應用層在做轉換。當看到ENUM類型時有點困惑，為什麼沒選擇使用ENUM而是TINY，網上查找了一下原因，如下圖：
總結原因如下：
- 不友善遷移，可擴充性弱，如比較熟悉的PostgreSQL資料庫就不支援ENUM類型
- ENUM字段添加或删除字元串時會進行表重構，這個操作非常耗時和耗性能
- 有坑 以之前的表dataset_enum為例插入資料：
成功插入了資料 查詢資料：
數值類型做轉化以後也可以插入
- 無法與其他表做關聯
【雲栖号線上課堂】每天都有産品技術專家分享！
課程位址：
https://yqh.aliyun.com/live立即加入社群，與專家面對面，及時了解課程最新動态！
【雲栖号線上課堂 社群】
https://c.tb.cn/F3.Z8gvnK
原文釋出時間：2020-05-08
本文作者：何甜甜在嗎
本文來自：“
掘金”，了解相關資訊可以關注“掘金”