sql優化提速整理

sql優化提速整理

場景描述

在我們實際開發中，隨著業務的不斷增加，資料量也在不斷的攀升，這樣就離不開一個問題：資料查詢效率優化
根據自己的以往實際專案作業經驗和學習所知，現在對SQL查詢優化做一個簡單的梳理總結，總結的不好之處，望多多指點交流學習
主要通過以下幾個點來進行總結分析：索引、陳述句本身、磁區存盤、分庫分表

索引

 　　在實際作業中，sql優化第一想到的應該就是索引，因為添加索引能夠很直觀的提升查詢效率，但是在添加索引的時也不是越多多好，下面簡單總結一下索引的實際使用

　　索引簡介

 　　關于索引的定義，在此不詳細說明，網上的資料很多，索引簡單的理解就是資料的目錄，就好比一個字典的目錄，其目的是提高查詢效率

　　索引分類

 　　 SQL索引根據存盤關系，分為兩類：聚合索引和非聚合索引

　　聚集索引和非聚集索引的根本區別是表記錄的排列順序和與索引的排列順序是否一致，聚合索引的索引排序與表記錄的排序是一致的，非聚合索引正好相反，
　　在一個表中，只會存在一個聚合索引，主鍵默認就是聚合索引，聚合索引的關鍵詞為：clustered
　　創建聚合索引的SQL陳述句：

　　---- 根據資料表的欄位1、欄位2創建一個組合的聚合索引　　use 庫名　　create clustered index 索引名稱 on 表名(欄位1,欄位2)

　　SQL索引根據使用關系，分為四類：主鍵索引、唯一索引、普通索引（組合索引）、全文索引
　　主鍵索引：
　　表的主鍵自動為主鍵索引，每條資料的唯一標識，一個表只有一個主鍵索引
　　唯一索引：
　　唯一索引也是確保資料的唯一性，一個表可以多有多個唯一索引，這也是和主鍵索引的區別所在
　　創建唯一索引sql陳述句：　　

create UNIQUE index 索引名稱 on 表名(欄位1,欄位2)

　　普通索引：
　　普通索引可以對任意欄位或者多個欄位添加索引

　　

----創建普通索引sql陳述句：　　create index 索引名稱 on 表名(欄位1,欄位2) 

　　索引創建技巧

　　建立索引的原則

• 定義主鍵的資料列一定要建立索引，
• 定義有外鍵的資料列一定要建立索引，
• 對于經常查詢的資料列最好建立索引，
• 對于需要在指定范圍內的快速或頻繁查詢的資料列;
• 經常用在WHERE子句中的資料列，
• 經常出現在關鍵字order by、group by、distinct后面的欄位，建立索引，如果建立的是復合索引，索引的欄位順序要和這些關鍵字后面的欄位順序一致，否則索引不會被使用，
• 對于那些查詢中很少涉及的列，重復值比較多的列不要建立索引，
• 對于定義為text、image和bit的資料型別的列不要建立索引，
• 對于經常存取的列避免建立索引
• 限制表上的索引數目，對一個存在大量更新操作的表，所建索引的數目一般不要超過3個，最多不要超過5個，索引雖說提高了訪問速度，但太多索引會影響資料的更新操作，
• 對復合索引，按照欄位在查詢條件中出現的頻度建立索引，在復合索引中，記錄首先按照第一個欄位排序，對于在第一個欄位上取值相同的記錄，系統再按照第二個欄位的取值排序，以此類推，因此只有復合索引的第一個欄位出現在查詢條件中，該索引才可能被使用,因此將應用頻度高的欄位，放置在復合索引的前面，會使系統最大可能地使用此索引，發揮索引的作用，

　　索引碎片化處理（重構索引）

 　　關于索引的定義，在此不詳細說明，網上的資料很多，索引簡單的理解就是資料的目錄，就好比一個字典的目錄，其目的是提高查詢效率

　　索引簡介

 　　在實際開發中，有時候會發現新增了索引，但是效率還是沒有明顯提升，這時候需要考慮是否由于資料的更新編輯產生了索引碎片化，并處理

　　如果檢查是否有索引碎片：

---- 檢查一個表索引碎片化        　　use 庫名    DBCC ShowContig(待查詢的表)


---- 執行結果實體：

DBCC SHOWCONTIG 正在掃描 'SYS_Confige' 表...
表: 'SYS_Confige' (37575172)；索引 ID: 1，資料庫 ID: 7
已執行 TABLE 級別的掃描，
- 掃描頁數................................: 7885
- 掃描區數..............................: 986
- 區切換次數..............................: 985
- 每個區的平均頁數........................: 8.0
- 掃描密度 [最佳計數:實際計數].......: 100.00% [986:986]
- 邏輯掃描碎片 ..................: 0.01%
- 區掃描碎片 ..................: 1.12%
- 每頁的平均可用位元組數.....................: 23.0
- 平均頁密度(滿).....................: 99.72%
DBCC 執行完畢，如果 DBCC 輸出了錯誤資訊，請與系統管理員聯系，

