商务智能SQL语句优化技术教材34631

,Click to edit Master title style,Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,*,Where the experts go for performance,DM10:Sybase SQL Expert 12.5,新一代智能,SQL,语句优化技术,The New Generation of Intelligent SQL Tuning Technology,杜伟业(,Richard To),R,技术总监,灵高科研有限公司,什么是反应时间,1/10秒是用户认为系统能够立即反应的极限,1 秒是用户觉得没有被中断的极限,10秒是用户能将注意力继续集中在与计算机的对话上的时间极限,引用自,Jakob Nielsen,的“,Usability Engineering”,第五章,(,Morgan Kaufmann,San Francisco,出版),我们可以关注的地方,可调整的部分:,硬件,操作系统,数据库,网络,应用程序,许多人在试图提高数据库应用程序性能时只将注意力放在了前三项：硬件、操作系统、数据库,数据库系统各部分对性能的影响,应用程序,数据库,操作系统,硬件,网络,Wrong Focus,应用程序,其它,最 少 60%的 数 据 库 性 能 问 题 是 由 应 用 程 序 引 起 的,性能的挑战,我们可以优化什么?,数据库设计问题,编程问题,索引问题,SQL,问题,什么是,SQL?,(,结构化查询语言,),访问和更新数据,对应用程序的性能产生最重大的影响,SQL,语句消耗,70%,到,90%,的数据库系统资源,SQL,语句可以有多种写法,易学,，,但难以精通,需要考虑在数据和系统环境变化后仍能达到最佳化,服务器收到,SQL,语句,SQL,SQL,语句解析,数据库的优化器决定执行路径,变量赋值,执行,Data,数据库是如何处理,SQL,语句的?,SQL,数据库的优化器是如何工作的?,内部重写并产生多种执行计划,执行计划1,执行计划2,执行计划3,成本估计,执行计划1 成本=1000,执行计划2 成本=3000,执行计划3 成本=500,执行,它是否尝试了所有的的途,径来重写你的,SQL,语句?,成本的估计有多精确?,优化器说明,什么是执行计划,SQL,语法如何影响优化器的决定,什么是,SQL,优化?,解决这个问题的传统途径,执行计划,SET SHOWPLAN ON,GO,SELECT*,FROM EMPLOYEE,WHERE EMP_ID 73712,GO,SET SHOWPLAN OFFGO,QUERY PLAN FOR STATEMENT 1(at line 1).,STEP 1,The type of query is SELECT.,FROM TABLE,EMPLOYEE,Nested iteration.,Table Scan.,Forward scan.,Positioning at start of table.,Using I/O Size 2 Kbytes for data pages.,With LRU Buffer Replacement Strategy for data pages.,Total estimated I/O cost for statement 1(at line 1):20600.,iSQL,IO,和时间统计信息,SET STATISTICS IO ON,GO,SET STATISTICS TIME ON,GO,SELECT*,FROM EMPLOYEE,WHERE EMP_ID 73712,GO,SET STATISTICS IO OFF,GO,SET STATISTICS TIME OFF,GO,iSQL,Server Message:Number 3631,Severity 10,Line 1:,Total actual I/O cost for this command:0.,Total writes for this command:0,Server Message:Number 3631,Severity 10,Line 1:,Total actual I/O cost for this command:0.,Total writes for this command:0,Execution Time 0.,SQL Server cpu time:0 ms.SQL Server elapsed time:3614 ms.,Parse and Compile Time 0.,SQL Server cpu time:0 ms.,Table:EMPLOYEE scan count 1,logical reads:(regular=1030 apf=0 total=1030),physical reads:(regular=0 apf=0 total=0),apf IOs used=0,Server Message:Number 3631,Severity 10,Line 1:,Total actual I/O cost for this command:2060.,Total writes for this command:0,Execution Time 16.,SQL Server cpu time:1600 ms.SQL Server elapsed time:7730 ms.,(18371 rows affected),Parse and Compile Time 0.,SQL Server cpu time:0 ms.,Execution Time 0.,SQL Server cpu time:0 ms.SQL Server elapsed time:0 ms.,Parse and Compile Time 0.,SQL Server cpu time:0 ms.,Traceon,DBCC TRACEON(3604,302,310),GO,SELECT*,FROM EMPLOYEE,WHERE EMP_ID 73712,GO,iSQL,Beginning selection of qualifying indexes for table EMPLOYEE,varno=0,objectid 1593824790.,The table(Allpages)has 18373 rows,1030 pages,Data Page Cluster Ratio 0.998890,Table scan cost is 18373 rows,1030 pages,using no data prefetch(size 2K I/O),in data cache default data cache(cacheid 0)with LRU replacement,Selecting best index for the SEARCH CLAUSE:,EMPLOYEE.EMP_ID 73712.000000,Estimated selectivity for EMP_ID,selectivity=0.999946.,Estimating selectivity of index EMPLOYEE_1961957491,indid 1,scan selectivity 0.999946,filter selectivity 0.999946,18372 rows,1032 pages,index height 2,Data Row Cluster Ratio 1.000000,Index Page Cluster Ratio 0.000000,Data Page Cluster Ratio 0.998890,.,使用,EXIST,还是,IN？,SELECT*,FROM A,WHERE A.CITY IN(SELECT B.CITY FROM B),SELECT*,FROM A,WHERE EXISTS(SELECT X FROM B,WHERE B.CITY=A.CITY),EXIST or IN,表连接与子查询,通情况下表连接(,Table join),要好过子查询(,sub-query),源语句,SELECT*,FROM A,WHERE A.CITY IN(SELECT B.CITY,FROM B),重写后,SELECT A.*,FROM A,B,WHERE A.CITY=B.CITY,/*if B.CITY is unique in B*/,如果你确实知道问题所在，可以强制使用一个特殊的表操纵路径(,driving path,)。,源语句,SELECT*,FROM A,B,WHERE A.STATE=B.STATE,重写后的语句,/*禁止使用,B,的索引来改变表操纵路径(,B drive A)*/,SELECT*,FROM A,B,WHERE A.STATE=,ISNULL(,B.STATE,B.STATE),Joins Change Table Driving Path,要使用索引就不能在该索引字段上进行操作,源语句/*,A.key1,上的索引被使用*/,SELECT*,FROM A,WHERE A.Key1=ver1,AND A.Key2=ver2,重写后/*禁止使用字段,A.key1,上的索引，使用字段,A.Key2,上的索引*/,SELECT*,FROM A,WHERE ISNULL(A.Key1,A.key1)=ver1,AND A.Key2=ver2,Use Indexes,学习使用强制选项,(,Forces Options,),SET FORCEPLAN ON,INDEX hint,PARALLEL hint,SET SORT_MERGE ON,SET JTC ON,SET TABLE COUNT,Forces,使用,FORCEPLAN,强制选项强制使用特殊的表操纵路径(,driving path)。,原来的方法,/*禁止使用,B,的索引来改变表操纵路径(,B drive A)*/,SELECT*,FROM A,B,WHERE,A,.STATE=,ISNULL(,B,.STATE,B,.STATE),用,FORCEPLAN,重写后,SET FORCEPLAN ON,GO,SELECT*,FROM B,A,WHERE,A,.STATE=,B,.STATE,GO,Joins Change Table Driving Path,强制索引,(,Force Index),SELECT*,FROM EMPLOYEE,WHERE EMP_ID 73712,Force Index,备注：,仅在事先知道使用使用索引,x,将会较快的情况下采用,(,INDEX,EMP_ID_INX,),QUERY PLAN FOR STATEMENT 1(at line 1).,STEP 1,The type of query is SELECT.,FROM TABLE,EMPLOYEE,Nested iteration.,Table Scan.,Forward scan.,Positioning at start of table.,Using I/O Size 2 Kbytes for data pages.,With LRU Buffer Replacement Strategy for data pages.,Total estimated I/O cost for statement 1(at line 1):20600.,QUERY PLAN FOR STATEMENT 1(at line 1).,STEP 1,The type of query is SELECT.,FROM TABLE,EMPLOYEE,Nested iterat