OLTP与OLAP的区别

OLTP与OLAP的区别

数据处理大致可以分成两大类：联机事务处理OLTP(On-line transaction processing)、联机分析处理OLAP(On-Line Analytical Processing)。OLTP是传统的关系型数据库的主要应用，主要是基本的、日常的事务处理，例如银行交易。OLAP是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。

OLTP，也叫联机事务处理（Online Transaction Processing），表示事务性非常高的系统，一般都是高可用的在线系统，以小的事务以及小的查询为主，评估其系统的时候，一般看其每秒执行的Transaction以及Execute SQL的数量。在这样的系统中，单个数据库每秒处理的Transaction往往超过几百个，或者是几千个，Select 语句的执行量每秒几千甚至几万个。典型的OLTP系统有电子商务系统、银行、证券等。
OLTP系统最容易出现瓶颈的地方就是CPU与磁盘子系统。
（1）CPU出现瓶颈常表现在逻辑读总量与计算性函数或者是过程上，逻辑读总量等于单个语句的逻辑读乘以执行次数，如果单个语句执行速度虽然很快，但是执行次数非常多，那么，也可能会导致很大的逻辑读总量。设计的方法与优化的方法就是减少单个语句的逻辑读，或者是减少它们的执行次数。另外，一些计算型的函数，如自定义函数、decode等的频繁使用，也会消耗大量的CPU时间，造成系统的负载升高，正确的设计方法或者是优化方法，需要尽量避免计算过程，如保存计算结果到统计表就是一个好的方法。
（2）磁盘子系统在OLTP环境中，它的承载能力一般取决于它的IOPS处理能力. 因为在OLTP环境中，磁盘物理读一般都是db file sequential read，也就是单块读，但是这个读的次数非常频繁。如果频繁到磁盘子系统都不能承载其IOPS的时候，就会出现大的性能问题。
OLTP比较常用的设计与优化方式为Cache技术与B-tree索引技术，Cache决定了很多语句不需要从磁盘子系统获得数据，所以，Web cache与Oracle data buffer对OLTP系统是很重要的。另外，在索引使用方面，语句越简单越好，这样执行计划也稳定，而且一定要使用绑定变量，减少语句解析，尽量减少表关联，尽量减少分布式事务，基本不使用分区技术、MV技术、并行技术及位图索引。因为并发量很高，批量更新时要分批快速提交，以避免阻塞的发生。
OLTP 系统是一个数据块变化非常频繁，SQL 语句提交非常频繁的系统。 对于数据块来说，应尽可能让数据块保存在内存当中，对于SQL来说，尽可能使用变量绑定技术来达到SQL重用，减少物理I/O 和重复的SQL 解析，从而极大的改善数据库的性能。
这里影响性能除了绑定变量，还有可能是热块（hot block）。 当一个块被多个用户同时读取时，Oracle 为了维护数据的一致性，需要使用Latch来串行化用户的操作。当一个用户获得了latch后，其他用户就只能等待，获取这个数据块的用户越多，等待就越明显。 这就是热块的问题。 这种热块可能是数据块，也可能是回滚端块。 对于数据块来讲，通常是数据库的数据分布不均匀导致，如果是索引的数据块，可以考虑创建反向索引来达到重新分布数据的目的，对于回滚段数据块，可以适当多增加几个回滚段来避免这种争用。
**OLAP，也叫联机分析处理（Online Analytical Processing）**系统，有的时候也叫DSS决策支持系统，就是我们说的数据仓库。在这样的系统中，语句的执行量不是考核标准，因为一条语句的执行时间可能会非常长，读取的数据也非常多。所以，在这样的系统中，考核的标准往往是磁盘子系统的吞吐量（带宽），如能达到多少MB/s的流量。
磁盘子系统的吞吐量则往往取决于磁盘的个数，这个时候，Cache基本是没有效果的，数据库的读写类型基本上是db file scattered read与direct path read/write。应尽量采用个数比较多的磁盘以及比较大的带宽，如4Gb的光纤接口。
在OLAP系统中，常使用分区技术、并行技术。
分区技术在OLAP系统中的重要性主要体现在数据库管理上，比如数据库加载，可以通过分区交换的方式实现，备份可以通过备份分区表空间实现，删除数据可以通过分区进行删除，至于分区在性能上的影响，它可以使得一些大表的扫描变得很快（只扫描单个分区）。另外，如果分区结合并行的话，也可以使得整个表的扫描会变得很快。总之，分区主要的功能是管理上的方便性，它并不能绝对保证查询性能的提高，有时候分区会带来性能上的提高，有时候会降低。
在OLAP系统中，不需要使用绑定（BIND）变量，因为整个系统的执行量很小，分析时间对于执行时间来说，可以忽略，而且可避免出现错误的执行计划。但是OLAP中可以大量使用位图索引，物化视图，对于大的事务，尽量寻求速度上的优化，没有必要像OLTP要求快速提交，甚至要刻意减慢执行的速度。
绑定变量真正的用途是在OLTP系统中，这个系统通常有这样的特点，用户并发数很大，用户的请求十分密集，并且这些请求的SQL 大多数是可以重复使用的。
对于OLAP系统来说，绝大多数时候数据库上运行着的是报表作业，执行基本上是聚合类的SQL 操作，比如group by，这时候，把优化器模式设置为all_rows是恰当的。 而对于一些分页操作比较多的网站类数据库，设置为first_rows会更好一些。 但有时候对于OLAP 系统，我们又有分页的情况下，我们可以考虑在每条SQL 中用hint。

