數(shù)據(jù)庫(kù)優(yōu)化(數(shù)據(jù)庫(kù)自身的優(yōu)化，數(shù)據(jù)庫(kù)表優(yōu)化，程序操作優(yōu)化)

一、增加次數(shù)據(jù)文件，設(shè)置文件自動(dòng)增長(zhǎng)（粗略數(shù)據(jù)分區(qū)）

1. 增加次數(shù)據(jù)文件

從SQL SERVER
2005開(kāi)始，數(shù)據(jù)庫(kù)不默認(rèn)生成NDF數(shù)據(jù)文件，一般情況下有一個(gè)主數(shù)據(jù)文件（MDF）就夠了，但是有些大型的數(shù)據(jù)庫(kù)，由于信息很多，而且查詢頻繁，所以為了提高查詢速度，可以把一些表或者一些表中的部分記錄分開(kāi)存儲(chǔ)在不同的數(shù)據(jù)文件里
由于CPU和內(nèi)存的速度遠(yuǎn)大于硬盤(pán)的讀寫(xiě)速度，所以可以把不同的數(shù)據(jù)文件放在不同的物理硬盤(pán)里，這樣執(zhí)行查詢的時(shí)候，就可以讓多個(gè)硬盤(pán)同時(shí)進(jìn)行查詢，以充分利用CPU和內(nèi)存的性能，提高查詢速度。
在這里詳細(xì)介紹一下其寫(xiě)入的原理，數(shù)據(jù)文件（MDF、NDF）和日志文件（LDF）的寫(xiě)入方式是不一樣的：

數(shù)據(jù)文件：SQL
Server按照同一個(gè)文件組里面的所有文件現(xiàn)有空閑空間的大小，按這個(gè)比例把新的數(shù)據(jù)分布到所有有空間的數(shù)據(jù)文件里，如果有三個(gè)數(shù)據(jù)文件A.MDF，B.NDF，C.NDF，空閑大小分別為200mb，100mb，和50mb，那么寫(xiě)入一個(gè)70mb的東西，他就會(huì)向ABC三個(gè)文件中一次寫(xiě)入40、20、10的數(shù)據(jù)，如果某個(gè)日志文件已滿，就不會(huì)向其寫(xiě)入

日志文件：日志文件是按照順序?qū)懭氲?，一個(gè)寫(xiě)滿，才會(huì)寫(xiě)入另外一個(gè)

由上可見(jiàn)，如果能增加其數(shù)據(jù)文件NDF,有利于大數(shù)據(jù)量的查詢速度，但是增加日志文件卻沒(méi)什么用處。

2. 設(shè)置文件自動(dòng)增長(zhǎng)（大數(shù)據(jù)量，小數(shù)據(jù)量無(wú)需設(shè)置）

在SQL Server
2005中，默認(rèn)MDF文件初始大小為5MB，自增為1MB，不限增長(zhǎng)，LDF初始為1MB，增長(zhǎng)為10%，限制文件增長(zhǎng)到一定的數(shù)目，一般設(shè)計(jì)中，使用SQL自帶的設(shè)計(jì)即可，但是大型數(shù)據(jù)庫(kù)設(shè)計(jì)中，最好親自去設(shè)計(jì)其增長(zhǎng)和初始大小，如果初始值太小，那么很快數(shù)據(jù)庫(kù)就會(huì)寫(xiě)滿，如果寫(xiě)滿，在進(jìn)行插入會(huì)是什么情況呢？當(dāng)數(shù)據(jù)文件寫(xiě)滿，進(jìn)行某些操作時(shí)，SQL
Server會(huì)讓操作等待，直到文件自動(dòng)增長(zhǎng)結(jié)束了，原先的那個(gè)操作才能繼續(xù)進(jìn)行。如果自增長(zhǎng)用了很長(zhǎng)時(shí)間，原先的操作會(huì)等不及就超時(shí)取消了（一般默認(rèn)的閾值是15秒），不但這個(gè)操作會(huì)回滾，文件自動(dòng)增長(zhǎng)也會(huì)被取消。也就是說(shuō)，這一次文件沒(méi)有得到任何增大，增長(zhǎng)的時(shí)間根據(jù)自動(dòng)增長(zhǎng)的大小確定的，如果太小，可能一次操作需要連續(xù)幾次增長(zhǎng)才能滿足，如果太大，就需要等待很長(zhǎng)時(shí)間，所以設(shè)置自動(dòng)增長(zhǎng)要注意一下幾點(diǎn)：

