SQL優化與加固

提示：優化有風險，涉足需謹慎，

一、優化可能帶來的問題有哪些？

1.優化不總是對一個單純的環境進行，還很可能是一個復雜的已投產的系統；

2.優化手段本來就有很大的風險，只不過你沒能力意識到和預見到；

3.任何的技術可以解決一個問題，但必然存在帶來一個問題的風險；

4.對于優化來說解決問題而帶來的問題，控制在可接受的范圍內才是有成果；

5.保持現狀或出現更差的情況都是失敗！

二、優化的需求

1.提升sql性能，減少不必要的系統資源；

2.穩定性和業務可持續性，通常比性能更重要；

3.優化不可避免涉及到變更，變更就有風險；

4.優化使性能變好，維持和變差是等概率事件；

三、如何優化

1. SQL陳述句性能優化

1.任何地方都不要使用 select * from t ，用具體的欄位串列代替“*”，不要回傳用不到的任何欄位，

2.應盡量避免在 where 子句中對欄位進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描，如：
select id from t where num is null
可以在num上設定默認值0，確保表中num列沒有null值，然后這樣優化查詢：
select id from t where num=0

3.應盡量避免在 where 子句中使用!=或<>運算子，否則將引擎放棄使用索引而進行全表掃描，

4.應盡量避免在 where 子句中使用 or 來連接條件，否則將導致引擎放棄使用索引而進行全表掃描，如：
select id from t where num=10 or num=20
可以這樣優化查詢：
select id from t where num=10
union all
select id from t where num=20

5.in 和 not in 也要慎用，否則會導致全表掃描，如：
select id from t where num in(1,2,3)
對于連續的數值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3

6.下面的查詢也將導致全表掃描：
select id from t where name like '%abc%'

7.應盡量避免在 where 子句中對欄位進行運算式操作，這將導致引擎放棄使用索引而進行全表掃描，如：
select id from t where num/2=100
優化為:
select id from t where num=100*2

8.應盡量避免在where子句中對欄位進行函式操作，這將導致引擎放棄使用索引而進行全表掃描，如：
select id from t where substring(name,1,3)=‘abc’–name以abc開頭的id
優化為:
select id from t where name like 'abc%'

9.不要在 where 子句中的“=”左邊進行函式、算術運算或其他運算式運算，否則系統將可能無法正確使用索引，

10.在使用索引欄位作為條件時，如果該索引是復合索引，那么必須使用到該索引中的第一個欄位作為條件時才能保證系統使用該索引，
否則該索引將不會被使用，并且應盡可能的讓欄位順序與索引順序相一致，

11.不要寫一些沒有意義的查詢，如需要生成一個空表結構：
select col1,col2 into #t from t where 1=0
這類代碼不會回傳任何結果集，但是會消耗系統資源的，應改成這樣：
create table #t(…)

12.很多時候用 exists 代替 in 是一個好的選擇：
select num from a where num in(select num from b)
用下面的陳述句替換：
select num from a where exists(select 1 from b where num=a.num)

13.并不是所有索引對查詢都有效，SQL是根據表中資料來進行查詢優化的，當索引列有大量資料重復時，SQL查詢可能不會去利用索引，
如一表中有欄位sex，male、female幾乎各一半，那么即使在sex上建了索引也對查詢效率起不了作用，

14.索引并不是越多越好，索引固然可以提高相應的 select 的效率，但同時也降低了 insert 及 update 的效率，
因為 insert 或 update 時有可能會重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定，
一個表的索引數最好不要超過6個，若太多則應考慮一些不常使用到的列上建的索引是否有必要，

15.盡量使用數字型欄位，若只含數值資訊的欄位盡量不要設計為字符型，這會降低查詢和連接的性能，并會增加存盤開銷，
這是因為引擎在處理查詢和連接時會逐個比較字串中每一個字符，而對于數字型而言只需要比較一次就夠了，

16.盡可能的使用 varchar 代替 char ，因為首相比長欄位存盤空間小，可以節省存盤空間，
其次對于查詢來說，在一個相對較小的欄位內搜索效率顯然要高些，

17.對查詢進行優化，應盡量避免全表掃描，需要什么欄位操作什么欄位不建議使用 “ * ”（所有） 來代替查詢的欄位，首先應考慮在 where 及 order by 涉及的列上建立索引，

