資料庫設計優化

資料庫的基本語法的使用，很多，我也沒有去整理，所以在這里就不介紹那些基本的語法了，如果對這方面有問題，我在這里給大家分享一個方法：

 資料庫基本語法的使用，可以參照下面的文章：

sqlserver語法之觸發器：https://www.cnblogs.com/hoojo/archive/2011/07/20/2111316.htmlsqlserver語法之存盤程序：http://www.cnblogs.com/hoojo/archive/2011/07/19/2110862.htmlsqlserver語法之各種查詢：http://www.cnblogs.com/hoojo/archive/2011/07/16/2108129.htmlsqlserver語法之視圖：http://www.cnblogs.com/hoojo/archive/2011/07/18/2109291.htmlsqlserver語法之例外處理：http://www.cnblogs.com/hoojo/archive/2011/07/19/2110325.html

 　　SQL陳述句應該怎樣去優化？在職業生涯中，有一次維護一個專案中的一個模塊，之前做的那個同事離職了，交給我來維護，我記得好像是七八張表吧，各種join、order by、distinct、group by、having.......看得頭大，這樣的SQL陳述句應該怎么優化呢？額......這怎么優化啊？沒辦法優化，建議從表的設計方面考慮吧，后來我也是這樣做的，重新設計表，將表的數量減少，將資料更新到那寫新表中去，即使不刷資料，流程中做下控制，時間久了，老表中的資料也都會更新到新表中去，

資料庫設計步驟：

　　1、需求分析：大概明白需要存盤什么

　　2、概要設計階段：E-R圖，表-屬性-關系表，解決多方的溝通問題

　　3、詳細設計，細致到表甚至欄位

關系型資料庫：面向物件的封裝+關系

面向物件的思想來建表：

　　一對一：人----身份證；垂直分表的時候；相同主鍵/外鍵

　　一對多：訂單----商品表；省-----市；公司-----員工；主外鍵表示

　　多對多：用戶----選單；學生----課程；女神----備胎；關系表/中間/映射表

三大范式：

　　第一范式：每一列保持原子性，不可分割

　　　　原子性：不可分割，

　　　　人----作業電話/家庭電話/移動電話，必須要拆分開，分三個列來表示

　　　　【錯誤的】核心業務表-----醫院/醫生/疾病/癥狀/藥品/可是----不同型別有不同的資訊----用XML放在一個欄位====》這種方式能解決問題，但是違反了第一范式

　　第二范式：每一列都得跟主鍵相關，一張表應該描述一個物件

　　　　a  要有主鍵   b  資訊跟主鍵相關

　　　　公司-----多用戶，不能用一個用戶表（里面寫公司的資訊），應該是公司一張表，用戶一張表，

　　　　第二范式，一般不要違背，

　　第三范式：每一列都得跟主鍵直接相關，而不是間接相關

　　　　用戶---公司ID----公司名稱（非直接相關）===》違背了第三范式，因為用戶表中包含了公司名稱，如果需要顯示公司名稱，用第三范式，查詢會很慢，還要關聯公司表，但是更麻煩，更新了公司表，還要用戶更新表中的公司名稱，這個可以用一個定時作業去解決，如果更新很少但是查詢特別多，這個時候就可以去適當違背第三范式，

三大范式只是建議，就好像設計模式一樣，其實都可能被違背，正確的設計會減少很多作業量，

基礎內容：

　　命名風格：雙駝峰命名；大寫字母的模塊縮寫，不要下劃線（避免映射），英文單詞，首字母大寫；拼音/漢字完全反對，

　　主鍵：資料庫自動增加，int/bigint，SQLserver默認聚集索引，空間小，可以有業務意義（比如大于1000W的都是新用戶）；多庫環境，不同環境的資料庫id沖突；訂單連續，容易讓同行推出你的銷售量

　　GUID：程式生成的，全球唯一，插入資料庫，不會錯誤的join，無限的，方便匯入；空間大，沒有自身含義，沒有聚集索引（創建時間來聚集）

　　外鍵：描述書關系，規范資料關系；外鍵做資料校驗，級聯洗掉（其實作在都是假洗掉）；有嚴格的資料關系的時候，使用外鍵；匯入麻煩、增刪改查的資料庫多一步操作；更多的是，通程序式來完成，虛擬外鍵；

　　對外鍵要合理使用：盡量少的使用外鍵；大型互聯網專案的瓶頸一般都在資料庫，盡量少的讓資料庫做事；如果資料嚴謹性要求很高，可以用外鍵；其他的就不怎么用了，尤其是互聯網專案，