1）要設(shè)置成按固定大小增長(zhǎng)，而不能按比例。這樣就能避免一次增長(zhǎng)太多或者太少所帶來(lái)的不必要的麻煩。建議對(duì)比較小的數(shù)據(jù)庫(kù)，設(shè)置一次增長(zhǎng)50 MB到100
MB。對(duì)大的數(shù)據(jù)庫(kù)，設(shè)置一次增長(zhǎng)100 MB到200 MB。

2）要定期監(jiān)測(cè)各個(gè)數(shù)據(jù)文件的使用情況，盡量保證每個(gè)文件剩余的空間一樣大，或者是期望的比例。

3）設(shè)置文件最大值，以免SQL Server文件自增長(zhǎng)用盡磁盤(pán)空間，影響操作系統(tǒng)。

4）發(fā)生自增長(zhǎng)后，要及時(shí)檢查新的數(shù)據(jù)文件空間分配情況。避免SQL Server總是往個(gè)別文件寫(xiě)數(shù)據(jù)。

因此，對(duì)于一個(gè)比較繁忙的數(shù)據(jù)庫(kù)，推薦的設(shè)置是開(kāi)啟數(shù)據(jù)庫(kù)自動(dòng)增長(zhǎng)選項(xiàng)，以防數(shù)據(jù)庫(kù)空間用盡導(dǎo)致應(yīng)用程序失敗，但是要嚴(yán)格避免自動(dòng)增長(zhǎng)的發(fā)生。同時(shí)，盡量不要使用自動(dòng)收縮功能。

3. 數(shù)據(jù)和日志文件分開(kāi)存放在不同磁盤(pán)上

數(shù)據(jù)文件和日志文件的操作會(huì)產(chǎn)生大量的I/O。在可能的條件下，日志文件應(yīng)該存放在一個(gè)與數(shù)據(jù)和索引所在的數(shù)據(jù)文件不同的硬盤(pán)上以分散I/O，同時(shí)還有利于數(shù)據(jù)庫(kù)的災(zāi)難恢復(fù)。

二、表分區(qū)，索引分區(qū) （優(yōu)化①粗略的進(jìn)行了表分區(qū)，優(yōu)化②為精確數(shù)據(jù)分區(qū)）

1. 為什么要表分區(qū)？

當(dāng)一個(gè)表的數(shù)據(jù)量太大的時(shí)候，我們最想做的一件事是什么？將這個(gè)表一分為二或者更多分，但是表還是這個(gè)表，只是將其內(nèi)容存儲(chǔ)分開(kāi)，這樣讀取就快了N倍了

原理：表數(shù)據(jù)是無(wú)法放在文件中的，但是文件組可以放在文件中，表可以放在文件組中，這樣就間接實(shí)現(xiàn)了表數(shù)據(jù)存放在不同的文件中。能分區(qū)存儲(chǔ)的還有：表、索引和大型對(duì)象數(shù)據(jù)。

SQL SERVER 2005中，引入了表分區(qū)的概念，
當(dāng)表中的數(shù)據(jù)量不斷增大，查詢數(shù)據(jù)的速度就會(huì)變慢，應(yīng)用程序的性能就會(huì)下降，這時(shí)就應(yīng)該考慮對(duì)表進(jìn)行分區(qū),當(dāng)一個(gè)表里的數(shù)據(jù)很多時(shí)，可以將其分拆到多個(gè)的表里,因?yàn)橐獟呙璧臄?shù)據(jù)變得更少
,查詢可以更快地運(yùn)行,這樣操作大大提高了性能,表進(jìn)行分區(qū)后，邏輯上表仍然是一張完整的表，只是將表中的數(shù)據(jù)在物理上存放到多個(gè)表空間(物理文件上)，這樣查詢數(shù)據(jù)時(shí)，不至于每次都掃描整張表

2. 什么時(shí)候使用分區(qū)表：

1、表的大小超過(guò)2GB。

2、表中包含歷史數(shù)據(jù)，新的數(shù)據(jù)被增加到新的分區(qū)中。

3. 表分區(qū)的優(yōu)缺點(diǎn)

