DB知識(shí)點(diǎn)記錄

分頁(yè)

SqlServer：ROW_NUMBER () over (ORDER BY ID) AS RN,

MySql：limit

Oracle：ROWNUM AS RN

數(shù)據(jù)表的基本結(jié)構(gòu)

一個(gè)新表被創(chuàng)建之時(shí)，會(huì)在磁盤(pán)中分配一段以8K為單位的連續(xù)空間，當(dāng)字段的值從內(nèi)存寫(xiě)入磁盤(pán)時(shí)，就在這空間隨機(jī)保存，當(dāng)8K用完會(huì)自動(dòng)分配另一個(gè)8K的空間。

這里8K稱為一個(gè)數(shù)據(jù)頁(yè)，并分配0-7的頁(yè)號(hào)，每個(gè)文件的第0頁(yè)記錄引導(dǎo)信息，叫文件頭；每8個(gè)數(shù)據(jù)頁(yè)（64K）的組合形成擴(kuò)展區(qū)，稱為擴(kuò)展。全數(shù)據(jù)頁(yè)的組成形成堆；

SQL規(guī)定行不能跨越數(shù)據(jù)頁(yè)，每行記錄最大數(shù)據(jù)量只能是8K。所以char和varchar這兩種類型容量要限制在8K以內(nèi)，存儲(chǔ)超過(guò)8K的數(shù)據(jù)應(yīng)使用text類型，text類型的字段不能直接錄入和保存，只是存儲(chǔ)一個(gè)指針，指向若干的8K的文本數(shù)據(jù)頁(yè)所組成的擴(kuò)展區(qū)。

當(dāng)一個(gè)擴(kuò)展區(qū)的8個(gè)數(shù)據(jù)頁(yè)中既包含了空間頁(yè)面又包含了數(shù)據(jù)或索引頁(yè)面時(shí)，稱為混合擴(kuò)展，每張表都以混合擴(kuò)展開(kāi)始；反之稱為一致擴(kuò)展，專門(mén)保存數(shù)據(jù)及索引信息。

表被創(chuàng)建時(shí)，SQL在混合擴(kuò)展中分配至少一個(gè)數(shù)據(jù)頁(yè)，隨著數(shù)量增長(zhǎng)，可以在混合擴(kuò)展中分配7個(gè)頁(yè)，當(dāng)數(shù)據(jù)超過(guò)8個(gè)頁(yè)面時(shí)，則從一致擴(kuò)展中分配數(shù)據(jù)頁(yè)面。

空間頁(yè)面專門(mén)負(fù)責(zé)數(shù)據(jù)空間的分配和管理。PFS頁(yè)面：記錄一個(gè)頁(yè)面是否已分配，還有多少可用空間等信息。GAM頁(yè)面和SGAM頁(yè)面：用來(lái)記錄空閑的擴(kuò)展或含有空閑頁(yè)面的混合擴(kuò)展位置。

數(shù)據(jù)頁(yè)或索引頁(yè)則專門(mén)保存數(shù)據(jù)及索引信息，使用4種類型的數(shù)據(jù)頁(yè)來(lái)管理表或索引，分別為：IAM頁(yè)、數(shù)據(jù)頁(yè)、文本/圖像頁(yè)和索引頁(yè)

頁(yè)分裂

一半的數(shù)據(jù)將保存在老頁(yè)面、另一半將存放入新頁(yè)面，且新頁(yè)面可能被分配到任何可用的頁(yè)。所以、頻繁頁(yè)分裂后果很?chē)?yán)重，將使物理表產(chǎn)生大量數(shù)據(jù)碎片、導(dǎo)致直接造成I/O效率的急劇下降。

填充因子

索引的一個(gè)特性，定義所以每頁(yè)上的可用空間量。FILLFACTOR（填充因子）適應(yīng)以后表數(shù)據(jù)的擴(kuò)展并減少了頁(yè)拆分的可能性。填充因子是從0到100的百分比數(shù)值，設(shè)為100時(shí)表示將數(shù)據(jù)頁(yè)填滿。只有當(dāng)不會(huì)對(duì)數(shù)據(jù)進(jìn)行更改時(shí)才用此設(shè)置。值越小則數(shù)據(jù)頁(yè)上的空閑空間越大，這樣可以減少在索引增長(zhǎng)過(guò)程中進(jìn)行頁(yè)分裂的需要，但是需要占用更多的磁盤(pán)空間。

索引

查看索引：SHOW INDEX FROM tableName;

查看數(shù)據(jù)庫(kù)索引：select * from information_schema.STATISTICS where INDEX_NAME != 'PRIMARY' and TABLE_SCHEMA = 'bank'

刪除索引：ALTER TABLE tableName DROP INDEX ID;

添加索引：ALTER TABLE tableName ADD INDEX (ID);

索引通常是用來(lái)加快數(shù)據(jù)處理速度的最普遍采用的優(yōu)化方式。數(shù)據(jù)庫(kù)的索引相當(dāng)于書(shū)的目錄，可以快速找到你想要的信息。