資料庫事務：

　　多條SQL作為一個整體提交給資料庫系統，要么全部執行，要么全部取消，是一個不可分割的邏輯單元；兩個SQL陳述句，一個下單成功，另外一個減少庫存，事務保證一起成功或者一起失敗，

　　最簡單的事務，就是 update A set name="123",tag=123 where id=123

　　begin tran--rollback/commit；默認開啟事務，就是跨域省略begin tran；顯示開啟事務，還有個開啟全部事務，

ACID

　　原子性：要么都成功，要么都失敗

　　一致性：事務執行完，資料都是正確的

　　隔離性：兩個事務同時操作一張表，B事務要么在A事務前完成，要么在A事務完成執行（鎖表）

　　持久性：資料提交后，就固化下來

鎖

　　多用戶同時訪問一個資料資源：

　　　　1、修改資料===》同時修改，一個改成張三，一個改成李四，總歸有一個被覆寫

　　　　2、不可重復讀

　　　　3、臟讀/幻讀===》要么讀到的是更新前的，要么讀到的是更新后的，正在更新的的資料，突然來查詢了，，，，

　　鎖就是，保證訪問同一資源時，有個先后順序管理，處理并發問題，

　　樂觀鎖：

　　　　認為沒有并發，讀取資料---更新---保存，樂觀鎖就是沒有鎖，

　　　　更新的時候做一個判斷：（樂觀鎖的方式）

　　　　　　1、時間戳：資料庫增加一列TimeSpan，long，每次查詢拿出來，更新時+1，保存時，判斷是否大于資料庫的值，要保證任何操作都按照這個規范，所以也有個漏洞，比如其他渠道更新，

　　　　　　2、Version，版本號

　　　　　　3、檢查更新欄位，一定是原始的資料上更新的

　　　　　　4、檢查所有的欄位

　　　　樂觀鎖的性能高！！！！！！！

　　悲觀鎖：

　　　　認為任何時候都可能在多執行緒并發，讀資料的時候，別人恰好在修改；基于資料庫的鎖的機制來完成的

　　　　共享鎖，也叫S鎖，讀的鎖：允許別的事務來讀，但是不允許修改；讀完就釋放，鎖定資料頁；除非HoldLock一直鎖定

　　　　排它鎖，也叫X鎖，寫的鎖：準備寫資料，不允許讀也不允許寫，比如select * from a update b，b表就不允許讀，

　　　　更新所，也叫U鎖：先查詢再更新

　　　　行鎖：where id = 2

　　　　表鎖：where 1 = 1

　　怎么避免死鎖呢？高并發的情況下，死鎖是不可能避免的，只能盡量減少，

　　　　1、不用鎖，就不會死鎖，樂觀鎖也可以，因為樂觀鎖沒有鎖的概念

　　　　2、統一操作順序，先A后B再C

　　　　3、最小單元鎖，鎖里面的操作盡量減少

　　　　4、避免事務中等待用戶輸入

　　　　5、減少資料庫并發

　　　　6、分庫/分表/磁區

　　　　7、降低事務級別----謹慎使用

　　　　8、設定死鎖時間----謹慎使用

　　高并發的系統，死鎖其實是不可避免的，只能盡量減少

　　　　1、按照固有的順序操作資料

　　　　2、事務盡量的簡短；更不要再事務期間搞什么等待；降低并發；事務里面耗時短；鎖的隔離級別

存盤程序

　　存盤程序Procedure是一組為了完成特定功能的SQL陳述句集合，經編譯后存盤在資料庫中，用戶用過指定存盤程序的名稱并給出引數來執行，

　　壞處：

　　　　存盤程序不太好維護；邏輯分散了；不好管理；全部資料庫操作

　　　　【少用】，大量復雜的邏輯、計算、資料傳輸，可以用，否則還是程式來完成

　　好處：

　　　　資料庫編程（下訂單---減少一個庫存---寫日志），遷移方便；預編譯；減少資料的傳輸

觸發器

　　觸發器是一種特殊型別的存盤程序，觸發器主要是通過事件進行觸發被自動呼叫執行的；盡量不要使用；事發前/事發后執行動作；資料庫的自發動作，更新Company的name，會自動更新別的表名字，這個是完整動作；觸發器泛濫，嵌套式，有很多個觸發器的時候，排查問題都不好排查