表分區(qū)有以下優(yōu)點(diǎn)：
1、改善查詢性能：對(duì)分區(qū)對(duì)象的查詢可以僅搜索自己關(guān)心的分區(qū)，提高檢索速度。
2、增強(qiáng)可用性：如果表的某個(gè)分區(qū)出現(xiàn)故障，表在其他分區(qū)的數(shù)據(jù)仍然可用；
3、維護(hù)方便：如果表的某個(gè)分區(qū)出現(xiàn)故障，需要修復(fù)數(shù)據(jù)，只修復(fù)該分區(qū)即可；
4、均衡I/O：可以把不同的分區(qū)映射到磁盤(pán)以平衡I/O，改善整個(gè)系統(tǒng)性能。
缺點(diǎn)：
分區(qū)表相關(guān)：已經(jīng)存在的表沒(méi)有方法可以直接轉(zhuǎn)化為分區(qū)表。不過(guò) Oracle 提供了在線重定義表的功能.

4. 表分區(qū)的操作三步走

4.1 創(chuàng)建分區(qū)函數(shù)

CREATE PARTITION FUNCTION xx1(int)

AS RANGE LEFT FOR VALUES (10000, 20000);

注釋：創(chuàng)建分區(qū)函數(shù)：myRangePF2，以INT類型分區(qū)，分三個(gè)區(qū)間，10000以內(nèi)在A 區(qū)，1W-2W在B區(qū)，2W以上在C區(qū).

4.2 創(chuàng)建分區(qū)架構(gòu)

CREATE PARTITION SCHEME myRangePS2

AS PARTITION xx1

TO (a, b, c);

注釋：在分區(qū)函數(shù)XX1上創(chuàng)建分區(qū)架構(gòu)：myRangePS2，分別為A,B,C三個(gè)區(qū)間

A,B,C分別為三個(gè)文件組的名稱，而且必須三個(gè)NDF隸屬于這三個(gè)組，文件所屬文件組一旦創(chuàng)建就不能修改

4.3 對(duì)表進(jìn)行分區(qū)

常用數(shù)據(jù)規(guī)范–數(shù)據(jù)空間類型修改為：分區(qū)方案，然后選擇分區(qū)方案名稱和分區(qū)列列表，結(jié)果如圖所示：

也可以用sql語(yǔ)句生成

CREATE TABLE [dbo].[AvCache](

[AVNote] [varchar](300) NULL,

[bb] [int] IDENTITY(1,1)

) ON [myRangePS2](bb); --注意這里使用[myRangePS2]架構(gòu)，根據(jù)bb分區(qū)

4.4 查詢表分區(qū)

SELECT *, $PARTITION.[myRangePF2](bb) FROM dbo.AVCache  

這樣就可以清楚的看到表數(shù)據(jù)是如何分區(qū)的了

4.5 創(chuàng)建索引分區(qū)

三、分布式數(shù)據(jù)庫(kù)設(shè)計(jì)

分布式數(shù)據(jù)庫(kù)系統(tǒng)是在集中式數(shù)據(jù)庫(kù)系統(tǒng)的基礎(chǔ)上發(fā)展起來(lái)的，理解起來(lái)也很簡(jiǎn)單，就是將整體的數(shù)據(jù)庫(kù)分開(kāi)，分布到各個(gè)地方，就其本質(zhì)而言，分布式數(shù)據(jù)庫(kù)系統(tǒng)分為兩種：1.數(shù)據(jù)在邏輯上是統(tǒng)一的，而在物理上卻是分散的，一個(gè)分布式數(shù)據(jù)庫(kù)在邏輯上是一個(gè)統(tǒng)一的整體，在物理上則是分別存儲(chǔ)在不同的物理節(jié)點(diǎn)上，我們通常說(shuō)的分布式數(shù)據(jù)庫(kù)都是這種2.邏輯是分布的，物理上也是分布的，這種也成聯(lián)邦式分布數(shù)據(jù)庫(kù)，由于組成聯(lián)邦的各個(gè)子數(shù)據(jù)庫(kù)系統(tǒng)是相對(duì)“自治”的，這種系統(tǒng)可以容納多種不同用途的、差異較大的數(shù)據(jù)庫(kù)，比較適宜于大范圍內(nèi)數(shù)據(jù)庫(kù)的集成。

