从 SQL 转向 NoSQL，转向哪个数据库？
最近，我们当前的 SQL Server 数据库遇到了与性能相关的重大问题。 我们的应用程序非常依赖于单个表，我们做了一些分析，大约 90% 的数据库数据都在单个表中。我们也出于分析目的在此表上运行了大量查询，我们现在遇到了重大性能问题，即使添加单列有时也会减慢我们当前的 Sp。我们的大多数团队都是开发人员，我们无法联系 dba，这可能有助于重新调整我们当前的数据库并使事情运行得更快。
由于这些限制，我们正在考虑将应用程序的这一部分移至 NoSQL 数据库。 我的问题是：
- 这是否是我们前进的正确方向？因为我们预计这张桌子会呈指数级增长。上面运行着大量的分析。
- CouchDB、Cassandra、MongoDB 哪个对我们来说是最好的选择？强调可扩展性和性能
- 对于类似于 SQL 的实时分析和支持，NoSQL 中的工作方式是否有一种工具可以让我们查看当前存储的数据？我在某处读到过有关 Hadoop 的 HIVE 可用于从 NoSQL 数据库写入和检索数据作为 SQL 的内容，对吗？
- 从 SQL 转向 NoSQL 时我们可能会失去哪些东西？
We recently are having major performance related issues in our current SQL Server DB.
Our application is pretty heavy on a single table we did some analysis and about 90% of our db data is in a single table. We run lot of queries on this table as well for analyticall purposes we are experiencing major performance issues now even with a single column addition sometimes slows our current Sp. Most of our teams are developers and we don't have access to a dba which might help in retuning our current db and make things work faster.
Cause of these constraints we are thinking of moving this part of the app to a NoSQL db.
My Questions are :
- If this is the right direction we are heading ? As we are expecting exponential growth on this table. With loads of analytic's running on it.
- Which would be best option for us CouchDB , Cassandra , MongoDB ? With stress on scalability and performance
- For real time analysis and support similar to SQL how things work in a NoSQL is there a facility through which we can view current data being stored? I had read somewhere about Hadoop’s HIVE can be used to write and retreive data as SQL from NoSQL db's am I right?
- What might be things which we would be losing out of while shifting from SQL to NoSQL ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
对于您的问题：
1..如果这是我们前进的正确方向？因为我们预计这张桌子会呈指数级增长。上面运行着大量的分析。
是的，大多数 noSQL 系统都是专门为了解决可扩展性和可用性问题而开发的，如果您以预期的方式使用它们。
2.. CouchDB、Cassandra、MongoDB 哪个是我们的最佳选择？由于强调可扩展性和性能，
这完全取决于您的数据是什么样的以及您将如何使用它。您提到的 noSQL 数据库的实现和行为彼此之间非常不同，请参阅此链接以获取更详细的概述，比较您提到的几个数据库。 noSQL 解决方案比较
3.. 实时类似于 SQL 的分析和支持 NoSQL 中的工作方式 是否有一种工具可以让我们查看当前存储的数据？我在某处读到过有关 Hadoop 的 HIVE 可用于从 NoSQL 数据库以 SQL 形式写入和检索数据，对吗？
这取决于您使用的系统，因为某些 noSQL 数据库不支持范围查询或联接，您可以查看的内容以及查看的速度受到限制。
4.. 从 SQL 转向 NoSQL 时我们可能会失去哪些东西？
noSQL 有两个主要考虑因素：
查询/结构：NoSQL 意味着没有SQL。如果您的系统实际上需要结构化和复杂的查询，但您采用了这些很酷的新解决方案之一（尤其是键值存储，基本上是一个巨大的哈希表），您可能很快就会发现自己正在重新实现一个业余的解决方案，设计不良的 RDBMS，存在所有最初的问题。
一致性：如果你选择一个最终一致的系统来水平扩展，那么你将不得不接受你的数据已经过时，这对于某些应用程序（论坛？）可能是无害的，或者在其他一些系统（银行）中是可怕的）。
To your questions:
1.. If this is the right direction we are heading ? As we are expecting exponential growth on this table. With loads of analytic's running on it.
Yes, most of the noSQL systems are developed specifically to address scalability and availability, if you use them in the intended way.
2.. Which would be best option for us CouchDB , Cassandra , MongoDB ? With stress on scalability and performance
This depends entirely on what does your data looks like and how you will use it. The noSQL db you mentioned are implemented and behaves very differently from each other, see this link for a more detailed overview comparing the few you mentioned. Comparisons of noSQL solution
3.. For real time analysis and support similar to SQL how things work in a NoSQL is there a facility through which we can view current data being stored? I had read somewhere about Hadoop’s HIVE can be used to write and retreive data as SQL from NoSQL db's am I right?
This depends on the system you go with, because some noSQL db doesn't support range queries or joins, you are restricted in what you can view and how fast you can view.
4.. What might be things which we would be losing out of while shifting from SQL to NoSQL?
There are two major considerations for noSQL:
Query/Structure: NoSQL means no SQL. If your system actually requires structured and complex queries but you went with one of these cool new solution (especially a key-value storage, which is basically a giant hash table), you may soon find yourself in the middle of re-implementing a amateurish, ill-designed RDBMS, with all of your original problems.
Consistency: If you choose a eventual consistent system to scale horizontally, then you will have to accept your data being outdated, which may be harmless to some applications (forums?) or horrible in some other systems (bank).
我认为您应该保持关系并调整表、其索引及其连接的表。您还应该考虑使用聚合（汇总数据）。也许更加非规范化的设计将有助于甚至将数据重新设计成更多的星形结构。此外，操作处理和决策支持（或报告）分析不应在同一个表上运行。
I think you should stay relational and tune the table, its indexes, and the tables it joins to. You should also consider the use of aggregated (summarized data). Perhaps a more denormalized design would help or even re-designing the data into more of a star structure. Also, operational processing and decision support (or reporting) analyses should not be run on the same tables.
通过检查丢失的索引等并查看您使用的隔离级别是否是最佳的，可以改进 SQL 方法。可以使用快照隔离等来提高性能。 MSDN 链接
还可以了解 OLTP 与 OLAP 的比较。
NoSQL 可能仍然是一个更好的选择，但您仍然需要学习如何正确使用数据库，它会带来另一组不同的问题。
It might be possible to improve the SQL approach by checking for missing indexes etc and also seeing if the isolation level you are using is optimal. It may be possible to use snapshot isolation etc to improve performance. MSDN link
Read up on OLTP vs OLAP also.
NoSQL may still be a better option but you would still need to learn how to work with the database properly, it will come with another different set of issues.