Introduction to MyCat, the database middleware

1. Mycat application scenarios

Mycat has been developed to a wide range of scenarios, and new users are constantly providing new and innovative solutions. The following are several typical application scenarios:

• 1. Simple read-write separation. The configuration is the simplest at this time. It supports read-write separation and master-slave switching.
• 2. Shard tables and databases. Shard tables with more than 10 million items. Support sharding of up to 100 billion items per table.
• 3. Multi-tenant application, each application has a library, but the application only connects to Mycat, so as not to modify the program itself and realize multi-tenancy
• 4. Reporting system, with the help of Mycat's table partitioning capability, handles statistics of large-scale reports
• 5. Replace Hbase and analyze big data
• 6. As a simple and effective solution for real-time query of massive data, for example, 10 billion frequently queried records need to be queried within 3 seconds. In addition to primary key-based queries, there may also be range queries or other attribute queries. In this case, Mycat may be the simplest and most effective choice.

MYCAT can realize read operation load balancing under read-write separation, balancing a large number of read operations to different slave libraries, which mainly occurs in the case of one master and multiple slaves.

MYCAT can achieve high availability of the database. When the database master node is available, a writable slave node is configured. Both nodes are configured in MYCAT. When the master node goes down, MyCAT will automatically route the write operation to the backup node, but it does not support continued master-slave synchronization after the switch.

When read-write separation can no longer meet the continuously increasing access volume, MYCAT can realize vertical splitting of the database, dividing all database tables into modules and splitting different types of tables into different database servers.

As the business volume grows, if database performance problems arise after vertical splitting, horizontal splitting will be required, which is commonly known as database and table sharding. The table data with large amount of data is divided into different server libraries. The table structure is the same, and MYCAT is used to achieve horizontal division. It is completely transparent to the front-end application and there is no need to adjust the front-end logic.

From the definition and classification, it is an open source distributed database system and a server that implements the MySQL protocol. Front-end users can regard it as a database proxy and access it using MySQL client tools and command lines, while its back-end can communicate with multiple MySQL servers using the MySQL native protocol, or with most mainstream database servers using the JDBC protocol. Its core function is table and database sharding, that is, horizontally splitting a large table into N small tables and storing them in the back-end MySQL server or other databases.

The current version of MyCat is no longer a simple MySQL proxy. Its backend can support mainstream databases such as MySQL, SQL Server, Oracle, DB2, PostgreSQL, and also supports new NoSQL storage methods such as MongoDB. It will support more types of storage in the future. From the end user's point of view, no matter which storage method is used, in MyCat, it is a traditional database table that supports standard SQL statements to operate data. This can greatly reduce the development difficulty and improve the development speed for the front-end business system.

2. Limitations of traditional relational databases

Traditional relational databases have huge defects when facing big data due to their lack of scalability, but relational models and transaction mechanisms are indispensable for most systems. The current mainstream practice in the industry is to split traditional databases (including vertical splitting, horizontal splitting, etc.) to improve the scalability of the database. However, the splitting brought new problems, such as multi-data source management, cross-node join, and distributed transaction problems. Let's explore how Mycat solves these problems.

Multiple data source management issues

There are two main solutions to the problem of multiple data source management. The first is the client mode, which configures and manages one (or more) data sources required by each application module, directly accesses each database, and completes data integration within the module. Second: All data sources are managed uniformly through the intermediate proxy layer, and the back-end database cluster is transparent to the front-end application. The first method is not universal. Each application needs to develop its own data integration function, and the code of the already built system needs to be refactored, which is not suitable for promotion. Currently, the second method is mainly used. The principle of Mycat is as follows: The most important verb in the principle of Mycat is "intercept". It intercepts the SQL statements sent by the user, and first performs some specific analysis on the SQL statements: such as sharding analysis, routing analysis, read-write separation analysis, cache analysis, etc., and then sends this SQL to the real database on the backend, and processes the returned results appropriately, and finally returns them to the user.

The principle of Mycat is very similar to other distributed database middleware, but there are still differences in architecture. Mycat is derived from Cobar, but it has been greatly improved on its basis. The architecture of Mycat is as follows:

The current mainstream distributed database middleware includes TDDL, Amoeba, Coba, etc. TDDL is different from other products. It is not an independent middleware, but can only be regarded as an intermediate layer, which is provided to the application in the form of Jar package. This is the idea of ​​JDBC Shard, and there are many other similar products on the Internet. Amoeba provides services as a truly independent middleware, that is, applications connect to Amoeba to operate a MySQL cluster, just like operating a single MySQL. From the architecture, it can be seen that Amoeba is an early product in the middleware, and the backend is still using the JDBC Driver. Cobar is an evolved version based on Amoeba. A significant change is that the backend JDBC Driver is changed to the native MySQL communication protocol layer, which means that it cannot support mainstream databases such as Oracle and ProstgreSQL. MyCat is another version developed based on Cobar. The backend is changed from BI0 to NIO, the concurrency is greatly improved, and support for aggregation functions such as Order By, GroupBy, and limit is added. It supports most of the current mainstream databases.

Cross-node join problem

Mycat supports inner join, leaf/right join, cross join, full join and other cross-node join methods, mainly implemented through global table, ER sharding, Share Join and catlet (artificial intelligence):

1. Global table

In a real business system, there are often a large number of tables similar to dictionary tables. They may have a relationship with the business table. This relationship can be understood as a "label" rather than the usual "master-slave relationship". These tables rarely change and can be cached based on the primary key ID. The following figure illustrates a typical "label relationship" diagram:

In the case of sharding, when the business table is sharded due to scale, the association between the business table and these attached dictionary tables becomes a more difficult problem, considering that the dictionary table has the following characteristics:

• 1. Infrequent changes
• 2. The overall amount of data has not changed much
• 3. The data size is not large, rarely exceeding hundreds of thousands of records.

In view of this, MyCAT defines a special table called "global table", which has the following characteristics:

• 1. Insertion and update operations of the global table will be executed on all nodes in real time to maintain data consistency of each shard
• 2. The query operation of the global table is obtained from only one node
• 3. Global tables can perform J0IN operations with any table

Defining dictionary tables or some tables that meet the characteristics of dictionary tables as global tables can effectively solve the problem of data JOIN from another aspect. Through global table + ER relationship-based sharding strategy, MyCAT can meet more than 80% of enterprise application development.

The global table is configured as follows (the global table will be stored in all nodes):

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links