索引的利弊：查詢執(zhí)行的大部分開(kāi)銷(xiāo)是I/O，使用索引提高性能的主要目標(biāo)是避免全表掃描，因?yàn)槿頀呙栊枰獜拇疟P(pán)上讀取表的每一個(gè)數(shù)據(jù)頁(yè)，如果有索引指向，則查詢只需要讀少數(shù)次的磁盤(pán)就可以了。帶索引的表需要在數(shù)據(jù)庫(kù)中占用更多的存儲(chǔ)空間，會(huì)增加增刪數(shù)據(jù)的命令運(yùn)行時(shí)長(zhǎng)。

SQL SERVER中有多種索引類型

按存儲(chǔ)結(jié)構(gòu)區(qū)分：聚集索引、分聚集索引

按數(shù)據(jù)唯一性區(qū)分：唯一索引、非唯一索引

按鍵列個(gè)數(shù)區(qū)分：單列索引、多列索引

MySql所有有以下幾種

普通索引、唯一索引、全文索引、單列索引、多列索引

聚集索引

一種對(duì)磁盤(pán)上實(shí)際數(shù)據(jù)重新組織以按指定的一列或多列值排序。像我們用到的漢語(yǔ)字典，就是一個(gè)聚集索引，比如查張，就需要翻到后面。然后根據(jù)字母順序查找，這里用到微軟的平衡二叉樹(shù)算法。

聚集索引是給數(shù)據(jù)排序，一個(gè)表只能建立一個(gè)聚集索引，它的性能比其他索引更快。

非聚集索引

SqlServer默認(rèn)情況下建立的索引是非聚集索引，不重新組織表中的數(shù)據(jù)，而是對(duì)每一行存儲(chǔ)索引列值并用一個(gè)指針指向數(shù)據(jù)所在的頁(yè)面。即便對(duì)數(shù)據(jù)不排序，他擁有的目錄更像是目錄，對(duì)查取數(shù)據(jù)的效率也是具有提升空間的，而不需要全表掃描。

索引設(shè)計(jì)原則

對(duì)于一張表來(lái)說(shuō)索引的有無(wú)和建立什么樣的索引，要取決于where字句和Join表達(dá)式

一般來(lái)說(shuō)建立索引的原則包括以下內(nèi)容

• 系統(tǒng)一般會(huì)給逐漸字段自動(dòng)建立聚集索引
• 有大量重復(fù)值且經(jīng)常有范圍查詢和排序、分組的列，或者經(jīng)常頻繁訪問(wèn)的列，考慮建立聚集索引
• 經(jīng)常做插入操作的表中建立索引，應(yīng)使用填充因子來(lái)減少頁(yè)分裂，同時(shí)提高并發(fā)度降低死鎖的發(fā)生。
• 在選擇索引鍵時(shí)，應(yīng)采用小數(shù)據(jù)類型的列作為鍵以使每個(gè)索引能容納盡可能多的索引建和指針

事務(wù)隔離

數(shù)據(jù)庫(kù)中的事務(wù)是數(shù)據(jù)庫(kù)并發(fā)控制的基本單位，一條或一組語(yǔ)句要么全部成功，對(duì)數(shù)據(jù)庫(kù)的某些數(shù)據(jù)成功修改，要么全部不成功，數(shù)據(jù)的數(shù)據(jù)還原到這些語(yǔ)句執(zhí)行。

數(shù)據(jù)庫(kù)中事務(wù)的ACID原則

原子性(Atomicity)：事務(wù)的原子性指一個(gè)事務(wù)中包含的一條語(yǔ)句或多條語(yǔ)句構(gòu)成了一個(gè)完整的邏輯單元，這個(gè)邏輯單元具有不可再分的原子性。要么一起提交全部執(zhí)行完成、要么一起提交全部執(zhí)行失敗。

一致性(Consistency)：可以理解為數(shù)據(jù)的完整性，事務(wù)的提交要確保在數(shù)據(jù)庫(kù)上的操作沒(méi)有破壞數(shù)據(jù)的完整性，比如不要違背一些約束的數(shù)據(jù)插入或修改行為。一旦破壞了數(shù)據(jù)完整性，會(huì)回滾這個(gè)事務(wù)來(lái)確保數(shù)據(jù)庫(kù)中的數(shù)據(jù)是一致的。

隔離性(Isolation)：與數(shù)據(jù)庫(kù)的事務(wù)隔離級(jí)別以及鎖相關(guān)，多個(gè)用戶可以對(duì)同一數(shù)據(jù)并發(fā)訪問(wèn)而又不破壞數(shù)據(jù)的正確性和完整性。但是并行事務(wù)的修改必須與其他并行事務(wù)的修改相互獨(dú)立，隔離。但是在不同的隔離級(jí)別下，事務(wù)的讀取操作可能得到的結(jié)果是不同的。

持久性(Durability)：數(shù)據(jù)持久化，事務(wù)一旦對(duì)數(shù)據(jù)的操作完成并提交后，數(shù)據(jù)修改就已經(jīng)完成，即使服務(wù)重啟這些數(shù)據(jù)也不會(huì)改變。如果在事務(wù)的執(zhí)行過(guò)程中，系統(tǒng)服務(wù)崩潰或者重啟，那么事務(wù)所有的操作就會(huì)被回滾，回到事務(wù)操作之前的狀態(tài)。

