《SQL优化原则和技巧课件》由会员分享，可在线阅读，更多相关《SQL优化原则和技巧课件（26页珍藏版）》请在金锄头文库上搜索。
1、SQLSQL优化原则和技巧优化原则和技巧撰写人撰写人: :石霖石霖SQLSQL优化原则和技巧优化原则和技巧能使查询加快的途径n n索引的引用n nSQL的优化n n一些函数的使用技巧SQLSQL优化原则和技巧优化原则和技巧索引的引用（1）n n当插入的数据为数据表中的记录数量的10%以上，首先需要删除该表的索引来提高数据的插入效率，当数据插入后，再建立索引。 SQLSQL优化原则和技巧优化原则和技巧索引的引用（2）n n避免在索引列上使用函数或计算，在避免在索引列上使用函数或计算，在wherewhere子句中，如果索引是函数的一子句中，如果索引是函数的一部分，优化器将不再使用索引而使用部分，优
2、化器将不再使用索引而使用全表扫描。如：全表扫描。如： 低效：低效：select * from report_sale_account select * from report_sale_account where hsjj*10 1000; where hsjj*10 1000;高效：高效：select * from report_sale_accountselect * from report_sale_account where hsjj 1000/10; where hsjj 1000/10; SQLSQL优化原则和技巧优化原则和技巧索引的引用（3）n n尽量避免在索引列上使用not和
3、“!=”和“”，索引只能告诉什么存在于表中，而不能告诉什么不存在于表中，当数据库遇到not 和 “!=” 和“”时，就会停止使用索引而去执行全表扫描。 SQLSQL优化原则和技巧优化原则和技巧索引的引用（4）n n请务必注意，检索中不要对索引列进行处理，如：TRIM，TO_DATE，类型转换等操作，破坏索引，使用全表扫描，影响SQL执行效率 SQLSQL优化原则和技巧优化原则和技巧索引的引用（5）l l避免在索引列上使用避免在索引列上使用IS NULLIS NULL和和IS NOT IS NOT NULLNULLn n避免在索引中使用任何可以为空的列，避免在索引中使用任何可以为空的列，ORAC
4、LEORACLE将无法使用该索引将无法使用该索引n n对于单列索引，如果列包含空值，索引中将对于单列索引，如果列包含空值，索引中将不存在此记录；不存在此记录；n n对于复合索引，如果每个列都为空，索引中对于复合索引，如果每个列都为空，索引中同样不存在此记录。如果至少有一个列不为同样不存在此记录。如果至少有一个列不为空，则记录存在于索引中空，则记录存在于索引中n n因为空值不存在于索引列中，所以因为空值不存在于索引列中，所以WHEREWHERE子子句中对索引列进行空值比较将使句中对索引列进行空值比较将使ORACLEORACLE停停用该索引用该索引SQLSQL优化原则和技巧优化原则和技巧索引的引用
5、（6）l l索引列上索引列上“=”“=”代替代替“”“”低效：select * from report_sale_account where hsjj 10;高效：select * from report_sale_account where hajj =10.0001; SQLSQL优化原则和技巧优化原则和技巧SQL的优化技巧 （1）n nWhere 子句中的连接顺序：oracle采用自下而上的顺序解析where子句，根据这个原理，表之间的连接必须写在其他where条件之前，那些可以过滤掉大量记录的条件必须写在where子句的末尾。 SQLSQL优化原则和技巧优化原则和技巧SQL的优化技巧
6、（1）n n例如例如低效：低效：select * from report_sale_account e select * from report_sale_account e where hsje5000 where hsje5000 and dzxl = 000001 and dzxl = 000001 and 25(select count(*) and 25(select count(*) from report_sale_account from report_sale_account where code=e.code);where code=e.code);高效：高效：select
7、 * from report_sale_account e select * from report_sale_account e where 25(select count (*) where 255000 and hsje5000 and dzxl = 000001;and dzxl = 000001;SQLSQL优化原则和技巧优化原则和技巧SQL的优化技巧 （2）n n删除全表时，用删除全表时，用truncate truncate 替代替代 delete, delete,同时注意同时注意truncatetruncate只能在删除全表时只能在删除全表时适用，因为适用，因为truncatet
8、runcate是是ddlddl而不是而不是dmldml。例如删除掉一个例如删除掉一个100100万行的数据。万行的数据。Truncate table report_sale_account;Truncate table report_sale_account; 比比delete from report_sale_account;delete from report_sale_account;至至少快少快10001000倍。倍。 SQLSQL优化原则和技巧优化原则和技巧SQL的优化技巧 （3）n n尽量多使用commit：只要有可能就在程序中对每个delete、insert、update操作尽量
9、多使用commit,这样系统性能会因为commit所释放的资源而大大提高。 SQLSQL优化原则和技巧优化原则和技巧SQL的优化技巧 （4）n n用用existsexists替代替代in in ，可以提高查询的效率。，可以提高查询的效率。低效低效SELECT *SELECT *FROM REPORT_SALE_ACCOUNTFROM REPORT_SALE_ACCOUNT WHERE COM_CODE WHERE COM_CODE NOT IN (SELECT CODE NOT IN (SELECT CODE FROM BASEINFO_GOODS FROM BASEINFO_GOODS WH
10、ERE DZXL = 000001) WHERE DZXL = 000001)高效高效SELECT *SELECT *FROM REPORT_SALE_ACCOUNTFROM REPORT_SALE_ACCOUNT WHERE NOT EXISTS WHERE NOT EXISTS (SELECT CODE FROM BASEINFO_GOODS (SELECT CODE FROM BASEINFO_GOODS WHERE CODE = REPORT_SALE_ACCOUNT.COM_CODE WHERE CODE = REPORT_SALE_ACCOUNT.COM_CODE AND DZXL
11、 = 000001) AND DZXL = 000001)SQLSQL优化原则和技巧优化原则和技巧SQL的优化技巧 （5）n n优化group by提高group by语句的效率，可以将不需要的记录在group by之前过滤掉。 SQLSQL优化原则和技巧优化原则和技巧SQL的优化技巧 （5）n n例如：例如：低效：低效：select dzxl, select dzxl, avg(hsje) avg(hsje) from report_sale_account from report_sale_account group by dzxl group by dzxl having dzxl =
12、000001 having dzxl = 000001 or dzxl =000002; or dzxl =000002;高效：高效： select dzxl, select dzxl, avg(hsje) avg(hsje) from report_sale_account from report_sale_account where dzxl = 000001 where dzxl = 000001 or dzxl =000002 or dzxl =000002 group by dzxl; group by dzxl; SQLSQL优化原则和技巧优化原则和技巧SQL的优化技巧 （5）n
13、n避免使用HAVING子句，HAVING只会在检索出所有记录之后才对结果集进行过滤，这个处理需要排序、统计等操作。如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。 SQLSQL优化原则和技巧优化原则和技巧SQL的优化技巧 （6）n n有条件的使用union-all 替代 union：这样做效率会提高3到5倍。 SQLSQL优化原则和技巧优化原则和技巧SQL的优化技巧 （7）n n在含有子查询的SQL语句中，要特别注意减少对表的查询 SQLSQL优化原则和技巧优化原则和技巧SQL的优化技巧 （7）n n例如：例如：低效低效SELECT SUM(HSJE) FROM REPORT_
14、SALE_ACCOUNTSELECT SUM(HSJE) FROM REPORT_SALE_ACCOUNTWHERE DZXL = (SELECT DZXLWHERE DZXL = (SELECT DZXL FROM BASEINFO_GOODS FROM BASEINFO_GOODS WHERE CODE = 0001) WHERE CODE = 0001) AND PP =(SELECT PP AND PP =(SELECT PP FROM BASEINFO_GOODS FROM BASEINFO_GOODS WHERE CODE = 0001) WHERE CODE = 0001)高效高
15、效SELECT SUM(HSJE) FROM REPORT_SALE_ACCOUNTSELECT SUM(HSJE) FROM REPORT_SALE_ACCOUNT WHERE (DZXL, PP) = (SELECT DZXL,PP WHERE (DZXL, PP) = (SELECT DZXL,PP FROM BASEINFO_GOODS FROM BASEINFO_GOODS WHERE CODE = 0001) WHERE CODE = 0001) UpdateUpdate多个多个ColumnColumn同样适用于以上的例子。同样适用于以上的例子。 SQLSQL优化原则和技巧优化原则
16、和技巧SQL的优化技巧 （8）n nSELECTSELECT子句中避免使用子句中避免使用* 当你想在当你想在SELECTSELECT子句中列出所有的子句中列出所有的COLUMNCOLUMN时，使用动态时，使用动态SQLSQL列引用列引用*是一个方便的方法，不幸的是，这是一个方便的方法，不幸的是，这是一个非常低效的方法，实际上，是一个非常低效的方法，实际上，ORACLEORACLE在解析的过程中，会将在解析的过程中，会将*依次转换成所有的列名，这个工作是依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着通过查询数据字典完成的，这意味着将耗费更多的时间。将耗费更多的时间。SQLSQL
17、优化原则和技巧优化原则和技巧一些函数的使用技巧n n虽然在SQL中盲目引用函数会使性能降低，但如果正确使用合适的函数不仅会使SQL可读性加强，并且能对SQL性能得到提高，使复杂的查询能很方便地实现。SQLSQL优化原则和技巧优化原则和技巧一些函数的使用技巧n n举例说明举例说明有如下学生成绩表有如下学生成绩表( (表名为表名为TABLE)TABLE)姓名姓名分数分数张三张三7878李四李四9292王五王五6767赵六赵六8888钱七钱七9191陈八陈八3030SQLSQL优化原则和技巧优化原则和技巧一些函数的使用技巧n n我们的需求：l l9090分或分或9090分以上为优分以上为优l l80
18、808989分良分良l l60607979分为中分为中l l6060分以下为差分以下为差l l获得以下数据获得以下数据n n姓名姓名n n分数分数n n等级等级SQLSQL优化原则和技巧优化原则和技巧一些函数的使用技巧n n低效的做法：低效的做法：Select Select 姓名姓名, ,分数分数, ,优优 From TABLE Where From TABLE Where 分数分数=90=90UNION ALLUNION ALLSelect Select 姓名姓名, ,分数分数, ,良良 From TABLE Where From TABLE Where 分数分数=80 and =80 an
19、d 分数分数90=60 and =60 and 分数分数8080UNION ALLUNION ALLSelect Select 姓名姓名, ,分数分数, ,差差 From TABLE Where From TABLE Where 分数分数60;60;SQLSQL优化原则和技巧优化原则和技巧一些函数的使用技巧n n高效的做法：高效的做法：Select Select 姓名姓名, , 分数分数, , decode(sign( decode(sign(分数分数 - 90), - 90), -1, -1, decode(sign( decode(sign(分数分数 - 80), - 80), -1, -1, decode(sign( decode(sign(分数分数 - 60), - 60), -1, -1, 差差, , 中中),), 良良),), 优优) ) from table;from table;SQLSQL优化原则和技巧优化原则和技巧完完SQLSQL优化原则和技巧优化原则和技巧
