SQL筆記之?dāng)?shù)據(jù)庫(kù)結(jié)構(gòu)優(yōu)化

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

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

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

通過(guò)這種分解可以提高表的查詢(xún)效率。

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

例如：

會(huì)員members表存儲(chǔ)會(huì)員登錄認(rèn)證信息，該表中有很多字段，如id、姓名、密碼、地址、電 話(huà)、個(gè)人描述字段。

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

2. 增加中間表

假如當(dāng)前有兩個(gè)表，學(xué)生信息表包含id、學(xué)號(hào)、姓名、年齡和班號(hào)，班級(jí)表包含id、班級(jí)名、地址、班長(zhǎng)。

若現(xiàn)在有一個(gè)模塊需要經(jīng)常查詢(xún)帶有學(xué)生名稱(chēng)（name）、學(xué)生所在班級(jí)名稱(chēng)（className）、學(xué)生班級(jí)班 長(zhǎng)（monitor）的學(xué)生信息。

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

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

3. 增加冗余字段

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

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

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

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

（1）對(duì)整數(shù)類(lèi)型數(shù)據(jù)進(jìn)行優(yōu)化。

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

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

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

對(duì)于 非負(fù)型 的數(shù)據(jù)（如自增ID、整型IP）來(lái)說(shuō)，要優(yōu)先使用無(wú)符號(hào)整型 UNSIGNED 來(lái)存儲(chǔ)。

因?yàn)闊o(wú)符號(hào) 相對(duì)于有符號(hào)，同樣的字節(jié)數(shù)，存儲(chǔ)的數(shù)值范圍更大。

如tinyint有符號(hào)為-128-127，無(wú)符號(hào)為0-255，多出一倍的存儲(chǔ)空間。

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

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

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

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

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

（4）避免使用ENUM類(lèi)型

修改ENUM值需要使用ALTER語(yǔ)句。

ENUM類(lèi)型的ORDER BY 操作效率低，需要額外操作。使用TINYINT來(lái)代替ENUM類(lèi)型。

（5）使用TIMESTAMP存儲(chǔ)時(shí)間

TIMESTAMP存儲(chǔ)的時(shí)間范圍1970-01-01 00:00:01 ~ 2038-01_19-03:14:07。

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

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

• 非精準(zhǔn)浮點(diǎn)： float, double
• 精準(zhǔn)浮點(diǎn)：decimal

Decimal類(lèi)型為精準(zhǔn)浮點(diǎn)數(shù)，在計(jì)算時(shí)不會(huì)丟失精度，尤其是財(cái)務(wù)相關(guān)的金融類(lèi)數(shù)據(jù)。

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

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

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

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

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

（1）MyISAM引擎的表：

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

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

插入一條記錄的INSERT語(yǔ)句情形如下：

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語(yǔ)句插入多條記錄的情形如下：

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 批量導(dǎo)入

當(dāng)需要批量導(dǎo)入數(shù)據(jù)時(shí)，如果能用LOAD DATA INFILE語(yǔ)句，就盡量使用。

因?yàn)長(zhǎng)OAD DATA INFILE語(yǔ)句導(dǎo)入數(shù)據(jù)的速度比INSERT語(yǔ)句塊。

（2） InnoDB引擎的表：

• ① 禁用唯一性檢查

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

這個(gè)和MyISAM引擎的使用方法一樣。

• ② 禁用外鍵檢查
• ③ 禁止自動(dòng)提交

6. 使用非空約束

總結(jié)

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

最新評(píng)論