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

首先我們介紹一下這個schema：

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

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

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

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

整數(shù)類型

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

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

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

實數(shù)類型

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

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

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

字符串類型

varchar

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

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

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

char

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

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

對于經(jīng)常變更的數(shù)據(jù)，char也比varchar好，因為定長的char類型不容易產(chǎn)生碎片。

BLOB和TEXT

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

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

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

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

有時候可以使用枚舉列來代替常用的字符串類型。枚舉列可以把一些不重復(fù)的字符串存儲成一個預(yù)定義的集合。mysql在存儲枚舉時非常緊湊，會根據(jù)列表值的數(shù)量壓縮到一個或者兩個字節(jié)中。mysql內(nèi)部會將每個值在列表中的位置保存為整數(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‘）。所以盡量別這么用。

另外一個讓人大吃一驚的事情是：

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

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

日期和時間類型

DATETIME

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

TIMRSTAMP

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

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

BIT

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

SET

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

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

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

??太多的列

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

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

??全能的枚舉

注意放置過度使用枚舉

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

??變相的枚舉

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

比如：

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

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

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

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

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

范式和反范式

范式：

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

第一范式（1NF）

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

第二范式（2NF）

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

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

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

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

缺點(diǎn)：

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

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

反范式化的schema因為所有的數(shù)據(jù)都在一張表中，所以很好的避免了關(guān)聯(lián)。

混用范式化和反范式化

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

緩存表和匯總表

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

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

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

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

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

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

物化視圖

計數(shù)器表

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

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

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

我們對這個計數(shù)表這樣更新：

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

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

UPDATE hit_counter 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;

這樣的話我們就不要去預(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ù)，避免表變得太大，可以寫一個周期執(zhí)行的任務(wù)，合并所有結(jié)果到0號槽，并且刪除所有其他的槽：

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

對于常見的場景，能使用的場景只有兩種：

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

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

慢的方式：

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

這種方式是比較慢的，因為modify這種方式是要導(dǎo)致表的重建的。

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

這種alter的方式就很快，因為他是直接修改.firm文件而不涉及表數(shù)據(jù)。所以這個操作是特別快的。

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