常見(jiàn)事務(wù)

自動(dòng)提交事務(wù)

SqlServer默認(rèn)的一種事務(wù)模式，每條Sql語(yǔ)句都被看成一個(gè)事務(wù)進(jìn)行處理。

顯式事務(wù)

由Begin Transaction開(kāi)啟事務(wù)開(kāi)始，由Commit Transaction提交事務(wù)、Rollback Transcation回滾事務(wù)結(jié)束，Save Transaction事務(wù)內(nèi)部設(shè)置保存點(diǎn)，事務(wù)可以不全部回滾只回滾到這里

---開(kāi)啟事務(wù) begin tran --錯(cuò)誤撲捉機(jī)制，看好啦，這里也有的。并且可以嵌套。 begin try --語(yǔ)句正確insert into lives (Eat,Play,Numb) values ('豬肉','足球',1)--Numb為int類型，出錯(cuò)insert into lives (Eat,Play,Numb) values ('豬肉','足球','abc') end try begin catchselect Error_number() as ErrorNumber, --錯(cuò)誤代碼Error_severity() as ErrorSeverity, --錯(cuò)誤嚴(yán)重級(jí)別，級(jí)別小于10 try catch 捕獲不到Error_state() as ErrorState , --錯(cuò)誤狀態(tài)碼Error_Procedure() as ErrorProcedure , --出現(xiàn)錯(cuò)誤的存儲(chǔ)過(guò)程或觸發(fā)器的名稱。Error_line() as ErrorLine, --發(fā)生錯(cuò)誤的行號(hào)Error_message() as ErrorMessage --錯(cuò)誤的具體信息if(@@trancount>0) --全局變量@@trancount，事務(wù)開(kāi)啟此值+1，他用來(lái)判斷是有開(kāi)啟事務(wù)rollback tran ---由于出錯(cuò)，這里回滾到開(kāi)始，第一條語(yǔ)句也沒(méi)有插入成功。 end catch if(@@trancount>0) commit tran --如果成功Lives表中，將會(huì)有3條數(shù)據(jù)。--表本身為空表，ID ,Numb為int 類型，其它為nvarchar類型 select * from lives

set xact_abort

設(shè)置xact_abort on/off，指定是否回滾當(dāng)前事務(wù)，為on時(shí)如果當(dāng)前sql出錯(cuò)，回滾整個(gè)事務(wù)，為off時(shí)如果sql出錯(cuò)回滾當(dāng)前sql語(yǔ)句，其他語(yǔ)句照常運(yùn)行讀寫(xiě)數(shù)據(jù)庫(kù)

set xact_abort off

等待5秒：waitfor delay '0:0:5'?

事務(wù)中常見(jiàn)的問(wèn)題

SqlServer中多用戶并發(fā)的情況下，使用事務(wù)可能會(huì)遇到一些情況

臟讀(Dirty Reads)：一個(gè)事務(wù)正在訪問(wèn)并修改數(shù)據(jù)庫(kù)中的數(shù)據(jù)但是沒(méi)有提交，另一個(gè)事務(wù)可能讀取到這些已經(jīng)做出修改但未提交的數(shù)據(jù)。可能導(dǎo)致的結(jié)果就是所有操作都有可能回滾，比如第一個(gè)事務(wù)對(duì)數(shù)據(jù)做出的修改可能違背了數(shù)據(jù)表的某些約束，破壞了完整性，但是恰巧第二個(gè)事務(wù)卻讀取到了這些不正確的數(shù)據(jù)造成它自身操作也發(fā)生失敗回滾。

不可重復(fù)讀取(Non-Repeatable Reads)：A事務(wù)兩次讀取同一數(shù)據(jù)，B事務(wù)也讀取這同一數(shù)據(jù)，但是A事務(wù)在第二次讀取前B事務(wù)已經(jīng)更新了這一數(shù)據(jù)。對(duì)于A事務(wù)來(lái)說(shuō)，第一次和第二次讀取到這一數(shù)據(jù)可能就不一致了。

幻讀(Phantom Reads)：與不可重復(fù)讀有點(diǎn)類似，都是兩次讀取，不同的是A事務(wù)第一次操作的比如說(shuō)是全表的數(shù)據(jù)，此時(shí)B事務(wù)并不是只修改某一具體數(shù)據(jù)而是插入了一條新數(shù)據(jù)，然后A事務(wù)第二次讀取這全表的時(shí)候就發(fā)現(xiàn)比上一次多了一條數(shù)據(jù)，發(fā)生幻覺(jué)了。

更新丟失(Lost Update)：兩個(gè)事務(wù)同時(shí)更新，但由于某一個(gè)事務(wù)更新失敗發(fā)生回滾操作，這樣有可能的結(jié)果就是第二個(gè)事務(wù)已更新的數(shù)據(jù)因?yàn)榈谝粋€(gè)事務(wù)發(fā)生回滾而導(dǎo)致數(shù)據(jù)最終沒(méi)有發(fā)生更新，因此兩個(gè)事務(wù)的更新都失敗了。

