SQL優(yōu)化原則和技巧.doc

SQL優(yōu)化原則和技巧索引的引用1、 索引的刪除與重建當插入的數(shù)據(jù)為數(shù)據(jù)表中的記錄數(shù)量的10%以上，首先刪除該表的索引來提高數(shù)據(jù)的插入效率，當數(shù)據(jù)插入后，在建立索引2、避免在所有列上使用函數(shù)或計算，在where字句中，如果索引是函數(shù)的一部分，優(yōu)化器將不再使用索引而是用全表掃描。如：低效：select * from table_name where col1*101000;高效：select * from table_name where col11000/10;3、盡量避免在索引列上使用not和”!=”和”,索引只能告訴你什么存在于表中，而不能告訴你什么不存在于表中，當數(shù)據(jù)庫遇到not和”!=”和”時，就會停止使用索引而去執(zhí)行全表掃描。請務必注意，檢索中不要對索引列進行處理，如:trim,to_date,類型轉換等操作，破壞索引，使用全表掃描，影響執(zhí)行效率。避免在索引列上使用 is null 和 is not null 避免在索引中使用任何可以為空的列，oracle將無法使用索引對于單列索引，如果列包含空值，索引將不存在此記錄對于復合索引，如果每列都為空，索引中同樣不存在此記錄。如果至少有一列不為空，則記錄存在于索引中因為空值不存在于索引中，所以where字句中對索引列進行空值比較將使oracle停用該索引4、索引列上”=”代替” 低效：select * from table_name where col110;高效: select * from table_name where cil1=10.000000000001;SQL的優(yōu)化where字句的連接順序：oracle采用自下而上的順序解析where字句，根據(jù)這個原理，表之間的連接必須寫在其他where條件之前，那些可以過濾掉大量記錄的條件必須寫在where字句的末尾，例如：低效：select * from table_name t where col15000 and col2=00102 and 25(select count(1) from table_name where code=t.code);高效：select * from table_name t where 255000 and col2=00102;刪除全表時，用truncate替代delete同時注意truncate只能在刪除全表時使用，因為truncate是ddl而不是dml。例如刪除一個100萬行的數(shù)據(jù)。Truncate table table_name比delete from teble_name至少快1000倍。盡量多使用commit。只要有可能就在程序中對每個delete、insert、update操作盡量多使用commit，這樣系統(tǒng)性能會因為commit所釋放的資源二大大提高用exit替代in，可以提高查詢的效率低效：select * from table_name where com_code not in（select code from table_name2 where col1=10101）;高效：select * from table_name where not exits (select code from table_name2 where code = table__code and col1=10101)優(yōu)化group by 提高group by語句的效率，可以將不需要的記錄在group by之前過濾掉。例如低效：select col1 ,avg(col2) from table_name group by col1 having col1=10101 or col1=10102;高效：select col1,avg(col2) from table_name where col1=10101 or col2=10102 group by col1;避免使用having字句 Having只會在檢索出所有記錄之后才會對結果集進行過濾，這個處理需要排序、統(tǒng)計等操作。如果能通過where字句限制記錄的數(shù)目，那就能減少這方面的開銷有條件的使用union-all替代union：這樣做會使效率提高3到5倍在含有子查詢的SQL語句中，要注意減少對表的查詢例如低效：select sum(col1) from table_name1 where col2 =(select col2 from table_name2 where code=10101) and pp=(select pp from table_name2 where code =10101); 高效：select sum(col1) from table_name where (col2,pp)=(select col2,pp from table_name2 where code=10101);Update多個column同樣適用select字句中盡量避免使用*當你想在select字句中 列出所有的column時，使用動態(tài)SQL列引用*是一個比較方便的方法，不幸的是，這是一個非常低效的方法，實際上，oracle在解析的過程中，會將*依次轉換為所有的列名，這個工作是通過查詢數(shù)據(jù)字典完成的，這意味著將耗費更多的時間。一些函數(shù)的使用技巧雖然在SQL中盲目引用函數(shù)會使性能下降，但如果正確使用合適的函數(shù)不僅會使SQL可讀性加強，而且能對SQL性能得到提高，使復雜的查詢能很方便地實現(xiàn)例如：有如下學生成績表（表名稱 grade姓名分數(shù)張三78李四92王五67趙六88錢七91陳八30我們的需求：90分或90以上為優(yōu)8089為良6079為中60以下為差獲得以下數(shù)據(jù) 姓名 分數(shù) 等級低效的做法：select name，grade，優(yōu) from grade where grade=90Union allselect name，grade，良 from grade where grade=80 and grade=60 and grade 80 union allselect name，grade，優(yōu) from grade whe