優(yōu)化SQLServer數(shù)據(jù)庫(kù)的一些經(jīng)驗(yàn)和注意事項(xiàng)，詳細(xì)介紹了SQL語(yǔ)句優(yōu)化的基本原則，包括索引、查詢和游標(biāo)的使用等。下面由學(xué)習(xí)啦小編為大家整理的SQL數(shù)據(jù)庫(kù)優(yōu)化方式，希望大家喜歡!

　　SQL數(shù)據(jù)庫(kù)優(yōu)化的方式

　　1. 利用表分區(qū)

　　分區(qū)將數(shù)據(jù)在物理上分隔開(kāi)，不同分區(qū)的數(shù)據(jù)可以制定保存在處于不同磁盤上的數(shù)據(jù)文件里。這樣，當(dāng)對(duì)這個(gè)表進(jìn)行查詢時(shí)，只需要在表分區(qū)中進(jìn)行掃描，而不必進(jìn)行全表掃描，明顯縮短了查詢時(shí)間，另外處于不同磁盤的分區(qū)也將對(duì)這個(gè)表的數(shù)據(jù)傳輸分散在不同的磁盤I/O，一個(gè)精心設(shè)置的分區(qū)可以將數(shù)據(jù)傳輸對(duì)磁盤I/O競(jìng)爭(zhēng)均勻地分散開(kāi)。對(duì)數(shù)據(jù)量大的時(shí)時(shí)表可采取此方法。可按月自動(dòng)建表分區(qū)。

　　2. 別名的使用

　　別名是大型數(shù)據(jù)庫(kù)的應(yīng)用技巧，就是表名、列名在查詢中以一個(gè)字母為別名，查詢速度要比建連接表快1.5倍。

　　3. 索引Index的優(yōu)化設(shè)計(jì)

　　索引可以大大加快數(shù)據(jù)庫(kù)的查詢速度。但是并不是所有的表都需要建立索引，只針對(duì)大數(shù)據(jù)量的表建立索引就好。

　　缺點(diǎn)：

　　1.創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間，這種時(shí)間隨著數(shù)據(jù)量的增加而增加。

　　2.索引需要占物理空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個(gè)索引還要占一定的物理空間，如果要建立聚簇索引，那么需要的空間就會(huì)更大。

　　3.當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候，索引也要?jiǎng)討B(tài)的維護(hù)，這樣就降低了數(shù)據(jù)的維護(hù)速度。

　　索引需要維護(hù)：為了維護(hù)系統(tǒng)性能，索引在創(chuàng)建之后，由于頻繁地對(duì)數(shù)據(jù)進(jìn)行增加、刪除、修改等操作使得索引頁(yè)發(fā)生碎塊，因此，必須對(duì)索引進(jìn)行維護(hù)。

　　4. 物化視圖(索引視圖)

　　一般的視圖是虛擬的，而物化視圖是實(shí)實(shí)在在的數(shù)據(jù)區(qū)域，是要占據(jù)存儲(chǔ)空間的，另外系統(tǒng)刷新物化視圖也需要耗費(fèi)一定的資源，但是它卻換來(lái)了效率和靈活性。

　　索引視圖更適合在OLAP(讀取較多，更新較少)的數(shù)據(jù)庫(kù)中使用，不適合在OLTP(記錄即時(shí)的增、刪、改、查)的數(shù)據(jù)庫(kù)中使用 。

　　物化視圖的注意事項(xiàng)：

　　1.對(duì)于復(fù)雜而高消耗的查詢，如果使用頻繁，應(yīng)建成物化視圖。

　　2.物化視圖是一種典型的以空間換時(shí)間的性能優(yōu)化方式。

　　3.對(duì)于更新頻繁的表慎用物化視圖。

　　4.選擇合適的刷新方式。

　　普通視圖和物化視圖的區(qū)別：

　　普通視圖和物化視圖根本就不是一個(gè)東西，普通視圖是不存儲(chǔ)任何數(shù)據(jù)的，在查詢中是轉(zhuǎn)換為對(duì)應(yīng)定義的SQL去查詢，而物化視圖是將數(shù)據(jù)轉(zhuǎn)換為一個(gè)表，實(shí)際存儲(chǔ)著數(shù)據(jù)，這樣查詢數(shù)據(jù)，就不用關(guān)聯(lián)一大堆表，如果表很大的話，會(huì)在臨時(shí)表空間內(nèi)做大量的操作。

