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

首先我們介紹一下這個(gè)schema：

schema（發(fā)音 “skee-muh” 或者“skee-mah”，中文叫模式）是數(shù)據(jù)庫的組織和結(jié)構(gòu)

選擇優(yōu)化的數(shù)據(jù)類型

mysql支持的數(shù)據(jù)類型非常多，選擇正確的數(shù)據(jù)類型對(duì)于獲得高性能至關(guān)重要。不管存儲(chǔ)那種類型的數(shù)據(jù)，下面幾個(gè)簡單的原則你需要記住。

• 更小的通常更好
• 簡單就好
• 盡量避免null

整數(shù)類型

我們有以下幾種整數(shù)類型：

他們的存儲(chǔ)空間范圍從-2（N-1）次方到2的（N-1）次方減一。N是位數(shù)

整數(shù)類型有可選的unsigned屬性，表示不允許負(fù)值，這大致可以使正數(shù)的上限提高一倍。

實(shí)數(shù)類型

實(shí)數(shù)嘛，就是帶有小數(shù)部分的數(shù)字，然而，它不只是為了存儲(chǔ)小數(shù)部分；也可以使用decimal存儲(chǔ)比bigint還大的整數(shù)。

decimal（18,9）小數(shù)點(diǎn)兩邊各存儲(chǔ)9個(gè)數(shù)字，一個(gè)使用9個(gè)字節(jié)；小數(shù)點(diǎn)前的數(shù)字使用4個(gè)字節(jié)，小數(shù)點(diǎn)后的數(shù)字使用4個(gè)字節(jié)，小數(shù)點(diǎn)本身占一個(gè)字節(jié)。

因?yàn)樾枰~外的空間和計(jì)算開銷，所以應(yīng)該盡量只在對(duì)小數(shù)進(jìn)行精確計(jì)算時(shí)才使用decimal--例如存儲(chǔ)財(cái)務(wù)數(shù)據(jù)，但是在數(shù)據(jù)量比較大的時(shí)候們可以使用bigint代替decimal，將存儲(chǔ)的數(shù)據(jù)根據(jù)小數(shù)的位數(shù)乘以相應(yīng)的倍數(shù)即可。

字符串類型

varchar

用于存儲(chǔ)可變長的字符串，如果MySQL使用 ROW_FORMAT=FIXED創(chuàng)建的話，每一行都會(huì)使用定長存儲(chǔ)，這會(huì)很浪費(fèi)空間。

varchar需要使用1個(gè)或者2個(gè)額外的字節(jié)記錄字符串的長度。例如：varchar（10）的列需要11個(gè)字節(jié)的存儲(chǔ)空間。

但是由于行是變長的在update的時(shí)候可能使行變得比原來更長，這就導(dǎo)致需要額外的工作。例如myisam會(huì)將行拆成不同的片段存儲(chǔ)，innodb則需要分裂頁來使行可以放進(jìn)頁內(nèi)。

char

char類型是定長的，當(dāng)存儲(chǔ)char值時(shí)。mysql會(huì)刪除所有的末尾空格。

char適合存儲(chǔ)很短的字符串，或者所有值都接近同一個(gè)長度。例：char十分適合存儲(chǔ)密碼的md5值。

對(duì)于經(jīng)常變更的數(shù)據(jù)，char也比varchar好，因?yàn)槎ㄩL的char類型不容易產(chǎn)生碎片。

BLOB和TEXT

都是為了存儲(chǔ)很大的數(shù)據(jù)而設(shè)計(jì)的字符串?dāng)?shù)據(jù)類型，分別采用二進(jìn)制和字符方式存儲(chǔ)。

MySQL把每個(gè)blob和text值當(dāng)做一個(gè)獨(dú)立的對(duì)象處理。存儲(chǔ)引擎在存儲(chǔ)時(shí)通常會(huì)做特殊處理。當(dāng)blob和text值太大時(shí)，innodb會(huì)使用專門的外部存儲(chǔ)區(qū)域進(jìn)行存儲(chǔ)，此時(shí)每個(gè)值在行內(nèi)需要1--4個(gè)字節(jié)來存儲(chǔ)一個(gè)指針，然后在外部的存儲(chǔ)區(qū)域存儲(chǔ)實(shí)際的值。

BLOB和TEXT家族之間僅有的不同是BLOB類型存儲(chǔ)的是二進(jìn)制數(shù)據(jù)，沒有排序規(guī)則或字符集，而text類型有字符集和排序規(guī)則。

