杜伟业 ( Richard To) Richardto@leccotech 技术总监 灵高科研有限公司

1. DM10:Sybase SQL Expert 12.5新一代智能SQL语句优化技术The New Generation of Intelligent SQL Tuning Technology 杜伟业 ( Richard To) Richardto@leccotech.com 技术总监 灵高科研有限公司

2. 什么是反应时间 1/10秒是用户认为系统能够立即反应的极限 1 秒是用户觉得没有被中断的极限 10秒是用户能将注意力继续集中在与计算机的对话上的时间极限 引用自Jakob Nielsen的 “Usability Engineering”第五章 (Morgan Kaufmann, San Francisco出版)

3. 我们可以关注的地方 • 可调整的部分: • 硬件 • 操作系统 • 数据库 • 网络 • 应用程序 许多人在试图提高数据库应用程序性能时只将注意力放在了前三项：硬件、操作系统、数据库…

4. 数据库系统各部分对性能的影响 Wrong Focus 网络 硬件 操作系统 数据库 应用程序

5. 性能的挑战 最 少 60% 的 数 据 库 性 能 问 题 是 由 应 用 程 序 引 起 的 其它 应用程序

6. 我们可以优化什么? • 数据库设计问题 • 编程问题 • 索引问题 • SQL问题

7. 什么是 SQL ? (结构化查询语言) • 访问和更新数据 • 对应用程序的性能产生最重大的影响 • SQL语句消耗70%到90%的数据库系统资源 • SQL语句可以有多种写法 • 易学，但难以精通 • 需要考虑在数据和系统环境变化后仍能达到最佳化

8. 服务器收到SQL语句 SQL语句解析 变量赋值 执行 SQL 数据库的优化器决定执行路径 Data 数据库是如何处理SQL语句的?

9. 它是否尝试了所有的的途 径来重写你的SQL语句? 成本的估计有多精确? 数据库的优化器是如何工作的? 执行计划1 内部重写并产生多种执行计划 成本估计 执行计划2 SQL 执行计划3 执行计划1 成本=1000 执行计划2 成本=3000 执行计划3 成本= 500 执行

10. 优化器说明 什么是执行计划 SQL语法如何影响优化器的决定

11. 什么是SQL优化?

12. 解决这个问题的传统途径

13. 执行计划 iSQL 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.

14. IO和时间统计信息 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. 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

15. Traceon 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 ….. DBCC TRACEON (3604, 302, 310) GO SELECT * FROM EMPLOYEE WHERE EMP_ID > 73712 GO

16. EXIST or IN 使用 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)