----資料結構分析：處理

　　Logical Scan Fragmentation-邏輯掃描碎片：無序頁的百分比，該百分比應該在0％到10％之間，高了則說明有外部碎片，

　　解決方式：
　　解決方式有兩種方式:整理索引碎片、重建索引，在實際操程序中建議采用：重建索引，
　　重建索引的SQL陳述句：
　　use 庫名
　　DBCC DBREINDEX(待重建索引的表名稱)

查詢陳述句優化

　　在處理好索引后，接下來就是分析查詢陳述句，查詢陳述句可以借助專業的分析工具來分析，一個好的陳述句和不好的陳述句也會很影響效率，現在簡單總結一下在查詢陳述句的優化方向：

　　1、查詢欄位禁止出現 selete * 

2、where 及 order by 涉及的列上建立索引，

　　3、where避免出現非空判斷：比如：select from table where num is null
　　　　此時可以給num賦一個默認值0，陳述句修改為：select from table where num=0
　　4、應盡量避免在 where 子句中使用!=或<>運算子，否則將引擎放棄使用索引而進行全表掃描
　　5、應盡量避免在 where 子句中使用 or 來連接條件，否則將導致引擎放棄使用索引而進行全表掃描，如：
　　　　-----查詢value值為1 或者 4 的資料集合
　　　　select Id from SYS_Confige where Value=https://www.cnblogs.com/xiaoXuZhi/p/1 or Value=4

　　　　---- 可以這樣查詢：
　　　　select * from SYS_Confige where Value=https://www.cnblogs.com/xiaoXuZhi/p/1
　　　　union all
　　　　select * from SYS_Confige where Value=https://www.cnblogs.com/xiaoXuZhi/p/4
　　6、in 和 not in 也要慎用，否則會導致全表掃描，如：
　　　　select id from SYS_Configet where Value in(1,2,3)
　　　　對于連續的數值，能用 between 就不要用 in 了：
　　　　select id from SYS_Configet where num between  Value 1 and 3
　　7、查詢時避免使用like '%待查詢關鍵字%' 查詢
　　8、在使用索引欄位作為條件時，如果該索引是復合索引，那么必須使用到該索引中的第一個欄位作為條件時才能保證系統使用該索引，
　　否則該索引將不會被使用，并且應盡可能的讓欄位順序與索引順序相一致
　　9、能夠用關聯查詢的不要用exists
　　10、避免頻繁創建和洗掉臨時表，以減少系統表資源的消耗，
　　11、盡量避免向客戶端回傳大資料量，若資料量過大，應該考慮相應需求是否合理

磁區存盤

　　當單表的數量達到一定量時，為了提高查詢效率，資料表磁區存盤也是一個不錯的優化方案，
　　磁區呢就是把一張表的資料分成N多個區塊，這些區塊可以在同一個磁盤上，也可以在不同的磁盤上，通過提高減少檔案大小，提高IO處理效率，間接的提高查詢效率
　　磁區存盤，只是在資料存盤上采用磁區，但是在表現上還是一張表，

　　表磁區有以下優點：
　　　　1、改善查詢性能：對磁區物件的查詢可以僅搜索自己關心的磁區，提高檢索速度，
　　　　2、增強可用性：如果表的某個磁區出現故障，表在其他磁區的資料仍然可用；
　　　　3、維護方便：如果表的某個磁區出現故障，需要修復資料，只修復該磁區即可；
　　　　4、均衡I/O：可以把不同的磁區映射到磁盤以平衡I/O，改善整個系統性能，
　　缺點：
　　磁區表相關：已經存在的表沒有方法可以直接轉化為磁區表

分庫分表

　　分庫分表其實原理也是將一個大表拆分不同的小表，在拆分上有兩種拆分方式：
　　橫向拆分：主要針對一個表的欄位比較多，可以根據欄位的查詢頻率、更新頻率進行分割存盤，可以理解為表擴展
　　縱向拆分：縱向拆分主要是根據資料量，將資料存盤在不同的表，常用的拆分方式有：按照時間、按照哈希等等
　　分庫分表和磁區存盤兩者看上去是有點矛盾，實際上兩者的出發點不一樣，磁區：是降低大單表資料磁區存盤，分庫分表：直接將單表拆分為多表

　　同時分庫分表不僅僅會增加資料維護難度，同時也會需要投入大量的開發作業，所以分庫分表一般是要系統有一定的規模，公司有一定的資源支持
　　分庫分表兩種可以配合使用，比如在分表后，還可以對表進行磁區存盤，

總結

 　在資料優化程序中，索引是第一出發點，陳述句優化必不可少，磁區、分庫、分表也得考慮，

標籤：SQL Server

上一篇：oracle中兩個服務器連接中sys密碼修改問題

下一篇：Excel資料匯入到Sql server