游標

　　游標實際上是一種能從多條資料記錄的結果集中每次提取一條記錄的機制；復雜的結果集，復雜的操作就可以用，但是我倒是從來沒用到過游標，一般存盤程序中會用到游標

視圖

　　視圖就是一個虛擬的資料表，該資料表中的資料記錄是有一條查詢陳述句的查詢結果得到的

函式

　　不太推薦使用自定義函式，因為計算交給資料庫，沒法使用索引

欄位型別

　　這里有一個博客，介紹的還是蠻詳細的：https://www.cnblogs.com/andy_tigger/archive/2011/08/21/2147745.html

欄位可空

　　數值型別的，不要可空，in查詢  not in 查詢，會沒有結果，一般給個默認值；盡量非空，看情況（時間不適合默認值）

統計欄位

　　創建時間、創建人、最后更新時間、最后更新人、state、IsEnable

軟洗掉

　　假洗掉、邏輯洗掉，把狀態改成洗掉，而不是物理洗掉，會造成一點空間浪費，但是可以保存資料備份，

資料庫瓶頸，資料量的增加，并發的增加：

　　1、加強硬體，換商業化軟體====》花錢解決

　　2、架構設計上來解決，讓資料庫最少的做事，放棄觸發器、外鍵、存盤程序、函式

資料庫復雜均衡

 多要資料庫完成一臺的事，資料庫要保證一致性的

負載均衡：

　　利用多臺服務器的讀寫能力，但是資料同步和訪問分配交給第三方軟體

Moebius：

　　讀的壓力分配到不同的服務器，寫其實是多型服務器都得完成，對外只有一個IP，使用者是不知道細節的，

讀寫分離

　　二八原則：資料庫中80%操作都是讀，只有20%是寫，

　　實作原理：就是把讀和寫的壓力分開，降低IO壓力；一主多從，主庫寫從庫讀；資料同步的問題，從主庫到從庫（肯定是有延遲的，網速沒問題，兩三秒吧）

　　1、資料庫里面列別的資料庫？link到主要+定時的job，是不是就可以了？【效率低】

　　2、日志傳送，SQLserver2005，備份----復制----復制，這種方式簡單，但是有局限性（局域網，只能檔案夾共享）

 　　3、鏡像，snapshot：記憶體拍照；主庫，對外提供服務；從庫，通過快斬訓復，資料跟主課一直；監控轉移，負責檢查狀況，有問題切到從庫

　　4、資料復制（發布/訂閱）

 　　主庫---發布到服務器---從庫；延遲小，配置方便，但是需要程式配合，發布訂閱后，從資料庫是可以水平卓展的，查詢就不是大問題，業務量進一步增加，寫庫還是抗不住===》分庫/磁區/分表

分庫/分表/磁區（最考驗設計和開發）

　　多臺服務器

　　分庫：

　　　　1、系統里面有訂單/物流/倉儲/論壇/博客/客服

　　　　　　垂直分庫，按照業務拆分庫，不同的庫不同的服務器

　　　　2、訂單增刪改查特別大

　　　　　　水平分庫，每個庫結構一致，資料不一致（地域/時間/類別/隨機演算法）

　　分表　　

　　　　垂直分表：

　　　　　　1、文章表，10個常規欄位，還有一個很長很長的內容欄位

　　　　　　　　垂直分表，直接減小表的體積，提升增刪改查的效率

　　　　　　2、單表資料量太大（訂單表/商品表）

　　　　　　　　水平分表（地域/時間/類別/隨機演算法）

　　分完之后，資料的查詢怎么辦呢？

　　　　拆分的時候要根據產品特點來

　　　　　　房產-----按照城市拆分

　　　　　　訂單/流水-----按時間拆分

　　　　　　追求平均----隨機演算法

　　　　　　拋磚引玉，自己要根據業務特點來拆分，考慮業務，拆分的維度，最好不要再被聚合查詢，但是又無法完全避免業務操作，

　　　　1、跨庫垮表join---很不推薦

　　　　2、匯總表，定時任務定時作業====》專門應該特殊要求

　　　　3、特殊要求，就是為難你，同步一個報表庫（總庫），專門滿足需求，性能肯定低

　　　　4、向業務妥協

　　表磁區：就是水平分表的意思，也可以垂直分（用的少）

標籤：SQL Server

上一篇：SQL Server如何正確的洗掉Windows認證用戶

下一篇：資料庫索引、優化