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

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