使用枚舉enum代替字符串類型

有時(shí)候可以使用枚舉列來代替常用的字符串類型。枚舉列可以把一些不重復(fù)的字符串存儲(chǔ)成一個(gè)預(yù)定義的集合。mysql在存儲(chǔ)枚舉時(shí)非常緊湊，會(huì)根據(jù)列表值的數(shù)量壓縮到一個(gè)或者兩個(gè)字節(jié)中。mysql內(nèi)部會(huì)將每個(gè)值在列表中的位置保存為整數(shù)，并且在表的.frm文件中保存“數(shù)字-字符串‘映射關(guān)系的查找表。

我們看看下面的例子：

create TABLE  enum_test(
        e ENUM('fish','apple','dog') NOT NULL
);
?
INSERT INTO enum_test(e) VALUES ('fish'),('dog'),('apple');

SELECT e + 0 FROM enum_test;

SELECT e FROM enum_test;

所以使用數(shù)字作為enum枚舉常量，這種雙重性很容易導(dǎo)致混亂，例如enum（’1‘，’2‘，’3‘）。所以盡量別這么用。

另外一個(gè)讓人大吃一驚的事情是：

枚舉字段是按照內(nèi)部存儲(chǔ)的整數(shù)而不是定義的字符串進(jìn)行排序的。

枚舉最不好的地方是，字符串列表是固定的，添加或刪除字符串必須使用ALTER TABLE 。因此對(duì)于一系列未來可能改變的字符串，枚舉并不是一個(gè)好主意。

日期和時(shí)間類型

DATETIME

這個(gè)類型可以保存大范圍的值，從1001年到9999年，精度為秒，他把日期和時(shí)間封裝到格式為YYYYMMDDHHMMSS的整數(shù)中，與時(shí)區(qū)無關(guān)。使用8個(gè)字節(jié)的存儲(chǔ)空間。

TIMRSTAMP

就像它的名字一樣TIMESTAMP類型保存了從1970年1月1日午夜（格林尼治標(biāo)準(zhǔn)時(shí)間）以來的秒數(shù)。它和unix時(shí)間戳相同。timestamp值使用4個(gè)字節(jié)的存儲(chǔ)空間，因此他的范圍比datetime 小的多。只能表示從1970到2038年MySQL提供了FROM_UNIXTIME（）函數(shù)把Unix時(shí)間戳轉(zhuǎn)換為日期，并且提供Unix_TIMESTAMP()函數(shù)把日期轉(zhuǎn)換為Unix時(shí)間戳。

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

BIT

mysql把bit當(dāng)做字符串類型，而不是數(shù)字類型，當(dāng)檢索bit（1）的值時(shí)，結(jié)果是一個(gè)包含二進(jìn)制0或1 的字符串，而不是ascii碼的0,1。

SET

如果需要保存很多true/false值，可以考慮合并這些列到一個(gè)set數(shù)據(jù)類型，他在mysql內(nèi)部是以一系列打包的位的集合來表示的。這樣可以有效的利用空間，并且MySQL有像FIND_IN_SET()和FIELD（）這樣的函數(shù)，方便地在查詢中使用。

它的主要缺點(diǎn)是改變列的定義的代價(jià)較高：需要alter TABLE，這對(duì)大表來說是非常昂貴的操作。

MySQL schema設(shè)計(jì)中的缺陷

??太多的列

MySQL的存儲(chǔ)引擎API工作時(shí)需要在服務(wù)器層和存儲(chǔ)引擎層之間通過行緩沖格式拷貝數(shù)據(jù)，然后在服務(wù)器層將緩沖內(nèi)容解碼成各個(gè)列。從行緩沖中將編碼過的列轉(zhuǎn)換成行數(shù)據(jù)結(jié)構(gòu)的代價(jià)是十分大的。而轉(zhuǎn)換的代價(jià)依賴與列的數(shù)量。當(dāng)我們研究一個(gè)CPU占用非常高的案例時(shí)，發(fā)現(xiàn)客戶使用了非常寬的表，然而只有一小部分的列會(huì)實(shí)際用到，這時(shí)候轉(zhuǎn)換的代價(jià)就非常高了。

MySQL限制了每個(gè)關(guān)聯(lián)操作最多只能有61個(gè)表，一個(gè)粗略的經(jīng)驗(yàn)，如果希望查詢執(zhí)行的快速且并發(fā)性好，單個(gè)查詢最好在12個(gè)表以內(nèi)做關(guān)聯(lián)。

