Cassandra和MySql集成：如何处理UUID密钥？ [英] Cassandra and MySql integration: How to deal with UUID keys?

问题描述

我正在开发一个服务，大量使用Cassandra进行超过60个列族的集群插入和查询。

到目前为止，我能够通过重新排序我的键或添加具有不同索引集的重复（非标准化）表来解决我的很多问题为了实现非常快速的插入和查询，但现在，我遇到了两个情况Cassandra不能处理开箱：

1. 文本搜索跨文本列


2. 按计数器列实时排序（请参阅
   

这两个问题都有解决方案，但它们涉及在我的Cassandra集群之上使用不同的引擎，这不容易设置，我没有使用它们的专业知识。此外，对于我的使用情况，他们看起来有点过分。

为此，我决定，对于少数情况下，需要文本搜索或某些排序和分页，我会使用MySQL，意味着我会同时插入数据到MySQL和Cassandra几个表。对于不需要搜索或专门分页的正常查询，我将使用Cassandra，但是当我需要这样做时，我会回溯到MySQL。

原则上很容易做到这一点，但问题是，我的Cassandra中的复合键都是UUID格式（生成的UUID或时间UUID）。 MySQL然而不是良好的UUID作为主OR索引的密钥从我读了。 （例如，请参见 http://kccoder.com/mysql/uuidvsvs -int-insert-performance / ）。

我的问题是，你会如何建议处理这个问题？我如何使Cassandra和MySQL共享一个键，同时保持一个体面的足够的性能？

我应该在MySQL上使用自动增量索引，然后将该键存回Cassandra，以便下次需要从MySQL获取行时，我查询Cassandra的那个UUID首先然后得到自动增量键出来使用MySQL？你有另一个更好的建议吗？有没有反对避免这个往返？

请让我知道，如果这个问题不够清楚，我会尽我所能进一步解释。 p>

非常感谢，

解决方案

UUID实际上是一个128位整数，您可以在MySQL中使用2个64位int来表示。

I'm developing a service that is heavily using Cassandra for clustered inserts and queries with over 60 column families.

Up until now, I was able to get around a lot of my problems by re-ordering my keys or adding duplicate (non-normalized) tables with different sets of indexes in order to achieve very fast inserts and queries, but now, I've come across two scenarios Cassandra can't handle out of the box:

1. Text searches across text columns
2. Ordering by counter columns in real-time (See What data structure should I use to mimic "order by counter" in Cassandra?)

Both these problems have "solutions" but they involve using different engines on top of my Cassandra clusters which are not easy to setup and I have no expertise in using them. Additionally, for my use-case, they look to be a bit of an overkill.

For this reason, I decided that for the few scenarios that require either text searches or certain ordering and paging, I would use MySQL, meaning I would insert the data simultaneously into MySQL and Cassandra for a couple of tables. For normal queries not needing searches or specialized paging, I would use Cassandra but when I needed to do those, I would refer back to MySQL.

It's easy to do this in principle but the problem is, my composite keys in Cassandra are all in UUID format (be that a generated UUID or a time UUID). MySQL however doesn't bode well with UUID as primary OR indexed key from what I've read. (For example, see http://kccoder.com/mysql/uuid-vs-int-insert-performance/).

My question is, how would you recommend dealing with this problem? How can I make Cassandra and MySQL "share" a key whilst maintaining a decent enough performance?

Should I use an auto-increment index on the MySQL and then store that key back into Cassandra so that the next time I need to get the row from MySQL, I query Cassandra for that UUID first and then get the auto increment key out to use in MySQL? Do you have another, better recommendation? Is there anyway to avoid this round-trip?

Please let me know if the question is not clear enough and I'll do my best to explain it further.

Many thanks,

解决方案

UUID is actually a 128-bit integer, you can use 2 64-bit int in MySQL to represent that.

这篇关于Cassandra和MySql集成：如何处理UUID密钥？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！