良好的邏輯設計和物理設計是高性能的基石，前期的表結(jié)構設計質(zhì)量，會直接影響基于表結(jié)構的業(yè)務SQL性能，所以，表結(jié)構的設計質(zhì)量，直接關系到系統(tǒng)的運行情況，甚至是系統(tǒng)的用戶體驗，本章，主要對MySQL支持的數(shù)據(jù)進行講解，說明數(shù)據(jù)類型的優(yōu)缺點及適用場景

一、選擇數(shù)據(jù)類型的基本原則

1. 更小的通常最好

一般情況下，應該盡量選擇可以正確存儲數(shù)據(jù)的最小數(shù)據(jù)類型，因為它們占用的磁盤、內(nèi)存和CPU緩存更少，并且調(diào)用CPU周期也更短，但是如果由于前期選擇的數(shù)據(jù)類型支持的范圍不足以支持業(yè)務數(shù)據(jù)，需要修改數(shù)據(jù)類型以增加數(shù)據(jù)范圍，這將需要一個非常耗時和痛苦的操作，所有在設計數(shù)據(jù)類型時，無法確認哪個數(shù)據(jù)類型是最好的，就選擇你覺得不會超過范圍的最小類型。

2. 簡單就好

簡單的數(shù)據(jù)類型的操作通常需要更少的CPU周期，例如整形比字符串代價更低、使用內(nèi)建類型（datetime）而不是字符串來保持時間

3. 盡量避免NULL

null是MySQL數(shù)據(jù)列的默認屬性，但是通常情況下，最好知道列為NOT NULL，除非系統(tǒng)真的需要保持NULL值，查詢中包含NULL的列，對MySQL來說，更難優(yōu)化，因為NULL會使得所有、索引統(tǒng)計更為復雜

二、數(shù)據(jù)類型

在為列選擇數(shù)據(jù)類型時，第一步需要確定合適的大致類型，下一步就是選擇具體的類型，有些數(shù)據(jù)，有幾種數(shù)據(jù)類型都支持存儲，只是它們的存儲范圍、精度不一樣，這就需要我們根據(jù)業(yè)務來選擇更加合適的類型，下面將對MySQL支持的數(shù)據(jù)類型進行介紹

1.整數(shù)類型

數(shù)據(jù)類型：
? 1.TINYINT 8位
? 2.SMALLINT16位
? 3.MEDIUMINT24位
? 4.INT32位
? 5.BIGINT64位

特性：

1. UNSIGNED屬性，表示不允許有負值，設置UNSIGNED會是整數(shù)的上限提高一倍，除此以外，相比于不設置，沒有其他任何區(qū)別，包括存儲空間和性能

tip：整數(shù)可以指定寬度，但是幷不能限制數(shù)據(jù)的范圍，它只是規(guī)定了在交互工具上顯式的字符個數(shù)，對于存儲來說，INT(1)和INT（20）沒有區(qū)別

2.實數(shù)整形

數(shù)據(jù)類型：
? 1.FLOAT 和DOUBLE 支持使用標準的浮點運算進行近似計算
? 2. DECIMAL用于存儲精確的小數(shù)。

特性：
? 1. FLOAT 和DOUBLE 在存儲相同范圍的值時，比DECIMAL使用空間更少
? 2. DOUBLE（8字節(jié)）比FLOAT（4字節(jié)）有更高的精度和更大的范圍
? 3. DECIMAL支持對小數(shù)進行精確計算，但是需要額外的空間和計算開銷，因為CPU不支持對DECIMAL的直接計算，需要MySQL內(nèi)部自身的實現(xiàn)

tip：可以通過對要存儲的高精度小數(shù)據(jù)數(shù)據(jù)乘以相應的倍數(shù)后用bigint來進行存儲，因為bigint開銷更，當然，只有在數(shù)據(jù)量比較大的時候才需要考慮這樣做。

3.字符串類型

數(shù)據(jù)類型：
? 1.CHAR 用于存儲固定長度的字符串類型
? 2. VARCHAR 用于存儲不定長的字符串數(shù)據(jù)類型
? 3. TEXT用于存儲很大的數(shù)據(jù)而設計的字符串類型
? 4.BINARY和CHAR 對應的二進制字符串
? 5. VABINARY 和VARCHAR 對應的二進制字符串
? 6.BLOB和TEXT對應的二進制字符串

特性：

1. VARCHAR 比CHAR 更節(jié)省空間，因為它只使用必要的空間
2. VARCHAR 由于變長，在UODATE時可能使行變得比原來長，可能會導致分裂頁的操作
3. CHAR總是根據(jù)定義的長度分配對應的空間，不論數(shù)據(jù)實際長度是否比定義的短
4. CHAR會刪除所有的末尾空格而最近版本的VARCHAR 不會
5. 定長的CHAR更不容易產(chǎn)生碎片
6. BINARY和VARCHAR 存儲的是二進制，比較時，每次按一個字節(jié)進行比較，對大小寫敏感，且更快
7. BLOB和TEXT在存儲大值時，會使用專門的存儲空間進行存儲，而行數(shù)據(jù)存儲的是指向?qū)嶋H存儲空間的地址
8. BLOB和TEXT只對列最前面的max_sort_length字節(jié)進行排序

