Shulou(Shulou.com)06/01 Report--

Abstract: MyCat as of April 2015, it is conservatively estimated that there are more than 60 projects in use, mainly in the field of telecommunications, Internet projects, most of them are trading and management systems, and a few are information systems. In the larger system, the data scale is 3 billion per month. This article gives you a comprehensive understanding of MyCat.

Why do I need MyCat?

Although in the era of cloud computing, the traditional database has congenital disadvantages, but the NoSQL database can not replace it. If the traditional data is easy to expand and can be sliced, the performance defects of stand-alone (single library) can be avoided.

The goal of MyCat is to smoothly migrate existing stand-alone databases and applications to the "cloud" at low cost, so as to solve the data bottleneck in the case of rapid growth of data storage and business scale. MyCat's first public presentation at the "Chinese architect" conference in Shanghai in 2014 attracted more people to participate, and then more and more projects adopted MyCat.

MyCat as of April 2015, it is conservatively estimated that there are more than 60 projects in use, mainly in the telecommunications field, Internet projects, most of them are trading and management systems, and a few are information systems. In the larger system, the data scale is 3 billion per month.

What is MyCat?

From the point of view of definition and classification, it is an open source distributed database system and a server that implements MySQL protocol. Front-end users can regard it as a database agent, which can be accessed by MySQL client tools and command line, while its back-end can communicate with multiple MySQL servers using MySQL native protocol, or with most mainstream database servers using JDBC protocol. Its core function is to divide tables and libraries. Split a large table horizontally into N small tables and store them in a back-end MySQL server or other database.

With the development of the current version, MyCat is no longer a simple MySQL agent, its back-end can support mainstream databases such as MySQL, SQL Server, Oracle, DB2, PostgreSQL and other mainstream databases, as well as MongoDB, a new type of NoSQL storage, and will support more types of storage in the future. In the view of end-users, no matter what kind of storage method, in MyCat, it is a traditional database table, which supports standard SQL statements to operate the data. In this way, for the front-end business system, it can greatly reduce the difficulty of development and improve the speed of development.

Figure 1 MyCat architecture design diagram

What problems did MyCat solve?

1. If there are too many connections, you can manage all data sources through MyCat, and the back-end database cluster is transparent to the front-end applications. The system structure before using MyCat is shown in figure 2.

Figure 2 previous system architecture of MyCat

MyCat introduces connection reuse to solve the problem of multi-application competition, which is modified by MyCat, as shown in figure 3.

Fig. 3 modified MyCat

two。 Original ER relational slicing to solve the problem that Emurr sharding is difficult to deal with. In the process of data insertion, the child table of the associated parent table will be routed by MyCat to the node of its related parent table record, so that the Join query of the parent table can be pushed down to each database node. This is the most efficient cross-node Join processing technology, and it is also the initiative of MyCat.

Figure 4 original ER relationship slicing, which is first created by MyCat

3. Using global slicing technology, each node simultaneously inserts and updates data, and each node can read data, which not only improves the read performance, but also solves the efficiency of cross-node Join.

Fig. 5 using global slicing technology

4. Support complex SQL implementation and stored procedure support across fragments through catlet of artificial intelligence. The usage is mainly performed by MyCat comments, as follows:

(1) support for cross-shard federated query annotations:

/ *! 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-slicing table.

(2) stored procedure annotations support:

/ *! MyCat: sql=select * from base_user where id_=1;*/ CALL proc_test ()

Note: at present, the stored procedure is executed by MyCat annotations. Note that the sql in the stored procedure needs to be written into the annotations.

(3) support for the combination of batch insertion and ID self-growth:

/ *! MyCat:catlet=demo.catlets.BatchInsertSequence * / insert into sam_test (name_) values ('t1'), (' t2')

Note: this method does not need to set the primary key field shown in the sql statement. In the background, according to the primary key column configured by primaryKey, the program automatically generates the sequence value of the primary key and replaces the relevant columns and values in the original sql.

(4) obtain the support for batch sequence values:

/ *! MyCat:catlet=demo.catlets.BatchGetSequence * / SELECT MyCat_get_seq ('MyCat_TEST',100)

Note: this method means to get 100 sequence values of the MyCat_ test table. For example, if the maximum sequence value of the current MyCat_ test table is 5000, the maximum sequence value returned in this way is 5001, while the maximum sequence value of the MyCat_ test table in the database is 5100.

(5) better support database read-write separation and high availability. MyCat supports advanced read-write separation control mechanisms based on MySQL master-slave replication status (such as Slave_behind_master).

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.