　　普通視圖的三個(gè)特征：

　　1).簡(jiǎn)化設(shè)計(jì)，方便，清晰編碼。視圖并不是提高性能的，它的存在只會(huì)降低性能(例如我們關(guān)聯(lián)兩個(gè)視圖，一個(gè)視圖關(guān)聯(lián)6個(gè)表，另一個(gè)視圖關(guān)聯(lián)7個(gè)表)。

　　2).安全，在授權(quán)給其他用戶或者查看角度，多個(gè)表關(guān)聯(lián)只允許查看，不允許修改。

　　3.從不同的角度看不同的維度，視圖可以劃分維度和權(quán)限，并使多個(gè)維度的綜合，也就是你要什么就可以從不同的角度看，而表是一個(gè)實(shí)體的而已，一般維度較少。

　　5. 死鎖與阻塞

　　1).對(duì)于需要頻繁更新的數(shù)據(jù)，盡量避免放在長(zhǎng)事務(wù)中，以免導(dǎo)致連鎖反應(yīng)。

　　2).不是迫不得已，最好不要在數(shù)據(jù)庫(kù)鎖機(jī)制外再加自己設(shè)計(jì)的鎖。

　　3).減少事務(wù)大小，及時(shí)提交事務(wù)。

　　4).盡量避免跨數(shù)據(jù)庫(kù)的分布式事務(wù)，因?yàn)榄h(huán)境的復(fù)雜性，很容易導(dǎo)致阻塞。

　　5).慎用位圖索引，更新時(shí)容易導(dǎo)致死鎖。

　　6.減少IO與網(wǎng)絡(luò)傳輸次數(shù)

　　1).盡量用較少的數(shù)據(jù)庫(kù)請(qǐng)求，獲取到需要的數(shù)據(jù)，能一次性取出的不分多次取出。

　　2).對(duì)于頻繁操作數(shù)據(jù)庫(kù)的批量操作，應(yīng)采用存儲(chǔ)過(guò)程，減少不必要的網(wǎng)絡(luò)傳輸。

　　sql內(nèi)存釋放的方式

　　一、這些內(nèi)存一般都是SqlServer運(yùn)行時(shí)候用作緩存

　　例如你運(yùn)行一個(gè)select語(yǔ)句

　　那么Sql Server會(huì)將相關(guān)的數(shù)據(jù)頁(yè)(Sql Server操作的數(shù)據(jù)都是以頁(yè)為單位的)加載到內(nèi)存中來(lái)，

　　下一次如果再次請(qǐng)求此頁(yè)的數(shù)據(jù)的時(shí)候，就無(wú)需讀取磁盤了，大大提高了速度。這類的緩存叫做數(shù)據(jù)緩存。

　　還有一些其他類型的緩存，如執(zhí)行存儲(chǔ)過(guò)程時(shí)，Sql Server需要先編譯再運(yùn)行，編譯后的結(jié)果也會(huì)緩存起來(lái)，

　　下一次就無(wú)需再次編譯了。如果這些緩存已經(jīng)不需要了，那么我們可以調(diào)用以下幾個(gè)DBCC管理命令來(lái)清理這些緩存：

　　DBCC FREEPROCCACHE

　　DBCC FREESESSIONCACHE

　　DBCC FREESYSTEMCACHE('All')

　　DBCC DROPCLEANBUFFERS

　　這幾個(gè)命令分別用來(lái)清除存儲(chǔ)過(guò)程相關(guān)的緩存、會(huì)話緩存、系統(tǒng)緩存以及所有所有緩存

　　但是需要注意的是，這幾個(gè)命令雖然會(huì)清除掉現(xiàn)有緩存，為新的緩存騰地方，

　　但是Sql server并不會(huì)因此釋放掉已經(jīng)占用的內(nèi)存。無(wú)奈的是，Sql Server

　　并沒(méi)有提供任何命令允許我們釋放不用到的內(nèi)存。因此我們只能通過(guò)動(dòng)態(tài)調(diào)整

　　Sql Server可用的物理內(nèi)存設(shè)置來(lái)強(qiáng)迫它釋放內(nèi)存。

　　我們也可以通過(guò)Sql Server Management企業(yè)管理器進(jìn)行動(dòng)態(tài)控制。

