怎样进行sql数据库的优化

在数据库应用系统中编写可执行的SQL语句可以有多种方式实现，但哪一条是最佳方案却难以确定。为了解决这一问题，有必要对SQL实施优化。简单地说，SQL语句的优化就是将性能低下的SQL语句转换成达到同样目的的性能更好的SQL语句。

优化SQL语句的原因

数据库系统的生命周期可以分成: 设计、开发和成品三个阶段。在设计阶段进行优化的成本最低，收益最大。在成品阶段进行优化的成本最高，收益最小。如果将一个数据库系统比喻成一座楼房，在楼房建好后进行矫正往往成本很高而收效很小（甚至可能根本无法矫正），而在楼房设计、生产阶段控制好每块砖瓦的质量就能达到花费小而见效高的目的。

为了获得最大效益，人们常需要对数据库进行优化。数据库的优化通常可以通过对网络、硬件、 *** 作系统、数据库参数和应用程序的优化来进行。根据统计，对网络、硬件、 *** 作系统、数据库参数进行优化所获得的性能提升全部加起来只占数据库应用系统性能提升的40%左右，其余60%的系统性能提升全部来自对应用程序的优化。许多优化专家甚至认为对应用程序的优化可以得到80%的系统性能提升。因此可以肯定，通过优化应用程序来对数据库系统进行优化能获得更大的收益。

对应用程序的优化通常可分为两个方面: 源代码的优化和SQL语句的优化。由于涉及到对程序逻辑的改变，源代码的优化在时间成本和风险上代价很高(尤其是对正在使用中的系统进行优化) 。另一方面，源代码的优化对数据库系统性能的提升收效有限，因为应用程序对数据库的 *** 作最终要表现为SQL语句对数据库的 *** 作。

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

1 SQL语句是对数据库(数据) 进行 *** 作的惟一途径，应用程序的执行最终要归结为SQL语句的执行，SQL语句的效率对数据库系统的性能起到了决定性的作用。

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

3 SQL语句独立于程序设计逻辑，对SQL语句进行优化不会影响程序逻辑，相对于对程序源代码的优化，对SQL语句的优化在时间成本和风险上的代价都很低。

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

5 SQL语句易学，难精通。SQL语句的性能往往同实际运行系统的数据库结构、记录数量等有关，不存在普遍适用的规律来提升性能。

传统的优化方法

SQL程序人员在传统上采用手工重写来对SQL语句进行优化。这主要依靠DBA或资深程序员对SQL语句执行计划的分析，依靠经验，尝试重写SQL语句，然后对结果和性能进行比较以试图找到性能较佳的SQL语句。这种做法存在着以下不足：

1 无法找出SQL语句的所有可能写法。很可能花费了大量的时间也无法找到性能较佳的SQL语句。即便找到了某个性能较佳的SQL语句也无法知道是否存在性能更好的写法。

2 非常依赖于人的经验，经验的多寡往往决定了优化后SQL语句的性能。

3 非常耗时间。重写-->校验正确性-->比较性能，这一循环过程需要大量的时间。

根据传统的SQL优化工具的功能，人们一般将优化工具分为以下三代产品：

第一代的SQL优化工具是执行计划分析工具。这类工具对输入的SQL语句从数据库提取执行计划，并解释执行计划中关键字的含义。

第二代的SQL优化工具只能提供增加索引的建议，它通过对输入的SQL语句的执行计划的分析来产生是否要增加索引的建议。这类工具存在着致命的缺点——只分析了一条SQL语句就得出增加某个索引的结论，根本不理会(实际上也无法评估到)增加的索引对整体数据库系统性能的影响。

第三代工具是利用人工智能实现自动SQL优化。

人工智能自动SQL优化

随着人工智能技术的发展和在数据库优化领域应用的深入，在20世纪90年代末优化技术取得了突破性的进展，出现了人工智能自动SQL优化。人工智能自动SQL优化的本质就是借助人工智能技术，自动对SQL语句进行重写，找到性能最好的等效SQL语句。LECCO SQL Expert就采用了这种人工智能技术，其SQL Expert支持Oracle、Sybase、MS SQL Server和IBM DB2数据库平台。其突出特点是自动优化SQL语句。除此以外，还可以以人工智能知识库“反馈式搜索引擎”来重写SQL语句，并找出所有等效的SQL语句及可能的执行计划，通过测试运行为应用程序和数据库自动找到性能最好的SQL语句，提供微秒级的计时; 能够优化Web应用程序和有大量用户的在线事务处理中运行时间很短的SQL语句; 能通过比较源SQL和待选SQL的不同之处，为开发人员提供“边做边学式训练”，迅速提高开发人员的SQL编程技能等等。