tip：1. 當使用VARCHAR 時，如果表使用ROW_FORMAT = FIXED,每一行都會使用定長存儲，這回很浪費空間
9. VARCHAR (5)和VARCHAR （200）存儲hello使用的空間開銷是一樣的，但是VARCHAR （200）會消耗更多的內(nèi)存，因為MySQL會分配固定大小的內(nèi)存來保持內(nèi)部值，尤其是使用內(nèi)存臨時表進行操作時會很糟糕，所有盡量只分配真正需要的空間

4.日期和時間類型

數(shù)據(jù)類型：
? 1.DATETIME 用于存儲1001年到9999年的時間，精度為秒
? 2. TIMESTAMP用于存儲1970年1月1日午夜以來的秒數(shù)

特性：

1. TIMESTAMP與時區(qū)有關，它的顯式會依賴時區(qū)設置，而DATETIME 不會
2. TIMESTAMP默認為NOT NULL
3. TIMESTAMP在更新或者插入時，如果未指定時間，會自動設置為當前時間
4. TIMESTAMP比DATETIME 的空間效率更高
   tip：TIMESTAMP最遠只能表示到2038年的時間

5.位數(shù)據(jù)類型

數(shù)據(jù)類型：
? 1.BIT用于在一個列存儲多個true/false值
? 2. SET用于合并存儲很多的true\false

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

IPv4地址，可以使用無符號整數(shù)存儲IP地址，MySQL提供inet_ATON()和INET_NTOA()來轉(zhuǎn)化ip地址

三、MySQL schema設計中的陷阱

1.太多的列

MySQL的存儲引擎API在工作時，需要在服務器層和存儲引擎層之間進行緩沖格式拷貝數(shù)據(jù)，然后在服務器層將緩存內(nèi)容解碼成各個列，而轉(zhuǎn)換的代價依賴列的數(shù)量。

2.太多的關聯(lián)

大多的關聯(lián)，會導致解析和優(yōu)化查詢的代價成為問題，單個查詢，最好在12個表以內(nèi)做關聯(lián)

3.全能的枚舉

使用枚舉，當需要在枚舉列表中增加一個新的值時，需要做一次ALTER TABLE操作，這是全表的阻塞操作，操作起來會很麻煩

4.非此發(fā)明的NULL

避免使用NULL會帶來很多好處，而且即使需要存儲null值，也可以考慮使用其他的特殊值來代替，但是也不能走極端，當使用其他替代方案會導致不確定性，或者bug時，也許null幷不是不能接受的，而且MySQL會在索引中存儲null的值，只是處理起來相比于not null 更復雜一點

三、范式和反范式

1.范式的優(yōu)點和缺點

• 范式化的更新操作通常比反范式化要快
• 當數(shù)據(jù)更好的范式化時，就只有很少或者沒有重復的數(shù)據(jù)，所以只需要修改很少的數(shù)據(jù)
• 范式化的表通常更小，可以更好的放到內(nèi)存里，所以操作會更快
• 檢索列表時，更少的需要使用DISTINCT或者GROUP BY語句

范式化設計的schema通常的缺點是需要關聯(lián)，稍微復雜一點的語句在符合范式的schema上都可能需要至少一次關聯(lián)

2.反范式的優(yōu)點和缺點

• 避免過多的關聯(lián)
• 更有效的索引策略

范式化和反范式化各有優(yōu)缺點，在正常的情況下，我們應綜合業(yè)務需要，混合使用二者，所謂，黑貓白貓，能抓老鼠的才是好貓。

四、緩存表和匯總表

有時，提升性能最好的辦法是在同一張表中保存衍生的冗余數(shù)據(jù)，然而有時候也需要創(chuàng)建一張完全獨立的匯總表和緩存表，匯總表，指的是對某些數(shù)據(jù)進行聚合而產(chǎn)生具有統(tǒng)計功能的表，緩存表用來存儲那些可以比較簡單獲取但獲取速度比較慢的數(shù)據(jù)。

匯總表
使用匯總表，相比于實時進行匯總計算，高效了很多，因為實時計算總是需要掃描大部分的數(shù)據(jù)，或者索引要求很高，但是有一個問題就是匯總表總是在使用前就多某一段的數(shù)據(jù)進行了匯總，導致實時性不高，但有時候也有一個折中的辦法，就是一段時間的數(shù)據(jù)使用匯總表，而當前一小段的數(shù)據(jù)使用實時數(shù)據(jù)進行計算。

緩存表
有時，我們需要很多不同的的索引組合來加速各種類型的查詢，這些矛盾的需求有時需要創(chuàng)建一張只包含主表中部分列的緩存表

1.物化視圖

