SQL數據庫怎么進行優化_SQL數據庫有什么優化方式

　　優化SQLServer數據庫的一些經驗和注意事項，詳細介紹了SQL語句優化的基本原則，包括索引、查詢和游標的使用等。下面由學習啦小編為大家整理的SQL數據庫優化方式，希望大家喜歡!

　　SQL數據庫優化的方式

　　1. 利用表分區

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

　　2. 別名的使用

　　別名是大型數據庫的應用技巧，就是表名、列名在查詢中以一個字母為別名，查詢速度要比建連接表快1.5倍。

　　3. 索引Index的優化設計

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

　　缺點：

　　1.創建索引和維護索引要耗費時間，這種時間隨著數據量的增加而增加。

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

　　3.當對表中的數據進行增加、刪除和修改的時候，索引也要動態的維護，這樣就降低了數據的維護速度。

　　索引需要維護：為了維護系統性能，索引在創建之后，由于頻繁地對數據進行增加、刪除、修改等操作使得索引頁發生碎塊，因此，必須對索引進行維護。

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

　　一般的視圖是虛擬的，而物化視圖是實實在在的數據區域，是要占據存儲空間的，另外系統刷新物化視圖也需要耗費一定的資源，但是它卻換來了效率和靈活性。

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

　　物化視圖的注意事項：

　　1.對于復雜而高消耗的查詢，如果使用頻繁，應建成物化視圖。

　　2.物化視圖是一種典型的以空間換時間的性能優化方式。

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

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

　　普通視圖和物化視圖的區別：

　　普通視圖和物化視圖根本就不是一個東西，普通視圖是不存儲任何數據的，在查詢中是轉換為對應定義的SQL去查詢，而物化視圖是將數據轉換為一個表，實際存儲著數據，這樣查詢數據，就不用關聯一大堆表，如果表很大的話，會在臨時表空間內做大量的操作。

　　普通視圖的三個特征：

　　1).簡化設計，方便，清晰編碼。視圖并不是提高性能的，它的存在只會降低性能(例如我們關聯兩個視圖，一個視圖關聯6個表，另一個視圖關聯7個表)。

　　2).安全，在授權給其他用戶或者查看角度，多個表關聯只允許查看，不允許修改。

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

　　5. 死鎖與阻塞

　　1).對于需要頻繁更新的數據，盡量避免放在長事務中，以免導致連鎖反應。

　　2).不是迫不得已，最好不要在數據庫鎖機制外再加自己設計的鎖。

　　3).減少事務大小，及時提交事務。

　　4).盡量避免跨數據庫的分布式事務，因為環境的復雜性，很容易導致阻塞。

　　5).慎用位圖索引，更新時容易導致死鎖。

　　6.減少IO與網絡傳輸次數

　　1).盡量用較少的數據庫請求，獲取到需要的數據，能一次性取出的不分多次取出。

　　2).對于頻繁操作數據庫的批量操作，應采用存儲過程，減少不必要的網絡傳輸。

　　sql內存釋放的方式

　　一、這些內存一般都是SqlServer運行時候用作緩存

　　例如你運行一個select語句

　　那么Sql Server會將相關的數據頁(Sql Server操作的數據都是以頁為單位的)加載到內存中來，

　　下一次如果再次請求此頁的數據的時候，就無需讀取磁盤了，大大提高了速度。這類的緩存叫做數據緩存。

　　還有一些其他類型的緩存，如執行存儲過程時，Sql Server需要先編譯再運行，編譯后的結果也會緩存起來，

　　下一次就無需再次編譯了。如果這些緩存已經不需要了，那么我們可以調用以下幾個DBCC管理命令來清理這些緩存：

　　DBCC FREEPROCCACHE

　　DBCC FREESESSIONCACHE

　　DBCC FREESYSTEMCACHE('All')

　　DBCC DROPCLEANBUFFERS

　　這幾個命令分別用來清除存儲過程相關的緩存、會話緩存、系統緩存以及所有所有緩存

　　但是需要注意的是，這幾個命令雖然會清除掉現有緩存，為新的緩存騰地方，

　　但是Sql server并不會因此釋放掉已經占用的內存。無奈的是，Sql Server

　　并沒有提供任何命令允許我們釋放不用到的內存。因此我們只能通過動態調整

　　Sql Server可用的物理內存設置來強迫它釋放內存。

　　我們也可以通過Sql Server Management企業管理器進行動態控制。

　　連接到企業管理器之后打開Sql Server實例的屬性面板，

　　找到內存設置，改變其中的最大服務器內存使用即可

　　二、--內存使用情況

　　SELECT * FROM sys.dm_os_performance_counters

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

　　三、-- 內存狀態

　　DBCC MemoryStatus

　　四、--查看最小最大內存

　　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

　　五、--設置最小最大內存

　　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 --強制釋放內存

　　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存儲引擎是什么

　　一、存儲引擎的介紹

　　關系型數據庫表是用來存儲和組織信息的數據結構，可以將表理解為由行和列組成的表格。

　　由于表的類型不同，我們在實際開發過程中，就有可能需要各種各樣的表，不同的表就意味著存儲不同類型的數據，數據的處理上也會存在差異

