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=1 or Value=4

　　　　---- 可以這樣查詢：
　　　　select * from SYS_Confige where Value=1
　　　　union all
　　　　select * from SYS_Confige where Value=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 Value 1 and 3
　　7、查詢時避免使用like '%待查詢關鍵字%' 查詢
　　8、在使用索引欄位作為條件時，如果該索引是複合索引，那麼必須使用到該索引中的第一個欄位作為條件時才能保證系統使用該索引，
　　否則該索引將不會被使用，並且應儘可能的讓欄位順序與索引順序相一致
　　9、能夠用關聯查詢的不要用exists
　　10、避免頻繁創建和刪除臨時表，以減少系統表資源的消耗。
　　11、儘量避免向客戶端返回大數據量，若數據量過大，應該考慮相應需求是否合理

分區存儲

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

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

分庫分表

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

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

總結

 　在數據優化過程中，索引是第一齣發點，語句優化必不可少，分區、分庫、分表也得考慮。