OLTP与OLAP之间的比较：

OLTP应用的IO特征

OLTP通常是指事务性非常高的在线系统，以小的事务以及小的查询为主，评估其系统的时候，一般看其每秒执行的Transaction以及Execute SQL的数量。在这样的系统中，单个数据库每秒处理的Transaction往往超过几百个，或者是几千个，Select语句的执行量每秒几千甚至几万个。典型的OLTP系统有电子商务系统、银行、证券等：

• 每个I/O非常小，通常为2KB~8KB
• 访问磁盘数据的位置非常随机
• 至少30%的数据是随机写操作
• 联机重做日志是写入非常频繁的顺序写

1、业务特征：每个事务的读，写，更改涉及的数据量非常小，同时有很多用户连接到数据库，使用数据库，要求数据库有很快的响应时间，通常一个事务在几秒内完成，时延要求一般在10-20ms。

2、IO特征：针对DATA LUN，随机小IO，IO大小主要为8KB(IO大小与数据库的Block块大小一致)，读写比约为3:2，读全随机，写有一定合并。针对LOG LUN，多路顺序小IO，大小不定，几乎都是写IO。

OLTP系统最容易出现瓶颈的地方除了服务器的CPU，就是存储系统IOPS处理能力。因为在OLTP环境中，硬盘物理读一般都是db file sequential read，即单个数据块物理读，但是这个读的次数非常频繁。如果频繁到硬盘子系统都不能承载其IOPS的时候，就会出现大的性能问题。

OLAP应用的IO特征

OLAP系统，也称为DSS决策支持系统，就是我们说的数据仓库。在这样的系统中，绝大多数时候数据库上运行着的是报表作业，执行基本上是聚合类的SQL 操作，比如Group by，同时扫描非常多的行，一个查询将花费数小时，甚至数天，一次读取的数据量大;一般无数据修改，或者只有非常少的数据修改：

• 单个I/O很大，典型的值为64KB~1MB
• 读取操作为顺序读取
• 当读取操作进行时，发生的写操作通常在临时表空间内
• 平常对在线日志写入很少，除非在批量加载数据时