　　連接到企業(yè)管理器之后打開(kāi)Sql Server實(shí)例的屬性面板，

　　找到內(nèi)存設(shè)置，改變其中的最大服務(wù)器內(nèi)存使用即可

　　二、--內(nèi)存使用情況

　　SELECT * FROM sys.dm_os_performance_counters

　　WHERE counter_name IN ('Target Server Memory (KB)','Total Server Memory (KB)')

　　三、-- 內(nèi)存狀態(tài)

　　DBCC MemoryStatus

　　四、--查看最小最大內(nèi)存

　　SELECT

　　cfg.name AS [Name],

　　cfg.configuration_id AS [Number],

　　cfg.minimum AS [Minimum],

　　cfg.maximum AS [Maximum],

　　cfg.is_dynamic AS [Dynamic],

　　cfg.is_advanced AS [Advanced],

　　cfg.value AS [ConfigValue],

　　cfg.value_in_use AS [RunValue],

　　cfg.description AS [Description]

　　FROM

　　sys.configurations AS cfg

　　五、--設(shè)置最小最大內(nèi)存

　　sp_configure 'show advanced options', 1

　　go

　　sp_configure 'min server memory', 0

　　RECONFIGURE

　　GO

　　sp_configure 'max server memory', 2147483647

　　RECONFIGURE

　　GO

　　sp_configure 'max server memory', 256

　　RECONFIGURE

　　GO

　　sp_configure 'show advanced options', 0

　　-----------------------------------------------------------------------------------------------

　　CREATE proc [dbo].reclaimmemory --強(qiáng)制釋放內(nèi)存

　　as

　　begin

　　DBCC FREEPROCCACHE

　　DBCC FREESESSIONCACHE

　　DBCC FREESYSTEMCACHE('All')

　　DBCC DROPCLEANBUFFERS

　　exec sp_configure 'max server memory', 256

　　EXEC ('RECONFIGURE' )

　　WAITFOR DELAY '00:00:05'

　　EXEC sp_configure 'max server memory', 2147483647

　　EXEC ('RECONFIGURE' )

　　GO

　　end

　　--使用示例

　　/*

　　reclaimmemory

　　*/

　　Mysql存儲(chǔ)引擎是什么

　　一、存儲(chǔ)引擎的介紹

　　關(guān)系型數(shù)據(jù)庫(kù)表是用來(lái)存儲(chǔ)和組織信息的數(shù)據(jù)結(jié)構(gòu)，可以將表理解為由行和列組成的表格。

　　由于表的類型不同，我們?cè)趯?shí)際開(kāi)發(fā)過(guò)程中，就有可能需要各種各樣的表，不同的表就意味著存儲(chǔ)不同類型的數(shù)據(jù)，數(shù)據(jù)的處理上也會(huì)存在差異

　　對(duì)于Mysql來(lái)說(shuō)，它提供了很多種類型的存儲(chǔ)引擎

　　存儲(chǔ)引擎說(shuō)白了就是如何存儲(chǔ)數(shù)據(jù)，如何為存儲(chǔ)的數(shù)據(jù)建立索引和如何更新、查詢數(shù)據(jù)等技術(shù)的實(shí)現(xiàn)方法。

　　因?yàn)樵陉P(guān)系型數(shù)據(jù)庫(kù)中數(shù)據(jù)的存儲(chǔ)是以表的形式存儲(chǔ) 的，所以存儲(chǔ)引擎也可以稱為表類型(即存儲(chǔ)和操作此表的類型)

　　二、Mysql中的存儲(chǔ)引擎

　　MyISAM

　　這種引擎是mysql最早提供的，這種引擎又可以分為靜態(tài)MyISAM、動(dòng)態(tài)MyISAM和壓縮MyISAM三種

　　靜態(tài)MyISAM:

　　如果數(shù)據(jù)表中的各數(shù)據(jù)列的長(zhǎng)度都是預(yù)先固定好的，服務(wù)器將自動(dòng)選擇這種表類型。

　　因?yàn)閿?shù)據(jù)表中的每一條記錄所占用的空間都是一樣的，所以這種表存取和更新的效率非常高。

　　當(dāng)數(shù)據(jù)受損時(shí)，恢復(fù)工作也比較容易做