事務(wù)隔離級(jí)別

希望通過(guò)何種方式讓并發(fā)的事務(wù)隔離開(kāi)來(lái)，隔離到什么程度。比如可以容忍臟讀、或者不希望并發(fā)的事務(wù)出現(xiàn)臟讀的情況，那么這些可以通過(guò)隔離界別的設(shè)置使得并發(fā)事務(wù)之間的隔離程度變得寬松或者很?chē)?yán)峻。

隔離級(jí)別越高，讀取臟數(shù)據(jù)或者造成數(shù)據(jù)不統(tǒng)一不完整的機(jī)會(huì)就越少，但是在高并發(fā)的系統(tǒng)中性能降低就越嚴(yán)重。隔離級(jí)別越低，并發(fā)系統(tǒng)中性能上提升很大，但是數(shù)據(jù)本身可能不完整。

SqlServer 2012中可以通過(guò)下面的語(yǔ)法來(lái)設(shè)置事務(wù)的隔離級(jí)別（從低到高排列）

SET TRANSACTION ISOLATION LEVEL{ READ UNCOMMITTED| READ COMMITTED| REPEATABLE READ| SNAPSHOT| SERIALIZABLE} [ ; ]

Read Uncommitted（未提交讀）

隔離級(jí)別最低，容易產(chǎn)生的問(wèn)題就是臟讀，因?yàn)榭梢宰x取其他事務(wù)修改了但沒(méi)有提交的數(shù)據(jù)。它的作用跟在事務(wù)中SELECT語(yǔ)句對(duì)象表上設(shè)置（NOLOCK）相同

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED DBCC USEROPTIONS

Read Committed（已提交讀）

SqlServer的默認(rèn)設(shè)置，已提交讀，可以避免臟讀，可以滿足大多數(shù)要求。事務(wù)中的語(yǔ)句不能讀取已由其他事務(wù)做出修改但是還未提交的數(shù)據(jù)，但是能讀取由其他事務(wù)做出修改并提交了的數(shù)據(jù)。有可能出現(xiàn)不可重復(fù)讀和幻讀的情況，因?yàn)楫?dāng)前事務(wù)中可能出現(xiàn)兩次讀取同一資源，但是兩次讀取的過(guò)程之間，另外一個(gè)事務(wù)可能對(duì)這一資源完成了讀取更新并提交的行為，這樣數(shù)據(jù)前后可能就不一致了。

Repeatable Read（可重復(fù)讀）

不能讀取已由其他事務(wù)修改了但是未提交的行，其他任何事務(wù)也不能修改在當(dāng)前事務(wù)完成之前由當(dāng)前事務(wù)讀取的數(shù)據(jù)。但是對(duì)于其他事務(wù)插入的新行數(shù)據(jù)，當(dāng)事務(wù)第二次訪問(wèn)表行時(shí)會(huì)檢索這一新行。因此這個(gè)隔離級(jí)別的設(shè)置解決了不可重復(fù)讀取的問(wèn)題，但是避免不了幻讀。

SNAPSHOT（快照隔離）

可以解決幻讀的問(wèn)題，當(dāng)前事務(wù)中讀取的數(shù)據(jù)在整個(gè)事務(wù)開(kāi)始到事務(wù)提交結(jié)束之間，這個(gè)數(shù)據(jù)版本是一致的。其他的事務(wù)可能對(duì)這些數(shù)據(jù)做出修改，但是對(duì)于當(dāng)前事務(wù)來(lái)說(shuō)它是看不到這些變化。有點(diǎn)類似于當(dāng)前事務(wù)拿到這個(gè)數(shù)據(jù)的時(shí)候是拿到這個(gè)數(shù)據(jù)的快照，因此在這個(gè)快照上做出的操作同一事務(wù)中前后幾次操作都是基于同一數(shù)據(jù)版本。因此這個(gè)隔離界別可以解決幻讀問(wèn)題。但是要注意的是，其他事務(wù)是可以在當(dāng)前事務(wù)完成之前修改由當(dāng)前事務(wù)讀取的數(shù)據(jù)。

默認(rèn)情況下數(shù)據(jù)庫(kù)不允許設(shè)置SNAPSHOT隔離界別，直接設(shè)置會(huì)出現(xiàn)錯(cuò)誤。

需要使用SET命令開(kāi)啟這個(gè)支持

ALTER DATABASE BIWORK_SSIS SET ALLOW_SNAPSHOT_ISOLATION ON

SERIALIZABLE（序列化）