分布式數(shù)據(jù)庫(kù)較為復(fù)雜，在此不作詳細(xì)的使用和說(shuō)明，只是舉例說(shuō)明一下，現(xiàn)在分布式數(shù)據(jù)庫(kù)多用于用戶分區(qū)性較強(qiáng)的系統(tǒng)中，如果一個(gè)全國(guó)連鎖店，一般設(shè)計(jì)為每個(gè)分店都有自己的銷售和庫(kù)存等信息，總部則需要有員工，供應(yīng)商，分店信息等數(shù)據(jù)庫(kù)，這類型的分店數(shù)據(jù)庫(kù)可以完全一致，很多系統(tǒng)也可能導(dǎo)致不一致，這樣，各個(gè)連鎖店數(shù)據(jù)存儲(chǔ)在本地，從而提高了影響速度，降低了通信費(fèi)用，而且數(shù)據(jù)分布在不同場(chǎng)地，且存有多個(gè)副本，即使個(gè)別場(chǎng)地發(fā)生故障，不致引起整個(gè)系統(tǒng)的癱瘓。
但是他也帶來(lái)很多問(wèn)題，如：數(shù)據(jù)一致性問(wèn)題、數(shù)據(jù)遠(yuǎn)程傳遞的實(shí)現(xiàn)、通信開(kāi)銷的降低等，這使得分布式數(shù)據(jù)庫(kù)系統(tǒng)的開(kāi)發(fā)變得較為復(fù)雜，只是讓大家明白其原理，具體的使用方式就不做詳細(xì)的介紹了。

四、整理數(shù)據(jù)庫(kù)碎片

如果你的表已經(jīng)創(chuàng)建好了索引，但性能卻仍然不好，那很可能是產(chǎn)生了索引碎片，你需要進(jìn)行索引碎片整理。

1. 什么是索引碎片?

由于表上有過(guò)度地插入、修改和刪除操作，索引頁(yè)被分成多塊就形成了索引碎片，如果索引碎片嚴(yán)重，那掃描索引的時(shí)間就會(huì)變長(zhǎng)，甚至導(dǎo)致索引不可用，因此數(shù)據(jù)檢索操作就慢下來(lái)了。

2. 如何知道是否發(fā)生了索引碎片?

在SQLServer數(shù)據(jù)庫(kù)，通過(guò)DBCC ShowContig或DBCC ShowContig(表名)檢查索引碎片情況，指導(dǎo)我們對(duì)其進(jìn)行定時(shí)重建整理。

通過(guò)對(duì)掃描密度（過(guò)低），掃描碎片（過(guò)高）的結(jié)果分析，判定是否需要索引重建，主要看如下兩個(gè)：
Scan Density [Best Count:Actual Count]-掃描密度［最佳值:實(shí)際值］：DBCC
SHOWCONTIG返回最有用的一個(gè)百分比。這是擴(kuò)展盤(pán)區(qū)的最佳值和實(shí)際值的比率。該百分比應(yīng)該盡可能靠近100％。低了則說(shuō)明有外部碎片。

Logical Scan Fragmentation-邏輯掃描碎片：無(wú)序頁(yè)的百分比。該百分比應(yīng)該在0％到10％之間，高了則說(shuō)明有外部碎片。

解決方式：

• 利用DBCC INDEXDEFRAG整理索引碎片

• 利用DBCC DBREINDEX重建索引。

兩者區(qū)別調(diào)用微軟的原話如下：

DBCC INDEXDEFRAG

命令是聯(lián)機(jī)操作，所以索引只有在該命令正在運(yùn)行時(shí)才可用，而且可以在不丟失已完成工作的情況下中斷該操作。這種方法的缺點(diǎn)是在重新組織數(shù)據(jù)方面沒(méi)有聚集索引的除去/重新創(chuàng)建操作有效。
重新創(chuàng)建聚集索引將對(duì)數(shù)據(jù)進(jìn)行重新組織，其結(jié)果是使數(shù)據(jù)頁(yè)填滿。填滿程度可以使用 FILLFACTOR
選項(xiàng)進(jìn)行配置。這種方法的缺點(diǎn)是索引在除去/重新創(chuàng)建周期內(nèi)為脫機(jī)狀態(tài)，并且操作屬原子級(jí)。如果中斷索引創(chuàng)建，則不會(huì)重新創(chuàng)建該索引。也就是說(shuō)，要想獲得好的效果，還是得用重建索引，所以決定重建索引。

五、數(shù)據(jù)庫(kù)表優(yōu)化

1. 設(shè)計(jì)規(guī)范化表，消除數(shù)據(jù)冗余

數(shù)據(jù)庫(kù)范式是確保數(shù)據(jù)庫(kù)結(jié)構(gòu)合理，滿足各種查詢需要、避免數(shù)據(jù)庫(kù)操作異常的數(shù)據(jù)庫(kù)設(shè)計(jì)方式。滿足范式要求的表，稱為規(guī)范化表，范式產(chǎn)生于20世紀(jì)70年代初，一般表設(shè)計(jì)滿足前三范式就可以，在這里簡(jiǎn)單介紹一下前三范式

先給大家看一下百度百科給出的定義：

