• 1. 2004.5 数据库开发调优 BI小组:张天宏
  • 2. 内容引言 数据库设计 SQL编码 *以Oracle和Sybase为例
  • 3. 引言数据库性能调整的四个方面
  • 4. 数据库设计表 存储碎片的处理 表缓存和数据缓存 分区 存储参数 索引 锁
  • 5. 数据库设计 表设计存储碎片的产生导致: 系统性能减弱 浪费大量的表空间 表空间的碎片整理 自动合并:表空间的 pctincrease 值为非 0,一般将其设为 1 手工合并 :alter tablespace temp coalesce; 表的碎片整理 使用Export和Import 索引的碎片整理 在线重建索引:alter index index_name rebuild online; 手工合并 : alter index index_name coalesce; DB_BLOCK_SIZE的合适大小 9i多种数据块大小
  • 6. 数据库设计 表缓存(CACHE)将经常需要全表扫描的表放在数据缓冲区使用列表的最前端 缺省是NOCACHE CREATE TABLE TEST_TAB(COL1 NUMBER) TABLESPACE USERS CACHE;ALTER TABLE TEST_TAB CACHE;
  • 7. 数据库设计 数据缓存合理缓存经常使用的查询数据 使用DBMS_SHARED_POOL将常用的或大型的PL/SQL,cursor,sequences,triggers,packages,procedures驻留在SGA区SELECT /*+ CACHE(CUST) */ ENAME,JOB FROM CUST WHERE TABLE_NAME=‘EMP’;EXECUTE DMBS_SHARED_POOL.KEEP(); EXECUTE DMBS_SHARED_POOL.UNKEEP();
  • 8. 数据库设计 表设计合理组织数据提高数据访问效率 Oracle7.3以后 Oracle8i两种分区方法 Oracle9i三种分区方法 Range partitioning Hash partitioning List partitioning
  • 9. 数据库设计 表设计表分区益处 提高SQL语句执行性能 利用并行查询的优势 SQL仅访问必要的数据—分区排除 设计优势 每个分区可单独地索引和确定大小 表维护 分区的相对独立性,方便维护 输出/输入可运行于单独的分区 更高的伸缩性和访问效率以支持更多的并发用户访问
  • 10. 数据库设计 表设计存储参数 INITIAL NEXT PCTINCREASE: 0 MINEXTENTS MAXEXTENTS:unlimited
  • 11. 数据库设计 表设计查看表的存储参数 查看表的空间占用 SELECT OWNER, TABLE_NAME, TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE FROM DBA_TABLES WHERE owner=’CSSAPP’ SELECT SEGMENT_NAME “表名”,SUM(BYTES) “字节数”, COUNT(*) “区间数” FROM DBA_EXTENTS WHERE OWNER=‘CSSAPP' AND SEGMENT_TYPE='TABLE' GROUP BY TABLESPACE_NAME,SEGMENT_NAME ORDER BY SUM(BYTES) DESC;
  • 12. 数据库设计 索引设计逻辑 单列或复合 唯一或非唯一 基于函数 物理 分区与非分区 B树 标准或反向键 位图 聚簇索引与非聚簇索引(Sybase)
  • 13. B树索引—B-Tree IndexIndex entry headerKey column lengthKey column valueROWIDRootBranchLeafIndex entry
  • 14. 反向键索引—Reverse Key IndexKEY ROWID ID (BLOCK# ROW# FILE#) ----- ------------------- 1257 0000000F.0002.0001 2877 0000000F.0006.0001 4567 0000000F.0004.0001 6657 0000000F.0003.0001 8967 0000000F.0005.0001 9637 0000000F.0001.0001 9947 0000000F.0000.0001 ... ... ... ...Index on EMPLOYEE (ID)EMPLOYEE tableID FIRST_NAME JOB ----- ---------- -------- 7499 ALLEN SALESMAN 7369 SMITH CLERK 7521 WARD SALESMAN 7566 JONES MANAGER 7654 MARTIN SALESMAN 7698 BLAKE MANAGER 7782 CLARK MANAGER ?... ... ... ... ... ... ...
  • 15. 函数索引—Function-Based Index极大提高查询性能 使用表达式进行查询将使用该索引CREATE INDEX summit.item_quantity_to_deliver_idx ON summit.item(quantity - quantity_shipped);SELECT ord_id, item_id FROM ITEM WHERE (quantity - quantity_shipped) > 0;
  • 16. 位图索引—Bitmap Indexkeystart ROWIDend ROWID bitmapTableIndexBlock 10Block 11Block 12File 3
  • 17. 数据库设计 索引设计索引对数据库的性能影响 表扫描需要访问大量数据页,而索引可以避免表扫描,从而使每次查询只需访问索引页和少量的数据页即可。 对于某些查询,数据可以从索引中获得,而无需访问数据库(索引覆盖)。 由于聚簇索引的插入是在数据页上分布进行的,因而避免了将表的最后一页作为“热点”进行大量的插入操作(Sybase)。 如果索引的顺序与order by 子句的顺序相同,则通过索引查询可以避免排序的过程。 索引(唯一索引)还可以使表中的数据唯一。 索引在加快查询数据速度的同时,因为在数据更新时还要更新索引,因而会减慢数据更新速度。
  • 18. 数据库设计 索引设计建立索引的原则 对于经常在排序后访问的列(即用order by子句指定的列)可能应该建索引,这样可以利用索引顺序的特点加快排序速度。 连接中经常使用的列或表中有外键约束的列应该建立索引,因为如果列处在索引顺序中则系统可更快执行连接。 表的主键列和唯一性约束列自动建立唯一性索引,不需要单独指定索引。 在Sybase中对于数量大的的日志表不宜建立聚簇索引,Sybase中主键缺省为聚簇索引。 经常按值的范围搜索的列最好具有聚簇索引。一旦找到具有范围中第一个值的行,具有后续值的行保证物理上连接在一起。聚簇索引在搜索单一值时不具有这些优点。
  • 19. 数据库设计 索引设计不适合建立索引 对于数据表比较小的表,不需要建索引。 值域范围比较小,只有两个或三个值的列,例如:男性和女性(或是和否),不会从索引得到好处。 不能对bit、text或image数据类型的列建立索引。 如果组合索引中含有已建立唯一性索引字段的列,不需要建立组合索引
  • 20. 数据库设计 索引设计索引的选择技术(Oracle) 使用大量访问路径作为索引列 满足查询条件的数据不超过20% 能实现索引覆盖 用于集函数、连接、group by和order by的列 要权衡索引对查询速度的加快与降低修改速度之间的利弊。 索引的平衡性:对于顺序增长的列索引使用Reversed key index 对于数据仓库,可以选择使用位图索引,而在OLTP系统中,选择B树索引则更为有效 索引RI外键以防止锁*RI:参照完整性
  • 21. 数据库设计 索引设计建立索引注意事项(Sybase) 对于经常修改的字段最好不要为它建立聚簇索引,因为维护索引的代价太大。实际上对于所有的索引来说都应该尽量遵守这个原则,因为索引会降低插入、删除或修改操作的效率。如果在需要进行大量的插入、删除或修改操作,那么就应该先删除索引,等到大量操作完成以后再重新建立索引。 如果聚簇索引建立的字段上需要有大量的插入,那么聚簇索引就不能建立在顺序增长的字段上,比如IDENTITY列或顺序增长的‘╳╳代码’列。因为如果字段的插入值是顺序增长的,则全部的INSERT语句和部分的UPDATE语句都需要在数据页的最后一页上进行插入和修改,从而多个事务对最后一页加锁的竞争会严重影响性能。所以,聚簇索引只能建立在随机插入的字段上。
  • 22. 数据库设计 索引设计建立索引注意事项(Oracle) 索引与数据表应分别建在不同的表空间上 存储参数 INITIAL NEXT PCTINCREASE: 0 MINEXTENTS MAXEXTENTS:unlimited 查看索引的分布SELECT * FROM DBA_IND_COLUMNS WHERE INDEX_OWNER='&USER_NAME' ORDER BY TABLE_NAME,INDEX_NAME,COLUMN_NAME;
  • 23. 数据库设计 锁锁的类型 共享(S)锁 多个事务可封锁一个共享页 任何事务都不能修改该页 通常是该页被读取完毕,S锁立即被释放 排它(X)锁 仅允许一个事务封锁此页 其他任何事务必须等到X锁被释放才能对该页 进行访问 X锁一直到事务结束才能被释放 更新(U)锁 用来预定要对此页施加X锁,它允许其他事务读, 但不允许再施加U锁或X锁 当被读取的页将要被更新时,则升级为X锁 U锁一直到事务结束时才能被释放
  • 24. 数据库设计 锁锁对性能的影响 多个事务并发修改少数对象会发生锁争夺 死锁 在事务内存在的问题: 在事务内执行复杂的计算 在事务内允许用户交互操作 在事务内执行多个不相关的任务 在正常工作过程中执行系统维护操作 建立索引将锁住表的修改,直到索引建立完毕,建议不活跃时建立索引(Oracle支持在线建索引)。
  • 25. 数据库设计 锁Oracle在锁处理方面的优势 利用回滚段避免了在查询时加共享锁 对数据的更新和插入采用行级锁 锁资源的系统开销小 Sybase对锁的处理 需要配置足够的锁资源 缺省为ALL PAGE全页锁 全页锁 页级锁 行级锁 自动执行锁提升
  • 26. 数据库设计 锁事务的隔离级别与锁 ANSI标准为SQL事务定义了四个隔离级别,隔离级别越高,出现数据不一致性的可能性就越小(并发度也就越低)。较高的级别中包含了较低级别中所规定了的限制。 隔离级别0:防止“丢失修改”,允许“脏”读。 隔离级别1:防止“脏”读。(Sybase Default) 隔离级别2:防止“不可重复读”。 隔离级别3;“可串行化”(Serializable)。其含义为某组并行事务的一种交叉调度产生的结果和这些事务的某一串行调度的结果相同。(可避免破坏数据一致性)。 Sybase支持0、1、3种隔离级别,不直接支持隔离级别2,但已被隔离级别所3包含。
  • 27. 数据库设计 锁设定隔离级别 在Sybase中设定事务隔离级别的方法有三种: 会话层设定 set transaction isolation level {0,1,3 | read uncommitted, read committed, serializable} 系统提供的系统存储过程将在级别1下执行,它不受会话层设定的影响。 语句层设定 在select, declare cursor及readtext语句中增加选项, 比如:select... at isolation {0 | read uncommitted} 注意:语句层的设定将替代会话层的设定。 利用关键词设定 在select语句中,加选项holdlock则设定级别3 在select语句中,加noholdlock则设定级别1
  • 28. 数据库设计 锁降低锁争夺 在数据库和应用的设计时充分考虑将来可能的锁争夺情况,减少批量删除和更新 改变加锁模式,或者重新设计表和表的应用程序访问 增加索引,尤其对删除和修改操作,使得允许在修改时使用页锁或行锁,从而提高并发性 大的长的事务改成短小事务,减少锁驻留的时间 注意“热点”,尤其对表锁的堆表插入 考虑将表锁改为页锁或是行锁 大表分区 建立聚簇索引 如果不能建立索引缓解锁的争夺,考虑在一个游标内完成事务。通过不断的commit transaction降低页锁数。
  • 29. 数据库设计 锁降低锁争夺 另外的解决方法 调整事务的隔离级别,比如用隔离级别3 大事务操作可用带游标的存储过程,通过频繁的提交避免堵塞 适当建立数据cache和大块I/O内存池,通过减少I/O和等待时间,达到降低锁争夺的目的 加快事务的执行,从整体上减少事务驻留锁的时间 更新有外键引用从表的主表记录 如果从表上存在索引,只有被更改的行被锁上 如果没有索引,在事务处理期间一个共享锁被加在从表上,所以建议 给任何需要更新并通过外键与其它表联系的列创建索引 对于那些没有实际索引的,用行级触发器替换内置的外键约束 Oracle 9i中由于外键获取锁后会立刻将锁释放
  • 30. 数据库设计 锁死锁及其解除 引起死锁的主要原因是两个进程已各自锁住一个页, 但又要求访问被对方锁住的页。封锁请求page Apage B请求封锁等待等待事务 1事务 2
  • 31. 数据库设计 锁死锁及其解除 更一般的情况是,一个事务独占了其他事务正在申请的资源,且若干个这样的事务形成一个等待圈。 Sybase和Oracle都能自动发现并解除死锁。当发现死锁时,它会选择其进程累计的CPU时间最少者所对应的用户作为“牺牲者”,(令其夭折)以让其他进程能继续执行。
  • 32. 数据库设计 锁死锁的预防 在所有的事务中都按同一顺序来访问各个表。尽可能利用存储过程来完成一个事务,以能保证对各表的访问次序都是一致的。 除非有“可重复读”的必要性,否则不要使用holdlock选项。 事务应缩小且应尽快提交。 避免人工输入操作出现在事务中或是同时对该表施加holdlock。 避免并发地执行许多像insert, update, delete这类数据修改语句。
  • 33. SQL编码SQL代码优化 定位 分析 调整 优化 技巧
  • 34. SQL编码 定位查找大量使用内存及磁盘I/O的查询(v$sqlarea)
  • 35. SQL编码 定位查找逻辑读最多的查询语句
  • 36. SQL编码 定位查找锁死其他用户操作的用户 杀掉有问题的用户进程ALTER SYSTEM KILL SESSION ‘sid,serial#’;
  • 37. SQL编码 分析SQL语句的分析过程(无论是否使用优化器,分析过程都相同) 检查语法 搜索共享池 搜索数据字典 计算搜索路径 基于规则和成本 保存执行计划 确保为提高SQL的共享性,建议使用统一的SQL编程标准 Put all verbs in one case Begin all SQL verbs on a new line Right or left align verbs with the initial verb Bind variables will ensure all SQL is equal so statements is not reparsed
  • 38. SQL编码 调整使用下列信息确定访问路径 SQL语句 在引用表上的索引 执行计划 当前优化模式 提示变量 表统计(如果是基于成本的优化) 知道选择何种优化ANALYZE TABLE CUSTOMER ESTIMATE STATISTICS;
  • 39. SQL编码 优化Oracle的优化器其有三种: 基于规则的优化(RULE) 基于成本的优化(COST) 基于选择性的优化(CHOOSE) 设置优化器ALTER SESSION SET OPTIMIZER_MODE=choose ;
  • 40. SQL编码 优化基于规则的优化(rules) Oracle分析器从右到左处理,最后一个表最先被处理 最后一个表应该是包含父键或连接判定到其它表 SQL每一条件都被分级 1 ROWID=CONSTANT 2 主键 = constant … …
  • 41. SQL编码 优化基于规则的优化(rules) 如果优化器有多个索引可选: 唯一索引总排在非唯一索引的前面 若两个索引一样: 如果是不同表上的两个索引,表的顺序将用于决定使用那个索引 如果两个索引在同一表上,首先引用的索引将被使用 除索引以外,WHERE子句中比较常量和变量比字段之间的比较优先
  • 42. SQL编码 优化基于成本的优化(cost) 基于成本的优化器没有固定的费用评估规则,评估是灵活的并基于分析统计的结果变化 逻辑读取的评估标准 用最少的资源实现最大吞吐量(ALL_ROWS) 用最少的资源实现首行最佳响应时间(FIRST_ROWS)
  • 43. SQL编码 优化基于成本的优化(cost) 100%地选择唯一索引 计算非唯一索引的选择性 用列的最大最小值处理评估范围 Hint可被用于影响优化器
  • 44. SQL编码 优化基于成本的优化(cost) 可以通过加入hint来强制改变优化策略 FULL——强制进行全表扫描 SELECT /*+ FULL(table_name)*/ column1,column2… INDEX——强制使用指定的索引 SELECT /*+ INDEX(table_name index_name1, index_name2…)*/ ORDERED——强制指定查询时的驱动表 SELECT /*+ ORDERED*/ column1,column2… FROM table1, table2 ALL_ROWS——基于成本的优化,最大的提高数据的吞吐性 SELECT /*+ ALL_ROWS */ ………… ALL_ROWS hint 通常会禁用索引
  • 45. SQL编码 优化From子句的内表外表次序 在表的连接中一个表作为外表,也称驱动表,另一个表作为内表 首先检查外表的每一行是否满足查询条件,对于满足条件的那些外表记录,扫描每一个内表记录并且与它在连接列上进行匹配 优化器选择外表的根据是: 满足条件的返回数据行数少 或定位数据行时需要读操作的次数多(不能有效利用索引) 如果有三个或以上的表进行关联查询,中间的表应该作为外表 与之相反的表作为内表
  • 46. SQL编码 优化确定表连接次序的实例(Sybase) select TableA.colx , TableB.coly from TableA, TableB where TableA.col1 = TableB.col1 and TableB.col2=anything and TableA.col2 = something 假设A,B表都是有10条记录满足条件 表A:1,000,000条记录,每个数据页上10行记录, (共100,000页),没有索引。 (定位数据行时需要读操作的次数多-应选作外表) 表B:100,000条记录,每个数据页上10行记录, (共10,000页) 在连接列上有聚簇索引,索引树为3层。 通过以下的计算说明应该选表A为外表
  • 47. SQL编码 优化确定表连接次序的实例(Sybase) 如果表A是外表: 对它的存取是通过全表扫描,当找到第一个满足条件的记录,在表B上用聚簇索引查找B表中col1列与从A表检索出的值匹配的数据行,做完后继续扫描A表,重复上面的过程,直到A表全部扫描完毕。假设A表有10条满足条件的记录,则该次查询所需的读数据页的次数如下: 读取的页数 A表扫描 100,000 10次B表索引扫描 10*3=30 合计 100,030
  • 48. SQL编码 优化确定表连接次序的实例(Sybase) 如果表B是外表: 对它的存取是通过聚簇索引,当找到第一个满足条件的记录,在表A上用全表扫描A表中col1列与从B表检索出的值匹配的数据行,做完后继续扫描B表,重复上面的过程,直到B表全部扫描完毕。假设B表有10条满足条件的记录,则该次查询所需的读数据页的次数如下: 读取的页数 B表扫描 3 10次A表全表扫描 10*100,000=1,000,000 合计 1,000,003 1,000,003>100,030,所以优化器自动选择A表做外表
  • 49. SQL编码 优化WHERE子句后面参数的写法: 1、有索引且能用上索引的表达式,其格式有: < column > < operator > < expression > < expression > < operator > < column > is null(对Oracle不适合) column只能是一个列名,在列名上不能有函数或其它操作符 operator只能是=, >, <, >=, <= 和 is null expression可以是常数或能计算出常数的表达式 不等操作符!>和!=是特殊情况,这时查询优化器不能使用索引来定位搜索
  • 50. SQL编码 优化2、可以进行优化的查询条件书写 下面的例子是可优化的: au_lname = “Bennett” price >= $12.00 price >= $12.00*3 advance >10000 and advance <20000 下面的例子则不是: salary=commission /*两边都是列名*/ substring(lname,1,3) =“Ben” /*列名边不允许函数*/ advance * 2 = 5000 /*列名边不允许表达式*/ advance =$10000 or price = $12.00 /*含有or谓词*/
  • 51. SQL编码 优化3、可以转化为优化的查询条件书写 between谓词可以转化为>=and<=子句,比如: price between 10 and 20 可以转化为 price>= 10 and <=20 like子句中匹配值的第一个字符是常量,也可以进行转换, 例如: like “sm%”可以转换成 >=“sm” and <“sn” 但like “%x”这种类型不能转换
  • 52. SQL编码 优化4、使用一些冗余的查询条件给优化器更多的信息 表titles和表titleauthor是一对多关系,title_id在表titles中是唯一的 select au_lname ,title from titles t, titleauthor ta, authors a where t.title_id = ta.tatle_id and a.au_id = ta.au_id and t.title_id = "T81002” title_id在表titles中是唯一的 2. select au_lname ,title from titles t, titleauthor ta, authors a where t.title_id = ta.tatle_id and a.au_id = ta.au_idand ta.title_id = “T81002” 最差 因为title_id在表titleauthor中非唯一 3. select au_lname ,title from titles t, titleauthor ta, authors a where t.title_id = ta.tatle_id and a.au_id = ta.au_id and t.title_id = “T81002” and ta.title_id = “T81002” 最优 查询1就可能比查询2执行的好,因为title_id是唯一的,筛选的效果会好些,第3个查询提供了两个表的搜索变量,给优化器更多的选择来调整连接操作表的顺序。
  • 53. SQL编码 优化 5. 连接顺序(只适用于基于规划的优化器) Oracel 采用自下而上的顺序解析WHERE子句 可以过滤掉最大数量记录的条件写在WHERE子句的末尾高效 SELECT … FROM EMP E WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO) AND SAL > 50000 AND JOB = ‘MANAGER’
  • 54. SQL编码 优化6、编写SQL语句时关于查询条件书写的注意事项 在搜索子句的列名边要避免函数、算术操作符和其它的表达式 避免不兼容的数据类型 使用复合索引的第一个列名 给优化器提供尽可能多的查询条件-可以用上索引的检索条件!
  • 55. SQL编码 优化子查询的优化 概念:子查询是包含在括号里的select语句,它通常嵌套在下面的语句里: where子句、having子句或select语句的选择列中 update和delete语句的where子句中 update语句的set子句中 insert语句中 其它的子查询中
  • 56. SQL编码 优化子查询的优化 对子查询一般使用下面的优化策略 展平策略: 把子查询转化成连接查询 将嵌套改成扩展的连接操作 实例化:把子查询的结果存储到临时工作表中 在外层查询执行之前,先将内层查询的结果放在临时工作表中,再应用于外层查询。Oracle提供了表函数可以预先将查询结果作为数据集供其他查询使用。 短路:改变子查询的执行顺序 比如or条件和and条件的尽早判断避免一些不必要的子查询 存储子查询结果:记录执行子查询的结果在数据cache中,减少磁盘I/O的次数(用cache的命中率衡量)。
  • 57. SQL编码 优化嵌套子查询(FYI)
  • 58. SQL编码 优化使用EXPLAIN PLAN 可以在不执行SQL的情况下分析语句 通过分析,我们就可以知道ORACLE是怎么样连接表,使用什么方式扫描表(索引扫描或全表扫描)以及使用到的索引名称 按照从里到外,从上到下的次序解读分析的结果 分析结果是用缩进的格式排列的, 最内部的操作将被最先解读, 如果两个操作处于同一层中,带有最小操作号的将被首先执行 NESTED LOOP是少数不按照上述规则处理的操作, 正确的执行路径是检查对NESTED LOOP提供数据的操作,其中操作号最小的将被最先处理
  • 59. SQL编码 优化SQL>set autotrace traceonly SQL> SELECT * FROM dept, emp WHERE emp.deptno = dept.deptno Execution Plan --------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 TABLE ACCESS (FULL) OF 'EMP' 3 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' 4 3 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)TABLE ACCESS (FULL) OF 'EMP‘ INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF 'DEPT‘ NESTED LOOPS (JOINING 1 AND 3)执 行 顺 序
  • 60. SQL编码 技巧or与in子句的优化 查询优化器对and和or子句的查询采用不同的处理方式,包含or或in子句查询的优化依赖于在这些子句中使用的表上的索引和这些子句是否返回重复的结果。 or子句通常采用下面的形式: where column1 = or column1= where column1 = or column2= in子句转化为or来处理
  • 61. SQL编码 技巧or子句的解决办法 全表扫描 :如果or子句中使用的列上有任一个列没有索引或使用索引的开销超过了全表扫描的开销,则执行全表扫描。 OR策略 :使用or子句的查询相当于多个查询的union,一些记录可能满足多个条件,但只应当返回一次。 OR策略将 使用每一列的索引把满足每个or子句的记录行merge 到一起,然后再去掉重复数据行。
  • 62. SQL编码 技巧关于or策略(Sybase) 如果or子句不会返回重复记录:优化器就会使用多个匹配索引查询,每个or子句单独考虑,可以使用不同的索引。 如or子句返回重复记录,则采用比较费时的or策略: 先把满足每一个or子句的记录行的ID号存储到数据库tempdb中的工作表中,然后进行排序并删除重复的ID号,再利用行ID从基表中检索出不 重复的数据行。 or策略在被访问的数据页上维护一共享锁所以会影响并发性。在使用隔离级别3或holdlock子句的情况下,要避免or查询。 (可考虑用union 代替or)
  • 63. SQL编码 技巧当在索引列上进行条件判断时,用>=替代> 例如下面的查询,在int_col上建有索引: select * from table where int_col > 3 该查询使用索引找到第一个int_col=3的值,然后向前扫描。如果表中有许多行中int_col的值是3,则该查询不得不扫描许多页后找到第一个int_col大于3的数据行。 若把查询写成下面的形式,则会更有效: select * from table where int_col >=4
  • 64. SQL编码 技巧exist和 not exist 在子查询,exists和in要比not exists和not in执行得快,因为对于exists和in,优化器只要找到一条记录就可以返回TRUE,而对于not exists和not in则必须检查所有的值。
  • 65. SQL编码 技巧开发调优 1、在搜索子句的列名边要避免函数、算术操作符和其它的表达式,除非建立基于函数的索引 2、使用复合索引的第一个列名 3、SELECT子句中避免使用 ‘*’ 4、用TRUNCATE替代DELETE 5、尽量多使用COMMIT 6、避免在索引列上使用IS NULL和IS NOT NULL(Oracle) 7、用UNION-ALL 替换UNION ( 如果有可能的话) 8、使用较大的BUFFER(比如10MB , 10,240,000)可以提高EXPORT和IMPORT的速度 9、在物理存储上分离表和索引
  • 66. SQL编码 技巧开发调优 10、当连接的两个或多个数据表的数据量都过大时,考虑将数据表进行整合到一张表中 11、对于批处理的操作,使用指定的大的回滚段 12、当使用存储过程处理数据量比较大表时,可考虑使用中间表或临时工作表 13、当没有可用的索引时,则只能创建工作表对结果数据排序 14、将纵向表拆成横向表处理,比如费用字段拆成市话、长话、IP费用等。 15、将层次结构复杂(如雪花形结构)的维表如咨询树类型,或将信息比较多的维表比如客户信息表,直接取出维度信息放进事实表中,并对该表建立索引,增加查询的速度
  • 67. SQL编码 技巧应用调优 1、对于频繁进行更新和插入的表,比如状态监控表和日志表使用行级锁。 2、对于频繁进行更新和插入的大数据量表,比如日志表,要考虑所建索引的数量,避免建过多的索引(会降低插入和更新操作的执行效率,并且占用大量的存储空间,可能比原表还大)。 3、对于字段较多并且包含memo,text,image等记录长度大的数据表,在查询时要考虑执行效率,比如对于批量工单查询,只需返回有限的几个字段(减少I/O和执行时间),对于工单的精确查询返回全部字段的值。 4、在应用中对业务的处理引入事务机制和异常处理机制,保证业务处理的完整性。 5、考虑主外键约束的使用(灵活与严整),使用触发器或事务处理的方式来保证参照完整性。 6、对于数据量不大的表,可以将数据一次取到内存中,然后再进行计算和查找,比如树形结构的展现。
  • 68. Thank you !