1、业务特征：一般很少有数据修改，除非在批量加载数据时;系统调用非常复杂的查询语句，同时扫描非常多的行;一个查询将花费数小时，甚至数天;主要取决于查询语句的复杂程度;查询的输出通常是一个统计值，由group by与order by得出;当读取操作进行时，发生的写操作通常在临时表空间内;平常对在线日志写入很少，除非在批量加载数据时;分析型业务，一般对时延没有要求。

2、IO特征：针对DATA LUN，多路顺序大IO(可以近似认为是随机大IO)，IO大小与主机侧设置的分条大小有关(如512KB)，90%以上为读业务，混合间断读写。针对TMP LUN，随机IO，读写混合(先写后读，计算时写，读临时表时读，大部分是写，占整个业务中很少部分的IO)，IO大小基本为200KB以上大IO。

OLTP系统最容易出现瓶颈的地方是存储系统的带宽。阵列的带宽则往往取决于主机到阵列的前端网络和后端硬盘的个数，这个时候，阵列CACHE基本是没有效果的，数据库的读写类型基本上是db file scattered read与direct path read/write。

在实际应用中，既然OLTP中存放了大量的细节数据，为什么不直接在OLTP上进行分析处理呢?

由于OLTP主要是为了操作数据而设计(操作系统)，用于处理已知的任务和负载：常见的优化在于主码索引和散列，检索特定的记录。去优化某一些特定的查询语句。

而OLAP则是为了分析数据而设计(数据仓库)，其查询的方式往往是复杂且未知的，通常会涉及大量数据在汇总后的计算，这种需要基于多维视图的数据操作在OLTP上执行的时候性能将是非常差的，并且是也是极其危险的。

但是OLAP系统数据来源与各种OLTP数据库。因为OLTP系统存储的数据往往是异质的，所以OLAP系统需要把各种来源于OLTP的异质数据通过转换(ETL)做到同质并且合并。

分开设计与优化

在设计上要特别注意，如在高可用的OLTP环境中，不要盲目地把OLAP的技术拿过来用。

如分区技术，假设不是大范围地使用分区关键字，而采用其它的字段作为where条件，那么，如果是本地索引，将不得不扫描多个索引，而性能变得更为低下。如果是全局索引，又失去分区的意义。

并行技术也是如此，一般在完成大型任务时才使用，如在实际生活中，翻译一本书，可以先安排多个人，每个人翻译不同的章节，这样可以提高翻译速度。如果只是翻译一页书，也去分配不同的人翻译不同的行，再组合起来，就没必要了，因为在分配工作的时间里，一个人或许早就翻译完了。

位图索引也是一样，如果用在OLTP环境中，很容易造成阻塞与死锁。但是，在OLAP环境中，可能会因为其特有的特性，提高OLAP的查询速度。MV也是基本一样，包括触发器等，在DML频繁的OLTP系统上，很容易成为瓶颈，甚至是Library Cache等待，而在OLAP环境上，则可能会因为使用恰当而提高查询速度。

OLTP设计注意事项

事务处理系统数据库应设计为支持：

1.很好的数据放置。对于 OLTP 系统，输入/输出瓶颈是一个尤为关心的问题，原因在于修改整个数据库中数据的用户很多。确定数据的可能访问模式，并将经常访问的数据放在一起。在此过程中，可辅以文件组和 RAID（独立磁盘冗余阵列）系统。
　　2.缩短事务以将长期锁减至最少，提高并发性。在事务期间，避免用户交互。无论何时，只要有可能，就通过执行单个存储过程来处理整个事务。在事务内对表的引用顺序可能会影响并发性。将对经常访问的表的引用置于事务的末尾，以便将控制锁的持续时间减至最短。
　　3.联机备份。OLTP 系统通常的特征是连续操作（一天 24 小时，一周 7 天），为达到此目的，停工时间要保持绝对最短。尽管 Microsoft SQL Server 2000 可以在数据库正在使用时对其进行备份，但是应将备份过程安排在活动不频繁时进行，以使对用户的影响减至最小。
　　4.数据库的高度规范化。尽可能减少冗余信息以提高更新的速度，从而提高并发性。减少数据还可以提高备份的速度，因为只需要备份更少的数据。
　　5.很少或没有历史或聚合数据。可以将很少引用的数据归档到单独的数据库中，或者从经常更新的表中移出，并置于仅含历史数据的表中。这将保持表尽可能地小，从而缩短备份时间，改善查询性能。
　　6.小心使用索引。每次添加或修改行时，必须更新索引。若要避免对经常更新的表进行过多的索引，索引范围应保持较窄。请用索引优化向导设计索引。

