面向行和面向列的数据库在处理信息检索方面的区别
最近，我开始研究HBase（面向列的数据库之一）。在查看源代码时，我的脑海中不断浮现出一个问题。想到这里就想问一下。 我的问题是，面向行的数据库究竟如何处理信息检索（例如选择查询）以及面向列的数据库有何不同。 以及这些数据库在底层平面文件中存储数据的不同之处（归根结底，每个数据库都使用文件）。
如果我在这个问题的任何部分出错，请纠正我。
问候， 克里希纳
Recently, I started working on HBase (one of the column-oriented databases). While going through the source code, one question keeps popping in my head. Thought of asking this.
My question is, how exactly a row-oriented database deals with information retrieval (say a select query) and how different is that when comes to column-oriented database.
And how different these databases store data in the underlying flat files ( at the end of day, every database uses files).
Please do correct me in case I went wrong in any part of this question.
Regards,
Krishna
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您的问题的一个问题是，长期存在的数据库术语“面向列”已被 NOSQL 社区盗用（有些人可能会说“劫持”！）来描述与其最初含义完全不同的东西。 “面向列”的两种含义仍然流行，但它们指的是非常不同的 DBMS 产品。因此，澄清你所谈论的内容通常会很有帮助。在本例中，它是该术语的 NOSQL 含义。
在面向列的数据库的原始含义中，您问题的答案是检索信息的方式没有区别。列存储并不是一种不同的数据模型，它只是内部存储中不同类型的表示形式。
然而，在 NOSQL 社区中，术语“列存储”指的是一种不同类型的数据模型。
这里有很好的解释：
http://dbmsmusings.blogspot.com /2010/03/distinguishing-two-major-types-of_29.html
One problem with your question is that the long-standing database term "column oriented" has been appropriated (some might say "hijacked"!) by the NOSQL community to describe something completely different from what it originally meant. Both meanings of "column oriented" are still current but they refer to very different DBMS products. So it is often helplful to clarify which you are talking about. In this case, it's the NOSQL meaning of the term.
In the original meaning of a column oriented database the answer to your question is that there is no difference in the way you retrieve information. Column store isn't a different data model, it is simply a different type of representation in internal storage.
In the NOSQL community however, the term column store refers to a different kind of data model.
Good explanations here:
http://dbmsmusings.blogspot.com/2010/03/distinguishing-two-major-types-of_29.html
面向行的数据库，又名“传统 RDBMS”（例如 MySQL、Oracle、DB2）使用事务性二级索引更新，在大多数情况下使用类似 B 树的结构作为二级索引
面向列的数据库，又名“NoSQL”（例如 Google Big）表、HBase、Cassandra）使用简化的结构作为主键索引（不是 B 树）。
面向列的数据库不支持“传统”事务性二级索引。用户有责任维护“倒排索引”。
Cassandra 支持类似 B 树的行索引：行中的每个单元格都有一个标题，并且单元格按标题进行物理排序。
另一个（可能非常重要）的区别：对于 Oracle 中的无数条记录，您将需要维护主键 B 树，并且它的大小也将是无数条； “按主键查找”的性能不好。
另一方面，您可以在 Cassandra 或 HBase 中拥有“宽行”，并将相似的“单元”合并为单个宽行； “主键索引”的大小变得小了数百万倍，并且“通过主键查找”速度非常快（而且它不是B树；它是聚集搜索）
Row oriented databases, a.k.a. "traditional RDBMS" (such as MySQL, Oracle, DB2) use transactional secondary index updates, use B-Tree -like structures for secondary indices in most cases
Column oriented databases, a.k.a. "NoSQL" (such as Google Big Table, HBase, Cassandra) use simplified structures for primary key indices (which are not B-Tree)
Column oriented databases do not support "traditional" transactional secondary indices. It is responsibility of a user to maintain "inverted index".
Cassandra supports B-Tree -like index for a row: each cell in a row has a title, and cells are physically sorted by title.
Another (possibly super important) difference: for zillions records in Oracle, you will need to maintain B-Tree for primary key, and it's size will be also zillions-like; performance of "find by primary key" is not good.
On the other hand, you can have "wide rows" in Cassandra or HBase, and unite similar "cells" into single wide row; size of "primary key index" becomes millions times smaller, and "find by primary key" is super fast (and it is not B-Tree; it is clustered search)
如果我理解正确的话，您对底层存储和检索问题更感兴趣，而对 DDL 和定义问题（面向列的数据库的类别）不太感兴趣，对吗？
我假设您了解，几乎所有存储，无论供应商如何，都是某种形式：
在此基础上，每个供应商都有优化和专利专业。例如。 Sybase（行）具有：
下一个问题是，所有供应商（除了 Oracle）都拥有相当复杂的引擎，采用模块化设计，并且 I/O 是在低级别异步处理的，以获得速度。 I/O 的单位是页。通常 OLTP 系统为 2 到 8KB，DSS 为 8 到 64KB。 （请注意，我避免了行与列问题。）因此，无论行/列如何，DSS 引擎都是为大规模检索而构建的，因为在大块中获取更多索引/数据行或列，而 I/O 请求更少。
“大I/O”可以通过一个I/O请求将范围（8页）和更大的分配单元（256页）读入内存来执行。但基本单位是Page。
行与列
。
针对引擎执行的所有查询都必须导航索引，从上述数据存储结构中检索数据行/列。
结果是上面的乘法；
小/大块大小，时间
底层物理结构，时间
行/列方向
这是您正在寻找的吗？ Sybase ASE 有一组上述技术（不是温暖和模糊的）图表，Sybase ASE 是一个 OLTP/DSS 严格面向行的引擎，如果您有兴趣，我可以使用它。
对评论的回复
。
您的意思是说，无论数据库类型如何，最终我们都会归结为页面。
是的。
如果是这种情况，那么数据库的集群将如何完成。让我们采用一个以行方式存储数据的数据库。如果我正在对这种类型的数据库进行集群，那么表结构将如何准确地传送到不同的节点（如果我有多个节点）。这个表结构会链接到一个页面还是会通过不同的机制。
你知道，在我回答这个问题之前，我必须向你致谢。对于像你这样的知识水平的人来说，你已经深入到了那个关键点，获得了洞察力，这是很好的。 Shiva ki Jai！
是的，这就是集群DBMS的关键设计问题，关键的限制问题，首先是与集群相关的各种设计问题；如果供应商能够很好地处理这个问题，那么集群就会运行良好；如果没有，集群就是狗的早餐。
IT 中的一切都受物理定律支配。没有什么是免费的，每个功能都有成本、处理或存储。没有什么神奇的，除了微软的营销手册。
良好的集群DB架构
我不知道所有集群DBMS；我非常了解 Sybase CE 和 Oracle RAC。 Sybase IQ 的应用知识。
.
需要注意的是，Oracle非RAC架构是垃圾，或者更准确地说，不存在；所以 RAC 有一个可以建造的沙地基础。
.
更不用说，稳定性简直就是死熊。
。
Sybase CE只有一年的历史。但这个架构非常出色，它很好地处理了这个关键问题。 SAN 上只有一个页面版本。所有节点都连接到 SAN。任何节点都可以读取或写入该Page。这些节点通过专用 LAN 连接（除了网络上其他所有设备使用的正常客户端-服务器 LAN 之外）。节点协调锁以及一些用于负载平衡等的节点间通信。
.
归根结底，为了获得最大并发性，即使使用 Sybase CE，您也需要对数据库进行逻辑分区，以便将每个节点上的工作负载分开，访问不同的文件路径或共享数据库的单独物理区域。
Sybase IQ 已经 100% 面向列。这是他们的 DW 产品。它已经完成了完整的负载平衡。可以使用集群，但不能使用上述CE意义上的集群。我应该将其包含在
糟糕的集群数据库架构
中。狗早餐类型的集群数据库管理系统会做一些愚蠢的事情。列出一些：
将页面存储在每个节点上[大规模重复]，但随后必须在集群中移动更新的页面
使用 MVCC 来克服问题（但是 MVCC 的开销要大得多，而且实际上会减慢并发速度，所以它是在自相矛盾）
集群不适合专用数据库服务器
基本上，集群对于某些应用程序来说非常有用，但对于专用数据库服务器来说这是一个愚蠢的想法（一个事实在一个地方；一起管理的共享资源；在一个地方管理时最有效的锁争用，因为数据在一个地方）。我永远不会推荐数据库服务器集群。
与 SAN 问题相同。当然，许多人的数据库存储位于 SAN 内，但为了实现最高速度以及与连接到 SAN 的其他服务器的负载问题隔离，没有什么比本地磁盘更接近的了。
与VMWare问题相同。当然，许多人将数据库服务器建立为 VMWare 主机单元，但为了获得最高速度，请消除 VMWare 的开销；为了与机箱中其他主机单元的负载问题隔离，请将其从那里取出到专用硬盒上。
为什么数据库供应商要为集群烦恼
哦，它是有价值的，但不是现在，而是将来。 AFAIC，随着时间的推移，Sybase 架构将占据主导地位，而所有其他架构都将被淘汰。每个供应商都会照常复制它。
Sybase CE 的真正威力是：
真正的 100% 正常运行时间（能够向集群添加节点并删除旧节点进行维护）并且
完全动态负载平衡（假设现有节点是 4 x 四核；添加一个临时 4 x 四核节点；删除旧节点；插入 2 x 四核；启动它；删除临时节点节点下降），然后在 60 秒内，手指不再放在任何键盘上，整个野兽重新平衡。
一家商店可以错开其多个单节点服务器的夜间数据库维护计划，可以节省相当多的资金；他们只是有一些额外的机器用于切换输入/输出。
数据仓库有点不同。它们大多是只读的。因此，将其托管在集群上是没有问题的（许多读取器节点，只有一个写入器节点，没有争用，没有人关心页面在读取时是否正在写入）。 Sybase IQ 就是这样一个产品。
面向列的Sybase CE
Sybase IQ 已经是面向列的并且可以部署在集群中，但不能部署在上述CE 意义上的集群中。列映射到页面。我应该将其包含在上面的良好集群数据库架构中，现已更正。
我不知道组合面向列和行的混合是值得的。
但这个问题的完整答案是，使用纯 Db（不是 DW），例如 Sybase ASE 或 ASE/CE，并实现真正的第六范式数据库。这是最终的归一化，不可约的 NF，具有几个显着的优势，包括速度和易于旋转。它在页面上提供面向列的存储。由于 SQL 不完全支持 6NF，您将需要提供视图来从（存储的）6NF 结构提供 5NF 行。我编写了一个目录扩展，以便我可以生成 SQL 代码供开发人员使用。
If I understand you correctly, you are interested more in the underlying storage and retreival issues, and less in the DDL and definition issues, the categories of column-oriented dbs, right ?
I will assume you understand that virtually all storage, regardless of vendor, is some form of:
On top of that foundation, each vendor has optimisations, and patented specialisations. Eg. Sybase (row) has:
The next issue is that all the vendors (except oracle) have reasonably sophisticated engines, with modular design, and the I/O is handled asynchronously, at a low level, to obtain speed. The Unit of I/O is a Page. Usually 2 to 8KB for OLTP systems and 8 to 64KB for DSS. (notice I an avoiding the Row vs Column issue.) So regardless of row/column the DSS engines are build for mass retrieval, due to getting more Index/Data rows or columns in large blocks, with less I/O requests.
"Large I/O" can be performed by reading Extents (8 Pages) and larger AllocationUnits (256 Pages) into memory with one I/O request. But the basic unit is the Page.
Row vs Column
.
All queries executed against the engine, have to navigate indices, retrieve data rows/columns from the above data storage structures.
The result is a multiplication of the above;
small/large blocksize, times
underlying physical structures, times
Row/Column orientation
Is that what you were looking for ? There is a set of technical (not warm and fuzzy) diagrams of the above for Sybase ASE, a OLTP/DSS strictly row-oriented engine, which I can get my hands on, if you are interested.
Responses to Comments
.
you mean to say that eventually we will boil down to page irrespective of database type.
Yes.
If this is the case, then how clustering of a database will be done. Lets take a database that stores data in row mannered. If I am doing clustering for this type of databases, how exactly will the table structured be carried to different nodes ( if I have more than one node). Will this table structure linked to a page or will it be through a different mechanism.
You know, before I answer the question, I have to acknowledge you. For someone with your level of knowledge, it is excellent that you have penetrated down to that crucial point, obtained that insight. Shiva ki Jai!
Yes, that is the critical design problem of a clustered DBMS, the crucial limiting issue, above all the various design issues related to clustering; that if the vendor handles this issue well, the cluster works well; and if not, the cluster is a dogs breakfast.
Everything in IT is governed by the laws of physics. Nothing is free, every function of feature has a cost, processing or storage. There is no magic, except maybe in MS marketing brochures.
Good Clustered DB Architecture
I do not know all clustered DBMS; I know Sybase CE and Oracle RAC really well. Working knowledge of Sybase IQ.
.
It should be noted that Oracle non-RAC architecture is crap, or more precisely, non-existent; so the RAC has a sandy foundation to build on.
.
Not to mention, the stability sucks dead bears.
.
Sybase CE is only one year old. But the architecture is brilliant, it handles this critical issue really well. There is only one version of the page on a SAN. All nodes are connected to the SAN. Any node can read or write the Page. The nodes are connected by a private LAN (in addition to the normal client-server LAN used by everything else on the network). The nodes co-ordinate the locks plus a bit of inter-node communication for laod-balancing, etc.
.
At the end of the day, for maximum concurrency, even with Sybase CE, you need to logically partition the databases, so that the workload on each node is separated, is accessing diffrent filepaths, or separate physical areas of the shared db.
Sybase IQ is already 100% column oriented. It is their DW offering. It already does full load balancing. It can be used is a cluster, but not clustering in the CE sense described above. I should have included it in
Poor Clustered DB Architecture
The dogs breakfast type of clustered dbms do stupid things. To list a few:
have the pages stored on every node[massive duplication], but then have to move the updated pages around the cluster
use MVCC to overcome the problem (but MVCC is far more overhead and actually slows the concurrency down, so it is fighting itself)
Cluster Unsuitable for Dedicated DB Server
Basically clusters are great for some applications, but it is a stupid idea for dedicated db servers (one fact in one place; shared resources which are administered together; lock contention which is most efficient when managed in one place because the data is in one place). I would never recommend a cluster for a db server.
Same as the SAN problem. Sure, many people have their db storage sitting inside a SAN, but for highest speed, and isolation from the load issues of other servers connected to the SAN, nothing comes close to local disk.
Same as the VMWare problem. Sure, many people have the db server established as a VMWare host unit, but for the highest speed, remove the overhead of VMWare; for isolation from the load issues of other host units in the chassis, get it out of there, onto a dedicated hard box.
Why Do DB Vendors Bother with a Cluster
Oh there is value in it, but not now, in the future. AFAIC, the Sybase architecture will predominate over time, and all others will fall by the wayside. Every vendor will copy it as usual.
The real power of Sybase CE is:
true 100% uptime (being able to add a node to the cluster and take the old node down for maintenance) and
fully dynamic load balancing (say the existing node is 4 x quad core; add a temp 4 x quad core node; take the old node down; insert 2 x quad core; bring it up; take down the temp node down) and then within 60 seconds, with no fingers on any keyboard, the whole beast rebalances.
A shop that can stagger the nightly db maintenance schedule of their several single-node servers, can save a fair amount of money; they just have a a couple of additional machines for switching in/out.
Data Warehouses are a little different. They are mostly read-only. So it is no problem to host it on a cluster (many reader nodes, only one writer node, no contention, no one cares that the pages are being written as they are being read). Sybase IQ is such a product.
Sybase CE for Column Oriented
Sybase IQ is already column oriented and can be deployed in a cluster, but not clustering in the CE sense described above. Columns are mapped to pages. I should have included it in Good Clustered Db Architecture above, corrected now.
I am not aware of hybrids combining column- and row-oriented that are worthwhile.
But the full answer to that question is, use a pure Db (not DW) such as Sybase ASE or ASE/CE, and implement a true Sixth Normal Form database. That is the ultimate Normalisation, the irreducible NF, with several substantial advantages, including speed and ease of pivoting. It provides column-oriented storage on the pages. Due to SQL not supporting 6NF fully, you will need to provide Views to supply 5NF rows from the (stored) 6NF structures. I wrote an extension to the catalogue, so that I could generate the SQL code for developers to use.