Oracle、mysql产品性能优化总结
李万鸿2018-8-7
数据库是产品的一大性能瓶颈，oracle、mysql等数据库的查询速度直接影响产品的性能指标。通过多年的多次实战优化oracle、mysql产品，找到了内在的原理和规律，取得了明显的成效，这里做个总结，希望对大家高效正确使用数据库有所帮助。
数据库性能调整目标：
改善指定类型的SQL语句的性能
改善专门的数据库应用的性能
改善所有同时应用数据库的用户及其应用的所有性能
1 索引的建立和使用
1原理与方法
数据库的查询速度慢是产品性能不达标的主要原因，大多数产品都是读多写少，而提高查询速度的关键在于索引，因此合理设置表的索引是优化的一个核心任务。建立索引可避免全表扫描，快速定位数据，从而提高查询速度。
数据库执行selelct查询，首先会生成执行计划，根据where的查询字段到数据库选取数据，然后join连接、group、order排序。这是数据库的基本原理。Oracle和mysql的sql语句执行顺序相反，Oracle是从右到左，mysql是从左到右。为了提高查询速度，需要把最容易判断出的条件放在查询的最前面，这就是建立索引和使用索引的原理和规律。
为此，在建立索引时，需要综合考虑这张表所对应的sql语句，查询条件有哪些字段，各个字段的类型、取值大小，表的读写情况，然后根据上面的原理，选取字段建立索引。索引的顺序很重要，原则是取值越少的越放在前面，比如validflag，一般取值为0和1，那么通过这个字段，可以很快判断一半的数据，效率很高，所以放在最前面，其余与此类似。
对于where、group、order条件都需要建立索引，group、order要单独建索引。事务是辩证的，凡事有利就有弊，索引利于查询，但不利于写入数据，因此，要把握好这个矛盾，权衡利弊达到最佳效果。索引的数量不易过多，一般建4到5个，每条索引的字段不要超过6个，具体情况根据需求和使用来定，法无定法，因地制宜，随机应变。这样可以在保证写入速度的同时提高查询速度，达到优化的全局整体效果最高。
索引建立后就需要根据索引来修改设sql的查询字段顺序，Oracle和mysql的sql语句执行顺序相反，sql条件的写法顺序相反。根据oracle的sql执行顺序规律，sql查询条件的执行是从右到左的，因此，查询条件的顺序与索引顺序相反。Mysql的查询条件的顺序与索引顺序相同。根据字段取值大小，索引的规律是从小到大， Oracle和mysql都遵循此规律；mysql查询的顺序与索引相同，Oracle查询的顺序与索引相反。这样可以简单明了地建立和使用索引。
一般，索引的第一个字段为查询条件的第一个字段，便于使用索引查询。Oracle9I后的版本，会自动根据查询字段在所有索引中查找，所以查询的第一个字段可以与索引不同。而mysql没有此功能，因此mysql查询的第一个字段必须与索引的第一个字段相同，后面的顺序要一致，这样才能用到索引，不会进行全表扫描。索引的类型为normal，不要设为唯一unique类型，否则引起插入错误。唯一索引是根据业务需求而定的，会产生唯一性的约束，normal索引不会。唯一索引的速度是最快的。
对于特别慢的复杂sql，可以使用explain分析sql，找到耗时的问题点，加以优化。优化后要进行全面压测，保证整个产品所有性能达标，如果有不达标的，要综合分析后再对索引进行调整。
索引的优化越早越好，因为索引是关系到全局的，一张表的索引的变动会影响到成百上千的sql的变动，因此，在设计和使用数据库及成品开发的前期阶段，就要合理设计和正确使用索引。这样可以保证产品的性能一开始就是最优，有效提升产品的质量和开发效率。
2操作步骤
完成以上优化并不难，可通过简单有效的操作进行，一般的工程师都可以实现。对于一张表建索引，可以采用以下办法来逐步完成：
-
找到表所用到的所有sql的查询条件的字段，并加以分析，确定使用频度，可以用word画一张表来统计。
-
根据查询条件和字段合理决定建几个索引，包含哪些字段。这一部很关键，直接影响到查询的性能，索引的数量和包含字段的数量都要合理，不是越多越好，应该尽量精简。
-
为了解索引效果，可以运行一条复杂的sql语句，使用pl/sql或navicat工具运行sql，看第一次查询的速度。查询后重复查询会快很多，因为已经被缓存了，所以只能看第一次查的询时间才能了解真实效果。可修改查询字段的顺序进行查询，比较效果，通过多次试验探索从而决定最佳的设计。
-
索引建好后，需修改用到这张表的所有sql查询条件，按照相同或相反的顺序调整查询字段顺序，然后测试运行，评估效果。
-
全面修改完毕，需进行系统全面压测，评估读写整体性能指标，达到全局最优化。
2 表和字段的创建
索引优化只是数据库性能优化的一部分，表和字段的设计是根本，一个设计合理的数据库，需要在准确把握客户需求的基础上，科学划分表及表的字段。一般，表和字段越少越好，满足三范式，表之间用键关联，不要设外键，不利于表的使用。表的字段数量要控制好，在50个以内。
表需要扩展，一般都设置很多预留字段，这个问题可以优化。有2个新的方法：
-
建立一张扩展表保存所有的扩展字段，extension（id，table_Id，extension _field，extension _field_name,field _value），需要扩展的表正需要增加一个扩展字段extension _field。如果有表需要增加字段，比如user表，可以在user表增加一个扩展字段extension _field，以后需增加补贴薪资类目，新增数据时，在user的扩展字段extension _field中保存Subsidized wages，然后在extension表插入（id，user表的id，“Subsidized wages”，“补贴薪资”，3000），这样就完成了新增一个字段，查询时通过user表的id和Subsidized wages从extension表即刻获取数据。这个办法也很灵活，可以无限扩展。
-
使用json，建立一个扩展字段，类型为text，有需要增加的扩展字段，采用key：value的json方式存入，非常灵活，可以无限扩展。以上面为例，key为Subsidized wages，value为{补贴薪资，3000}。
-
对于表的数据量过大，可以采用分库、分区、分表的方法解决。Mycat是mysql水平分表的利器，可以自动扩展分表，实现读写分离、主从集群、自动完成分页、join等复杂操作，值得使用。
3 Sql语句优化
Sql查询的关键在于先查出量小的数据再去查询量大的数据，用小表驱动大表，尽量在主表使用最多的查询条件，降低主表查询的数据，这是优化复杂sql的秘诀。复杂sql涉及多张表的join，主表的选择至关重要，一定要选数据量小的表为主表，而且主表的查询条件尽量多，尽量把能提前到主表的查询条件放在主表的where里面，这样一开始的查询其数据量可以最少，然后在join关联其他表的数据就会很快，显著提升速度，起到化腐朽为神奇的奇迹效果。
举个例子：
这个sql运行很慢，需几十秒，看执行计划：
-
整个执行计划的COST并不高；
-
纵观整个执行计划，并没有出现SORT操作。那么ORDER BY又是怎么实现的呢？
在执行计划中，有如下一个操作：
INDEX FULL SCAN DESCENDING的对象是PK_CCP_CROSS_DATA，查看了下这个对象：
这个UNIQUE INDEX的字段对象是CCP_CORSS_DATA_ID，而该字段就是ORDER BY的字段对象。查看了下该表的数据量，接近4000万，
在分页ORDER BY时出现INDEX FULL SCAN DESCENDING的操作。
优化方法：等价改写后的SQL如下：
再看执行计划：
成本下降，从之前的560变成了249，超过了一半。降低成本仅仅是优化的手段，优化的目的是让SQL跑得更快，所以真正考验SQL等价改写效果的是SQL的执行效率。
将改写后的SQL在数据库中执行：0.7s，风驰电掣，飞速提升！
成功的秘密就在于在开始就使用查询条件把关键查询的数量降低了，只有15条数据，后面再join就很轻松了：
一、操作符优化
1、IN 操作符
用IN写出来的SQL的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。但是用IN的SQL性能总是比较低的，从Oracle执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别：
ORACLE试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。
推荐方案：在业务密集的SQL当中尽量不采用IN操作符，用EXISTS 方案代替。
2、NOT IN操作符
此操作是强列不推荐使用的，因为它不能应用表的索引。
推荐方案：用NOT EXISTS 方案代替
3、IS NULL 或IS NOT NULL操作（判断字段是否为空）
判断字段是否为空一般是不会应用索引的，因为索引是不索引空值的。
推荐方案：用其它相同功能的操作运算代替，如：a is not null 改为 a>0 或a>’’等。不允许字段为空，而用一个缺省值代替空值，如申请中状态字段不允许为空，缺省为申请。
4、> 及 < 操作符（大于或小于操作符）
大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有100万记录，一个数值型字段A，30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了，因为A>2时ORACLE会先找出为2的记录索引再进行比较，而A>=3时ORACLE则直接找到=3的记录索引。
5、LIKE操作符
LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE ‘%5400%’ 这种查询不会引用索引，而LIKE ‘X5400%’则会引用范围索引。
一个实际例子：用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号 YY_BH LIKE ‘%5400%’ 这个条件会产生全表扫描，如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 则会利用YY_BH的索引进行两个范围的查询，性能肯定大大提高。
6、UNION操作符
UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如：
select * from gc_dfys
union
select * from ls_jg_dfys
这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。
推荐方案：采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。
select * from gc_dfys
union all
select * from ls_jg_dfys
7.join
Join的on条件不为空，left join、right join等join的连接查询走索引，Join 的on 只要连接条件，其余的放在where。
二、SQL书写的影响
1、同一功能同一性能不同写法SQL的影响。
如一个SQL在A程序员写的为 Select * from zl_yhjbqk
B程序员写的为 Select * from dlyx.zl_yhjbqk（带表所有者的前缀）
C程序员写的为 Select * from DLYX.ZLYHJBQK（大写表名）
D程序员写的为 Select * from DLYX.ZLYHJBQK（中间多了空格）
以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的，但是从ORACLE共享内存SGA的原理，可以得出ORACLE对每个SQL 都会对其进行一次分析，并且占用共享内存，如果将SQL的字符串及格式写得完全相同，则ORACLE只会分析一次，共享内存也只会留下一次的分析结果，这不仅可以减少分析SQL的时间，而且可以减少共享内存重复的信息，ORACLE也可以准确统计SQL的执行频率。
2、WHERE后面的条件顺序影响
WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响。如：
Select * from zl_yhjbqk where dy_dj = '1KV以下' and xh_bz=1
Select * from zl_yhjbqk where xh_bz=1 and dy_dj = '1KV以下'
以上两个SQL中dy_dj（电压等级）及xh_bz（销户标志）两个字段都没进行索引，所以执行的时候都是全表扫描，第一条SQL的dy_dj = '1KV以下'条件在记录集内比率为99%，而xh_bz=1的比率只为0.5%，在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较，而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较，以此可以得出第二条SQL的CPU占用率明显比第一条低。
3、查询表顺序的影响
在FROM后面的表中的列表顺序会对SQL执行性能影响，在没有索引及ORACLE没有对表进行统计分析的情况下，ORACLE会按表出现的顺序进行链接，由此可见表的顺序不对时会产生十分耗服物器资源的数据交叉。（注：如果对表进行了统计分析，ORACLE会自动先进小表的链接，再进行大表的链接）
三、SQL语句索引的利用
1、操作符优化（同上）
2、对条件字段的一些优化
采用函数处理的字段不能利用索引，如：
substr(hbs_bh,1,4)=’5400’，优化处理：hbs_bh like ‘5400%’
trunc(sk_rq)=trunc(sysdate)， 优化处理：sk_rq>=trunc(sysdate) and sk_rq<trunc(sysdate+1)
进行了显式或隐式的运算的字段不能进行索引，如：ss_df+20>50，优化处理：ss_df>30
‘X’ || hbs_bh>’X5400021452’，优化处理：hbs_bh>’5400021542’
sk_rq+5=sysdate，优化处理：sk_rq=sysdate-5
hbs_bh=5401002554，优化处理：hbs_bh=’ 5401002554’，注：此条件对hbs_bh 进行隐式的to_number转换，因为hbs_bh字段是字符型。
条件内包括了多个本表的字段运算时不能进行索引，如：ys_df>cx_df，无法进行优化
qc_bh || kh_bh=’5400250000’，优化处理：qc_bh=’5400’ and kh_bh=’250000’
四、其他
1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
3.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20
5.in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3
6.下面的查询也将导致全表扫描：
select id from t where name like '%abc%'
若要提高效率，可以考虑全文检索。
7.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：
select id from t where num=@num
可以改为强制查询使用索引：
select id from t with(index(索引名)) where num=@num
8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2=100
应改为:
select id from t where num=100*2
9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where substring(name,1,3)='abc' // oracle总有的是substr函数。
select id from t where datediff(day,createdate,'2005-11-30')=0 //查过了确实没有datediff函数。
应改为:
select id from t where name like 'abc%'
select id from t where createdate>='2005-11-30' and createdate<'2005-12-1' //
oracle 中时间应该把char 转换成 date 如： createdate >= to_date('2005-11-30','yyyy-mm-dd')
10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
12.不要写一些没有意义的查询，如需要生成一个空表结构：
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
create table #t(...)
13.很多时候用 exists 代替 in 是一个好的选择：
select num from a where num in(select num from b)
用下面的语句替换：
select num from a where exists(select 1 from b where num=a.num)
14.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
15.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过5个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
16.应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
17.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
18.尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
19.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
20.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
21.避免频繁创建和删除临时表，以减少系统表资源的消耗。
22.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
23.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
24.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
25.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
26.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。
27.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。
28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。
29.尽量避免大事务操作，提高系统并发能力。
30.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
ORACLE的提示功能是比较强的功能，也是比较复杂的应用，并且提示只是给ORACLE执行的一个建议，有时如果出于成本方面的考虑ORACLE也可能不会按提示进行。根据实践应用，一般不建议开发人员应用ORACLE提示，因为各个数据库及服务器性能情况不一样，很可能一个地方性能提升了，但另一个地方却下降了，ORACLE在SQL执行分析方面已经比较成熟，如果分析执行的路径不对首先应在数据库结构（主要是索引）、服务器当前性能（共享内存、磁盘文件碎片）、数据库对象（表、索引）统计信息是否正确这几方面分析。
4 数据库参数优化
数据库系统的参数需要根据硬件和操作系统及使用情况进行优化设置，实践表明，优化前后，性能可以提升几十到上百倍。
-
oracle优化
数据库安装时的优化
在数据库安装时作的优化工作主要是关于db_block_size参数的设置，该参数决定了ORACLE每次操作多少的数据。该参数在安装时一经确认就不能修改，除非重新安装数据库。一般来说，一个中型的应用系统的db_block_size大小可设为4K，而对于一个较大型应用，db_block_size一般设为8K或更大。
一个较大的db_block_size，不仅可以加快系统的运行速度（因为从系统的I/O吞吐能力来说，一次性读取较多的数据可以减少I/O的读取次数），而且可以有较大的系统扩展能力（对于一个系统而言，在它的db_block_size确认之后，它的最大扩展数其实也已经确定下来，而如果一个系统的扩展能力有限的话，则系统容易发生诸如ORA-01562表空间无法扩展之类的错误）。
注：在ORACLE7.3之后的版本中，在创建表空间时，有一个参数autoextent，如果这个参数设置为ON，则在达到最大的扩展值时自动扩展，而不再受最大扩展数的限制。Db_block_size和最大扩展数的对应关系如下：
DB_BLOCK_SIZE | MAX EXTENTS |
512BYTES | 25 |
1K | 57 |
2K | 121 |
4K | 249 |
8K | 505 |
初始化参数的修改
初始化参数的修改参见1.3.2，值得注意的是需要许多参数之间存在关联，需要根据实际需要进行调整（如lm_ress、lm_proc、dlm_locks等参数相互关联）；同时许多参数和操作系统核心参数相关，需要先对操作系统核心参数进行修改（如process参数和OS的信号量、最大进程数相关等）。
优化存储设计
存储设计的原则为：把由用户创建的其余表空间同SYSTEM表空间进行分离，把系统的数据表空间同索引表空间分离，把操作频繁和不经常操作的表划分在不同的表空间中；控制文件和重作日志文件尽可能的分配在不经常使用的磁盘上。
a．表空间的设计原则
l 把用户数据与数据字典数据分开
l 把不同应用组的数据分别放在不同的表空间中
l 为了减少I/O的竞争，把不同表空间的数据文件放在不同的硬盘上
l 分离用户数据和回滚段的数据以防止某个磁盘出现故障丢失数据
l 控制数据的可用性，例如使一些表空间online而另一些表空间offline
l 为特殊类型的数据库使用保留某个表空间，例如高频率的更新操作，只读操作或临时段的存储等
l 索引空间同数据表空间分离
l 建议每一个表空间对应的数据文件的大小应小于2G（某些操作系统不支持大于2G的文件；可以提高备份的速度及其安全性）
l 创建临时表空间时指定temporary选项
b．回滚段设计原则
-
回滚段使用规则
l 一个事务的回退信息可以写到在线的任何的一个回滚段中
l 一个事务的所有回退信息只能写入一个回滚段中
l 多个事务的回退信息可以同时写入同一个回滚段中，甚至同一个extent中
l 回滚段的extent中的所有事务都被提交或回退了，这个extent就可以被重新使用
l 一旦回滚段的某个extent被重新使用，则这个extent中所有的信息都不可用
l 在系统需要更多的回滚空间时，如果回滚段的下一个extent中有活动事务，例程就会扩展此回滚段
l SELECT、INSERT、UPDATE和DELETE语句都可能会使数据库从多个回滚段去读数据
l 回滚段的头永远不会移入当前被回滚的事务为所占用的extent中
l 当回滚段的头进行扩展时，它是按顺序的，不会跳过环中的extent
l 如果回滚段的头不能使用下一个extent，它就会另外分配一个EXTENT，并把它插入到环中
-
SQL语句产生的回退量
用于存储回退信息所需的字节数是由两个方面决定的：事务的类型（如INSERT、UPDATE、DELETE等）和被处理的实际数据。一般来说，插入一条记录比删除同样一条记录产生的回退要少，这是因为当回退一个插入时只需删除该记录，而回退一个删除时则需重新插入那条记录（存储一个ROWID比存储用来重构该记录本身所需的字节数要少）。到目前为止，还没有公式可以计算一条SQL语句所产生的回退量，DBA只能监测到一条SQL语句实际产生了多少回退信息。ORACLE公司根据测试得到的SQL产生的回退量如下：
记录数 | 插入产生的回退量 | 删除产生的回退量 |
1000 | 61949 | 114290 |
10000 | 621456 | 1143029 |
此外，更改一条记录的一部分比更改整条记录所产生的回退信息要多。请看下面所列出的数据信息：
记录数 | 更改3列产生的回退量 | 更改8列产生的回退量 |
1000 | 81192 | 101192 |
10000 | 821472 | 1014383 |
可以通过对V$ROLLSTAT视图查询回滚段的使用情况。
-
回滚段设计原则
l 对于OLTP应用，回滚段的设计原则为：使用大量（30～50个）的小回滚段（例如initial 128k，next 128k，minextents 10，optimal 2m），设置初始化参数中的transaction_per_rollback_segment=6～10以减少回滚段的竞争。
l 对于批处理操作，回滚段的设计原则是：使用少量（<10个）的大回滚段（例如initial 10m，next 10m，minextents 10，optimal 100m）以避免出现诸如ORA-01562、ORA-01555之类的错误影响应用正常运行。
l 对于混合型应用（大量的OLTP应用及少量批处理应用），回滚段设计原则是使用大量在线的小回滚段结合少量离线的大回滚段。一旦需要进行批处理操作，使用以下语句使用大回滚段：
svrmgrl > alter rollback segment r01 online;
svrmgrl > set transaction use rollback segment r01;
svrmgrl > SQL 语句
svrmgrl > alter rollback segment r01 offline;
在PL/SQL中，可以使用ORACLE的包进行回滚段的指定。
系统设计优化
a．数据库块结构
在ORACLE数据库中，数据的存储是以块（block）为基本单位，块的大小即数据库创建时确定的db_block_size的大小。多个块构成一个扩展区域（extent），多个扩展区域构成一个段（segment）。
数据块的结构如下所示：
公用的变长标题（common and variable header） A |
表目录（table directory） B |
行目录（row directory） C |
未用空间（free space） D |
行数据（row data） E |
l A包含一般的块信息，如块地址、段的类型（数据段、索引段或回滚段等）
l B包含所存放的表的信息
l C包含块中的行信息（如行数据区中的每一行的行地址）。
l D包含可用于插入新行及修改
l E包含在表或索引数据。需要的附加空间的行或额外的事物头。
b．表设计
l PCTFREE和PCTUSED
在建表时用到关于存储空间的参数是PCTFREE和PCTUSED，这两个参数构成了每一数据块的总体构成：PCTFREE参数为块中保留的未用空间百分比，用于修改块中已存在行；PCTUSED为当一数据块被填满后（由PCTFREE决定），新行不能被插入该块，只有块的使用率低于PCTUSED时，才可插入，在达到PCTUSED值之前，块中的未用空间仅用于修改行；PCTUSED和PCTFREE值的总和不能超过100， PCTFEE参数尤为重要。
注：一个数据库表中的每一条记录以一行片（ROW PIECE）或多个行片形式存储：如果一个整行可插入到单个数据块中，那该行初始存储为一个行片；如果不能插入到单个数据块或者由于对已存在的行进行修改时引起超出该块，则可能会发生行链接和行迁移（行链接------ROW CHAINED：一条记录使用多个行片进行存储，若数据在一个数据块中无法存放，而在其余块中也无法找到有足够的空间存放该行数据，则将发生行链接。行迁移------ROW MIGRATION：如果在其余块中能找到足够的空间存放该行的数据，那么，这一整行的数据就全部转移到另一能够容纳下该行数据的块中即称为行迁移）。行迁移导致数据从一个数据块移到另一数据块，加大了系统I/O负担；行链接则导致对于数据的查找将进行跨块查询，也大大地增加了系统的I/O负担。
如果PCTFREE参数的设置太小，将导致行链接问题。使用以下语句可以查找哪些表发生了行链接问题：
Analyze table table_name list chained rows into chained_rows；
执行该操作前需执行?/rdbms/admin/utlchain.sql脚本创建CHAINED_ROWS表。如果确认系统存在大量的行链接，需要对这些数据进行处理（将发生行链接的数据存放在临时表中；删除这部分数据；修改表的PCTFREE值；再把临时表的数据插入到表中）。
l INITRANS
ORACLE在对表或索引进行操作之前，对每一数据块的修改，都要在块中申请一个事务项（事务项值的大小依赖于OS）。INITRANS参数保证可同时并发修改块的最小事务数，它避免动态事务项的开销，INITRANS参数太小则在ORACLE中并发操作多时可能导致锁升级（行级锁à块级锁）。
l STORAGE
ORACLE公司建议表的扩展次数控制在5个以下，因此需要根据实际的数据量计算所需要的表大小设置合适的INITIAL和NEXT存储策略；建议PCTINCREASE设为0以避免诸如表空间无法扩展等错误。
l 对表的空间大小的确定
对表所需空间大小可以进行如下的估算（db_block_size=2k）：
max(4,ceil(ROW_COUNT/((round((1958-(initrans*23))*((100-PCT_FREE)/100))/ADJ_ROW_SIZE))))*2
其中：
n 一个块的实际可用字节数为1958bytes
n 每个INITRANS使用23bytes
n PCT_FREE为建表时要指定的值
n 表中每行估计的已调试的行大小（ADJ_ROW_SIZE）
n 表的估计行数（ROW_COUNT）
n ceil取加1的整数，round进行四舍五入
对于已有数据的旧的数据库结构中，对表占用空间大小的计算公式如下：
select avg(nvl(vsize(col1),0)) +…+ avg(nvl(vsize(coln),n)) from table_name;
通过对现有的建表策略及数据量的分析，可以确定一个良好的建表策略以提高应用的效率及空间的利用率。
c．索引设计
l 索引的存储参数同表的存储参数设置
l 创建索引的限制
一个索引最多可包含16列，索引项为每一列的数据值的连串，按指定的列的顺序连串。这一顺序对ORACLE如何使用该索引非常重要。ORACLE在一张表上可建立多个索引，索引的数目没有限制。但是应该注意增加索引会增加维护表所需的处理时间。建议只有当要索引的数据在表中所占的数据量，少于总数据量的25%时，使用索引才会提高查询的速度，否则对表的全表扫描的速度将比使用索引的速度快。
l 索引空间大小的确认
max(4,1.01*(ROW_COUNT/((floor(((2048-113-(initrans*23))*(1-(pct_free/100)))/((10+uniqueness)+number_col_index+(total_col_length))))))*2)
其中：
n 表的估计行数（ROW_COUNT）
n 块的实际可用行数（1935或2048-113）
n 为索引指定的自由百分比（PCT_FREE）
n 索引是否是唯一的，对字节数是有影响的（如果索引是唯一的，UNIQUENESS变量为1，否则为0）
n 索引的列数（NUMBER_COL_INDEX）
索引的估计长度（NUMBER_COL_LENGTH）
-
mysql优化
93
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