　　動(dòng)態(tài)MyISAM:

　　如果數(shù)據(jù)表中出現(xiàn)varchar,xxxtext或xxxxBLOB字段時(shí)，服務(wù)器將自動(dòng)選擇這種表類型 。

　　相對(duì)于靜態(tài)MyISAM,這種表存儲(chǔ)空間比較小，但由于每條記錄的長(zhǎng)度不一，所以多次修改數(shù)據(jù)后，數(shù)據(jù)表中的數(shù)據(jù)就可能離散的存儲(chǔ)在內(nèi)存中，進(jìn)而導(dǎo)致執(zhí)行效率下降.

　　同時(shí)內(nèi)存中也可能會(huì)出現(xiàn)很多碎片。

　　因此這種類型的表要經(jīng)常 用optimize table命令 或優(yōu)化工具來(lái)進(jìn)行碎片整理

　　壓縮MyISAM:

　　以上說(shuō)的兩種類型的表都可以用myisamchk工具壓縮。

　　這種類型的表進(jìn)一步減少了占用的存儲(chǔ)。

　　但是這種表壓縮之后不能再被修改。

　　另外，因?yàn)槭菈嚎s數(shù)據(jù)，所以這種表在讀取的時(shí)候要先執(zhí)行解壓縮

　　但是，不管是哪種MyISAM表，目前都不支持事務(wù)，行級(jí)鎖和外鍵約束的功能

　　MyISAM表示獨(dú)立于操作系統(tǒng)的，這說(shuō)明可以輕松的將其從windows服務(wù)器轉(zhuǎn)移 到linux服務(wù)器;

　　每當(dāng)我們建立一個(gè)MyISAM引擎的表時(shí)，就會(huì)在本地磁盤上建立三個(gè)文件，文件名就是表名。

　　例如：我建立一個(gè)MyISAM引擎的th_Demo表，那么就會(huì)生成以下三個(gè)文件:

　　1.th_demo.frm:存儲(chǔ)表定義;

　　2.th_demo.MYD:存儲(chǔ)數(shù)據(jù);

　　3.th_demo.MYI:存儲(chǔ)索引;

　　MyISAM存儲(chǔ)引擎特別適合在以下幾種情況下使用:

　　1.選擇密集型的表，MYISAM存儲(chǔ)引擎在篩選大量數(shù)據(jù)時(shí)非常迅速，這是它最突出的優(yōu)點(diǎn)

　　2.插入密集型的表，MYISAM的并發(fā)插入特性允許同時(shí)選擇和插入數(shù)據(jù)。例如：MYISAM存儲(chǔ)引擎很適合管理郵件或web服務(wù)器日志數(shù)據(jù)

　　InnoDB

　　InnoDB表類型可以看作是對(duì)MyISAM的進(jìn)一步更新產(chǎn)品，它提供了事務(wù)、行級(jí)鎖機(jī)制和外鍵約束的功能

　　InnoDB是一個(gè)健壯的事務(wù)型存儲(chǔ)引擎

　　使用場(chǎng)景：

　　1.更新密集的表。InnoDB存儲(chǔ)引擎特別適合處理多重并發(fā)的更新請(qǐng)求

　　2.事務(wù).InnoDB存儲(chǔ)引擎是支持事務(wù)的標(biāo)準(zhǔn)mysql存儲(chǔ)引擎

　　3.自動(dòng)災(zāi)難恢復(fù)。與其他存儲(chǔ)引擎不同，InnoDB表能夠自動(dòng)從災(zāi)難中恢復(fù)

　　4.外鍵約束。mysql支持外鍵的存儲(chǔ)引擎只有InnoDB

　　5.支持自動(dòng)增加列AUTO_INCREMENT屬性

　　一般來(lái)說(shuō)，如果需要事務(wù)支持，并且有較高的并發(fā)讀取頻率，InnoDB是不錯(cuò)的選擇

　　memory(heap)

　　使用Mysql Memory存儲(chǔ)引擎的出發(fā)點(diǎn)是速度，為了得到最快的響應(yīng)時(shí)間

　　采用的邏輯存儲(chǔ)介質(zhì)是系統(tǒng)內(nèi)存。

　　它要求存儲(chǔ)在Memory數(shù)據(jù)表里的數(shù)據(jù)使用的是長(zhǎng)度不變的格式，這意味著不能使用BLOB和Text這樣的長(zhǎng)度可變的數(shù)據(jù)類型

