什么是CUBRID与1000+列索引的性能 [英] What is the performance of CUBRID with a 1000+ columns with indexes

问题描述

我有一个项目需要我在飞行中创建表。创建表时，它需要在每个表中至少支持1000列。所有INT，日期，BIT将是索引。因此，在一个表上可以有大约400个索引。一旦数据上传到服务器，则不会对表执行任何其他插入或更新。我将使用像lucene这样的库来索引文本。

我的问题是：

<

1. 在一个包含数千列和数百个索引的表上选择时，CUBRID的性能如何？


2. CUBRID是否具有用于运行即席查询和管理表的Windows GUI界面


3. DDL和DML语句可以在同一事务中吗？

我很清楚这里的数据库规范化设计问题。

解决方案

免责声明：我已经完全研究了归一化问题，并且得出结论，创建许多表和列是最好的解决方案。

ol>

是的。

是的。

长答案：

1. 如提到的CUBRID RDBMS大小限制< a>，只要您有足够的磁盘空间，您就可以创建无限数量的表，每个表具有无限数量的索引。
   
   
   然而，每个表的列数限制为6400，




2. 这取决于您要拥有的记录数量。如果你在一行中没有这么多的列，这不会太重要。记录数不仅影响数据卷大小（存储数据的磁盘上的文件），还会影响存储索引键和值的索引卷大小。对于您当前的模式设计，您有成千上万的列索引卷大小，我怀疑，将显着大于您的数据卷大小，因为每个索引将存储所有索引列的值的副本。
   
   $

   
   
   1. 查看 CUBRID中的索引优化，以了解有关如何优化查询的详情。
   
   
   2. 要进一步优化查询，利用索引提示。
   
   

   
   但您应该认真考虑正常化数据。

   
   
   

   会显着影响服务器的性能。对于您的情况，强烈建议使用大型RAM和CPU。

   
   
   

   任何其他RDBMS在使用此类模式时都将面临类似的问题。 CUBRID通常提供高SELECT性能和良好定义的索引。




3. 这里有

   
   
   1. CUBRID Manager ：您可以使用三个GUI工具来管理数据库。一个功能强大的数据库管理工具
   
   
   2. CUBRID Web Manager ：CUBRID Manager的基于网络的替代方案。
   
   
   3. CUBRID查询浏览器：没有主机管理和监控功能的轻量版CUBRID Manager。
   
   



4. p> CUBRID符合SQL92标准。因此，您可以在同一个事务中定义表格和插入记录。

I have a project that requires me to create tables on the fly. When tables are created it needs to support at least 1000 columns in each table. All INTs, Dates, BITs, will be indexes. So there could be about 400 indexes on one table. Once data is uploaded to the server no other inserts or updated will be performed on the table. I will use a library like lucene to index text.

My questions are:

1. Can CUBRID handle 1000's of tables in one database?
2. What is the performance of CUBRID when selecting on a table with a thousand columns and hundreds of indexes?
3. Does CUBRID have a windows GUI interface to run ad hoc queries and manage tables
4. Can DDL and DML statements be in the same transaction?

I am well aware of database normalization design issues here. I have fully researched normalization issues and arrived that creating many tables and columns is the best solution.

解决方案

Disclaimer: I am a part of the CUBRID team.

Short answer:

1. Yes.
2. Depends.
3. Yes.
4. Yes.

Long answer:

1. As mentioned at CUBRID RDBMS Size Limits, you can create unlimited number of tables with unlimited number of indexes per table as long as you have enough disk space.
   
   However, the number of columns per table is limited to 6400 which should be enough for you, I guess.

2. It depends on the number of records you are going to have. It wouldn't matter too much if you didn't have so many columns in one row. The number of records will affect not only the data volume size (the file on the disk where the data is stored), but also the index volume size, where index keys and values are stored. With your current schema design where you have thousands of columns your index volume size, I suspect, will be significantly larger than your data volume size because every index will store copies of all values of the column you index.
   
   Creating a proper set of indexes will allow you to improve the performance.

   1. Check out Index Optimization in CUBRID to learn more about how you can optimize your queries.
   2. To further optimize your query, leverage Index Hints.

   
   But you should seriously consider normalizing your data.

   Also, your hardware configuration will significantly affect the performance of the server. For your case, large RAM and CPU is highly recommended.

   Any other RDBMS will face similar issues when working with such schema. CUBRID, in general, provides high SELECT performance with well defined indexes. So you should focus on this.

3. There are three GUI tools you can use to administer your database.

   1. CUBRID Manager: an all-in-one, powerful database administration tool for CUBRID
   2. CUBRID Web Manager: a Web-based alternative for CUBRID Manager.
   3. CUBRID Query Browser: a light version of CUBRID Manager without host management and monitoring features.

4. CUBRID complies with SQL92 standard. So you can define a table and insert records within the same transaction.

这篇关于什么是CUBRID与1000+列索引的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！