关于我姓名 : 葛云杰网名 :Dylan Oracle OCM Oracle 用户组年轻专家中国 OCM 联盟成员山东 Oracle 用户组联合创始人齐鲁 IT 联盟联合创始人软件研发工程师具有十年以上

Size: px

Start display at page:

Download "关于我姓名 : 葛云杰网名 :Dylan Oracle OCM Oracle 用户组年轻专家中国 OCM 联盟成员山东 Oracle 用户组联合创始人齐鲁 IT 联盟联合创始人软件研发工程师具有十年以上"

涎爵冉施
7 years ago
Views:

1 分享技术分享快乐葛云杰 (Fiyo) 电话 : 邮箱

2 关于我姓名 : 葛云杰网名 :Dylan Oracle OCM Oracle 用户组年轻专家中国 OCM 联盟成员山东 Oracle 用户组联合创始人齐鲁 IT 联盟联合创始人软件研发工程师具有十年以上软件研发 / 设计经验参与了众多软件研发系统架构设计数据库运维项目, 积累了丰富的经验

3 开发人员 : 如果你是做数据库开发, 那本文的内容非常适合, 因为本文是从程序员的角度来谈数据库性能优化架构师 : 如果你已经是数据库应用的架构师, 那本文的知识你应该清楚 90% DBA( 数据库管理员 ): 大型数据库优化的知识非常复杂, 本文只是从程序员的角度来谈性能优化,DBA 除了需要了解这些知识外, 还需要深入数据库的内部体系架构来解决问题

4 要正确的优化 SQL, 我们需要快速定位能性的瓶颈点, 也就是说快速找到我们 SQL 主要的开销在哪里? 而大多数情况性能最慢的设备会是瓶颈点, 如下载时网络速度可能会是瓶颈点, 本地复制文件时硬盘可能会是瓶颈点, 为什么这些一般的工作我们能快速确认瓶颈点呢, 因为我们对这些慢速设备的性能数据有一些基本的认识, 如网络带宽是 2Mbps, 硬盘是每分钟 7200 转等等

6 这个优化法则归纳为 5 个层次 : 1 减少数据访问 ( 减少磁盘访问 ) 2 返回更少数据 ( 减少网络传输或磁盘访问 ) 3 减少交互次数 ( 减少网络传输 ) 4 减少服务器 CPU 开销 ( 减少 CPU 及内存开销 ) 5 利用更多资源 ( 增加资源 )

7 数据库访问优化详解减少数据访问返回更少的数据减少交互次数减少数据库服务器 CPU 运算利用更多的资源

8 数据库访问优化详解减少数据访问创建并使用正确的索引只通过索引访问数据优化 SQL 执行计划

9 减少数据访问创建并使用正确的索引索引会大大增加表记录的 DML 操作 (INSERT,UPDATE,DELETE) 开销, 正确的索引可以让性能提升 100,1000 倍以上, 不合理的索引也可能会让性能下降 100 倍

10 减少数据访问创建并使用正确的索引索引有哪些种类? 常见的索引有 B-TREE 索引位图索引全文索引, 位图索引一般用于数据仓库应用, 全文索引由于使用较少, 这里不深入介绍 B-TREE 索引包括很多扩展类型, 如组合索引反向索引函数索引等等 B-TREE 索引也称为平衡树索引 (Balance Tree), 它是一种按字段排好序的树形目录结构, 主要用于提升查询性能和唯一约束支持叶子节点内容 : 索引字段内容 + 表记录 ROWID 根节点, 分支节点内容 : 当一个数据块中不能放下所有索引字段数据时, 就会形成树形的根节点或分支节点

引包括很多扩展类型, 如组合索引反向索引函数索引等等 B-TREE 索引也称为平衡树索引 (Balance Tree), 它是一种按字段排好序