　　VARCHAR是一種長(zhǎng)度可變的類型，但因?yàn)樗贛ysql內(nèi)部當(dāng)作長(zhǎng)度固定不變的CHAR類型，所以可以使用

　　Memory同時(shí)支持散列索引和B樹(shù)索引。B樹(shù)索引優(yōu)于散列索引的是:

　　可以使用部分查詢和通配查詢，也可以使用<,>和>=等操作符方便數(shù)據(jù)挖掘

　　散列索引進(jìn)行“相等比較”非?？?，但是對(duì)"范圍比較"的速度就慢多了

　　散列索引值適合使用在=和<>的操作符，不適合<或>操作符中，也同樣不適合在order by子句中

　　三、一般在以下幾種情況下使用Memory存儲(chǔ)引擎:

　　1.目標(biāo)數(shù)據(jù)較小，而且被非常頻繁地訪問(wèn)。

　　在內(nèi)存中存放數(shù)據(jù)，所以會(huì)造成內(nèi)存的使用，可以通過(guò)參數(shù)max_heap_table_size控制memory表的大小，設(shè)置此參數(shù)，就可以限制Memory表的最大大小了。

　　2.如果數(shù)據(jù)是臨時(shí)的，而且要求必須立即可用，那么就可以存放在內(nèi)存表中

　　3.存儲(chǔ)在Memory表中的數(shù)據(jù)如果突然丟失，不會(huì)對(duì)應(yīng)用服務(wù)器產(chǎn)生實(shí)質(zhì)的負(fù)面影響

　　archive

　　archive是歸檔的意思，在歸檔之后很多的高級(jí)功能就不再支持了，僅僅支持最基本的插入和查詢兩種功能。

　　mysql5.5版本以前，Archive是不支持索引，但是在Mysql以后的版本中就開(kāi)始支持索引了。

　　Archive擁有很好的壓縮機(jī)制，它使用zlib壓縮庫(kù)，在記錄被請(qǐng)求時(shí)會(huì)實(shí)時(shí)壓縮，所以它經(jīng)常被用來(lái)當(dāng)作倉(cāng)庫(kù)使用

　　常用于日志記錄和聚合分析方面

　　MERGE

　　MERGE存儲(chǔ)引擎是一組MyISAM表的組合，這些MYISAM表結(jié)構(gòu)必須完全相同，盡管其使用不如其他引擎突出，但是在某些情況下非常有用

　　Merge表就是幾個(gè)相同MyISAM表的聚合器

　　Merge表中并沒(méi)有數(shù)據(jù)，對(duì)Merge類型的表可以進(jìn)行查詢、更新、刪除操作

　　這些操作實(shí)際上是對(duì)內(nèi)部的MyISAM表進(jìn)行操作

　　Merge存儲(chǔ)引擎的使用場(chǎng)景:

　　對(duì)于服務(wù)器日志這種信息，一般常用的存儲(chǔ)策略是將數(shù)據(jù)分成很多表，每個(gè)名稱與特定的時(shí)間端相關(guān)

　　刪除Merge表只是刪除Merge表的定義，對(duì)內(nèi)部的表沒(méi)有任何影響

　　四、Mysql中關(guān)于存儲(chǔ)引擎的操作

　　1.查看數(shù)據(jù)庫(kù)可以支持的存儲(chǔ)引擎

　　show engines;

　　2.查看表的結(jié)構(gòu)等信息的若干命令

　　desc tablename:查看數(shù)據(jù)表的結(jié)構(gòu)

　　show create table tablename:顯示表的創(chuàng)建語(yǔ)句

　　show table status like 'tablename'\G:顯示表的當(dāng)前狀態(tài)值

　　3.設(shè)置或修改表的存儲(chǔ)引擎

　　create table tablename(

　　columnname(列名1) type(數(shù)據(jù)類型) attri(屬性設(shè)置)，

　　columnname(列名1) type(數(shù)據(jù)類型) attri(屬性設(shè)置)，

　　)engine= enginename:創(chuàng)建數(shù)據(jù)庫(kù)表時(shí)設(shè)置存儲(chǔ)存儲(chǔ)引擎

　　Alter table tablename engine = engineName：修改存儲(chǔ)引擎