Database Middleware __mycat

Summary: Mycat as of April 2015, conservative estimates have been used in more than 60 projects, mainly in the telecommunications sector, Internet projects, mostly trading and management systems, and a small number of information systems. In larger systems, the data scale is single-form month 3 billion. This article gives you a comprehensive understanding of mycat.

Why do I need Mycat ?

Although there are congenital drawbacks to traditional databases in the era of cloud computing, NoSQL databases cannot replace them. If the traditional data is easy to expand, can be segmented, you can avoid single-machine (library) performance defects.

The goal of Mycat is to smoothly migrate existing stand-alone databases and applications to the "cloud" side at low cost, addressing data bottlenecks in the context of rapidly growing data storage and business scale. 2014 Mycat for the first time in Shanghai's "Chinese architect" conference at the foreign propaganda aroused onlookers, more people participate in, then more and more projects adopted mycat.

Mycat as of April 2015, conservative estimates have been used in more than 60 projects, mainly in the telecommunications sector, Internet projects, mostly trading and management systems, and a small number of information systems. In larger systems, the data scale is single-form month 3 billion.

Mycat What is it?

From the definition and classification, it is an open source distributed database system, is a server that implements the MySQL protocol, the front end user can think of it as a database agent, with the MySQL client tool and command line access, and the backend can use the MySQL native protocol with multiple MySQL server communication, You can also use the JDBC protocol to communicate with most major database servers, whose core function is to divide the table into tables, and to split a large table horizontally into n small tables, stored in the back-end MySQL server or in other databases.

Mycat Development to the current version, is not a simple MySQL agent, its backend can support MySQL, SQL Server, Oracle, DB2, PostgreSQL and other mainstream databases, but also support MongoDB this new NoSQL mode of storage, More types of storage will also be supported in the future. And in the end user's view, no matter is that storage way, in Mycat, is a traditional database table, supports the standard SQL statement to carry on the data operation, thus, to the front-end business system, can greatly reduce the development difficulty, enhances the development speed

Figure 1 Mycat Architecture design diagram

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M02/82/C4/wKiom1dgs4-yE1MlAAIBC-Sba4k667.jpg "style=" vertical-align:middle;border:none;margin:0px; "title=" 55a7545d41503_middle.jpg "/>

Mycat What issues are resolved

1. Too many connectivity issues, all data sources can be managed centrally through MYCAT, and the backend DB cluster is transparent to the front-end application. before using MYCAT system structure 2.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/82/C4/wKiom1dgs9-x-1B-AAHsyPPzA0o170.jpg "style=" vertical-align:middle;border:none;float:none;margin:0px; "title=" 55a754913bdd6_middle.jpg "/>

Figure 2 Mycat earlier system architecture

Mycat introduces the connection multiplexing to solve the multi-application competition problem, which is shown in 3 after the Mycat transformation.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/82/C2/wKioL1dgtRygb7-_AAFcAn9dAg8530.jpg "style=" vertical-align:middle;border:none;float:none;margin:0px; "title=" 55a754be29df9_middle.jpg "/>

Fig. 3 Mycat after transformation

2. Original ER-relational shard, solve the problem of e-r fragmentation , the parent-child table with association relationship in the process of data inserting, the child table is routed to the node of its related parent table record, so that the parent-child table's join query can be pushed down to the individual database nodes. This is the most efficient cross-node join processing technology, but also the MYCAT initiative.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/82/C4/wKiom1dgtB7AZFGcAADK8rypiJ8547.jpg "style=" vertical-align:middle;border:none;float:none;margin:0px; "title=" 55a754f26c1d2_middle.jpg "/>

Figure 4 The original ER-relational shard, the MYCAT initiative

3. Using global sharding Technology, each node concurrently inserts and updates data, each node can read data , improve read performance, but also solve the efficiency of cross-node join.

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M02/82/C4/wKiom1dgtDmC37bCAACwbkyJhz8203.jpg "style=" vertical-align:middle;border:none;float:none;margin:0px; "title=" 55a75501a2290_middle.jpg "/>

Figure 5 Using Global sharding technology

4. Catlet supports cross-shard complex SQL implementations and stored procedure support through AI. the method of use is mainly performed by Mycat annotations, as follows:

(1) Cross-shard Federated query Annotation Support:

