SQL优化

nullnull SQL优化陈默目录P-*目录目标 内容 ORACLE体系结构简介 EM初步使用 书写优化（*） 算法与机制优化（*） 索引优化 参考资料目标P-*目标了解ORACLE基本的运行机制 可以写出较为高效的SQL代码 了解最基本的一些SQL操作符的机制 能够初步解决一些编程过程中出现的基本的问题ORACLE体系结构简介ORACLE体系结构简介 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 空间 SYSTEM SYSAUX TEMP UNDOTBS1 USERS*ORACLE体系结构简介ORACLE体系结构简介*Java PoolDatabase Buffer CacheRedo Log BufferShared PoolLarge PoolSGAStreams PoolServer Process 1PGAServer Process 2PGABack- ground ProcessPGAORACLE体系结构简介ORACLE体系结构简介PGA(Program Global Area) 内存中的区域，包含单个进程的数据和控制信息。每个服务器进程分配一个PGA，PGA由每个服务器进程独占。当用户连接入Oracle数据库并建立会话时，Oracle分配PGA。与SGA不同，PGA仅被一个进程使用。 排序 会话信息 游标 栈*ORACLE体系结构简介ORACLE体系结构简介*System Monitor SMONDatabase Writer DBW0Log Writer LGWRProcess Monitor PMONArchiver ARC0SGAJava PoolShared PoolLarge PoolStreams PoolDatabase Buffer CacheRedo Log BufferCheck point CKPTEM初步使用EM初步使用EM地址 EM的启动与关闭 EM语言调整 EM各部分简介 如何监视会话 如何监视oracle活动*EM初步使用EM初步使用EM地址 EM是一个网页化企业管理器。 网址结构如下 http://serverip:port/em Serverip:要登录的服务器的ip地址 Port:EM的占用端口*EM初步使用EM初步使用EM的启动与关闭 启动EM命令： emctl start dbconsole [oracle@vpndb ~]$ emctl start dbconsole TZ set to Japan Oracle Enterprise Manager 10g Database Control Release 10.2.0.4.0 Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved. http://vpndb:1158/em/console/aboutApplication Starting Oracle Enterprise Manager 10g Database Control ....................... started. ------------------------------------------------------------------ Logs are generated in directory /home/oracle/10.2.0/db_1/vpndb_CSDB/sysman/log [oracle@vpndb ~]$ *EM已启动EM初步使用EM初步使用EM启动与关闭 查询EM状态命令： emctl status dbconsole [oracle@vpndb ~]$ emctl status dbconsole TZ set to Japan Oracle Enterprise Manager 10g Database Control Release 10.2.0.4.0 Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved. http://vpndb:1158/em/console/aboutApplication Oracle Enterprise Manager 10g is running. ---------------------------------------------------- Logs are generated in directory /home/oracle/10.2.0/db_1/vpndb_CSDB/sysman/log [oracle@vpndb ~]$*EM地址EM当前是启动状态EM初步使用EM初步使用EM启动与关闭 关闭EM命令： emctl stop dbconsole [oracle@vpndb ~]$ emctl stop dbconsole TZ set to Japan Oracle Enterprise Manager 10g Database Control Release 10.2.0.4.0 Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved. http://vpndb:1158/em/console/aboutApplication Stopping Oracle Enterprise Manager 10g Database Control ... ... Stopped. [oracle@vpndb ~]$ *EM已停止EM初步使用EM初步使用EM语言调整 如果使用IE登录EM可以选择EM显示的语言，在“工具——Internet选项——语言”下可以调整 *EM初步使用EM初步使用EM各部分简介 登录画面：输入用户名，密码，选择连接身份*EM初步使用EM初步使用EM各部分简介 主画面 *EM初步使用EM初步使用EM各部分简介 性能画面 *EM初步使用EM初步使用EM各部分简介 管理画面*EM初步使用EM初步使用如何监视会话 选择“性能——搜索会话”，可以进行会话的搜索和管理 *EM初步使用EM初步使用如何监视oracle活动 选择“性能——顶级活动”，可以进行查看当前的顶级SQL，以及顶级会话 *书写优化书写优化大小写的区分 少用“*”的原因 少用“IN”的原因 “UNION”与“UNION ALL” “DISTINCT”的机制 “>=”与“>” ANSI JOIN MERGE书写优化书写优化大小写的区分 Oracle会将所有要执行的SQL语句先转化成大写。 Oracle对大小写比较敏感，以下的SQL语句对于oracle来说是不同的 Select Deptno From Dept select deptno from dept SELECT DEPTNO FROM DEPT 思考：CREATE TABLE Aa…系统中生成的表名是什么？（AA,aa,Aa）书写优化书写优化少用“*”的原因 “*”在编译时会自动转化为字段名，因此需要增加访问数据字典，多次访问数据库。 Count（*）改为count（索引）较为高效书写优化书写优化少用“IN”的原因 “IN”与“OR”机制类似，a IN (b,c,d)的处理机制类似“a=b or a=c or a=d”，“OR”的处理很慢，因此不建议使用。 例子 SELECT DNAME FROM DEPT WHERE DEPTNO IN (10,20) 最好改写成 SELECT DNAME FROM DEPT WHERE DEPTNO =10 UNION ALL SELECT DNAME FROM DEPT WHERE DEPTNO =20书写优化书写优化“UNION”与“UNION ALL” UNION在连接后会进行排序去除重复的工作，因此，在确定没有重复的情况下，尽量使用UNION ALL*书写优化书写优化Distinct执行时，会首先进行排序，之后才是去除重复，因此Distinct的时间复杂度较高o（n^2） 可能情况下，尽量通过条件限制达到去除重复项的目的，少用distinct书写优化书写优化“>=”与“>” 比较A>2与A>=3，A>2时ORACLE会先找出为2的记录索引再进行比较，而A>=3时ORACLE则直接找到=3的记录索引。*书写优化书写优化ANSI JOIN 从ORACLE 9i开始，ANSI JOIN被引入。 ANSI JOIN更易于读取和清楚地指定连接条件。 ORACLE能够支持传统的链接语法，不过应该首选ANSI JOIN。 Join on/natural join/join using*书写优化书写优化MERGE 从ORACLE 9i开始，MERGE被引入。 需要同时插入和更新一些字段时，可以考虑使用MERGE。 MERGE INTO table1 a USING table2 b ON(a.XXX=b.XXX) WHEN MATCHED THEN UPDATE SET a.YYY=b.YYY WHEN NOT MATCHED THEN INSERT VALUES();*算法与机制优化算法与机制优化减少时间复杂度 慎重使用排序等时间复杂度高的运算 定期提交事务 提交事务能有效的释放内存。在进行大数据量的操作时，定期的提交事务是必要的。 绑定变量的应用 使用绑定变量能有效防止数据库的重复编译。以下语句对于数据库来讲是不同的: SELECT DNAME FROM DEPT WHERE DEPTNO=1; SELECT DNAME FROM DEPT WHERE DEPTNO=2;索引优化索引优化索引的优缺点 减少在索引字段上的函数处理 NULL的认识 多列索引的触发 “<>”的认识索引优化索引优化索引的优缺点 优点：大表查询效率提高 缺点： 延缓增删改速度，尤其是索引列的操作。 热点查询有可能增加新的热点*索引优化索引优化减少在索引字段上的函数处理 索引采取的是严格的等于方式，因此，所有在索引字段上的函数处理都会导致全表扫描。在大表中，这种扫描比较可怕。 例： SELECT DNAME FROM DEPT WHERE TO_CHAR(DEPTNO) =‘10’ SELECT DNAME FROM DEPT WHERE DEPTNO || DNAME =‘10ACCOUNTING’ *索引优化索引优化NULL的认识 下面哪个SQL能显示“1” SELECT 1 FROM DUAL WHERE NULL=NULL SELECT 1 FROM DUAL WHERE NULL>NULL SELECT 1 FROM DUAL WHERE NULLNULL*索引优化索引优化多列索引的触发 Index（col1,col2）作为联合索引，在使用时，必须要有col1=XXX的条件，否则将触发全表扫描。*索引优化索引优化“<>”的认识 “<>”操作不会使用索引 “>”、“>=”、“<”、“<=”可以使用索引*参考资料P-*参考资料《Oracle Database 10g:Administration Workshop I》 《Oracle Database 10g:Administration Workshop II》 null