11 减少数据访问创建并使用正确的索引 SQL 什么条件会使用索引? 当字段上建有索引时, 通常以下情况会使用索引 : INDEX_COLUMN =? INDEX_COLUMN >? INDEX_COLUMN >=? INDEX_COLUMN <? INDEX_COLUMN <=? INDEX_COLUMN between? and? INDEX_COLUMN in (?,?,...,?) INDEX_COLUMN like? '%'( 后导模糊查询 ) T1. INDEX_COLUMN=T2. COLUMN1( 两个表通过索引字段关联 )

INDEX_COLUMN >=? INDEX_COLUMN <? INDEX_COLUMN <=? INDEX_COLUMN between? and?

12 减少数据访问创建并使用正确的索引 SQL 什么条件不会使用索引? 查询条件 INDEX_COLUMN <>? INDEX_COLUMN not in (?,?,...,?) function(index_column) =? INDEX_COLUMN + 1 =? INDEX_COLUMN 'a' =? INDEX_COLUMN like '%'? INDEX_COLUMN like '%'? '%' INDEX_COLUMN is null NUMBER_INDEX_COLUMN='12345' CHAR_INDEX_COLUMN=12345 a.index_column=a.column_1 不能使用索引原因不等于操作不能使用索引经过普通运算或函数运算后的索引字段不能使用索引含前导模糊查询的 Like 语法不能使用索引 B-TREE 索引里不保存字段为 NULL 值记录, 因此 IS NULL 不能使用索引 Oracle 在做数值比较时需要将两边的数据转换成同一种数据类型, 如果两边数据类型不同时会对字段值隐式转换, 相当于加了一层函数处理, 所以不能使用索引给索引查询的值应是已知数据, 不能是未知字段值

column_1 不能使用索引原因不等于操作不能使用索引经过普通运算或函数运算后的索引字段不能使用索引含前导模糊查询的 Like 语法不能使用索引 B-TREE 索引里不保存字段为 NULL 值记录, 因此

13 减少数据访问创建并使用正确的索引一般在什么字段上建索引? 主键及外键通常都要有索引, 其它需要建索引的字段应满足以下条件 : 1 字段出现在查询条件中, 并且查询条件可以使用索引 ; 2 语句执行频率高, 一天会有几千次以上 ; 3 通过字段条件可筛选的记录集很小, 那数据筛选比例是多少才适合? 这个没有固定值, 需要根据表数据量来评估, 以下是经验公式, 可用于快速评估 : 小表 ( 记录数小于行的表 ): 筛选比例 <10%; 大表 :( 筛选返回记录数 )<( 表总记录数 * 单条记录长度 )/10000/16 单条记录长度字段平均内容长度之和 + 字段数 *2

频率高, 一天会有几千次以上 ; 3 通过字段条件可筛选的记录集很小, 那数据筛选比例是多少才适合?

14 减少数据访问创建并使用正确的索引如何知道 SQL 是否使用了正确的索引? 简单 SQL 可以根据索引使用语法规则判断, 复杂的 SQL 不好办, 判断 SQL 的响应时间是一种策略, 但是这会受到数据量主机负载及缓存等因素的影响, 有时数据全在缓存里, 可能全表访问的时间比索引访问时间还少要准确知道索引是否正确使用, 需要到数据库中查看 SQL 真实的执行计划

15 减少数据访问创建并使用正确的索引索引对 DML(INSERT,UPDATE,DELETE) 附加的开销有多少? 这个没有固定的比例, 与每个表记录的大小及索引字段大小密切相关, 以下是一个普通表测试数据, 仅供参考 : 索引对于 Insert 性能降低 56% 索引对于 Update 性能降低 47% 索引对于 Delete 性能降低 29% 因此对于写 IO 压力比较大的系统, 表的索引需要仔细评估必要性, 另外索引也会占用一定的存储空间

仅供参考 : 索引对于 Insert 性能降低 56% 索引对于 Update 性能降低 47% 索引对于 Delete 性能降

16 数据库访问优化详解减少数据访问创建并使用正确的索引只通过索引访问数据优化 SQL 执行计划