OLTP与OLAP关系

概念

OLTP处理的数据是高度结构化的，涉及的事务比较简单，因此复杂的表关联不会严重影响性能。反之，决策支持系统的一个查询可能涉及数万条记录，这时复杂的连接操作会严重影响性能。在OLTP系统中，数据访问路径是已知的，至少是相对固定的，应用程序可以在事务中使用具体的数据结构如表、索引等。而决策支持系统使用的数据不仅有结构化数据，而且有非结构化数据，用户常常是在想要某种数据前才决定去分析该数据。因此，数据仓库系统中一定要为用户设计出更为简明的数据分析模型，这样才能为决策支持提供更为透明的数据访问。 [3]

OLAP是以数据仓库为基础的，其最终数据来源与OLTP一样均来自底层的数据库系统，但由于二者面对的用户不同，OLTP面对的是操作人员和低层管理人员，OLAP面员和高层管理人员。 [3]

联机分析处理 (OLAP) 的概念最早是由关系数据库之父E.F.Codd于1993年提出的，他同时提出了关于OLAP的12条准则。OLAP的提出引起了很大的反响，OLAP作为一类产品同联机事务处理(OLTP) 明显区分开来。

OLAP是使分析人员、管理人员或执行人员能够从多角度对信息进行快速、一致、交互地存取，从而获得对数据的更深入了解的一类软件技术。OLAP的目标是满足决策支持或者满足在多维环境下特定的查询和报表需求，它的技术核心是"维"这个概念。

“维”是人们观察客观世界的角度，是一种高层次的类型划分。“维”一般包含着层次关系，这种层次关系有时会相当复杂。通过把一个实体的多项重要的属性定义为多个维(dimension)，使用户能对不同维上的数据进行比较。因此OLAP也可以说是多维数据分析工具的集合。

OLAP的基本多维分析操作有钻取（roll up和drill down）、切片（slice）和切块（dice）、以及旋转（pivot）、drill across、drill through等。

OLAP工具是针对特定问题的联机数据访问与分析。它通过多维的方式对数据进行分析、查询和报表。维是人们观察数据的特定角度。例如，一个企业在考虑产品的销售情况时，通常从时间、地区和产品的不同角度来深入观察产品的销售情况。这里的时间、地区和产品就是维。而这些维的不同组合和所考察的度量指标构成的多维数组则是OLAP分析的基础，可形式化表示为（维1，维2，……，维n，度量指标），如（地区、时间、产品、销售额）。多维分析是指对以多维形式组织起来的数据采取切片（Slice）、切块（Dice）、钻取（Drill-down和Roll-up）、旋转（Pivot）等各种分析动作，以求剖析数据，使用户能从多个角度、多侧面地观察数据库中的数据，从而深入理解包含在数据中的信息。

根据综合性数据的组织方式的不同，常见的OLAP主要有基于多维数据库的MOLAP及基于关系数据库的ROLAP两种。MOLAP是以多维的方式组织和存储数据，ROLAP则利用现有的关系数据库技术来模拟多维数据。在数据仓库应用中，OLAP应用一般是数据仓库应用的前端工具，同时OLAP工具还可以同数据挖掘工具、统计分析工具配合使用，增强决策分析功能。