SQL筆記之數(shù)據(jù)庫結(jié)構(gòu)優(yōu)化

1. 拆分表（冷熱數(shù)據(jù)分離）

不常用的數(shù)據(jù)為冷數(shù)據(jù)，反之則為熱數(shù)據(jù)。

如果一個表中的數(shù)據(jù)存在明顯的使用頻率差異，那么可以將冷熱數(shù)據(jù)分離。

通過這種分解可以提高表的查詢效率。

對于字段很多且有些字段使用不頻繁的表，可以通過這種分解的方式來優(yōu)化數(shù)據(jù)庫的性能。

例如：

會員members表存儲會員登錄認證信息，該表中有很多字段，如id、姓名、密碼、地址、電 話、個人描述字段。

其中地址、電話、個人描述等字段并不常用，可以將這些不常用的字段分解出另一 個表。

2. 增加中間表

假如當前有兩個表，學生信息表包含id、學號、姓名、年齡和班號，班級表包含id、班級名、地址、班長。

若現(xiàn)在有一個模塊需要經(jīng)常查詢帶有學生名稱（name）、學生所在班級名稱（className）、學生班級班 長（monitor）的學生信息。

根據(jù)這種情況可以創(chuàng)建一個 temp_student 表。temp_student表中存儲學生名稱（stu_name）、學生所在班級名稱（className）和學生班級班長（monitor）信息。

以后，可以直接從temp_student表中查詢學生名稱、班級名稱和班級班長，而不用每次都進行聯(lián)合查 詢。這樣可以提高數(shù)據(jù)庫的查詢速度。

3. 增加冗余字段

設(shè)計數(shù)據(jù)庫表時應盡量遵循范式理論的規(guī)約，盡可能減少冗余字段，讓數(shù)據(jù)庫設(shè)計看起來精致、優(yōu)雅。 但是，合理地加入冗余字段可以提高查詢速度。

表的規(guī)范化程度越高，表與表之間的關(guān)系就越多，需要連接查詢的情況也就越多。

尤其在數(shù)據(jù)量大，而 且需要頻繁進行連接的時候，為了提升效率，我們也可以考慮增加冗余字段來減少連接。

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

（1）對整數(shù)類型數(shù)據(jù)進行優(yōu)化。

遇到整數(shù)類型的字段可以用 INT 型 。這樣做的理由是，INT 型數(shù)據(jù)有足夠大的取值范圍，不用擔心數(shù) 據(jù)超出取值范圍的問題。

剛開始做項目的時候，首先要保證系統(tǒng)的穩(wěn)定性，這樣設(shè)計字段類型是可以 的。

但在數(shù)據(jù)量很大的時候，數(shù)據(jù)類型的定義，在很大程度上會影響到系統(tǒng)整體的執(zhí)行效率。

對于 非負型 的數(shù)據(jù)（如自增ID、整型IP）來說，要優(yōu)先使用無符號整型 UNSIGNED 來存儲。

因為無符號 相對于有符號，同樣的字節(jié)數(shù)，存儲的數(shù)值范圍更大。

如tinyint有符號為-128-127，無符號為0-255，多出一倍的存儲空間。

（2）既可以使用文本類型也可以使用整數(shù)類型的字段，要選擇使用整數(shù)類型。

跟文本類型數(shù)據(jù)相比，大整數(shù)往往占用更少的存儲空間 ，因此，在存取和比對的時候，可以占用更少的 內(nèi)存空間。

所以，在二者皆可用的情況下，盡量使用整數(shù)類型，這樣可以提高查詢的效率。

如：將IP地 址轉(zhuǎn)換成整型數(shù)據(jù)。

（3）避免使用TEXT、BLOB數(shù)據(jù)類型

（4）避免使用ENUM類型

修改ENUM值需要使用ALTER語句。

ENUM類型的ORDER BY 操作效率低，需要額外操作。使用TINYINT來代替ENUM類型。

（5）使用TIMESTAMP存儲時間

TIMESTAMP存儲的時間范圍1970-01-01 00:00:01 ~ 2038-01_19-03:14:07。

TIMESTAMP使用4字節(jié)，DATETIME使用8個字節(jié)，同時TIMESTAMP具有自動賦值以及自動更新的特性。

（6）用DECIMAL代替FLOAT和DOUBLE存儲精確浮點數(shù)

• 非精準浮點： float, double
• 精準浮點：decimal

Decimal類型為精準浮點數(shù)，在計算時不會丟失精度，尤其是財務相關(guān)的金融類數(shù)據(jù)。

占用空間由定義的寬度決定，每4個字節(jié)可以存儲9位數(shù)字，并且小數(shù)點要占用一個字節(jié)。

可用于存儲比bigint更大的整型數(shù)據(jù)。

總之，遇到數(shù)據(jù)量大的項目時，一定要在充分了解業(yè)務需求的前提下，合理優(yōu)化數(shù)據(jù)類型，這樣才能充 分發(fā)揮資源的效率，使系統(tǒng)達到最優(yōu)。

5. 優(yōu)化插入記錄的速度

插入記錄時，影響插入速度的主要是索引、唯一性校驗、一次插入記錄條數(shù)等。根據(jù)這些情況可以分別進行優(yōu)化。

（1）MyISAM引擎的表：

• ① 禁用索引
• ② 禁用唯一性檢查
• ③ 使用批量插入

插入多條記錄時，可以使用一條INSERT語句插入一條數(shù)據(jù)，也可以使用一條INSERT語句插入多條數(shù)據(jù)。

插入一條記錄的INSERT語句情形如下：

insert into student values(1,'zhangsan',18,1);
insert into student values(2,'lisi',17,1);
insert into student values(3,'wangwu',17,1);
insert into student values(4,'zhaoliu',19,1);

使用一條INSERT語句插入多條記錄的情形如下：

insert into student values
(1,'zhangsan',18,1),
(2,'lisi',17,1),
(3,'wangwu',17,1),
(4,'zhaoliu',19,1);

第2種情形的插入速度要比第1種情形快。

• ④ 使用LOAD DATA INFILE 批量導入

當需要批量導入數(shù)據(jù)時，如果能用LOAD DATA INFILE語句，就盡量使用。

因為LOAD DATA INFILE語句導入數(shù)據(jù)的速度比INSERT語句塊。

（2） InnoDB引擎的表：

• ① 禁用唯一性檢查

插入數(shù)據(jù)之前執(zhí)行set unique_checks=0來禁止對唯一索引的檢查，數(shù)據(jù)導入完成之后再運行set unique_check=1。

這個和MyISAM引擎的使用方法一樣。

• ② 禁用外鍵檢查
• ③ 禁止自動提交

6. 使用非空約束

總結(jié)

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論