Shulou(Shulou.com)05/31 Report--

This article mainly shows you the "sample analysis of database middleware MyCat", which is easy to understand and well-organized. I hope it can help you solve your doubts. Let the editor lead you to study and study the "sample analysis of database middleware MyCat".

1. Mycat application scenarios

With the development of Mycat, there are plenty of applicable scenarios, and new users are constantly coming up with new and innovative solutions. Here are several typical application scenarios:

1. Simple read-write separation, which is the easiest to configure. It supports read-write separation and master-slave switching.

two。 Sub-table and sub-library. For more than 10 million tables, a maximum of 100 billion of single-table slices are supported.

3. Multi-tenant applications, each application has a library, but the application only connects to Mycat, so that the program itself is not modified and multi-tenancy is realized.

4. The report system deals with the statistics of large-scale reports with the help of the sub-table ability of Mycat.

5. Instead of Hbase, analyze big data

6. As a simple and effective scheme for real-time query of massive data, for example, 10 billion frequently queried records need to query the results within 3 seconds. In addition to the query based on primary key, there may also be range query or other attribute query. Mycat may be the most simple and effective choice.

MYCAT can achieve negative read operations and mycat load equalization under the separation of read and write, balancing a large number of read operations to different slave libraries, mainly in the case of one master and more slaves.

MYCAT can achieve high availability of the database. When the master node of the database is available, configure a writable slave node, both of which are configured in MYCAT. When the master node goes down, MyCAT will automatically route the write operation to the standby node, but does not support continuing master-slave synchronization after switching.

When read-write separation can no longer meet the ever-increasing traffic, MYCAT can split the database vertically, dividing all database tables according to modules, and splitting different types of tables to different database servers.

With the growth of business, if there is a database performance problem after vertical split, it needs to be split horizontally, which is commonly known as the sub-database table. Split a large amount of table data into different server libraries, the table structure is the same, and using MYCAT to achieve horizontal segmentation is completely transparent to the front-end application, and there is no need to adjust the foreground logic.

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.

two。 Limitations of traditional relational databases

Due to the lack of expansibility, the traditional relational database has great defects in the face of big data, but the relational model and transaction mechanism are not essential for most systems. at present, the mainstream practice in the industry is to split the traditional database (including vertical segmentation, horizontal segmentation, etc.) to improve the scalability of the database. However, segmentation brings new problems, such as multi-data source management, cross-node join, distributed transaction and so on. Let's explore how Mycat can solve these problems.

Multi-data source management problem

There are mainly two ways to solve the problem of multi-data source management. the first is the client mode, which configures and manages one (or more) data sources in each application module and accesses each database directly. complete the data integration in the module. Second, all data sources are managed uniformly through the intermediate agent layer, and the back-end database cluster is transparent to the front-end applications. The first method is not universal, each application needs to develop its own data integration function, and the system that has been built needs code refactoring, which is not suitable for promotion. At present, the second method is mainly used, and the principle of Mycat is as follows: the most important verb in the principle of Mycat is "intercept", which intercepts the SQL statement sent by the user. First, it makes some specific analysis of the SQL statement, such as fragmentation analysis, routing analysis, read-write separation analysis, cache analysis, etc., and then sends the SQL to the real database at the back end, and the returned results are processed appropriately. Finally, it is returned to the user.

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

At present, the mainstream distributed database middleware are TDDL, Amoeba, Coba and so on. TDDL is different from other products, it is not an independent middleware, it can only be regarded as the middle layer, and it is provided to the application in the way of Jar package. It belongs to the idea of JDBC Shard, and there are many other similar products online. Amoeba provides services as a true independent middleware, that is, applications connect to Amoeba to operate MySQL clusters, just like a single MySQL. From the architecture, Amoeba is the early product of middleware, and the back end is still using JDBC Driver. Cobar is an evolved version based on Amoeba. A significant change is to change the back-end JDBC Driver to the native MySQL communication protocol layer, which means that mainstream databases such as Oracle and ProstgreSQL cannot be supported. MyCat is also a version developed on the basis of Cobar, the back-end is changed from BI0 to NIO, the concurrency has been greatly improved, and the support for aggregation functions such as Order By, GroupBy, limit and so on has been increased, and most of the mainstream databases are supported.

Cross-node join problem

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

1. Overall table

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

In the case of fragmentation, when the business table is sliced because of its size, the association between the business table and these affiliated dictionary tables becomes a thorny problem, considering that the dictionary table has the following characteristics:

1. Changes are infrequent

two。 The overall amount of data has not changed much.

3. The scale of the data is small and there are rarely more than hundreds of thousands of records.

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

1. The insert and update operations of the global table will be performed on all nodes in real time to maintain the data consistency of each fragment.

two。 The query operation of the global table, which is obtained from only one node

3. Global tables can perform J0IN operations with any table.

Defining a dictionary table or some tables that conform to the characteristics of a dictionary table as a global table, on the other hand, solves the problem of data J0IN. Through the global table + sharding strategy based on ER relationship, MyCAT can meet more than 80% of enterprise application development.

The global table is configured as follows (the global table is stored on all nodes):

The above is all the contents of the article "sample Analysis of Database Middleware MyCat". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.