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

Abstract: after the database is split, the data is distributed to different database instances, which can reduce the amount of data and increase the number of instances. However, the future is bright and the road is tortuous. When it comes to splitting, there is no escape from the query problem of "the query that was originally in the same database will become across two database instances".

Guide reading

A few days ago, an e-commerce user due to the rapid development of business, the rapid growth of visits, resulting in database capacity and performance bottlenecks. In order to reduce the database size and improve performance, the user decided to split the schema vertically. Splitting according to different tables will have less impact on the application, and the splitting rules will be relatively simple and clear.

The user divides the data vertically into three databases according to members, commodities and orders, and distributes the data to different database instances after the database is divided, in order to reduce the amount of data and increase the number of instances. However, the future is bright and the road is tortuous. When it comes to splitting, there is no escape from the query problem of "the query that was originally in the same database will become across two database instances".

When there is a single database, the data needed for many lists and details pages in the system can be simply queried through the SQL join associated table; but after the library is split, the split data may be distributed on different nodes / instances, and join cannot be used across the database, so the problem brought by join is very thorny.

Cdn.com/02505f6906e90ab445ad612b17858d24a9ccd226.png ">

For example, in the business, you need to show the sales order quantity of a certain category of goods. Now that the order data and commodity data are distributed in two separate database instances, how to make an associated query in business?

The first method that comes to mind for users is to ReFactor the existing business code, query data from two databases respectively, and then do join association in the business code. So the problem is, if you adopt this solution, so many queries in the business will be transformed, and it will be extremely difficult to split and too complex to operate. Cross-library join operation does not have a very efficient method, which requires each sub-database iterative query, query efficiency will also be affected to a certain extent.

Is it just the first two big when you just think about it? Don't worry, the problem of business transformation after the split of the database can be easily solved with a SQL. The specific solution is as follows: ⬇️

Solution idea

After communication, we find that what users encounter is a typical cross-database instance query problem. Currently, Aliyun DMS supports the ability to query SQL across database instances. Users can solve the above problems by using a SQL through DMS. It can not only meet the core demand of "cross-library Join", but also greatly simplify the technical solution of users.

In addition to the customer cases introduced at the beginning, DMS's cross-database instance query function can solve any cross-database query requirements encountered in our business. For example: cross-online database and history database join query to quickly obtain full data; under the unitary architecture, the database of each unit of join queries global data; game business, user data in join MySQL and game equipment data in MongoDB, etc.

Next, let's take a quick-start example to see how users write this SQL.

Information of commodity storehouse

Instance connection: 198.12.13.1 3306, database name: seller

Trade list name: commodity

A table structure that contains some fields:

Create table commondity (id BIGINT (20),-- Commodity IDname varchar (100),-- Commodity name create_time TIMESTAMP,-- Commodity entry time catogary BIGINT (30),-- Commodity Category features text,-- Commodity description param text);-- Information of commodity attribute order base

Instance connection: 198.12.13.2 3306, database name: buyer

Order table name: order_list

A table structure that contains some fields:

Create table order_list (id BIGINT (20),-- order IDbuyer_id BIGINT (30),-- buyer IDcreate_time TIMESTAMP,-- order generation time seller_id BIGINT (30),-- seller IDcommodity_id BIGINT (30),-- Commodity IDstatus int (8)-order status) create DBLink

Before writing the query SQL, you need to configure the DBLink of the seller library and the buyer library in the DMS.

Write and run cross-library query SQL

When the DBLink configuration is complete, you can start writing and running SQL in DMS to achieve the need to query the order list for an item.

SELECT comomndity.catogary, count (1) from buyer_db.buyer.order_list order, seller_db.seller.commondity commonditywhere order.commodity_id= commondity.idGROUP BY commondity.catogary

The syntax of this SQL is fully compatible with MySQL, except that the table name of From is preceded by DBLink.

Therefore, the business side only needs to use DMS to query SQL across databases to easily solve the cross-database query problem after dismantling the database, and the business basically does not need to be reformed.

What is DMS cross-database query

SELECT * FROM oracle .dsqltest.b oracle inner join

Mysql .dsqltest.a mysql on oracle.id = mysql.id

WHERE oracle.id=1

The cross-database instance query function provided by DMS is incubated in Alibaba Group and has served more than 5000 developers, fully supporting all online query requirements of Alibaba cross-database instances. DMS supports online query across isomerous databases, and supports data sources such as MySQL, SQLServer, PostgreSQL and Redis, which provides a global data query capability for applications. Users can cross-query across instances through standard SQL without going through data collection.

Immediate experience

Please log in to the DMS console first.

From the SQL operation, go to the cross-instance SQL window.

Refer to the usage guide, create DBlink, write and run SQL.

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.