17 减少数据访问只通过索引访问数据有些时候, 我们只是访问表中的几个字段, 并且字段内容较少, 我们可以为这几个字段单独建立一个组合索引, 这样就可以直接只通过访问索引就能得到数据, 一般索引占用的磁盘空间比表小很多, 所以这种方式可以大大减少磁盘 IO 开销如 :select id,name from company where type='2'; 引如果这个 SQL 经常使用, 我们可以在 type,id,name 上创建组合索 create index my_comb_index on company(type,id,name); 有了这个组合索引后,SQL 就可以直接通过 my_comb_index 索引返回数据, 不需要访问 company 表

:select id,name from company where type='2'; 引如果这个 SQL 经常使用, 我们可以在 type,id,name 上创建组合索 create index

18 数据库访问优化详解减少数据访问创建并使用正确的索引只通过索引访问数据优化 SQL 执行计划

19 减少数据访问优化 SQL 执行计划 SQL 执行计划是关系型数据库最核心的技术之一, 它表示 SQL 执行时的数据访问算法由于业务需求越来越复杂, 表数据量也越来越大, 程序员越来越懒惰,SQL 也需要支持非常复杂的业务逻辑, 但 SQL 的性能还需要提高, 因此, 优秀的关系型数据库除了需要支持复杂的 SQL 语法及更多函数外, 还需要有一套优秀的算法库来提高 SQL 性能

懒惰,SQL 也需要支持非常复杂的业务逻辑, 但 SQL 的性能还需要提高, 因此, 优秀的关系型数

20 数据库访问优化详解减少数据访问返回更少的数据减少交互次数减少数据库服务器 CPU 运算利用更多的资源

21 数据库访问优化详解返回更少的数据数据分页处理只返回需要的字段

22 返回更少的数据数据分页处理客户端 ( 应用程序或浏览器 ) 分页将数据从应用服务器全部下载到本地应用程序或浏览器, 在应用程序或浏览器内部通过本地代码进行分页处理优点 : 编码简单, 减少客户端与应用服务器网络交互次数缺点 : 首次交互时间长, 占用客户端内存适应场景 : 客户端与应用服务器网络延时较大, 但要求后续操作流畅, 如手机 GPRS, 超远程访问 ( 跨国 ) 等等

23 返回更少的数据数据分页处理应用服务器分页将数据从数据库服务器全部下载到应用服务器, 在应用服务器内部再进行数据筛选以下是一个应用服务器端 Java 程序分页的示例 : List list=executequery( select * from employee order by id ); Int count= list.size(); List sublist= list.sublist(10, 20); 优点 : 编码简单, 只需要一次 SQL 交互, 总数据与分页数据差不多时性能较好缺点 : 总数据量较多时性能较差适应场景 : 数据库系统不支持分页处理, 数据量较小并且可控

24 返回更少的数据数据分页处理数据库 SQL 分页采用数据库 SQL 分页需要两次 SQL 完成一个 SQL 计算总数量一个 SQL 返回分页后的数据优点 : 性能好缺点 : 编码复杂, 各种数据库语法不同, 需要两次 SQL 交互 Oracle 数据库一般采用 rownum 来进行分页, 常用分页语法有如下两种 : 直接通过 rownum 分页 : select * from (select a.*,rownum from (select * from product a where company_id=? order by status) a where rownum<=20) where rownum>10; 数据访问开销 = 索引 IO+ 索引全部记录结果对应的表数据 IO 采用 rowid 分页语法 : 优化原理是通过纯索引找出分页记录的 ROWID, 再通过 ROWID 回表返回数据, 要求内层查询和排序字段全在索引里 create index myindex on product(company_id,status); select b.* from (select * from (select a.*,rownum rn from (select rowid rid,status from product a where company_id=? order by status) a where rownum<=20) where rn>10) a, product b where a.rid=b.rowid; 数据访问开销 = 索引 IO+ 索引分页结果对应的表数据 IO

25 数据库访问优化详解返回更少的数据数据分页处理只返回需要的字段

26 返回更少的数据只返回需要的字段通过去除不必要的返回字段可以提高性能, 例 : 调整前 :select * from product where company_id=?; 调整后 :select id,name from product where company_id=?; 优点 : 1 减少数据在网络上传输开销 2 减少服务器数据处理开销 3 减少客户端内存占用 4 字段变更时提前发现问题, 减少程序 BUG 5 如果访问的所有字段刚好在一个索引里面, 则可以使用纯索引访问提高性能缺点 : 增加编码工作量

