提高資料庫並發效能概要

Table of Contents

• 1 細部鎖定表
• 2 拆表
  • 2.1 物理拆分方式
  • 2.2 邏輯拆分方式
• 3 多資料來源(多庫）
• 4 樂觀鎖
• 5 暫存資料表
• 6 緩衝
• 7 資料庫叢集
• 8 讀寫分離策略
• 9 SQL最佳化
• 10 正確使用索引

1 細部鎖定表

1. 減少大事物操作 (一個包含很多SQL語句、牽涉很多表的大事物操作鎖住的資源也會多）；
2. 盡量不使用表級獨佔鎖定，而是用行級鎖；又因為鎖一般是由資料庫根據你的 SQL自動分配，所以要注意SQL的寫法。比如

   select * into B from A

   該操作會建立B表，並對B表加表鎖。

2 拆表

與降低鎖的粒度異曲同工。

2.1 物理拆分方式

1. 水平分割: 把不同行記錄分開放進不同表中。比如男性放入一張表中，女性放入另一張表。
2. 垂直分割: 把不同列分別放入不同表中。比如主使用者資訊放入一張表，附加資訊放入另一張表。
3. 混合拆分（水平分割+垂直分割）

2.2 邏輯拆分方式

1. 按範圍拆分，比如所有zipcode列的值 between 70000 and 79999的記錄拆成一個表.
2. 分組拆分，比如country列為冰島、挪威、瑞典、丹麥這些北歐國家的記錄拆成一個表
3. 雜湊拆分：參見資料庫的雜湊設計：http://hi.baidu.com/dapplehou/blog/item/471a277ff8eafc0529388ac4.html
4. 混合拆分，以上幾種拆分方式的綜合。
5. 可建立尋找表以方便對拆分的子表進行登記、查詢、定位。

3 多資料來源(多庫）

本質是拆庫。與拆表異曲同工。資料庫路由（定位），對於程式員來說就是資料來源路由(尋找、定位），需要定義多個資料來源，並採取合適演算法進行定位。可參考
http://hi.baidu.com/dapplehou/blog/item/6ba8034f5114e038aec3abdb.html 多庫部分.

什麼是合適演算法？那要根據庫的切分方式、效能需要以及業務需要來定奪。

4 樂觀鎖

可以降低資料庫長事物。只在提交的那一刹那判斷自己提交的資料是否已經被別人修改過了，如是，則認為自己要提交的資料是到期資料，取消提交，否則，提交成功。避免長事物帶來的等待。通常採用版本號碼來判斷是否到期資料。過程如下：

1. A、B兩人同時讀取同一條銀行賬戶記錄，記錄版本號碼為1。
2. A修改此記錄：增加100元，版本號碼加1。
3. B修改此記錄：減少50元，版本號碼加1。
4. A提交。假設提交SQL如下：

   update account set money=money+50,version=version+1 where id=10 and version=1

   提交成功。id=10的這條記錄version為2.

5. B提交。提交SQL如下：

   update account set money=money-50,version=version+1 where id=10 and version=1

   因為version已經等於2了，所以這條更新語句會影響0行。這樣，誰先提交，誰成功。後來者的不會因為先到者讀取後遲遲不提交而等待等待。減少了站著茅坑不拉屎的情況。提高了並發效能。

5 暫存資料表

使用局部暫存資料表提高並發效能。局部暫存資料表特點是多使用者操作互不干擾。一個局部暫存資料表只對一個session可見、有效。多使用者並發實際是各操作各自的暫存資料表，不會產生資料表鎖的問題。提高並發效率。

SQL Server建立暫存資料表語句：

CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)

Oracle:

CREATE GLOBAL TEMPORARY TABLE MyTempTable

把主表（頻繁使用又資料量大）中的部分資料放入局部暫存資料表，然後在暫存資料表裡進行計算、統計等工作，然後同步到主表中去（同步過程可用樂觀鎖機制），減少了主表在並發時鎖等待的開銷。

暫存資料表效能提升原理和拆分表原理相同：通過把一個大集合拆成小集合，然後在小集合上進行計算。

6 緩衝

1. 原理同暫存資料表。先在記憶體裡折騰(計算、update等），然後把折騰的結果一次性的同步給db(同步過程可使用樂觀鎖機制）.
2. 演算法最佳化。

   有的操作，雖然沒有更新過程，但頻繁的複雜查詢也會使資料庫效能降低。這種情況下，可採取把一些查詢後的結果且不輕易變的資料儲存在緩衝中供後續使用。

   例如：不同使用者登入後的菜單顯示可能要根據不同部門、不同許可權以及委託、兼職等複雜邏輯來確定菜單的顯示項目，資料庫的查詢可能非常消耗效能，這種情況下，可緩衝所有菜單形成菜單池，再採用亨元模式，緩衝每個使用者的可見菜單，這樣減少了資料庫查詢次數，避免了顯示菜單時每次都要查詢資料庫的效能損耗。

   亨元模式可參見：http://hi.baidu.com/dapplehou/blog/item/aeca1d958268e1047bf48089.html

7 資料庫叢集

與多庫(拆庫）區別： 叢集是每個庫上都有一份相同的資料；拆庫是每個庫只有全集的部分資料。

多資料庫冗餘可以減少單個資料庫的負載，總體上提高了效能。它有3個技術關鍵點：

1. 資料庫查詢路由
2. 資料庫同步
3. 負載平衡演算法

8 讀寫分離策略

在讀操作遠多於寫操作的大型應用中，可採用讀寫分離策略。用來讀的資料庫叫從資料庫，用來寫或者事務性查詢的叫主要資料庫。主要資料庫的資料變化會通過某些策略（比如資料庫複寫）同步到從資料庫，從而保證主從資料庫資料一致性。其本質是空間換時間，即：通過增加資料庫冗餘，降低資料庫對鎖的複雜調度。但另一方面，增加了應用架構的複雜度 （這種複雜性可以通過使用第三方中介軟體來降低）。

9 SQL最佳化

SQL語句是資料庫資源消耗的主要來源，它往往都是可以進一步最佳化的，SQL最佳化在時間成本和風險上代價低。

我們寫的SQL語句都會被查詢最佳化工具自動最佳化，它的最佳化原則是這樣的：

1. 選擇運算優先運行。（即優先通過條件過濾記錄集）2. 投影運算與選擇運算同時進行。3. 將笛卡爾積與隨後的選擇運算合并為串連運算4. 投影運算與其他運算同時進行5. 尋找公用子運算式並將結果加以儲存6. 對檔案進行預先處理

由於有了以上最佳化，所以下面兩種SQL的寫法效率一樣

select * from A,B where A.id=B.id and A.name='Jake'select * from A,B were A.name='Jake' and A.id=B.id

隨著資料庫的進化，原來效率低的語句現在不一定低，需要實際測試。

10 正確使用索引

1. 索引列
   1. 經常查詢的列
   2. 經常排序的列
   3. 經常串連的列
2. 非索引列
   1. 不經常使用的列
   2. 重複記錄太多的列(比如性別）
   3. Image text類型的列 

完畢!

有問題歡迎加入群號探討:173711587