【MySQL】Schema与数据类型优化
目錄
選擇優化的數據類型
整數類型
實數類型
字符串類型
日期和時間類型
位置據類型
選擇標識符
特殊類型數據
MySQL schema 設計中的陷阱
范式和反范式
緩存表和匯總表
加快 ALTER TABLE 操作的速度
快速創建 MyISAM 索引
選擇優化的數據類型
不管存儲哪種數據類型,下面幾個簡單的原則都有助于做出更好的選擇。
更好的通常更小
??盡量使用可以正確存儲數據的最小數據類型,但是要確保沒有低估需要存儲的值的范圍,因為在schema中的多個地方增加數據類型的范圍是一個非常耗時和痛苦的操作
簡單就好
??簡單數據類型的操作通常需要更少的CPU周期
盡量避免NULL
??? 列最好指定為 NOT NULL,除非確實可以是 NULL。如果查詢中包含可為 NULL 的列,會使索引、索引統計和值比較都更復雜。當可為NULL的列被索引時,每個索引記錄需要一個額外的字節,在MyISAM里甚至還可能導致固定大小的索引變成可變大小的索引。
??? 把 NULL 列改為 NOT NULL 列帶來的性能提升比較小,但是用于索引的列最好是 NOT NULL。另外,InnoDB 使用單獨的位(bit)存儲 NULL 值,包含很多 NULL 值的稀疏數據的空間效率比較高。
整數類型
可用的整型數據有:
TINYINT:8 bit
SMALLINT:16 bit
MEDIUMINT:24 bit
INT:32 bit
BIGINT:64 bit
所有的整數類型都支持 UNSIGNED 屬性,表示無符號數據,例如 TINYINT UNSIGNED。有符號和無符號整型數據存儲空間一樣,性能一樣。
MySQL 中可以為整數類型指定寬度,例如 INT(11),這個寬度只是規定了 MySQL 交互工具用來顯示字符的個數,不影響存儲和計算。
實數類型
可用的實數數據有:
浮點類型:
FLOAT:32 bit。不精確類型,支持浮點運算。
DOUBLE:64 bit。不精確類型,支持浮點運算。
DECIMAL:最多允許 65 個數字,例如 DECIMAL(33,32),DECIMAL(65,0)。可以存儲精確的小數,也可以存儲比 BIGINT 還大的整數,支持精確計算。因為 CPU 不支持對 DECIMAL 的直接計算,所以 MySQL 服務器自身實現了 DECIMAL 的高精度計算。
MySQL 支持精確類型(DECIMAL),也支持不精確類型(浮點類型,即 FLOAT 和 DOUBLE)。
FLOAT、DOUBLE 和 DECIMAL 類型都可以指定精度。例如 DOUBLE(12,3) 表示最多 9 位整數、3 位小數。DECIMAL(18,9) 表示小數點兩邊各存儲 9 個數字,一共使用 9 個字節(小數點占一個字節)。
DECIMAL 需要的存儲空間大,計算開銷高,如果數據量很大,可以考慮用 BIGINT 替代 DECIMAL。例如金額需要保證 0.001 的精度,則可以將所有金額乘以 1000 后取整存入 BIGINT 類型的字段,從而避免浮點數計算不準確和 DECIMAL 精確計算代價高的問題。
字符串類型
VARCHAR 和 CHAR 類型
存儲引擎存儲CHAR或者VARCHAR值的方式在內存中和磁盤上可能不一樣,所以MySQL服務器從存儲引擎讀出的值可能需要轉換為另一種存儲格式。
VARCHAR
??VARCHAR類型用于存儲可變長字符串,比定長類型更省空間,因為它僅使用必要的空間,有一種情況例外,如果MySQL表使用ROW_FORMAT=FIXED創建,每一行都會使用定長存儲,這會浪費空間。
VARCHAR需要使用1或者2個額外字節記錄字符串的長度,如果列的最大長度小于或等于255字節,則只使用一個字節表示,否則使用兩個字節。
在執行 UPDATA 操作時,如果需要增大行的長度,需要額外工作。如果磁盤當前頁的空間不夠,不同存儲引擎的處理方式不同。MyISAM 會將行拆成不同的片段存儲,InnoDB 則需要分裂頁來使行可以放進頁內。InnoDB 會把過長的 VARCHAR 存儲為 BLOB。
CHAR
CHAR是定長的,MySQL總是根據定義的字符串長度分配足夠的空間。當存儲CHAR值時,MySQL會刪除所有的末尾空格。(VARCHAR不會)
VARBINARY
二進制變長字符串,存儲字節碼而不是字符。
BINARY
二進制定長字符串,存儲字節碼而不是字符,使用 \0(零字節)填充而不是空格,且檢索時不會去掉填充值。
適合使用 VARCHAR 的場景:
字符串列的最大長度比平均長度大得多
列很少更新,所以碎片不是問題
使用 UTF-8 字符集,每個字符用不同的字節數進行存儲
適合使用 CHAR 的場景:
短字符串
定長字符串,例如密碼的 HASH 值
經常變更的字符串,不易產生碎片
BLOB 和 TEXT 類型
BLOB:二進制方式存儲大數據的字符串類型,沒有字符集和排序規則。具體的類型有:TINYBLOB, SMALLBLOB, BLOB, MIDIUMBLOB, LONGBLOB
TEXT:字符方式存儲大數據的字符串類型,有字符集和排序規則。具體的類型有:TINYTEXT, SMALLTEXT, TEXT, MIDIUMTEXT, LONGTEXT
在 MySQL中,每個BLOB 和TEXT類型的值都是獨立對象。當值太大時,InnoDB還會使用專門的外部存儲區域來存儲,此時行內只需 1-4 個字節存儲指針。
??MySQL只對每個列的最前max_sort_length字節而不是整個字符串做排序,MySQL不能將BLOB和TEXT列全部長度的字符串進行索引,也不能使用這些索引消除排序。
ENUM 枚舉類型代替字符串
有時可以使用枚舉列代替常用的字符串類型,MySQL在內部會將每個值在列標中的位置保存為整數,并且在表的.frm文件中保存,”數字-字符串”映射關系的“查找表“。
例:
mysql> CREATE TABLE enum_test(
?e ENUM('fish', 'apple', 'dog') NOT NULL
);
mysql>INSERT INTO enum_test(e) VALUES('fish'), ('dog'), ('apple');
MariaDB [foo]> SELECT e + 0 FROM enum_test;
+-------+
| e + 0 |
+-------+
|???? 1 ?|
|???? 3 ?|
|???? 2 ?|
MariaDB [foo]> SELECT e FROM enum_test;
+-------+
| ?e???? |
+-------+
| fish |
| apple |
| ?dog ??|
+-------+
枚舉字段是根據內部存儲的整數而不是定義的字符串進行排序的
可以使用 FIELD() 函數顯式指定排序順序,但會導致 MySQL 無法利用索引消除排序
枚舉的缺點,字符串列標固定,添加或刪除字符串必須使用ALTER TABLE,因此,對于一系列未來可能會改變的字符串。
日期和時間類型
DATETIME:時間范圍 1001 - 9999 年,精度范圍 1 秒。內部把日期和時間封裝到格式為 YYYYMMDDHHMMSS 的整數中,與時區無關,使用 8 個字節的存儲空間。MySQL 默認以可排序、無歧義的格式顯示 DATETIME 的值,例如 “2018-04-26 16:51:05”。
TIMESTAMP:TIMESTAMP 顯示的值依賴于時區。時間范圍 1970 - 2038 年,保存從 1970 年 1 月 1 日凌時開始的秒數,跟 UNIX 的時間戳一樣。只使用 4 個字節的存儲空間。
如果跨時區訪問或存儲數據,TIMESTAMP 和 DATETIME 的行為完全不一樣。
常見的特殊屬性有:
MySQL 內置函數可以實現時間戳和日期的互相轉換:
FROM_UNIXTIME():將UNIX 時間戳轉為日期。
UNIX_TIMESTAMP():將日期轉為 UNIX 時間戳。
位置據類型
BIT(不建議用)
BIT 列中可以存儲一個或多個 true/false 值。BIT(n) 定義了存儲 n 個位的字段,BIT 列最大支持 64 個位。
BIT 的行為因存儲引擎的不同而不同。MyISAM 會打包存儲所有的 BIT 列,17 個單獨的 BIT 列只需要 17 個位存儲(假設沒有可為 NULL 的列),只需要 3 個字節。假設存儲引擎是 Memory 或 InnoDB,每個 BIT 列使用一個足夠存儲的最小整數來存放,無法節省存儲空間。
MySQL 把 BIT 當做字符串類型,而不是數字類型。當檢索 BIT(1) 的值時,結果是一個包含二進制 0 或 1 的字符串,而不是 ASCII 碼的 0 或 1。但是在數字上下文的場景中,會自動將位字符轉為對應的 ASCII 碼。例如,對于存儲了 b'00111001' 的 BIT(8) 列,正常檢索時得到字符碼為 57 的字符‘9’,但是在數字上下文場景中,得到數字 57。
SET
如果需要保存很多 true/false 值,可以合并這些列到一個 SET 數據類型,在 MySQL 內部以一系列打包的位的集合來表示。存儲空間利用率高,使用方便(可以在查詢中使用 FIELD() 和 FIELD() 函數)。缺點是改變列的定義時,代價較高(需要使用 ALTER TABLE),對于大表很麻煩。
選擇標識符
標識列(identifier column)選擇合適的數據類型很重要。標識列可以與其他值進行比較,或通過標識列尋找其他列。標識列也可以作為其他表中的外鍵。
選擇標識列的類型時,既要考慮存儲類型,也要考慮 MySQL 對這種類型怎么執行計算和比較。例如 MySQL 內部用整數存儲 ENUM 和 SET 類型,在比較操作時轉為字符串。
整數類型
整數是標識列最佳選擇,快,且可以使用 AUTO_INCREMENT
ENUM 和 SET 類型
標識列的糟糕選擇。ENUM 和 SET 列適合存儲固定信息,例如性別、產品類型。
字符串類型
盡量避免,慢,存儲空間大。尤其是 MyISAM,會對字符串壓縮索引。
特殊類型數據
例如IPv4,人們經常用VARCHAR(15)列來存儲IP地址,然而,它們實際上是32位無符號整數,不是字符串,用小數點將地址分成四段的表示方法只是為了讓人閱讀容易,所以應該用無符號整數存儲IP地址。MySQL 提供了 INET_ATON() 和 INET_NTOA() 函數進行轉換。
MySQL schema 設計中的陷阱
太多的列
MySQL的存儲引擎API工作時需要在服務器層和存儲引擎層之間通過行緩沖格式拷貝數據,然后在服務器層將緩沖內容解碼成各個列。從行緩沖中將編碼過的列轉換成行數據結構的操作代價是非常高的。
太多的關聯
如果希望查詢執行得快速且并發性好,單個查詢最好在12個表以內做關聯
全能得枚舉
當需要在枚舉列表中增加一個新的值時,就要做一次ALTER TABLE操作,在MySQL5.0以及更早的版本中ALTER TABLE是一種阻塞操作,在后續版本中,如果不是在列表的末尾增加值也會一樣需要ALTER TABLE。
變相的枚舉
枚舉(ENUM)列允許在列中存儲一組定義值中的單個值,集合(SET)列則允許在列中存儲一組定義值中的一個或多個值,因此使用時不要混亂。
范式和反范式
范式化數據庫中,每個事實數據會出現且只出現一次。反范式化數據庫中,信息是冗余的,可能存儲在多處。
范式化的優點:
更新操作快
較好的范式化可以減少數據冗余,修改簡單
范式化好的表更新
冗余少的數據可以更少的使用 DISTINCT 或 GROUP BY 語句
范式化設計的 schema 的缺點:
是通常需要關聯查詢。
反范式化的優點:
所有數據都在一張表中,避免了關聯。
最常見的混用范式化和反范式化的例子是復制或緩存,在不同的表中存儲相同的特定列。可以通過觸發器更新冗余列。
緩存表和匯總表
緩存表,表示存儲那些可以比較簡單從schema其他表獲取數據的表
匯總表,保存的是使用GROUP BY語句聚合數據的表
完全獨立的緩存表和匯總表,可以存儲少量冗余數據。
緩存表是實時維護的。
匯總表是定期重建的。
可以使用物化視圖(MySQL 需要借助工具 Flexviews)和計數器表。
加快 ALTER TABLE 操作的速度
MySQL 執行 ALTER TABLE 時,通常是用新的結構創建一個空表,從舊表中查出所有數據插入新表,再刪除舊表。
大部分 ALTER TABLE 操作會中斷 MySQL 服務。有幾種方式可以避免停機:
?
改變或刪除列的默認值時,可以使用 ALTER COLUMN(不一定重建表)或 MODIFY COLUMN(需要重建表)。列的默認值存儲在表的 .frm 文件中。
只修改.frm文件
下面的操作是有可能不需要重建表的:
??移除(不是增加)一個列的AUTO_INCREMENT屬性
? 增加、移除或更改ENUM和SET常量。如果移除的是已經有行數據用到其值的常量,查詢將會返回一個空字串值
基本技術是為想要的表創建一個新的.frm文件,然后用它替換掉已經存在的那張表的.frm文件
快速創建 MyISAM 索引
ALTER TABLE 時,先禁用索引,載入數據后再啟用索引,可以高效載入數據。
這個方法對唯一索引無效,因為DISABLE KEYS只對非唯一索引有效
操作步驟
總結
以上是生活随笔為你收集整理的【MySQL】Schema与数据类型优化的全部內容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: Unity游戏开发中的向量运算-点乘和叉
- 下一篇: 【FFmpeg】tbr tbn tbc