第一范式（1NF）無(wú)重復(fù)的列

所謂第一范式（1NF）是指在關(guān)系模型中，對(duì)域添加的一個(gè)規(guī)范要求，所有的域都應(yīng)該是原子性的，即數(shù)據(jù)庫(kù)表的每一列都是不可分割的原子數(shù)據(jù)項(xiàng)，而不能是集合，數(shù)組，記錄等非原子數(shù)據(jù)項(xiàng)。

第二范式（2NF）屬性

在1NF的基礎(chǔ)上，非碼屬性必須完全依賴于碼[在1NF基礎(chǔ)上消除非主屬性對(duì)主碼的部分函數(shù)依賴]

第三范式（3NF）屬性

在1NF基礎(chǔ)上，任何非主屬性不依賴于其它非主屬性[在2NF基礎(chǔ)上消除傳遞依賴]

通俗的給大家解釋一下（可能不是最科學(xué)、最準(zhǔn)確的理解）

第一范式：屬性(字段)的原子性約束，要求屬性具有原子性，不可再分割；

第二范式：記錄的惟一性約束，要求記錄有惟一標(biāo)識(shí)，每條記錄需要有一個(gè)屬性來(lái)做為實(shí)體的唯一標(biāo)識(shí)。

第三范式：屬性(字段)冗余性的約束，即任何字段不能由其他字段派生出來(lái)，在通俗點(diǎn)就是：主鍵沒(méi)有直接關(guān)系的數(shù)據(jù)列必須消除(消除的辦法就是再創(chuàng)建一個(gè)表來(lái)存放他們，當(dāng)然外鍵除外)

如果數(shù)據(jù)庫(kù)設(shè)計(jì)達(dá)到了完全的標(biāo)準(zhǔn)化，則把所有的表通過(guò)關(guān)鍵字連接在一起時(shí)，不會(huì)出現(xiàn)任何數(shù)據(jù)的復(fù)本(repetition)。標(biāo)準(zhǔn)化的優(yōu)點(diǎn)是明顯的，它避免了數(shù)據(jù)冗余，自然就節(jié)省了空間，也對(duì)數(shù)據(jù)的一致性(consistency)提供了根本的保障，杜絕了數(shù)據(jù)不一致的現(xiàn)象，同時(shí)也提高了效率。

2. 適當(dāng)?shù)娜哂?，增加?jì)算列

數(shù)據(jù)庫(kù)設(shè)計(jì)的實(shí)用原則是：在數(shù)據(jù)冗余和處理速度之間找到合適的平衡點(diǎn)

滿足范式的表一定是規(guī)范化的表，但不一定是最佳的設(shè)計(jì)。很多情況下會(huì)為了提高數(shù)據(jù)庫(kù)的運(yùn)行效率，常常需要降低范式標(biāo)準(zhǔn)：適當(dāng)增加冗余，達(dá)到以空間換時(shí)間的目的。比如我們有一個(gè)表，產(chǎn)品名稱，單價(jià)，庫(kù)存量，總價(jià)值。這個(gè)表是不滿足第三范式的，因?yàn)椤翱們r(jià)值”可以由“單價(jià)”乘以“數(shù)量”得到，說(shuō)明“金額”是冗余字段。但是，增加“總價(jià)值”這個(gè)冗余字段，可以提高查詢統(tǒng)計(jì)的速度，這就是以空間換時(shí)間的作法。合理的冗余可以分散數(shù)據(jù)量大的表的并發(fā)壓力，也可以加快特殊查詢的速度，冗余字段可以有效減少數(shù)據(jù)庫(kù)表的連接，提高效率。

其中"總價(jià)值"就是一個(gè)計(jì)算列，在數(shù)據(jù)庫(kù)中有兩種類型：數(shù)據(jù)列和計(jì)算列，數(shù)據(jù)列就是需要我們手動(dòng)或者程序給予賦值的列，計(jì)算列是源于表中其他的數(shù)據(jù)計(jì)算得來(lái)，比如這里的"總價(jià)值"

在SQL中創(chuàng)建計(jì)算列：

create table table1  
(  
	number decimal(18,4),  
	price money,  
	Amount as number*price --這里就是計(jì)算列  
)

也可以再表設(shè)計(jì)中，直接手動(dòng)添加或修改列屬性即可：如下圖

是否持久性，我們也需要注意：

如果是’否’，說(shuō)明這列是虛擬列，每次查詢的時(shí)候計(jì)算一次，而且那么它是不可以用來(lái)做check,foreign key或not null約束。

