MySQL Cluster如何确定要搜索SELECT查询的数据节点? [英] How does MySQL Cluster determine which data nodes to search for a SELECT query?

问题描述

我正在研究如何解决客户需要将特定客户的所有数据(仅该客户的数据)存储在地理位置不同的数据库服务器上的情况.

I'm researching how to resolve a situation where a client needs all data for a particular customer (and only the data for that customer) to be stored on a geographically disparate database server.

例如，所有数据应存储在主云中的数据库服务器中，与客户ID 92有关的所有数据除外，这些数据应存储在另一位置的不同云中的服务器中.

For example, all data should be stored in database servers on the main cloud, except for all data relating to customer ID 92, which should be stored in servers on a different cloud in another location.

我正在处理一些约束，这使这有些棘手，但到目前为止，

There are a couple of constraints I am working with that are making this a little tricky, but so far, MySQL Cluster seems like the best approach.

但是，我不清楚在执行查询时如何选择数据节点.例如，如果我要提交不需要客户ID 92的任何数据的查询，它是否还会对另一个云中的数据节点执行ping操作并引入延迟?

However, it is unclear to me how it selects data nodes when executing queries. E.g., if I were to submit a query that did not require any data for customer ID 92, would it still ping data nodes in the other cloud and introduce latency?

MySQL Cluster如何确定在SELECT查询期间要搜索哪些数据节点?有什么方法可以在查询中暗示某些数据节点可以忽略吗?

How does MySQL Cluster determine which data nodes to search during a SELECT query? Are there ways that I can hint in a query that certain data nodes can be ignored?

推荐答案

我怕答案是否". MySQL集群有时被称为分片，但实际上不是.它是PK随意分配每个表中的数据的方式，没有控制权，也没有考虑一起访问哪些数据以及每次访问以及每个查询或事务需要哪些节点.

Hi I'm afraid the answer is "no". MySQL cluster is sometimes called sharding but it's really not... It's arbitrary distribution of data from every table by the PK with no control and no thinking about which data is accessed together and which nodes are needed for every access and every query or transaction.

共享和良好的数据分发策略是一种将访问在一起的数据保存在同一数据库中的策略，因此，当事务需要数据时，它将最终使用此1个DB进行处理(联接，组)将被推送到该数据库(更接近数据，很好！)，而其他数据库将被处理其他事务(还有很多…….).

Sharding, and a good data distribution policy is one that keeps data that is accessed together, on the same database, so when a transaction needs data, it'll end-up using this 1 DB, processing (join, group) will be pushed to this database (closer to the data, good!) and other databases will be left to deal with other transactions (and there are many.......).

因此，从一起访问的数据存储在一个数据库中，我们得到了两件事:

So we get 2 things from storing together-accessed data on one database:

1. 需要此数据和查找的查询/交易的等待时间短 在1个节点上
2. 查询/事务是分布式的，并非在所有数据库上都相乘

1. Less latency for queries/transaction needing this data and finding it on 1 node
2. Queries/transaction are distributed, not multiplied on all databases

因此，如果我理解您的问题，这就是您想要实现的目标，MySQL Cluster无法做到这一点，如果现在还可以的话，当数据/并发性/写入增长时，它会再次咬住您.

So if I understand your question, this is what you want to achieve, MySQL Cluster can't give that, if it's OK for now, it will come back and bite you when data/concurrency/writes grow.....

您可能需要一个很好的旧分片，或者今天有一些工具可以真正实现分片过程的自动化(免责声明:我为ScaleBase工作，其中一个选项是仅使用必需的数据库，是的，还支持提示(通常不需要) ).

You probably need a good old sharding, or today there are tools that actually automates sharding process (disclaimer: I work for ScaleBase, one option for that, using just databases required, and yes, also supporting hints (usually not needed) ).

这篇关于MySQL Cluster如何确定要搜索SELECT查询的数据节点?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！