27 数据库访问优化详解减少数据访问返回更少的数据减少交互次数减少数据库服务器 CPU 运算利用更多的资源

28 数据库访问优化详解减少交互次数 batch DML In List 设置 Fetch Size 使用存储过程优化业务逻辑使用 ResultSet 游标处理记录

29 减少交互次数 batch DML 数据库访问框架一般都提供了批量提交的接口,jdbc 支持 batch 的提交处理方法当你一次性要往一个表中插入 1000 万条数据时, 如果采用普通的 executeupdate 处理, 那么和服务器交互次数为 1000 万次, 按每秒钟可以向数据库服务器提交次估算, 要完成所有工作需要 1000 秒如果采用批量提交模式,1000 条提交一次, 那么和服务器交互次数为 1 万次, 交互次数大大减少采用 batch 操作一般不会减少很多数据库服务器的物理 IO, 但是会大大减少客户端与服务端的交互次数, 从而减少了多次发起的网络延时开销, 同时也会降低数据库的 CPU 开销

30 数据库访问优化详解减少交互次数 batch DML In List 设置 Fetch Size 使用存储过程优化业务逻辑使用 ResultSet 游标处理记录

31 数据库访问优化详解减少交互次数 batch DML In List 设置 Fetch Size 使用存储过程优化业务逻辑使用 ResultSet 游标处理记录

32 减少交互次数 In List 很多时候我们需要按一些 ID 查询数据库记录, 我们可以采用一个 ID 一个请求发给数据库, 如下所示 : for :var in ids[] do begin select * from mytable where id=:var; end; 我们也可以做一个小的优化, 如下所示, 用 ID INLIST 的这种方式写 SQL: select * from mytable where id in(:id1,id2,...,idn); 通过这样处理可以大大减少 SQL 请求的数量, 从而提高性能那如果有个 ID, 那是不是全部放在一条 SQL 里处理呢? 答案肯定是否定的首先大部份数据库都会有 SQL 长度和 IN 里个数的限制, 如 ORACLE 的 IN 里就不允许超过 1000 个值

33 数据库访问优化详解减少交互次数 batch DML In List 设置 Fetch Size 使用存储过程优化业务逻辑使用 ResultSet 游标处理记录

34 减少交互次数设置 Fetch Size 当我们采用 select 从数据库查询数据时, 数据默认并不是一条一条返回给客户端的, 也不是一次全部返回客户端的, 而是根据客户端 fetch_size 参数处理, 每次只返回 fetch_size 条记录, 当客户端游标遍历到尾部时再从服务端取数据, 直到最后全部传送完成所以如果我们要从服务端一次取大量数据时, 可以加大 fetch_size, 这样可以减少结果数据传输的交互次数及服务器数据准备时间, 提高性能 String vsql ="select * from t_employee"; PreparedStatement pstmt = conn.preparestatement(vsql,resultset.type_forward_only,resultset.concur_ READ_ONLY); pstmt.setfetchsize(1000); ResultSet rs = pstmt.executequery(vsql); int cnt = rs.getmetadata().getcolumncount(); Object o; while (rs.next()) { } for (int i = 1; i <= cnt; i++) { } o = rs.getobject(i);

35 数据库访问优化详解减少交互次数 batch DML In List 设置 Fetch Size 使用存储过程优化业务逻辑使用 ResultSet 游标处理记录

36 减少交互次数使用存储过程如果采用存储过程你可以将整个业务逻辑封装在存储过程里, 然后在客户端直接调用存储过程处理, 这样可以减少网络交互的成本当然, 存储过程也并不是十全十美, 存储过程有以下缺点 : a 不可移植性 b 学习成本高 c 增加一些系统维护和调试成本 d 存储过程和常用应用程序语言不一样, 它支持的函数及语法有可能不能满足需求, 有些逻辑就只能通过应用程序处理 e 会增加一些数据库服务端的处理成本, 对于集中式数据库可能会导致系统可扩展性问题 f 存储过程需要重新编译才能生效, 在 24*7 高并发应用场景

