Why does database middleware not support join? Does middleware support join?

Why does database middleware not support join? Does middleware support join?

Some netizens have doubts that database middleware only supports four types of SQL in the article "Let you design database middleware:

• Partition key common Query

• IN query on the partition key

• Queries on non-partition keys

• Sorting with limited functions + paging Query

Can these four types of SQL statements meet the company's business needs?

It seems that the architecture conclusion of "Let you design database middleware" does not satisfy the netizens who are exploring the root cause, so I will talk about the details of the Demand Survey process at the end of the 13 s, as a front-line architect, the study is still rigorous.

 

I. Business-side post-database sharding SQL requirements

Conclusion: based on a preliminary survey, it is found that the application scenarios of 58 business lines that require database sharding are:

• Simple query on the partition key

  WHERE key = xxx AND xxx

• IN query on the partition key

  WHERE key IN (xxx, yyy) AND xxx

• Simple query on non-partition key

  WHERE notkey = xxx AND xxx

• Sorting + paging requirements

  Order by xxx OFFSET xxx LIMIT xxx

Most of the requirements are concentrated in the first three. Due to distributed implementation difficulties, various business lines often adopt some restrictions or workarounds. For example:

• Create an index table to avoid database traversal and internal sorting

• Use additional id query conditions to avoid querying large data volumes

 

The survey results show that each business line does not have the following requirements:

• Database join

• Exaggerated database transactions

• Kuafu subquery

• Other strange SQL statements

2. Research by the Search R & D department

I learned from @ longc, Senior Architect of the search R & D department that there is no database sharding requirement for the moment.

VOICEOVER: @ long Shen is the search kernel. I am a business developer using MySQL.

 

3. Instant Messaging Department survey

I communicated with @ sunx and helped the technical department not to perform horizontal database sharding and only to perform horizontal table sharding. the business requirement is "common query on partition key" in common requirements ".

For the "user login table" in the help of 58, the data volume is large. Currently, it is divided into 32 tables and the uid is used as the partition key. All queries will carry the partition key, therefore, you can directly locate the partition to which the data belongs.

In the preceding example, assume that 58 has divided a table with a large data volume into three tables with id as the partition key. All upstream queries will carry the query condition id = xxx (of course, you can also include other query conditions ).

VOICEOVER: @ Xuan Jie designs a system with an extremely complete architecture.

 

Iv. Mobile R & D department survey

I learned from @ liunz that the use of wireless database sharding is similar to that of the help Technology Department. It is a common query on the partition key ".

 

5. Architecture Department survey

From @ liuzw, I learned that the architecture department uses horizontal database sharding for imc, umc, and other services. The business needs are "common queries on the patition key" in common requirements ", "IN query on partition key" and "query on non-partition key ".

For "IN query on the partition key", the architecture department uses the method of locating each partition key to the relevant database, summarizing the query result set, and returning the upstream Method for implementation. Note: As shown IN, IN queries with a partition key do not necessarily traverse all databases.

 

For non-partition key queries, the architecture department has two processing methods based on different services:

Method 1

The business party does not need accurate data. It can obtain the data of a random database to meet the requirements of the business party. For example, "Query 10 users with portraits"

When the business side does not need to pay attention to the accuracy of the result set, a database can be randomly queried.

VOICEOVER: This is a good design, a typical good case of "determining technical solutions based on business needs.

Method 2

If the business party needs precise data, it must traverse all the databases, for example, "querying users with shenjian username ".

VOICEOVER: it is a pity that the "genetic method" is not used to generate uid. For details about the "genetic method" solution, refer to "single-KEY business, database horizontal splitting architecture practices | the path of architects".

Vi. Member Technology Department survey

According to @ wangzt, the member Technology Department uses horizontal database sharding. The four SQL requirements for database sharding are useful in the business.

In addition to the full database query solution used by the architecture department, the member technology department still uses redundant data to solve this problem:

This query method uses redundant data to avoid full-database queries. The disadvantage is that data consistency may exist.

 

For "multi-database paging query", the member technology department handles index tables.:

Using order database sharding, the buyer queries the index table, and the indexing table is redundant in nature.

VOICEOVER: For details about the "horizontal segmentation of post services" solution, see "1-to-multiple businesses, database horizontal segmentation architecture once done | architect's path".

VII. Investigation by the payment platform Department

I learned from @ hudp that database sharding Data Access and all online systems in the currency systemReal-time businessAll must carry the partition key, so the access mode is the same as the data access mode of instant messaging.

 

ButSupport System/statistical requirementsIn terms of database sharding data, they plan to introduce cobar to solve their problems.

 

VIII. Front-End Business Department survey

@ Wangjk understands that the front-end business department has four database-sharding SQL statements. For database paging, the business of the front-end business department must contain a special id as the where field, to avoid pulling a large amount of data and sorting it again.

IX. Conclusion

58 If You Want To implement database middleware, four types of SQL statements are supported in Phase I:

• Partition key common Query

• IN query on the partition key

• Queries on non-partition keys

• Sorting with limited functions + paging Query

It can meet the needs of most database shards in the business line.

All architecture designs that are out of business are rogue.

The technology should be rigorous. In addition to the above-mentioned demand research, we also did a full technical research:

Database middleware TDDL research notes

Database middleware cobar research notes

Database middleware mysql-proxy survey notes

With the subsequent Outline Design:

Suppose you want to design database Middleware

Last: I saw the familiar name in the research documents, and the tears flowed down without knowing it,Are you okay??