如果是’是’,就是真實(shí)的列，不需要每次都計(jì)算，可以再此列上創(chuàng)建索引等等。

3. 索引

索引是一個(gè)表優(yōu)化的重要指標(biāo)，在表優(yōu)化中占有極其重要的成分，所以將單獨(dú)寫(xiě)一章”SQL索引一步到位“去告訴大家如何建立和優(yōu)化索引

4. 主鍵和外鍵的必要性

主鍵與外鍵的設(shè)計(jì)，在全局?jǐn)?shù)據(jù)庫(kù)的設(shè)計(jì)中，占有重要地位。 因?yàn)椋褐麈I是實(shí)體的抽象，主鍵與外鍵的配對(duì)，表示實(shí)體之間的連接。

主鍵：根據(jù)第二范式，需要有一個(gè)字段去標(biāo)識(shí)這條記錄，主鍵無(wú)疑是最好的標(biāo)識(shí)，但是很多表也不一定需要主鍵，但是對(duì)于數(shù)據(jù)量大，查詢頻繁的數(shù)據(jù)庫(kù)表，一定要有主鍵，主鍵可以增加效率、防止重復(fù)等優(yōu)點(diǎn)。

主鍵的選擇也比較重要，一般選擇總的長(zhǎng)度小的鍵，小的鍵的比較速度快，同時(shí)小的鍵可以使主鍵的B樹(shù)結(jié)構(gòu)的層次更少。
主鍵的選擇還要注意組合主鍵的字段次序，對(duì)于組合主鍵來(lái)說(shuō)，不同的字段次序的主鍵的性能差別可能會(huì)很大，一般應(yīng)該選擇重復(fù)率低、單獨(dú)或者組合查詢可能性大的字段放在前面。

外鍵：外鍵作為數(shù)據(jù)庫(kù)對(duì)象，很多人認(rèn)為麻煩而不用，實(shí)際上，外鍵在大部分情況下是很有用的，理由是：外鍵是最高效的一致性維護(hù)方法

數(shù)據(jù)庫(kù)的一致性要求，依次可以用外鍵、CHECK約束、規(guī)則約束、觸發(fā)器、客戶端程序，一般認(rèn)為，離數(shù)據(jù)越近的方法效率越高。
謹(jǐn)慎使用級(jí)聯(lián)刪除和級(jí)聯(lián)更新，級(jí)聯(lián)刪除和級(jí)聯(lián)更新作為SQL SERVER
2000當(dāng)年的新功能，在2005作了保留，應(yīng)該有其可用之處。我這里說(shuō)的謹(jǐn)慎，是因?yàn)榧?jí)聯(lián)刪除和級(jí)聯(lián)更新有些突破了傳統(tǒng)的關(guān)于外鍵的定義，功能有點(diǎn)太過(guò)強(qiáng)大，使用前必須確定自己已經(jīng)把握好其功能范圍，否則，級(jí)聯(lián)刪除和級(jí)聯(lián)更新可能讓你的數(shù)據(jù)莫名其妙的被修改或者丟失。從性能看級(jí)聯(lián)刪除和級(jí)聯(lián)更新是比其他方法更高效的方法。

5. 存儲(chǔ)過(guò)程、視圖、函數(shù)的適當(dāng)使用

很多人習(xí)慣將復(fù)雜操作都放在應(yīng)用程序?qū)?，但如果你要?yōu)化數(shù)據(jù)訪問(wèn)性能，將SQL代碼移植到數(shù)據(jù)庫(kù)上(使用存儲(chǔ)過(guò)程，視圖，函數(shù)和觸發(fā)器)也是一個(gè)很大的改進(jìn)原因如下：

• 存儲(chǔ)過(guò)程減少了網(wǎng)絡(luò)傳輸、處理及存儲(chǔ)的工作量，且經(jīng)過(guò)編譯和優(yōu)化，執(zhí)行速度快，易于維護(hù)，且表的結(jié)構(gòu)改變時(shí)，不影響客戶端的應(yīng)用程序

• 使用存儲(chǔ)過(guò)程，視圖，函數(shù)有助于減少應(yīng)用程序中SQL復(fù)制的弊端，因?yàn)楝F(xiàn)在只在一個(gè)地方集中處理SQL

• 使用數(shù)據(jù)庫(kù)對(duì)象實(shí)現(xiàn)所有的TSQL有助于分析TSQL的性能問(wèn)題，同時(shí)有助于你集中管理TSQL代碼，更好的重構(gòu)TSQL代碼

