30條百萬級資料庫優化方案(收集整理)
1.對查詢進行優化,要盡量避免全表掃描,首先應考慮在 where 及 order by 涉及的列上建立索引。
2.應盡量避免在 where 子句中對字段進行 null 值判斷,否則将導緻引擎放棄使用索引而進行全表掃描,如:
select id from t where num is null
最好不要給資料庫留NULL,盡可能的使用 NOT NULL填充資料庫.
備注、描述、評論之類的可以設定為 NULL,其他的,最好不要使用NULL。
不要以為 NULL 不需要空間,比如:char(100) 型,在字段建立時,空間就固定了, 不管是否插入值(NULL也包含在内),都是占用 100個字元的空間的,如果是varchar這樣的變長字段, 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 Name = 'admin'
可以這樣查詢:
select id from t where num = 10
union all
select id from t where Name = 'admin'
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
很多時候用 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)
6.下面的查詢也将導緻全表掃描:
select id from t where name like ‘%abc%’
若要提高效率,可以考慮全文檢索。
7.如果在 where 子句中使用參數,也會導緻全表掃描。因為SQL隻有在運作時才會解析局部變量,但優化程式不能将通路計劃的選擇推遲到運作時;它必須在編譯時進行選擇。然 而,如果在編譯時建立通路計劃,變量的值還是未知的,因而無法作為索引選擇的輸入項。如下面語句将進行全表掃描:
select id from t where num = @num
可以改為強制查詢使用索引:
select id from t with(index(索引名)) where num = @num
8.應盡量避免在 where 子句中對字段進行表達式操作,這将導緻引擎放棄使用索引而進行全表掃描。如:
select id from t where num/2 = 100
應改為:
select id from t where num = 100*2
9.應盡量避免在where子句中對字段進行函數操作,這将導緻引擎放棄使用索引而進行全表掃描。如:
select id from t where substring(name,1,3) = ’abc’
-–name以abc開頭的id
select id from t where datediff(day,createdate,’2005-11-30′) = 0 -–‘2005-11-30’ --生成的id
應改為:
select id from t where name like 'abc%'
select id from t where createdate >= '2005-11-30' and createdate < '2005-12-1'
10.不要在 where 子句中的“=”左邊進行函數、算術運算或其他表達式運算,否則系統将可能無法正确使用索引。
11.在使用索引字段作為條件時,如果該索引是複合索引,那麼必須使用到該索引中的第一個字段作為條件時才能保證系統使用該索引,否則該索引将不會被使用,并且應盡可能的讓字段順序與索引順序相一緻。
12.不要寫一些沒有意義的查詢,如需要生成一個空表結構:
select col1,col2 into #t from t where 1=0
這類代碼不會傳回任何結果集,但是會消耗系統資源的,應改成這樣:
create table #t(…)