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

SQL優(yōu)化原則和技巧,撰寫人:石霖,能使查詢加快的途徑,索引的引用 SQL的優(yōu)化 一些函數(shù)的使用技巧,索引的引用（1）,當(dāng)插入的數(shù)據(jù)為數(shù)據(jù)表中的記錄數(shù)量的10%以上，首先需要刪除該表的索引來提高數(shù)據(jù)的插入效率，當(dāng)數(shù)據(jù)插入后，再建立索引。,索引的引用（2）,避免在索引列上使用函數(shù)或計算，在where子句中，如果索引是函數(shù)的一部分，優(yōu)化器將不再使用索引而使用全表掃描。如： 低效： select * from report_sale_account where hsjj*10 1000; 高效： select * from report_sale_account where hsjj 1000/10;,索引的引用（3）,盡量避免在索引列上使用not和 “!=”和“”，索引只能告訴什么存在于表中，而不能告訴什么不存在于表中，當(dāng)數(shù)據(jù)庫遇到not 和 “!=” 和“”時，就會停止使用索引而去執(zhí)行全表掃描。,索引的引用（4）,請務(wù)必注意，檢索中不要對索引列進(jìn)行處理，如：TRIM，TO_DATE，類型轉(zhuǎn)換等操作，破壞索引，使用全表掃描，影響SQL執(zhí)行效率,索引的引用（5）,避免在索引列上使用IS NULL和IS NOT NULL 避免在索引中使用任何可以為空的列，ORACLE將無法使用該索引 對于單列索引，如果列包含空值，索引中將不存在此記錄； 對于復(fù)合索引，如果每個列都為空，索引中同樣不存在此記錄。如果至少有一個列不為空，則記錄存在于索引中 因為空值不存在于索引列中，所以WHERE子句中對索引列進(jìn)行空值比較將使ORACLE停用該索引,索引的引用（6）,索引列上“=”代替“” 低效：select * from report_sale_account where hsjj 10; 高效：select * from report_sale_account where hajj =10.000000000000001;,SQL的優(yōu)化技巧 （1）,Where 子句中的連接順序：oracle采用自下而上的順序解析where子句，根據(jù)這個原理，表之間的連接必須寫在其他where條件之前，那些可以過濾掉大量記錄的條件必須寫在where子句的末尾。,SQL的優(yōu)化技巧 （1）,例如 低效：select * from report_sale_account e where hsje5000 and dzxl = 000001 and 255000 and dzxl = 000001;,SQL的優(yōu)化技巧 （2）,刪除全表時，用truncate 替代 delete,同時注意truncate只能在刪除全表時適用，因為truncate是ddl而不是dml。 例如刪除掉一個100萬行的數(shù)據(jù)。 Truncate table report_sale_account; 比delete from report_sale_account;至少快1000倍。,SQL的優(yōu)化技巧 （3）,盡量多使用commit： 只要有可能就在程序中對每個delete、insert、update操作盡量多使用commit,這樣系統(tǒng)性能會因為commit所釋放的資源而大大提高。,SQL的優(yōu)化技巧 （4）,用exists替代in ，可以提高查詢的效率。 低效 SELECT * FROM REPORT_SALE_ACCOUNT WHERE COM_CODE NOT IN (SELECT CODE FROM BASEINFO_GOODS WHERE DZXL = 000001) 高效 SELECT * FROM REPORT_SALE_ACCOUNT WHERE NOT EXISTS (SELECT CODE FROM BASEINFO_GOODS WHERE CODE = REPORT_SALE_ACCOUNT.COM_CODE AND DZXL = 000001),SQL的優(yōu)化技巧 （5）,優(yōu)化group by 提高group by語句的效率，可以將不需要的記錄在group by之前過濾掉。,SQL的優(yōu)化技巧 （5）,例如： 低效：select dzxl, avg(hsje) from report_sale_account group by dzxl having dzxl = 000001 or dzxl =000002; 高效： select dzxl, avg(hsje) from report_sale_account where dzxl = 000001 or dzxl =000002 group by dzxl;,SQL的優(yōu)化技巧 （5）,避免使用HAVING子句，HAVING只會在檢索出所有記錄之后才對結(jié)果集進(jìn)行過濾，這個處理需要排序、統(tǒng)計等操作。如果能通過WHERE子句限制記錄的數(shù)目，那就能減少這方面的開銷。,SQL的優(yōu)化技巧 （6）,有條件的使用union-all 替代 union：這樣做效率會提高3到5倍。,SQL的優(yōu)化技巧 （7）,在含有子查詢的SQL語句中，要特別注意減少對表的查詢,SQL的優(yōu)化技巧 （7）,例如： 低效 SELECT SUM(HSJE) FROM REPORT_SALE_ACCOUNT WHERE DZXL = (SELECT DZXL FROM BASEINFO_GOODS WHERE CODE = 0001) AND PP =(SELECT PP FROM BASEINFO_GOODS WHERE CODE = 0001) 高效 SELECT SUM(HSJE) FROM REPORT_SALE_ACCOUNT WHERE (DZXL, PP) = (SELECT DZXL,PP FROM BASEINFO_GOODS WHERE CODE = 0001) Update多個Column同樣適用于以上的例子。,SQL的優(yōu)化技巧 （8）,SELECT子句中避免使用* 當(dāng)你想在SELECT子句中列出所有的COLUMN時，使用動態(tài)SQL列引用*是一個方便的方法，不幸的是，這是一個非常低效的方法，實際上，ORACLE在解析的過程中，會將*依次轉(zhuǎn)換成所有的列名，這個工作是通過查詢數(shù)據(jù)字典完成的，這意味著將耗費更多的時間。,一些函數(shù)的使用技巧,雖然在SQL中盲目引用函數(shù)會使性能降低，但如果正確使用合適的函數(shù)不僅會使SQL可讀性加強(qiáng)，并且能對SQL性能得到提高，使復(fù)雜的查詢能很方便地實現(xiàn)。,一些函數(shù)的使用技巧,舉例說明 有如下學(xué)生成績表(表名為TABLE),一些函數(shù)的使用技巧,我們的需求： 90分或90分以上為優(yōu) 8089分良 6079分為中 60分以下為差 獲得以下數(shù)據(jù) 姓名 分?jǐn)?shù) 等級,一些函數(shù)的使用技巧,低效的做法： Select 姓名,分?jǐn)?shù),優(yōu) From TABLE Where 分?jǐn)?shù)=90 UNION ALL Select 姓名,分?jǐn)?shù),良 From TABLE Where 分?jǐn)?shù)=80 and 分?jǐn)?shù)=60 and 分?jǐn)?shù)80 UNION ALL Select 姓名,分?jǐn)?shù),差 From TA