共计 4262 个字符，预计需要花费 11 分钟才能阅读完成。
这期内容当中丸趣 TV 小编将会给大家带来有关 SQL Server 中怎么优化多表查询，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。
1. 执行路径
ORACLE 的这个功能大大地提高了 SQL 的执行性能并节省了内存的使用: 我们发现, 单表数据的统计比多表统计的速度完全是两个概念. 单表统计可能只要 0.02 秒, 但是 2 张表联合统计就
可能要几十秒了. 这是因为 ORACLE 只对简单的表提供高速缓冲(cache buffering) , 这个功能并不适用于多表连接查询.. 数据库管理员必须在 init.ora 中为这个区域设置合适的参数, 当这个内存区域越大, 就可以保留更多的语句, 当然被共享的可能性也就越大了.
2. 选择最有效率的表名顺序(记录少的放在后面)
ORACLE 的解析器按照从右到左的顺序处理 FROM 子句中的表名, 因此 FROM 子句中写在最后的表 (基础表 driving table) 将被最先处理. 在 FROM 子句中包含多个表的情况下, 你必须选择记录条数最少的表作为基础表. 当 ORACLE 处理多个表时, 会运用排序及合并的方式连接它们. 首先, 扫描第一个表 (FROM 子句中最后的那个表) 并对记录进行派序, 然后扫描第二个表(FROM 子句中最后第二个表), 最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.
例如:
表 TAB1 16,384 条记录
表 TAB2 1 条记录
选择 TAB2 作为基础表 (最好的方法)
select count(*) from tab1,tab2 执行时间 0.96 秒
选择 TAB2 作为基础表 (不佳的方法)
select count(*) from tab2,tab1 执行时间 26.09 秒
如果有 3 个以上的表连接查询, 那就需要选择交叉表 (intersection table) 作为基础表, 交叉表是指那个被其他表所引用的表.
例如: EMP 表描述了 LOCATION 表和 CATEGORY 表的交集.
SELECT * FROM LOCATION L , CATEGORY C, EMP E WHERE E.EMP_NO BETWEEN 1000 AND 2000 AND E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN将比下列 SQL 更有效率
SELECT * FROM EMP E , LOCATION L , CATEGORY C WHERE E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN AND E.EMP_NO BETWEEN 1000 AND 20003.WHERE 子句中的连接顺序(条件细的放在后面)
ORACLE 采用自下而上的顺序解析 WHERE 子句, 根据这个原理, 表之间的连接必须写在其他 WHERE 条件之前, 那些可以过滤掉最大数量记录的条件必须写在 WHERE 子句的末尾.
例如:
(低效, 执行时间 156.3 秒)
SELECT … FROM EMP E WHERE SAL 50000 AND JOB = ‘MANAGER AND 25 (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO); (高效, 执行时间 10.6 秒) SELECT … FROM EMP E WHERE 25 (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO) AND SAL 50000 AND JOB = ‘MANAGER4.SELECT 子句中避免使用 *
当你想在 SELECT 子句中列出所有的 COLUMN 时, 使用动态 SQL 列引用 * 是一个方便的方法. 不幸的是, 这是一个非常低效的方法. 实际上,ORACLE 在解析的过程中, 会将 * 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.
5. 减少访问数据库的次数
当执行每条 SQL 语句时, ORACLE 在内部执行了许多工作: 解析 SQL 语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少 ORACLE 的工作量.
方法 1 (低效)
SELECT EMP_NAME , SALARY , GRADE FROM EMP WHERE EMP_NO = 342; SELECT EMP_NAME , SALARY , GRADE FROM EMP WHERE EMP_NO = 291;方法 2 (高效)
SELECT A.EMP_NAME , A.SALARY , A.GRADE, B.EMP_NAME , B.SALARY , B.GRADE FROM EMP A,EMP B WHERE A.EMP_NO = 342 AND B.EMP_NO = 291;6. 删除重复记录
最高效的删除重复记录方法 (因为使用了 ROWID)
DELETE FROM EMP E WHERE E.ROWID (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);7. 用 TRUNCATE 替代 DELETE
当删除表中的记录时, 在通常情况下, 回滚段(rollback segments) 用来存放可以被恢复的信息. 如果你没有 COMMIT 事务,ORACLE 会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)，而当运用 TRUNCATE 时, 回滚段不再存放任何可被恢复的信息. 当命令运行后, 数据不能被恢复. 因此很少的资源被调用, 执行时间也会很短.
8. 尽量多使用 COMMIT
只要有可能, 在程序中尽量多使用 COMMIT, 这样程序的性能得到提高, 需求也会因为 COMMIT 所释放的资源而减少:
COMMIT 所释放的资源:
a. 回滚段上用于恢复数据的信息.
b. 被程序语句获得的锁
c. redo log buffer 中的空间
d. ORACLE 为管理上述 3 种资源中的内部花费(在使用 COMMIT 时必须要注意到事务的完整性, 现实中效率和事务完整性往往是鱼和熊掌不可得兼)
9. 减少对表的查询
在含有子查询的 SQL 语句中, 要特别注意减少对表的查询.
例如：
低效：
SELECT TAB_NAME FROM TABLES WHERE TAB_NAME = ( SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604) AND DB_VER= ( SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION = 604高效：
SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER) FROM TAB_COLUMNS WHERE VERSION = 604)Update 多个 Column 例子:
低效：
UPDATE EMP SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES), SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020;高效:
UPDATE EMP SET (EMP_CAT, SAL_RANGE) = (SELECT MAX(CATEGORY) , MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020;10. 用 EXISTS 替代 IN, 用 NOT EXISTS 替代 NOT IN
在许多基于基础表的查询中, 为了满足一个条件, 往往需要对另一个表进行联接. 在这种情况下, 使用 EXISTS(或 NOT EXISTS)通常将提高查询的效率.
低效:
SELECT * FROM EMP (基础表) WHERE EMPNO 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB)高效:
SELECT * FROM EMP (基础表) WHERE EMPNO 0 AND EXISTS (SELECT ‘X FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB)(相对来说, 用 NOT EXISTS 替换 NOT IN 将更显著地提高效率)
在子查询中,NOT IN 子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN 都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用 NOT IN , 我们可以把它改写成外连接 (Outer Joins) 或 NOT EXISTS.
例如:
SELECT … FROM EMP WHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT= A为了提高效率. 改写为:
(方法一: 高效)
SELECT …. FROM EMP A,DEPT B WHERE A.DEPT_NO = B.DEPT(+) AND B.DEPT_NO IS NULL AND B.DEPT_CAT(+) = A(方法二: 最高效)
SELECT …. FROM EMP E WHERE NOT EXISTS (SELECT X FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = A当然, 最高效率的方法是有表关联. 直接两表关系对联的速度是最快的!
11. 识别 低效执行 的 SQL 语句
用下列 SQL 工具找出低效 SQL:
SELECT EXECUTIONS , DISK_READS, BUFFER_GETS, ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio, ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run, SQL_TEXT FROM V$SQLAREA WHERE EXECUTIONS 0 AND BUFFER_GETS 0 AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS 0.8 ORDER BY 4 DESC;上述就是丸趣 TV 小编为大家分享的 SQL Server 中怎么优化多表查询了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注丸趣 TV 行业资讯频道。
