數據庫優化---空間換時間優化

在查詢優化中，有一個重要的概念：

空間換取查詢時間

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

現在討論一下在OLTP系統中空間換取查詢時間常用的幾個方法：

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

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

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

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

5，數據緩存

5.1，數據庫緩存（如換成64位的系統和數據庫):

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

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

案例一：

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

但后來發現總公司匯總數據和分公司匯總數據不對。原來這個系統的網絡管理員可以在系統里直接去改數據。這樣我們的統計靜態數據就不對。為何要改這個數據，因為系統bug和下面業務人員的操作不對，就直接修改數據庫數據。

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

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

也是計算冗余表的一個主要問題：數據的更改，統計數據不準。

目的： 減少查詢數據量（匯總字段和表）

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

案例一：

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

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

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

目的：減少業務邏輯計算和匯總時間

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

索引是數據冗余的一種，也是將表中列的數據冗余出來，這樣查詢時就可以不用去查詢表，同時可以使用索引數據結構快速搜索seek。

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

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

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

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

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

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

5，數據緩存

一，數據庫緩存，也是一種優化方式，如將經常訪問的表放到內存里，這樣在內存中查詢速度要比在硬盤速度快很多。一般的方法如下：

1，將32位操作系統和數據庫改成64位的，提高內存使用和cpu尋址能力

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

二，程序緩存

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

三，靜態變量

案例一：

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

數據緩存目的：1，減少和數據庫的交互次數

2，盡可能使用內存（數據庫服務器和應用服務器）