17. 表连接与子查询 通情况下表连接(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 */

18. Joins — Change Table Driving Path 如果你确实知道问题所在，可以强制使用一个特殊的表操纵路径( 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)

19. Use Indexes 要使用索引就不能在该索引字段上进行操作 源语句 /* 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

20. Forces 学习使用强制选项(Forces Options) SET FORCEPLAN ON INDEX hint PARALLEL hint SET SORT_MERGE ON SET JTC ON SET TABLE COUNT

21. Joins — Change Table Driving Path 使用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

22. 强制索引(Force Index) SELECT * FROM EMPLOYEE WHERE EMP_ID > 73712 QUERY PLAN FOR STATEMENT 1 (at line 1). STEP 1 The type of query is SELECT. FROM TABLE EMPLOYEE Nested iteration. Index : EMP_ID_INX Forward scan. Positioning by key. Keys are: EMP_ID ASC Using I/O Size 2 Kbytes for index leaf pages. With LRU Buffer Replacement Strategy for index leaf pages. 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): 23382. 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. (INDEXEMP_ID_INX) 备注： 仅在事先知道使用使用索引 x 将会较快的情况下采用 Force Index

23. 变量使用上的普遍问题 SELECT * FROM employee WHERE (emp_id > @range_lower OR @range_lower IS NULL) AND (emp_id < @range_upper OR @range_upper IS NULL) 使用配合输入范围边界的索引范围搜索(index range search) SELECT * FROM employee WHERE emp_id > ISNULL(@range_lower , min of emp_id) AND emp_id < ISNULL(@range_upper, max of emp_id)

24. 复杂的SQL转化 SELECT * FROM A WHERE EXISTS (SELECT ‘x’ FROM B WHERE A.key = B.key) 语义上等同于: • SELECT * • FROM A a1 • WHERE EXISTS IN (SELECT ‘x’ • FROM A a2, B • WHERE a1.key = B.key • AND a1.Unique_Key = a2.Unique_Key)

25. 这条SQL语句有多少个不同的等价写法? SELECT * FROM DEPARTMENT WHERE DPT_ID IN (SELECT EMP_DEPT FROM EMPLOYEE WHERE EMP_ID > 50000) 更多的信息： DEPARTMENTEMPLOYEE 记录数 = 410 记录数 = 18370 索引 - DPT_ID 索引 - EMP_ID

26. 最新的SQL优化技术 基于人工智能(A.I.)的 SQL 转换技术 使用人工智能(A.I.)来重写 SQL语句

27. ESR 人工智能引擎 Sybase SQL Expert 12.5 使用的技术 外部SQL重写 (External SQL Rewriter --ESR) 优化的 SQL 1 优化的 SQL 2 不良的SQL 优化的 SQL 3 优化的 SQL 4 优化的 SQL 5 优化的 SQL 6 源代码 最好的 SQL

28. SQL1 SQL2 SQL3 ... SQLn SQL1n SQLn1 SQLn2 SQLnn SQL11 SQL12 SQL13 … SQLn3 … SQL111 SQL112 SQL113 ... SQL11n SQLnn1 SQLnn2 SQLnn3 ... SQLnnn 人工智能( A.I.) SQL转换技术 Feedback Searching Engine 反馈式搜索引擎 SQL 优化后的 SQL

29. 什么是递归的 SQL 转换 (Recursive SQL Transformation)? 使用2 个转换规则 SELECT * FROM A WHERE A.C1IN (SELECT B.C1 FROM B WHERE EXISTS (SELECT ‘x’ FROM C WHERE B.C2=C.C2 )) SELECT * FROM A WHEREEXISTS (SELECT ‘x’ FROM B WHERE EXISTS (SELECT ‘x’ FROM C WHERE B.C2=C.C2) WHERE A.C1=B.C1) EXISTS to IN SELECT * FROM A WHERE EXISTS (SELECT ‘x’ FROM B WHERE B.C2IN (SELECT C.C2 FROM C) AND A.C1=B.C1) IN to EXISTS SELECT * FROM A WHERE A.C1 IN (SELECT B.C1 FROM B WHERE B.C2 IN (SELECT C.C2 FROM C)) SELECT * FROM A WHERE A.C1IN (SELECT B.C1 FROM B WHERE EXISTS (SELECT ‘x’ FROM C WHERE B.C2=C.C2 )) SELECT * FROM A WHERE EXISTS (SELECT ‘x’ FROM B WHERE B.C2 IN (SELECT C.C2 FROM C) AND A.C1=B.C1) EXISTS to IN SELECT * FROM A WHERE A.C1 IN (SELECT B.C1 FROM B WHERE EXISTS (SELECT ‘x’ FROM C WHERE B.C2=C.C2)) SELECT * FROM A WHERE A.C1 IN (SELECT B.C1 FROM B WHERE B.C2 IN (SELECT C.C2 FROM C)) IN to EXISTS SELECT * FROM A WHERE EXISTS (SELECT ‘x’ FROM B WHERE EXISTS (SELECT ‘x’ FROM C WHERE B.C2=C.C2) AND A.C1=B.C1)

30. 1 秒 Exec. P1 Exec. P2 2.4 秒 1.3 秒 Exec. P3 SQL10 或 SQL12 SQL10 SQL12 源代码 0.3 秒 Exec. P4 0.8 秒 Exec. P5 SQL 优化技术 ESR 人工智能重写执行计划性能结果 SQL1 SQL2 SQL3 SQL4 SQL5 SQL6 SQL7 SQL8 SQL9 SQL10 SQL11 SQL12 SQL.. SQLN SQL 相同的结果

31. Which SQL is the Best? SQL2 SQL1

32. 在线执行计划帮助 SQL 比较器 SQL 优化器 边做边学式训练

33. 演示 2 边做边学式训练 SQL 比较器 & 执行计划帮助

34. Sybase 12.5 最新的技术 Sybase Abstract Plan

35. 什么是 Abstract Plan? 捕获查询的SQL语句并存放该查询的abstract plan在系统表Sysqueryplans中 提交给数据库的SQL语句会跟已存于数据库内的SQL语句进行比较，如果匹配，存于数据库的abstract plan 将会用来执行该查询。

36. Abstract Plan Sybase 基于 数据库统计信息 来产生执行计划 SQL 执行 优化器 基于保存的 abstract plan 来产生执行计划

37. 使用Abstract Plan • /* 创建abstract plan组demo1 */ • sp_add_qpgroup demo1 • Go • /* 保存abstract plan到组demo1 */ • set plan dump demo1 on • Go • /* 执行 SQL */ • Select * from A, B • /* 从组demo1加载计划(Plan) */ • set plan load demo1 on • Go • Execution Plan /* 下一次你重新执行同一条SQL语句 */ • QUERY PLAN FOR STATEMENT 1 (at line 1). • Optimized using an Abstract Plan (ID : 1989579095).

38. 保存SQL的计划(Plan) • /* 在组 demo1 下创建abstract plan */ • /* 以使用字段A.key2上的索引 */ • CREATE PLAN • “SELECT * FROM A WHERE A.Key1 = 1 AND A.Key2 = 2” • “( i_scan Index_key2 A ) • ( prop A • ( parallel 1 ) • ( prefetch 2 ) • ( lru ) • )” into demo1

39. 使用Abstract Plan的好处 • 即使没有源代码的情况下也可以对SQL语句进行优化 • 执行计划将不会受以下情况影响： • 数据库升级引起的数据库内部优化器的改变 • 新的ASE特性 • 调整选项的改变，如：并行深度(parallel degree)，表分区(table partitioning)和索引(indexing)

40. 示范 Abstract Plan 使用 Abstract Plan优化SQL

41. 使用 Abstract Plan优化SQL语句 SQL Expert

42. SQL 扫瞄器 程序 A …………………………………………………………………………………………………………………………….. …… SQL1 ………… SQL2 …….……….. SQL3 …..…… SQL4 ………………………….... SQL5 ...……………... ……………… SQL6 …………….. SQL7 ………………………………. SQL8.……………………………………… ….. SQL9 ……………………………………………… SQL10 ……………………………………………………….. ……………………………. SQL11 …………………………………….. SQL12 …………… SQL13 ……………….. …………… SQL14 ………………………… SQL15 ………………………………………………………………….. ………………………. SQL16 ……………………………… SQL17 ……………… SQL18 ……….………………… ……………………………………………………………………………………………………………………………… SQL3 SQL1 SQL2 SQL4 SQL5 SQL6 SQL7 SQL8 SQL9 SQL10 SQL11 SQL12 SQL13 SQL14 SQL15 SQL17 SQL16 SQL18 程序 B ……………………………………………………………………………………………………………………………... ……………………. SQL1 ………………………………….………. SQL2 ………………………………………...….. …………………………………….…… SQL3 …………….. SQL4 ……………………. SQL5 ...……………….…… …………….….. SQL6 …………………………… SQL7 ………………….…..…...………………………………….. ………………….…………….…. SQL8 ……………………….… SQL9…………… SQL10 ………………….…….. …..… SQL11 ………… SQL12 ………………………… SQL13 ………………………………..… SQL14 …...…….. .………………. SQL15 …………………… SQL16 …………………………………………..………………………… …………. …………………………………………………………………………………………………………………. SQL1 SQL2 SQL5 SQL3 SQL4 SQL7 SQL6 SQL8 SQL10 SQL9 SQL13 SQL12 SQL11 SQL14 SQL15 SQL16

43. SQL 扫瞄器 有问题的SQL语句 SQL 语法优化器 反馈式搜索引擎 定位有问题的SQL语句 数据库对象或监视结果 程序源代码 优化的 SQL 1 优化的 SQL 2 优化的 SQL 3 优化的 SQL 4 优化的 SQL 5 优化的 SQL 6

44. 演示 3 SQL 扫瞄器 定位有问题的SQL语句

45. 高质量 VS 高生产力 高生产力 高质量

46. 谢 谢 杜伟业 ( Richard To) Richardto@leccotech.com 技术总监 灵高科研有限公司