Teach you to use a SINGLE SQL to solve cross-database query problems

Recently, an e-commerce user due to rapid business development, the rapid growth of traffic, resulting in database capacity and performance bottlenecks. To reduce database size and improve performance, the user decided to split the schema vertically. Split by different tables has less impact on the application and the split rules are simpler and clearer.

The user divides the data vertically into three databases according to members, goods, and orders, and distributes the data to different database instances to reduce the amount of data and increase the number of instances. However, the future is good, the road is tortuous. When it comes to splitting, you can’t escape the problem of “a query in the same database becomes a query across two database instances.”

In the case of single library, the data required by many lists and detail pages in the system can be simply queried by SQL JOIN associated table. However, the data after splitting may be distributed on different nodes/instances, and join cannot be used across libraries. In this case, the problem brought by Join is very difficult.

For example, the business needs to display the sales order quantity of a certain category of commodities. Now the order data and commodity data are distributed in two independent database instances. How to conduct associated query in the business?

The first approach that comes to mind is to refactor the existing business code, query the data from the two databases separately, and then join the business code. The problem is that if this solution is adopted, there are so many queries in the business that it is extremely difficult to split and operate. There is no very efficient method for cross-library join operation, which requires each branch library to iterate query, and the query efficiency will also have a certain impact.

Is one head bigger than two just thinking about it? Don’t worry, about the business transformation problem after the database split, in fact, with a SQL can be easily done. The specific solution is as follows: ⬇️

solution

After communication, we found that the user encountered a typical cross-database instance query problem. At present, Ali Cloud DMS has supported the ability of SQL query across database instances. Users can solve the above problems by using a SINGLE SQL through DMS. It can not only meet the core demand of “cross-library Join”, but also greatly simplify users’ technical solutions.



In addition to the customer cases introduced at the beginning, the query capability of DMS cross-database instances can solve any cross-database query we encounter in our business. For example, join query across online library and history library can quickly obtain full data; In the unitary architecture, join the database of each unit to query global data. For game business, user data in MySQL and game equipment data in MongoDB can be joined.

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

Commodity repository information

Example connection: 198.12.13.1:3306, database name: seller Commodity name: commodity

Create table commondity(id BIGINT(20), -- merchandise id name varchar(100), -- merchandise name create_time TIMESTAMP, -- Catogary BIGINT(30), -- commodity category features text, -- commodity description param text); -- Commodity attributesCopy the code

Order library information

Example connection: 198.12.13.2:3306, database name: buyer table name: order_list

Create table order_list(id BIGINT(20), -- order id buyer_id BIGINT(30), -- buyer id create_time TIMESTAMP, Seller_id BIGINT(30), -- seller ID commodity_id BIGINT(30), -- commodity ID status int(8) -- order statusCopy the code

Create DBLink

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

Write and run cross-library query SQL

After the DBLink configuration is completed, SQL can be written and run in DMS to realize the requirement of querying the order list of a certain commodity.

SELECT comomndity.catogary,
       count(1)
 from buyer_db.buyer.order_list 
order,
      seller_db.seller.commondity commondity
where order.commodity_id= commondity.id
GROUP BY commondity.catogary;
Copy the code

The syntax of this SQL is fully compatible with MySQL, except that DBLink is preceded by the name of the table From. Therefore, the business side only needs to use DMS to query SQL across the database to easily solve the problem of cross-database query after the debasement, and there is basically no need to transform the business.

What are DMS cross-database queries

SELECT * FROM

.dsqltest.b oracle inner join

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


WHERE oracle.id=1

The cross-database instance query function provided by DMS was incubated by Alibaba Group and has served more than 5000 developers, comprehensively supporting all online query requirements of Alibaba’s cross-database instance. DMS supports online query across heterogeneous databases and data sources such as MySQL, SQLServer, PostgreSQL and Redis, providing applications with the capability of global data query. Users can cross-query across instances through standard SQL without data aggregation.

Experience immediately

1. Log in to the DMS console first.
2. From SQL operations, enter the Cross-instance SQL window.
   
   
3. Refer to the user guide to create DBlink, write and run SQL.