??全能的枚舉

注意放置過度使用枚舉

你別一個(gè)枚舉，舉了個(gè)數(shù)字全集出來，那就不禮貌了。

??變相的枚舉

枚舉列允許在列中存儲(chǔ)一組定義值中的單個(gè)值，集合set列則允許在列中存儲(chǔ)一組定義值中的一個(gè)或多個(gè)值。

比如：

create TABLE 。。。 （
is_default set('Y','N') NOT NULL default 'N'
）

這里我們需要注意到這個(gè)真假的情況是不會(huì)同時(shí)出現(xiàn)的，那么我們就應(yīng)該毫無疑問的使用枚舉而不是這個(gè)set。

??非此發(fā)明的null

我們之前寫了避免使用null的好處，并且建議盡可能的考慮替代方案。比如我們可以用0，或者一些特殊字符去代替null。

但是遵循這一原則也不要走極端。當(dāng)確實(shí)需要表示未知值時(shí)也不要害怕使用null。

范式和反范式

范式：

范式是符合某一種級(jí)別的關(guān)系模式的集合。關(guān)系數(shù)據(jù)庫中的關(guān)系必須滿足一定的要求，滿足不同程度要求的為不同范式。

第一范式（1NF）

在任何一個(gè)關(guān)系數(shù)據(jù)庫中，第一范式（1NF） [2] 是對(duì)關(guān)系模式的基本要求，不滿足第一范式（1NF）的數(shù)據(jù)庫就不是關(guān)系數(shù)據(jù)庫。

第二范式（2NF）

是在第一范式（1NF）的基礎(chǔ)上建立起來的，即滿足第二范式（2NF）必須先滿足第一范式（1NF）。第二范式（2NF）要求數(shù)據(jù)庫表中的每個(gè)[實(shí)例]或行必須可以被唯一地區(qū)分。為實(shí)現(xiàn)區(qū)分通常需要為表加上一個(gè)列，以存儲(chǔ)各個(gè)實(shí)例的唯一標(biāo)識(shí)。這個(gè)唯一屬性列被稱為[主關(guān)鍵字]或主鍵、主碼。

范式的優(yōu)點(diǎn)和缺點(diǎn)

優(yōu)點(diǎn)：

• 范式化的更新操作比反范式化的更新要快
• 當(dāng)數(shù)據(jù)較好的范式化，就只有很少或者較少的重復(fù)數(shù)據(jù)，所以只需要修改更是少的數(shù)據(jù)。
• 范式化的表通常更小，可以更好的放在內(nèi)存里，所以執(zhí)行的操作會(huì)更快。
• 很少的重復(fù)數(shù)據(jù)也就意味著在select時(shí)我們會(huì)更少的使用distinct或者group by 語句。

缺點(diǎn)：

• 需要關(guān)聯(lián)

反范式化的優(yōu)點(diǎn)和缺點(diǎn)

反范式化的schema因?yàn)樗械臄?shù)據(jù)都在一張表中，所以很好的避免了關(guān)聯(lián)。

混用范式化和反范式化

最常見的反范式化數(shù)據(jù)的方法就是復(fù)制或者緩存，在不同的表里存儲(chǔ)相同的特定列。我們還可以使用觸發(fā)器更新緩存值，這使得實(shí)現(xiàn)這樣的方案變得更簡單。

緩存表和匯總表

有時(shí)候提升性能的最好方法是在同一張表中保存衍生的冗余數(shù)據(jù)。然而，有時(shí)也需要?jiǎng)?chuàng)建一張完全獨(dú)立的匯總表或緩存表。

我們用術(shù)語緩存表來表示存儲(chǔ)那些可以比較簡單的從schema其他表獲得的數(shù)據(jù)的表。而術(shù)語匯總表，則保存的是使用group by 語句聚合數(shù)據(jù)的表。

我們使用匯總表，要遠(yuǎn)比我們掃描表的全部行要有效的多。

緩存表則相反，其對(duì)優(yōu)化搜索和檢索查詢語句很有效。這些查詢語句經(jīng)常需要特殊的表和索引結(jié)構(gòu)。例如：可能會(huì)需要很多不同的索引組合來加速各種類型的查詢。這些矛盾的需求有時(shí)候要?jiǎng)?chuàng)建一張只包含主表中部分列的緩存表。一個(gè)有用的技巧是我嗎可以使用不同的存儲(chǔ)引擎。比如說，主表使用innodb，我嗎可以把myisam作為緩存表的引擎，這樣會(huì)得到更小的索引占用空間，并且可以做全文搜索。