性能最低，隔離界別最高最嚴(yán)格，可以幾乎上面提到的所有問(wèn)題。比如不能讀取其他已由其它事務(wù)修改但是沒(méi)有提交的數(shù)據(jù)，不允許其他事務(wù)在當(dāng)前事務(wù)完成修改之前修改由當(dāng)前事務(wù)讀取的數(shù)據(jù)，不允許其他事務(wù)在當(dāng)前事務(wù)完成修改之前插入新的行。作用于在事務(wù)內(nèi)所有SELECT語(yǔ)句中的所有表上設(shè)置HOLDLOCK相同，并發(fā)級(jí)別比較低但又對(duì)安全性要求比較高的時(shí)候可以考慮使用。如果并發(fā)級(jí)別很高，使用這個(gè)隔離級(jí)別，性能瓶頸將非常嚴(yán)重。

數(shù)據(jù)庫(kù)鎖定

加鎖是SqlServer數(shù)據(jù)庫(kù)引擎用來(lái)同步多個(gè)用戶同時(shí)對(duì)同一個(gè)數(shù)據(jù)塊的訪問(wèn)的一種機(jī)制。

當(dāng)有事務(wù)操作時(shí)，數(shù)據(jù)庫(kù)引擎會(huì)要求不同類型的鎖定，當(dāng)鎖定運(yùn)行時(shí)，會(huì)阻止其他事務(wù)對(duì)已經(jīng)鎖定的數(shù)據(jù)行、數(shù)據(jù)頁(yè)或數(shù)據(jù)表進(jìn)行操作。只有在當(dāng)時(shí)事務(wù)對(duì)于自己鎖定的資源不在需要時(shí)，才會(huì)釋放其鎖定的資源，供其他事務(wù)使用。

鎖的種類與范圍如下

共享鎖（S）

用于不更改或不更新數(shù)據(jù)的讀取操作，如SELECT語(yǔ)句。

允許并發(fā)事務(wù)在封閉式并發(fā)控制下讀取SELECT資源。

當(dāng)查詢某條記錄時(shí)，SQL SERVER會(huì)嘗試取得該條記錄上存在的共享鎖。如無(wú)法獲取，就必須等待別人釋放對(duì)該記錄中某幾種與共享鎖互斥的鎖，才能在設(shè)置共享鎖之后，獲取該條記錄。

當(dāng)需要查詢某條記錄時(shí)，會(huì)在該記錄上防止共享鎖，而其他人在查詢此記錄時(shí)，因?yàn)楣蚕礞i彼此不互斥，所以可以再次放置共享鎖。如果有人要更新此記錄，因?yàn)楠?dú)占鎖與共享鎖互斥，所以無(wú)法放置獨(dú)占鎖，要等到所有讀取此記錄的人都讀取完畢，釋放了共享鎖，更新數(shù)據(jù)的人才能對(duì)該記錄設(shè)置獨(dú)占鎖，并進(jìn)一步更新數(shù)據(jù)。默認(rèn)的事務(wù)隔離級(jí)別下，當(dāng)數(shù)據(jù)讀取完畢，SqlServer會(huì)釋放共享鎖

更新鎖（U）

用于可更新的資源中。防止當(dāng)多個(gè)會(huì)話在讀取、鎖定以及隨后可能進(jìn)行的資源更新時(shí)發(fā)生常見(jiàn)形式的死鎖

更新鎖是一種中繼鎖，當(dāng)同一項(xiàng)資源從原來(lái)的查詢操作轉(zhuǎn)換為更新操作時(shí)，鎖定機(jī)制會(huì)從共享鎖變?yōu)楦骆i，進(jìn)一步變成獨(dú)占鎖。

在可重復(fù)讀或可序列化事務(wù)中，此事務(wù)讀取數(shù)據(jù)，然后修改數(shù)據(jù)。如果兩個(gè)事務(wù)獲得資源上的共享鎖，然后試圖同時(shí)更新數(shù)據(jù)，則一個(gè)事務(wù)嘗試將鎖轉(zhuǎn)換為獨(dú)占鎖。共享模式到獨(dú)占鎖的轉(zhuǎn)換必須等待一段時(shí)間，因?yàn)橐粋€(gè)事務(wù)的獨(dú)占鎖與其他事務(wù)的共享鎖不兼容，發(fā)生鎖等待。第二個(gè)事務(wù)試圖獲取獨(dú)占鎖以進(jìn)行更新，由于兩個(gè)事務(wù)都要轉(zhuǎn)換為獨(dú)占鎖，并且每個(gè)事務(wù)都等待另一個(gè)事務(wù)釋放共享鎖，因此發(fā)生死鎖。

若避免這種死鎖問(wèn)題，請(qǐng)使用更新鎖。一次只有一個(gè)事務(wù)可以獲得資源的更新鎖，如果事務(wù)修改資源，則更新鎖轉(zhuǎn)換為獨(dú)占鎖。

當(dāng)查詢一條記錄后，將次內(nèi)容更新，一定會(huì)先查找記錄，在查找的過(guò)程中會(huì)對(duì)相關(guān)的記錄放置共享鎖，等找到相應(yīng)的記錄之后，會(huì)對(duì)記錄放置更新鎖，以避免發(fā)生死鎖。因?yàn)楣蚕礞i與更新鎖并不互斥，如果兩個(gè)人同時(shí)對(duì)同一條記錄放置共享鎖，先進(jìn)行更新的人，可以在別人放置共享鎖時(shí)，繼續(xù)放置更新鎖，因?yàn)楦骆i互斥，當(dāng)另一個(gè)人想再放置更新鎖時(shí)，無(wú)法設(shè)置，進(jìn)入停止等待狀態(tài)。

