SQL數(shù)據(jù)庫怎么進行優(yōu)化_SQL數(shù)據(jù)庫有什么優(yōu)化方式
優(yōu)化SQLServer數(shù)據(jù)庫的一些經(jīng)驗和注意事項,詳細介紹了SQL語句優(yōu)化的基本原則,包括索引、查詢和游標的使用等。下面由學習啦小編為大家整理的SQL數(shù)據(jù)庫優(yōu)化方式,希望大家喜歡!
SQL數(shù)據(jù)庫優(yōu)化的方式
1. 利用表分區(qū)
分區(qū)將數(shù)據(jù)在物理上分隔開,不同分區(qū)的數(shù)據(jù)可以制定保存在處于不同磁盤上的數(shù)據(jù)文件里。這樣,當對這個表進行查詢時,只需要在表分區(qū)中進行掃描,而不必進行全表掃描,明顯縮短了查詢時間,另外處于不同磁盤的分區(qū)也將對這個表的數(shù)據(jù)傳輸分散在不同的磁盤I/O,一個精心設(shè)置的分區(qū)可以將數(shù)據(jù)傳輸對磁盤I/O競爭均勻地分散開。對數(shù)據(jù)量大的時時表可采取此方法??砂丛伦詣咏ū矸謪^(qū)。
2. 別名的使用
別名是大型數(shù)據(jù)庫的應用技巧,就是表名、列名在查詢中以一個字母為別名,查詢速度要比建連接表快1.5倍。
3. 索引Index的優(yōu)化設(shè)計
索引可以大大加快數(shù)據(jù)庫的查詢速度。但是并不是所有的表都需要建立索引,只針對大數(shù)據(jù)量的表建立索引就好。
缺點:
1.創(chuàng)建索引和維護索引要耗費時間,這種時間隨著數(shù)據(jù)量的增加而增加。
2.索引需要占物理空間,除了數(shù)據(jù)表占數(shù)據(jù)空間之外,每一個索引還要占一定的物理空間,如果要建立聚簇索引,那么需要的空間就會更大。
3.當對表中的數(shù)據(jù)進行增加、刪除和修改的時候,索引也要動態(tài)的維護,這樣就降低了數(shù)據(jù)的維護速度。
索引需要維護:為了維護系統(tǒng)性能,索引在創(chuàng)建之后,由于頻繁地對數(shù)據(jù)進行增加、刪除、修改等操作使得索引頁發(fā)生碎塊,因此,必須對索引進行維護。
4. 物化視圖(索引視圖)
一般的視圖是虛擬的,而物化視圖是實實在在的數(shù)據(jù)區(qū)域,是要占據(jù)存儲空間的,另外系統(tǒng)刷新物化視圖也需要耗費一定的資源,但是它卻換來了效率和靈活性。
索引視圖更適合在OLAP(讀取較多,更新較少)的數(shù)據(jù)庫中使用,不適合在OLTP(記錄即時的增、刪、改、查)的數(shù)據(jù)庫中使用 。
物化視圖的注意事項:
1.對于復雜而高消耗的查詢,如果使用頻繁,應建成物化視圖。
2.物化視圖是一種典型的以空間換時間的性能優(yōu)化方式。
3.對于更新頻繁的表慎用物化視圖。
4.選擇合適的刷新方式。
普通視圖和物化視圖的區(qū)別:
普通視圖和物化視圖根本就不是一個東西,普通視圖是不存儲任何數(shù)據(jù)的,在查詢中是轉(zhuǎn)換為對應定義的SQL去查詢,而物化視圖是將數(shù)據(jù)轉(zhuǎn)換為一個表,實際存儲著數(shù)據(jù),這樣查詢數(shù)據(jù),就不用關(guān)聯(lián)一大堆表,如果表很大的話,會在臨時表空間內(nèi)做大量的操作。
普通視圖的三個特征:
1).簡化設(shè)計,方便,清晰編碼。視圖并不是提高性能的,它的存在只會降低性能(例如我們關(guān)聯(lián)兩個視圖,一個視圖關(guān)聯(lián)6個表,另一個視圖關(guān)聯(lián)7個表)。
2).安全,在授權(quán)給其他用戶或者查看角度,多個表關(guān)聯(lián)只允許查看,不允許修改。
3.從不同的角度看不同的維度,視圖可以劃分維度和權(quán)限,并使多個維度的綜合,也就是你要什么就可以從不同的角度看,而表是一個實體的而已,一般維度較少。
5. 死鎖與阻塞
1).對于需要頻繁更新的數(shù)據(jù),盡量避免放在長事務中,以免導致連鎖反應。
2).不是迫不得已,最好不要在數(shù)據(jù)庫鎖機制外再加自己設(shè)計的鎖。
3).減少事務大小,及時提交事務。
4).盡量避免跨數(shù)據(jù)庫的分布式事務,因為環(huán)境的復雜性,很容易導致阻塞。
5).慎用位圖索引,更新時容易導致死鎖。
6.減少IO與網(wǎng)絡傳輸次數(shù)
1).盡量用較少的數(shù)據(jù)庫請求,獲取到需要的數(shù)據(jù),能一次性取出的不分多次取出。
2).對于頻繁操作數(shù)據(jù)庫的批量操作,應采用存儲過程,減少不必要的網(wǎng)絡傳輸。
sql內(nèi)存釋放的方式
一、這些內(nèi)存一般都是SqlServer運行時候用作緩存
例如你運行一個select語句
那么Sql Server會將相關(guān)的數(shù)據(jù)頁(Sql Server操作的數(shù)據(jù)都是以頁為單位的)加載到內(nèi)存中來,
下一次如果再次請求此頁的數(shù)據(jù)的時候,就無需讀取磁盤了,大大提高了速度。這類的緩存叫做數(shù)據(jù)緩存。
還有一些其他類型的緩存,如執(zhí)行存儲過程時,Sql Server需要先編譯再運行,編譯后的結(jié)果也會緩存起來,
下一次就無需再次編譯了。如果這些緩存已經(jīng)不需要了,那么我們可以調(diào)用以下幾個DBCC管理命令來清理這些緩存:
DBCC FREEPROCCACHE
DBCC FREESESSIONCACHE
DBCC FREESYSTEMCACHE('All')
DBCC DROPCLEANBUFFERS
這幾個命令分別用來清除存儲過程相關(guān)的緩存、會話緩存、系統(tǒng)緩存以及所有所有緩存
但是需要注意的是,這幾個命令雖然會清除掉現(xiàn)有緩存,為新的緩存騰地方,
但是Sql server并不會因此釋放掉已經(jīng)占用的內(nèi)存。無奈的是,Sql Server
并沒有提供任何命令允許我們釋放不用到的內(nèi)存。因此我們只能通過動態(tài)調(diào)整
Sql Server可用的物理內(nèi)存設(shè)置來強迫它釋放內(nèi)存。
我們也可以通過Sql Server Management企業(yè)管理器進行動態(tài)控制。
連接到企業(yè)管理器之后打開Sql Server實例的屬性面板,
找到內(nèi)存設(shè)置,改變其中的最大服務器內(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 --強制釋放內(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存儲引擎是什么
一、存儲引擎的介紹
關(guān)系型數(shù)據(jù)庫表是用來存儲和組織信息的數(shù)據(jù)結(jié)構(gòu),可以將表理解為由行和列組成的表格。
由于表的類型不同,我們在實際開發(fā)過程中,就有可能需要各種各樣的表,不同的表就意味著存儲不同類型的數(shù)據(jù),數(shù)據(jù)的處理上也會存在差異
對于Mysql來說,它提供了很多種類型的存儲引擎
存儲引擎說白了就是如何存儲數(shù)據(jù),如何為存儲的數(shù)據(jù)建立索引和如何更新、查詢數(shù)據(jù)等技術(shù)的實現(xiàn)方法。
因為在關(guān)系型數(shù)據(jù)庫中數(shù)據(jù)的存儲是以表的形式存儲 的,所以存儲引擎也可以稱為表類型(即存儲和操作此表的類型)
二、Mysql中的存儲引擎
MyISAM
這種引擎是mysql最早提供的,這種引擎又可以分為靜態(tài)MyISAM、動態(tài)MyISAM和壓縮MyISAM三種
靜態(tài)MyISAM:
如果數(shù)據(jù)表中的各數(shù)據(jù)列的長度都是預先固定好的,服務器將自動選擇這種表類型。
因為數(shù)據(jù)表中的每一條記錄所占用的空間都是一樣的,所以這種表存取和更新的效率非常高。
當數(shù)據(jù)受損時,恢復工作也比較容易做
動態(tài)MyISAM:
如果數(shù)據(jù)表中出現(xiàn)varchar,xxxtext或xxxxBLOB字段時,服務器將自動選擇這種表類型 。
相對于靜態(tài)MyISAM,這種表存儲空間比較小,但由于每條記錄的長度不一,所以多次修改數(shù)據(jù)后,數(shù)據(jù)表中的數(shù)據(jù)就可能離散的存儲在內(nèi)存中,進而導致執(zhí)行效率下降.
同時內(nèi)存中也可能會出現(xiàn)很多碎片。
因此這種類型的表要經(jīng)常 用optimize table命令 或優(yōu)化工具來進行碎片整理
壓縮MyISAM:
以上說的兩種類型的表都可以用myisamchk工具壓縮。
這種類型的表進一步減少了占用的存儲。
但是這種表壓縮之后不能再被修改。
另外,因為是壓縮數(shù)據(jù),所以這種表在讀取的時候要先執(zhí)行解壓縮
但是,不管是哪種MyISAM表,目前都不支持事務,行級鎖和外鍵約束的功能
MyISAM表示獨立于操作系統(tǒng)的,這說明可以輕松的將其從windows服務器轉(zhuǎn)移 到linux服務器;
每當我們建立一個MyISAM引擎的表時,就會在本地磁盤上建立三個文件,文件名就是表名。
例如:我建立一個MyISAM引擎的th_Demo表,那么就會生成以下三個文件:
1.th_demo.frm:存儲表定義;
2.th_demo.MYD:存儲數(shù)據(jù);
3.th_demo.MYI:存儲索引;
MyISAM存儲引擎特別適合在以下幾種情況下使用:
1.選擇密集型的表,MYISAM存儲引擎在篩選大量數(shù)據(jù)時非常迅速,這是它最突出的優(yōu)點
2.插入密集型的表,MYISAM的并發(fā)插入特性允許同時選擇和插入數(shù)據(jù)。例如:MYISAM存儲引擎很適合管理郵件或web服務器日志數(shù)據(jù)
InnoDB
InnoDB表類型可以看作是對MyISAM的進一步更新產(chǎn)品,它提供了事務、行級鎖機制和外鍵約束的功能
InnoDB是一個健壯的事務型存儲引擎
使用場景:
1.更新密集的表。InnoDB存儲引擎特別適合處理多重并發(fā)的更新請求
2.事務.InnoDB存儲引擎是支持事務的標準mysql存儲引擎
3.自動災難恢復。與其他存儲引擎不同,InnoDB表能夠自動從災難中恢復
4.外鍵約束。mysql支持外鍵的存儲引擎只有InnoDB
5.支持自動增加列AUTO_INCREMENT屬性
一般來說,如果需要事務支持,并且有較高的并發(fā)讀取頻率,InnoDB是不錯的選擇
memory(heap)
使用Mysql Memory存儲引擎的出發(fā)點是速度,為了得到最快的響應時間
采用的邏輯存儲介質(zhì)是系統(tǒng)內(nèi)存。
它要求存儲在Memory數(shù)據(jù)表里的數(shù)據(jù)使用的是長度不變的格式,這意味著不能使用BLOB和Text這樣的長度可變的數(shù)據(jù)類型
VARCHAR是一種長度可變的類型,但因為它在Mysql內(nèi)部當作長度固定不變的CHAR類型,所以可以使用
Memory同時支持散列索引和B樹索引。B樹索引優(yōu)于散列索引的是:
可以使用部分查詢和通配查詢,也可以使用<,>和>=等操作符方便數(shù)據(jù)挖掘
散列索引進行“相等比較”非???,但是對"范圍比較"的速度就慢多了
散列索引值適合使用在=和<>的操作符,不適合<或>操作符中,也同樣不適合在order by子句中
三、一般在以下幾種情況下使用Memory存儲引擎:
1.目標數(shù)據(jù)較小,而且被非常頻繁地訪問。
在內(nèi)存中存放數(shù)據(jù),所以會造成內(nèi)存的使用,可以通過參數(shù)max_heap_table_size控制memory表的大小,設(shè)置此參數(shù),就可以限制Memory表的最大大小了。
2.如果數(shù)據(jù)是臨時的,而且要求必須立即可用,那么就可以存放在內(nèi)存表中
3.存儲在Memory表中的數(shù)據(jù)如果突然丟失,不會對應用服務器產(chǎn)生實質(zhì)的負面影響
archive
archive是歸檔的意思,在歸檔之后很多的高級功能就不再支持了,僅僅支持最基本的插入和查詢兩種功能。
mysql5.5版本以前,Archive是不支持索引,但是在Mysql以后的版本中就開始支持索引了。
Archive擁有很好的壓縮機制,它使用zlib壓縮庫,在記錄被請求時會實時壓縮,所以它經(jīng)常被用來當作倉庫使用
常用于日志記錄和聚合分析方面
MERGE
MERGE存儲引擎是一組MyISAM表的組合,這些MYISAM表結(jié)構(gòu)必須完全相同,盡管其使用不如其他引擎突出,但是在某些情況下非常有用
Merge表就是幾個相同MyISAM表的聚合器
Merge表中并沒有數(shù)據(jù),對Merge類型的表可以進行查詢、更新、刪除操作
這些操作實際上是對內(nèi)部的MyISAM表進行操作
Merge存儲引擎的使用場景:
對于服務器日志這種信息,一般常用的存儲策略是將數(shù)據(jù)分成很多表,每個名稱與特定的時間端相關(guān)
刪除Merge表只是刪除Merge表的定義,對內(nèi)部的表沒有任何影響
四、Mysql中關(guān)于存儲引擎的操作
1.查看數(shù)據(jù)庫可以支持的存儲引擎
show engines;
2.查看表的結(jié)構(gòu)等信息的若干命令
desc tablename:查看數(shù)據(jù)表的結(jié)構(gòu)
show create table tablename:顯示表的創(chuàng)建語句
show table status like 'tablename'\G:顯示表的當前狀態(tài)值
3.設(shè)置或修改表的存儲引擎
create table tablename(
columnname(列名1) type(數(shù)據(jù)類型) attri(屬性設(shè)置),
columnname(列名1) type(數(shù)據(jù)類型) attri(屬性設(shè)置),
)engine= enginename:創(chuàng)建數(shù)據(jù)庫表時設(shè)置存儲存儲引擎
Alter table tablename engine = engineName:修改存儲引擎