该工具针对数据库应用的开发和维护阶段提供了数个特别的模块：SQL语法优化器、PL/SQL集成化开发调试环境(IDE)、扫描器、数据库监视器等。其核心模块之一“SQL 语法优化器”的工作原理大致如下：输入一条源SQL语句，“人工智能反馈式搜索引擎”对输入的SQL语句结合检测到的数据库结构和索引进行重写，产生N条等效的SQL语句输出，产生的N条等效SQL语句再送入“人工智能反馈式搜索引擎”进行重写，直至无法产生新的输出或搜索限额满，接下来对输出的SQL语句进行过滤，选出具有不同执行计划的SQL语句(不同的执行计划意味着不同的执行效率)，最后，对得到的SQL语句进行批量测试，找出性能最好的SQL语句（参见下图）。

图 人工智能自动SQL优化示意图

LECCO SQL Expert不仅能够找到最佳的SQL语句，它所提供的“边做边学式训练”还能够教会开发人员和数据库管理员如何写出性能最好的SQL语句。LECCO SQL Expert的SQL语句自动优化功能使SQL的优化变得极其简单，只要能够写出SQL语句，它就能帮开发人员找到最好性能的写法。

小 结

SQL语句是数据库应用中一个非常关键的部分，它执行性能的高低直接影响着应用程序的运行效率。正因为如此，人们在SQL语句的优化上投入了很大的精力，出现了许多SQL语句优化工具。随着人工智能等相关技术的日益成熟, 肯定还会有更多更好的工具出现，这将会给开发人员提供更多的帮助。

几个简单的步骤大幅提高Oracle性能 我优化数据库的三板斧

数据库优化的讨论可以说是一个永恒的主题 资深的Oracle优化人员通常会要求提出性能问题的人对数据库做一个statspack 贴出数据库配置等等 还有的人认为要抓出执行最慢的语句来进行优化 但实际情况是 提出疑问的人很可能根本不懂执行计划 更不要说statspack了 而我认为 数据库优化 应该首先从大的方面考虑 网络 服务器硬件配置 *** 作系统配置 Oracle服务器配置 数据结构组织 然后才是具体的调整 实际上网络 硬件等往往无法决定更换 应用程序一般也无法修改 因此应该着重从数据库配置 数据结构上来下手 首先让数据库有一个良好的配置 然后再考虑具体优化某些过慢的语句 我在给我的用户系统进行优化的过程中 总结了一些基本的 简单易行的办法来优化数据库 算是我的三板斧 呵呵 不过请注意 这些不一定普遍使用 甚至有的会有副作用 但是对OLTP系统 基于成本的数据库往往行之有效 不妨试试 （注 附件是Burleson写的用来报告数据库性能等信息的脚本 本文用到）

一．设置合适的SGA

常常有人抱怨服务器硬件很好 但是Oracle就是很慢 很可能是内存分配不合理造成的 ( )假设内存有 M 这通常是小型应用 建议Oracle的SGA大约 M 其中 共享池（SHARED_POOL_SIZE）可以设置 M到 M 根据实际的用户数 查询等来定 数据块缓冲区可以大致分配 M M i下需要设置DB_BLOCK_BUFFERS DB_BLOCK_BUFFERDB_BLOCK_SIZE等于数据块缓冲区大小 i 下的数据缓冲区可以用db_cache_size来直接分配

( )假设内存有 G Oracle 的SGA可以考虑分配 M 共享池分配 M到 M 数据缓冲区分配 M到 M

( )内存 G SGA可以考虑分配 G 共享池 M到 M 剩下的给数据块缓冲区