37 数据库访问优化详解减少交互次数 batch DML In List 设置 Fetch Size 使用存储过程优化业务逻辑使用 ResultSet 游标处理记录

38 减少交互次数优化业务逻辑某移动公司推出优惠套参, 活动对像为 VIP 会员并且 2010 年 1,2,3 月平均话费 20 元以上的客户那我们的检测逻辑为 : select avg(money) as avg_money from bill where phone_no=' ' and date between '201001' and '201003'; select vip_flag from member where phone_no=' '; if avg_money>20 and vip_flag=true then begin 执行套参 (); end; 如果我们修改业务逻辑为 : select avg(money) as avg_money from bill where phone_no=' ' and date between '201001' and '201003'; if avg_money>20 then begin select vip_flag from member where phone_no=' '; if vip_flag=true then begin 执行套参 (); end; end; 通过这样可以减少一些判断 vip_flag 的开销, 平均话费 20 元以下的用户就不需要再检测是否 VIP 了

39 数据库访问优化详解减少交互次数 batch DML In List 设置 Fetch Size 使用存储过程优化业务逻辑使用 ResultSet 游标处理记录

40 减少交互次数使用 ResultSet 游标处理记录现在大部分 Java 框架都是通过 jdbc 从数据库取出数据, 然后装载到一个 list 里再处理,list 里可能是业务 Object, 也可能是 hashmap 由于 JVM 内存一般都小于 4G, 所以不可能一次通过 sql 把大量数据装载到 list 里为了完成功能, 很多程序员喜欢采用分页的方法处理, 如一次从数据库取 1000 条记录, 通过多次循环搞定, 保证不会引起 JVM Out of memory 问题代码示例 : String vsql ="select * from t_employee"; PreparedStatement pstmt = conn.preparestatement(vsql,resultset.type_forward_only,resultset.concur_read_only); pstmt.setfetchsize(100); ResultSet rs = pstmt.executequery(vsql); int col_cnt = rs.getmetadata().getcolumncount(); Object o; while (rs.next()) { for (int j = 1; j <= col_cnt; j++) { o = rs.getobject(j); } }

41 数据库访问优化详解减少数据库服务器 CPU 运算使用绑定变量合理使用排序减少比较操作大量复杂运算在客户端处理

42 减少数据库服务器 CPU 运算使用绑定变量绑定变量有以下优点 : 1 防止 SQL 注入 2 提高 SQL 可读性 3 提高 SQL 解析性能, 不使用绑定变更我们一般称为硬解析, 使用绑定变量我们称为软解析如果我们不采用绑定变量, 采用字符串拼接的模式生成 SQL, 那么每条 SQL 都会产生执行计划, 这样会导致共享池耗尽, 缓存命中率也很低一些不使用绑定变量的场景 : a 数据仓库应用, 这种应用一般并发不高, 但是每个 SQL 执行时间很长,SQL 解析的时间相比 SQL 执行时间比较小, 绑定变量对性能提高不明显数据仓库一般都是内部分析应用, 所以也不太会发生 SQL 注入的安全问题 b 数据分布不均匀的特殊逻辑, 如产品表, 记录有 1 亿, 有一产品状态字段, 上面建有索引, 有审核中, 审核通过, 审核未通过 3 种状态, 其中审核通过 9500 万, 审核中 1 万, 审核不通过 499 万

43 数据库访问优化详解减少数据库服务器 CPU 运算使用绑定变量合理使用排序减少比较操作大量复杂运算在客户端处理

44 减少数据库服务器 CPU 运算合理使用排序普通 OLTP 系统排序操作一般都是在内存里进行的, 对于数据库来说是一种 CPU 的消耗, 曾在 PC 机做过测试, 单核普通 CPU 在 1 秒钟可以完成 100 万条记录的全内存排序操作, 所以说由于现在 CPU 的性能增强, 对于普通的几十条或上百条记录排序对系统的影响也不会很大但是当你的记录集增加到上万条以上时, 你需要注意是否一定要这么做了, 大记录集排序不仅增加了 CPU 开销, 而且可能会由于内存不足发生硬盘排序的现象, 当发生硬盘排序时性能会急剧下降