許多數(shù)據(jù)庫管理系統(tǒng)都提供了一個叫物化視圖的功能，物化視圖實際上是預先計算并且存儲在磁盤上的表，可以通過各種各樣的策略刷新和更新，MySQL并不支持物化視圖，然而我們可以使用Jsitin swanhart的開源工具Flexviews，對比傳統(tǒng)的維護匯總表和緩存表的方法，F(xiàn)lexviews通過提取對源表的更改，可以增量的重新計算物化視圖的內(nèi)容。

2.計數(shù)器表

計數(shù)器表最大的問題就是會遇到更新并發(fā)的問題，當多個線程對計數(shù)字段進行更新時，因為互斥的關系，這些更新需要串行化的執(zhí)行，這會嚴重影響性能，這里有個和好的技巧可供參開，而且這個思路也可以在其他地方獲益

比如，你要記錄一個網(wǎng)站的點擊次數(shù)，你可能會建一張表

create table hit_count(cnt int unsigned not null) engine = innoDB;

每點擊一次，就執(zhí)行如下sql:

update hit_count set cnt -= cnt 1;

但是當許多人點擊時，這些事務只能串行的執(zhí)行，這時，可以考慮如下修改:

create table hit_count(
	slot tinyint unsigned not null primary key,
	cnt int unsigned not null
) engine = innoDB;

這時，在進行更新時，就可以隨機對一行數(shù)據(jù)進行更新

update hit_count set cnt  = cnt 1 where slot = RAND()*100;

當要獲得統(tǒng)計結(jié)果時，需要使用下面這樣的聚合查詢

select sum(cnt) from hit_counter;

更進一步，每隔一段時間開始一個新的計數(shù)器，可以做如下修改：

create table daily_hit_counter(
	day date not null,
	slot tinyint unsigned not null,
	cnt int unsigned not null,
	primary key(day, slot)
)engine=InnoDB;

更新則可使用如下語句

insert into daily_hit_counter(dat, slot, cnt)
	values (current_date, rand()*100,1)
	on duplicate key update cnt = cnt 1;

五、加快ALTER TABLE 操作的速度

MySQL的ALTER TABLE操作的性能對大表來說是個大問題，MySQL執(zhí)行大部分修改表結(jié)構操作的方法是用新的結(jié)構創(chuàng)建一個空表，從舊表中查出素有數(shù)據(jù)插入新表，然后刪除舊表，這樣的操作可能需要花費很長的時間，如果內(nèi)存不足而表又很大，而且需要很多索引的情況下尤其如此。

對于常見的場景，能使用的技巧有兩種：
一、先在一臺不提供服務的機器上執(zhí)行ALTER TABLE 操作，然后和提供服務的主庫進行切換。
二、使用影子拷貝，創(chuàng)建一張和源表結(jié)構一樣的新表，然后通過重命名的和刪表操作交換兩張表

理論上，MYSQL可以跳過創(chuàng)建新表的步驟，列的默認值實際上存在表的.frm中，可以直接修改這個文件而不需要改動表本身，然而mysql還沒有采取這種優(yōu)化的方法，所以ALTER TABLE 操作都將導致表重建
另外一種方法是通過ALTER COLUMN操作來改變列的默認值：

ALTER TABLE sakila.film
ALTER COLUMN rental_duration SET DEFAULT 5

這個語句會直接修改.frm而不涉及表數(shù)據(jù)

tip：ALTER TABLE 允許使用ALTER COLUMN 、MODIFY COLUMN 、CHANGE COLUMN 語句修改列

只修改.frm文件

有時候mysql會在沒有必要的時候重建表，如果愿意冒一點風險，可以讓mysql做一些其他類型的修改而不用重建表。

• 移除一個列的AUTO_INCREMENT屬性

1. 創(chuàng)建一張相同結(jié)構的空表，幷進行所需要的的修改
2. 執(zhí)行FLUSH TABLE WITH READ LOCK,這將會關閉所有正在使用的表，并且禁止任何表被打開
3. 交換.frm文件
4. 4.執(zhí)行UNLOCK TABLES 釋放鎖。

快速創(chuàng)建索引

1. 用需要的表結(jié)構創(chuàng)建一張表，但不包括索引
2. 載入數(shù)據(jù)到表中以創(chuàng)建.MYD
3. 按照需要的結(jié)構創(chuàng)建另一張空表，這次要包含索引，這會創(chuàng)建需要的.frm和.MYI文件
4. 獲取讀鎖幷刷新表
5. 重命名第二張表的.frm和MYI,讓MySQL認為是第一張表的文件。
6. 釋放鎖
7. 使用REPARE TABLE來重新建表的索引，該操作會通過排序來構件所有的索引，包括唯一索引

總結(jié)

良好的schema設計原則是普遍適用的，但是MySQL有他自己的實現(xiàn)細節(jié)要注意，概況來講，盡可能保持任何東西小而簡單總是好的。有以下簡單的原則值得你去考慮使用

• 盡量避免過度設計
• 使用小而簡單的合適數(shù)據(jù)類型，盡可能避免使用null
• 盡量使用相同的數(shù)據(jù)類型存儲相似或者相關的值
• 注意可變長字符串，其在臨時表和排序時可能按最大長度分配內(nèi)存
• 盡量使用整形定義標識符