Schema與數(shù)據(jù)類型優(yōu)化

最常用的數(shù)據(jù)類型分類

• 數(shù)字（int、decimal）
• 字符串（varchar、char、text）
• 時間（date、datetime、timestamp）

在PHPmyadmin工具中，還可以看到兩種大類型：spatial（空間類型）、JSON，不過使用很少

如何選擇數(shù)據(jù)類型？

• 簡單的區(qū)分大類型：數(shù)字、字符串、時間等
• 盡量使用可以正確存儲數(shù)據(jù)的最小數(shù)據(jù)類型（比如：數(shù)字范圍小的考慮 tinyint 之類）
• 盡量使用簡單的數(shù)據(jù)類型（整型 > 字符串，內建類型[date, timestamp, datetime] > 字符串）
• 日期。TIMESTAMP占用的存儲空間是DATETIME的一半，但是TIMESTAMP的范圍只能是1970~2038，需要具體分析

整數(shù)類型的范圍

INT(11)中的11表示為整數(shù)類型指定顯示的寬度。所以，對于存儲與計算來說，INT(1)和INT(20)是相同的

實數(shù)（帶有小數(shù)部分的數(shù)字）類型

1、 FLOAT、DOUBLE類型

精度不足，僅支持近似計算

但是由于CPU可以直接支持原生浮點型計算：

• 運算速度更快
• 空間消耗比較小

2、 DECIMAL類型

MySQL 5.0及以上的版本，將數(shù)字打包保存到一個二進制字符串中（每4個字節(jié)存9個數(shù)字），且小數(shù)點占1個字節(jié)。最多允許65個數(shù)字

建議：因為需要額外的空間和計算開銷，所以應該盡量只在對小數(shù)進行精確計算時才使用DECIMAL——例如財務數(shù)據(jù)。在數(shù)據(jù)量比較大時，可以考慮使用BIGINT代替DECIMAL，乘以相應的倍數(shù)得到最終的結果。這樣可以同時避免浮點存儲計算不精確和DECIMAL精確計算代價高的問題

字符串類型

1、 VARCHAR、CHAR類型

最主要的兩種字符串類型，一般存儲的數(shù)據(jù)量較小

VARCHAR的特點：

• 用于存儲可變長的字符串
• 需要1或2個額外的字節(jié)記錄字符串的長度，len <= 255需要1個字節(jié)，否則需要2個
• 可以節(jié)省存儲空間，但是update可能會造成碎片產生

使用VARCHAR的場景：

• 字符串列的最大長度比平均長度大很多
• 列更新較少
• 使用復雜的字符集（如UTF-8），每個字符使用不同的字節(jié)數(shù)進行存儲

另外需要注意的： 雖然VARCHAR(5) 和 VARCHAR(200)來存儲hello字符串，空間開銷是一樣的，但是后者需要分配更多的內存去保存內部值和排序。所以最好的策略是只分配真正需要的空間

InnoDB可以把過長的VARCHAR存儲為BLOB

CHAR的特點：

• 根據(jù)定義的字符串長度分配足夠的空間
• 不需要記錄長度的額外字節(jié)

使用CHAR的場景：

• 存儲很短的字符串，或者所有值都接近同一個長度（如MD5密碼值等）
• 經常變更的數(shù)據(jù)，CHAR優(yōu)于VARCHAR，因為CHAR不容易產生碎片

2、 BLOB、TEXT類型

為存儲很大的數(shù)據(jù)而設計的字符串數(shù)據(jù)類型，BLOB采用二進制，TEXT采用字符方式存儲

特點：

• 當作獨立的對象處理，存儲也會特殊處理（太大的數(shù)據(jù)量可能存儲為指針，然后在外部存儲區(qū)域存儲實際的值）
• 排序只對最前max_sort_length字節(jié)排序，如果只需要排序前面一小部分字符，可以減小max_sort_length的配置，或者使用ORDER BY SUBSTRING(column, length)

mysql> select @@max_sort_length;
+-------------------+
| @@max_sort_length |
+-------------------+
|              1024 |
+-------------------+
1 row in set (0.00 sec)

問題： 如果查詢使用了BLOB、TEXT類型并且需要使用隱式臨時表，將不得不使用MyISAM磁盤臨時表(即EXPLAIN的Extra列包含Using temporary)，造成嚴重的性能開銷！

• 最好的解決方案是盡量避免使用BLOB和TEXT類型
• 如果無法避免則可以在用到這些類型的地方使用SUBSTRING(column, length)，將列值轉換為字符串，這樣就可以使用內存臨時表了。但是必須確保截取的子字符串足夠短，否則如果臨時表大小超過max_heap_table_size 或 tmp_table_size，仍然會轉換為磁盤臨時表

mysql> select @@max_heap_table_size;
+-----------------------+
| @@max_heap_table_size |
+-----------------------+
|              16777216 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select @@tmp_table_size;
+------------------+
| @@tmp_table_size |
+------------------+
|         16777216 |
+------------------+
1 row in set (0.00 sec)

3、 使用枚舉(ENUM)代替字符串類型

相對固定且少量的字符串，可以用枚舉類型代替，在MySQL中存儲為整數(shù)，并用一個“數(shù)字 - 字符串”的映射表保存