獨(dú)占鎖/排他鎖（X）

用于數(shù)據(jù)修改操作，例如INSERT、UPDATE、DELETE。確保不會(huì)同時(shí)對(duì)同一資源進(jìn)行多重更新

可以防止并發(fā)事務(wù)對(duì)資源進(jìn)行訪問(wèn)。使用獨(dú)占鎖時(shí)時(shí)，任何其他事務(wù)都無(wú)法修改數(shù)據(jù)。僅在使用NOLOCK提示或未提交讀隔離級(jí)別時(shí)才會(huì)進(jìn)行讀取操作。

對(duì)數(shù)據(jù)進(jìn)行添加、修改、刪除操作時(shí)，語(yǔ)句在執(zhí)行所需的操作之前首先執(zhí)行讀取操作以獲取數(shù)據(jù)。因此需先對(duì)所在的資源放置獨(dú)占鎖，以確保以上操作未完成時(shí)不受到干擾，獨(dú)占鎖在開(kāi)啟事務(wù)之后，一直保留到事務(wù)結(jié)束。

UPDATE語(yǔ)句可能根據(jù)與一個(gè)表的連接修改另一個(gè)表中的行，再次情況下，除了請(qǐng)求更新行上的獨(dú)占鎖之外，UPDATE還將請(qǐng)求在連接表中讀取的行上的共享鎖

意向鎖

用于建立鎖的層次結(jié)構(gòu)。意向鎖包含三種類型：意向共享（IS）、意向排他（IX）、意向排他共享（SIX）

在記錄上放置共享鎖之前，需要對(duì)存放該記錄的更大范圍上設(shè)置意向鎖，以避免其他連接對(duì)該頁(yè)放置獨(dú)占鎖。

數(shù)據(jù)庫(kù)引擎使用意向鎖來(lái)保護(hù)共享鎖（S）或獨(dú)占鎖（X）放置在鎖層次結(jié)構(gòu)的底層資源上。意向鎖在較低級(jí)別鎖前可以獲取它們，因此會(huì)通知將意向鎖放置在較低級(jí)別上。

意向鎖有兩種用途：

• 防止其他事務(wù)以會(huì)使較低級(jí)別的鎖無(wú)效的方式修改較高級(jí)別的資源。
• 提高數(shù)據(jù)庫(kù)引擎在較高的粒度級(jí)別檢測(cè)鎖沖突的效率

在該表的數(shù)據(jù)頁(yè)或數(shù)據(jù)行上請(qǐng)求共享鎖之前，在表級(jí)請(qǐng)求共享意向鎖，以防止另一個(gè)事務(wù)隨后在包含那一頁(yè)的表上嘗試放置獨(dú)占鎖。意向鎖可以提高性能，因?yàn)閿?shù)據(jù)庫(kù)引擎僅在表級(jí)別檢查意向鎖來(lái)確定事務(wù)是否可以安全地獲取該表上的鎖。而不需要檢查表中的每行或每頁(yè)上的鎖以確定事務(wù)是否可以鎖定整個(gè)表

意向鎖包括意向共享（IS）、意向排他（IX）、意向排他共享（SIX）等等

意向共享 （IS）

保護(hù)針對(duì)層次結(jié)構(gòu)中某些低層資源請(qǐng)求或獲取的共享鎖

意向獨(dú)占（IX）

保護(hù)針對(duì)層次結(jié)構(gòu)中某些（而并非所有）低層資源請(qǐng)求或獲取的獨(dú)占鎖。 IX 是 IS 的超集，它也保護(hù)針對(duì)低層級(jí)別資源請(qǐng)求的共享鎖。

意向獨(dú)占共享（SIX）?

保護(hù)針對(duì)層次結(jié)構(gòu)中某些（而并非所有）低層資源請(qǐng)求或獲取的共享鎖以及針對(duì)某些（而并非所有）低層資源請(qǐng)求或獲取的意向獨(dú)占鎖。 頂級(jí)資源允許使用并發(fā) IS 鎖。 例如，獲取表上的 SIX 鎖也將獲取正在修改的頁(yè)上的意向獨(dú)占鎖以及修改的行上的獨(dú)占鎖。 雖然每個(gè)資源在一段時(shí)間內(nèi)只能有一個(gè) SIX 鎖，以防止其他事務(wù)對(duì)資源進(jìn)行更新，但是其他事務(wù)可以通過(guò)獲取表級(jí)的 IS 鎖來(lái)讀取層次結(jié)構(gòu)中的低層資源。

意向更新（IU）

保護(hù)針對(duì)層次結(jié)構(gòu)中所有低層資源請(qǐng)求或獲取的更新鎖。 僅在頁(yè)資源上使用 IU 鎖。 如果進(jìn)行了更新操作，IU 鎖將轉(zhuǎn)換為 IX 鎖。

共享意向更新（SIU）

