sql优化.pdf - 墨天轮文档

sql优化.pdf

emiliy

156

9页

7次

2024-05-13

5墨值下载

美河学习在线 www.eimhe.com 收集分享

第 1 页共 9 页

sql 优化

一．目的

数据库参数进行优化所获得的性能提升全部加起来只占数据库应用系统性能提

升的 40%左右，其余 60%的系统性能提升全部来自对应用程序的优化。许多优化专家

甚至认为对应用程序的优化可以得到 80%的系统性能提升。因此可以肯定，通过优化

应用程序来对数据库系统进行优化能获得更大的收益。

对应用程序的优化通常可分为两个方面: 源代码的优化和 SQL 语句的优化。由于

涉及到对程序逻辑的改变，源代码的优化在时间成本和风险上代价很高(尤其是对正

在使用中的系统进行优化) 。另一方面，源代码的优化对数据库系统性能的提升收效

有限，因为应用程序对数据库的操作最终要表现为 SQL 语句对数据库的操作。

对 SQL 语句进行优化有以下一些直接原因：

1. SQL 语句是对数据库(数据) 进行操作的惟一途径，应用程序的执行最终要归结为

SQL 语句的执行，SQL 语句的效率对数据库系统的性能起到了决定性的作用。

2. SQL 语句消耗了 70%～90%的数据库资源。

3. SQL 语句独立于程序设计逻辑，对 SQL 语句进行优化不会影响程序逻辑，相对于对

程序源代码的优化，对 SQL 语句的优化在时间成本和风险上的代价都很低。

4. SQL 语句可以有不同的写法，不同的写法在性能上的差异可能很大。

5. SQL 语句易学，难精通。SQL 语句的性能往往同实际运行系统的数据库结构、记录

数量等有关，不存在普遍适用的规律来提升性能。

二. 优化数据库的思想：

1、关键字段建立索引。

2、使用存储过程，它使 SQL 变得更加灵活和高效。

3、备份数据库和清除垃圾数据。

4、SQL 语句语法的优化。

5、清理删除日志。

三．SQL 语句优化的原则：

不要以为只有 SELECT 语句是查询。实际上，带有任何 WHERE 条件的 DML(INSERT、UPDATE、

DELETE)语句中都包含查询要求，在后面的文章中，当说到查询时，不一定只是指 SELECT 语

美河学习在线 www.eimhe.com 收集分享

第 2 页共 9 页

句，也有可能指 DML 语句中的查询部分。

我们知道，SQL 语句同其它语言(如 C 语言)的语句不一样，它是非过程化(non-procedural)

的语句，即当你要取数据时，不需要告诉数据库通过何种途径去取数据，如到底是通过索引

取数据，还是应该将表中的每行数据都取出来，然后再通过一一比较的方式取数据(即全表扫

描)。

为了实现一个查询，内核必须为每个查询定制一个查询策略，或为取出符合条件的数据

生成一个执行计划(execution plan)。典型的，对于同一个查询，可能有几个执行计划都符

合要求，都能得到符合条件的数据。例如，参与连接的表可以有多种不同的连接方法，这取

决于连接条件和优化器采用的连接方法。为了在多个执行计划中选择最优的执行计划，优化

器必须使用一些实际的指标来衡量每个执行计划使用的资源(I/0 次数、 CPU 等)，这些资源

也就是我们所说的代价(cost)。如果一个执行计划使用的资源多，我们就说使用执行计划的

代价大。以执行计划的代价大小作为衡量标准，优化器选择代价最小的执行计划作为真正执

行该查询的执行计划，并抛弃其它的执行计划。

3.1 选择最有效率的表名顺序

在 FROM 子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.首先,

扫描第一个表(FROM 子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM 子

句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行

合并.

基础表(Driving Table)是指被最先访问的表(通常以全表扫描的方式被访问).

例如:

表 TAB1 16,384 条记录

表 TAB2 1 条记录

选择 TAB2 作为基础表 (最好的方法)

select count(*) from tab1,tab2 执行时间 0.96 秒 oracle 测试标准

选择 TAB2 作为基础表 (不佳的方法)

select count(*) from tab2,tab1 执行时间 26.09 秒

如果有 3 个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表,

交叉表是指那个被其他表所引用的表.

例如:

EMP 表描述了 LOCATION 表和 CATEGORY 表的交集.

美河学习在线 www.eimhe.com 收集分享

第 3 页共 9 页

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 2000

3.3 WHERE 子句中的连接顺序

采用自下而上的顺序解析 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 …

of 9

5墨值下载

sql优化

相关文档

评论