/*! Mycat:catlet=demo.catlets.sharejoin/select Bu., sg.* from Base_user bu,sam_glucose sg where bu.id_=sg.user_id;

Note: Sam_glucose is a cross-shard table.

(2) Stored procedure annotations support:

/*! Mycat:sql=select * from Base_user where id_=1;*/call Proc_test ();

Note: The current execution of stored procedures is performed by Mycat annotations, noting the need to write the SQL in the stored procedure to the annotations.

(3) batch insert with ID self-growth combined support:

/*! Mycat:catlet=demo.catlets.batchinsertsequence */INSERT INTO sam_test (name_) VALUES (' T1 '), (' T2 ');

Note: This method does not need to be displayed in the SQL statement to set the primary key field, the program in the background according to the PrimaryKey configuration of the primary key column, automatically generate the sequence value of the primary key and replace the relevant columns and values in the original SQL;

(4) Get support for batch sequence values:

/*! Mycat:catlet=demo.catlets.batchgetsequence */select mycat_get_seq (' mycat_test ', 100);

Note: This method represents getting the 100 sequence values of the Mycat_test table, for example, the maximum sequence value for the current Mycat_test table is 5000, and 5001 is returned in this way, while the mycat_ in the database is updated The test table has a maximum sequence value of 5100.

(5) Better support for database read and write separation and high availability, MYCAT supports advanced read/write separation control based on MySQL master-slave replication state (e.g. Slave_behind_master <100 is turned on), and once master-slave synchronization error is detected or latency exceeds development, The readhost is automatically excluded, preventing the program from reading old data for a long time.

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M02/82/C4/wKiom1dgtFnzyGTPAADHDvB8axg759.jpg "style=" vertical-align:middle;border:none;float:none;margin:0px; "title=" 55a75554c262f_middle.jpg "/>

Figure 6 Mycat supports advanced read and write separation control based on MySQL master-slave replication state

Mycat Technical Principle

One of the most important verbs in the MYCAT technical principle is "intercept", which intercepts the SQL statements sent by the user, and first makes some specific analysis of the SQL statements: such as shard analysis, routing analysis, read-write separation analysis, cache analysis, and so on, and then send this SQL back to the real database, The returned results are processed appropriately and then returned to the user.

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/82/C4/wKiom1dgtHjSCCaFAAEVWFp2FRc948.jpg "style=" vertical-align:middle;border:none;float:none;margin:0px; "title=" 55a7557f1f2d8_middle.jpg "/>

Figure 7 Orders is divided into three shards Datanode

7, the Orders table is divided into three shard Datanode (DN), the three shards are distributed on two MySQL servers (datahost), that is, the [email protected] way, so you can use one to n server to Shard, The Shard rule is a typical string enumeration shard rule (sharding rule), where a rule is defined as a shard field (sharding column) + Shard function (rule function), where the Shard fields are prov and the Shard function is a string enumeration. When Mycat receives a SQL, it parses the SQL, finds the table involved, and then looks at the table definition, if there is a shard rule, gets the value of the Shard field in SQL, matches the Shard function, obtains the SQL corresponding Shard list, and then sends the SQL to those shards to execute, Finally collects and processes the result data returned by all shards and outputs it to the client. Take the SELECT * from Orders where prov= statement for example, find Prov=wuhan, according to the Shard function, Wuhan return DN1, so SQL sent to MySQL1, to fetch the results on the DB1, and return to the user. If the above SQL is changed to select * from Orders where Prov in (' Wuhan ', ' Beijing '), then SQL is sent to MySQL1 and MySQL2 to execute, and then the result set is merged and output to the user. But usually our SQL in the business has an order by and limit paging syntax, at this time involves the result set at the Mycat end of the two processing, this part of the code is more complex, and the most complex is two table jion problem, for this, Mycat proposed innovative ER shard, global table, HBT (Human Brain Tech) AI Catlet and so on.

Mycat Next Step planning

Strengthen the function of distributed database middleware, make it rich in plug-in, powerful Database intelligent optimization function, comprehensive system monitoring ability, and convenient data operation and maintenance tools, realize online data expansion, migration and other advanced functions.

Further into the field of big data computing, deep integration with the distributed real-time streaming engine, such as Spark stream and storm, enables fast OLAP-oriented capabilities such as giant table correlation, sorting, grouping aggregation, and integrating some of the most popular real-time analysis algorithms Make it easier for engineers and DBAs to implement advanced data analysis processing capabilities with MYCAT.

Database Middleware __mycat