S 鎖和 IU 鎖的組合，作為分別獲取這些鎖并且同時(shí)持有兩種鎖的結(jié)果。 例如，事務(wù)執(zhí)行帶有 PAGLOCK 提示的查詢，然后執(zhí)行更新操作。 帶有 PAGLOCK 提示的查詢將獲取 S 鎖，更新操作將獲取 IU 鎖。

更新意向排他（UIX）

U?鎖和?IX?鎖的組合，作為分別獲取這些鎖并且同時(shí)持有兩種鎖的結(jié)果。

架構(gòu)鎖

在執(zhí)行依賴于表架構(gòu)的操作時(shí)使用。架構(gòu)鎖包含兩種類型：架構(gòu)修改（Sch-M）和架構(gòu)穩(wěn)定性（Sch-S）

數(shù)據(jù)庫(kù)引擎在表數(shù)據(jù)定義語(yǔ)言（DDL）操作的過(guò)程中使用架構(gòu)修改鎖。保持該鎖期間，Sch-M鎖將阻止對(duì)表進(jìn)行并發(fā)訪問(wèn)。

數(shù)據(jù)庫(kù)引擎在編譯和執(zhí)行查詢時(shí)使用架構(gòu)穩(wěn)定性鎖，不會(huì)阻止某些事務(wù)鎖，其中包括排他鎖。因此在編譯查詢的過(guò)程中，其他事務(wù)將繼續(xù)運(yùn)行。但是無(wú)法針對(duì)表執(zhí)行獲取Sch-M鎖的并發(fā)DDL操作和并發(fā)DML操作

大容量更新鎖（BU）

在向表進(jìn)行大容量數(shù)據(jù)復(fù)制且制定了TABLOCK提示時(shí)使用

數(shù)據(jù)庫(kù)引擎在將數(shù)據(jù)大容量復(fù)制到表中時(shí)，指定TABLOCK提示或使用sp_tableoption選項(xiàng)，則是使用大容量更新鎖。大容量更新鎖允許多個(gè)線程將數(shù)據(jù)并發(fā)地大容量加載到同一表，以降低數(shù)據(jù)表的鎖定競(jìng)爭(zhēng)，同時(shí)防止其他不進(jìn)行大容量加載數(shù)據(jù)的進(jìn)程訪問(wèn)該表。

鍵范圍鎖

當(dāng)使用可序列化事務(wù)隔離級(jí)別時(shí)保護(hù)查詢讀取的行的范圍。確保再次運(yùn)行查詢時(shí)其他事務(wù)無(wú)法插入符合可序列化事務(wù)的查詢的行。

在使用可序列化事務(wù)隔離界別時(shí)，保護(hù)用戶對(duì)查詢時(shí)所讀取的數(shù)據(jù)行范圍，以確保其他事務(wù)無(wú)法插入受"鍵范圍鎖"保護(hù)的數(shù)據(jù)行。鍵范圍鎖放置在索引上，指定開(kāi)始于結(jié)束的索引鍵值。這些操作會(huì)先在索引上獲取鎖定，此種鎖定可以封鎖任何嘗試插入、修改、刪除索引鍵值在"鍵范圍鎖"中的數(shù)據(jù)行。?

SQL優(yōu)化

查看執(zhí)行時(shí)間和CPU占用時(shí)間

set statistics time on SELECT * FROM MSTB_ACCL_ABRS_SHOP_INFO; set statistics time off

消息里面就可以查看執(zhí)行時(shí)間了

查看查詢對(duì)I/O的操作情況

set statistics io on SELECT * FROM MSTB_ACCL_ABRS_SHOP_INFO; set statistics io off

掃描計(jì)數(shù)：索引或表掃描次數(shù)

邏輯讀取：數(shù)據(jù)緩存中讀取的頁(yè)數(shù)

物理讀取：從磁盤(pán)中讀取的頁(yè)數(shù)

預(yù)讀：查詢過(guò)程中，從磁盤(pán)放入緩存的頁(yè)數(shù)

lob邏輯讀取：從數(shù)據(jù)緩存中讀取，image，text，ntext或大型數(shù)據(jù)的頁(yè)數(shù)

lob物理讀取：從磁盤(pán)中讀取，image，text，ntext或大型數(shù)據(jù)的頁(yè)數(shù)

lob預(yù)讀：查詢過(guò)程中，從磁盤(pán)放入緩存的image，text，ntext或大型數(shù)據(jù)的頁(yè)數(shù)

如果物理讀取和預(yù)讀次數(shù)比較多，可以使用索引優(yōu)化

查詢->查詢選擇->高級(jí)，勾選SET STATISTICS TIME可以不用在SQL中指定

查看執(zhí)行計(jì)劃

執(zhí)行右側(cè)有顯示執(zhí)行計(jì)劃，會(huì)顯示此步驟執(zhí)行的詳細(xì)內(nèi)容。表下面都顯示一個(gè)開(kāi)銷(xiāo)百分比

select查詢優(yōu)化

保證不查詢多余的列與行。

慎用distinct關(guān)鍵字，只在查詢一個(gè)字段或者很少字段的情況下使用

