一、SQL優化上不得不談的SQL災難
SQL災難初期不顯: 專案初期效果與災難不可觸及: 專案初期，業務資料量少， SQL執行效率的影響不明顯，開發和運維以及業務人員無法體悟出個中好壞。
SQL災難的后果是癱瘓性的:SQL的災難影響通常是一大片,涉及到的業務專案、功能模塊乃致整個資料庫。這在我們公司的物流出倉，生產訂單，銷售訂單等及時性高的業務部門專案上可能會給公司達來直接的經濟損失，會成為被追責的物件。
SQL災難的良方是未雨綢繆: SQL的災難相當于新冠病毒,一旦有體癥表現時,對于我們現在系統的寵大與復雜發生ＳＱＬ災難時猶于新冠病毒發生在千萬級別的武漢城，在查癥確認上都已不是容易的事。SQL災難以預防為主，結合個人與團隊的經驗未雨綢繆、疑事多問多學、防微杜漸。
二、SQL優化的一些方法
1. 索引的一些原則:
1). 索引的寬度
索引寬度，即索引的鍵占用了多少個位元組。影響索引寬度有2個因素：一是參考的列，二是索引的資料型別。原則上，索引應保持較窄，就是說，列要盡可能少，列的資料型別要盡可能精簡。
不能將 ntext、text、image、varchar(max)、nvarchar(max) 和 varbinary(max) 資料型別的列指定為索引鍵列。不過，varchar(max)、nvarchar(max)、varbinary(max) 和 xml 資料型別的列可以作為非鍵索引列參與非聚集索引,但不建議用。
設計時注意以下幾類資料型別：
（1）日期型 vs. 日期時間型
舊版本的SQL Server只有日期時間型datetime（1753-01-01 00:00:00.000到 9999-12-31 23:59:59.999，8個位元組）和smalldatetime（范圍從1900-01-01 00:00到 2079-06-06 23:59，4個位元組）。SQL Server 2008 新增了多種日期型別，其中包括date（范圍從0001-01-01 到 9999-12-31，3個位元組）和time(time資料型別支持從0到7不同的精度，就像DATETIME2格式，它的磁盤開銷是3到5個位元組)。如果某個列經常只需要查詢日期，建議將實際業務的Datetime拆分為date和time型別的2個列，并在date型別的列上建立索引。
（2）整型 vs. 字符型
有些編號，例如客戶ID，如果業務上沒有特別要求，那么使用整型是最佳選擇。因為，整型的范圍從 -2,147,483,648 到 2,147,483,647 ，占4個位元組，而同樣范圍的字符型卻需要10個位元組。此外，對于小范圍的編號，smallint也是不錯的選擇，它的范圍從 -32,768 到 32,767 ，只占2個位元組。
（3）Uniqueidentifier列（GUID）vs. IDENTITY列
有些程式員希望每行有一個唯一標識，于是將GUID作為標識。如果在 表定義中將列型別指定為 uniqueidentifier，則列的值就為 GUID 型別，占16個位元組。
CREATE TABLE Table1( MyID uniqueidentifier, MyName varchar(10) )
insert into Table1 values (newid(),'noname')
select * from Table1
從程式設計的角度來看，上述設計并無不妥。但如果在這個列上建立索引（尤其是聚集索引），對性能可能有很大的影響。建議改用IDENTITY列。
首先，GUID占用16個位元組；而IDENTITY列為int型別，僅占用4個位元組。對比之下，后者的索引寬度可以縮減12個位元組。
其次，GUID是隨機的，導致索引的分頁現象非常嚴重；而IDENTITY列的值一般是連續增長，因此不會造成過多索引分頁。
（4）主資料聚集主鍵: IDENTITY列 vs. varChar(10)編號列
有些程式員希望主資料以編號作為主鍵,然后在單據表里以主資料的編號欄位寫入記錄.相對于用 IDENTITY列作為主資料表的主鍵并寫入單據表記錄的方法而言,記錄相對看起來直觀,大多程式員都樂意這樣處理. 這可能在以往他們所經歷的小公司或軟體公司看起來沒什么影響. 這樣的主鍵索引 varchar(10)編號列 比 IDENTITY列 慢近20倍,對于我們公司大多時候都是百萬級,千萬級的資料,這樣的主鍵帶來20倍的效率損失將可能引發災難性后果.
(5) T_Code_info的災難:現在部份庫的這個表已達5000條資料,而這個表的索引效率非常低.引發了很多問題,后續需要改為T_Code_head 與 T_Code_item二個主從表以分類方式存盤優化這個表.
2.特殊情況下,建索引的一些經驗:
1). 對于頻繁插入更新的超大資料表, 聚集索引的正確創建猶其重要,否則后續資料量大的時候影響非常嚴重. 根據經驗應以自增量欄位或恒定順序的唯一欄位列作為聚集索引/聚集主鍵,可以大大提高資料插入時的時效,從而表被鎖定的時間減小,能提高整個涉及此表查詢的所有陳述句的執行效率. 這在公司的條碼掃碼表里已存在過負面例子.
約定:一年資料量在500W條記錄以上的表, 必須以自增量欄位為聚集索引/聚集主鍵,絕對不能以多個欄位的復合索引做為聚集主鍵/聚集索引.
注: 超大資料表用自增量欄位做為聚集索引,多個欄位組合成非聚集主鍵做為唯一主鍵約束,這是對頻繁插入與更新的大資料表的最優方案.
約定: 所有主資料表必須是int/bigint型別的NID自增量作為主鍵聚集索引,主從關系的單據表,主表必須是int/bigint型別的NID自增量作為聚集索引,并且從表以主表的NID欄位作為關聯欄位.
(這里再提一句計量系統按秒寫入資料表建表與建索引的經驗)
2).主鍵: 是所有索引里除聚集索引外最快的, 所有表我們都需要建一個單索引/復合索引的主鍵(注意涉及主鍵的欄位不能存在NULL值).
3). 設計索引的一個重要原則就是能用單索引不用復合索引,因為單索引往往比復合索引更有效.
如非必要不建議建復合索引(主鍵除外),因為復合索引的只有在單向順序符合才生效. 如果不知道或記錯順序,專案上線后,到后期資料量上來后,才發現這復合索引實際在你的查詢中不生效,很可能就會引發SQL災難.
4). 索引列盡量不要參與計算，保持查詢的索引列“干凈”。比如， cast(create_time as date)='2020-03-06' 就不能使用索引，原因很簡單，B+樹中存盤的都是資料表中的欄位值，但是進行檢索時，需要把所有元素都應用函式才能比較，顯然這樣的代價太大。所以陳述句要寫成 ： create_time >=cast('2020-03-06 00:00:00' as datetime) And create_time< cast('2020-03-07 00:00:00' as datetime)
5).再次講到應盡量避免在 where 子句中對欄位進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描.
這里拿出來講主要是上次有一個老同事有一條陳述句他優化幾次都不得要領,問題發生在關聯的子查詢里有null值, 在主查詢中直接通對子表里的null值作為查詢條件對效率影響很大. 為了避免這情況應把在子查詢里把這有null值的欄位轉化成其他特殊值. 如: nvl(ParentNO,'ISNULL', ParentNO) as ParentNO, 主查詢里用 ParentNO<>'ISNULL'
6).應盡量避免在 where 子句的中間使用!=或<>運算子，否則將引擎放棄使用索引而進行全表掃描,一定要用時,這類查詢條件盡量別用(布爾型的改為= ),一定要用時盡量放到where 子句的最后。
這里引申出一個問題: 就是我們很多時候為了省事,把狀態欄位定義為char(2),這對查詢效率的影響也是非常大的,這情況目前在我們的表里很常見.
解決方法: 改成布爾型或smallint或int, 且把狀態表的說明維護在系統配置表里,并為方便查詢可以在系統配置表里對狀態分組標識以提高查詢效率,減小 <>或IN查詢的出現.
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 時有可能會重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。
一個表的索引數最好不要超過12個，若太多則應考慮一些不常使用到的列上建的索引是否有必要。
15.盡量使用數字型欄位，若只含數值資訊的欄位盡量不要設計為字符型，這會降低查詢和連接的性能，并會增加存盤開銷。
這是因為引擎在處理查詢和連接時會逐個比較字串中每一個字符，而對于數字型而言只需要比較一次就夠了。
16.欄位值定長的欄位使用char代替varchar,不定長的欄位盡可能的使用 varchar 代替 char ，因為首先變長欄位存盤空間小，可以節省存盤空間，其次對于查詢來說，在一個相對較小的欄位內搜索效率顯然要高些。
17.任何地方都不要使用 select * from t ，用具體的欄位串列代替“*”，不要回傳用不到的任何欄位。
18.在回圈中為避免頻繁創建和洗掉臨時表，以減少系統表資源的消耗,應盡量在回圈外創建和洗掉臨時表。
19.臨時表并不是不可使用，適當地使用它們可以使某些例程更有效，例如，當需要重復參考大型表或常用表中的某個資料集時。但是，對于一次性事件，最好使用匯出表。
20.在新建臨時表時，如果一次性插入資料量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果資料量不大，為了緩和系統表的資源，應先create table，然后insert。
21.如果使用到了臨時表，在存盤程序的最后務必將所有的臨時表顯式洗掉，先 truncate table ，然后 drop table ，這樣可以避免系統表的較長時間鎖定。
22.盡量避免使用游標，因為游標的效率較差，如果游標操作的資料超過1萬行，那么就應該考慮改寫。
23.使用基于游標的方法或臨時表方法之前，應先尋找基于集的解決方案來解決問題，基于集的方法通常更有效。
24.與臨時表一樣，游標并不是不可使用。對小型資料集使用 FAST_FORWARD 游標通常要優于其他逐行處理方法，尤其是在必須參考幾個表才能獲得所需的資料時。
在結果集中包括“合計”的例程通常要比使用游標執行的速度快。如果開發時間允許，基于游標的方法和基于集的方法都可以嘗試一下，看哪一種方法的效果更好。
25.盡量避免大事務操作，提高系統并發能力。
26.盡量避免向客戶端回傳大資料量，若資料量過大，應該考慮相應需求是否合理。
-- [完]-- 2020年03月04日 易登科(電話: 688336/13929928336)
————————————————
著作權宣告：本文為CSDN博主「moqing」的原創文章，遵循 CC 4.0 BY-SA 著作權協議，轉載請附上原文出處鏈接及本宣告。
原文鏈接：https://blog.csdn.net/moqing/article/details/104657457
uj5u.com熱心網友回復：
絕大部分還是值得借鑒的， 感謝分享
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/44541.html
標籤：基礎類
下一篇：sql2019 安裝遇到的問題