在使用緩存表和匯總表的時(shí)候，我嗎必須決定到底是實(shí)時(shí)維護(hù)數(shù)據(jù)還是定期重建。那個(gè)更好依賴于應(yīng)用程序，但是定期重建并不只是節(jié)省資源，也可以保持表不會(huì)有那么多的碎片，以及有完全順序組織的索引。

當(dāng)然為了安全 ，我們還會(huì)在重建這些表的時(shí)候使用一個(gè)影子表，來保證數(shù)據(jù)在操作過程也是可以使用的。

物化視圖

計(jì)數(shù)器表

計(jì)數(shù)器表是一個(gè)經(jīng)常會(huì)用到的東西，我們使用單獨(dú)的表可以幫助避免查詢緩存失效。

下面我們要展示呢一些更高級(jí)的技巧：

你比如說，我們有一個(gè)計(jì)數(shù)器表，是記錄這個(gè)網(wǎng)站的點(diǎn)擊次數(shù)的這樣一個(gè)表，但我們每次修改的時(shí)候都會(huì)有一個(gè)全局的互斥鎖，這也就導(dǎo)致了這些事務(wù)只能串行執(zhí)行。我們要是想獲得更好的性能，就可以將計(jì)數(shù)器保存在多行，每次隨機(jī)選擇一行進(jìn)行更新。

我們對(duì)這個(gè)計(jì)數(shù)表這樣更新：

CREATE TABLE hit_counter(
slot tinyint unsigned not null primary key ,
cnt int unsigned not null
)ENGINE = InnoDB

我們預(yù)先在表中增加100行數(shù)據(jù)，選擇一個(gè)隨機(jī)的槽進(jìn)行更新：

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

要統(tǒng)計(jì)結(jié)果，我們就使用下面這樣的聚合查詢：

SELECT SUM(cnt) FROM hit_counter; 

??我們一個(gè)常見的需求是每隔一段時(shí)間開始一個(gè)新的計(jì)數(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;

這樣的話我們就不要去預(yù)先生成行，而用on duplicate key update語句（存在就更新，不存在那就插入）

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

如果希望減少表的行數(shù)，避免表變得太大，可以寫一個(gè)周期執(zhí)行的任務(wù)，合并所有結(jié)果到0號(hào)槽，并且刪除所有其他的槽：

UPDATE daily_hit_counter as c
	INNER JOIN (
	SELECT day,SUM(cnt)AS cnt,MIN(slot)AS mslot
	FROM daily_hit_counter
	GROUP BY day
	)AS x USING(day)
SET  c.cnt = IF(c.slot = x.mslot,x.slot,0),
	c.slot = IF (c.slot = x.mslot,0,c.slot);

DELETE FROM daily_hit_counter WHERE slot <>0 AND cnt = 0;

加快alter TABLE操作的速度

MySQL對(duì)于大表的alter TABLE一直是一個(gè)大問題。mysql執(zhí)行大部分的修改表的結(jié)構(gòu)操作的方法是用新的結(jié)構(gòu)創(chuàng)建一個(gè)空表，然后把舊表里的數(shù)據(jù)插入到新表。

對(duì)于常見的場(chǎng)景，能使用的場(chǎng)景只有兩種：

• 先在一臺(tái)不提供服務(wù)的機(jī)器上執(zhí)行ALTER TABLE 操作，然后和提供服務(wù)的主庫進(jìn)行切換
• 影子拷貝：用要求的表結(jié)構(gòu)創(chuàng)建一張和源表無關(guān)的新表，然后通過重命名和刪表操作交換兩張表。

不是所有的alter TABLE操作都會(huì)引起表重建。例如，有兩個(gè)方法可以改變或者刪除一個(gè)列的默認(rèn)值（一種方法很快，一種很慢）。

慢的方式：

ALTER TABLE sakila.film 
MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5;

這種方式是比較慢的，因?yàn)閙odify這種方式是要導(dǎo)致表的重建的。

ALTER TABLE sakila.film 
ALTER  COLUMN rental_duration  SET DEFAULT 5;

這種alter的方式就很快，因?yàn)樗侵苯有薷?firm文件而不涉及表數(shù)據(jù)。所以這個(gè)操作是特別快的。

到此這篇關(guān)于設(shè)計(jì)性能更優(yōu)MySQL數(shù)據(jù)庫schema的文章就介紹到這了,更多相關(guān)MySQL schema內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論