SQL大資料最佳化下

    繼續之前的SQL資料最佳化，在上篇對SQL操作常用的工具進行介紹，本篇從個人最佳化資料庫的幾點進行羅列，通過此方法一定程度的提高大資料量下的查詢，維護效能。

1、審核大資料表的索引、預存程序、sql語句

     此方式是基礎性的，重點通過資料表的邏輯分析和效能工具，執行計畫查看是否缺少索引或sql語句書寫的消耗效能進行最佳化，對於存在IO瓶頸的問題，可以嘗試   使用翻頁預存程序等方法，在底層上實現資料最佳化，之前也有文章說明了一些常用sql語句的效能對比，盡量修改之。

2、資料庫記錄檔壓縮

    一個資料庫包含Data檔案和Log檔案，對於一個大庫，細心的你有時候會發現，記錄檔如此之大，我使用的記錄檔達到18G，記錄檔對於分析資料庫操作和例外情況下的資料恢複具有關鍵的作用，但這麼的檔案最好的方法就是定期備份，然後清空日誌。在不影響資料庫正常使用的情況下清空日誌方法如下：

 a、執行如下語句：DUMP TRANSACTION DBName WITH NOLOG

 b、右鍵資料庫名，選擇：任務-->收縮--->檔案，選擇檔案類型：日誌，在收縮操作中，選擇釋放...,輸入0，點擊確定，則記錄檔則被清空

    記錄檔的太大一方面會大量佔用檔案磁碟，另外在對應的資料操作中，頻繁的日誌讀寫也一定程度上影響磁軌的檢索速度，影響效能。

註：如果需要備份日誌的，實現應該先備份日誌。

3、查看資料庫對應中繼資料，分析索引片段，整理索引片段

    索引就是一個字典目錄，儲存著快捷訪問記錄的方式。但由於資料是動態變化的，不停的修改，刪除，插入可能導致索引動態變化，日積月累就會存在索引片段，這將導致系統在執行對應查詢檢索過程中，要執行一些額外的操作能定位到指定的索引，最好的方法就是一次性定位到索引，因此動態整理索引，清楚索引片段也很關鍵。下文代碼系統自動清理當前庫中，索引片段大於12%的索引，並重建對應索引。

先看如何查看索引片段：

     DBCC SHOWCONTIG 接下來自動清理索引片段大於12%的索引並自動重建，12%可以自設定。

use DBName --對指定的整個資料庫所有表進行重新組織索引set nocount on--使用遊標重新組織指定庫中的索引,消除索引片段--R_T層遊標取出當前資料庫所有表declare R_T CURSORfor select name from sys.tablesdeclare @T varchar(50)open r_tfetch NEXT from r_t into @t while @@fetch_status=0 begin--R_index遊標判斷指定表索引片段情況並最佳化 declare R_Index CURSOR for select t.name,i.name,s.avg_fragmentation_in_percent  from sys.tables t   join sys.indexes i on i.object_id=t.object_id    join sys.dm_db_index_physical_stats(db_id(),object_id(@T),null,null,'limited') s      on s.object_id=i.object_id and s.index_id=i.index_id     declare @TName varchar(50),@IName varchar(100),@avg int,@str varchar(500)    open r_index    fetch next     from r_index into @TName,@Iname,@avg    while @@fetch_status=0 begin  if @avg>=12  --如果片段大於12,重建索引      begin   set @str='alter index '+rtrim(@Iname)+' on dbo.'+rtrim(@tname)+' rebuild'  end  else   --如果片段小於30,重新組織索引        begin   set @STR='alter index '+rtrim(@Iname)+' on dbo.'+rtrim(@tname)+' reorganize'  end  print @str  exec (@str)  --執行        fetch next from r_index into @TName,@Iname,@avg       end--結束r_index遊標       close r_index       deallocate r_index       fetch next from r_t into @t       end--結束R_T遊標       close r_t       deallocate r_t       set nocount off

附帶的清理統計資訊，適情況也可以清理一下，如果統計資訊有效則清理會自動跳過

USE DBNameGO EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?',' ',90)" GO EXEC sp_updatestats Go

注:上文中的Use DBName，是使用對應的資料庫名，使用時注意修改。
完成如上操作後可以重啟資料庫服務，看看效果，以上最佳化可以一定程度上提升效能。

4、建立分區表和分區庫

    這個在強度和力度上都是效果顯著的，之前老覺得建立分區表是不是需要很多複雜的操作，需要建立對程式業務熟悉等等，其實sql已經給出了完善的方案。

磁碟分割表就是實現表的水平資料分割，將一個資料分布在多個資料實體檔案中，即.mdf 檔案中，考慮到效能每個資料檔案最好在不同的物理磁碟上。具體操作步驟羅列如下：

a、建立分區函數，主要使用CREATE PARTITION FUNCTION XXX(parms)

b、查看分區函數是否建立成功

　　SELECT * FROM sys.partition_functionsc、建立分區策略，CREATE PARTITION SCHEME SchemaForPartitiond、查看分區策略是否建立成功　　SELECT * FROM sys.partition_schemese、把分區策略和函數定義到資料表上，實現表關聯。d、測試一下分區情況和要查詢資料所在的分區　　SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('yourname')    如上分區表主要函數名羅列，可以通過此方法查看協助文檔，可以系列化對對此模組進行學習，並單純的複製代碼效果好的多。    在建立完分區表後，你會發現使用指定的條件查詢效能提升明顯，此處的關鍵就是那什麼作為分區的關鍵，這要根據具體的業務區考慮，比如日整，地區等等。 好了，以上就是資料庫最佳化的個人見解，大家有什麼好方法可以多交流。