Briefly talk about MyCat sub-database and sub-table

Briefly talk about MyCat sub-database and sub-table

MyCat is a middleware of database sub-database based on server proxy mode. The principle is to forward SQL. The specific architecture diagram is as follows:

We know that the splitting of data will inevitably have an impact on the atomicity of things. What if the atomicity of transactions can be guaranteed while the database is split?

How to solve distributed transactions

XA protocol

XA protocol is an open source transaction protocol, related documents [XA transaction processing] .

XA protocol agreement in the second paragraph commitafter submission service thing, if there was some machines did not submit the transaction is successful, some machines has been submitted successfully, then the data has been submitted, the data can not be rolled back again, thus causing inconsistent data The problem.

Three-part submission agreement

The specific agreement is as follows:

XA relative to one more Precommitof the process, Do-Commitalso increase the time-out operations, to avoid the phenomenon can not receive Ack time in XA commit, appeared blocked.

Business rules avoid

This is a trick 方案. The specific method is that all business table modification and deletion operations can eventually be routed to a MySQL node, so that there is no distributed transaction problem.

How to solve the unique primary key (increment)

This ability is a distributed 归一idea. There are four ways of specific implementation ideas:

  1. Local file method
  2. Database way
  3. Timestamp
  4. Distributed zookeeper generates ID

How to query data for non-sharding rules

This question is a very tangled question, because there is no particularly good solution. There should be two specific ideas:

  1. Polling each node to query, but it will bring performance waste.
  2. The unfragmented fields are mapped to the fragmented fields, and the fragmented fields are now queried based on the non-fragmented fields. For example: We perform fragmentation based on Id. If we want to query data based on userId, is there any way? First of all, it is necessary to ensure that in the order of magnitude of business data, userid must be less than the order of id, so we can directly store userid-> id1, id2....idn, and then query the corresponding data according to the set of id.

[Reference material] [MyCat authoritative guide]

Reference: Simply talk about MyCat sub-library and sub-table-Cloud + Community-Tencent Cloud