　　對于Mysql來說，它提供了很多種類型的存儲引擎

　　存儲引擎說白了就是如何存儲數據，如何為存儲的數據建立索引和如何更新、查詢數據等技術的實現方法。

　　因為在關系型數據庫中數據的存儲是以表的形式存儲 的，所以存儲引擎也可以稱為表類型(即存儲和操作此表的類型)

　　二、Mysql中的存儲引擎

　　MyISAM

　　這種引擎是mysql最早提供的，這種引擎又可以分為靜態MyISAM、動態MyISAM和壓縮MyISAM三種

　　靜態MyISAM:

　　如果數據表中的各數據列的長度都是預先固定好的，服務器將自動選擇這種表類型。

　　因為數據表中的每一條記錄所占用的空間都是一樣的，所以這種表存取和更新的效率非常高。

　　當數據受損時，恢復工作也比較容易做

　　動態MyISAM:

　　如果數據表中出現varchar,xxxtext或xxxxBLOB字段時，服務器將自動選擇這種表類型 。

　　相對于靜態MyISAM,這種表存儲空間比較小，但由于每條記錄的長度不一，所以多次修改數據后，數據表中的數據就可能離散的存儲在內存中，進而導致執行效率下降.

　　同時內存中也可能會出現很多碎片。

　　因此這種類型的表要經常 用optimize table命令 或優化工具來進行碎片整理

　　壓縮MyISAM:

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

　　這種類型的表進一步減少了占用的存儲。

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

　　另外，因為是壓縮數據，所以這種表在讀取的時候要先執行解壓縮

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

　　MyISAM表示獨立于操作系統的，這說明可以輕松的將其從windows服務器轉移 到linux服務器;

　　每當我們建立一個MyISAM引擎的表時，就會在本地磁盤上建立三個文件，文件名就是表名。

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

　　1.th_demo.frm:存儲表定義;

　　2.th_demo.MYD:存儲數據;

　　3.th_demo.MYI:存儲索引;

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

　　1.選擇密集型的表，MYISAM存儲引擎在篩選大量數據時非常迅速，這是它最突出的優點

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

　　InnoDB

　　InnoDB表類型可以看作是對MyISAM的進一步更新產品，它提供了事務、行級鎖機制和外鍵約束的功能

　　InnoDB是一個健壯的事務型存儲引擎

　　使用場景：

　　1.更新密集的表。InnoDB存儲引擎特別適合處理多重并發的更新請求

　　2.事務.InnoDB存儲引擎是支持事務的標準mysql存儲引擎

　　3.自動災難恢復。與其他存儲引擎不同，InnoDB表能夠自動從災難中恢復

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

　　5.支持自動增加列AUTO_INCREMENT屬性

　　一般來說，如果需要事務支持，并且有較高的并發讀取頻率，InnoDB是不錯的選擇

　　memory(heap)

　　使用Mysql Memory存儲引擎的出發點是速度，為了得到最快的響應時間

　　采用的邏輯存儲介質是系統內存。

　　它要求存儲在Memory數據表里的數據使用的是長度不變的格式，這意味著不能使用BLOB和Text這樣的長度可變的數據類型

　　VARCHAR是一種長度可變的類型，但因為它在Mysql內部當作長度固定不變的CHAR類型，所以可以使用

　　Memory同時支持散列索引和B樹索引。B樹索引優于散列索引的是:

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

　　散列索引進行“相等比較”非常快，但是對"范圍比較"的速度就慢多了

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

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

　　1.目標數據較小，而且被非常頻繁地訪問。

　　在內存中存放數據，所以會造成內存的使用，可以通過參數max_heap_table_size控制memory表的大小，設置此參數，就可以限制Memory表的最大大小了。

　　2.如果數據是臨時的，而且要求必須立即可用，那么就可以存放在內存表中

　　3.存儲在Memory表中的數據如果突然丟失，不會對應用服務器產生實質的負面影響

　　archive

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

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

　　Archive擁有很好的壓縮機制，它使用zlib壓縮庫，在記錄被請求時會實時壓縮，所以它經常被用來當作倉庫使用

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

　　MERGE

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

　　Merge表就是幾個相同MyISAM表的聚合器

　　Merge表中并沒有數據，對Merge類型的表可以進行查詢、更新、刪除操作

　　這些操作實際上是對內部的MyISAM表進行操作

　　Merge存儲引擎的使用場景:

　　對于服務器日志這種信息，一般常用的存儲策略是將數據分成很多表，每個名稱與特定的時間端相關

　　刪除Merge表只是刪除Merge表的定義，對內部的表沒有任何影響

　　四、Mysql中關于存儲引擎的操作

　　1.查看數據庫可以支持的存儲引擎

　　show engines;

　　2.查看表的結構等信息的若干命令

　　desc tablename:查看數據表的結構

　　show create table tablename:顯示表的創建語句

　　show table status like 'tablename'\G:顯示表的當前狀態值

　　3.設置或修改表的存儲引擎

　　create table tablename(

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

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

　　)engine= enginename:創建數據庫表時設置存儲存儲引擎

　　Alter table tablename engine = engineName：修改存儲引擎