6. 傳說(shuō)中的‘三少原則’

• 數(shù)據(jù)庫(kù)的表越少越好

• 表的字段越少越好

• 字段中的組合主鍵、組合索引越少越好

當(dāng)然這里的少是相對(duì)的，是減少數(shù)據(jù)冗余的重要設(shè)計(jì)理念。

7. 分割你的表，減小表尺寸

如果你發(fā)現(xiàn)某個(gè)表的記錄太多，例如超過(guò)一千萬(wàn)條，則要對(duì)該表進(jìn)行水平分割。水平分割的做法是，以該表主鍵的某個(gè)值為界線，將該表的記錄水平分割為兩個(gè)表。

如果你若發(fā)現(xiàn)某個(gè)表的字段太多，例如超過(guò)八十個(gè)，則垂直分割該表，將原來(lái)的一個(gè)表分解為兩個(gè)表

8. 字段設(shè)計(jì)原則

字段是數(shù)據(jù)庫(kù)最基本的單位，其設(shè)計(jì)對(duì)性能的影響是很大的。需要注意如下：

• 數(shù)據(jù)類型盡量用數(shù)字型，數(shù)字型的比較比字符型的快很多。

• 數(shù)據(jù)類型盡量小，這里的盡量小是指在滿足可以預(yù)見(jiàn)的未來(lái)需求的前提下的。

• 盡量不要允許NULL，除非必要，可以用NOT NULL+DEFAULT代替。

• 少用TEXT和IMAGE，二進(jìn)制字段的讀寫(xiě)是比較慢的，而且，讀取的方法也不多，大部分情況下最好不用。

• 自增字段要慎用，不利于數(shù)據(jù)遷移

六、程序操作優(yōu)化

1. 操作符優(yōu)化

1. IN、NOT IN 操作符

IN和EXISTS 性能有外表和內(nèi)表區(qū)分的，但是在大數(shù)據(jù)量的表中推薦用EXISTS 代替IN 。

Not IN 不走索引的是絕對(duì)不能用的，可以用NOT EXISTS 代替

1.2 IS NULL 或IS NOT NULL操作

索引是不索引空值的，所以這樣的操作不能使用索引，可以用其他的辦法處理，例如：數(shù)字類型，判斷大于0，字符串類型設(shè)置一個(gè)默認(rèn)值，判斷是否等于默認(rèn)值即可

1.3 <> 操作符（不等于）

不等于操作符是永遠(yuǎn)不會(huì)用到索引的，因此對(duì)它的處理只會(huì)產(chǎn)生全表掃描。 用其它相同功能的操作運(yùn)算代替，如 a<>0 改為 a>0 or a<0 a<>’’ 改為 a>’’

1.4 用全文搜索搜索文本數(shù)據(jù)，取代like搜索

全文搜索始終優(yōu)于like搜索：

• 全文搜索讓你可以實(shí)現(xiàn)like不能完成的復(fù)雜搜索，如搜索一個(gè)單詞或一個(gè)短語(yǔ)，搜索一個(gè)與另一個(gè)單詞或短語(yǔ)相近的單詞或短語(yǔ)，或者是搜索同義詞;

• 實(shí)現(xiàn)全文搜索比實(shí)現(xiàn)like搜索更容易(特別是復(fù)雜的搜索);

2. SQL語(yǔ)句優(yōu)化

2.1 在查詢中不要使用 select

為什么不能使用，地球人都知道，但是很多人都習(xí)慣這樣用，要明白能省就省，而且這樣查詢數(shù)據(jù)庫(kù)不能利用“覆蓋索引”了

2.2 盡量寫(xiě)WHERE子句

盡量不要寫(xiě)沒(méi)有WHERE的SQL語(yǔ)句

2.3 注意SELECT INTO后的WHERE子句

因?yàn)镾ELECT INTO把數(shù)據(jù)插入到臨時(shí)表，這個(gè)過(guò)程會(huì)鎖定一些系統(tǒng)表，如果這個(gè)WHERE子句返回的數(shù)據(jù)過(guò)多或者速度太慢，會(huì)造成系統(tǒng)表長(zhǎng)期鎖定，諸塞其他進(jìn)程。

2.4 對(duì)于聚合查詢，可以用HAVING子句進(jìn)一步限定返回的行

** 避免使用臨時(shí)表**

• 除非卻有需要，否則應(yīng)盡量避免使用臨時(shí)表，相反，可以使用表變量代替;

