Mycat-Use article (1)

Database routing Middleware Mycat-use chapter (1) Basic concepts

Direct introduction to the concept is too boring, or to take a similar case with the background of the introduction 
Business Scenario: The customer completes the order, the Courier accepts and updates the Waybill status, the customer can check the consignment status at any time. A ticket courier may have more than one sub-parent piece. 
Therefore, we need to create the following table: 
650) this.width=650; "title=" "style=" Border-top:medium none;height:auto;border-right:medium none;border-bottom: Medium None;border-left:medium none; "alt=" er diagram "src=" http://img.blog.csdn.net/20160201160310802 "/> 
We are now in accordance with the business to split the database vertically into the waybill (3000tps,4000w data), Courier (1500tps,100w data), Customers (1500tps,4000w data records), Waybill and customer tables are fragmented according to a certain rule, divided evenly into two pieces. Then assume that they are all managed through Mycat Unified management. 
650) this.width=650; "title=" "style=" Border-top:medium none;height:auto;border-right:medium none;border-bottom: Medium None;border-left:medium none; "alt=" here write a picture describing "src=" http://img.blog.csdn.net/20160215194214981 "/>

1. Logic Library

App Access Mycat, as described earlier, application-aware to the background is just a database (assuming this library is called SF, there are waybill related tables, courier related tables and customer related tables); Access Mycat, the results should look like this
650) this.width=650; "title=" "style=" Border-top:medium none;height:auto;border-right:medium none;border-bottom: Medium None;border-left:medium none; "alt=" Logical Library Example "src=" http://img.blog.csdn.net/20160201105928338 "/>
Although the tables may exist in different libraries, on the surface they belong to the same logical library in the same Mycat instance

2. Logic tables

The Orders table and the Orders_fee are obviously the tables to be partitioned, but in Mycat's view, although they are distributed on different shards, they are still considered to be the same logical table in the same database .

2.1 Shard Table

A shard table is a table of large data that needs to be sliced into multiple databases, so that each shard has a subset of the data, and all shards form the complete data. Shard tables have their own shard rules that determine the Shard according to the Shard rules

2.2 Non-Shard table

Not all tables in a database are large, some tables can be used without slicing, non-shards are relative to the Shard table, and are those that do not need to be sliced.

2.3 ER Table

The relational database is based on the entity Relation Model (Entity-relationship model), which describes the things and relationships in the real world, and the ER table in Mycat is derived from this. According to this idea, a data slicing strategy based on e-r relationship is proposed, and the record of the child table is stored on the same data shard as the associated parent table record, that is, the child table relies on the parent table, and the table group ensures that the data join does not operate across the library.  
Table grouping is a good way to solve cross-shard data joins and is an important rule in data segmentation planning.

2.4 Global Tables

In a real business system, there are often a large number of tables similar to dictionary tables, these tables are rarely changed, and the dictionary table has several features:

• Less frequent changes

• The overall change in data volume is small

• Data size is small and there are rarely more than hundreds of thousands of records.

For this type of table, in the case of sharding, when the business table is fragmented because of its size, the association between the business table and these attached dictionary tables becomes a tricky issue, so the join of this type of table is solved by data redundancy in Mycat, that is, all shards have a copy of the data, All tables that have dictionary tables or attributes that conform to the dictionary table are defined as global tables.  
Data redundancy is a good idea to solve cross-shard data joins and another important rule of data segmentation planning.

2.5 How to decide?

According to the previous description, we can infer that for the modification and query of the Shard table, the request is forwarded to a shard if it is searched by the Shard field. If you do not follow the Shard field, the request is sent to each shard for lookup. Therefore, the choice of the Shard field is more important! For a global table, it is equivalent to having one copy on each shard, and the modification request executes on each shard, but the query falls only on one shard. Therefore, the global table as far as possible is not changed and is required and the Shard table to do join operations, if often change or do not need to do join, it is best to make a non-shard table.

First, the concept of these logical tables is thrown out, and we first have an impression. Now we're going to discuss how to determine the type of the table in conjunction with concrete.

First, the Orders table can be a shard table. The Orders_cargo table is a child parent table, and an order may have more than one child, so it is best to use Orders_cargo as a child of orders.  
In this case, orders and Orders_cargo follow the corresponding key (that is, the child table is partitioned by which key corresponds to which key of the primary table.) For example, Orders_cargo is the ID of order_id and orders. This is the correct join result with the ID of the order_id and orders), and also the .  
650) this.width=650; "title=" "style=" Border-top:medium none;height:auto;border-right:medium none;border-bottom : Medium None;border-left:medium none; "alt=" Pair n Scene Architecture "src=" http://img.blog.csdn.net/20160201145754358 "/> 
like this simple dependency on a pair of n tables, we deal with it very simply by setting them to the parent-child table as the key to the join.

But the following scene is very troublesome, such as courier and Waybill is many-to-many relationship, the customer for the waybill is also many-to-many relationship (a receiver pays, a sender). We have both the courier needs to view all of their waybill scene and the customer to view all of their waybill scene. In contrast, we also have to view a consignment note involving the courier and the customer's scene. 
The Customer table (client table) and the Courier Table (Courier table) are not used as public tables because they do not join operations with the Shard table orders. 
First, the relational table can act as a common table, so that there is no limit to the join operation with the Shard table, because in each shard, the public table is complete. However, the update of the relational tables is very frequent, and we may not be able to tolerate each update of the relational table on each shard (performance, reliability considerations). 
What about the sub-table as a waybill? Then find a waybill related to the courier and customers are relatively simple. Because according to the waybill number (that is, the Shard ID) query, MYCAT will be based on the Shard rule to him to target specific shards, rather than to search by the Shard. 
650) this.width=650; "title=" "style=" Border-top:medium none;height:auto;border-right:medium none;border-bottom: Medium None;border-left:medium none; "alt=" here write a picture describing "src=" http://img.blog.csdn.net/20160201155009362 "/> 
But accordingly, the customer view all of their own waybill scene is relatively slow, because the request is sent to each shard to find. 
650) this.width=650; "title=" "style=" Border-top:medium none;height:auto;border-right:medium none;border-bottom: Medium None;border-left:medium none; "alt=" here write a picture describing "src=" http://img.blog.csdn.net/20160201155022519 "/> 
The same is true of the client's sub-table. 
Another way is that the relationship table acts as both a waybill and a client's child table. However, at present, it is necessary to use their own to do double writing. Mycat has not yet achieved this. Of course, I think this is a place where we can improve ourselves as needed. 
In addition, what kind of method is taken from the business to consider. Here, if from the customer to find and from the waybill to find the business pressure is almost large, then it is best to use the relationship table as a waybill and customer's sub-table this method.

This article is from the "Zhang Hashi Technology Blog" blog, make sure to keep this source http://zhxhash.blog.51cto.com/10645572/1743714

Mycat-Use article (1)