數(shù)據(jù)庫優(yōu)化---空間換時間優(yōu)化

在查詢優(yōu)化中，有一個重要的概念：

空間換取查詢時間

這一理論最好的應(yīng)用就是：數(shù)據(jù)倉庫（OLAP）：在海量數(shù)據(jù)庫里（一般是TB級）分析數(shù)據(jù)，通過對數(shù)據(jù)的ETL和計算匯總，得到有用的數(shù)據(jù)，并通過不同維度查看統(tǒng)計數(shù)據(jù)（一般比較少），實現(xiàn)上鉆和下鉆分析數(shù)據(jù)。

現(xiàn)在討論一下在OLTP系統(tǒng)中空間換取查詢時間常用的幾個方法：

1，增加冗余表（計算匯總表）

2，增加冗余字段（包括計算字段)

3, 增加索引(包括計算索引）

4，增加索引視圖（物化視圖)

5，數(shù)據(jù)緩存

5.1，數(shù)據(jù)庫緩存（如換成64位的系統(tǒng)和數(shù)據(jù)庫):

5.2，程序緩存（如:hibernate中的緩存)

我們在開發(fā)系統(tǒng)中或多或少的使用了以上的方法，這次結(jié)合實際，討論一下這些方法的具體使用和目的：

案例一：

我們優(yōu)化一個系統(tǒng)，原來的開發(fā)人員，由于數(shù)據(jù)量比較大，為保證查詢性能，對于辦事處和分公司是實時計算，片區(qū)和總公司是非實時統(tǒng)計，開發(fā)人員就使用統(tǒng)計片區(qū)和總公司統(tǒng)計匯總靜態(tài)表，每天晚上凌晨1點計算上一天的的匯總數(shù)據(jù)。并存放到了靜態(tài)表。統(tǒng)計時，片區(qū)和總公司是統(tǒng)計靜態(tài)表數(shù)據(jù)，統(tǒng)計速度很快。

但后來發(fā)現(xiàn)總公司匯總數(shù)據(jù)和分公司匯總數(shù)據(jù)不對。原來這個系統(tǒng)的網(wǎng)絡(luò)管理員可以在系統(tǒng)里直接去改數(shù)據(jù)。這樣我們的統(tǒng)計靜態(tài)數(shù)據(jù)就不對。為何要改這個數(shù)據(jù)，因為系統(tǒng)bug和下面業(yè)務(wù)人員的操作不對，就直接修改數(shù)據(jù)庫數(shù)據(jù)。

為了保證總公司數(shù)據(jù)和分公司匯總數(shù)據(jù)一致，后來取消了這個計算冗余表，直接實時統(tǒng)計。

總結(jié)：這個例子說明冗余表的利和弊，假如我們的系統(tǒng)數(shù)據(jù)可能更改，這時冗余表的數(shù)據(jù)就不準確了，這點要注意的。這

也是計算冗余表的一個主要問題：數(shù)據(jù)的更改，統(tǒng)計數(shù)據(jù)不準。

目的： 減少查詢數(shù)據(jù)量（匯總字段和表）

2，增加冗余字段（包括計算字段)

案例一：

系統(tǒng)的一個模塊，業(yè)務(wù)要求是用戶輸入一段日期（時間段）的投入總金額，由于業(yè)務(wù)要求計算每天的金額，這時如果沒有每天的數(shù)據(jù)，則只有去查詢出總數(shù)，在通過時間相減得到總天數(shù)，在計算出每天的金額。

如果數(shù)據(jù)量比較大，計算時間比較長，這時我們就在程序里做一個計算功能，把這個數(shù)據(jù)算好自己放到表里，這時就不需要統(tǒng)計時計算了。

總結(jié)：我們增加冗余字段，目的就是為了將后期通過數(shù)據(jù)庫計算的時間細化，分散到每次插入數(shù)據(jù)時的時間，這點時間是很少的。但積累起來卻是很多，特別是在后面統(tǒng)計和分析時就很明顯。當然，我們可以細化時間和先期匯總數(shù)據(jù)，如總數(shù)。可以插入數(shù)據(jù)時，把總數(shù)計算出來，有利于減少匯總數(shù)據(jù)量。

目的：減少業(yè)務(wù)邏輯計算和匯總時間

3, 增加索引(包括計算索引）

索引是數(shù)據(jù)冗余的一種，也是將表中列的數(shù)據(jù)冗余出來，這樣查詢時就可以不用去查詢表，同時可以使用索引數(shù)據(jù)結(jié)構(gòu)快速搜索seek。

我們說的索引覆蓋，就是查詢中將需要的列全部冗余，通過索引來查詢，而不需要去表中查詢數(shù)據(jù)。

目的：查詢索引，而不掃描表（盡管掃描表有時是最優(yōu)的）

4，增加索引視圖（物化視圖）

我們說的索引視圖（物化視圖），就是對需要查詢關(guān)聯(lián)的數(shù)據(jù)或匯總數(shù)據(jù)，預(yù)先通過實體表存放起來。這樣查詢時可以不去關(guān)聯(lián)的表（表有時可能很多，數(shù)據(jù)量比較大），去直接去查詢索引視圖（物化視圖）。

這里，如上面“增加冗余表”，提到數(shù)據(jù)可能更改的問題，就可以用索引視圖（物化視圖）來實現(xiàn)。

當然索引視圖（物化視圖）有條件限制，不是所有查詢都可以使用的。如sql server索引視圖就只能用inner join關(guān)聯(lián)，同時要有唯一的聚集索引。由于索引視圖（物化視圖）成本較高，一般在OLTP系統(tǒng)中使用較少（以大量犧牲DML時間為代價），在OLAP中使用較多。 目的：減少查詢數(shù)據(jù)量

5，數(shù)據(jù)緩存

一，數(shù)據(jù)庫緩存，也是一種優(yōu)化方式，如將經(jīng)常訪問的表放到內(nèi)存里，這樣在內(nèi)存中查詢速度要比在硬盤速度快很多。一般的方法如下：

1，將32位操作系統(tǒng)和數(shù)據(jù)庫改成64位的，提高內(nèi)存使用和cpu尋址能力

2，在Oracle里可以把使用頻繁但數(shù)據(jù)量比較少的表（keep cache）起來，啟動時就一直放到內(nèi)存中。

二，程序緩存

目前有一些緩存框架，如JBOSS Cache，hibernate緩存，Ehcache等緩存框架，可以減少查詢數(shù)據(jù)庫，提高速度。

三，靜態(tài)變量

案例一：

我們以前做一個系統(tǒng)，由于沒有用緩存框架，一般使用的靜態(tài)基本的數(shù)據(jù)（如查詢機構(gòu)，車輛，人員等），啟動程序時，去數(shù)據(jù)庫去查詢數(shù)據(jù)，放到一個List里面，然后封裝了一些靜態(tài)方法，這些基本信息就放到靜態(tài)list里。這時可以做一些的查詢，如取一些數(shù)據(jù)，不用去查詢數(shù)據(jù)庫了，直接調(diào)用靜態(tài)方法，減少和數(shù)據(jù)庫交互次數(shù)。

數(shù)據(jù)緩存目的：1，減少和數(shù)據(jù)庫的交互次數(shù)

2，盡可能使用內(nèi)存（數(shù)據(jù)庫服務(wù)器和應(yīng)用服務(wù)器）