( )内存 G以上 共享池 M到 M就足够啦 再多也没有太大帮助 (Biti_rainy有专述)数据缓冲区是尽可能的大 但是一定要注意两个问题 一是要给 *** 作系统和其他应用留够内存 二是对于 位的 *** 作系统 Oracle的SGA有 G的限制 有的 位 *** 作系统上可以突破这个限制 方法还请看Biti的大作吧

二．分析表和索引 更改优化模式

Oracle默认优化模式是CHOOSE 在这种情况下 如果表没有经过分析 经常导致查询使用全表扫描 而不使用索引 这通常导致磁盘I/O太多 而导致查询很慢 如果没有使用执行计划稳定性 则应该把表和索引都分析一下 这样可能直接会使查询速度大幅提升 分析表命令可以用ANALYZE TABLE 分析索引可以用ANALYZE INDEX命令 对于少于 万的表 可以考虑分析整个表 对于很大的表 可以按百分比来分析 但是百分比不能过低 否则生成的统计信息可能不准确 可以通过DBA_TABLES的LAST_ANALYZED列来查看表是否经过分析或分析时间 索引可以通过DBA_INDEXES的LAST_ANALYZED列

下面通过例子来说明分析前后的速度对比 （表CASE_GA_AJZLZ大约有 万数据 有主键）首先在SQLPLUS中打开自动查询执行计划功能 (第一次要执行\RDBMS\ADMIN\utlxplan sql来创建PLAN_TABLE这个表)

SQL> SET AUTOTRACE ON SQL>SET TIMING ON

通过SET AUTOTRACE ON 来查看语句的执行计划 通过SET TIMING ON 来查看语句运行时间

SQL> select count() from CASE_GA_AJZLZ; COUNT() 已用时间: : : Execution Plan SELECT STATEMENT Optimizer=CHOOSE SORT (AGGREGATE) TABLE ACCESS (FULL) OF CASE_GA_AJZLZ ……………………

请注意上面分析中的TABLE ACCESS(FULL) 这说明该语句执行了全表扫描 而且查询使用了 秒 这时表还没有经过分析 下面我们来对该表进行分析

SQL> yze table CASE_GA_AJZLZ pute statistics;

表已分析 已用时间: : : 然后再来查询

SQL> select count() from CASE_GA_AJZLZ; COUNT() 已用时间: : : Execution Plan SELECT STATEMENT Optimizer=FIRST_ROWS (Cost= Card= ) SORT (AGGREGATE) INDEX (FAST FULL SCAN) OF PK_AJZLZ (UNIQUE) (Cost= Card= ) …………………………

请注意 这次时间仅仅用了 秒！这要归功于INDEX(FAST FULL SCAN) 通过分析表 查询使用了PK_AJZLZ索引 磁盘I/O大幅减少 速度也大幅提升！下面的实用语句可以

用来生成分析某个用户的所有表和索引 假设用户是GAXZUSR

SQL> set pagesize SQL> spool d:\ yze_tables sql; SQL> select yze table ||owner|| ||table_name|| pute statistics; from dba_tables where owner= GAXZUSR ; SQL> spool off SQL> spool spool d:\ yze_indexes sql; SQL> select yze index ||owner|| ||index_name|| pute statistics; from dba_indexes where owner= GAXZUSR ; SQL> spool off SQL> @d:\ yze_tables sql SQL> @d:\ yze_indexes sql

解释 上面的语句生成了两个sql文件 分别分析全部的GAXZUSR的表和索引 如果需要按照百分比来分析表 可以修改一下脚本 通过上面的步骤 我们就完成了对表和索引的分析 可以测试一下速度的改进啦 建议定期运行上面的语句 尤其是数据经过大量更新

当然 也可以通过dbms_stats来分析表和索引 更方便一些 但是我仍然习惯上面的方法 因为成功与否会直接提示出来

另外 我们可以将优化模式进行修改 optimizer_mode值可以是RULE CHOOSE FIRST_ROWS和ALL_ROWS 对于OLTP系统 可以改成FIRST_ROWS 来要求查询尽快返回结果 这样即使不用分析 在一般情况下也可以提高查询性能 但是表和索引经过分析后有助于找到最合适的执行计划

三．设置cursor_sharing=FORCE 或SIMILAR

这种方法是 i才开始有的 oracle 不支持 通过设置该参数 可以强制共享只有文字不同的语句解释计划 例如下面两条语句可以共享