mysql> create table enum_test(e ENUM('fish', 'apple', 'dog') NOT NULL);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO enum_test(e) VALUES ('fish'), ('dog'), ('apple');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select e from enum_test;
+-------+
| e     |
+-------+
| fish  |
| dog   |
| apple |
+-------+
3 rows in set (0.00 sec)

mysql> select e+0 from enum_test;
+-----+
| e+0 |
+-----+
|   1 |
|   3 |
|   2 |
+-----+
3 rows in set (0.01 sec)

mysql> desc enum_test;
+-------+----------------------------+------+-----+---------+-------+
| Field | Type                       | Null | Key | Default | Extra |
+-------+----------------------------+------+-----+---------+-------+
| e     | enum('fish','apple','dog') | NO   |     | NULL    |       |
+-------+----------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

有些問題需要注意：

• 枚舉類型在ORDER的時候，是根據(jù)ENUM的順序排序，如果需要對字符串特定排序，可以使用FIELD函數(shù)顯式指定。 ORDER BY FIELD(e, 'apple', 'dog', 'fish')
• 需要增加或刪除字符串的時候，除非只在列表末尾添加元素，否則需要重建整個表
• 在關聯(lián)（JOIN）多表的情況下，ENUM之間關聯(lián)，速度比VARCHAR之間關聯(lián)會快很多，所以在優(yōu)化中很值得使用。另外有時候，VARCHAR與ENUM之間關聯(lián)，會比VARCHAR之間關聯(lián)更慢，一定要注意保證ENUM與ENUM關聯(lián)

4、 日期和時間類型

MySQL能存儲的最小時間粒度為秒，最常用的類型：DATETIME、TIMESTAMP

DATETIME

從1001年到9999年，把日期封裝到格式為YYYYMMDDHHMMSS的整數(shù)中，與時區(qū)無關，使用8個字節(jié)存儲

TIMESTAMP（無特殊要求的情況下，建議使用）

從1970年到2038年，對應UNIX時間戳，依賴于時區(qū)，只需要4個字節(jié)存儲

存儲比秒更小粒度的時間：

• BIGINT
• DOUBLE
• 使用MariaDB替代MySQL

范式與反范式

在范式化的數(shù)據(jù)庫中，每個事實數(shù)據(jù)會出現(xiàn)且只出現(xiàn)一次。相反，在反范式化的數(shù)據(jù)庫中，信息是冗余的，可能會存儲在多個地方

范式的優(yōu)缺點：

• 范式化更新操作通常比反范式化要快

• 當數(shù)據(jù)較好地規(guī)范化時，就只有很少或者沒有重復數(shù)據(jù)，所以只需要修改更少的數(shù)據(jù)

• 范式化的表通常更小，可以更好地放到內存中，執(zhí)行操作會更快

• 檢索時更少需要DISTINCT或者GROUP BY語句

• 通常需要關聯(lián)一次或多次，代價昂貴且使一些索引策略失效

反范式的優(yōu)缺點：

• 如果不需要關聯(lián)，即使表沒有使用索引，最差的情況也只是全表掃描，避免了隨機I/O
• 單獨的表可以使用更有效的索引策略

緩存表和匯總表

有時提高性能最好的方法是在同一張表中保存衍生的冗余數(shù)據(jù)，有時也需要創(chuàng)建一張完全獨立的匯總表或緩存表

緩存表示例： 可以把復雜查詢的結果放在一個索引合理的表中，便于多次查詢

匯總表示例： 假設需要計算之前24小時內發(fā)送的消息數(shù)，可以每小時生成一張匯總表，或者在匯總表的基礎上，把之前23個完整的小時的統(tǒng)計表中的計數(shù)全部加起來，最后加上當前小時內的計數(shù)即可

在重建緩存表和匯總表的時候，通常需要保證數(shù)據(jù)在操作時依然可用，需要通過“影子表”來實現(xiàn)

DROP TABLE IF EXISTS my_s_new, my_s_old;
CREATE TABLE my_s_new LIKE my_s;
-- 按照需要去填充my_s_new
RENAME TABLE my_s TO my_s_old, my_s_new TO my_s;

計數(shù)器表的例子

如果應用在表中保存計數(shù)器，則在更新計數(shù)器時可能碰到并發(fā)問題，出現(xiàn)一個全局的互斥鎖（mutex），這會使得這些事務只能串行執(zhí)行

可以預先在一張表中增加100行數(shù)據(jù)，隨機選擇一個槽（slot）進行更新，使用聚合查詢來統(tǒng)計總數(shù)

UPDATE hit_counter SET cnt = cnt + 1 WHERE slot = RAND() * 100;
SELECT SUM(cnt) FROM hit_counter;

可以用ON DUPLICATE KEY UPDATE來代替預先生成行，統(tǒng)計每日的數(shù)據(jù)

INSERT INTO daily_hit_counter(day, slot, cnt) VALUES (CURRENT_DATE, RAND() * 100, 1) ON DUPLICATE KEY UPDATE cnt = cnt + 1;

上面的方法都是“更快地讀，更慢地寫”，通過建立一些額外索引、增加冗余列、創(chuàng)建緩存表和匯總表，雖然增加寫查詢的負擔，但是會提升讀查詢的速度