45 减少数据库服务器 CPU 运算合理使用排序可能会发生排序操作的 SQL 语法 : Order by Group by Distinct Exists 子查询 Not Exists 子查询 In 子查询 Not In 子查询 Union( 并集 ),Union All 也是一种并集操作, 但是不会发生排序, 如果你确认两个数据集不需要执行去除重复数据操作, 那请使用 Union All 代替 Union Minus( 差集 ) Intersect( 交集 ) Create Index Merge Join, 这是一种两个表连接的内部算法, 执行时会把两个表先排序好再连接, 应用于两个大表连接的操作如果你的两个表连接的条件都是等值运算, 那可以采用 Hash Join 来提高性能, 因为 Hash Join 使用 Hash 运算来代替排序的操作

46 数据库访问优化详解减少数据库服务器 CPU 运算使用绑定变量合理使用排序减少比较操作大量复杂运算在客户端处理

47 减少数据库服务器 CPU 运算合理使用排序我们 SQL 的业务逻辑经常会包含一些比较操作, 如 a=b,a<b 之类的操作, 对于这些比较操作数据库都体现得很好, 但是如果有以下操作, 我们需要保持警惕 : Like 模糊查询, 如下所示 : a like %abc% Like 模糊查询对于数据库来说不是很擅长, 特别是你需要模糊检查的记录有上万条以上时, 性能比较糟糕

48 数据库访问优化详解减少数据库服务器 CPU 运算使用绑定变量合理使用排序减少比较操作大量复杂运算在客户端处理

49 减少数据库服务器 CPU 运算大量复杂运算在客户端处理如果有大量这类函数运算, 尽量放在客户端处理, 一般 CPU 每秒中也只能处理 1 万 -10 万次这样的函数运算, 放在数据库内不利于高并发处理

50 数据库访问优化详解利用更多的资源客户端多进程并行访问数据库并行处理并不是所有的 SQL 都可以使用并行处理, 一般只有对表或索引进行全部访问时才可以使用并行数据库表默认是不打开并行访问, 所以需要指定 SQL 并行的提示, 如下所示 : select /*+parallel(a,4)*/* from employee; 并行的优点 : 使用多进程处理, 充分利用数据库主机资源 (CPU,IO), 提高性能并行的缺点 : 1 单个会话占用大量资源, 影响其它会话, 所以只适合在主机负载低时期使用 ; 2 只能采用直接 IO 访问, 不能利用缓存数据, 所以执行前会触发将脏缓存数据写入磁盘操作注 : 1 并行处理在 OLTP 类系统中慎用, 使用不当会导致一个会话把主机资源全部占用, 而正常事务得不到及时响应, 所以一般只是用于数据仓库平台 2 一般对于百万级记录以下的小表采用并行访问性能并不能提高, 反而可能会让性能更差

51 Thank You

I

机电一级注册建造师继续教育培训广东培训点网上报名操作使用手册 (2013 年 1 月, 第一版 ) 第一章个人注册与个人信息管理 1. 个人注册 ( 请每人只申请一个注册号, 如果单位批量报班单位帮申请注册, 不需个人再注册 ) 首次报班,

关 于 我 姓 名 : 葛 云 杰 网 名 :Dylan Oracle OCM Oracle 用 户 组 年 轻 专 家 中 国 OCM 联 盟 成 员 山 东 Oracle 用 户 组 联 合 创 始 人 齐 鲁 IT 联 盟 联 合 创 始 人 软 件 研 发 工 程 师 具 有 十 年 以 上

关于我姓名 : 葛云杰网名 :Dylan Oracle OCM Oracle 用户组年轻专家中国 OCM 联盟成员山东 Oracle 用户组联合创始人齐鲁 IT 联盟联合创始人软件研发工程师具有十年以上