• 大多數(shù)時(shí)候(99%)，表變量駐扎在內(nèi)存中，因此速度比臨時(shí)表更快，臨時(shí)表駐扎在TempDb數(shù)據(jù)庫(kù)中，因此臨時(shí)表上的操作需要跨數(shù)據(jù)庫(kù)通信，速度自然慢。

2.5 減少訪問(wèn)數(shù)據(jù)庫(kù)的次數(shù)

程序設(shè)計(jì)中最好將一些常用的全局變量表放在內(nèi)存中或者用其他的方式減少數(shù)據(jù)庫(kù)的訪問(wèn)次數(shù)

2.6 盡量少做重復(fù)的工作

盡量減少無(wú)效工作，但是這一點(diǎn)的側(cè)重點(diǎn)在客戶端程序，需要注意的如下：

• 控制同一語(yǔ)句的多次執(zhí)行，特別是一些基礎(chǔ)數(shù)據(jù)的多次執(zhí)行是很多程序員很少注意的

• 減少多次的數(shù)據(jù)轉(zhuǎn)換，也許需要數(shù)據(jù)轉(zhuǎn)換是設(shè)計(jì)的問(wèn)題，但是減少次數(shù)是程序員可以做到的。

• 杜絕不必要的子查詢和連接表，子查詢?cè)趫?zhí)行計(jì)劃一般解釋成外連接，多余的連接表帶來(lái)額外的開(kāi)銷。

• 合并對(duì)同一表同一條件的多次UPDATE，比如

  UPDATE EMPLOYEE SET FNAME=’HAIWER’ WHERE EMP_ID=’ VPA30890F’  
  UPDATE EMPLOYEE SET LNAME=’YANG’ WHERE EMP_ID=’ VPA30890F’  
  

  這兩個(gè)語(yǔ)句應(yīng)該合并成以下一個(gè)語(yǔ)句

  UPDATE EMPLOYEE SET FNAME=’HAIWER’,LNAME=’YANG’  
  WHERE EMP_ID=’ VPA30890F’
  

• UPDATE操作不要拆成DELETE操作+INSERT操作的形式，雖然功能相同，但是性能差別是很大的。

• 不要寫(xiě)一些沒(méi)有意義的查詢，比如

  SELECT * FROM EMPLOYEE WHERE 1=2
  

3. where使用原則

3.1 在下面兩條select語(yǔ)句中:

select * from table1 where field1<=10000 and field1>=0;  
select * from table1 where field1>=0 and field1<=10000;  

如果數(shù)據(jù)表中的數(shù)據(jù)field1都>=0,則第一條select語(yǔ)句要比第二條select語(yǔ)句效率高的多，因?yàn)榈诙lselect語(yǔ)句的第一個(gè)條件耗費(fèi)了大量的系統(tǒng)資源。
第一個(gè)原則：在where子句中應(yīng)把最具限制性的條件放在最前面。

3.2 在下面的select語(yǔ)句中:

select * from tab where a=… and b=… and c=…;  

若有索引index(a,b,c)，則where子句中字段的順序應(yīng)和索引中字段順序一致。
第二個(gè)原則：where子句中字段的順序應(yīng)和索引中字段順序一致。

3.3 以下假設(shè)在field1上有唯一索引I1，在field2上有非唯一索引I2。

select field3,field4 from tb where field1=‘sdf’ 快  
select * from tb where field1=‘sdf’ 慢，  

因?yàn)楹笳咴谒饕龗呙韬笠嘁徊絉OWID表訪問(wèn)。

select field3,field4 from tb where field1>=‘sdf’ 快  
select field3,field4 from tb where field1>‘sdf’ 慢  

因?yàn)榍罢呖梢匝杆俣ㄎ凰饕?/p>

select field3,field4 from tb where field2 like ‘R%’ 快  
select field3,field4 from tb where field2 like ‘%R’ 慢，  

因?yàn)楹笳卟皇褂盟饕?mark hidden color="red">文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-619404.html

3.4 使用函數(shù)如：

select field3,field4 from tb where upper(field2)='RMN’	--不使用索引 

如果一個(gè)表有兩萬(wàn)條記錄，建議不使用函數(shù)；如果一個(gè)表有五萬(wàn)條以上記錄，嚴(yán)格禁止使用函數(shù)！兩萬(wàn)條記錄以下沒(méi)有限制。文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-619404.html

到了這里，關(guān)于數(shù)據(jù)庫(kù)優(yōu)化(數(shù)據(jù)庫(kù)自身的優(yōu)化，數(shù)據(jù)庫(kù)表優(yōu)化，程序操作優(yōu)化)的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！