18.避免頻繁創建和洗掉臨時表，以減少系統表資源的消耗，

19.臨時表并不是不可使用，適當地使用它們可以使某些例程更有效，例如，當需要重復參考大型表或常用表中的某個資料集時，但是，對于一次性事件，最好使用匯出表，

20.在新建臨時表時，如果一次性插入資料量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，
以提高速度；如果資料量不大，為了緩和系統表的資源，應先create table，然后insert，

21.如果使用到了臨時表，在存盤程序的最后務必將所有的臨時表顯式洗掉，先 truncate table ，然后 drop table ，這樣可以避免系統表的較長時間鎖定，

22.盡量避免使用游標，因為游標的效率較差，如果游標操作的資料超過1萬行，那么就應該考慮改寫，

23.在所有的存盤程序和觸發器的開始處設定 SET NOCOUNT ON ，在結束時設定 SET NOCOUNT OFF ，無需在執行存盤程序和觸發器的每個陳述句后向客戶端發送 DONE_IN_PROC 訊息，

24.盡量避免向客戶端回傳大資料量，若資料量過大，應該考慮相應需求是否合理，

25.盡量避免大事務操作，提高系統并發能力，

2. SQL陳述句安全優化

1.替換單引號，即把所有單獨出現的單引號改成兩個單引號，防止攻擊者修改SQL命令的含義，再來看前面的例子，"SELECT * from Users WHERE login = ‘’’ or ‘‘1’’=’‘1’ AND password = ‘’’ or ‘‘1’’=’‘1’"顯然會得到與"SELECT * from Users WHERE login = ‘’ or ‘1’=‘1’ AND password = ‘’ or ‘1’=‘1’"不同的結果，

2.洗掉用戶輸入內容中的所有連字符，防止攻擊者構造出類如"SELECT * from Users WHERE login = ‘mas’ – AND password =’’"之類的查詢，因為這類查詢的后半部分已經被注釋掉，不再有效，攻擊者只要知道一個合法的用戶登錄名稱，根本不需要知道用戶的密碼就可以順利獲得訪問權限，

3.對于用來執行查詢的資料庫帳戶，限制其權限，用不同的用戶帳戶執行查詢、插入、更新、洗掉操作，由于隔離了不同帳戶可執行的操作，因而也就防止了原本用于執行SELECT命令的地方卻被用于執行INSERT、UPDATE或DELETE命令，

4.用存盤程序來執行所有的查詢，SQL引數的傳遞方式將防止攻擊者利用單引號和連字符實施攻擊，此外，它還使得資料庫權限可以限制到只允許特定的存盤程序執行，所有的用戶輸入必須遵從被呼叫的存盤程序的安全背景關系，這樣就很難再發生注入式攻擊了，

5.限制表單或查詢字串輸入的長度，如果用戶的登錄名字最多只有10個字符，那么不要認可表單中輸入的10個以上的字符，這將大大增加攻擊者在SQL命令中插入有害代碼的難度，

6.檢查用戶輸入的合法性，確信輸入的內容只包含合法的資料，資料檢查應當在客戶端和服務器端都執行——之所以要執行服務器端驗證，是為了彌補客戶端驗證機制脆弱的安全性，

7.將用戶登錄名稱、密碼等資料加密保存，加密用戶輸入的資料，然后再將它與資料庫中保存的資料比較，這相當于對用戶輸入的資料進行了"消毒"處理，用戶輸入的資料不再對資料庫有任何特殊的意義，從而也就防止了攻擊者注入SQL命令， System.Web.Security.FormsAuthentication類有一個 HashPasswordForStoringInConfigFile，非常適合于對輸入資料進行消毒處理，

8.檢查提取資料的查詢所回傳的記錄數量，如果程式只要求回傳一個記錄，但實際回傳的記錄卻超過一行，那就當作出錯處理

9.在MyBatis中，#{ }方式能夠很大程度防止sql注入攻擊，而$ { }方式無法防止Sql注入攻擊，一般情況推薦使用#{ }的方式，排序時使用order by 動態引數時需要注意，用${ }而不是#{ }，

總結

在進行MySQL的優化之前，必須要了解的就是MySQL的查詢程序，很多查詢優化作業實際上就是遵循一些原則，讓MySQL的優化器能夠按照預想的合理方式運行而已，