SQL> SELECT FROM MYTABLE WHERE NAME= tom SQL> SELECT FROM MYTABLE WHERE NAME= turner

这个方法可以大幅降低缓冲区利用率低的问题 避免语句重新解释 通过这个功能 可以很大程度上解决硬解析带来的性能下降的问题 个人感觉可根据系统的实际情况 决定是否将该参数改成FORCE 该参数默认是exact 不过一定要注意 修改之前 必须先给ORACLE打补丁 否则改之后oracle会占用 %的CPU 无法使用 对于ORACLE i 可以设置成SIMILAR 这个设置综合了FORCE和EXACT的优点 不过请慎用这个功能 这个参数也可能带来很大的负面影响！

四．将常用的小表 索引钉在数据缓存KEEP池中

内存上数据读取速度远远比硬盘中读取要快 据称 内存中数据读的速度是硬盘的 倍！如果资源比较丰富 把常用的小的 而且经常进行全表扫描的表给钉内存中 当然是在好不过了 可以简单的通过ALTER TABLE tablename CACHE来实现 在ORACLE i之后可以使用ALTER TABLE table STORAGE(BUFFER_POOL KEEP) 一般来说 可以考虑把 数据块之内的表放在keep池中 当然要根据内存大小等因素来定 关于如何查出那些表或索引符合条件 可以使用本文提供的access sql和access_report sql 这两个脚本是著名的Oracle专家 Burleson写的 你也可以在读懂了情况下根据实际情况调整一下脚本 对于索引 可以通过ALTER INDEX indexname STORAGE(BUFFER_POOL KEEP)来钉在KEEP池中

将表定在KEEP池中需要做一些准备工作 对于ORACLE i 需要设置DB_KEEP_CACHE_SIZE 对于 i 需要设置buffer_pool_keep 在 i中 还要修改db_block_lru_latches 该参数默认是 无法使用buffer_pool_keep 该参数应该比 CPU数量少 但是要大于 才能设置DB_KEEP_CACHE_BUFFER buffer_pool_keep从db_block_buffers中分配 因此也要小于db_block_buffers 设置好这些参数后 就可以把常用对象永久钉在内存里

五．设置optimizer_max_permutations

对于多表连接查询 如果采用基于成本优化(CBO) ORACLE会计算出很多种运行方案

从中选择出最优方案 这个参数就是设置oracle究竟从多少种方案来选择最优 如果设置太大 那么计算最优方案过程也是时间比较长的 Oracle 和 i默认是 建议改成 对于 i 已经默认是 了

六．调整排序参数

( ) SORT_AREA_SIZE:默认的用来排序的SORT_AREA_SIZE大小是 K 通常显得有点小 一般可以考虑设置成 M（ ） 这个参数不能设置过大 因为每个连接都要分配同样的排序内存

lishixinzhi/Article/program/Oracle/201311/18879

数据,也许你现在觉得没有用,汇总了,将来不知道什么时候就用到

所以不管什么优化方式都是不科学的

只能是说,根据你最紧迫的需求进行处理

比如你的ERP系统由于数据过多而缓慢，那么一是想办法升级硬件、二是想办法优化数据库、三是引用更好的算法

从优化来说，可以加索引、可以改视图、可以优化存储过程，还可以去掉一些目前看无用的数据，而这最后一条往往是见效最快的

所以很多ERP软件采用的是年结的方法，每年建个新数据库，速度会有显著提升

但是，分割的时间段和粒度和企业数据量和规模应该紧密相关

而这么做，损失的是历史数据，或者说牺牲了历史数据的分析方便性，而提升了现有数据处理能力

那么，等你们解决了现有矛盾，需要从历史数据挖掘信息时，就应该引入数据集市技术，独立于ERP系统进行数据的切片和分析。

所以，为了将来能够进行有效分析，我建议你采用的优化手段一定是要改善现有ERP系统处理速度，而同时不损害历史既有数据的方法。这需要你的软件供应商能够支持，或者你作为软件厂商需要考虑。

以上就是关于怎样进行sql数据库的优化全部的内容，包括:怎样进行sql数据库的优化、优化数据库大幅度提高Oracle的性能、如何优化erp数据库等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！