database 什么是 OLTP 和 OLAP。它们之间有什么区别？

What are OLTP and OLAP. What is the difference between them?

提问by Amarnath R Shenoy

Actually what do they mean? All articles I find about them don't give me an idea, or my knowledge is too insufficient to understand it.

其实他们是什么意思？我找到的所有关于它们的文章都没有给我一个想法，或者我的知识太少而无法理解。

Will some one give me some resources with which I can learn this from scratch.

有人会给我一些资源，让我可以从头开始学习。

回答by Nagaraj S

Here you will find a better solution OLTP vs. OLAP

在这里你会找到更好的解决方案OLTP vs. OLAP

• OLTP(On-line Transaction Processing) is involved in the operation of a particular system. OLTP is characterized by a large number of short on-line transactions (INSERT, UPDATE, DELETE). The main emphasis for OLTP systems is put on very fast query processing, maintaining data integrity in multi-access environments and an effectiveness measured by number of transactions per second. In OLTP database there is detailed and current data, and schema used to store transactional databases is the entity model (usually 3NF). It involves Queries accessing individual record like Update your Email in Company database.

• OLAP(On-line Analytical Processing) deals with Historical Data or Archival Data. OLAP is characterized by relatively low volume of transactions. Queries are often very complex and involve aggregations. For OLAP systems a response time is an effectiveness measure. OLAP applications are widely used by Data Mining techniques. In OLAP database there is aggregated, historical data, stored in multi-dimensional schemas (usually star schema). Sometime query need to access large amount of data in Management records like what was the profit of your company in last year.

• OLTP（在线事务处理）涉及特定系统的操作。OLTP 的特点是有大量短的在线事务（INSERT、UPDATE、DELETE）。OLTP 系统的主要重点是非常快速的查询处理、在多访问环境中保持数据完整性以及通过每秒事务数衡量的有效性。在 OLTP 数据库中有详细的和当前的数据，用于存储事务数据库的模式是实体模型（通常是 3NF）。它涉及访问个人记录的查询，例如在公司数据库中更新您的电子邮件。

• OLAP（在线分析处理）处理历史数据或档案数据。OLAP 的特点是交易量相对较低。查询通常非常复杂并且涉及聚合。对于 OLAP 系统，响应时间是一种有效性度量。OLAP 应用程序被数据挖掘技术广泛使用。在 OLAP 数据库中有聚合的历史数据，存储在多维模式（通常是星型模式）中。有时查询需要访问管理记录中的大量数据，例如您公司去年的利润是多少。

回答by nha

Very short answer :

非常简短的回答：

Different databases have different uses. I'm not a database expert. Rule of thumb:

不同的数据库有不同的用途。我不是数据库专家。经验法则：

• if you are doing analytics (ex. aggregating historical data) use OLAP
• if you are doing transactions (ex. adding/removing orders on an e-commerce cart) use OLTP

• 如果您正在进行分析（例如聚合历史数据），请使用 OLAP
• 如果您正在进行交易（例如在电子商务购物车上添加/删除订单），请使用 OLTP

Short answer:

简短的回答：

Let's consider two example scenarios:

让我们考虑两个示例场景：

Scenario 1:

场景一：

You are building an online store/website, and you want to be able to:

您正在建立一个在线商店/网站，并且您希望能够：

• store user data, passwords, previous transactions...
• store actual products, their associated prices

• 存储用户数据、密码、以前的交易...
• 存储实际产品及其相关价格

You want to be able to find data for a particular user, change its name... basically perform INSERT, UPDATE, DELETE operations on user data. Same with products, etc.

您希望能够为特定用户查找数据，更改其名称...基本上是对用户数据执行 INSERT、UPDATE、DELETE 操作。与产品等相同。