慎用union關(guān)鍵字，主要功能是把各個(gè)查詢語(yǔ)句的結(jié)果集合并到一個(gè)結(jié)果集中返回給你。會(huì)依次執(zhí)行select語(yǔ)句-->合并結(jié)果集-->對(duì)結(jié)果集進(jìn)行排序。效率很低，應(yīng)避免使用

判斷表中是否存在：select top(1) id from product

insert查詢優(yōu)化

盡量使用insert into select批量插入，明顯提升效率

SqlServer表分區(qū)

一般情況下，建立數(shù)據(jù)庫(kù)時(shí)，表數(shù)據(jù)都存放在一個(gè)文件里。但是如果是分區(qū)表的話，表數(shù)據(jù)就會(huì)按照你指定的規(guī)則分放到不同的文件里，把一個(gè)大的數(shù)據(jù)文件拆分成多個(gè)小文件，還可以把這些小文件放在不同的磁盤(pán)下由多個(gè)cpu進(jìn)行處理。這樣文件的大小隨著拆分而減少，還得到硬件系統(tǒng)的加強(qiáng)，自然對(duì)我們操作數(shù)據(jù)是大大有利的。

所以大數(shù)據(jù)量的數(shù)據(jù)表，對(duì)分區(qū)的需要還是必要的，因?yàn)榭梢蕴岣遱elect效率，還可以對(duì)歷史數(shù)據(jù)進(jìn)行區(qū)分存檔。表分區(qū)會(huì)對(duì)數(shù)據(jù)庫(kù)產(chǎn)生不必要的開(kāi)銷(xiāo)，除了性能會(huì)增加實(shí)現(xiàn)對(duì)象的管理費(fèi)用和復(fù)雜性。

分區(qū)是要把一個(gè)表數(shù)據(jù)拆分為若干子集合，把一個(gè)數(shù)據(jù)文件拆分到多個(gè)數(shù)據(jù)文件中，然而這些文件的存放可以依托一個(gè)文件組或者多個(gè)文件組，多個(gè)文件組可以提高數(shù)據(jù)庫(kù)的訪問(wèn)并發(fā)量，還可以把不同的分區(qū)配置到不同的磁盤(pán)中提高效率，創(chuàng)建時(shí)建議分區(qū)跟文件組個(gè)數(shù)相同。

創(chuàng)建文件組

alter database 數(shù)據(jù)庫(kù)名 add filegroup 文件組名

創(chuàng)建數(shù)據(jù)文件到文件組里面

alter database 數(shù)據(jù)庫(kù)名 add file 數(shù)據(jù)標(biāo)識(shí) to filegroup 文件組名

<數(shù)據(jù)標(biāo)識(shí)> （name:文件名，fliename:物理路徑文件名，size:文件初始大小kb/mb/gb/tb，filegrowth:文件自動(dòng)增量kb/mb/gb/tb/%,maxsize:文件可以增加到的最大大小kb/mb/gb/tb/unlimited）

alter database AmwayFrameworkWorkflow add file (name=N'ById1',filename=N'D:\數(shù)據(jù)庫(kù)\ById1.ndf',size=5Mb,filegrowth=5mb) to filegroup AGroup1; alter database AmwayFrameworkWorkflow add file (name=N'ById2',filename=N'D:\數(shù)據(jù)庫(kù)\ById2.ndf',size=5Mb,filegrowth=5mb) to filegroup AGroup2; alter database AmwayFrameworkWorkflow add file (name=N'ById3',filename=N'D:\數(shù)據(jù)庫(kù)\ById3.ndf',size=5Mb,filegrowth=5mb) to filegroup AGroup3; alter database AmwayFrameworkWorkflow add file (name=N'ById4',filename=N'D:\數(shù)據(jù)庫(kù)\ById4.ndf',size=5Mb,filegrowth=5mb) to filegroup AGroup4; alter database AmwayFrameworkWorkflow add file (name=N'ById5',filename=N'D:\數(shù)據(jù)庫(kù)\ById5.ndf',size=5Mb,filegrowth=5mb) to filegroup AGroup5;

?

可以右鍵數(shù)據(jù)庫(kù)->屬性->文件組中查找

使用向?qū)?chuàng)建分區(qū)表?

右鍵要分區(qū)的表->存儲(chǔ)->創(chuàng)建分區(qū)->顯示向?qū)б晥D?

會(huì)讓你選擇可用分區(qū)列，如果是int類型，那么你可以指定1-100W是一個(gè)分區(qū)，100W-200W是另一個(gè)分區(qū)。

加入你選擇的是datatime，那么可以指定日期作為分區(qū)。

?

?

?

?

?

?

?

?

?

?

?

?

http://www.cnblogs.com/knowledgesea/p/3696912.html

SqlServer游標(biāo)

http://www.cnblogs.com/knowledgesea/p/3699851.html

?

￥

轉(zhuǎn)載于:https://www.cnblogs.com/chenxygx/p/9294326.html

總結(jié)

以上是生活随笔為你收集整理的DB知识点记录的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。