You want to be able to make transactions, possibly involving a user buying a product (that's a relation). Then OLTP is probably a good fit.

您希望能够进行交易，可能涉及用户购买产品（这是一种关系）。那么 OLTP 可能是一个很好的选择。

Scenario 2:

场景2：

You have an online store/website, and you want to compute things like

您有一个在线商店/网站，并且想要计算诸如

• the "total money spent by all users"
• "what is the most sold product"

• “所有用户花费的总金额”
• “什么是最畅销的产品”

This falls into the analytics/business intelligence domain, and therefore OLAP is probably more suited.

这属于分析/商业智能领域，因此 OLAP 可能更适合。

If you think in terms of "It would be nice to know how/what/how much"..., and that involves all "objects" of one or more kind (ex. all the users and most of the products to know the total spent) then OLAP is probably better suited.

如果您从“知道如何/什么/多少”的角度考虑……，并且涉及一种或多种类型的所有“对象”（例如，所有用户和大多数产品都知道总花费）那么 OLAP 可能更适合。

Longer answer:

更长的答案：

Of course things are not so simple. That's why we have to use short tags like OLTPand OLAPin the first place. Each database should be evaluated independently in the end.

当然事情并没有那么简单。这就是为什么我们必须首先使用像OLTP和这样的短标签OLAP。最后应该独立评估每个数据库。

So what could be the fundamental difference between OLAP and OLTP?

那么 OLAP 和 OLTP 之间的根本区别是什么？

Well, databases have to store data somewhere. It shouldn't be surprising that the way the data is stored heavily reflects the possible use of said data. Data is usually stored on a hard drive. Let's think of a hard drive as a really wide sheet of paper, where we can read and write things. There are two ways to organize our reads and writes so that they can be efficient and fast.

好吧，数据库必须在某处存储数据。数据的存储方式在很大程度上反映了所述数据的可能用途，这一点不足为奇。数据通常存储在硬盘上。让我们把硬盘想象成一张非常宽的纸，我们可以在上面读写东西。有两种方法可以组织我们的读取和写入，以便它们既高效又快速。

One wayis to make a book that is a bit like a phone book. On each page of the book, we store the information regarding a particular user. Now that's nice, we can find the information for a particular user very easily! Just jump to the page! We can even have a special page at the beginning to tell us on which page the users are if we want. But on the other hand, if we want to find, say, how much money all of our users spent then we would have to read every page, i.e. the whole book! That would be a row-based book/database (OLTP). The optional page at the beginning would be the index.

一种方法是制作一本有点像电话簿的书。在本书的每一页上，我们都存储有关特定用户的信息。现在很好，我们可以很容易地找到特定用户的信息！直接跳到页面！如果我们愿意，我们甚至可以在开始时有一个特殊页面来告诉我们用户在哪个页面上。但另一方面，如果我们想知道所有用户花了多少钱，那么我们就必须阅读每一页，即整本书！那将是一个基于行的书籍/数据库 (OLTP)。开头的可选页面将是索引。

Another wayto use our big sheet of paper is to make an accounting book. I'm no accountant, but let's imagine that we would have a page for "expenditures", "purchases"... That's nice because now we can query things like "give me the total revenue" very quickly (just read the "purchases" page). We can also ask for more involved things like "give me the top ten products sold" and still have acceptable performance. But now consider how painful it would be to find the expenditures for a particular user. You would have to go through the whole list of everyone's expenditures and filter the ones of that particular user, then sum them. Which basically amounts to "read the whole book" again. That would be a column-based database (OLAP).

另一种使用我们大张纸的方法是制作会计账簿。我不是会计师，但让我们想象一下，我们会有一个页面用于“支出”、“采购”……这很好，因为现在我们可以非常快速地查询诸如“给我总收入”之类的内容（只需阅读“采购” “ 页）。我们还可以要求更多涉及的事情，例如“给我销售的前十名产品”，并且仍然具有可接受的性能。但是现在考虑一下为特定用户查找支出是多么痛苦。您必须查看每个人的整个支出清单并过滤该特定用户的支出，然后将它们相加。这基本上相当于再次“阅读整本书”。那将是一个基于列的数据库 (OLAP)。

It follows that:

它如下：

• OLTPdatabases are meant to be used to do many small transactions, and usually serve as a "single source of truth".

• OLAPdatabases on the other hand are more suited for analytics, data mining, fewer queries but they are usually bigger (they operate on more data).

• OLTP数据库旨在用于进行许多小交易，通常用作“单一事实来源”。

• OLAP另一方面，数据库更适合分析、数据挖掘、更少的查询，但它们通常更大（它们对更多数据进行操作）。

It's a bit more involved than that of course and that's a 20 000 feet overview of how databases differ, but it allows me not to get lost in a sea of acronyms.

这当然比这更复杂，这是对数据库如何不同的 20 000 英尺的概述，但它让我不会迷失在首字母缩略词的海洋中。

Speaking of acronyms:

说到缩略词：

• OLTP = Online transaction processing
• OLAP = Online analytical processing

• OLTP = 在线事务处理
• OLAP = 在线分析处理

To read a bit further, here are some relevant links which heavily inspired my answer:

为了进一步阅读，这里有一些相关链接，它们极大地启发了我的回答：

• http://datawarehouse4u.info/OLTP-vs-OLAP.html
• http://www.ibmsystemsmag.com/Blogs/You-and-i/Archive/db-102-database-orientation-row-vs-column/
• http://martinfowler.com/bliki/DataLake.html

• http://datawarehouse4u.info/OLTP-vs-OLAP.html
• http://www.ibmsystemsmag.com/Blogs/You-and-i/Archive/db-102-database-orientation-row-vs-column/
• http://martinfowler.com/bliki/DataLake.html

回答by Serhii Burkovskyi

The difference is quite simple:

区别很简单：

OLTP (Online Transaction Processing)

OLTP（在线事务处理）

OLTP is a class of information systems that facilitate and manage transaction-oriented applications. OLTP has also been used to refer to processing in which the system responds immediately to user requests. Online transaction processing applications are high throughput and insert or update-intensive in database management. Some examples of OLTP systems include order entry, retail sales, and financial transaction systems.

OLTP 是一类促进和管理面向事务的应用程序的信息系统。OLTP 也被用来指代系统立即响应用户请求的处理。在线事务处理应用程序在数据库管理中具有高吞吐量和插入或更新密集型的特点。OLTP 系统的一些示例包括订单输入、零售和金融交易系统。

OLAP (Online Analytical Processing)

OLAP（在线分析处理）

OLAP is part of the broader category of business intelligence, which also encompasses relational database, report writing and data mining. Typical applications of OLAP include business reporting for sales, marketing, management reporting, business process management (BPM), budgeting and forecasting, financial reporting and similar areas.

OLAP 是更广泛的商业智能类别的一部分，它还包括关系数据库、报告编写和数据挖掘。OLAP 的典型应用包括销售、营销、管理报告、业务流程管理 (BPM)、预算和预测、财务报告和类似领域的业务报告。

See more details OLTP and OLAP

查看更多细节OLTP 和 OLAP

回答by rakesh singh

OLTP-: The oltp stands for online transaction processings which is used for managing the current day to day data information . OLAP-: The olap stand for online analytical processing which is used to maintain the past history of the data and mainly used for data analysis and it can also be called as warehouse.

OLTP-：oltp 代表在线事务处理，用于管理当前的日常数据信息。OLAP-：olap代表在线分析处理，用于维护数据的过去历史，主要用于数据分析，也可以称为仓库。

回答by vikrant solanke

oltp- mostly used for business transaction.used to collect business data.In sql we use insert,update and delete command for retrieving small source of data.like wise they are highly normalised.... OLTP Mostly used for maintaining the data integrity.

oltp-主要用于业务事务。用于收集业务数据。在sql中，我们使用插入、更新和删除命令来检索小数据源。同样，它们是高度规范化的.... OLTP主要用于维护数据完整性。

olap- mostly use for reporting,data mining and business analytic purpose. for the large or bulk data.deliberately it is de-normalised. it stores Historical data..

olap - 主要用于报告、数据挖掘和业务分析目的。对于大数据或批量数据。故意将其反规范化。它存储历史数据..